DATA SCIENCE!

Following are the tables:

/* The Queen Anne Curiousity Shop Database Create Tables */

CREATE TABLE CUSTOMER(

CustomerID Int NOT NULL IDENTITY (1, 1),

LastName Char(25) NOT NULL,

FirstName Char(25) NOT NULL,

City Char(35) NULL,

Phone Char(12) NOT NULL,

CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID)

);

CREATE TABLE VENDOR(

VendorID Int NOT NULL IDENTITY (1, 1),

CompanyName Char(100) NULL,

ContactLastName Char(25) NOT NULL,

ContactFirstName Char(25) NOT NULL,

City Char(35) NULL,

Email VarChar(100) NULL,

CONSTRAINT VENDOR_PK PRIMARY KEY(VendorID)

);

CREATE TABLE ITEM(

ItemID Int NOT NULL IDENTITY (1,1),

ItemDescription VarChar(255) NOT NULL,

PurchaseDate DateTime NOT NULL,

ItemCost Numeric(9,2) NOT NULL,

VendorID Int NOT NULL,

CONSTRAINT ITEM_PK PRIMARY KEY (ItemID),

CONSTRAINT ITEM_VENDOR_FK FOREIGN KEY (VendorID)

REFERENCES VENDOR(VendorID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

CREATE TABLE SALE(

SaleID Int NOT NULL IDENTITY (1, 1),

CustomerID Int NOT NULL,

ItemID Int NOT NULL,

SaleDate DateTime NOT NULL,

SubTotal Numeric(15,2) NULL,

Tax Numeric(15,2) NULL,

Total Numeric(15,2) NULL,

CONSTRAINT SALE_PK PRIMARY KEY (SaleID),

CONSTRAINT SALE_CUSTOMER_FK FOREIGN KEY (CustomerID)

REFERENCES Customer(CustomerID)

ON UPDATE NO ACTION

ON DELETE NO ACTION,

CONSTRAINT SALE_ITEM_FK FOREIGN KEY(ItemID)

REFERENCES ITEM(ItemID)

ON UPDATE NO ACTION

ON DELETE NO ACTION

);

/********************************************************************************/

/* delete data in tables */

DELETE FROM SALE;

DELETE FROM ITEM;

DELETE FROM VENDOR;

DELETE FROM CUSTOMER;

/* delete tables */

DROP TABLE SALE;

DROP TABLE ITEM;

DROP TABLE VENDOR;

DROP TABLE CUSTOMER;

/* The Queen Anne Curiousity Shop [QACS] Database Data */

/***** CUSTOMER DATA ********************************************************/

INSERT INTO CUSTOMER VALUES(

'Shire', 'Robert', 'Seattle', '206-524-2433');

INSERT INTO CUSTOMER VALUES(

'Goodyear', 'Katherine', 'Seattle', '206-524-3544');

INSERT INTO CUSTOMER VALUES(

'Bancroft', 'Chris','Bellevue','425-635-9788');

INSERT INTO CUSTOMER VALUES(

'Griffith', 'John', 'Seattle', '206-524-4655');

INSERT INTO CUSTOMER VALUES(

'Tierney', 'Doris', 'Bellevue', '425-635-8677');

INSERT INTO CUSTOMER VALUES(

'Anderson', 'Donna', 'Mt. Vernon', '360-538-7566');

INSERT INTO CUSTOMER VALUES(

'Svane', 'Jack', 'Seattle', '206-524-5766');

INSERT INTO CUSTOMER VALUES(

'Walsh', 'Denesha', 'Redmond', '425-635-7566');

INSERT INTO CUSTOMER VALUES(

'Enquist', 'Craig', 'Bellingham', '360-538-6455');

INSERT INTO CUSTOMER VALUES(

'Anderson', 'Rose', 'Seattle','206-524-6877');

/***** VENDOR DATA **********************************************************/

INSERT INTO VENDOR VALUES(

'Linens and Things', 'Huntington', 'Anne', 'Seattle', 'LAT@business.com');

INSERT INTO VENDOR VALUES(

'European Specialties', 'Tadema', 'Ken', 'Seattle', 'ES@business.com');

INSERT INTO VENDOR VALUES(

'Lamps and Lighting', 'Swanson', 'Sally', 'Seattle', 'LAL@business.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, City, Email)

VALUES('Lee', 'Andrew', 'Kirkland', 'Andrew.Lee@somewhere.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, City, Email)

VALUES('Harrison', 'Denise', 'Kirkland', 'Denise.Harrison@somewhere.com');

INSERT INTO VENDOR VALUES(

'New York Brokerage', 'Smith', 'Mark', 'Seattle', 'NYB@business.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, City, Email)

VALUES('Walsh', 'Denesha', 'Redmond', 'Denesha.Walsh@somewhere.com');

INSERT INTO VENDOR

(ContactLastName, ContactFirstName, City, Email)

VALUES('Bancroft', 'Chris', 'Bellevue', 'Chris.Bancroft@somewhere.com');

INSERT INTO VENDOR VALUES(

'Specialty Antiques', 'Nelson', 'Fred', 'San Francisco', 'SA@business.com');

INSERT INTO VENDOR VALUES(

'General Antiques', 'Garner', 'Patty', 'San Francisco', 'GA@business.com');

/***** ITEM DATA ************************************************************/

INSERT INTO ITEM VALUES(

'Antique Desk', '07-Nov-12', 1800.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk Chair', '10-Nov-12', 300.00, 4);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '14-Nov-12', 600.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '14-Nov-12', 30.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '14-Nov-12', 27.00, 1);

INSERT INTO ITEM VALUES(

'Desk Lamp', '14-Nov-12', 150.00, 3);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '14-Nov-12', 450.00, 1);

INSERT INTO ITEM VALUES(

'Book Shelf', '21-Nov-12', 150.00, 5);

INSERT INTO ITEM VALUES(

'Antique Chair', '21-Nov-12', 750.00, 6);

INSERT INTO ITEM VALUES(

'Antique Chair', '21-Nov-12', 1050.00, 6);

INSERT INTO ITEM VALUES(

'Antique Candle Holders', '28-Nov-12', 210.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk', '05-Jan-13', 1920.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk', '05-Jan-13', 2100.00, 2);

INSERT INTO ITEM VALUES(

'Antique Desk Chair', '06-Jan-13', 285.00, 9);

INSERT INTO ITEM VALUES(

'Antique Desk Chair', '06-Jan-13', 339.00, 9);

INSERT INTO ITEM VALUES(

'Desk Lamp', '06-Jan-13', 150.00, 10);

INSERT INTO ITEM VALUES(

'Desk Lamp', '06-Jan-13', 150.00, 10);

INSERT INTO ITEM VALUES(

'Desk Lamp', '06-Jan-13', 144.00, 3);

INSERT INTO ITEM VALUES(

'Antique Dining Table', '10-Jan-13', 3000.00, 7);

INSERT INTO ITEM VALUES(

'Antique Sideboard', '11-Jan-13', 2700.00, 8);

INSERT INTO ITEM VALUES(

'Dining Table Chairs', '11-Jan-13', 5100.00, 9);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '12-Jan-13', 450.00, 1);

INSERT INTO ITEM VALUES(

'Dining Table Linens', '12-Jan-13', 480.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '17-Jan-13', 30.00, 1);

INSERT INTO ITEM VALUES(

'Candles', '17-Jan-13', 36.00, 1);

/***** SALE DATA ************************************************************/

INSERT INTO SALE VALUES(1, 2, '14-Dec-12', 3500.00, 290.50, 3790.50);

INSERT INTO SALE VALUES(2, 3, '15-Dec-12', 1000.00, 83.00, 1083.00);

INSERT INTO SALE VALUES(3, 1, '15-Dec-12', 50.00, 4.15, 54.15);

INSERT INTO SALE VALUES(4, 4, '23-Dec-12', 45.00, 3.74, 48.74);

INSERT INTO SALE VALUES(1, 5, '05-Jan-13', 250.00, 20.75, 270.75);

INSERT INTO SALE VALUES(5, 18, '10-Jan-13', 750.00, 62.25, 812.25);

INSERT INTO SALE VALUES(6, 11, '12-Jan-13', 250.00, 20.75, 270.75);

INSERT INTO SALE VALUES(2, 15, '15-Jan-13', 3000.00, 249.00, 3249.00);

INSERT INTO SALE VALUES(5, 20, '25-Jan-13', 350.00, 29.05, 379.05);

INSERT INTO SALE VALUES(7, 9, '04-Feb-13', 14250.00, 1182.75, 15432.75);

INSERT INTO SALE VALUES(8, 10, '04-Feb-13', 250.00, 20.75, 270.75);

INSERT INTO SALE VALUES(5, 12, '07-Feb-13', 50.00, 4.15, 54.15);

INSERT INTO SALE VALUES(9, 21, '07-Feb-13', 4500.00, 373.50, 4873.50);

INSERT INTO SALE VALUES(10, 6, '11-Feb-13', 3675.00, 305.03, 3980.03);

INSERT INTO SALE VALUES(2, 8, '11-Feb-13', 800.00, 66.40, 866.40);

IST210 Spring 2017 Section 01 Assignment 4: SQL 2-3 Problem Assume that The Queen Anne Curiosity Shop designs a database with the following tables. CUSTOMER (CustomerID, LastName,FirstName,City,Phone) VENDOR (VendorID.CompanyName,ContactLastName,ContactFirstName,City,Email) ITEM (ItemID, ItemDescription, PurchaseDate,ItemCost, VendorID SALE (SaleID, CustomerID,ItemID,SaleDate, SubTotal,Tax,Total) The referential integrity constraints are VendorID in ITEM must exist in VendorID in VENDOR o CustomerID in SALE must exist in CustomerID in CUSTOMER ItemID in SALE must exist in ItemID in ITEM Prepare tables and data: (5 points) 1. Download Assignment3-SQLScripts.zip 2. Delete the tables you have created in your database using assign-3-delete-tables.sql 3. Create tables using assign-3-create-tables.sql 4. Insert data using assign-3-insert-data.sql. Then, write SQL statements and answer questions for this database as follows. Answer the questions in order. Please use comment /*** QuestionXbefore each query to separate your answers (X should be question number). (5 points for each question) 1. Show all customer information (Note: When we say show all customers, we mean show all columns for customers. Same for the questions below) 2. Show ItemID and ItemDescription for all items that cost more than S1000 3. Count how many items cost more than $1000 Note: using COUNT (*) . Count how many distinct ItemDescription in ITEM table Note: using DISTINCT 5. Show ItemDescription and ItemCost for the item with maximum cost Note: using subquery 6. Show items with ItemDescription containing word "Table and the ItemCost is more than $1000 7. Show all customers NOT from city "Se" or "Bellevue" Note: use NOT IN 8. Show all the items in descending order of their ItemCost 9. Show all the vendors with CompanyName as NULL 10. Show the sum of Total in SALE 11. For ITEM table, group by VendorID. Show VendorID and corresponding sum of ItemCost 12. Add one constraint to question above: only show the vendors with sum of ItemCost more than $2000