Showcase T-SQL

https://github.com/MicrosoftLearning/QueryingT-SQL

https://mystery.knightlab.com/

✅ List of Possible SQL Subqueries

🔹 1. Single-row subquery

Returns exactly one row and one column.

SELECT name FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

🔹 2. Multi-row subquery

Returns multiple rows, used with IN, ANY, ALL.

SELECT name FROM employees

WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');

🔹 3. Multi-column (tuple) subquery

Returns multiple columns (one or more rows).

SELECT * FROM employees

WHERE (department_id, job_id) IN (

  SELECT department_id, job_id FROM job_assignments

);

🔹 4. Correlated subquery

Depends on the outer query — evaluated once per row of the outer query.

SELECT name FROM employees e

WHERE salary > (

    SELECT AVG(salary) FROM employees WHERE department_id = e.department_id

);

🔹 5. Subquery in SELECT clause

Used to compute derived data per row.

SELECT name,

       (SELECT COUNT(*) FROM tasks WHERE tasks.employee_id = e.id) AS task_count

FROM employees e;

🔹 6. Subquery in FROM clause (inline view)

Acts like a temporary table in the FROM.

SELECT department_id, AVG(salary)

FROM (

    SELECT * FROM employees WHERE status = 'active'

) AS active_employees

GROUP BY department_id;

🔹 7. Subquery with EXISTS

Checks if subquery returns any rows.

SELECT name FROM employees e

WHERE EXISTS (

    SELECT 1 FROM tasks t WHERE t.employee_id = e.id

);

🔹 8. Subquery with NOT EXISTS

Used to find rows not matched in a subquery.

SELECT name FROM employees e

WHERE NOT EXISTS (

    SELECT 1 FROM tasks t WHERE t.employee_id = e.id

);

🔹 9. Subquery with IN / NOT IN

Used to filter based on value lists.

SELECT name FROM employees

WHERE department_id IN (

    SELECT id FROM departments WHERE location = 'NYC'

);

🔹 10. Subquery using ANY / SOME / ALL

Used to compare a value against multiple returned values.

SELECT name FROM employees

WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);

🔹 11. Subquery in UPDATE statement

Update using values from another table.

UPDATE employees

SET salary = salary * 1.1

WHERE department_id = (

    SELECT id FROM departments WHERE name = 'Engineering'

);

🔹 12. Subquery in DELETE statement

Delete records based on a condition from another table.

DELETE FROM employees

WHERE id IN (SELECT employee_id FROM terminated_employees);

🔹 13. Subquery in INSERT statement

Insert data from a subquery result.

INSERT INTO retired_employees (id, name)

SELECT id, name FROM employees

WHERE retirement_date <= CURRENT_DATE;

✅ First Normal Form (1NF) – Eliminate Repeating Groups

Rule:

• Each column should contain atomic (indivisible) values.
  
  

• Each record should be unique with a primary key.
  
  

• There should be no repeating groups or arrays in a single column.

✅ Second Normal Form (2NF) – Eliminate Partial Dependencies

Rule:

• Table must be in 1NF.
  
  

• All non-key attributes must depend on the entire primary key (not just part of it).
  
  

• Mostly relevant when you have a composite primary key.

✅ Third Normal Form (3NF) – Eliminate Transitive Dependencies

Rule:

• Table must be in 2NF.
  
  

• Non-key attributes should not depend on other non-key attributes (i.e., no transitive dependencies).