Filtering & Sorting

-- comparison
SELECT name, salary FROM Employees WHERE salary >= 85000;

-- several values: IN is shorter than many ORs
SELECT name FROM Employees WHERE city IN ('Pune', 'Delhi');

-- range: BETWEEN includes both ends
SELECT name FROM Employees WHERE salary BETWEEN 60000 AND 85000;

-- NULL: use IS NULL, never = NULL
SELECT name FROM Employees WHERE department_id IS NULL;      -- Neha

-- read as: Pune OR (Bengaluru AND salary > 80000)
SELECT name FROM Employees
WHERE city = 'Pune' OR city = 'Bengaluru' AND salary > 80000;
-- Aakash, Priya, Rahul, Arjun   (Pune employees with any salary slip in)

-- what we meant: (Pune OR Bengaluru) AND salary > 80000
SELECT name FROM Employees
WHERE (city = 'Pune' OR city = 'Bengaluru') AND salary > 80000;
-- Aakash, Rahul

-- 1. = NULL never matches. Comparing anything with NULL gives UNKNOWN, not true.
SELECT name FROM Employees WHERE department_id = NULL;       -- 0 rows

-- 2. NOT IN with a NULL in the list returns NOTHING
SELECT name FROM Employees WHERE department_id NOT IN (102, NULL);   -- 0 rows
-- each row is checked as: department_id <> 102 AND department_id <> NULL
-- "<> NULL" is UNKNOWN, so no row passes

-- safe version with a subquery: NOT EXISTS
SELECT e.name
FROM Employees e
WHERE NOT EXISTS (
  SELECT 1 FROM Departments d
  WHERE d.department_id = e.department_id
    AND d.department_name = 'HR'
);

CREATE INDEX IX_Employees_hire_date ON Employees (hire_date);
CREATE INDEX IX_Employees_email     ON Employees (email);
CREATE INDEX IX_Employees_dept_salary
  ON Employees (department_id, salary)
  INCLUDE (name);     -- name stored in the index too, so no table lookup

-- scan: YEAR() runs on every row
SELECT name FROM Employees
WHERE YEAR(hire_date) = 2024;

-- seek: the column is left alone, the range is on the other side
SELECT name FROM Employees
WHERE hire_date >= '2024-01-01'
AND hire_date <  '2025-01-01';      -- Neha, Arjun

SET STATISTICS IO, TIME ON;

SELECT name, salary
FROM Employees
WHERE department_id = 101 AND salary > 80000;
-- Messages tab: "logical reads 2" = good; thousands of reads = scanning
-- Execution plan (Ctrl + M in SSMS): Index Seek on IX_Employees_dept_salary

-- highest salary first; same salary -> alphabetical by name
SELECT name, salary
FROM Employees
ORDER BY salary DESC, name ASC;

-- alias works in ORDER BY (it runs after SELECT)
SELECT name, salary * 12 AS annual_salary
FROM Employees
ORDER BY annual_salary DESC;

-- but NOT in WHERE: error "Invalid column name 'annual_salary'"
-- SELECT name, salary * 12 AS annual_salary FROM Employees WHERE annual_salary > 1000000;

-- custom order with CASE: Bengaluru first, then Pune, then the rest
SELECT name, city
FROM Employees
ORDER BY
  CASE city
      WHEN 'Bengaluru' THEN 1
      WHEN 'Pune'      THEN 2
      ELSE 3
  END,
  name;

-- default: Neha (NULL) comes first
SELECT name, department_id FROM Employees ORDER BY department_id;

-- push NULLs to the end
SELECT name, department_id
FROM Employees
ORDER BY
  CASE WHEN department_id IS NULL THEN 1 ELSE 0 END,
  department_id;

-- top 2 earners
SELECT TOP (2) name, salary
FROM Employees
ORDER BY salary DESC;              -- Ananya, Rahul

-- WITH TIES: also return rows tied with the last one
SELECT TOP (1) WITH TIES name, salary
FROM Employees
ORDER BY salary DESC;              -- Ananya AND Rahul (both 95000)

-- pagination: page 2, 3 rows per page (OFFSET requires ORDER BY)
SELECT name, salary
FROM Employees
ORDER BY employee_id
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;     -- Neha, Arjun, Ananya

-- starts with
SELECT name FROM Employees WHERE name LIKE 'A%';             -- Aakash, Arjun, Ananya

-- ends with
SELECT name FROM Employees WHERE name LIKE '%a';             -- Priya, Neha, Ananya

-- contains
SELECT name, email FROM Employees WHERE email LIKE '%.hr@%'; -- Priya

-- exact length: 5 characters
SELECT name FROM Employees WHERE name LIKE '_____';          -- Priya, Rahul, Arjun

-- exclude a pattern
SELECT name, email FROM Employees WHERE email NOT LIKE '%@company.com';   -- Arjun

-- wrong: _ means "any one character"
SELECT email FROM Employees WHERE email LIKE '%_%';          -- all 6 rows

-- right: [_] means a literal underscore
SELECT email FROM Employees WHERE email LIKE '%[_]%';        -- neha_s@company.com

-- same with ESCAPE: the character after ! is taken literally
SELECT email FROM Employees WHERE email LIKE '%!_%' ESCAPE '!';

SELECT name FROM Employees
WHERE name LIKE 'a%' COLLATE Latin1_General_CS_AS;    -- CS = case-sensitive: 0 rows

-- @search comes from the app as a parameter, never glued into the SQL string
DECLARE @search NVARCHAR(50) = 'rah';

SELECT name
FROM Employees
WHERE name LIKE @search + '%';          -- starts with: index-friendly

-- escape user wildcards before using them
SET @search = REPLACE(REPLACE(REPLACE(@search, '[', '[[]'), '%', '[%]'), '_', '[_]');