SQL Medium Flashcards: Intermediate SQL Practice for Interviews

SELECT name - salary - CASE WHEN salary > 100000 THEN 'High' WHEN salary BETWEEN 50000 AND 100000 THEN 'Mid' ELSE 'Low' END AS SalaryGrade FROM Employee;

SELECT sale_date - amount - SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM Sales;

CASE WHEN condition THEN result ... ELSE result END

SELECT o.order_id - (SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id = o.order_id) AS item_count FROM Orders o;

DELETE is row-by-row and can be rolled back. TRUNCATE is bulk and faster but irreversible.

ROW_NUMBER() gives unique numbers. RANK() gives same rank to ties but skips ranks. DENSE_RANK() gives same rank to ties but doesn't skip.

UNION removes duplicates; UNION ALL includes all rows including duplicates.

Clustered index defines row order; only one allowed. Non-clustered is a separate structure and you can have many.

Yes - BETWEEN is inclusive.

INNER JOIN returns only matching rows; LEFT JOIN returns all rows from the left table - even without a match.

SELECT email FROM Customers GROUP BY email HAVING COUNT(*) > 1;

SELECT name FROM Employee e WHERE salary > (SELECT AVG(salary) FROM Employee WHERE department_id = e.department_id);

WITH Ranked AS (SELECT * - ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM Employee) SELECT * FROM Ranked WHERE rn = 1;

SELECT customer_id - YEAR(order_date) AS order_year - SUM(amount) AS total_amount FROM Orders GROUP BY customer_id - YEAR(order_date);

Use YEAR(date_column) or EXTRACT(YEAR FROM date_column) depending on DBMS.

Use EXCEPT or MINUS or a LEFT JOIN with IS NULL.

Add proper indexes - avoid SELECT * - limit scanned data - use EXPLAIN to analyze performance.

Use ROW_NUMBER() OVER PARTITION BY columns - then delete where row > 1.

Use COALESCE(column - 'default') or IFNULL/ISNULL depending on DBMS.

Use a CTE with WITH clause. Define the subquery once and reference it multiple times.

DELETE FROM Customers WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE Orders.customer_id = Customers.customer_id);

SELECT e.name FROM Employee e LEFT JOIN Department d ON e.dept_id = d.id WHERE d.id IS NULL;

INSERT INTO Alumni (id - name - dept_id - join_date - leave_date - status) SELECT id - name - dept_id - join_date - leave_date - status FROM Employees WHERE status = 'Resigned';

SELECT e.name - d.dept_name - l.city FROM Employee e JOIN Department d ON e.dept_id = d.dept_id JOIN Location l ON d.location_id = l.location_id;

'%' matches any number of characters - '_' matches one character.

ROWNUM is assigned before ORDER BY. ROW_NUMBER() is a window function with sorting and partitioning support.

DROP removes the table and its data. TRUNCATE removes data but keeps table structure.

A CROSS JOIN returns the Cartesian product: m * n rows for m and n rows in the two tables.

A CTE is a named temporary result set defined using WITH. It's used to simplify complex queries and improve readability.

A FULL OUTER JOIN returns all rows from both tables - matching rows where possible and filling NULLs where there is no match.

An index on multiple columns. Useful when queries filter on those columns together.

A correlated subquery uses values from the outer query and executes once for each row of the outer query.

A recursive CTE is a CTE that references itself - used for hierarchical or recursive data.

A self join is when a table is joined with itself. It's used when rows in a table are related to other rows in the same table.

A window function performs a calculation across rows related to the current row without collapsing the result set.

An index is a structure that speeds up searches on a table. It reduces full table scans by allowing direct access to matching rows.

COUNT(*) counts all rows - including NULLs; COUNT(column_name) counts only non-NULL values.

WHERE filters rows before GROUP BY; HAVING filters after GROUP BY - often using aggregate functions.

IN checks for membership in a fixed list or subquery result. EXISTS checks for the presence of at least one row and is more efficient for correlated subqueries.

The order is: FROM (and JOINs) - then WHERE - then GROUP BY - then HAVING - then SELECT (the select-list) - and finally ORDER BY (and LIMIT/OFFSET).

When the column has low selectivity or on small tables.

When filtering based on aggregated results or readability is better with subqueries.

INTERSECT

Because NULL represents unknown. Use IS NULL or IS NOT NULL.

SELECT product_name - price FROM Products WHERE category = 'Electronics' AND price BETWEEN 1000 AND 5000 ORDER BY price DESC;

SELECT e.name AS employee_name - m.name AS manager_name FROM Employee e LEFT JOIN Employee m ON e.manager_id = m.employee_id;

UPDATE Employees SET salary = salary * 1.10 WHERE salary < (SELECT AVG(salary) FROM Employees);

SELECT e.name - d.department_name FROM Employee e LEFT JOIN Department d ON e.department_id = d.department_id;

SELECT DISTINCT job_title FROM Employee ORDER BY job_title ASC;

SELECT category - SUM(sales_amount) AS total_sales FROM Sales GROUP BY category HAVING SUM(sales_amount) > 1000000 ORDER BY total_sales DESC;