SQLsummary.sql

---Table: Employee_Anjali
---Table Structure
CREATE TABLE Employee_Anjali
(
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(30),
    Department VARCHAR(20),
    Salary INT,
    Age INT,
    City VARCHAR(20)
);
INSERT INTO Employee_Anjali VALUES
(1, 'Anjali', 'IT', 30000, 22, 'Vadodara'),
(2, 'Rohan', 'HR', 18000, 24, 'Ahmedabad'),
(3, 'Aman', 'IT', 25000, 26, 'Surat'),
(4, 'Neha', 'Finance', 40000, 30, 'Vadodara'),
(5, 'Kiran', 'HR', 22000, 23, 'Surat'),
(6, 'Arjun', 'IT', 35000, 28, 'Ahmedabad');

---1.Name starts with a letter    --->Employee_Anjali(EmpID, EmpName, Department, Salary, Age, City)
---Keyword in question: starts with / begins with
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE 'A%';

---2.Name ends with a letter
---Keyword: ends with
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '%n';
---'%n' → anything before, ends with n

---3.Name contains a word or letter
---Keyword: contains / has / includes
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '%an%';
--- %an% → an can be anywhere

---4.Name exact length (e.g., 5 letters)
---Keyword: exactly X characters
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '_____';


---Each _ = one character
---(5 underscores = 5 letters)

---Name starts with A and ends with i
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE 'A%i';

--- Second letter is specific
--Keyword: second character is…

SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '_n%';


--- _ skips first character
---City name contains "da"
SELECT *
FROM Employee_Anjali
WHERE City LIKE '%da%';

---Department starts with F
SELECT *
FROM Employee_Anjali
WHERE Department LIKE 'F%';

---Names NOT starting with A
SELECT *
FROM Employee_Anjali
WHERE EmpName NOT LIKE 'A%';


---NOT LIKE = reverse condition
---Names ending with "sh"
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '%sh';

---Names containing either A or N
SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '%a%'
   OR EmpName LIKE '%n%';

---Email-style pattern (if column exists)
SELECT *
FROM Employee_Anjali
WHERE Email LIKE '%@gmail.com';

Employee_Anjali
(EmpID, EmpName, Department, Salary, Age, City)

---EQUAL TO (=)
---Keyword: is / equals / exactly
SELECT *
FROM Employee_Anjali
WHERE Department = 'IT';

---Used for exact match
---NOT EQUAL (!= or <>)
---Keyword: not / except

SELECT *
FROM Employee_Anjali
WHERE Department != 'HR';


OR

WHERE Department <> 'HR';


---GREATER THAN (>)
---Keyword: more than / above

SELECT *
FROM Employee_Anjali
WHERE Salary > 30000;

---LESS THAN (<)
---Keyword: less than / below
SELECT *
FROM Employee_Anjali
WHERE Age < 25;

---GREATER THAN OR EQUAL TO (>=)
---Keyword: at least / minimum
SELECT *
FROM Employee_Anjali
WHERE Salary >= 20000;

---LESS THAN OR EQUAL TO (<=)
---Keyword: at most / maximum

SELECT *
FROM Employee_Anjali
WHERE Age <= 30;

---BETWEEN (RANGE)
---Keyword: between X and Y (inclusive)
SELECT *
FROM Employee_Anjali
WHERE Salary BETWEEN 20000 AND 30000;


---Includes 20000 and 30000
---Wrong thinking: BETWEEN is NOT exclusive

---BETWEEN (WITHOUT BETWEEN)
SELECT *
FROM Employee_Anjali
WHERE Salary >= 20000 AND Salary <= 30000;


---Same result as BETWEEN

---IN (MULTIPLE VALUES)
---Keyword: in / among / belongs to
SELECT *
FROM Employee_Anjali
WHERE Department IN ('IT','HR','QA');


---Cleaner than OR
---NOT IN
---Keyword: not in / except
SELECT *
FROM Employee_Anjali
WHERE Department NOT IN ('IT','HR');

--AND OPERATOR
---Keyword: and / both conditions
SELECT *
FROM Employee_Anjali
WHERE Department = 'IT'
AND Salary > 30000;

---ALL conditions must be true

--- OR OPERATOR
---Keyword: or / either
SELECT *
FROM Employee_Anjali
WHERE Department = 'IT'
OR Department = 'HR';

---Any ONE true
---AND + OR (IMPORTANT)
SELECT *
FROM Employee_Anjali
WHERE Department = 'IT'
AND (Salary > 30000 OR Age > 30);

---Brackets are mandatory to control logic

---IS NULL
---Keyword: missing / blank
SELECT *
FROM Employee_Anjali
WHERE Salary IS NULL;

--❌ = NULL is WRONG

---IS NOT NULL
SELECT *
FROM Employee_Anjali
WHERE Salary IS NOT NULL;

---LIKE (TEXT PATTERNS)
WHERE EmpName LIKE 'A%';      -- starts with A
WHERE EmpName LIKE '%n';      -- ends with n
WHERE EmpName LIKE '%an%';    -- contains an

---NOT LIKE
SELECT *
FROM Employee_Anjali
WHERE EmpName NOT LIKE 'A%';

---COMBINED WHERE (REAL EXAM STYLE)
SELECT *
FROM Employee_Anjali
WHERE Department IN ('IT','QA')
AND Salary BETWEEN 20000 AND 40000
AND City != 'Delhi';


---WHERE is used for: Filtering rows
--Comparisons
---NULL / NOT NULL — ALL PATTERNS (COMPLETE GUIDE)
---Base Table

Employee_Anjali
(EmpID, EmpName, Department, Salary, Age, City, Email)

---1.What is NULL? (Concept)

---NULL = missing / unknown / not provided

---NULL ≠ 0

---NULL ≠ empty string (' ')

---2.Find rows where column is NULL

---Keyword: missing / not available / blank

SELECT *
FROM Employee_Anjali
WHERE Email IS NULL;


--✅ Correct
---❌ Email = NULL → WRONG

---3️. Find rows where column is NOT NULL
SELECT *
FROM Employee_Anjali
WHERE Email IS NOT NULL;

---4️. NULL with Numeric Column
SELECT *
FROM Employee_Anjali
WHERE Salary IS NULL;


--Salary not entered

---5️.NOT NULL with Numeric Column
SELECT *
FROM Employee_Anjali
WHERE Salary IS NOT NULL;

---6️. Combine NULL with AND
SELECT *
FROM Employee_Anjali
WHERE Department = 'IT'
AND Salary IS NULL;


---IT employees whose salary is missing

---7️. Combine NULL with OR
SELECT *
FROM Employee_Anjali
WHERE Salary IS NULL
OR Email IS NULL;


---Any missing data

---8. NULL with NOT
SELECT *
FROM Employee_Anjali
WHERE NOT Salary IS NULL;


-------------Works, but better style:

WHERE Salary IS NOT NULL;

---9.Count NULL values
SELECT COUNT(*)
FROM Employee_Anjali
WHERE Email IS NULL;


---COUNT(column) ignores NULL
COUNT(*) counts rows

---10.Difference: COUNT(*) vs COUNT(column)
SELECT COUNT(Salary) FROM Employee_Anjali;


---✔ Counts only non-NULL salaries
SELECT COUNT(*) FROM Employee_Anjali;


---✔ Counts all rows
---11.NULL inside IN (IMPORTANT TRAP)

---❌ WRONG
WHERE Salary IN (NULL);

---✔ CORRECT
WHERE Salary IS NULL;

---12. NULL inside NOT IN
SELECT *
FROM Employee_Anjali
WHERE Department NOT IN ('IT','HR')
AND Department IS NOT NULL;


---Always protect NOT IN with IS NOT NULL

---13. Replace NULL using ISNULL (SQL Server)
SELECT EmpName,
ISNULL(Salary, 0) AS Salary_Value
FROM Employee_Anjali;


---If Salary is NULL → show 0

---14. NULL in Calculations
SELECT EmpName,
ISNULL(Salary,0) + 5000 AS NewSalary
FROM Employee_Anjali;


--- Without ISNULL → result becomes NULL

---15. UPDATE NULL values
UPDATE Employee_Anjali
SET Salary = 20000
WHERE Salary IS NULL;

---16.DELETE NULL records
DELETE FROM Employee_Anjali
WHERE Email IS NULL;

---17. NULL in GROUP BY (IMPORTANT)
SELECT Department, COUNT(*)
FROM Employee_Anjali
GROUP BY Department;


---NULL appears as a separate group

---18.HAVING with NULL
SELECT Department, COUNT(*)
FROM Employee_Anjali
GROUP BY Department
HAVING COUNT(Salary) > 2;


---AGGREGATE FUNCTIONS — ALL PATTERNS (EXAM MASTER GUIDE)
---Base Table
Employee_Anjali
(EmpID, EmpName, Department, Salary, Age, City)

--- 1️⃣ COUNT() — TOTAL ROWS
a) Count all rows
SELECT COUNT(*)
FROM Employee_Anjali;


--- Counts every row (including NULLs)

b) Count non-NULL values in a column
SELECT COUNT(Salary)
FROM Employee_Anjali;


--- Ignores NULL salaries

c) Count with condition
SELECT COUNT(*)
FROM Employee_Anjali
WHERE Department = 'IT';

---2️. SUM() — TOTAL (NUMERIC ONLY)
Total salary of all employees
SELECT SUM(Salary)
FROM Employee_Anjali;

Sum with condition
SELECT SUM(Salary)
FROM Employee_Anjali
WHERE Department = 'HR';

---3️. AVG() — AVERAGE (NUMERIC ONLY)
Average salary
SELECT AVG(Salary)
FROM Employee_Anjali;

Average salary per department
SELECT Department, AVG(Salary)
FROM Employee_Anjali
GROUP BY Department;


--- GROUP BY mandatory when column is in SELECT

--- 4️. MAX() — HIGHEST VALUE
Highest salary
SELECT MAX(Salary)
FROM Employee_Anjali;

Highest salary per department
SELECT Department, MAX(Salary)
FROM Employee_Anjali
GROUP BY Department;

--- 5️. MIN() — LOWEST VALUE
Lowest salary
SELECT MIN(Salary)
FROM Employee_Anjali;

Minimum age per city
SELECT City, MIN(Age)
FROM Employee_Anjali
GROUP BY City;

--- 6️. MULTIPLE AGGREGATES TOGETHER
SELECT 
MAX(Salary) AS Highest,
MIN(Salary) AS Lowest,
AVG(Salary) AS Average
FROM Employee_Anjali;

--- 7️. AGGREGATES WITH WHERE
SELECT AVG(Salary)
FROM Employee_Anjali
WHERE Department = 'IT';


--- WHERE filters rows before aggregation

-- 8️. AGGREGATES WITH GROUP BY (MOST IMPORTANT)
Employees count per department
SELECT Department, COUNT(*)
FROM Employee_Anjali
GROUP BY Department;

Total salary per department
SELECT Department, SUM(Salary)
FROM Employee_Anjali
GROUP BY Department;

--- 9️. HAVING — FILTER AFTER GROUP BY
Departments with avg salary > 25000
SELECT Department, AVG(Salary)
FROM Employee_Anjali
GROUP BY Department
HAVING AVG(Salary) > 25000;


--- WHERE ❌ | HAVING ✅ for aggregates

--- WHERE vs HAVING (EXAM FAVORITE)

-- WRONG

WHERE AVG(Salary) > 25000


-- ✔ CORRECT

HAVING AVG(Salary) > 25000

--- COUNT + GROUP BY + HAVING
SELECT Department, COUNT(*)
FROM Employee_Anjali
GROUP BY Department
HAVING COUNT(*) > 3;


--- Departments with more than 3 employees

--- DISTINCT with AGGREGATE
SELECT COUNT(DISTINCT Department)
FROM Employee_Anjali;


--- Unique departments count

--- AGGREGATE with CALCULATION
SELECT SUM(Salary * 12) AS Annual_Payout
FROM Employee_Anjali;

--- AGGREGATE with NULL SAFETY
SELECT AVG(ISNULL(Salary,0))
FROM Employee_Anjali;


--- Prevent NULL affecting result

--- TOP with AGGREGATE
SELECT TOP 1 Salary
FROM Employee_Anjali
ORDER BY Salary DESC;


--- TOP N per GROUP (INTERVIEW STYLE)
SELECT Department, MAX(Salary)
FROM Employee_Anjali
GROUP BY Department;

--- AGGREGATE with ORDER BY
SELECT Department, AVG(Salary) AS AvgSal
FROM Employee_Anjali
GROUP BY Department
ORDER BY AvgSal DESC;

--- AGGREGATE + BETWEEN
SELECT Department, COUNT(*)
FROM Employee_Anjali
WHERE Salary BETWEEN 20000 AND 40000
GROUP BY Department;

--- GOLDEN RULES (MUST MEMORIZE)

--✔ If column is in SELECT, it must be:

in GROUP BY
OR

inside aggregate function

-- ✔ WHERE → before GROUP BY
---✔ HAVING → after GROUP BY

--- 10-SECOND MEMORY FORMULA
SELECT column, AGG(column)
FROM table
WHERE condition
GROUP BY column
HAVING AGG(column) condition
ORDER BY column;

SET OPERATORS — ALL PATTERNS (COMPLETE GUIDE)

Used to combine results of two SELECT statements

--- Tables Used (Example)
Table: Employee_Anjali
(EmpID, EmpName, Department)

Table: Employee_Anju
(EmpID, EmpName, Department)


--- Important Rule (Must match):

Same number of columns

Same order

Compatible data types

--- UNION
--➤ Combines results & removes duplicates
SELECT EmpID, EmpName, Department
FROM Employee_Anjali
UNION
SELECT EmpID, EmpName, Department
FROM Employee_Anju;


--- Unique rows only

--- UNION ALL
--➤ Combines results & keeps duplicates
SELECT EmpID, EmpName, Department
FROM Employee_Anjali
UNION ALL
SELECT EmpID, EmpName, Department
FROM Employee_Anju;


--- Faster than UNION

--- INTERSECT
--➤ Common rows in both tables
SELECT EmpID, EmpName, Department
FROM Employee_Anjali
INTERSECT
SELECT EmpID, EmpName, Department
FROM Employee_Anju;


---- Returns matching rows only

---4.EXCEPT
---➤ Rows present in first table but not in second
SELECT EmpID, EmpName, Department
FROM Employee_Anjali
EXCEPT
SELECT EmpID, EmpName, Department
FROM Employee_Anju;


---Order matters!

---5.REVERSE EXCEPT
SELECT EmpID, EmpName, Department
FROM Employee_Anju
EXCEPT
SELECT EmpID, EmpName, Department
FROM Employee_Anjali;


--- Opposite difference

---6.SET OPERATORS WITH WHERE
SELECT EmpID, EmpName, Department
FROM Employee_Anjali
WHERE Department = 'IT'

UNION

SELECT EmpID, EmpName, Department
FROM Employee_Anju
WHERE Department = 'IT';


--- WHERE filters first, then UNION

---7.SET OPERATORS WITH DISTINCT COLUMNS
SELECT EmpName
FROM Employee_Anjali
UNION
SELECT EmpName
FROM Employee_Anju;

---DISTINCT automatically in UNION

---8.SET OPERATORS WITH ORDER BY
SELECT EmpID, EmpName
FROM Employee_Anjali
UNION
SELECT EmpID, EmpName
FROM Employee_Anju
ORDER BY EmpID;


---ORDER BY only at the end

---9.SET OPERATORS WITH TOP
SELECT TOP 3 EmpID, EmpName
FROM Employee_Anjali

UNION ALL

SELECT TOP 3 EmpID, EmpName
FROM Employee_Anju;

--- SET OPERATORS WITH ALIAS
SELECT EmpID AS ID, EmpName AS Name
FROM Employee_Anjali

UNION

SELECT EmpID, EmpName
FROM Employee_Anju;


--- Alias name comes from first SELECT

--- LIMITATIONS OF SET OPERATORS (EXAM FAVORITE)
-- 1. Same number of columns
-- 2. Same column order
-- 3. Same data type
-- 4. ORDER BY only at the end


---Full company-level mock SQL test with solutions
---Absolutely  Anjalii
---Here’s a **FULL company-level SQL mock test WITH SOLUTIONS**, designed exactly like **internal company tests / interviews**.

--- Covers **WHERE, LIKE, NULL, AGGREGATES, GROUP BY, HAVING, SET OPERATORS**
---Questions are **natural + human-written**, not AI-looking
--- Difficulty: **Easy → Medium → Tricky (but fair)**

---

--- SQL MOCK TEST (Company Level)

--- Base Table (Assume this already exists)

Employee_Anjali
(
 EmpID INT,
 EmpName VARCHAR(30),
 Department VARCHAR(20),
 Salary INT,
 Age INT,
 City VARCHAR(20)
)
---

--- SECTION A — WHERE & COMPARISON (10 Marks)

--- Q1. Display all employees whose salary is greater than 25,000.

SELECT *
FROM Employee_Anjali
WHERE Salary > 25000;

--- Q2. List employees who are exactly 22 years old.

SELECT *
FROM Employee_Anjali
WHERE Age = 22;

--- Q3. Show employees who do not belong to the IT department.

SELECT *
FROM Employee_Anjali
WHERE Department <> 'IT';

--- Q4. Find employees whose salary is between 20,000 and 30,000.

SELECT *
FROM Employee_Anjali
WHERE Salary BETWEEN 20000 AND 30000;

--- Q5. Display employees who work in IT or HR.
SELECT *
FROM Employee_Anjali
WHERE Department IN ('IT','HR');

---  SECTION B — LIKE PATTERNS (10 Marks)

--- Q6. Find employees whose name starts with ‘A’.

SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE 'A%';

--- Q7. Find employees whose name ends with ‘n’.

SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '%n';

--- Q8. Find employees whose name contains ‘an’.

SELECT *
FROM Employee_Anjali
WHERE EmpName LIKE '%an%';


--- Q9. Find employees whose city starts with ‘M’.

SELECT *
FROM Employee_Anjali
WHERE City LIKE 'M%';

--- Q10. Display employees whose name does NOT start with ‘S’.

SELECT *
FROM Employee_Anjali
WHERE EmpName NOT LIKE 'S%';

--- Q11. Find employees whose salary is not available.

SELECT *
FROM Employee_Anjali
WHERE Salary IS NULL;

--- Q12. Display employees whose city is available.

SELECT *
FROM Employee_Anjali
WHERE City IS NOT NULL;

--- Q13. Count how many employees have NULL salary.

SELECT COUNT(*)
FROM Employee_Anjali
WHERE Salary IS NULL;


--- SECTION D — AGGREGATE FUNCTIONS (20 Marks)

--- Q14. Find the highest salary.

SELECT MAX(Salary)
FROM Employee_Anjali;

--- Q15. Find the average salary of all employees.

SELECT AVG(Salary)
FROM Employee_Anjali;

--- Q16. Find total salary paid to all employees.
SELECT SUM(Salary)
FROM Employee_Anjali;

--- Q17. Count total number of employees.
SELECT COUNT(*)
FROM Employee_Anjali;

--- Q18. Find minimum salary.
SELECT MIN(Salary)
FROM Employee_Anjali;

--- Q19. Count employees in each department.
SELECT Department, COUNT(*)
FROM Employee_Anjali
GROUP BY Department;

--- Q20. Find average salary per department.
SELECT Department, AVG(Salary)
FROM Employee_Anjali
GROUP BY Department;

--- Q21. Display departments having average salary greater than 25,000.
SELECT Department, AVG(Salary)
FROM Employee_Anjali
GROUP BY Department
HAVING AVG(Salary) > 25000;

--- Q22. Find cities where total salary exceeds 50,000.
SELECT City, SUM(Salary)
FROM Employee_Anjali
GROUP BY City
HAVING SUM(Salary) > 50000;

--- Q23. Find departments having more than 3 employees.
SELECT Department, COUNT(*)
FROM Employee_Anjali
GROUP BY Department
HAVING COUNT(*) > 3;


--- Q24. Find departments where max salary is greater than 40,000.
SELECT Department, MAX(Salary)
FROM Employee_Anjali
GROUP BY Department
HAVING MAX(Salary) > 40000;

--- Q25. Find IT employees with salary above 30,000.
SELECT *
FROM Employee_Anjali
WHERE Department = 'IT'
AND Salary > 30000;

--- Q26. Find employees older than 25 and not from HR.

SELECT *
FROM Employee_Anjali
WHERE Age > 25
AND Department <> 'HR';


--- Q27. Display employees whose salary is NULL or less than 20,000.
SELECT *
FROM Employee_Anjali
WHERE Salary IS NULL
OR Salary < 20000;


--- Q28. Find employees whose city is Delhi and salary between 25,000 and 40,000.

SELECT *
FROM Employee_Anjali
WHERE City = 'Delhi'
AND Salary BETWEEN 25000 AND 40000;

--- Q29. Show employees ordered by salary (highest first).
SELECT *
FROM Employee_Anjali
ORDER BY Salary DESC;

--- Q30. Display top 3 highest paid employees.
SELECT TOP 3 *
FROM Employee_Anjali
ORDER BY Salary DESC;