Have the following tables/data. I am trying to \'For each customer having an ord
ID: 3869788 • Letter: H
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, 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'));
Explanation / Answer
select C.customer_num, sum(O.order_num),sum(i.quantity),sum(i.quantity * i.total_price) from customer C,orders O,items i where C.customer_num=O.order_num and O.order_num=i.order_num group by C.customer_num.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.