Question

CREATE TABLE Orders
(Order# NUMBER(4),
Customer# NUMBER(4),
OrderDate DATE NOT NULL,
ShipDate DATE,
ShipStreet VARCHAR2(18),
ShipCity VARCHAR2(15),
ShipState VARCHAR2(2),
ShipZip VARCHAR2(5),
ShipCost NUMBER(4,2),
CONSTRAINT orders_order#_pk PRIMARY KEY(order#),
CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#)
REFERENCES customers(customer#));

INSERT INTO ORDERS
VALUES (1000,1005,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00);
INSERT INTO ORDERS
VALUES (1001,1010,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', 3.00);
INSERT INTO ORDERS
VALUES (1002,1011,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'58 TILA CIRCLE', 'CHICAGO', 'IL', '60605', 3.00);
INSERT INTO ORDERS
VALUES (1003,1001,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'958 MAGNOLIA LANE', 'EASTPOINT', 'FL', '32328', 4.00);
INSERT INTO ORDERS
VALUES (1004,1020,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'),'561 ROUNDABOUT WAY', 'TRENTON', 'NJ', '08601', NULL);
INSERT INTO ORDERS
VALUES (1005,1018,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'), '1008 GRAND AVENUE', 'MACON', 'GA', '31206', 2.00);
INSERT INTO ORDERS
VALUES (1006,1003,TO_DATE('01-APR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'558A CAPITOL HWY.', 'TALLAHASSEE', 'FL', '32307', 2.00);
INSERT INTO ORDERS
VALUES (1007,1007,TO_DATE('02-APR-09','DD-MON-YY'),TO_DATE('04-APR-09','DD-MON-YY'), '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 7.00);
INSERT INTO ORDERS
VALUES (1008,1004,TO_DATE('02-APR-09','DD-MON-YY'),TO_DATE('03-APR-09','DD-MON-YY'), '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', 3.00);
INSERT INTO ORDERS
VALUES (1009,1005,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'),'9 LIGHTENING RD.', 'SEATTLE', 'WA', '98110', NULL);
INSERT INTO ORDERS
VALUES (1010,1019,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('04-APR-09','DD-MON-YY'),'384 WRONG WAY HOME', 'MORRISTOWN', 'NJ', '07960', 2.00);
INSERT INTO ORDERS
VALUES (1011,1010,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'), '102 WEST LAFAYETTE', 'ATLANTA', 'GA', '30311', 2.00);
INSERT INTO ORDERS
VALUES (1012,1017,TO_DATE('03-APR-09','DD-MON-YY'),NULL,'1295 WINDY AVENUE', 'KALMAZOO', 'MI', '49002', 6.00);
INSERT INTO ORDERS
VALUES (1013,1014,TO_DATE('03-APR-09','DD-MON-YY'),TO_DATE('04-APR-09','DD-MON-YY'),'7618 MOUNTAIN RD.', 'CODY', 'WY', '82414', 2.00);
INSERT INTO ORDERS
VALUES (1014,1007,TO_DATE('04-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'), '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 3.00);
INSERT INTO ORDERS
VALUES (1015,1020,TO_DATE('04-APR-09','DD-MON-YY'),NULL,'557 GLITTER ST.', 'TRENTON', 'NJ', '08606', 2.00);
INSERT INTO ORDERS
VALUES (1016,1003,TO_DATE('04-APR-09','DD-MON-YY'),NULL,'9901 SEMINOLE WAY', 'TALLAHASSEE', 'FL', '32307', 2.00);
INSERT INTO ORDERS
VALUES (1017,1015,TO_DATE('04-APR-09','DD-MON-YY'),TO_DATE('05-APR-09','DD-MON-YY'),'887 HOT ASPHALT ST', 'MIAMI', 'FL', '33112', 3.00);
INSERT INTO ORDERS
VALUES (1018,1001,TO_DATE('05-APR-09','DD-MON-YY'),NULL,'95812 HIGHWAY 98', 'EASTPOINT', 'FL', '32328', NULL);
INSERT INTO ORDERS
VALUES (1019,1018,TO_DATE('05-APR-09','DD-MON-YY'),NULL, '1008 GRAND AVENUE', 'MACON', 'GA', '31206', 2.00);
INSERT INTO ORDERS
VALUES (1020,1008,TO_DATE('05-APR-09','DD-MON-YY'),NULL,'195 JAMISON LANE', 'CHEYENNE', 'WY', '82003', 2.00);

CREATE TABLE Customers
(Customer# NUMBER(4),
LastName VARCHAR2(10) NOT NULL,
FirstName VARCHAR2(10) NOT NULL,
Address VARCHAR2(20),
City VARCHAR2(12),
State VARCHAR2(2),
Zip VARCHAR2(5),
Referred NUMBER(4),
Region CHAR(2),
Email VARCHAR2(30),
CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#),
CONSTRAINT customers_region_ck
CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')) );

INSERT INTO CUSTOMERS
VALUES (1001, 'MORALES', 'BONITA', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'SE', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1002, 'THOMPSON', 'RYAN', 'P.O. BOX 9835', 'SANTA MONICA', 'CA', '90404', NULL, 'W', NULL);
INSERT INTO CUSTOMERS
VALUES (1003, 'SMITH', 'LEILA', 'P.O. BOX 66', 'TALLAHASSEE', 'FL', '32306', NULL, 'SE', NULL);
INSERT INTO CUSTOMERS
VALUES (1004, 'PIERSON', 'THOMAS', '69821 SOUTH AVENUE', 'BOISE', 'ID', '83707', NULL, 'NW', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL, 'NW', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1006, 'CRUZ', 'MESHIA', '82 DIRT ROAD', 'ALBANY', 'NY', '12211', NULL, 'NE', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1007, 'GIANA', 'TAMMY', '9153 MAIN STREET', 'AUSTIN', 'TX', '78710', 1003, 'SW', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1008, 'JONES', 'KENNETH', 'P.O. BOX 137', 'CHEYENNE', 'WY', '82003', NULL, 'N', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1009, 'PEREZ', 'JORGE', 'P.O. BOX 8564', 'BURBANK', 'CA', '91510', 1003, 'W', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1010, 'LUCAS', 'JAKE', '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', NULL, 'SE', NULL);
INSERT INTO CUSTOMERS
VALUES (1011, 'MCGOVERN', 'REESE', 'P.O. BOX 18', 'CHICAGO', 'IL', '60606', NULL, 'N', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1012, 'MCKENZIE', 'WILLIAM', 'P.O. BOX 971', 'BOSTON', 'MA', '02110', NULL, 'NE', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1013, 'NGUYEN', 'NICHOLAS', '357 WHITE EAGLE AVE.', 'CLERMONT', 'FL', '34711', 1006, 'SE', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1014, 'LEE', 'JASMINE', 'P.O. BOX 2947', 'CODY', 'WY', '82414', NULL, 'N', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1015, 'SCHELL', 'STEVE', 'P.O. BOX 677', 'MIAMI', 'FL', '33111', NULL, 'SE', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1016, 'DAUM', 'MICHELL', '9851231 LONG ROAD', 'BURBANK', 'CA', '91508', 1010, 'W', NULL);
INSERT INTO CUSTOMERS
VALUES (1017, 'NELSON', 'BECCA', 'P.O. BOX 563', 'KALMAZOO', 'MI', '49006', NULL, 'N', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1018, 'MONTIASA', 'GREG', '1008 GRAND AVENUE', 'MACON', 'GA', '31206', NULL, 'SE', '[email protected]');
INSERT INTO CUSTOMERS
VALUES (1019, 'SMITH', 'JENNIFER', 'P.O. BOX 1151', 'MORRISTOWN', 'NJ', '07962', 1003, 'NE', NULL);
INSERT INTO CUSTOMERS
VALUES (1020, 'FALAH', 'KENNETH', 'P.O. BOX 335', 'TRENTON', 'NJ', '08607', NULL, 'NE', '[email protected]');

CREATE TABLE ORDERITEMS
( Order# NUMBER(4),
Item# NUMBER(2),
ISBN VARCHAR2(10),
Quantity NUMBER(3) NOT NULL,
PaidEach NUMBER(5,2) NOT NULL,
CONSTRAINT orderitems_pk PRIMARY KEY (order#, item#),
CONSTRAINT orderitems_order#_fk FOREIGN KEY (order#)
REFERENCES orders (order#) ,
CONSTRAINT orderitems_isbn_fk FOREIGN KEY (isbn)
REFERENCES books (isbn) ,
CONSTRAINT oderitems_quantity_ck CHECK (quantity > 0) );

INSERT INTO ORDERITEMS
VALUES (1000,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1001,1,'9247381001',1,31.95);
INSERT INTO ORDERITEMS
VALUES (1001,2,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1002,1,'8843172113',2,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1003,2,'1059831198',1,30.95);
INSERT INTO ORDERITEMS
VALUES (1003,3,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1004,1,'2491748320',2,85.45);
INSERT INTO ORDERITEMS
VALUES (1005,1,'2147428890',1,39.95);
INSERT INTO ORDERITEMS
VALUES (1006,1,'9959789321',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,1,'3957136468',3,72.15);
INSERT INTO ORDERITEMS
VALUES (1007,2,'9959789321',1,54.50);
INSERT INTO ORDERITEMS
VALUES (1007,3,'8117949391',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1007,4,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1008,1,'3437212490',2,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1009,2,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1010,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1011,1,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,1,'8117949391',1,8.95);
INSERT INTO ORDERITEMS
VALUES (1012,2,'1915762492',2,25.00);
INSERT INTO ORDERITEMS
VALUES (1012,3,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1012,4,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1013,1,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1014,1,'0401140733',2,22.00);
INSERT INTO ORDERITEMS
VALUES (1015,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1016,1,'2491748320',1,85.45);
INSERT INTO ORDERITEMS
VALUES (1017,1,'8117949391',2,8.95);
INSERT INTO ORDERITEMS
VALUES (1018,1,'3437212490',1,19.95);
INSERT INTO ORDERITEMS
VALUES (1018,2,'8843172113',1,55.95);
INSERT INTO ORDERITEMS
VALUES (1019,1,'0401140733',1,22.00);
INSERT INTO ORDERITEMS
VALUES (1020,1,'3437212490',1,19.95);

1. In a bid to increase its customer retention, Ebenzer Books would like to know the names of its customers who have spent less than R50.00. This list would be used to send a questionnaire whose objective would be to determine what can be done to increase the patronage of these customers.

2.Ebenzer Books would like to know which orders are awaiting shipping and are being shipped to a ship zip code that starts with the number 3. Display each order number, shipdate and ship zip code.

3.Ebenzer Books has requested that the shipping date for all orders that do not have a shipping cost be updated to today’s date

Answer 1

1. select A.FirstName, A.LastName, A.Customer#, sum(B.TotalExpense) from
(select FirstName, LastName, Customer#, Order# from Customers C, Orders Or
where C.Customer# = Or.Customer#) A,
(select Order#, Quantity*PaidEach as TotalExpense from ORDERITEMS) B
Where A.Order# = B.Order#
And sum(B.TotalExpense)>50
group by A.Customer#; -- A single customer can have multiple order so adding all there orders

2. select Order#, ShipDate, ShipZip from Orders
where ShipZip like '3%';

3. update Orders set ShipDate = SYSDATE
where ShipCost IS null;