Scribd
Upload
53 views9 pages

QUERIES FOR PRACTICE(simple)

The document contains a series of SQL queries for practicing database operations, including creating tables, inserting values, and retrieving specific data from 'Products' and 'Sales' tables. It also includes queries for filtering, calculating totals, and using nested subqueries to analyze employee and customer data. Additionally, it provides examples of formatting and aggregating results in SQL.

Uploaded by

saarthak22305
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
53 views9 pages

QUERIES FOR PRACTICE(simple)

The document contains a series of SQL queries for practicing database operations, including creating tables, inserting values, and retrieving specific data from 'Products' and 'Sales' tables. It also includes queries for filtering, calculating totals, and using nested subqueries to analyze employee and customer data. Additionally, it provides examples of formatting and aggregating results in SQL.

Uploaded by

saarthak22305
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 9

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 emp1.role = emp2.role );

6. SELECT role, AVG(salary) FROM Employees GROUP BY role;

You might also like