Muhammad Awais Rafique
$4/hr
Database Admin & Project Manager adapt in fluid data entry Ops.
- Reply rate:
- 80.0%
- Availability:
- Hourly ($/hour)
- Location:
- Islamabad, Pakistan, Pakistan
- Experience:
- 1 year
3/28/2024
DB Assignment-2
Result of Queries
Awais Rafique
Zaim Abbasi
22I-2511
22I-2462
1
Table of Contents
Query-1: ..................................................................................................................................... 3
Query-2: ..................................................................................................................................... 4
Query-3: ..................................................................................................................................... 4
Query-4: ..................................................................................................................................... 4
Query-5: ..................................................................................................................................... 5
Query-6: ..................................................................................................................................... 5
Query-7: ..................................................................................................................................... 5
Query-8: ..................................................................................................................................... 6
Query-9: ..................................................................................................................................... 6
Query-10: ................................................................................................................................... 6
Query-11: ................................................................................................................................... 6
Query-12: ................................................................................................................................... 7
Query-13: ................................................................................................................................... 7
Query-14: ................................................................................................................................... 7
Query-15: ................................................................................................................................... 7
Query-16: ................................................................................................................................... 8
Query-17: ................................................................................................................................... 8
Query-18: ................................................................................................................................... 8
Query-19: ................................................................................................................................... 9
Query-20: ................................................................................................................................... 9
Query-21: ................................................................................................................................... 9
Query-22: ................................................................................................................................... 9
Query-23: ..................................................................................................................................10
Query-24: ..................................................................................................................................10
Query-25: ..................................................................................................................................10
Query-26: ..................................................................................................................................10
Query-27: ..................................................................................................................................10
Query-28: .................................................................................................................................. 11
Query-29: .................................................................................................................................. 11
Query-30: .................................................................................................................................. 11
Query-31: .................................................................................................................................. 11
Query-32: ..................................................................................................................................12
Query-33: ..................................................................................................................................12
Query-34: ..................................................................................................................................12
2
Query-35: ..................................................................................................................................12
Query-36: ..................................................................................................................................13
Query-37: ..................................................................................................................................13
Query-38: ..................................................................................................................................13
Query-39: ..................................................................................................................................13
Query-40: ..................................................................................................................................14
Query-41: ..................................................................................................................................14
Query-42: ..................................................................................................................................14
Query-43: ..................................................................................................................................14
Query-44: ..................................................................................................................................15
Query-45: ..................................................................................................................................15
Query-46: ..................................................................................................................................15
Query-47: ..................................................................................................................................16
Query-48: ..................................................................................................................................16
Query-49: ..................................................................................................................................17
Query-50: ..................................................................................................................................17
Query-51: ..................................................................................................................................17
Query-52: ..................................................................................................................................18
Query-53: ..................................................................................................................................18
Query-54: ..................................................................................................................................19
Query-55: ..................................................................................................................................19
Query-56: ..................................................................................................................................19
Query-57: ..................................................................................................................................20
Query-58: ..................................................................................................................................20
Query-59: ..................................................................................................................................20
Query-60: ..................................................................................................................................21
Query-61: ..................................................................................................................................21
Query-62: ..................................................................................................................................21
Query-63: ..................................................................................................................................22
Query-64: ..................................................................................................................................22
Query-65: ..................................................................................................................................22
Query-66: ..................................................................................................................................23
Query-68: ..................................................................................................................................23
Query-69: ..................................................................................................................................24
Query-70: ..................................................................................................................................24
3
Query-71: ..................................................................................................................................24
Query-72: ..................................................................................................................................25
Query-73: ..................................................................................................................................25
Query-74: ..................................................................................................................................25
Query-75: ..................................................................................................................................26
Query-76: ..................................................................................................................................26
Query-77: ..................................................................................................................................26
Query-78: ..................................................................................................................................27
Query-79: ..................................................................................................................................27
Query-80: ..................................................................................................................................27
Query-1:
-- Select all columns from the Products table.
SELECT * FROM Products;
4
Query-2:
-- Select specific columns (name, price) from the Products table.
SELECT name, price FROM Products;
Query-3:
-- Select products with a price greater than 1000.
SELECT * FROM Products WHERE price > 1000;
Query-4:
-- Select products with a stock level less than or equal to 50.
SELECT * FROM Products WHERE stock_level <= 50;
5
Query-5:
-- Select products sorted by price in descending order.
SELECT * FROM Products ORDER BY price DESC;
Query-6:
-- Select products with a price between 1000 and 5000.
SELECT * FROM Products WHERE price BETWEEN 1000 AND 5000;
Query-7:
--Select products with a name starting with 'P'.
SELECT * FROM Products WHERE name LIKE 'P%';
6
Query-8:
-- Select products with a name containing 'Shoes'.
SELECT * FROM Products WHERE name LIKE '%Shoes%';
Query-9:
-- Select products sorted by name in ascending order.
SELECT * FROM Products ORDER BY name ASC;
Query-10:
-- Select distinct categories from the Products table
SELECT DISTINCT category_id FROM Products;
Query-11:
-- Select products with a price less than or equal to 3000 and in
category 2.
SELECT * FROM Products WHERE price <= 3000 AND category_id = 2;
7
Query-12:
-- Select products with a stock level greater than or equal to 10 and
less than or equal to 20.
SELECT * FROM Products WHERE stock_level BETWEEN 10 AND 20;
Query-13:
-- Select products with a name starting with 'M' and a price greater
than 5000.
SELECT * FROM Products WHERE name LIKE 'M%' AND price > 5000;
Query-14:
--Select products with a description containing 'embroidered' and
prices less than 3000.
SELECT * FROM Products WHERE description LIKE '%embroidered%' AND
price < 3000;
Query-15:
--Select products sorted by stock level in descending order
SELECT * FROM Products ORDER BY stock_level DESC;
8
Query-16:
-- Select products with a name not starting with 'L'.
SELECT * FROM Products WHERE name NOT LIKE 'L%';
Query-17:
-- Select products with a price greater than the average price.
SELECT * FROM Products WHERE price > (SELECT AVG(price) FROM
Products);
Query-18:
-- Select products with a stock level less than the minimum stock
level.
9
SELECT * FROM Products WHERE stock_level < (SELECT MIN(stock_level)
FROM Products);
Query-19:
-- Select products with a category ID in a list (1, 3, 4).
SELECT * FROM Products WHERE category_id IN (1, 3, 4);
Query-20:
-- Select products with a name ending with 'Suit'.
SELECT * FROM Products WHERE name LIKE '%Suit';
Query-21:
-- Count the total number of products.
SELECT COUNT(*) AS total_products FROM Products;
Query-22:
-- Calculate the average price of products.
SELECT AVG(price) AS average_price FROM Products;
10
Query-23:
-- Find the maximum price among products.
SELECT MAX(price) AS max_price FROM Products;
Query-24:
-- Find the minimum stock level among products.
SELECT MIN(stock_level) AS min_stock_level FROM Products;
Query-25:
-- Sum up the total stock levels of all products.
SELECT SUM(stock_level) AS total_stock_levels FROM Products;
Query-26:
-- Calculate the average stock level of products.
SELECT AVG(stock_level) AS average_stock_level FROM Products;
Query-27:
-- Count the number of products in each category.
SELECT category_id, COUNT(*) AS total_products FROM Products GROUP BY
category_id;
11
Query-28:
-- Calculate the total value of all products (price * stock_level).
SELECT SUM(price * stock_level) AS total_value FROM Products;
Query-29:
-- Find the product with the highest price.
SELECT * FROM Products WHERE price = (SELECT MAX(price) FROM
Products);
Query-30:
-- Calculate the total number of characters in all product names
SELECT SUM(LEN(name)) AS total_characters FROM Products;
Query-31:
-- Find the average price of products in each category.
SELECT category_id, AVG(price) AS average_price FROM Products GROUP BY
category_id;
12
Query-32:
-- Find the product with the lowest stock level.
SELECT * FROM Products WHERE stock_level = (SELECT MIN(stock_level)
FROM Products);
Query-33:
-- Calculate the total number of products in stock (stock_level > 0).
SELECT COUNT(*) AS total_products_in_stock FROM Products WHERE
stock_level > 0;
Query-34:
-- Calculate the total price of all products.
SELECT SUM(price) AS total_price FROM Products;
Query-35:
-- Find the category with the most products.
SELECT TOP 1 category_id, COUNT(*) AS total_products
FROM Products
13
GROUP BY category_id
ORDER BY COUNT(*) DESC;
Query-36:
-- Calculate the difference between the highest and lowest prices
SELECT (MAX(price) - MIN(price)) AS price_difference FROM Products;
Query-37:
-- Find the product with the highest stock level
SELECT * FROM Products WHERE stock_level = (SELECT MAX(stock_level)
FROM Products);
Query-38:
-- Find the category with the highest average price.
SELECT TOP 1 category_id, AVG(price) AS average_price
FROM Products
GROUP BY category_id
ORDER BY AVG(price) DESC;
Query-39:
-- Calculate the total number of products with prices greater than
5000.
SELECT COUNT(*) AS total_expensive_products FROM Products WHERE price
> 5000;
14
Query-40:
-- Calculate the total value of products in each category (price *
stock_level).
SELECT category_id, SUM(price * stock_level) AS total_value FROM
Products GROUP BY category_id;
Query-41:
-- Calculate the total number of oders.
SELECT COUNT(*) AS total_orders FROM Orders;
Query-42:
-- Calculate the average order total.
SELECT AVG(total_order_amount) AS average_order_total FROM (
SELECT oi.order_id, SUM(oi.quantity * p.price) AS
total_order_amount
FROM Order_Items oi
JOIN Products p ON oi.product_id = p.product_id
GROUP BY oi.order_id
) AS OrderTotals;
Query-43:
-- Find the customer who made the most orders.
SELECT TOP 1 customer_id, COUNT(*) AS total_orders
FROM Orders
15
GROUP BY customer_id
ORDER BY COUNT(*) DESC;
Query-44:
-- Calculate the total number of reviews for each product.
SELECT product_id, COUNT(*) AS total_reviews
FROM Reviews
GROUP BY product_id;
Query-45:
-- Calculate the total revenue generated from each category.
SELECT p.category_id, SUM(oi.quantity * p.price) AS total_revenue
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.category_id;
Query-46:
-- Find the average price of products purchased by each customer.
SELECT o.customer_id, AVG(p.price) AS average_price
FROM Order_Items oi
16
JOIN Products p ON oi.product_id = p.product_id
JOIN Orders o ON oi.order_id = o.order_id
GROUP BY o.customer_id;
Query-47:
-- Calculate the total value of products in each promotion.
SELECT pp.promotion_id, SUM(p.price * p.stock_level) AS total_value
FROM Products p
JOIN Product_Promotions pp ON p.product_id = pp.product_id
GROUP BY pp.promotion_id;
Query-48:
-- Count the total number of carts for each customer.
SELECT customer_id, COUNT(*) AS total_carts
FROM Carts
GROUP BY customer_id;
17
Query-49:
-- Calculate the total number of items in each order.
SELECT order_id, SUM(quantity) AS total_items
FROM Order_Items
GROUP BY order_id;
Query-50:
-- Find the customer with the highest total spending.
SELECT TOP 1 o.customer_id, SUM(oi.quantity * p.price) AS
total_spending
FROM Order_Items oi
JOIN Products p ON oi.product_id = p.product_id
JOIN Orders o ON oi.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY total_spending DESC;
Query-51:
-- Calculate the total number of reviews written by each customer.
18
SELECT customer_id, COUNT(*) AS total_reviews
FROM reviews
GROUP BY customer_id;
Query-52:
-- Calculate the average order total for each customer.
SELECT o.customer_id, AVG(total_price) AS avg_order_total
FROM Orders o
JOIN (
SELECT order_id, SUM(quantity * price) AS total_price
FROM Order_Items oi
JOIN Products p ON oi.product_id = p.product_id
GROUP BY order_id
) AS order_totals ON o.order_id = order_totals.order_id
GROUP BY o.customer_id;
Query-53:
-- Find the category with the highest average price of products.
SELECT TOP 1 category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
19
ORDER BY avg_price DESC;
Query-54:
-- Count the total number of orders placed in each month.
SELECT DATEPART(MONTH, order_date) AS month, COUNT(*) AS total_orders
FROM orders
GROUP BY DATEPART(MONTH, order_date);
Query-55:
-- Calculate the total revenue generated by each customer.
SELECT o.customer_id, SUM(oi.quantity * p.price) AS total_revenue
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
GROUP BY o.customer_id;
Query-56:
-- Calculate the total number of reviews for each product category.
SELECT p.category_id, COUNT(r.review_id) AS total_reviews
FROM Products p
LEFT JOIN Reviews r ON p.product_id = r.product_id
GROUP BY p.category_id;
20
Query-57:
-- Find the product with the highest average rating:
SELECT Top 1 product_id, AVG(rating) AS avg_rating
FROM Reviews
GROUP BY product_id
ORDER BY avg_rating DESC;
Query-58:
-- Calculate the total number of orders per year:
SELECT YEAR(order_date) AS year, COUNT(*) AS total_orders
FROM Orders
GROUP BY YEAR(order_date);
Query-59:
-- Find the customer who made the highest single order.
SELECT TOP 1 customer_id, SUM(price * quantity) AS highest_order_total
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
GROUP BY customer_id
ORDER BY highest_order_total DESC;
21
Query-60:
--Calculate the total value of products purchased by each customer.
SELECT o.customer_id, SUM(p.price * od.quantity) AS
total_value_purchased
FROM Orders o
JOIN Order_Items od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
GROUP BY o.customer_id;
Query-61:
-- Find the total number of products in each category.
SELECT category_id, COUNT(*) AS total_products
FROM Products
GROUP BY category_id;
Query-62:
-- Find the average price of products in each category.
SELECT category_id, AVG(price) AS average_price
FROM Products
GROUP BY category_id;
22
Query-63:
-- Find the category with the highest total value of products (price *
stock_level).
SELECT TOP 1 WITH TIES C.name AS [MAX TOTAL VALUE CATEGORY],
COALESCE(SUM(P.price * P.stock_level),0) AS [highest total value]
FROM Categories C
LEFT JOIN Products P ON C.category_id = P.category_id
GROUP BY C.name
ORDER BY [highest total value] DESC;
Query-64:
-- Find the average stock level of products in categories with more
than 10 products.
SELECT C.category_id AS [CATEGORY ID], COALESCE(AVG(P.stock_level),0)
AS [avg stock level]
FROM Categories C
LEFT JOIN Products P ON C.category_id = P.category_id
GROUP BY C.category_id
HAVING COUNT(P.product_id) > 10;
Query-65:
-- Find the total number of orders for each customer.
SELECT C.customer_id, COUNT(O.order_id) AS [order count]
FROM Customers C
LEFT JOIN Orders O
ON C.customer_id = O.customer_id
GROUP BY C.customer_id;
23
Query-66:
-- Find the average order value (total price of order) for each
customer.
SELECT C.customer_id, COALESCE(AVG(P.price * I.quantity),0) AS [avg
order value]
FROM Customers C
LEFT JOIN Orders O
ON C.customer_id = O.customer_id
LEFT JOIN Order_Items I
ON O.order_id = I.order_id
LEFT JOIN Products P
ON I.product_id = P.product_id
GROUP BY C.customer_id;
Query-68:
-- Find the customer(s) with the highest total order value.
SELECT TOP 1 WITH TIES C.customer_id, SUM(P.price * I.quantity) AS
[max order value]
FROM Customers C
INNER JOIN Orders O
ON C.customer_id = O.customer_id
INNER JOIN Order_Items I
ON O.order_id = I.order_id
24
INNER JOIN Products P
ON I.product_id = P.product_id
GROUP BY C.customer_id
ORDER BY [max order value] DESC;
Query-69:
-- Find the category with the highest average price.
SELECT TOP 1 WITH TIES C.category_id AS [MAX AVG PRICE CATEGORY],
COALESCE(AVG(P.price),0) AS [avg price]
FROM Categories C
LEFT JOIN Products P ON C.category_id = P.category_id
GROUP BY C.category_id
ORDER BY [avg price] DESC;
Query-70:
-- Find the product with the highest rating.
SELECT TOP 1 WITH TIES P.product_id, MAX(R.rating) AS [max rating]
FROM Products P
INNER JOIN Reviews R
ON P.product_id = R.product_id
GROUP BY P.product_id
ORDER BY [max rating] DESC;
Query-71:
-- Find the total number of products purchased by each customer.
SELECT customer_id, COUNT(*) AS total_products_purchased
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
GROUP BY customer_id;
25
Query-72:
-- Find the average rating of products in each category.
SELECT category_id, AVG(rating) AS average_rating
FROM Products p
LEFT JOIN Reviews r ON p.product_id = r.product_id
GROUP BY category_id;
Query-73:
-- Find the category with the highest total number of products.
SELECT TOP 1 category_id, COUNT(*) AS total_products
FROM Products
GROUP BY category_id
ORDER BY total_products DESC;
Query-74:
-- Find the customer(s) who have placed orders with the highest total
value.
SELECT TOP 1 customer_id, SUM(oi.quantity * p.price) AS
total_order_value
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
26
JOIN Products p ON oi.product_id = p.product_id
GROUP BY customer_id
ORDER BY total_order_value DESC;
Query-75:
-- Find the product(s) with the highest total number of orders.
SELECT TOP 1 product_id, COUNT(*) AS total_orders
FROM Order_Items
GROUP BY product_id
ORDER BY total_orders DESC;
Query-76:
-- Find the category with the highest total order value.
SELECT TOP 1 p.category_id, SUM(oi.quantity * p.price) AS
total_order_value
FROM Order_Items oi
JOIN Products p ON oi.product_id = p.product_id
GROUP BY p.category_id
ORDER BY total_order_value DESC;
Query-77:
-- Find the customer(s) with the highest average order value.
SELECT TOP 1 customer_id, AVG(oi.quantity * p.price) AS
average_order_value
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
GROUP BY customer_id
ORDER BY average_order_value DESC;
27
Query-78:
-- Find the product(s) with the highest total order quantity.
SELECT TOP 1 product_id, SUM(quantity) AS total_order_quantity
FROM Order_Items
GROUP BY product_id
ORDER BY total_order_quantity DESC;
Query-79:
-- Find the customer(s) who have placed orders with the highest
average quantity per order.
SELECT TOP 1 customer_id, AVG(quantity) AS average_quantity_per_order
FROM Order_Items oi
JOIN Orders o ON oi.order_id = o.order_id
GROUP BY customer_id
ORDER BY average_quantity_per_order DESC;
Query-80:
-- Find the category with the highest average stock level.
SELECT TOP 1 category_id, AVG(stock_level) AS average_stock_level
FROM Products
GROUP BY category_id
ORDER BY average_stock_level DESC
