Assignment DBMS-4:
CREATE DATABASE IF NOT EXISTS Employee;
USE employee;
CREATE TABLE Sales(
SalesID int NOT NULL AUTO_INCREMENT,
Product varchar(50),
Category varchar(50),
Quantity int,
Prince decimal(10,2),
SaleDate date,
PRIMARY KEY (SalesID)
);
INSERT INTO sales(Product,Category,Quantity,Price,SaleDate)VALUES
("MotherBoard","Electronics",20,13000.00,"21-2-2024"),
("RAM","Electroncis",70,7000,"22-2-2024"),
("Chair","Furniture",20,10000,"23-2-2024"),
("Desk","Furniture",10,5000,"24-2-2024"),
("Water-bottle","Meterials",15,200,"25-2-2024")
Questions & Answers:
1. what is the totla revenue generated ?
Ans: SELECT SUM([Link]*[Link]) FROM `sales` WHERE 1
2. How many products have been sold in total?
Ans: SELECT SUM([Link]) FROM `sales` WHERE 1
3. What ist he average price of product?
Ans: SELECT AVG([Link]) FROM `sales` WHERE 1;
4. How many unique product categories exist?
Ans: SELECT COUNT(DISTINCT [Link]) FROM `sales` WHERE 1;
5. What is the highest price of a product in the table?
Ans: SELECT MAX([Link]) FROM `sales` WHERE 1;
6. Wha is the lowest price of a product in the table?
Ans: SELECT MIN([Link]) FROM `sales` WHERE 1;
7. What is the average quantity sold per transaction?
Ans: SELECT AVG([Link]) AS avrg_qunatity_sold_per_transaction FROM
`sales` WHERE 1;
8. How many total sales records are there in the table?
Ans: SELECT COUNT([Link]) AS total_seles_record FROM `sales` WHERE 1;
9. What is the total revenue for each product category?
Ans: SELECT [Link] ,SUM([Link]*[Link]) AS
total_revenue_each_product_catagory FROM `sales` GROUP BY [Link];
10. What is the average price of products in each
category?
Ans: SELECT [Link] ,AVG([Link]) AS average_price_each_product FROM
`sales` GROUP BY [Link];
11. Which category has the highest total revenue?
Ans: SELECT [Link] , MAX([Link] *[Link]) AS
highest_total_revenue FROM `sales` GROUP BY [Link];
12. What is the total quantity sold for each product?
Ans: SELECT SUM([Link]) AS total_quantity FROM `sales` GROUP by
[Link];
13. What is the maximum quantity sold in a single
transaction?
Ans: SELECT MAX([Link]) AS max_qunatity_single_transaction FROM `sales`
WHERE 1;
14. What is the total revenue for a specific product?
Ans: for MotherBoard:-
SELECT SUM([Link]*[Link]) AS product_total_revenue FROM `sales`
WHERE [Link] = "MotherBoard";
Assignment DBMS-5:
CREATE DATABASE Employees;
use employees;
CREATE TABLE employee(
EmployeeID int AUTO_INCREMENT,
Name varchar(50),
Department varchar(20),
Salary int,
Age int,
joiningDate date,
City varchar(10),
PRIMARY KEY(EmployeeID)
);
INSERT INTO employee(Name,Department,Salary,Age,joiningDate,City)VALUES
("Alice","HR",50000,30,"2018-02-10","New York"),
("Bob","IT",70000,35,"2016-05-21","San Diego"),
("Charlie","IT",80000,40,"2015-08-19","San Diego"),
("Diana","HR",60000,29,"2019-11-12","New York"),
("Eve","Finance",75000,32,"2017-03-15","Boston"),
("Frank","Finance",55000,38,"2019-07-08","Boston"),
("Grace","IT",90000,28,"2020-06-01","Chicago"),
("Hannah","HR",65000,31,"2021-10-11","New York"),
("Issac","IT",72000,45,"2014-01-25","San Diego"),
("Jacob","Finance",50000,50,"2013-12-30","Boston")
Questions & Answers:
1. Write a query to find the total salary paid in each
department.
Ans: SELECT [Link],SUM([Link]) AS total_salary_department
FROM `employee` GROUP BY [Link];
2. How many employees are there in each department?
Ans: SELECT [Link],COUNT([Link]) AS
employee_in_each_dept FROM `employee` GROUP BY [Link];
3. Find departments where the total salary exceeds
150000
Ans: SELECT [Link] FROM `employee` GROUP BY [Link]
HAVING SUM([Link]) > 150000;
4. What ist he average salary in each department?
Ans: SELECT AVG([Link]) FROM `employee` GROUP BY [Link];
5. Retrieve the employee details sorted by their joining
date in descending order?
Ans: SELECT * FROM `employee` ORDER BY [Link] DESC;
6. List departments with their average employee age ,
sorted by average age in ascending order
Ans: SELECT AVG([Link])FROM `employee`GROUP BY [Link] ORDER
BY AVG([Link]) ASC ;
7. Find cities with more than 2 employees
Ans SELECT [Link] FROM `employee` GROUP BY City HAVING
COUNT([Link]) > 2 ;
8. Get the top 3 hightest-paid employees
Ans SELECT * FROM `employee` ORDER BY [Link] DESC LIMIT 3;
9. Group employees by department and city , and find the
total salary in each group
Ans: SELECT [Link],[Link] , SUM([Link]) AS
total_salary_each_group FROM `employee` GROUP BY [Link],
[Link] ;
10. Find the highest salary in each department
Ans: SELECT MAX([Link]) FROM `employee` GROUP BY [Link] ;