QUERIES FOR PRACTICE
1. Create table products and insert values into the
table
2. Create table Sales and insert values into the
table
3. Retrieve the sale_id and sale_date from the Sales
table.
4. Filter the Sales table to show only sales with a
total_price greater than $100.
5. Filter the Products table to show only products
in the 'Electronics' category.
6. Retrieve the sale_id and total_price from the
Sales table for sales made on January 3, 2024.
7. Calculate the total revenue generated from all
sales in the Sales table.
8. Calculate the average unit_price of products in
the Products table.
9. Calculate the total quantity_sold from the Sales
table. And name the field as total_sold.
10. Count Sales Per Day from the Sales table
11. Retrieve the sale_id, product_id, and total_price
from the Sales table for sales with a
quantity_sold greater than 4.
12. Retrieve product_name and unit_price from the
Products table with the Highest Unit Price
13. Retrieve the product_name and unit_price from
the Products table, ordering the results by
unit_price in descending order.
14. Retrieve the total_price of all sales, rounding
the values to two decimal places.
15. Retrieve the sale_id and sale_date from the
Sales table, formatting the sale_date as 'YYYY-
MM-DD'.
SELECT product_name, unit_price
FROM Products
ORDER BY unit_price DESC
LIMIT 1;
SELECT product_name, unit_price
FROM Products
ORDER BY unit_price DESC;
SELECT ROUND(SUM(total_price), 2) AS total_sales
FROM Sales;
SELECT sale_id, DATE_FORMAT(sale_date, '%Y-
%m-%d') AS formatted_date
FROM Sales;
1. Display Orders Issued by Salesman 'Paul Adam'
2. Display Orders Generated by London-Based Salespeople
3. Display Orders from Salespeople Handling Customer ID 3007
4. Display Orders Exceeding Average Value on 10-Oct-2012
5. Display Orders Generated in New York City
6. Display Commission of Salespeople in Paris
7. Display Customers with ID Below 2001 Under Salesperson Mc Lyon
8. Count of Customers with Above-Average Grades in New York City
9. Display Orders of Salespeople with Maximum Commission
10. Display Customers Who Placed Orders on 17th August 2012
7. -- Selecting all columns from the 'customer' table
SELECT * FROM customer WHERE customer_id = (SELECT salesman_id - 2001 FROM
salesman WHERE name = 'Mc Lyon');
8. -- Selecting the 'grade' column and the count of rows from the 'customer' table
SELECT grade, COUNT(*) FROM customer
GROUP BY grade
HAVING grade >
(SELECT AVG(grade)
FROM customer
WHERE city = 'New York');
9. -- Selecting specific columns from the 'orders' table
SELECT ord_no, purch_amt, ord_date, salesman_id )
FROM orders
WHERE salesman_id IN (
SELECT salesman_id
FROM salesman
WHERE commission = (
SELECT MAX(commission)
FROM salesman ));
-- Selecting all columns from the 'orders' table and the 'cust_name' column from the
'customer' table
SELECT b.*, a.cust_name
FROM orders b, customer a
WHERE a.customer_id = b.customer_id
AND b.ord_date = '2012-08-17';
Nested Subqueries and Corelated Subqueries
1. Select all employees who won an award.
2. Select all employees who never won an award.
3. Select all Developers who earn more than all the Managers
4. Select all Developers who earn more than any Manager
5. Select all employees whose salary is above the average salary of employees in their
role.
6. Find the average salary of managers and developers using the below query
Solutions
1. SELECT id, name FROM Employees WHERE id NOT IN (SELECT employee_id FROM
Awards);
2. SELECT id, name FROM Employees WHERE id NOT IN (SELECT employee_id FROM
Awards);
3. SELECT * FROM Employees WHERE role = 'Developer' AND salary > ALL ( SELECT salary
FROM Employees WHERE role = 'Manager');
4. SELECT * FROM Employees WHERE role = 'Developer' AND salary > ANY ( SELECT salary
FROM Employees WHERE role = 'Manager');
5. SELECT * FROM Employees emp1 WHERE salary > ( SELECT AVG(salary) FROM Employees
emp2 WHERE [Link] = [Link] );
6. SELECT role, AVG(salary) FROM Employees GROUP BY role;