Question

USE FOLLOWING TABLES TO ANSWER FOLLOWING QUESTIONS:
WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager, SquareFeet)

INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand, QuantityOnOrder)

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

2.Write an SQL statement to show a unique SKU and SKU_Description for all products having a description that includes the word 'Climb'.

3.Write an SQL statement to show a unique SKU and SKU_Description for all products with a ‘d’ in the third position from the left in SKU_Description.

4.Write an SQL statement that uses all of the SQL built-in functions on the QuantityOn Hand column. Include meaningful column names in the result.

HTML EditorKeyboard Shortcuts

5.Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in descending order of TotalItemsOnHand.

6.Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.

7.Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use JOIN ON syntax.

8.Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON syntax.

9.Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.

10.Write an SQL statement to show the WarehouseID and average QuantityOnHand of all items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use JOIN ON syntax.

11.What are the functions for DBMS?

Answer 1

Databse was not mentioned so i have considered Oracle as database. Please comment if face any issue. Also do upvote if i answered your question. THANKS!!

1.
select distinct SKU, SKU_Description from INVENTORY where SKU_Description like 'Half-dome%';

2.
select distinct SKU, SKU_Description from INVENTORY where SKU_Description like '%Climb%';

3.
select distinct SKU, SKU_Description from INVENTORY where INSTR(SKU_Description, 'd', 1) = 3;

4.
select count(WarehouseID) as column_name, max(WarehouseID) as max_column_name, min(WarehouseID) as min_col_name , avg(WarehouseID) as avg_col_name, sum(WarehouseID) as sum_col_name from INVENTORY;

5.
select WarehouseID, sum(QuantityOnHand) as TotalItemsOnHand from INVENTORY group by WarehouseID order by TotalItemsOnHand desc;

6.
select SKU, SKU_Description, WarehouseID from INVENTORY where WarehouseID in (select WarehouseID from WAREHOUSE where Manager = 'Lucille Smith');

7.
select i.SKU, i.SKU_Description, i.WarehouseID from INVENTORY i, WAREHOUSE w where w.Manager = 'Lucille Smith' and i.WarehouseID = w.WarehouseID;

8.
select i.SKU, i.SKU_Description, i.WarehouseID from INVENTORY i inner join WAREHOUSE w on i.WarehouseID = w.WarehouseID where w.Manager = 'Lucille Smith';

9.
select i.WarehouseID, avg(i.QuantityOnHand) from INVENTORY i where i.WarehouseID in (select WarehouseID from WAREHOUSE where Manager = 'Lucille Smith');

10.
select i.WarehouseID, avg(i.QuantityOnHand) from INVENTORY i , WAREHOUSE w where i.WarehouseID = w.WarehouseID and w.Manager = 'Lucille Smith';

11. There were so many questions so didnt get much time to answer this question is detail.
There are several functions that a DBMS performs to ensure data integrity and consistency of data in the database. The ten functions in the DBMS are: data dictionary management, data storage management, data transformation and presentation, security management, multiuser access control, backup and recovery management, data integrity management, database access languages and application programming interfaces, database communication interfaces, and transaction management.