Your organization has requested that you create a new database to support organizational needs....

Question

Your organization has requested that you create a new database to support organizational needs. Based on the information you provided in this week’s Discussion, create a database as defined in the database project.

The project must include at least five tables, and should have no more than 10 tables.

Define your database.
- Describe the database, its purpose, why it was created, who it was created for, who the users are, and what type of processes and functions it will support.
- List the entities of your database.
- Present the business rules that determine connectivity, using the format reviewed in Week 2.
- Draw the ERD; copy the drawing into your Word project document.

Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys.

Insert data into each table. Show select statements and display the output of each table.Note:Student’s name must be inserted into table as part of the data!

Perform the SQL below:
- Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and the output.
- Get information from at least3tables in one statement, and provide the output using the Join operator. Use ANSI Join syntax. State the purpose of the query; show the query and the output. Add a screen shot of SS Management Studio showing the query and results.
- Get information from2tables in one statement, and provide the output using the Left Outer Join operator. State the purpose of the query; show the query and the output. The outer join should be designed to retrieve information from the left table that has no matches in the right table. If that is not possible for your database, explain why.
- Create a query using the IN keyword with a subquery. State the purpose of the query; show the query and the output.
- Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command. State the purpose of the query; show the query and the output.
- Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command using the HAVING clause to filter the aggregate results. State the purpose of the query; show the query and the output.
- Update one row. State the purpose of the query; show the result set for the row(s) before the update; show the query; show the row(s) after the update.
- Delete one row. State the purpose of the query; show the result set before the delete; show the query; show the result set after the delete.

Ravindra Kumar · Accepted Answer

College/4(iii).PNG
College/4(iv).txt
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, EQUIJOIN are the types of joins. Explanation: Right outer join refers to 
join records from the write table that have no matching key in the left table are include in the result set.
College/4_i.PNG
College/4_ii.PNG
College/4_iv.PNG
College/4_iv.sql
SELECT * 
   FROM Student_details
   WHERE Student_details.[Roll number] IN (SELECT course_id 
         FROM Course_details 
         WHERE course_id=3) ;
College/4_v.PNG
College/4_vi.PNG
College/4_vi.sql
SELECT COUNT(*)
FROM Student_details;
College/asdfasdfs.PNG
College/college_management_system.bacpac
model.

Your organization has requested that you create a new database to support organizational needs. Based on the information you provided in this week’s Discussion, create a database as defined in the...

Answer To: Your organization has requested that you create a new database to support organizational needs....

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment