SQL quick recap

-- Employee (emp_id, name, salary, dept_id, manager_id, joining_date)
-- Department (dept_id, dept_name)
-- Customer (cust_id, name, city, phone)
-- Orders (order_id, cust_id, product_id, order_date, amount)
-- Product (product_id, name, price, stock)
-- Student (id, name, city, marks)

-- Display all records from Employee table
SELECT * FROM Employee;

-- Fetch employee names and salaries where salary > 50000
SELECT name, salary FROM Employee WHERE salary > 50000;

-- Get all customers who live in 'Delhi'
SELECT * FROM Customer WHERE city = 'Delhi';

-- Display unique city names from Customer table
SELECT DISTINCT city FROM Customer;

-- Show products with price between 500 and 2000
SELECT * FROM Product WHERE price BETWEEN 500 AND 2000;

-- Display employee names in uppercase
SELECT UPPER(name) FROM Employee;

-- Extract first 3 characters of employee name
SELECT SUBSTRING(name, 1, 3) FROM Employee;

-- Count employees who joined in 2023
SELECT COUNT(*) FROM Employee 
WHERE YEAR(joining_date) = 2023;

-- Display current date and time
SELECT GETDATE(); -- SQL Server
-- SELECT NOW(); -- MySQL
-- SELECT SYSDATE FROM DUAL; -- Oracle

-- Round salary to nearest thousand
SELECT name, ROUND(salary, -3) AS rounded_salary FROM Employee;

-- Fetch students whose names start with 'A'
SELECT * FROM Student WHERE name LIKE 'A%';

-- Fetch employees in specific departments
SELECT * FROM Employee 
WHERE department IN ('HR', 'IT', 'Finance');

-- Find employees with joining date in 2023
SELECT * FROM Employee 
WHERE joining_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Sort product list by price (highest first)
SELECT * FROM Product ORDER BY price DESC;

-- Show customers whose phone number ends with '55'
SELECT * FROM Customer WHERE phone LIKE '%55';

-- Count employees in each department
SELECT department, COUNT(*) as employee_count 
FROM Employee 
GROUP BY department;

-- Departments where average salary > 60000
SELECT department, AVG(salary) as avg_salary
FROM Employee 
GROUP BY department 
HAVING AVG(salary) > 60000;

-- Total sales per customer
SELECT cust_id, SUM(amount) as total_sales
FROM Orders 
GROUP BY cust_id;

-- Orders placed each month in 2024
SELECT MONTH(order_date) as month, COUNT(*) as order_count
FROM Orders 
WHERE YEAR(order_date) = 2024
GROUP BY MONTH(order_date);

-- Products sold more than 50 times
SELECT product_id, COUNT(*) as times_sold
FROM Orders 
GROUP BY product_id 
HAVING COUNT(*) > 50;

-- Employee name with department name (INNER JOIN)
SELECT e.name, d.dept_name 
FROM Employee e 
INNER JOIN Department d 
ON e.dept_id = d.dept_id;

-- All departments even if no employees (RIGHT JOIN)
SELECT d.dept_name, e.name 
FROM Employee e 
RIGHT JOIN Department d 
ON e.dept_id = d.dept_id;

-- Customers and their orders (LEFT JOIN)
SELECT c.name, o.order_id, o.order_date 
FROM Customer c 
LEFT JOIN Orders o 
ON c.cust_id = o.cust_id;

-- Employees who have NO manager (SELF JOIN)
SELECT e1.name 
FROM Employee e1 
LEFT JOIN Employee e2 
ON e1.manager_id = e2.emp_id 
WHERE e1.manager_id IS NULL;

-- Customer name and total order value
SELECT c.name, SUM(o.amount) as total_value
FROM Customer c 
JOIN Orders o 
ON c.cust_id = o.cust_id
GROUP BY c.name;

-- Employees with salary > average salary
SELECT * FROM Employee 
WHERE salary > (SELECT AVG(salary) FROM Employee);

-- Customers who never placed an order
SELECT * FROM Customer 
WHERE cust_id NOT IN (SELECT DISTINCT cust_id FROM Orders);

-- Employees with second highest salary
SELECT * FROM Employee 
WHERE salary = (
    SELECT MAX(salary) FROM Employee 
    WHERE salary < (SELECT MAX(salary) FROM Employee)
);

-- Products with price > minimum price
SELECT * FROM Product 
WHERE price > (SELECT MIN(price) FROM Product);

-- Departments where employee count > company average
SELECT department, COUNT(*) as emp_count
FROM Employee 
GROUP BY department
HAVING COUNT(*) > (
    SELECT AVG(emp_count) FROM (
        SELECT COUNT(*) as emp_count 
        FROM Employee 
        GROUP BY department
    ) dept_counts
);

-- Create Student table
CREATE TABLE Student (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    marks DECIMAL(5,2)
);

-- Add email column
ALTER TABLE Student ADD email VARCHAR(100);

-- Modify salary datatype
ALTER TABLE Employee ALTER COLUMN salary DECIMAL(10,2);

-- Drop department table
DROP TABLE Department;

-- Rename Employee to Staff
EXEC sp_rename 'Employee', 'Staff'; -- SQL Server
-- ALTER TABLE Employee RENAME TO Staff; -- MySQL

-- Insert 3 rows into Student
INSERT INTO Student (id, name, city, marks) VALUES
(1, 'John Doe', 'Mumbai', 85.5),
(2, 'Jane Smith', 'Delhi', 92.0),
(3, 'Bob Wilson', 'Chennai', 78.5);

-- Update city of student with id=5
UPDATE Student SET city = 'Mumbai' WHERE id = 5;

-- Delete employees with salary < 20000
DELETE FROM Employee WHERE salary < 20000;

-- Increase all salaries by 10%
UPDATE Employee SET salary = salary * 1.10;

-- Insert data from one table to another
INSERT INTO Employee_Backup 
SELECT * FROM Employee WHERE dept_id = 1;

-- Create table with constraints
CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2) CHECK (salary > 10000),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- Add UNIQUE constraint
ALTER TABLE Employee 
ADD CONSTRAINT UQ_Employee_Email UNIQUE (email);

-- Add CHECK constraint
ALTER TABLE Employee 
ADD CONSTRAINT CHK_Salary CHECK (salary > 10000);

-- Add FOREIGN KEY
ALTER TABLE Employee 
ADD CONSTRAINT FK_Employee_Department 
FOREIGN KEY (dept_id) REFERENCES Department(dept_id);

-- Remove constraint
ALTER TABLE Employee 
DROP CONSTRAINT CHK_Salary;

-- WHERE vs HAVING example
SELECT department, AVG(salary) as avg_salary
FROM Employee 
WHERE joining_date > '2023-01-01'  -- Filters rows before grouping
GROUP BY department 
HAVING AVG(salary) > 50000;        -- Filters after grouping

-- EXISTS vs IN
SELECT name FROM Employee e
WHERE EXISTS (
    SELECT 1 FROM Projects p 
    WHERE p.emp_id = e.emp_id
);

-- DISTINCT with GROUP BY
SELECT DISTINCT department, COUNT(*) 
FROM Employee 
GROUP BY department;

-- First 5 highest salaries
SELECT TOP 5 salary FROM Employee ORDER BY salary DESC; -- SQL Server
-- SELECT salary FROM Employee ORDER BY salary DESC LIMIT 5; -- MySQL

-- CASE for grade assignment
SELECT name, marks,
    CASE 
        WHEN marks >= 90 THEN 'A'
        WHEN marks >= 80 THEN 'B' 
        WHEN marks >= 70 THEN 'C'
        ELSE 'D'
    END as grade
FROM Student;

-- Create view with employee name and department
CREATE VIEW EmployeeDeptView AS
SELECT e.name, d.dept_name 
FROM Employee e 
JOIN Department d ON e.dept_id = d.dept_id;

-- View for employees with salary > 50000
CREATE VIEW HighSalaryEmployees AS
SELECT * FROM Employee WHERE salary > 50000;

-- Update data using view (if updatable)
UPDATE HighSalaryEmployees SET salary = 60000 WHERE emp_id = 101;

-- Drop view
DROP VIEW EmployeeDeptView;

-- View with JOIN
CREATE VIEW CustomerOrderView AS
SELECT c.name, o.order_date, o.amount 
FROM Customer c 
JOIN Orders o ON c.cust_id = o.cust_id;

-- Fetch all employees
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employee;
END;

-- Get employees by department
CREATE PROCEDURE GetEmployeesByDept
    @DeptName VARCHAR(50)
AS
BEGIN
    SELECT e.* 
    FROM Employee e 
    JOIN Department d ON e.dept_id = d.dept_id
    WHERE d.dept_name = @DeptName;
END;

-- Increase salary for department
CREATE PROCEDURE IncreaseSalaryByDept
    @DeptId INT,
    @Percent DECIMAL(5,2)
AS
BEGIN
    UPDATE Employee 
    SET salary = salary * (1 + @Percent/100)
    WHERE dept_id = @DeptId;
END;

-- Count orders for customer
CREATE PROCEDURE CountCustomerOrders
    @CustId INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @OrderCount = COUNT(*) 
    FROM Orders 
    WHERE cust_id = @CustId;
END;

-- Delete stored procedure
DROP PROCEDURE GetAllEmployees;

-- Log deleted employee data
CREATE TRIGGER LogDeletedEmployee
ON Employee
AFTER DELETE
AS
BEGIN
    INSERT INTO Employee_Archive 
    SELECT *, GETDATE() FROM deleted;
END;

-- Track salary changes
CREATE TRIGGER TrackSalaryChange
ON Employee
AFTER UPDATE
AS
BEGIN
    IF UPDATE(salary)
    BEGIN
        INSERT INTO Salary_History 
        SELECT i.emp_id, d.salary, i.salary, GETDATE()
        FROM inserted i JOIN deleted d ON i.emp_id = d.emp_id;
    END
END;

-- Prevent insert if marks < 35
CREATE TRIGGER PreventLowMarks
ON Student
INSTEAD OF INSERT
AS
BEGIN
    IF (SELECT marks FROM inserted) >= 35
    BEGIN
        INSERT INTO Student SELECT * FROM inserted;
    END
    ELSE
    BEGIN
        PRINT 'Marks cannot be less than 35';
    END
END;

-- Update stock after order
CREATE TRIGGER UpdateStockAfterOrder
ON Orders
AFTER INSERT
AS
BEGIN
    UPDATE Product 
    SET stock = stock - 1 
    WHERE product_id IN (SELECT product_id FROM inserted);
END;

-- Check employees before department delete
CREATE TRIGGER PreventDeptDelete
ON Department
INSTEAD OF DELETE
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Employee WHERE dept_id IN (SELECT dept_id FROM deleted))
    BEGIN
        DELETE FROM Department WHERE dept_id IN (SELECT dept_id FROM deleted);
    END
    ELSE
    BEGIN
        PRINT 'Cannot delete department with existing employees';
    END
END;

-- 3rd highest salary without MAX/ORDER BY
SELECT salary FROM Employee e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) 
           FROM Employee e2 
           WHERE e2.salary > e1.salary);

-- Find duplicate emails
SELECT email, COUNT(*) 
FROM User 
GROUP BY email 
HAVING COUNT(*) > 1;

-- Delete duplicates, keep one
WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
    FROM User
)
DELETE FROM CTE WHERE rn > 1;

-- Employees hired in last 7 days
SELECT * FROM Employee 
WHERE joining_date >= DATEADD(day, -7, GETDATE());

-- Running total of sales
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) as running_total
FROM Orders;