I need help with a sql task. Have attached the sample database needed. please read the task before attempting. DATA SAMP

[answerhappygod]

I need help with a sql task. Have attached the sample database
needed. please read the task before attempting.

I Need Help With A Sql Task Have Attached The Sample Database Needed Please Read The Task Before Attempting Data Samp 1 (184.33 KiB) Viewed 69 times

DATA SAMPLE

I Need Help With A Sql Task Have Attached The Sample Database Needed Please Read The Task Before Attempting Data Samp 2 (581.66 KiB) Viewed 69 times

Download a file solution3.sql and insert into the file the implementations of the following queries as SELECT statements of SQL. Your implementation must directly follow a comment with a specification of a subtask. The queries listed below must be implemented as nested SELECT statements with IN/NOT IN set membership operation. (1) Find the product number, price and manufacturer that have no keyword. Hint: Consider the tables Product and Keyword. (2) Find the customer number and full name (fname, Iname) for all customers that have created baskets of products. Hint: Consider the tables Customer and Pbasket. The queries (3) and (4) listed below must be implemented as nested queries with EXISTS/NOT EXISTS clauses. (3) Find the product number, price, and manufacturer of all the products that have been ranked. Hint: Consider the tables Product and Cevaluation. (4) Find the customer number and full name (fname, lname) of all customers that have not evaluated any product. Hint: Consider the tables Customer and Cevaluation. The queries listed below must be implemented with a WITH clause. (5) Find the product number, price, and manufacturer of all products that have the maximum number of keywords. Hint: Count the total number of keywords for each product. Find the maximum value. Then compare the number of keywords with the maximum value to find the expected product number. Use the product number found for the product details. (6) Find the customer number and full name (fname, Iname) of the customers that have the biggest product basket. Note: The biggest product basket is a basket that contains the maximum total number of products. Hint: Count the total number of products for each basket. Find the maximum value. Then compare the product number of baskets with the maximum value to find the expected basket. Use the basket found before to find the customer. To create a report from the processing of SELECT statements, open a Terminal window, change the directory to the working directory, and start the command line interface mysql in the following way: mysql -u csit115 - -v -c

Database Server Tools Scripting Help 360 Query 3 x dbcreate X dbcount X dbload X ZQ 3 Limit to 1000 rows 1 /* */ 2 /* SHOP */ */ 3 /* 4 Ір phone email 5. CREATE TABLE Product 6 pNumber DECIMAL (8) NOT NULL, /* Product number */ 7 price DECIMAL (5,2) NOT NULL, /* Price per item */ 8 manufacturer VARCHAR(30), /* Manufacturer name / */ 9 9 pcomment VARCHAR(30), / /* Brief comments */ 10 CONSTRAINT Product_PK PRIMARY KEY (pNumber) 11 ); 12 13 CREATE TABLE Keyword 14 pNumber DECIMAL(8) ) NOT NULL, , /* product number */ 15 kword VARCHAR(30) NOT NULL, /* Keyword 16 CONSTRAINT Keyword PK PRIMARY KEY (pNumber, kword), 17 CONSTRAINT Keyword_FK1 FOREIGN KEY(pNumber) REFERENCES Product (pNumber) 18 ); 19 20. CREATE TABLE Customer 21 cNumber DECIMAL (10) NOT NULL, /* Customer number 22 fname VARCHAR(30) NOT NULL, /* First name */ 23 lname VARCHAR(30) NOT NULL, /* Last name */ 24 VARCHAR(20) ) NOT NULL, /* Phone number */ 25 VARCHAR(50) NOT NULL, /* E-mail address */ 26 fax DECIMAL (20) /* Fax number */ 27 country VARCHAR(30) NOT NULL, /* Country part of address */ 28 state VARCHAR(30), /* State part of address / */ 29 city VARCHAR(30) NOT NULL, /* city part of address */ 30 pcode VARCHAR(10), /* Post code part of address */ 31 street VARCHAR(30) NOT NULL, /* Street part of address */ */ 32 houseNumber DECIMAL (6) NOT NULL, /* House number part of adress */ 33 flatNumber DECIMAL(6), () /* Flat number part of address */ 34 CONSTRAINT Customer_PK PRIMARY KEY (CNumber) 35 ); 36 37 38 CREATE TABLE Pbasket 39 whencreated DATE NOT NULL /* Transferred from Abasket */ 40 whenfinalised TIMESTAMP NOT NULL, /* Date time when finalised */ 41 ccard DECIMAL (16) NOT NULL, /* Credit card used 42 cNumber DECIMAL (10) NOT NULL, /* Customer number */ 43 CONSTRAINT Pbasket PK PRIMARY KEY (whenfinalised), 44 CONSTRAINT PBasket_FK1 FOREIGN KEY(CNumber) REFERENCES Customer (cNumber) 45 ); 46 47

May 9 15:40 MySQL Workbench Database Server Tools Scripting Help Query 3 * dbcreate X dbcount X dbload * 7 27 28 29 30 90 XS Limit to 1000 rows country VARCHAR(30) NOT NULL, /* Country part of address */ state VARCHAR(30) /* State part of address */ city VARCHAR(30) NOT NULL, /* City part of address */ pcode VARCHAR(10), /* Post code part of address */ street VARCHAR(30) NOT NULL, /* Street part of address */ houseNumber DECIMAL(6) NOT NULL, /* House number part of adress */ flatNumber DECIMAL(6), /* Flat number part of address */ CONSTRAINT Customer_PK PRIMARY KEY (CNumber) ); 31 32 33 34 35 36 37 38. CREATE TABLE Pbasket 39 whencreated DATE NOT NULL, /* Transferred from Abasket */ 40 whenfinalised TIMESTAMP NOT NULL, /* Date time when finalised */ 41 ccard DECIMAL (16) NOT NULL, /* Credit card used */ 42 cNumber DECIMAL (10) NOT NULL, /* Customer number 43 CONSTRAINT Pbasket_PK PRIMARY KEY (whenfinalised), 44 CONSTRAINT PBasket FKI FOREIGN KEY (Number) REFERENCES Customer (cNumber) 45 ); */ 46 47 48. CREATE TABLE PPC 49 whenfinalised TIMESTAMP NOT NULL, * /* Date time when finalised */ 50 pNumber DECIMAL(8) NOT NULL, /* Product number */ 51 CONSTRAINT PP_PK PRIMARY KEY(whenfinalised, pNumber), 52 CONSTRAINT PP FK1 FOREIGN KEY(whenfinalised) 53 REFERENCES Pbasket (whenfinalised), 54 CONSTRAINT PP_FK2 FOREIGN KEY (Number) REFERENCES Product (pNumber) 55 ); 56 57 58. CREATE TABLE Cevaluation 59 CNumber DECIMAL (10) NOT NULL, /* Customer number */ 60 etext VARCHAR(50) NOT NULL, /* Text of evaluation */ 61 erank DECIMAL (1) NOT NULL, /* Rank provided by customer */ 62 pNumber DECIMAL (8) NOT NULL, /* Product number */ 63 CONSTRAINT Cevaluation_PK PRIMARY KEY (CNumber, pNumber), 64 CONSTRAINT Cevaluation_FK1 FOREIGN KEY (CNumber) REFERENCES Customer (CNumber), 65 CONSTRAINT Cevaluation_FK2 FOREIGN KEY (pNumber) REFERENCES Product (pNumber), 66 CONSTRAINT Cevaluation_CHK CHECK( erank IN (0, 1, 2, 3, 4, 5)) 67 ); 68 69 70 71 72 /* */ 73