SQL ASSIGNMENT
Write sql queries to retrieve data from the following “employees” table and sort the results
as specified.
Employeeid Firstname Lastname Department Salary Hiredate
1 John Doe It 60000 2019-03-15
2 Jane Smith Hr 65000 2018-06-10
3 Alice Johnson It 70000 2020-11-20
4 Bob Brown Marketing 45000 2017-09-30
5 Charlie Wilson It 80000 2016-12-01
o Write a query to select all columns from the employees table and sort the results
by lastname in ascending order.
o Write a query to select firstname, lastname, and salary from the employees
table and sort the results by salary in descending order.
Write sql queries to filter data from the products table based on the specified conditions.
Productid Productname Category Price Stockquantity
1 Laptop Electronics 1000 50
2 Phone Electronics 800 100
3 Tv Electronics 1500 30
4 Blender Home appliances 200 80
5 Microwave Home appliances 300 60
o Write a query to select all columns from the products table where category is
'electronics'.
o Write a query to select productname and price from the products table where
price is greater than 500 and stockquantity is less than 100.
Write sql queries to calculate aggregate values from the sales table.
Saleid Productid Quantity Saleamount Saledate
1 1 5 5000 2023-01-01
2 2 10 8000 2023-01-02
3 3 3 4500 2023-01-03
5 5 8 1600 2023-01-04
4 4 8 1600 2023-01-04
o Write a query to calculate the total saleamount from the sales table.
o Write a query to calculate the average saleamount from the sales table.
o Write a query to calculate the total quantity of sales for the product with
productid = 1.
Write sql queries that combine querying, sorting, filtering, and aggregate functions using the
orders table.
SQL ASSIGNMENT
Orderid Customerid Orderdate Totalamount
1 101 2023-06-01 1500
2 102 2023-06-02 2000
3 103 2023-06-03 2500
4 104 2023-06-04 1800
5 101 2023-06-05 2200
o Write a query to select all columns from the orders table where totalamount is
greater than 2000 and sort the results by orderdate in descending order.
o Write a query to calculate the total totalamount for all orders placed in june 2023.
o Write a query to select customerid and the sum of totalamount for each
customer, grouped by customerid.