Reading: The posted Lecture 5 and Lecture 6 Slides, and Ullman/Widom Sections 6.

Question

Reading: The posted Lecture 5 and Lecture 6 Slides, and Ullman/Widom Sections 6.1-6.2 and 6.4 [Next week: Ullman/Widom Sections 6.2-6.3.] Your task in this assignment is to write a set of SQL queries (I will supply the tables) First, download the script file Furniture2018.sql from the dropbox and run it in SQLDeveloper to build the tables you will be querying This script will build and display the contents of the following four tables that store data for a fictitious furniture supplier: CUSTOMER(CustomerID, Name, City, State, Zip), which contains information on customers of the company; PRODUCT ProductID, Description, Material, Price), which contains information on products sold by the company; FULLORDER(OrderlD, OrderDate, CustomerlD), which contains information on orders placed with the company; REQUEST(OrderID, ProductID, Quantity), which contains information on the individual products requested in customers' orders In additional to the primary keys indicated above, OrderID and ProductID in REQUEST are foreign keys referencing OrderID in FULLORDER and ProductID in PRODUCT, respectively, and CustomerII in FULLORDER is a foreign key referencing CustomerlD in CUSTOMER. In SQLDeveloper, look at the Columns, Data, and Constraints for each of the four tables before continuing, to be sure that they have been constructed correctly. You might also want to draw the foreign keys and reference arrows into the set of relation schemas given above to be sure that you understand the links among the tables For each of the following query problems, follow the steps we discussed in class: interpret the problem, predict the output by solving it by hand on the table in question, write a query to solve the problem, and test the query. (Most of the query problems can be solved with information from just one of the given tables, but some will require joins.) In a separate .sql file (do not modify Furniture2018.sql), write a script that contains just the following ten SQL queries (in this order) 1. Give an alphabetical list of the names of all customers 2. Give the city, state, and zip of all customers on the west coast (that is, in Washington (WA), Oregon (OR), and California (CA))

Explanation / Answer

--Query 1
SELECT Name FROM CUSTOMER ORDER BY Name ASC;
--Query 2
SELECT Name,City,State,Zip FROM CUSTOMER WHERE State IN ('WA','OR','CA');
--Query 3
SELECT Material,ProductID,Price FROM PRODUCT ORDER BY Material ASC,ProductID ASC;
--Query 4
SELECT Name,City FROM CUSTOMER WHERE Name LIKE '%Furnishings';
--Query 5
SELECT State,COUNT(CustomerID) FROM CUSTOMER
GROUP BY State ORDER BY State ASC;
--Query 6
SELECT O.OrderDate,R.OrderID FROM FULLORDER O INNER JOIN
REQUEST R ON O.OrderID=R.OrderID
WHERE O.OrderDate>='01-JAN-17'
GROUP BY R.OrderID HAVING COUNT(R.orderID>=1)
ORDER BY O.OrderDate
--QUERY 7
SELECT CustomerID,OrderDate FROM FULLORDER
GROUP BY CustomerID HAVING COUNT(OrderID) >=1;

--Query 8
SELECT OrderID,SUM(Quantity) AS Total_NO_of_Items FROM REQUEST
GROUP BY OrderID
ORDER BY Total_NO_of_Items DESC;
--QUERY 9
SELECT O.OrderID,C.State,C.Zip FROM FULLORDER O INNER JOIN
CUSTOMER C ON O.CustomerID=C.CustomerID
ORDER BY OrderID;
--Query 10
SELECT P.ProductID,COUNT(R.OrderID) FROM PRODUCT P
INNER JOIN REQUEST R ON P.productID=R.ProductID
GROUP BY P.ProductID
ORDER BY ASC;

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.