1. Engineering
  2. Computer Science
  3. drop tables drop table request cascade constraints drop table...

Question: drop tables drop table request cascade constraints drop table...

Question details

-- drop tables

DROP TABLE REQUEST CASCADE CONSTRAINTS;
DROP TABLE FULLORDER CASCADE CONSTRAINTS;
DROP TABLE PRODUCT CASCADE CONSTRAINTS;
DROP TABLE CUSTOMER CASCADE CONSTRAINTS;

-- create and link tables

CREATE TABLE CUSTOMER
(
   CustomerID   INTEGER,
   Name       VARCHAR2(40),
   City       VARCHAR2(20),
   State       CHAR(2),
   Zip       CHAR(5),

   CONSTRAINT PK_CUSTOMER
       PRIMARY KEY (CustomerID)
);
CREATE TABLE PRODUCT
(
   ProductID   INTEGER,
   Description   VARCHAR2(30),
   Material   VARCHAR2(20),
   Price       NUMBER(5,2),

   CONSTRAINT PK_PRODUCT
       PRIMARY KEY (ProductID)
);
CREATE TABLE FULLORDER
(   OrderID       INTEGER,
   OrderDate   DATE,
   CustomerID   INTEGER,

   CONSTRAINT PK_FULLORDER
       PRIMARY KEY (OrderID),

   CONSTRAINT FK_FULLORDER_CUSTOMER
       FOREIGN KEY (CustomerID)
       REFERENCES CUSTOMER(CustomerID)
);
CREATE TABLE REQUEST
(
   OrderID       INTEGER,
   ProductID   INTEGER,
   Quantity   INTEGER,

   CONSTRAINT PK_REQUEST
       PRIMARY KEY (OrderID, ProductID),

   CONSTRAINT FK_REQUEST_FULLORDER
       FOREIGN KEY (OrderID)
       REFERENCES FULLORDER(OrderID),

   CONSTRAINT FK_REQUEST_PRODUCT
       FOREIGN KEY (ProductID)
       REFERENCES PRODUCT(ProductID)
);

-- populate tables

INSERT INTO CUSTOMER VALUES
   (1, 'Mannys Home Style', 'Newburgh', 'NY', '12550');
INSERT INTO CUSTOMER VALUES
   (2, 'Furniture-palooza', 'Hauppauge', 'NY', '11788');
INSERT INTO CUSTOMER VALUES
   (3, 'Finer Home Furniture', 'Hartford', 'CT', '06167');
INSERT INTO CUSTOMER VALUES
   (4, 'Midwest Antique Furnishings', 'Lansing', 'MI', '48922');
INSERT INTO CUSTOMER VALUES
   (5, 'Californian Comfort', 'Irvine', 'CA', '92612');
INSERT INTO CUSTOMER VALUES
   (6, 'Unpainted Arizona', 'Sedona', 'AZ', '87507');
INSERT INTO CUSTOMER VALUES
   (7, 'Furthur Furnishings', 'San Francisco', 'CA', '94121');
INSERT INTO CUSTOMER VALUES
   (10, 'Coastal Casuals', 'Miami', 'FL', '33101');
INSERT INTO CUSTOMER VALUES
   (11, 'Mountain Forest Home', 'Oregon City', 'OR', '97045');
INSERT INTO CUSTOMER VALUES
   (12, 'Classic Home', 'Albany', 'NY', '12209');
INSERT INTO CUSTOMER VALUES
   (13, 'East Coast Furnishings', 'Nutley', 'NJ', '07110');
INSERT INTO CUSTOMER VALUES
   (14, 'Home Impressions', 'Fort Myers', 'FL', '33901');
INSERT INTO CUSTOMER VALUES
   (15, 'Mountain Furniture', 'Boulder', 'CO', '80514');
INSERT INTO CUSTOMER VALUES
   (16, 'Vintage Furniture', 'Ann Arbor', 'MI', '48103');
INSERT INTO CUSTOMER VALUES
   (17, 'Home Design Classics', 'Simsbury', 'CT', '06070');
INSERT INTO CUSTOMER VALUES
   (20, 'M and H Casual Furniture and Stuff', 'Seattle', 'WA', '98105');
INSERT INTO CUSTOMER VALUES
   (21, 'Seminole Interiors', 'Seminole', 'FL', '34646');
INSERT INTO PRODUCT VALUES
   (101, 'Dining Table', 'Oak', 500.00);
INSERT INTO PRODUCT VALUES
   (102, 'Dining Table', 'Maple', 475.00);
INSERT INTO PRODUCT VALUES  
   (103, 'Dining Table', 'Maple', 500.00);
INSERT INTO PRODUCT VALUES
   (104, 'Dining Table', 'Oak', 250.00);
INSERT INTO PRODUCT VALUES
   (105, 'Wardrobe', 'Oak', 300.00);
INSERT INTO PRODUCT VALUES
   (106, 'Wardrobe', 'Pine', 375.00);
INSERT INTO PRODUCT VALUES
   (107, 'Wardrobe', 'Oak', 425.00);
INSERT INTO PRODUCT VALUES
   (208, 'Entertainment Center', 'Cherry', 550.00);
INSERT INTO PRODUCT VALUES
   (209, 'Entertainment Center', 'Pine', 350.00);
INSERT INTO FULLORDER VALUES
   (1001, DATE '2017-12-30', 10);
INSERT INTO FULLORDER VALUES
   (1002, DATE '2017-12-30', 5);
INSERT INTO FULLORDER VALUES
   (1003, DATE '2017-12-30', 12);
INSERT INTO FULLORDER VALUES
   (1004, DATE '2018-01-15', 13);
INSERT INTO FULLORDER VALUES
   (1005, DATE '2018-02-03', 20);
INSERT INTO FULLORDER VALUES
   (1006, DATE '2018-02-03', 6);
INSERT INTO FULLORDER VALUES
   (1007, DATE '2018-03-10', 13);
INSERT INTO FULLORDER VALUES
   (1008, DATE '2018-08-01', 20);
INSERT INTO FULLORDER VALUES
   (1009, DATE '2018-08-01', 13);
INSERT INTO FULLORDER VALUES
   (1010, DATE '2018-09-30', 6);
INSERT INTO FULLORDER VALUES
   (1011, DATE '2018-09-30', 2);
INSERT INTO FULLORDER VALUES
   (1012, DATE '2019-01-15', 10);
INSERT INTO FULLORDER VALUES
   (1013, DATE '2019-01-15', 1);
INSERT INTO FULLORDER VALUES
   (1014, DATE '2019-01-15', 7);
INSERT INTO FULLORDER VALUES
   (1015, DATE '2019-01-15', 20);
INSERT INTO FULLORDER VALUES
   (1016, DATE '2018-01-20', 7);
INSERT INTO FULLORDER VALUES
   (1017, DATE '2018-01-23', 20);
INSERT INTO REQUEST VALUES
   (1001, 106, 2);
INSERT INTO REQUEST VALUES
   (1001, 102, 2);
INSERT INTO REQUEST VALUES
   (1001, 104, 1);
INSERT INTO REQUEST VALUES
   (1002, 103, 3);
INSERT INTO REQUEST VALUES
   (1003, 103, 4);
INSERT INTO REQUEST VALUES
   (1004, 208, 2);
INSERT INTO REQUEST VALUES
   (1004, 104, 1);
INSERT INTO REQUEST VALUES
   (1005, 104, 4);
INSERT INTO REQUEST VALUES
   (1006, 104, 2);
INSERT INTO REQUEST VALUES
   (1006, 103, 2);
INSERT INTO REQUEST VALUES
   (1006, 102, 2);
INSERT INTO REQUEST VALUES
   (1007, 106, 4);
INSERT INTO REQUEST VALUES
   (1007, 102, 5);
INSERT INTO REQUEST VALUES
   (1008, 103, 1);
INSERT INTO REQUEST VALUES
   (1008, 208, 1);
INSERT INTO REQUEST VALUES
   (1010, 104, 5);
INSERT INTO REQUEST VALUES
   (1010, 107, 4);
INSERT INTO REQUEST VALUES
   (1011, 208, 5);
INSERT INTO REQUEST VALUES
   (1012, 106, 1);
INSERT INTO REQUEST VALUES
   (1013, 106, 2);
INSERT INTO REQUEST VALUES
   (1013, 209, 1);
INSERT INTO REQUEST VALUES
   (1014, 101, 1);
INSERT INTO REQUEST VALUES
   (1015, 102, 1);
INSERT INTO REQUEST VALUES
   (1016, 103, 1);
INSERT INTO REQUEST VALUES
   (1017, 104, 2);
COMMIT;

-- display tables

SELECT * FROM CUSTOMER;
SELECT * FROM PRODUCT;
SELECT * FROM FULLORDER;
SELECT * FROM REQUEST

For each order, give the order ID and the total number of items requested in that order (e.g., for an order that requested 4 of one product, 3 of a second product, and 1 of a third product, the total number of items reported for that order should be 8). Sort the orders from the one with the largest total number of items to the one with the smallest total number of items.

For each product, give the product ID and a count of how many different orders have requested it. (Don’t worry about the quantity of the product requested by each order, just count how many orders requested any number of that product.) Order the output from the products requested by the fewest different orders to the products requested by the most different orders. (Your output will not include products that have not been requested by any orders yet, and that is okay; to do that would require using more than one table.)

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution