Description
Please review chapters 9, 11. of the book Oracle 12c SQL by Joan Casteel
THE TEST WILL BE BASED ON USING JOINS AND GROUP FUNCTIONS, AS WELL AS WHERE, AND HAVING CONDITIONS. MAKE SURE YOU KNOW HOW TO USE GROUP BY AND JOIN
FOR PRACTICE PLEASE DO THE FOLLOWING QUESTIONS:
1) MAKE A LIST OF CUSTOMERS WITH THE LIST OF ORDER# AND THE AMOUNT OF DOLLARS THAT THEY PAID FOR EACH OF THEIR ORDERS, SORT BY CUSTOMER LAST NAME.
2) MAKE A LIST OF CUSTOMERS WITH THE TOTAL AMOUNT OF DOLLARS THAT THEY PAID FOR ALL THEIR ORDERS COMBINED (FOR EACH CUSTOMER).
3) MAKE A LIST OF CATEGORIES AND NUMBER OF BOOKS IN EACH CATEGORY, SORT BY CATEGORY NAME.
4) FIND OUT HOW MANY BOOKS ARE IN EACH ORDER, SORT ORDERS BY ORDER#. - TWO QUERIES A) AND B)
-A) ONE QUERY SHOWS HOW MANY DIFFERENT ISBN NUMBERS IN EACH ORDER (DIFFERENT BOOKS)
-B) ANOTHER QUERY SHOWS HOW MANY PHYSICAL COPIES OF THE BOOKS COMBINED, THAT IS PRESENTED BY "QUANTITY" FOR A SPECIFIC ISBN IN ONE OF YOUR TABLES.
YOU WOULD NEED TO FIND THE SUM OF THESE QUANTITIES FOR AN ORDER
book Oracle 12c SQL by Joan Casteel (2).pdf
Choose the data file that sute the question from the attached list
DATA FILES FOR ORACLE 11G BOOK, CAT9 LOG-IN BELOW
finally I have a test similar to this question after two days so it will be great if you will be available for another task
there is 4 more JLDB files will attach later
Explanation & Answer
Hello there. Assignment done. Should I have to make any changes?
DROP TABLE CUSTOMERS CASCADE CONSTRAINTS; DROP TABLE ORDERS CASCADE CONSTRAINTS; DROP TABLE PUBLISHER
CASCADE CONSTRAINTS; DROP TABLE AUTHOR CASCADE CONSTRAINTS; DROP TABLE BOOKS CASCADE CONSTRAINTS; DROP
TABLE ORDERITEMS CASCADE CONSTRAINTS; DROP TABLE
BOOKAUTHOR CASCADE CONSTRAINTS; DROP TABLE PROMOTION
CASCADE CONSTRAINTS; DROP TABLE ACCTMANAGER CASCADE
CONSTRAINTS; DROP TABLE ACCTBONUS CASCADE CONSTRAINTS;
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 customersregion_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’, ‘bm225@sat.net’);
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’, ‘tpier55@sat.net’);
INSERT INTO CUSTOMERS VALUES (1005, ‘GIRARD’, ‘CINDY’, ‘P.O.
BOX 851’, ‘SEATTLE’, ‘WA’, ‘98115’, NULL, ‘NW’, ‘cing101@zep.net’);
INSERT INTO CUSTOMERS VALUES (1006, ‘CRUZ’, ‘MESHIA’, ‘82 DIRT
ROAD’, ‘ALBANY’, ‘NY’, ‘12211’, NULL, ‘NE’, ‘cruztop@axe.com’); INSERT
INTO CUSTOMERS VALUES (1007, ‘GIANA’, ‘TAMMY’, ‘9153 MAIN
STREET’, ‘AUSTIN’, ‘TX’, ‘78710’, 1003, ‘SW’, ‘treetop@zep.net’); INSERT
INTO CUSTOMERS VALUES (1008, ‘JONES’, ‘KENNETH’, ‘P.O. BOX
137’, ‘CHEYENNE’, ‘WY’, ‘82003’, NULL, ‘N’, ‘kenask@sat.net’); INSERT
INTO CUSTOMERS VALUES (1009, ‘PEREZ’, ‘JORGE’, ‘P.O. BOX 8564’,
‘BURBANK’, ‘CA’, ‘91510’, 1003, ‘W’, ‘jperez@canet.com’); 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’, ‘reesemc@sat.net’);
INSERT INTO CUSTOMERS VALUES (1012, ‘MCKENZIE’, ‘WILLIAM’, ‘P.O.
BOX 971’, ‘BOSTON’, ‘MA’, ‘02110’, NULL, ‘NE’, ‘will2244@axe.net’); INSERT
INTO CUSTOMERS VALUES (1013, ‘NGUYEN’, ‘NICHOLAS’, ‘357 WHITE
EAGLE AVE.’, ‘CLERMONT’, ‘FL’, ‘34711’, 1006, ‘SE’, ‘nguy33@sat.net’);
INSERT INTO CUSTOMERS VALUES (1014, ‘LEE’, ‘JASMINE’, ‘P.O. BOX
2947’, ‘CODY’, ‘WY’, ‘82414’, NULL, ‘N’, ‘jaslee@sat.net’); INSERT INTO
CUSTOMERS VALUES (1015, ‘SCHELL’, ‘STEVE’, ‘P.O. BOX 677’, ‘MIAMI’,
‘FL’, ‘33111’, NULL, ‘SE’, ‘sschell3@sat.net’); INSERT INTO CUSTOMERS
1
VALUES (1016, ‘DAUM’, ‘MICHELL’, ‘9851231 LONG ROAD’, ‘BURBANK’,
‘CA’, ‘91508’, 1010, ‘W’, NULL); INSERT INTO...
