Microsoft SQL Server

Objective:

You are required to address a set of Data Query Language (DQL) tasks for a Store Management System using the tables provided. Each task focuses on different aspects of querying data from the system using SELECT statements with various conditions, functions, and joins.

1. SELECT Command

• Select all columns from the PRODUCT table.
• Select the product_name, price, and quantity_in_stock from the PRODUCT table.
• Select the first_name, last_name, and email from the CUSTOMER table.
• Select the order_date and total_amount from the ORDER table.

2. WHERE Command

• Select all customers where registration_date is after '2020-01-01'.
• Select all products where price is greater than 50.
• Select all orders where the total_amount is greater than 100.
• Select all products where quantity_in_stock is less than 10.

3. ORDER BY Command

• Select all products and order them by product_name in ascending order.
• Select all customers and order them by last_name in descending order.
• Select all orders and order them by order_date in ascending order.
• Select all employees and order them by hire_date in descending order.

4. TOP Command

• Select the top 5 customers from the CUSTOMER table based on registration_date.
• Select the top 10 products from the PRODUCT table ordered by price.
• Select the top 3 orders from the ORDER table ordered by total_amount.
• Select the top 5 employees from the EMPLOYEE table based on salary.

5. DISTINCT Command

• Select distinct category from the PRODUCT table.
• Select distinct last_name from the CUSTOMER table.
• Select distinct supplier_name from the SUPPLIER table.
• Select distinct store_name from the STORE table.

6. GROUP BY Command

• Group by category from the PRODUCT table and count the number of products per category.
• Group by last_name from the CUSTOMER table and count the number of customers with the same last name.
• Group by store_id from the STORE table and calculate the total quantity_in_stock of each store.
• Group by product_id from the ORDER_PRODUCT table and sum the total quantity_ordered per product.

7. HAVING Command

• Group by category from the PRODUCT table and filter the results where the count of products is greater than 5.
• Group by last_name from the CUSTOMER table and having more than 3 customers with the same last name.
• Group by store_id from the INVENTORY table, having a total quantity_in_stock greater than 50.
• Group by employee_id from the EMPLOYEE table and having a total salary greater than 50000.

8. INNER JOIN Command

• Select all orders along with customer details using INNER JOIN between the ORDER and CUSTOMER tables.
• Select all products and their corresponding suppliers using INNER JOIN between PRODUCT and SUPPLIER.
• Select all payments and the corresponding order details using INNER JOIN between PAYMENT and ORDER.
• Select all inventory records and their corresponding store details using INNER JOIN between INVENTORY and STORE.

9. LEFT JOIN Command

• Select all customers and their orders using LEFT JOIN, including customers without orders.
• Select all products and their suppliers using LEFT JOIN, including products without suppliers.
• Select all employees and the stores they work at using LEFT JOIN, including employees without a store assignment.
• Select all stores and their inventory using LEFT JOIN, including stores with no inventory.

10. COUNT, SUM, AVG Command

• Count the total number of customers in the CUSTOMER table.
• Count the number of distinct products in the PRODUCT table.
• Calculate the sum of total_amount from the ORDER table.
• Calculate the average salary of employees in the EMPLOYEE table.

11. CASE Command

• Select all products and use a CASE statement to show 'In Stock' if quantity_in_stock is greater than 0, otherwise 'Out of Stock'.
• Select all orders and use a CASE statement to show 'High Value' if total_amount is greater than 500, otherwise 'Standard'.
• Use a CASE statement to categorize products by price: 'Expensive' if price is greater than 100, 'Affordable' if price is between 50 and 100, otherwise 'Cheap'.
• Use a CASE statement in the ORDER_PRODUCT table to label products as 'Low Quantity' if quantity_ordered is less than 5, otherwise 'High Quantity'.

12. EXISTS and NOT EXISTS Command

• Select all customers where an order exists in the ORDER table using EXISTS.
• Select all customers where no order exists in the ORDER table using NOT EXISTS.
• Select all products where a supplier exists in the SUPPLIER table using EXISTS.
• Select all employees where no payments were processed using NOT EXISTS.

13. SUBQUERY Command

• Select all products whose product_id is in the result of a subquery selecting product_id from the ORDER_PRODUCT table.
• Select all customers where the customer_id is in the result of a subquery selecting customer_id from the ORDER table where total_amount is greater than 1000.
• Select all stores where the store_id is in the result of a subquery selecting store_id from the INVENTORY table where quantity_in_stock is greater than 50.
• Select all orders where the order_id is in the result of a subquery selecting order_id from the PAYMENT table where amount_paid is greater than 500.

14. RANK and DENSE_RANK Command

• Rank customers based on the total number of orders using RANK().
• Use DENSE_RANK() to rank products based on their price.
• Rank employees based on their salary using RANK().
• Use DENSE_RANK() to rank stores based on their total quantity_in_stock.

15. PIVOT and UNPIVOT

• Pivot the ORDER_PRODUCT data by order_id and product_id.
• Unpivot the sales data to show the product_name, category, and price for each product.
• Pivot the customer data by registration_date and first_name.
• Unpivot store inventory data by store_name and quantity_in_stock.

16. UNION and UNION ALL Command

• Select all products from the PRODUCT and INVENTORY tables using UNION.
• Select all customers from different regions using UNION ALL.
• Use UNION to combine high-value and standard orders.
• Use UNION ALL to select all products from two different categories.

17. COALESCE, ISNULL, NULLIF Command

• Select all products and use COALESCE to replace null category values with 'Uncategorized'.
• Use ISNULL to replace null email values in the CUSTOMER table with 'No Email Provided'.
• Use NULLIF to compare price and discount in the PRODUCT table and return NULL if they are the same.
• Select all employees and use COALESCE to replace null phone_number values with 'No Phone'.

18. STRING Functions

• Select all products' product_name in uppercase using the UPPER() function.
• Select all customers' first_name in lowercase using the LOWER() function.
• Use CONCAT() to combine the first_name and last_name of customers.
• Select all products' product_name and find the length of the string using LEN().

19. DATE Functions

• Select all customers and show their registration_date formatted as 'YYYY-MM-DD' using FORMAT().
• Add 1 year to all hire_date values in the EMPLOYEE table using DATEADD().
• Subtract 1 month from all order_date values using DATEADD().
• Select all orders and extract the year from the order_date using YEAR().

20. NUMERIC Functions

• Select the total_amount from the ORDER table and round it to the nearest integer using ROUND().
• Select all employees' salary and use CEILING() to round up their salary values.
• Use FLOOR() to round down the prices in the PRODUCT table.
• Select the maximum total_amount from the ORDER table using MAX().

21. CAST and CONVERT Command

• Select all products and cast the product_id as a string using CAST().
• Convert the order_date in the ORDER table to VARCHAR using CONVERT().
• Cast the price in the PRODUCT table to INT.
• Convert the payment_date from the PAYMENT table to DATETIME.

22. JSON Select

• Select all JSON data from a column in the INVENTORY table if it stores any JSON details.
• Select specific keys from a JSON column in the AUDIT_LOG table.
• Parse a JSON column from the ORDER table to extract product details.
• Use JSON_VALUE() to select a specific key from a JSON column in the PRODUCT table.

23. CONCAT and CONCAT_WS Functions

• Use CONCAT() to join first_name and last_name with a space in between in the CUSTOMER table.
• Use CONCAT_WS() to combine the store_name and location with a comma in the STORE table.
• Use CONCAT() to combine the product_name and category from the PRODUCT table.
• Use CONCAT_WS() to combine first_name, last_name, and email from the EMPLOYEE table.

24. LIKE and NOT LIKE

• Select products where the product_name contains 'Laptop' using LIKE.
• Select customers where email ends with 'gmail.com' using LIKE.
• Select products where category does not start with 'E' using NOT LIKE.
• Select stores where store_name contains 'Main' using LIKE.

25. EXISTS and NOT EXISTS

• Select all products where orders exist in the ORDER_PRODUCT table using EXISTS.
• Select all customers where no orders exist using NOT EXISTS.
• Select all stores where inventory exists in the INVENTORY table using EXISTS.
• Select all suppliers where no products exist using NOT EXISTS.

26. JOIN Commands

• Select all customers and their corresponding orders using INNER JOIN between CUSTOMER and ORDER.
• Select all products and their corresponding suppliers using INNER JOIN between PRODUCT and SUPPLIER.
• Select all employees and the stores they work at using LEFT JOIN between EMPLOYEE and STORE.
• Select all orders and their corresponding payments using INNER JOIN between ORDER and PAYMENT.

27. BETWEEN Command

• Select all products where the price is between 50 and 100.
• Select all orders where the total_amount is between 500 and 1000.
• Select all employees where the hire_date is between '2015-01-01' and '2020-01-01'.
• Select all payments where the amount_paid is between 100 and 500.

28. IN and NOT IN Command

• Select all products where the category is in ('Electronics', 'Furniture', 'Clothing').
• Select all customers where the customer_id is in (101, 202, 303).
• Select all orders where the order_id is not in (1, 2, 3).
• Select all stores where the location is in ('Downtown', 'Suburb', 'Mall').

29. UNION Command

• Select products from the PRODUCT and INVENTORY tables using UNION.
• Select customers from different regions using UNION ALL.
• Use UNION to combine orders from two different years.
• Use UNION ALL to select all suppliers from two different locations.

30. ARRAY Command

• Select all category and convert it into an array using STRING_AGG().
• Use UNSTRING() to expand arrays from the PRODUCT table.
• Convert product names into an array using STRING_AGG() in the ORDER_PRODUCT table.
• Use ARRAY functions to select and manipulate data from the STORE table.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.

Sample ERD Data Model for Store Management System