2) Find a name of product ordered and order date of all products ordered by a customer with customer code SAVEA and such that quantity of product is...

1. 2) Find a name of product ordered and order date of all products ordered by a customer with customer code SAVEA and such that quantity of product is 10.
2. (3) Find the total number of cities the customers who submitted at least one order in1996 or in 1997 or in 1998 come from.
3. (4) Find a description of category such that at least one product from the category has unit price greater than 100.
4. (5) Find the names of products that have not been ordered yet. A hint is to use OUTER JOIN operation.
5. (6) Find the first and last name of a direct manager of employee whose first name isNancy and last name is Davolio.

CREATE TABLE PRODUCT

(

PRODUCT_NAME  VARCHAR(40)  NOT NULL,

SUPPLIER_NAME  VARCHAR(40)  NOT NULL,

CATEGORY_NAME  VARCHAR(30)  NOT NULL,

QUANTITY_PER_UNIT  VARCHAR(20),

UNIT_PRICE     DECIMAL(10,2)  NOT NULL  DEFAULT 0,

UNITS_IN_STOCK  DECIMAL(9)  NOT NULL  DEFAULT 0,

UNITS_ON_ORDER  DECIMAL(9)  NOT NULL  DEFAULT 0,

REORDER_LEVEL  DECIMAL(9)  NOT NULL  DEFAULT 0,

DISCONTINUED  CHAR(1)     NOT NULL  DEFAULT 'N',

CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),

CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),

CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),

CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),

CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),

CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),

CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),

CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))

);

/* COMMENT ON COLUMN PRODUCT.SUPPLIER_ IS 'Same entry as in Suppliers table.';

COMMENT ON COLUMN PRODUCT.CATEGORY_ID IS 'Same entry as in Categories table.';

COMMENT ON COLUMN PRODUCT.QUANTITY_PER_UNIT IS '(e.g., 24-count case, 1-liter bottle).';

COMMENT ON COLUMN PRODUCT.REORDER_LEVEL IS 'Minimum units to maintain in stock.';

COMMENT ON COLUMN PRODUCT.DISCONTINUED IS 'Yes means item is no longer available.';           */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE ORDERS

(

ORDER_ID     DECIMAL(9)  NOT NULL,

CUSTOMER_CODE  VARCHAR(5)  NOT NULL,

EMPLOYEE_ID  DECIMAL(9)  NOT NULL,

ORDER_DATE     DATE    NOT NULL,

REQUIRED_DATE  DATE,

SHIPPED_DATE  DATE,

SHIP_VIA     VARCHAR(40),

FREIGHT     DECIMAL(10,2)        DEFAULT 0,

SHIP_NAME     VARCHAR(40),

SHIP_ADDRESS  VARCHAR(60),

SHIP_CITY     VARCHAR(15),

SHIP_REGION  VARCHAR(15),

SHIP_POSTAL_CODE  VARCHAR(10),

SHIP_COUNTRY  VARCHAR(15),

CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),

CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),

CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),

CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)

);

/* COMMENT ON COLUMN ORDERS.ORDER_ID IS 'Unique order number.';

COMMENT ON COLUMN ORDERS.CUSTOMER_CODE IS 'Same entry as in Customers table.';

COMMENT ON COLUMN ORDERS.EMPLOYEE_ID IS 'Same entry as in Employees table.';

COMMENT ON COLUMN ORDERS.SHIP_VIA IS 'Same as Company name in Shippers table.';

COMMENT ON COLUMN ORDERS.SHIP_NAME IS 'Name of person or company to receive the shipment.';

COMMENT ON COLUMN ORDERS.SHIP_ADDRESS IS 'Street address only -- no post-office box allowed.';

COMMENT ON COLUMN ORDERS.SHIP_REGION IS 'State or province.';                 */

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE ORDER_DETAIL

(

ORDER_ID     DECIMAL(9)  NOT NULL,

PRODUCT_NAME  VARCHAR(40)  NOT NULL,

UNIT_PRICE     DECIMAL(10,2)  NOT NULL  DEFAULT 0,

QUANTITY     DECIMAL(9)  NOT NULL   DEFAULT 1 ,

DISCOUNT     DECIMAL(4,2)  NOT NULL   DEFAULT 0,

CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),

CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),

CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME) REFERENCES PRODUCT (PRODUCT_NAME),

CONSTRAINT CK_ORDER_DETAIL_UNIT_PRICE CHECK (UNIT_PRICE >= 0),

CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),

CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1)