-----------------------------------------------------------------
Max Salary in all departments

USE SalaryDB;

SELECT Dept_Name, MAX(Salary) AS Max_Salary
FROM Employees
GROUP BY Dept_Name
order by Max_Salary DESC;

-------------------------------------------------------------------
High salary, Medium salary, Low salary

USE EmpDb;

SELECT
employee_id,
first_name,
last_name,
salary,
CASE
WHEN salary >= 50000 THEN 'High Salary'
WHEN salary BETWEEN 30000 AND 49999 THEN 'Medium Salary'
ELSE 'Low Salary'
END AS salary_category
FROM
employees
ORDER BY
employee_id;

-------------------------------------------------------------------
Example.com generated email

USE EmpDb;

SELECT
employee_id,
first_name,
last_name,
email,
CONCAT(SUBSTRING(first_name, 1, 3), SUBSTRING(last_name, 1, 4), '@example.com') AS generated_email
FROM
employees
ORDER BY
employee_id;

----------------------------------------------------------------------
Total amount exceed 500 dollars

USE Customerdb;

SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM
orders
GROUP BY
customer_id
HAVING
COUNT(order_id) > 2 AND SUM(total_amount) > 500
ORDER BY
customer_id;


-- ----------------------------------------------------------------------
Salary of employee greater than manager Salary

USE EmployeeData

SELECT
e.empid AS EMPID,
e.first_name AS EMPNAME,
e.salary AS EMPSALARY,
m.salary AS MANAGERSALARY
FROM
EmployeeData e
JOIN
EmployeeData m ON e.managerid = m.empid
WHERE
e.salary > m.salary
ORDER BY
e.empid ASC;

-------------------------------------------------------------------------------------------
Class have more than 3 students assigned to it

use studentClassDB

SELECT Class
FROM Courses
GROUP BY Class
HAVING COUNT(*) >= 3
ORDER BY Class ASC;


-----------------------------------------------------
Most expensive_product in each product category

USE ProductDb;

SELECT
category,
product_name AS most_expensive_product,
MAX(price) AS max_price
FROM
products
GROUP BY
category, product_name
HAVING
price = (SELECT MAX(price) FROM products p2 WHERE p2.category = products.category)
ORDER BY
max_price ASC;



-------------------------------------------------------------------------------------------
total amount spent by each customer


SELECT customer.customer_id, SUM(order.total_amount) AS total_spent
FROM customer
INNER JOIN order ON customer.customer_id = order.customer_id
GROUP BY customer.customer_id;



-----------------------------------------------------------
Current previous revenue difference

USE EmployeeDB;

SELECT RecordDate,
Revenue AS CurrentDayRevenue,
(SELECT Revenue FROM DailyRevenue WHERE RecordDate = DATE_SUB(RecordDate, INTERVAL 1 DAY)) AS PreviousDayRevenue,
(Revenue - (SELECT Revenue FROM DailyRevenue WHERE RecordDate = DATE_SUB(RecordDate, INTERVAL 1 DAY))) AS Difference
FROM
DailyRevenue
WHERE
RecordDate = CURDATE() OR RecordDate = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
ORDER BY
RecordDate ASC;



-----------------------------------------------------------------------------------------
Total salary expenditure for the Company


USE EMPLOYEEDATA;
SELECT SUM(Salary) as TotalExpenditure from EMP;



------------------------------------------------------------------------------------------
students enrolled in Python Course

use DB_Institute;

SELECT E.STUDENTID AS StudentID, E.STUDENTNM AS Name,
E.DTENROLL AS DateOfEnrollment, F.FEESPAID AS FeesPaid, F.FEESDUE AS FeesDue,
F.PAYMENTDATE as PaymentDate FROM
(SELECT * FROM TBL_STUDENTS_ENROLL WHERE STUDENTID NOT IN
(SELECT E.STUDENTID
FROM TBL_STUDENTS_ENROLL E JOIN
TBL_STUDENTS_FEES F ON
E.STUDENTID = F.STUDENTID
AND E.COURSENM = 'PYTHON') AND COURSENM = 'PYTHON') E
LEFT JOIN
TBL_STUDENTS_FEES F
ON E.STUDENTID = F.STUDENTID
ORDER BY E.STUDENTNM;


-----------------------------------------------------------------------------------------
List of all customers and total no of orders they place

USE CustomerDb;
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) as total_orders from 
customers c LEFT JOIN orders o on c.customer_id = o.customer_id
GROUP by c.customer_id
order by c.customer_id;


------------------------------------------------------------------------------------------
Percentage Increment in Salary

USE EmployeeDB;
SELECT 
    es1.EmployeeID,
    es1.Year,
    es1.Salary AS CurrentSalary,
    es2.Salary AS PreviousYearSalary,
    ((es1.Salary - es2.Salary) * 100 / es2.Salary)
     AS PercentageIncrement
FROM 
    EmployeeSalaries es1
LEFT JOIN 
    EmployeeSalaries es2 ON es1.EmployeeID = es2.EmployeeID AND es1.Year = es2.Year + 1
ORDER BY 
    es1.EmployeeID ASC, 
    es1.Year ASC;

-----------------------------------------------------------------------------------------
ER diagaram vala question

USE DoSelect;

SELECT
Genre.name AS Name,
AVG(Track.unitprice) AS AvgUnitPrice
FROM
Track
JOIN
Genre ON Track.genreid = Genre.genreid
GROUP BY
Genre.name
ORDER BY
AvgUnitPrice DESC, Name
LIMIT 10;


------------------------------------------------------------------------------------------
Third higest salary

USE DB_Company;
select distinct salary from TBL_Employee ORDER  by salary DESC limit 1 offset 2;


------------------------------------------------------------------------------------------
Top 3 employees with higest salary

USE EmployeeDB;
select employee_id, employee_name, salary from Employees ORDER by salary DESC limit 3 offset 0;


------------------------------------------------------------------------------------------
Salary greater than 30k or name starts with s

USE EMPLOYEEDATA;
select Emp_Name, Salary from EMP WHERE salary > 30000 or Emp_Name LIKE "S%";



------------------------------------------------------------------------------------------
salary less than 50k and more than or equal to 70k

USE EmployeeDB;
select distinct department_id FROM employees WHERE salary <50000 or salary >=70000 ORDER BY department_id;


------------------------------------------------------------------------------------------
Louis in HR Department

USE DB_Company;
SELECT
Department AS Department,
COUNT(ID) AS No_of_Employees
FROM
Employees
GROUP BY
Department
ORDER BY
Department ASC;


------------------------------------------------------------------------------------------
Employees details with salaries in each Department( include null)

USE CompanyDB;
SELECT 
    d.department_id AS department_id,
    d.department_name AS department_name,
    e.first_name AS first_name,
    e.last_name AS last_name,
    e.salary AS salary
FROM 
    departments d
LEFT JOIN 
    employees e ON d.department_id = e.department_id
ORDER BY 
    d.department_id ASC;
    
    
------------------------------------------------------------------------------------------
total employees living in each particular address.

USE EMPLOYEEDATA;
SELECT Emp_Add, COUNT(*) as No_of_Employees
from employees group by Emp_Add
order by Emp_Add ASC;


------------------------------------------------------------------------------------------
select student by grade category

USE GradeDb;
select student_id, first_name, last_name, grade,
case
when grade >= 90 then "Excellent",
when grade between 80 and 89 then "Good"
when grade between 70 and 79 then "Average"
else "Needs Improvement"
end as grade_category
from students
order by student_id asc;


------------------------------------------------------------------------------------------
combines student and teacher with their roles

use SchoolDb;
SELECT first_name, last_name,
'Student' AS role
FROM Students
UNION ALL
SELECT first_name, last_Name,
'Teacher' AS role
FROM teachers
ORDER BY first_name;

------------------------------------------------------------------------------------------
Employees in sales and Marketing Department

use EmployeeDB;
SELECT
e.employee_name AS employee_name
FROM
Employees e
JOIN
Departments d on
e.department_id = d.department_id
WHERE
d.department_name IN ('Sales', 'Marketing')
ORDER BY
e.employee_name ASC;

----------------------------------------------------------------------------------------------

-- Write an SQL query to find customers who:

-- Placed more than 2 orders where the total amount of each order exceeds $500.
-- Output the following columns:
-- customer_id: ID of the customer.
-- total_orders: The total number of orders placed by the customer.
-- Ensure the results are sorted in ascending order of customer_id.

SELECT A.CUSTOMER_ID, B.TOTAL_ORDERS
FROM 
    (SELECT CUSTOMER_ID, COUNT(ORDER_ID) AS TOTAL_ORDERS
     FROM ORDERS
     WHERE TOTAL_AMOUNT > 500.00
     GROUP BY CUSTOMER_ID) A
JOIN 
    (SELECT CUSTOMER_ID, COUNT(ORDER_ID) AS TOTAL_ORDERS
     FROM ORDERS
     GROUP BY CUSTOMER_ID) B
ON A.CUSTOMER_ID = B.CUSTOMER_ID AND A.TOTAL_ORDERS >= 2
ORDER BY A.CUSTOMER_ID;


-- -- Construct a query to display the Publication, Language, and the count of books (CountofBooks) 
-- according to publication and language. 
-- The result should be in the increasing order of the Publication.

SELECT  Publication  AS  'Publication', Language  AS  'Language', COUNT(*)  AS 'CountofBooks'  
FROM  TBL_Books  GROUP BY  Publication, Language  
ORDER BY  Publication ASC;


-- Louis is working in the HR Department.
-- He wants to prepare data that shows each department's wise count of employees. 
-- Construct a query that displays the department as  Department  and the number of employees for each department as No_of_Employees.
-- Display the output in an ascending order of the Department.

SELECT  D.Department AS Department, COUNT(E.ID) AS No_of_Employees
FROM  TBL_Department D
LEFT JOIN  TBL_Employee E ON D.ID = E.DepartmentID
GROUP BY  D.Department
ORDER BY  D.Department ASC;


-- Write a SQL query to retrieve a list of all customers and the total number of orders they have placed, even if they haven't placed any orders.
-- The result should be in the increasing order of the customer_id. 
-- The output should contain the following columns:  customer_id, customer_name, total_orders

SELECT  C.Customer_id AS customer_id, C.customer_name AS customer_name,COUNT(O.order_id) AS total_orders
FROM Customers C
LEFT JOIN Orders O ON  C.Customer_id = O.customer_id
GROUP BY  C.Customer_id, C.customer_name
ORDER BY  C.Customer_id ASC;


-- Find the total no of employees living in each particular address. 
-- Display the result in the increasing order of Emp_Add. 
SELECT Emp_Add,COUNT(Emp_NO) AS No_of_Employees
FROM  EMP
GROUP BY  Emp_Add
ORDER BY  Emp_Add ASC;

-- You work for an e-commerce company that sells products in multiple categories. 
-- The company has two tables: "Categories" and "Products".
-- Your task is to generate a report that lists all products as product_name along with their corresponding category names as category_name.

SELECT  Categories.category_name,  Products.product_name
FROM  Categories
INNER JOIN  Products
ON  Categories.category_id = Products.category_id
ORDER BY  Categories.category_name ASC,  Products.product_name ASC;


-- CATEGORIZE EMPLOYEES INTO 3 GROUPS BASED ON THEIR SALARY
SELECT  employee_id,first_name,last_name,salary,
    CASE
        WHEN salary >= 50000 THEN 'High Salary'
        WHEN salary BETWEEN 30000 AND 49999 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM  employees
ORDER BY employee_id ASC;


-- third highest salary
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- salary increment of Employee
SELECT 
    e1.EmployeeID AS 'EmployeeID',
    e1.Year AS 'Year',
    e1.Salary AS 'CurrentSalary',
    e2.Salary AS 'PreviousYearSalary',
    ROUND(((e1.Salary - e2.Salary) / e2.Salary) * 100, 2) AS 'PercentageIncrement'
FROM 
    EmployeeSalaries e1
LEFT JOIN 
    EmployeeSalaries e2
ON 
    e1.EmployeeID = e2.EmployeeID
    AND e1.Year = e2.Year + 1
ORDER BY 
    e1.EmployeeID, e1.Year;
    
    
--address of Employee
SELECT Emp_Add AS 'Emp_Add',COUNT(*) AS 'No_of_Employees'
FROM  Employees
GROUP BY   Emp_Add
ORDER BY  Emp_Add ASC;


-- Problem Statement
-- * You work for an e-commerce company that sells products in multiple categories. The company has two tables: "Categories" and "Products".
-- * Your task is to generate a report that lists all products as product_name along with their corresponding category names as category_name.

SELECT Categories.category_name AS category_name,Products.product_name AS product_name
FROM Products
INNER JOIN Categories
ON Products.category_id = Categories.category_id
ORDER BY Categories.category_name ASC;

-- Retrieve product name which is most expensive
SELECT  p.category AS 'category', p.product_name AS 'most_expensive_product', p.price AS 'max_price'
FROM  Products p
JOIN 
    (SELECT 
        category,
        MAX(price) AS max_price
    FROM 
        Products
    GROUP BY 
        category) max_prices
ON  p.category = max_prices.category AND p.price = max_prices.max_price
ORDER BY max_prices.max_price ASC;

--Write a SQL query to display the names of employees concatenated with their job IDs separated by a comma and space. Name the resulting column as emp_name job.

SELECT CONCAT(first_name, ' ', last_name, ', ', job_id) AS emp_name_job
FROM employees
ORDER BY emp_name_job ASC;

--Example.com generated email