Question
Have the following tables/data. I am trying to 'For each customer having an order, list the customer number, the number of orders that customer has,
Have the following tables/data. I am trying to 'For each customer having an order, list the customer number, the number of orders that customer has, the total quantity of items on those orders, and the total price for the items. Order the output by customer number. I know I should be using GROUP BY clause. Using Oracle 12c, command line.
CREATE TABLE manufact (manu_code CHAR(3) CONSTRAINT manu_code_pk PRIMARY KEY, manu_name VARCHAR2(15))
INSERT INTO manufact VALUES ('ANZ','Anza'); INSERT INTO manufact VALUES ('HSK','Husky'); INSERT INTO manufact VALUES ('HRO','Hero'); INSERT INTO manufact VALUES ('NRG','Norge'); INSERT INTO manufact VALUES ('SMT','Smith');
CREATE TABLE stock (stock_num NUMBER(2), manu_code CHAR(3), description VARCHAR2(15), unit_price NUMBER(8,2), unit CHAR(4), unit_descr VARCHAR2(15), PRIMARY KEY (stock_num,manu_code), FOREIGN KEY (manu_code) REFERENCES manufact (manu_code))
INSERT INTO stock VALUES (1,'HRO','baseball gloves',250.00,'case','10 gloves/case'); INSERT INTO stock VALUES (1,'HSK','baseball gloves',800.00,'case','10 gloves/case'); INSERT INTO stock VALUES (1,'SMT','baseball gloves',450.00,'case','10 gloves/case'); INSERT INTO stock VALUES (2,'HRO','baseball',126.00,'case','24/case'); INSERT INTO stock VALUES (3,'HSK','baseball bat',240.00,'case','12/case'); INSERT INTO stock VALUES (4,'HSK','football',960.00,'case','24/case'); INSERT INTO stock VALUES (4,'HRO','football',480.00,'case','24/case'); INSERT INTO stock VALUES (5,'NRG','tennis racquet',28.00,'each','each'); INSERT INTO stock VALUES (5,'SMT','tennis racquet',25.00,'each','each'); INSERT INTO stock VALUES (5,'ANZ','tennis racquet',19.80,'each','each'); INSERT INTO stock VALUES (6,'SMT','tennis ball',36.00,'case','24 cans/case'); INSERT INTO stock VALUES (6,'ANZ','tennis balls',48.00,'case','24 cans/case'); INSERT INTO stock VALUES (7,'HRO','basketball',600.00,'case','24/case'); INSERT INTO stock VALUES (8,'ANZ','volleyball',840.00,'case','24/case'); INSERT INTO stock VALUES (9,'ANZ','volleyball net',20.00,'each','each');
CREATE TABLE items (item_num NUMBER(2), order_num NUMBER(4), stock_num NUMBER(2), manu_code CHAR(3), quantity NUMBER(3), total_price NUMBER(8,2), PRIMARY KEY (item_num,order_num), FOREIGN KEY (stock_num, manu_code) REFERENCES stock (stock_num, manu_code))
INSERT INTO items VALUES (1,1001,1,'HRO',1,250.00); INSERT INTO items VALUES (1,1002,4,'HSK',1,960.00); INSERT INTO items VALUES (2,1002,3,'HSK',1,240.00); INSERT INTO items VALUES (1,1003,9,'ANZ',1,20.00); INSERT INTO items VALUES (2,1003,8,'ANZ',1,840.00); INSERT INTO items VALUES (3,1003,5,'ANZ',5,99.00); INSERT INTO items VALUES (1,1004,1,'HRO',1,960.00); INSERT INTO items VALUES (2,1004,2,'HRO',1,126.00); INSERT INTO items VALUES (3,1004,3,'HSK',1,240.00); INSERT INTO items VALUES (4,1004,1,'HSK',1,800.00); INSERT INTO items VALUES (1,1005,5,'NRG',10,280.00); INSERT INTO items VALUES (2,1005,5,'ANZ',10,198.00); INSERT INTO items VALUES (3,1005,6,'SMT',1,36.00); INSERT INTO items VALUES (4,1005,6,'ANZ',1,48.00); INSERT INTO items VALUES (1,1006,5,'SMT',5,125.00); INSERT INTO items VALUES (2,1006,5,'NRG',5,190.00); INSERT INTO items VALUES (3,1006,5,'ANZ',5,99.00); INSERT INTO items VALUES (4,1006,6,'SMT',1,36.00); INSERT INTO items VALUES (5,1006,6,'ANZ',1,48.00); INSERT INTO items VALUES (1,1007,1,'HRO',1,250.00); INSERT INTO items VALUES (2,1007,2,'HRO',1,126.00); INSERT INTO items VALUES (3,1007,3,'HSK',1,240.00); INSERT INTO items VALUES (4,1007,4,'HRO',1,480.00); INSERT INTO items VALUES (5,1007,7,'HRO',1,600.00); INSERT INTO items VALUES (1,1008,8,'ANZ',1,840.00); INSERT INTO items VALUES (2,1008,9,'ANZ',5,100.00); INSERT INTO items VALUES (1,1009,1,'SMT',1,450.00); INSERT INTO items VALUES (1,1010,6,'SMT',1,36.00); INSERT INTO items VALUES (2,1010,6,'ANZ',1,48.00); INSERT INTO items VALUES (1,1011,5,'ANZ',5,99.00); INSERT INTO items VALUES (1,1012,8,'ANZ',1,840.00); INSERT INTO items VALUES (2,1012,9,'ANZ',10,200.00); INSERT INTO items VALUES (1,1013,5,'ANZ',1,19.80); INSERT INTO items VALUES (2,1013,6,'SMT',1,36.00); INSERT INTO items VALUES (3,1013,6,'ANZ',1,48.00); INSERT INTO items VALUES (4,1013,9,'ANZ',2,40.00); INSERT INTO items VALUES (1,1014,4,'HSK',1,960.00); INSERT INTO items VALUES (2,1014,4,'HRO',1,480.00); INSERT INTO items VALUES (1,1015,1,'SMT',1,450.00);
CREATE TABLE customer (customer_num NUMBER(3) CONSTRAINT customer_num_pk PRIMARY KEY, fname VARCHAR2(15), lname VARCHAR2(15), company VARCHAR2(20), address1 VARCHAR2(20), address2 VARCHAR2(20), city VARCHAR2(15), state CHAR(2), zipcode CHAR(5), phone VARCHAR(18) )
INSERT INTO customer VALUES (101,'Ludwig','Pauli','All Sports Supplies','213 Erstwild Court', NULL,'Sunnyvale','CA','94086','408-789-8075'); INSERT INTO customer VALUES (102,'Carole','Sadler','Sports Spots','785 Geary St', NULL,'San Francisco','CA','94117','415-822-1289'); INSERT INTO customer VALUES (103,'Phillip','Currie','Phil''s Sports','654 Poplar', 'P.O. Box 3498','Palo Alto','CA','94303','415-328-4543'); INSERT INTO customer VALUES (104,'Anthony','Higgins','Play Ball!','East Shopping Cntr.', '422 Bay Road','Redwood City','CA','94026','415-368-1100'); INSERT INTO customer VALUES (105,'Raymond','Vector','Los Altos Sports','1899 Le Loma Drive', NULL,'Los Altos','CA','94022','415-776-3249'); INSERT INTO customer VALUES (106,'George','Watson','Watson and Son','1143 Carver Place', NULL,'Mountain View','CA','94063','415-389-8789'); INSERT INTO customer VALUES (107,'Charles','Ream','Athletic Supplies','41 Jordan Avenue', NULL,'Palo Alto','CA','94304','415-356-9876'); INSERT INTO customer VALUES (108,'Donald','Quinn','Quinn''s Sports','587 Alvarado', NULL,'Redwood City','CA','94063','415-544-8729'); INSERT INTO customer VALUES (109,'Jane','Miller','Sport Stuff','Mayfair Mart', '7345 Ross Blvd.','Sunnyvale','CA','94086','408-723-8789'); INSERT INTO customer VALUES (110,'Roy','Jaeger','AA athletics','520 Topaz Way', NULL,'Redwood City','CA','94062','415-743-3611'); INSERT INTO customer VALUES (111,'Frances','Keyes','Sports Center','3199 Sterling Court', NULL,'Sunnyvale','CA','94085','408-277-7245'); INSERT INTO customer VALUES (112,'Margaret','Lawson','Runners and Others','234 Wyandotte Way', NULL,'Los Altos Hills','CA','94022','415-887-7235'); INSERT INTO customer VALUES (113,'Lana','Beatty','Sportstown','654 Oak Grove', NULL,'Menlo Park','CA','94025','415-356-9982'); INSERT INTO customer VALUES (114,'Frank','Albertson','Sporting Place','947 Waverly Place', NULL,'Redwood City','CA','94062','415-886-6677'); INSERT INTO customer VALUES (115,'Alfred','Grant','Gold Medal Sports','776 Gary Avenue', NULL,'Menlo Park','CA','94025','415-356-1123'); INSERT INTO customer VALUES (116,'Jean','Parmelee','Olympic City','1104 Spinosa Drive', NULL,'Mountain View','CA','94040','415-534-8822'); INSERT INTO customer VALUES (117,'Arnold','Sipes','Kids Korner','850 Lytton Court', NULL,'Redwood City','CA','94063','415-245-4578'); INSERT INTO customer VALUES (118,'Dick','Baxter','Blue Ribbon Sports','5427 College', NULL,'Oakland','CA','94609','415-655-0011');
CREATE TABLE orders (order_num NUMBER(4) CONSTRAINT order_num_pk PRIMARY KEY, order_date DATE CONSTRAINT order_date_nn NOT NULL, customer_num NUMBER(3) CONSTRAINT order_cust_num_fk REFERENCES CUSTOMER(customer_num), ship_instruct VARCHAR2(40), backlog CHAR(1), po_num VARCHAR(10), ship_date DATE, ship_weight NUMBER(8,2), ship_charge NUMBER(6,2), paid_date DATE)
INSERT INTO orders VALUES (1001,TO_DATE('12/30/1999','MM/DD/YYYY'),104, 'ups','n','B77836', TO_DATE('09/31/1999','MM/DD/YYYY'),20.40,10.00, TO_DATE('01/12/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1002,TO_DATE('12/20/1999','MM/DD/YYYY'),101, 'po on box; deliver back door only','n','9270', TO_DATE('09/28/1999','MM/DD/YYYY'),50.60,15.30, TO_DATE('01/03/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1003,TO_DATE('12/05/1999','MM/DD/YYYY'),104,'via ups','n','B77890', TO_DATE('12/08/1999','MM/DD/YYYY'),35.60,10.80, TO_DATE('12/14/1999','MM/DD/YYYY')); INSERT INTO orders VALUES (1004,TO_DATE('12/30/1999','MM/DD/YYYY'),106, 'ring bell twice','y','8006', NULL,95.80,19.20, NULL); INSERT INTO orders VALUES (1005,TO_DATE('12/30/1999','MM/DD/YYYY'),116, 'call before delivering','n','2865', TO_DATE('12/03/2000','MM/DD/YYYY'),80.80,16.20, TO_DATE('12/10/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1006,TO_DATE('12/30/1999','MM/DD/YYYY'),112, 'after 10 am','y','Q13557', NULL,70.80,14.20, NULL); INSERT INTO orders VALUES (1007,TO_DATE('12/30/1999','MM/DD/YYYY'),117,NULL,'n','278693', TO_DATE('01/03/2000','MM/DD/YYYY'),125.90,25.20, NULL); INSERT INTO orders VALUES (1008,TO_DATE('12/30/1999','MM/DD/YYYY'),110, 'closed Monday','y','LZ230', TO_DATE('01/15/2000','MM/DD/YYYY'),45.60,13.80, TO_DATE('01/22/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1009,TO_DATE('12/01/1999','MM/DD/YYYY'),111, 'door next to supersaver','n','4745', TO_DATE('12/12/1999','MM/DD/YYYY'),20.40,10.00, TO_DATE('12/15/1999','MM/DD/YYYY')); INSERT INTO orders VALUES (1010,TO_DATE('12/31/1999','MM/DD/YYYY'),115, 'deliver 776 Gary if no answer','n','429Q', TO_DATE('01/02/2000','MM/DD/YYYY'),40.60,12.30, TO_DATE('01/22/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1011,TO_DATE('12/31/1999','MM/DD/YYYY'),104,'ups','n','B77897', TO_DATE('01/02/2000','MM/DD/YYYY'),10.40,5.00, TO_DATE('01/13/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1012,TO_DATE('12/31/1999','MM/DD/YYYY'),117,NULL,'n','278701', TO_DATE('01/05/2000','MM/DD/YYYY'),70.80,14.20, NULL); INSERT INTO orders VALUES (1013,TO_DATE('01/03/2000','MM/DD/YYYY'),104,'via ups','n','B77930', TO_DATE('01/08/2000','MM/DD/YYYY'),60.80,12.20, TO_DATE('01/17/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1014,TO_DATE('12/31/1999','MM/DD/YYYY'),106, 'ring bell kick door loudly','n','8052', TO_DATE('01/04/2000','MM/DD/YYYY'),40.60,12.30, TO_DATE('01/16/2000','MM/DD/YYYY')); INSERT INTO orders VALUES (1015,TO_DATE('01/03/2000','MM/DD/YYYY'),110,'closed Mon','n','MA003', TO_DATE('01/08/2000','MM/DD/YYYY'),20.60,6.30, TO_DATE('01/24/2000','MM/DD/YYYY'));
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started