When running the following script, 573 rows are returned. Number of orders is 15
ID: 3604045 • Letter: W
Question
When running the following script, 573 rows are returned. Number of orders is 15 and should get 15 rows returned.
SELECT C.company, S.description, M.manu_name, I.quantity, I.total_price
2 FROM customer C, orders O, items I, stock S, manufact M
3 WHERE C.customer_num=O.customer_num AND O.order_num=I.order_num
AND S.manu_code=M.manu_code
4 ORDER BY C.company, S.description;
Requirements:
For each customer that has placed an order, list each:
• The company name
• The item Description
• The manufacturer
• The quantity ordered
• The total price paid.
Include the following columns in the order given below:
From Customer Table: Company
From Stock Table: Description
From the Manufact Table: Manu_Name
From the Items Table: Quantity, Total Price
Order the output by Company and Description.
From the following data:
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 COUNT(*) FROM (SELECT C.company, S.description, M.manu_name, I.quantity, I.total_price,O.ORDER_NUM
FROM CUSTOMER1 C
INNER JOIN ORDERS O
ON C.CUSTOMER_NUM=O.CUSTOMER_NUM
INNER JOIN ITEMS I
ON O.ORDER_NUM=I.ORDER_NUM
INNER JOIN STOCK S
ON I.STOCK_NUM=S.STOCK_NUM
INNER JOIN MANUFACT M
ON S.MANU_CODE=M.MANU_CODE
ORDER BY C.company, S.description)A
GROUP BY A.ORDER_NUM;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.