FOR THE TEXT BOOK \"GUIDE TO ORACLE 10g \" , chapter 3b I need help with sloving

Question

FOR THE TEXT BOOK "GUIDE TO ORACLE 10g " , chapter 3b

I need help with sloving these cases in SQL command

All the following are based on the Clearwater Traders database

1. Determine the inventory price of inventory item (INV_ID) #1

2. Determine which customers were bormn in the 1970s.

3. Determine how many different categoeries of inventory are carried by Clearwater Traders.

4. Determin hom many shipmemnts have not yet been received.

5. Calculate the total quantity on hand for each inventory item in the INVENTORY table - ignore their different sizes and colors

6. Determine the total number of orders received on May 29 2006

7. Determine how many orders placed on May 31, 2006, were paid by credit card.

8. Create a list of all Florida and Georgia customers.

9. List all inventory item that do not have an associated size.

10. Identify which shipments are expected by September 1, 2006.

11. Determine how many inventory item have an inventory price grater than $60.00 and are available in a size of L or XL.

12. In the INVENTORY table, ITEM_ID 5 is available in how many different colors

13. In the INVENTORY table, ITEM_ID 5 is available in how many different sizes.

14. Determine the current age of each customer. Display the customer's first and last names and their age in years

15. Display the SHIP_DATE_EXPECTED of each shipment in the SHIPMENT table using the format MONTH DD,YYYY.

16. Determine how many orders were received from each of the available order sources (OS_ID).

17. Identify which items in stock (that is INV_QOH > 0) are available in sizes Medium or Large and are available in the colors Royal,Bright Pink or Spruce.

18. Create a listing that identifies the different items (ITEM_ID) in the INVENTORY table and the number of colors available for each item.

19. Determine how many items are not in categories 2 or 4.

20. List the unique ITEM_IDs along with their inventory prices from the INVENTORY table. Format the inventory prices so they are displayed in format $999.99.

Explanation / Answer

I can help u with these if u provide me time i ll try to help u with bit more
18.

SELECT inv.inv_id,item1.item_id,COUNT( distinct inv.color )
FROM inventory inv
JOIN item item1 ON (item1.item_id = inv.item_id)
GROUP BY inv.inv_id, item1.item_id

or
CREATE TABLE inventory
(inv_id NUMBER(10),
item_id NUMBER(8),
color VARCHAR2(20),
inv_size VARCHAR2(10),
inv_price NUMBER(6,2),
inv_qoh NUMBER(4),
CONSTRAINT inventory_inv_id_pk PRIMARY KEY (inv_id),
CONSTRAINT inventory_item_id_fk FOREIGN KEY (item_id) REFERENCES item(item_id),
CONSTRAINT inventory_color_fk FOREIGN KEY (color) REFERENCES color(color));

15

CREATE TABLE shipment
(ship_id NUMBER(10),
ship_date_expected DATE,
CONSTRAINT shipment_ship_id_pk PRIMARY KEY (ship_id));

16

CREATE TABLE order_line
(o_id NUMBER(8),
inv_id NUMBER(10),
ol_quantity NUMBER(4) NOT NULL,
CONSTRAINT order_line_o_id_fk FOREIGN KEY (o_id) REFERENCES orders(o_id),
CONSTRAINT order_line_inv_id_fk FOREIGN KEY (inv_id) REFERENCES inventory(inv_id),
CONSTRAINT order_line_oid_invid_pk PRIMARY KEY (o_id, inv_id));

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.