Question

(1) Use a single SQL statement to create a relational table and to load into the table all information about the orders submitted in 1996 or in 1998 by the customers located in Paris or in London or in Madrid. Next, enforce the appropriate consistency constraints on the new table. (2) Create a new relational table to store information about the company names of all suppliers and the total number of products supplied by each supplier. Enforce, the appropriate consistency constraints on the new table. Next, copy into the new table information about the company names of all suppliers and the total number of products supplied by each supplier. (3) Add to a relational table EMPLOYEE information about the total number of orders handled by each employee. Note, that if an employee handled no orders then for such employee the total number of orders must be set to zero. Enforce the appropriate consistency constraints on a relational table EMPLOYEE. (4) Delete from the database information about all suppliers located in USA. Information about all products supplied by the suppliers located in USA must remain in the database. You are not allowed to drop and/or to suspend any referential integrity constraints and you must modify one of NULL/NOT NULL consistency constraints. (5) Insert into a file solution1.sql implementation of the following query as SELECT statement with WITH clause. Find all orders such that a value of each order is greater than an average value of all orders submitted so far. List in each line of output an order identifier, a total value of an order, and an average value of all orders. The results must be sorted in the descending order of a total value of each order. A total value of an order must be computed as the summation of unit price * quantity over all items included in the order. The query must be implemented as a sequence of subquery definitions following WITH keyword and ended with the final SELECT. (i) The first subquery definition must find a total value of each order together with an order identifier (attribute order_id). (ii) The second subquery definition must find an average value of all orders. (iii) The third subquery definition must find all orders and their values such that value of each order is greater than an average value of all orders. (iv) The final SELECT statement must extend the results of subquery definition (iii) with a column average_value that contains an average value of all orders and it must sort the results in descending order of a total value of each order.

Answer 1

1. CREATE TABLE ORDER (ORDER_ID VARCHAR(10) NOT NULL,

CUSTOMER_ID VARCHAR(7) NOT NULL,

ORDER_DATE DATE NOT NULL,

COUNTRY VARCHAR(50) NOT NULL,

CITY VARCHAR(25) NOT NULL,

PRIMARY KEY(ORDER_ID),

CHECK (ORDER_DATE IN ('%1996','%1998') AND CITY IN ('PARIS','LONDON','MADRID')

);

  

LOAD DATA INFILE 'ORDERS.csv'

INTO TABLE ORDER

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

2. CREATE TABLE SUPPLIERS ( SUPPLIERS_NAME VARCHAR(10) NOT NULL'

SUPPLIER_ID INT(10) NOT NULL,

COMPANY_NAME VARCHAR(50) NOT NULL,

PRODUCTS_SUPPLIED INT(100),

COUNTY CHAR(15),

COUNTRY CHAR(10),

PRIMARY KEY (SUPPLIER_ID));

LOAD DATA INFILE 'SUPPLIER_RECORD.csv'

INTO TABLE SUPPLIERS

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

3. CREATE TABLE EMPLOYEES (
EMP_ID CHAR(9) NOT NULL,

SEX CHAR,
ORDERS_HANDLED INT(50) NOT NULL;
PRIMARY KEY (EMP_ID));

4. DELETE FROM SUPPLIERS WHERE COUNTRY=='USA';