Write a minimum of 15 different queries following guidelines below:
Checklist:
• Write questions that the query should answer.
• Write an SQL statement to answer the question.
• Must demonstrate the use of the following:
1. Arithmetic Operators
2. Distinct
3. Concatenation
4. Comparison operators.
5. BETWEEN … AND
6. AND
7. OR
8. NOT
9. IN
10. LIKE
11. Multi table JOIN
12. SORT
13. Arithmetic functions (SUM, COUNT, AVG, MIN, MAX etc)
14.
Guide On Rating System
Vote
GROUP BY
15. Subquery
1. What is the total number of orders placed?
SELECT COUNT(*) FROM Orders;
2. How many different customers have placed orders?
SELECT COUNT(DISTINCT customer_id) FROM Orders;
3. What is the full address of the customer with ID 123?
SELECT CONCAT(address, ', ', city, ', ', state, ', ', country) AS Full_Address FROM Customers WHERE customer_id = 123;
4. How many orders were placed between January 1, 2020, and December 31, 2020?
SELECT COUNT(*) FROM Orders WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';
5. What is the total revenue generated from orders placed by customer ID 456?
SELECT SUM(total_amount) FROM Orders WHERE customer_id = 456;
6. How many products have a price greater than $50?
SELECT COUNT(*) FROM Products WHERE price > 50;
7. Which orders were placed by customers in California or New York?
SELECT * FROM Orders WHERE customer_state IN ('California', 'New York');
8. Get the latest order placed by customer with ID 789.
SELECT * FROM Orders WHERE customer_id = 789 ORDER BY order_date DESC LIMIT 1;
9. Which customers have not placed any orders?
SELECT * FROM Customers WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM Orders);
10. Find all customers whose name starts with 'J'.
SELECT * FROM Customers WHERE customer_name LIKE 'J%';
11. Retrieve the product name and category name for all products.
SELECT Products.product_name, Categories.category_name FROM Products
JOIN Categories ON Products.category_id = Categories.category_id;
12. Sort the orders by total amount in descending order.
SELECT * FROM Orders ORDER BY total_amount DESC;
13. Calculate the average amount of orders placed.
SELECT AVG(total_amount) FROM Orders;
14. Find the product with the highest price.
SELECT MAX(price) FROM Products;
15. Retrieve the order details for orders placed by customers in California.
SELECT * FROM Orders WHERE customer_state = 'California';
Got a question with my answer?
Cheapmath.com is completely free!
Asking question is free.
Getting answers is free.
+99