Quiz 6 - P.Combs

Quiz 6 For each question write the question, SQL script and the output. Include all three in a notepad or word document. Do not include screen shots. Know how to cut and paste from the SQLplus screen. customers custID custLName custFNa me DateofBirth custCity C1 Attenweiler Shannon 10-Jul-1987 Florence C2 Marksbury Faisal 25-Dec-1965 Blue Ash C3 O'Connell Joseph 12-Feb-1976 West Chester C4 Owen Meghan 11-Mar-1982 Loveland C5 Sullivan Sean 19-Nov-1981 Batavia C6 Weaver John 21-Jan-1963 Dayton C7 Alghanem Kelli 22-Apr-1972 Newport C8 Ballhaus William 6-Oct-1977 Fort Thomas Orders Questions: 1. List the total number of orders for placed from each city SELECT custCity, COUNT(*) AS total_orders FROM combs_customers orderI D orderDate Amou nt custID 111 11-Mar-2013 234.45 C1 112 11-Mar-2013 962.89 C3 113 12-Mar-2013 734.46 C4 114 13-Mar-2013 393.76 C5 115 13-Mar-2013 314.34 C2 116 13-Mar-2013 733.23 C4 117 16-Mar-2013 678.89 C1 118 16-Mar-2013 895.34 C6 119 16-Mar-2013 232.64 C6 120 16-Mar-2013 723.29 C8 121 17-Mar-2013 278.34 C3 122 17-Mar-2013 123.45 C6 123 17-Mar-2013 673.45 C4 124 18-Mar-2013 312.35 C2 125 19-Mar-2013 863.34 C5 126 19-Mar-2013 123.45 C3 127 19-Mar-2013 965.76 C1

JOIN combs_orders ON combs_customers.custID = combs_orders.custID GROUP BY custCity; CustCity Total_Orders Batavia 2 Blue Ash 2 Dayton 1 Florence 2 Fort Thomas 1 Loveland 2 Newport 1 West Chester 1 2. List the total value (amount) for each customer. SELECT custID, SUM(Amount) AS total_amount FROM combs_orders GROUP BY custID; 3. List all the orders (ordered, order date, amount) along with customer details (customer name and city). SELECT o.orderID, o.orderDate, o.Amount, c.custLName || ', ' || c.custFName AS customer_name, c.custCity FROM combs_orders o JOIN combs_customers c ON o.custID = c.custID; 4. List the customer’s name with least total amount of orders. SELECT c.custLName, c.custFName AS CustomerName, SUM(o.Amount) AS TotalAmount FROM combs_customers JOIN combs_orders ON c.custID = o.custID GROUP BY c.custLName, c.custFName ORDER BY TotalAmount FETCH FIRST ROW ONLY; CUSTOMERNAME TOTALAMOUNT ---------------------------------- Marksbury, Faisal 314.34

The tables were created using the following SQL scripts: CREATE TABLE combs_customers (custID CHAR(2) PRIMARY KEY, custLName VARCHAR2(25), custFName VARCHAR2(25), DateofBirth DATE, custCity VARCHAR2(20)); Table created. CREATE TABLE combs_orders(orderID CHAR(3) PRIMARY KEY, orderDate DATE, Amount NUMBER(10,2), custID CHAR(2) REFERENCES raghavan_customers(custID)); Table created. INSERT INTO combs_customers VALUES ('C1', 'Attenweiler', 'Shannon', '10- Jul-1987', 'Florence'); 1 row created. INSERT INTO combs_customers VALUES ('C2','Marksbury', 'Faisal', '25-Dec- 1965', 'Blue Ash'); 1 row created.

INSERT INTO combs_customers VALUES ('C3','O''Connel', 'Joseph', '12-Feb- 1976', 'West Chester'); 1 row created. INSERT INTO combs_customers VALUES ('C4','Owen', 'Meghan', '11-Mar- 1982', 'Loveland'); 1 row created. INSERT INTO combs_customers VALUES ('C5','Sullivan', 'Sean', '19-Nov- 1981', 'Batavia'); 1 row created. INSERT INTO combs_customers VALUES ('C6','Weaver', 'John', '21-Jan-1963', 'Dayton'); 1 row created. INSERT INTO combs_customers VALUES ('C7','Alghanem', 'Kelli', '22-Apr- 1972', 'Newport'); 1 row created. INSERT INTO combs_customers VALUES ('C8','Ballhaus', 'William', '6-Oct- 1977', 'Fort Thomas'); 1 row created.

INSERT INTO combs_orders VALUES ('119','16-Mar-2013',232.64,'C6'); 1 row created. INSERT INTO combs_orders VALUES ('120','16-Mar-2013',723.29,'C8'); 1 row created. INSERT INTO combs_orders VALUES ('121','17-Mar-2013',278.34,'C3'); 1 row created. INSERT INTO combs_orders VALUES ('122','17-Mar-2013',123.45,'C6'); 1 row created. INSERT INTO combs_orders VALUES ('123','17-Mar-2013',673.45,'C4'); 1 row created. INSERT INTO combs_orders VALUES ('124','18-Mar-2013',312.35,'C2'); 1 row created. INSERT INTO combs_orders VALUES ('125','19-Mar-2013',863.34,'C5'); 1 row created. INSERT INTO combs_orders VALUES ('126','19-Mar-2013',123.45,'C3');

1 row created. INSERT INTO combs_orders VALUES 1 row created.