CREATE TABLE DELIVERIES (
DATETIME TIMESTAMP NOT NULL,
CUSTOMER_ID INT NOT NULL,
DELIVERY_SERVICE VARCHAR(20) NOT NULL,
COST NUMERIC(8,2), NOT NULL
INVOICE_NO CHAR(8) NOT NULL,
DRIVER VARCHAR(20),
PRIMARY KEY(DATETIME, CUSTOMER_ID),
FOREIGN KEY(DELIVERY_SERVICE) REFERENCES
DELIVERY_SERVICES(NAME)
);
CREATE TABLE DELIVERY_SERVICES(
NAME VARCHAR(20) NOT NULL,
PHONE CHAR(12) NOT NULL,
ADDR VARCHAR(30) NOT NULL,
ST CHAR(2) NOT NULL,
ZIP CHAR(5) NOT NULL,
CONTACT VARCHAR(20),
PRIMARY KEY(NAME)
);
8.5 Here are the schemas for a few more of the tables in one implementation of the antique store
database:
Item
Sale
Line Item
Invocie_No | Item_ID | Unit_Price | Quantity | Line_Item_Price
Invoice_No | Customer_ID | Date
Item_ID | Type | Description | Cost | Consignment_Seller_ID | Price_Sold
Name | Phone | Addr | City | St | ZIP | Contact
Date/Time | Customer_ID | Delivery_Service | Cost | Invoice_No | Driver
ANSWERS TO REVIEW QUESTIONS 209
210 ANSWERS TO REVIEW QUESTIONS
Consignment_Seller
Write SQL queries to report the following information:
a Report all of the consignment sellers located in NY, and list them in order by their
consignment fee percentages.
SELECT name, fee_percent FROM CONSIGNMENT_SELLER
WHERE state = 'NY'
ORDER BY fee_percent;
b Find all of the items in the store that are on consignment from ???Parker Smith???.
Pages:
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538