Need help with this: Write an SQL statement to display data for all of the colum

Question

Need help with this:

Write an SQL statement to display data for all of the columns.

Write an SQL statement to display data for SKU_Description and SKU.

Write an SQL statement to Create A View to display data for unique WarehouseIDs.

Write an SQL statement to display all data on products having a QuantityOnHand greater than 0.

Write an SQL statement to display the data for SKU, SKU_Description, and WarehouseID for product having QuantityOnHand equal to 0. Sort the results in ascending order by WarehouseID.

Write an SQL statement to display the data for SKU, SKU_Description, and WarehouseID for products having QuantityOnHand greater than 0. Sort the results in descending order by WarehouseID and ascending order by SKU.

Write an SQL statement to Create A View to display the data for SKU, SKU_Description, WarehouseID, and QuantityOnHand for all products having a QuantityOnHand greater than 1 and less than 10. Provide a screenshot of the result as well.

Write an SQL statement to show data for SKU and SKU_Description for all products having an SKU description starting with ‘Half-dome’.

Write an SQL statement to display the data for WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Name the sum ‘TotalItemsOnHand’.

Write an SQL statement to display the data for WarehouseID and the sum of QuantityOnHand grouped by WarehouseID. Omit all SKU items that have 3 or more items on hand from the sum, and name the sum ‘TotalItemsOnHandLT3’. Display the results in descending order of ‘TotalItemsOnHandLT3’.

Explanation / Answer

1.SQL> select * from inventory;

WAREHOUSEID SKU SKU_DESCRIPTION QUANTITYONHAND QUANTITYONORDER

------------ --- --------------- --------------- ----------------

100 100100 Std scba tank yellow 250 0

200 100100 Std scba tank yellow 100 50

300 100100 Std scba tank yellow 100 0

400 100100 Std scba tank yellow 200 0

100 100200 Std. Scuba Tank, Magenta 200 30

200 100200 Std. Scuba Tank, Magenta 75 75

300 100200 Std. Scuba Tank, Magenta 100 100

100 101100 Dive Mask, Small Clear 0 500

200 101100 Dive Mask, Small Clear 0 500

200 101200 Dive Mask, Med Clear 475 0

10 rows selected.

2.SQL> select sku,sku_description from inventory;

SKU SKU_DESCRIPTION
---------- ------------------------------
100100 Std scba tank yellow
100100 Std scba tank yellow
100100 Std scba tank yellow
100100 Std scba tank yellow
100200 Std. Scuba Tank, Magenta
100200 Std. Scuba Tank, Magenta
100200 Std. Scuba Tank, Magenta
101100 Dive Mask, Small Clear
101100 Dive Mask, Small Clear
101200 Dive Mask, Med Clear

OR

SQL> select sku,sku_desc from sku_data;

SKU SKU_DESC
---------- ------------------------------
100100 Std scba tank yellow
100200 Std scba tank magenta
101100 Dive Mask Small Clear
101200 Dive Mask Med Clear
201000 Half Dome Tent
202000 Half Dome Tent Vestibule
302000 Locking Carabiner Oval
301000 Light Fly Climbing Harness

8 rows selected.

3. SQL> create view inventory_view as select distinct warehouseid from inventory;

View created.

SQL> select * from inventory_view;

WAREHOUSEID
-----------
100
400
300
200

4.SQL> SELECT *FROM INVENTORY WHERE QuantityOnHand >0;

WAREHOUSEID SKU SKU_DESCRIPTION QUANTITYONHAND QUANTITYONORDER
----------- ----- ------------------ --------------- ---------------
100 100100 Std scba tank yellow 250 0
200 100100 Std scba tank yellow 100 50
300 100100 Std scba tank yellow 100 0
400 100100 Std scba tank yellow 200 0
100 100200 Std. Scuba Tank, Magenta 200 30
200 100200 Std. Scuba Tank, Magenta 75 75
300 100200 Std. Scuba Tank, Magenta 100 100
200 101200 Dive Mask, Med Clear 475 0
8 rows selected.

5.SQL> select sku, sku_Description,WarehouseID from inventory where QuantityOnHand =0

order by WarehouseID asc;

SKU SKU_DESCRIPTION WAREHOUSEID
---------- ------------------------------ -----------
101100 Dive Mask, Small Clear 100
101100 Dive Mask, Small Clear 200

6.SQL> select sku,sku_description,warehouseid from inventory
where quantityonhand =0 order by warehouseid desc, sku asc;

SKU SKU_DESCRIPTION WAREHOUSEID
---------- ------------------------------ -----------
101100 Dive Mask, Small Clear 200
101100 Dive Mask, Small Clear 100

7.SQL> Create view inv_v1 as Select sku, sku_description, warehouseid, quantityon
hand from inventory where quantityonhand between 1 and 10;

View created.

SQL> select * from inv_v1; // in inventory table no rows have these values(1-10)

no rows selected

8.SQL> select sku, sku_description from inventory where sku_description like 'Half Dome%';

SKU SKU_DESCRIPTION
---------- ------------------------------
201000 Half Dome Tent
201000 Half Dome Tent
201000 Half Dome Tent
201000 Half Dome Tent
202000 Half Dome Tent Verstibule
202000 Half Dome Tent Verstibule
202000 Half Dome Tent Verstibule
202000 Half Dome Tent Verstibule

8 rows selected.

9.SQL> Select warehouseid,sum(quantityonhand) as Totalitemsonhand from inventory
group by warehouseid ;

WAREHOUSEID TOTALITEMSONHAND
----------- ----------------
100 462
400 200
300 550
200 661

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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