SQL stands for Structured Query Language. It's a programming language designed for managing and manipulating relational databases. SQL allows you to create, read, update, and delete data from databases.

WHERE filters rows before grouping, while HAVING filters groups after GROUP BY. WHERE cannot use aggregate functions, but HAVING can. Example: WHERE salary > 50000 vs HAVING COUNT(*) > 5.

INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from left table and matching from right. RIGHT JOIN returns all from right and matching from left. FULL OUTER JOIN returns all rows from both tables.

A primary key is a column or combination of columns that uniquely identifies each row in a table. It cannot contain NULL values and must be unique. Each table can have only one primary key.

DELETE removes specific rows and can be rolled back. TRUNCATE removes all rows quickly but can't be rolled back easily. DROP removes the entire table structure and data permanently.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or using window functions: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank FROM employees) WHERE rank = 2;

Use IS NULL or IS NOT NULL for comparisons. COALESCE() returns first non-NULL value. ISNULL() replaces NULL with specified value. NULL in arithmetic operations returns NULL.

UNION removes duplicate rows and sorts results, making it slower. UNION ALL includes all rows including duplicates and doesn't sort, making it faster. Use UNION ALL when you know there are no duplicates.

Aggregate functions perform calculations on multiple rows and return a single value. Common ones: COUNT() - counts rows, SUM() - adds values, AVG() - calculates average, MIN()/MAX() - find minimum/maximum, STRING_AGG() - concatenates strings.

SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1; This groups by the columns that might have duplicates and filters for groups with more than one record.

Window functions perform calculations across rows related to current row without grouping. Examples: ROW_NUMBER() for pagination, RANK() for ranking with ties, LAG/LEAD for comparing with previous/next rows, running totals with SUM() OVER().

Common Table Expressions (CTEs) are temporary named result sets. Use for: breaking complex queries into readable parts, recursive operations, replacing views for single queries. Syntax: WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name.

1) Analyze execution plan 2) Add proper indexes 3) Avoid SELECT * 4) Use WHERE clauses early 5) Limit result sets 6) Use EXISTS instead of IN for subqueries 7) Consider query rewriting 8) Update table statistics.

Normalization reduces data redundancy. 1NF: eliminate repeating groups. 2NF: remove partial dependencies. 3NF: remove transitive dependencies. BCNF: every determinant is a candidate key. Higher forms exist but rarely used in practice.

WITH RECURSIVE emp_hierarchy AS (SELECT employee_id, name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.name, e.manager_id, h.level + 1 FROM employees e JOIN emp_hierarchy h ON e.manager_id = h.employee_id) SELECT * FROM emp_hierarchy;

SELECT user_id, COUNT(DISTINCT genre) as genre_count FROM user_views v JOIN content c ON v.content_id = c.content_id WHERE v.view_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) GROUP BY user_id HAVING COUNT(DISTINCT genre) > 5;

SELECT driver_id, AVG(trip_distance) as avg_distance FROM trips WHERE trip_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 WEEK) AND trip_status = 'completed' GROUP BY driver_id ORDER BY avg_distance DESC;

WITH daily_searches AS (SELECT date, COUNT(*) as search_count FROM searches GROUP BY date), search_with_prev AS (SELECT date, search_count, LAG(search_count) OVER (ORDER BY date) as prev_count FROM daily_searches) SELECT date, (search_count - prev_count) as increase FROM search_with_prev ORDER BY increase DESC LIMIT 1;