Assignment 4 Data Extraction

Assignment 4 is to use SQL queries to obtain appropriate data from databases for betting decision making. In this assignment, you will be asked to write SQL queries to get relevant information from the database you built from Assignment 3 and therefore to answer some questions pertaining to the S record business. Before starting this assignment, you may want to look at the script file below to make sure your queries for the assignment 3 were correct. [Example code for Assignment 3]asgmt3_answer.txt

DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE ORDERS CASCADE CONSTRAINTS; DROP TABLE DIGITALORDER CASCADE CONSTRAINTS; DROP TABLE PHYSICALORDER CASCADE CONSTRAINTS; DROP TABLE PLAYHISTORY CASCADE CONSTRAINTS; DROP TABLE ORDERLINE CASCADE CONSTRAINTS; DROP TABLE SONG CASCADE CONSTRAINTS; DROP TABLE ALBUM CASCADE CONSTRAINTS; DROP TABLE ARTIST CASCADE CONSTRAINTS; DROP TABLE GENRE CASCADE CONSTRAINTS; DROP TABLE CONTAINS CASCADE CONSTRAINTS; DROP TABLE WRITES CASCADE CONSTRAINTS; DROP TABLE BELONGSTO CASCADE CONSTRAINTS; CREATE TABLE CUSTOMER ( CustomerID NUMBER(6), CustomerName VARCHAR2(20), City VARCHAR2(20), State VARCHAR2(2), PostalCode VARCHAR2(5), PhoneNumber VARCHAR2(10), Birthday DATE, RegistrationDate DATE, CONSTRAINT customer_customerdid_pk PRIMARY KEY (CustomerID) ); INSERT INTO CUSTOMER VALUES (991001, 'MORALES BONITA', 'EASTPOINT', 'FL', '32328', '3132223333', '02-APR-70', '01-SEP-10'); -- Add more customers CREATE TABLE ORDERS ( OrderID NUMBER(10), OrderDate DATE, PaymentMethod VARCHAR2(2), OrderType VARCHAR2(1), CustomerID NUMBER(6), CONSTRAINT order_orderid_pk PRIMARY KEY (OrderID), CONSTRAINT order_paymentmenthod_ck CHECK (PaymentMethod IN ('CS', 'CC', 'PP')), CONSTRAINT order_ordertype_ck CHECK (OrderType IN ('P', 'D')), CONSTRAINT order_customerid_fk FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID) ); INSERT INTO ORDERS VALUES (1100000001, '02-SEP-10', 'CS', 'P', 991001); INSERT INTO ORDERS VALUES (1100000002, '10-AUG-11', 'CC', 'D', 991001); -- Add more orders CREATE TABLE DIGITALORDER ( OrderID NUMBER(10), SubscriptionOption VARCHAR2(1), CONSTRAINT dorder_orderid_pk PRIMARY KEY (OrderID), CONSTRAINT dorder_orderid_fk FOREIGN KEY (OrderID) REFERENCES ORDERS (OrderID), CONSTRAINT dorder_subsoption_ck CHECK (SubscriptionOption IN ('M', 'Y')) ); INSERT INTO DIGITALORDER VALUES (1100000002, 'Y'); -- Add more digital orders CREATE TABLE PHYSICALORDER ( OrderID NUMBER(10), DeliveryOption VARCHAR2(1), CONSTRAINT porder_orderid_pk PRIMARY KEY (OrderID), CONSTRAINT porder_orderid_fk FOREIGN KEY (OrderID) REFERENCES ORDERS (OrderID), CONSTRAINT porder_doption_ck CHECK (DeliveryOption IN ('F', 'S', 'E')) ); INSERT INTO PHYSICALORDER VALUES (1100000001, 'E'); -- Add more physical orders CREATE TABLE SONG ( SongID NUMBER(10), SongTitle VARCHAR2(50), PlayTime NUMBER(3), CONSTRAINT song_songid_pk PRIMARY KEY (SongID) ); INSERT INTO SONG VALUES (2200000001, 'I Wanna be Where You are', 4); INSERT INTO SONG VALUES (2200000008, 'Esta Nache Voy Contigo', 4); -- Add more songs CREATE TABLE ALBUM ( AlbumID NUMBER(10), AlbumTitle VARCHAR2(50), AlbumPrice NUMBER(3), ReleaseDate DATE, CONSTRAINT album_albumid_pk PRIMARY KEY (AlbumID) ); INSERT INTO ALBUM VALUES (3300000001, 'Got to Be There', 10, '24-JAN-1972'); INSERT INTO ALBUM VALUES (3300000003, 'Magia', 5, '24-JUN-1991'); -- Add more albums CREATE TABLE PLAYHISTORY ( OrderID NUMBER(10), SongID NUMBER(10), PlayDate DATE, PlayCount NUMBER(3), CONSTRAINT playhistory_ordersongid_pk PRIMARY KEY (OrderID, SongID), CONSTRAINT playhistory_orderid_fk FOREIGN KEY (OrderID) REFERENCES DIGITALORDER (OrderID), CONSTRAINT playhistory_songid_fk FOREIGN KEY (SongID) REFERENCES SONG (SongID) ); INSERT INTO PLAYHISTORY VALUES (1100000002, 2200000008, '11-AUG-11', 6); -- Add more playhistory records CREATE TABLE ORDERLINE ( OrderID NUMBER(10), AlbumID NUMBER(10), QuantitiesOrdered NUMBER(3), CONSTRAINT orderline_oderalbumid_pk PRIMARY KEY (OrderID, AlbumID), CONSTRAINT oderline_orderid_fk FOREIGN KEY (OrderID) REFERENCES PHYSICALORDER (OrderID), CONSTRAINT orderline_albumid_fk FOREIGN KEY (AlbumID) REFERENCES ALBUM (AlbumID) ); INSERT INTO ORDERLINE VALUES (1100000001, 3300000001, 2); -- Add more orderline records CREATE TABLE ARTIST ( ArtistID NUMBER(10), ArtistName VARCHAR2(20), DebutDate DATE, CONSTRAINT artist_artistid_pk PRIMARY KEY (ArtistID) ); INSERT INTO ARTIST VALUES (4400000001, 'Michael Jackson', '05-JAN-1964'); INSERT INTO ARTIST VALUES (4400000002, 'Shakira', '01-JUL-1990'); -- Add more artists CREATE TABLE GENRE ( GenreID NUMBER(4), GenreName VARCHAR2(50), CONSTRAINT genre_genreid_pk PRIMARY KEY (GenreID) ); INSERT INTO GENRE VALUES (5501, 'Rock Music'); INSERT INTO GENRE VALUES (5503, 'Pop Music'); -- Add more genres CREATE TABLE CONTAINS ( SongID NUMBER(10), AlbumID NUMBER(10), CONSTRAINT contains_songalbumid_pk PRIMARY KEY (SongID, AlbumID), CONSTRAINT contains_songid_fk FOREIGN KEY (SongID) REFERENCES SONG (SongID), CONSTRAINT contains_albumid_fk FOREIGN KEY (AlbumID) REFERENCES ALBUM (AlbumID) ); INSERT INTO CONTAINS VALUES (2200000001, 3300000001); INSERT INTO CONTAINS VALUES (2200000008, 3300000003); -- Add more records CREATE TABLE WRITES ( ArtistID NUMBER(10), SongID NUMBER(10), CONSTRAINT writes_artistsongid_pk PRIMARY KEY (ArtistID, SongID), CONSTRAINT writes_artistid_fk FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID),

[Important!!!]This example code is to give you the structure of our database. It has all the necessary commands to create tables, attributes, and constraints, except for the actual data. A few records have been added just to give you an idea of how you could add your own. They are followed by"--Add more records"where you need to write down your own code to populate your database. If you weren't successful in the assignment 3, use this example code as a template to make it right.

Problem statements

1. List the name of artists who had debuted after 2000.

2. Get the ID and title of albums whose price is above 5 dollars.

3. List the ID and name of customers living in Michigan.

4. List the ID and date of orders made by Cash.

5. Get the ID, date, and subscription option of all digital orders

6. Get all customer information for those who purchased the annual subscription.

7. Which state do customers live who chose the expedite delivery? (Suppress duplicates)

8. What is the most played song? List the top 10 list of songs with song titles, artist names, and play counts in descending order of the play count

9. Which album has been sold most? List the top 10 list of albums with album titles, release dates, and quantities ordered in descending order of the quantities ordered.

10. Who did write the song that has been played most in 2010?

11. What are the average prices of albums delivered by Free, Standard, and Expedite shipping?

Q1 SELECT artistname FROM ARTIST WHERE debutdate >= '01-Jan-2000';

-- Q2 SELECT albumid, albumtitle FROM ALBUM WHERE albumprice > 5;

-- Q3 SELECT customerid, customername FROM CUSTOMER WHERE state = 'MI';

-- Q4 SELECT orderid, orderDate FROM ORDERS WHERE paymentmethod = 'CS';

-- Q5 SELECT orderid, orderDate, subscriptionoption FROM ORDERS NATURAL JOIN DIGITALORDER;

-- Q6 SELECT c.* FROM CUSTOMER c, ORDERS o, DIGITALORDER do WHERE c.customerid = o.customerid and o.orderid = do.orderid and do.subscriptionoption = 'Y';

-- Q7 SELECT DISTINCT c.state FROM CUSTOMER c, ORDERS o, PHYSICALORDER po WHERE c.customerid = o.customerid and o.orderid = po.orderid and po.deliveryoption='E';

-- Q8 SELECT s.songtitle, a.artistname, p.playcount FROM ORDERS o, PLAYHISTORY p, WRITES w, SONG s, ARTIST a WHERE o.orderid = p.orderid and p.songid = s.songid and w.songid = s.songid and w.artistid = a.artistid and ROWNUM <= 10 ORDER BY p.playcount DESC;

-- Q9 SELECT a.albumtitle, a.releasedate, l.quantitiesordered FROM ALBUM a, ORDERS o, ORDERLINE l WHERE l.orderid = o.orderid and l.albumid = a.albumid and ROWNUM <= 10 ORDER BY l.quantitiesordered DESC;

-- Q10 SELECT a.artistname, sum(playcount) as totalplay FROM PLAYHISTORY p, SONG s, WRITES w, ARTIST a WHERE p.songid = s.songid and s.songid = w.songid and w.artistid = a.artistid and p.playdate BETWEEN '01-Jan-10' and '31-Dec-10' group by a.artistname;

-- Q11 SELECT p.deliveryoption, AVG(l.quantitiesordered * a.albumprice) "Average" FROM PHYSICALORDER p , ORDERLINE l , ALBUM a WHERE p.orderid = l.orderid and l.albumid = a.albumid GROUP BY p.deliveryoption;

The assignment 4 greatly depends on the quality of the data generated by your code.

For this assignment, you are required to submit two files one SQL script and one document. Your script must contain all queries that answer each question and must be executable without generating any errors to obtain a perfect score.Add your script for the assignment 4 to the end of the assignment 3 script, so that one combined script creates data as well as generate result tables altogether.

If your script fails to run on my computer, I will consider partial credits based on your answer. In such cases, you won't be able to get the full score.So please run your script on your computer before submission to make sure everything is running correctly.

The second deliverable is a word document that should include theresulting tables from your queries. You would need at least one table for each question. You can simply copy and paste the result tables from a client program to the document.Since you populated the database yourself, it is possible that some of your results might no returned rows at all. In that case, acknowledge that no rows were returned. Deliverables

Two files to be submitted one script (.txt) and one document (.pdf).

Run your script on your computer before submission