Database Design and Development
Topic: Structured Query Language (SQL) – Part 1
MCQs
i. SQL stands for Standard Query Language. True/False
ii. The SQL keyword LIKE is used in SQL expressions to select partial string values. True/ False
iii. In an SQL query, which SQL keyword actually begins the query?
- EXISTS
- FROM
- SELECT
- WHERE
iv. In an SQL query, which SQL keyword must be used to remove duplicate rows from the result table?
- DELETE
- DISTINCT
- UNIQUE
- KEY
v. In an SQL query, which SQL keyword is used with built-in functions to group together rows that have the same value in a specified column or columns?
- GROUP BY
- ORDER BY
- SELECT
- SORT BY
1. Create a new database called ‘Cape_Codd’ using SQL Server Management System.
2. Create tables using the script ‘CreateData.sql’ provided.
3. Populate these tables using the script ‘InsertData.sql’ provided.
4. Use this new database to implement the following SQL statements in SQL Server Management System. Write SQL statements and provide screenshots of the output in the answer.
a. Select all columns from SKU_Data
SELECT * FROM sku_data
b. Write an SQL statement to display warehouse ID, SKU and SKU_Description and Quantity on Hand.
SELECT WarehouseID, SKU, SKU_Description, QuantityOnHand FROM INVENTORY
c. Write an SQL statement to display unique buyers.
Select distinct Buyer, department from sku_data
Select Buyer, department from sku_data
d. Write an SQL statement to display top 5 most expensive products from Order_Item table. Sort the results in descending order by price.
Select top 5 sku, price from order_item order by price desc
e. Write an SQL statement to display all columns of Order_Item. Sort the results in ascending order by SKU and descending order by Quantity.
f. Write an SQL statement to display SKU, SKU_Description and WarehouseID for products having QuantityOnHand not equal to 0.
Select SKU, SKU_Description, WarehouseID from inventory where quantityonhand <> 0
g. Write an SQL statement to display SKU and SKU_Description, Department for ‘Climbing’ and ‘Water Sports’ departments.
SELECT SKU, SKU_Description, Department FROM SKU_DATA
WHERE Department = ‘Water Sports’
Or Department = ‘Climbing’;
h. Write an SQL statement to display details of all warehouses whose manager’s name starts with ‘D’.
i. Write an SQL statement to display SKU and SKU_Description for all products having a description that includes the word ‘Climb’.
Select SKU ,SKU_Description from sku_data where sku_description like ‘%climb%’
j. Write an SQL statement to display details of retail orders placed in December month.
SELECT * FROM RETAIL_ORDER WHERE OrderMonth = ‘December’
k. Write an SQL statement to display details of all products that have been purchased by either Nancy Meyers’, Pete Hanson or Jerry Martin’.
SELECT * FROM SKU_DATA
WHERE Buyer IN (‘Nancy Meyers’, ‘Cindy Lo’, ‘Jerry Martin’)
l. Write an SQL statement to display SKU and SKU_Description, WarehouseID and QuantityOnhHand for all products having QuantityOnHand greater than 1 and less than 10. Use the Between keyword.
SELECT SKU, SKU_Description, WarehouseID FROM INVENTORY
WHERE QuantityOnHand BETWEEN 1 AND 10
m. Write a SQL statement to find count of SKUs for each department.
SELECT Department, COUNT(SKU) AS Dept_SKU_Count
FROM SKU_DATA GROUP BY Department
n. Write a SQL statement to find the average of QuantityOnOrder for all SKUs. Only display averages that are greater than 200.
SELECT SKU, AVG(QuantityOnOrder) as ‘OrderedQty’
FROM Inventory
Group By SKU
Having AVG(QuantityOnOrder)> 200
