When running the following script, 76 rows are returned. Number of orders is 15 and should get 15 rows returned. Looking at using GROUP BY O.order_num, but not sure how to use aggregate function without messing up display of Company, Description, Manufacturer, Quantity, Total Price.

SELECT C.company, S.description, M.manu_name, I.quantity, I.total_price FROM customer 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;

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'));