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),

CONSTRAINT writes_songid_fk FOREIGN KEY (SongID)

REFERENCES SONG (SongID)

);

INSERT INTO WRITES

VALUES(4400000001, 2200000001);

INSERT INTO WRITES

VALUES(4400000002, 2200000008);

-- Add more records

CREATE TABLE BELONGSTO

(

SongID NUMBER(10),

GenreID NUMBER(4),

CONSTRAINT belongsto_songgenreid_pk PRIMARY KEY (SongID,

GenreID),

CONSTRAINT belongsto_songid_fk FOREIGN KEY (SongID)

REFERENCES SONG (SongID),

CONSTRAINT belongsto_genreid_fk FOREIGN KEY (GenreID)

REFERENCES GENRE (GenreID)

);

INSERT INTO BELONGSTO

VALUES (2200000001, 5501);

INSERT INTO BELONGSTO

VALUES (2200000008, 5503);

-- Add more records

[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. The assignment 4 greatly depends on the quality of the data generated by your code.

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?

Below is the answer to Assignment 4. Remember that these are just ones among many possible answers. Your queries might look different and yet produce correct results.

-- 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  
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 the resulting 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