Name:Shimanta Deb Nath
REG:939
SOLUTION OF SQL-4
First of all we have to create a database.
SQL: CREATE DATABASE Shimanta_shop;
Then we have to create a table naming by ‘Sales’
SQL: CREATE TABLE Sales (
SalesID INT PRIMARY KEY AUTO_INCREMENT,
Product VARCHAR(100),
Category VARCHAR(50),
Quantity INT,
Price DECIMAL(10, 2),
SaleDate DATE
);
Now we have to input some sample data.
SQL: INSERT INTO Sales (Product, Category, Quantity, Price, SaleDate) VALUES
('Laptop', 'Electronics', 3, 1200.50, '2025-01-10'),
('Smartphone', 'Electronics', 5, 800.00, '2025-01-11'),
('Refrigerator', 'Appliances', 2, 1500.00, '2025-01-12'),
('Microwave', 'Appliances', 4, 300.00, '2025-01-13'),
('Tablet', 'Electronics', 6, 450.00, '2025-01-14');
1. Answer: SELECT SUM(Quantity * Price) AS TotalRevenue FROM Sales;
2. Answer: SELECT SUM(Quantity) AS TotalProductsSold FROM Sales;
3. Answer: SELECT AVG(Price) AS AveragePrice FROM Sales;
4. Answer: SELECT COUNT(DISTINCT Category) AS UniqueCategories FROMSales;
5. Answer: SELECT MAX(Price) AS HighestPrice FROM Sales;
6. Answer: SELECT MIN(Price) AS LowestPrice FROM Sales;
7. Answer: SELECT AVG(Quantity) AS AverageQuantityPerTransaction FROM Sales;
8. Answer: SELECT COUNT(SalesID) AS TotalSalesRecords FROM Sales;
9. Answer: SELECT Category, SUM(Quantity * Price) AS TotalRevenue FROM Sales GROUP BY Category;
10. Answer: SELECT Category, AVG(Price) AS AveragePrice
FROM Sales GROUP BY Category;
11.Answer: SELECT Category, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Category
ORDER BY TotalRevenue DESC
LIMIT 1;
12.Answer: SELECT Product, SUM(Quantity) AS TotalQuantitySold
FROM Sales
GROUP BY Product;
13:Answer: SELECT MAX(Quantity) AS MaxQuantityInSingleTransaction
FROM Sales;
14.Answer: SELECT SUM(Quantity * Price) AS TotalRevenueForLaptop
FROM Sales
WHERE Product = 'Laptop';
SOLUTION OF SQL-5
1. GROUP BY: Write a query to find the total salary paid in each department.
2. GROUP BY with COUNT: How many employees are there in each department?
3. HAVING: Find departments where the total salary exceeds 150,000.
4. GROUP BY with AVG: What is the average salary in each department?
5. ORDER BY: Retrieve the employee details sorted by their joining date in descending order.
6. GROUP BY and ORDER BY: List departments with their average employee age, sorted by
average age in ascending order.
7. GROUP BY with HAVING and COUNT: Find cities with more than 2 employees.
8. ORDER BY with LIMIT: Get the top 3 highest-paid employees.
9. GROUP BY with Multiple Columns: Group employees by department and city, and find the
total salary in each group.
10. GROUP BY with MAX: Find the highest salary in each department.
**From the given table
1.Answer: GROUP BY: Total salary paid in each department
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;
2..Answer: GROUP BY with COUNT: Number of employees in each department
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
3..Answer: HAVING: Departments where total salary exceeds 150,000
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
HAVING SUM(Salary) > 150000;
4. Answer: GROUP BY with AVG: Average salary in each department
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;
5 Answer: ORDER BY: Employee details sorted by joining date (descending)
SELECT *
FROM Employees
ORDER BY JoiningDate DESC;
6: Answer: GROUP BY and ORDER BY: Departments with average employee age sorted by average age
(ascending)
SELECT Department, AVG(Age) AS AverageAge
FROM Employees
GROUP BY Department
ORDER BY AverageAge ASC;
7: Answer: GROUP BY with HAVING and COUNT: Cities with more than 2 employees
SELECT City, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY City
HAVING COUNT(*) > 2;
8: Answer: ORDER BY with LIMIT: Top 3 highest-paid employees
SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 3;
9: Answer: GROUP BY with Multiple Columns: Total salary by department and city
SELECT Department, City, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department, City;
10: Answer: GROUP BY with MAX: Highest salary in each department
SELECT Department, MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;
