COMP 1630
Relational Database Design and SQL
Term Project
Using Microsoft SQL Server, write the SQL statements for each question necessary to generate the required result set. Clearly identify your answers to the questions. Save your work in the Desire2Learn Drop box for SQL Project.
Part A - Database and Tables
1.Run the script SQLProjectData.sql to create the Project database, create the tables listed below, and to load the data into the tables.
Customers 91 rows
Employees 9 rows
Shippers 3 rows
Suppliers 15 rows
Products 77 rows
Orders 1078 rows
OrderDetails 2820 rows
There is nothing to hand in for this section!
Part B - SQL Statements
1.List the order detail rows where the quantity is greater than or equal to80 and less than or equal to 90. Display the order id and quantity from the OrderDetails table, the product id from the Products table, and the supplier id from the Suppliers table. Order the result set by the order id. The query should produce the result set listed below.
OrderID Quantity ProductID SupplierID
----------- ---------------------- -----------
10027 80 65 2
10052 90 60 8
10052 90 75 12
...
10991 90 76 12
11008 90 34 15
11031 80 13 6
(27 row(s) affected)
2.List the product id, product name, and unit price from the Products table where the unit price is less than $10.00. Order the result set by the product name. The query should produce the result set listed below.
ProductID ProductName UnitPrice
------------ -------------------------------------------------------
52 Filo Mix 7.00
33 Geitost 2.50
24 Guaraná Fantástica 4.50
…..
54 Tourtière 7.45
23 Tunnbr?d 9.00
47 Zaanse koeken 9.50
(11 row(s) affected)
3.List the customer id, company name, contact name, country, and phone from the Customers table where the country is equal to France or Spain. Order the result set by the customer id. The query should produce the result set listed below.
CustomerID CompanyName ContactName Country Phone
--------------- -------------------------------------------------------------------------------------------------
BLONP Blondel père et fils Frédérique Citeaux France 88.60.15.31
BOLID Bólido Comidas preparadasMartín Sommer Spain (91) 555 22 82
BONAP Bon app' Laurence Lebihan France 91.24.45.40
…..
SPECD Spécialités du monde Dominique Perrier France (1) 47.55.60.10
VICTE Victuailles en stock Mary Saveley France 78.32.54.86
VINET Vins et alcools Chevalier Paul Henriot France 26.47.15.10
(16 row(s) affected)
4.List the total number of shipping city and their names. Only display the shipping city and the count if there are more than30shipping cities. The query should produce the result set listed below.
ShipCity Count
------------------ -----------
Rio de Janeiro 40
London 47
So Paulo 35
Graz 33
México D.F. 36
Boise 42
(6 row(s) affected)
5.List the orders where the shipped date is greater than or equal to July 1, 1992 and less than or equal to June 30, 1993, and calculate the length in years from the shipped date to January 1, 2011. Display the order id, and the shipped date from the Orders table, the company name from the Customers table, and the calculated length in years for each order. Display the shipped date in the format MMM DD YYYY. Order the result set by order id and the calculated years. The query should produce the result set listed below.
OrderID CompanyName ShippedDate ElapsedYear
----------- ------------------------------------------------- -------------
10264 Folk och f? HB Jul 17 1992 18
10265 Blondel père et fils Jul 6 1992 18
10269 White Clover Markets Jul 3 1992 18
…..
10615 Wilman Kala Jun 30 1993 17
10616 Great Lakes Food Market Jun 29 1993 17
10617 Great Lakes Food Market Jun 28 1993 17
(346 row(s) affected)
6.List all the orders where the order date is greater than or equal to January 1, 1992 and less than or equal to March 30, 1992, and the cost of the order is greater than or equal to $1500.00. Display the order id, order date, and a new shipped date calculated by adding 10 days to the shipped date from the Orders table, the product name from the Products table, the company name from the Customer table, and the cost of the order. Format the order date and the shipped date as MON DD YYYY. Use the formula (OrderDetails.Quantity * Products.UnitPrice) to calculate the cost of the order. The query should produce the result set listed below.
OrderID ProductName CompanyName OrderDate NewShippedDate OrderCost
----------- ---------------------------------------------------------------- --------------------- -------------
10141 Schoggi Schokolade Frankenversand Jan 2 1992Jan 19 1992 2195.00
10141 Camembert Pierrot Frankenversand Jan 2 1992 Jan 19 1992 1700.00
10163 Camembert Pierrot Frankenversand Feb 7 1992 Feb 21 1992 1632.00
…..
10195 C?te de Blaye Que Delícia Mar 23 1992 Apr 6 1992 5533.50
10196 C?te de Blaye LILA-Supermercado Mar 24 1992 May 2 1992 7905.00
10198 C?te de Blaye Océano Atlántico Ltda. Mar 26 1992 Apr 9 1992 1581.00
(11 row(s) affected)
7.List all the orders with a shipping city of San Francisco and an order quantity greater than 20. Display the order id from the Orders table, and the unit price and quantity from the OrderDetails table. Order the result set by the order id. The query should produce the result set listed below.
OrderID UnitPrice Quantity
----------- --------------------------
10132 22.00 30
10579 7.75 21
10719 7.45 40
10884 10.00 40
10884 38.00 21
(5 row(s) affected)
8.List the products which contain chai or coffee in their name. Display the product id, product name, quantity per unit and unit price from the Products table. Order the result set by unit price in descending order. The query should produce the result set listed below.
ProductID ProductName QuantityPerUnit UnitPrice
------------ ----------------------------------------------------------- ---------------------
43 Ipoh Coffee 16 - 500 g tins 46.00
1 Chai 10 boxes x 20 bags 18.00
(2 row(s) affected)
Part C - INSERT, UPDATE, DELETE and VIEWS Statements
1.Create a view called vw_supplier_items listing the suppliers and the items they have shipped. Display the supplier id and name from the Suppliers table, and the product id and product name from the Products table. Use the following query to test your view to produce the result set listed below.
SELECT *
FROM vw_supplier_items
ORDER BY Name, ProductID
SupplierID Name ProductID ProductName
------------- ------------------------------------------- ----------------------------------------
5 Supplier 11 Queso Cabrales
5 Supplier 12 Queso Manchego La Pastora
1 Supplier A 1 Chai
...
15 Supplier O 56 Gnocchi di nonna Alice
15 Supplier O 69 Gudbrandsdalsost
15 Supplier O 71 Fl?temysost
(77 row(s) affected)
2.Using the UPDATE statement, modify the name of the supplier id 5 to ‘Supplier E’.
3.Using the INSERT statement, add two rows to the Suppliers table. The first row will have a supplier id of 16, and a supplier name of ‘Supplier P’, and the second row will have a supplier id of 17, and a supplier name of ‘Supplier Q’.
4.Create a view called vw_employee_info to list the employees in the Employee table. Display the employee id, last name, first name, and phone number. Format the name as last name followed by a comma and a space followed by the first name. Display the phone number as opening bracket followed by the first 3 digits of the phone number followed by the closing bracket followed by the next 3 digits of the phone number followed by a dash followed by the last 4 digits of the phone number. Use the following query to test your view to produce the result set listed below.
SELECT *
FROM vw_employee_info
WHERE EmployeeID IN ( 1, 3, 5)
EmployeeID Name PhoneNumber
--------------- ---------------------------------- ------------------
1 Davolio, Nancy (604)555-9857
3 Leverling, Janet (604)555-3412
5 Buchanan, Steven (604)555-4848
(3 row(s) affected)
5.Using the UPDATE statement, change the fax value to 000-000-0000 for all rows in the Customers table where the current fax value is NULL. There will be 22 rows updated.
6.Create a view called vw_order_cost to list the cost of orders. Display the order id and order date from the Orders table, the product id from the Products table, the company name from the Customers table, and the order cost. To calculate the cost of the orders, use the formula (OrderDetails.Quantity * OrderDetails.UnitPrice). Use the following query to test your view to produce the result set listed below.
SELECT *
FROM vw_order_cost
WHERE orderID BETWEEN 10100 AND 10200
ORDER BY ProductID
OrderID OrderDate ProductID CompanyName OrderCost
----------- ------------------------- ------------------------------------------------ -------------
10101 1991-10-28 00:00:00 1 Antonio Moreno Taquería 96.00
10156 1992-01-28 00:00:00 1 Richter Supermarkt 300.00
10159 1992-01-31 00:00:00 1 Maison Dewey 480.00
...
10170 1992-02-20 00:00:00 77 Reggiani Caseifici 216.00
10115 1991-11-20 00:00:00 77 Océano Atlántico Ltda. 45.00
10117 1991-11-22 00:00:00 77 Tradi??o Hipermercados 254.80
(257 row(s) affected)
7.Using the UPDATE statement, increate the unit price in the Products table by 10% for rows with a current unit price less than $5.00. There will be 2 rows updated.
8.Using the DELETE statement, delete the row with the supplier id of 16, and a supplier name of ‘Supplier P’ from the Suppliers table.
Part D – ERD Project SQL Queries
Refer back to your ERD project, fix up any problems with your ERD. Embed your ERD in this project submission and complete the following. You cannot use the same SQL query for multiple questions.
1.Create 5 (or more) tables that correspond to the entities in your ERD project. Choose tables that are in relationship with each other so you can use the tables for the following queries. Show the SQL statements. (Do not forget the primary and foreign keys, and any appropriate constraints.)
2.Insert some data into each of the tables. Show the SQL statements, and the content of each table after all the tables are being populated.
3.Come up with one query of your database that requires an inner join of at least two tables. State the query, show the SQL statement, and the output.
4.Come up with one query of your database that requires an outer join of at least two tables. State the query, show the SQL statement, and the output.
5.Come up with one query of your simple database that requires at least one subquery. State the query, show the SQL statement, and the output.
6.Come up with one query of your simple database that requires an aggregate function. State the query, show the SQL statement, and the output.
7.Come up with one query of your simple database that requires the OVER clause. State the query, show the SQL statement, and the output.
8.Come up with a view of your simple database that limits the user’s access to specific rows and columns. State the purpose of the view, show the SQL statement for the view definition, an example of how the view is invoked, and the output.
版权所有：编程辅导网 2021 All Rights Reserved 联系方式：QQ:99515681 微信：codinghelp 电子信箱：99515681@qq.com
免责声明：本站部分内容从网络整理而来，只供参考！如有版权问题可联系本站删除。
