SQL Fundamentals: Queries, Joins, Aggregation, and Data Management

What are the foundational SQL commands required to query a table, and what role do they play?

SELECT & FROM | Syntax: SELECT column1, column2 FROM table_name; | SELECT specifies which columns to retrieve and FROM specifies the target table. Every standard query begins here. DISTINCT can be added (SELECT DISTINCT) to remove duplicate rows. | Think of FROM as entering a filing room (table) and SELECT as choosing which drawers (columns) to look at.

How do you filter rows in a SQL query, and why is WHERE used instead of scrolling results?

WHERE | Syntax: WHERE column_name = 'value'; | WHERE evaluates a condition row-by-row and filters out rows that do not match. It operates before grouping or aggregation. | Like Excel's filter feature, it instantly hides rows that don't meet your criteria.

Why can't you use = to check for missing values in SQL, and what is the correct syntax?

NULL handling | Syntax: WHERE column_name IS NULL or WHERE column_name IS NOT NULL | NULL represents the absence of a value. Comparisons such as =, !=, or < against NULL evaluate to unknown, so you must use IS NULL or IS NOT NULL. | Instead of asking if an empty box equals something, ask whether the box is empty.

How do you match partial text, pick from a discrete list of values, or filter an inclusive range?

LIKE, IN, BETWEEN | Syntax: WHERE name LIKE '%text%' OR classification IN ('a','b') OR hp BETWEEN 10 AND 20; | LIKE uses % wildcards for pattern matching. IN checks membership in a list. BETWEEN filters values inclusively between two bounds. | LIKE = contains text, IN = choose from a menu, BETWEEN = values within a range including endpoints.

How do you sort query results and restrict the total number of rows returned?

ORDER BY & LIMIT | Syntax: ORDER BY column1 DESC, column2 ASC LIMIT 10; | ORDER BY sorts ascending (ASC) or descending (DESC). Secondary columns can break ties. LIMIT restricts the number of rows returned. | Like a leaderboard sorted by score and showing only the top 10 players.

What are aggregate functions, and how do they transform multiple rows into a single summary value?

Aggregate Functions (SUM, AVG, MIN, MAX, COUNT) | Syntax: SELECT COUNT(*), ROUND(AVG(hp), 2), MAX(hp) FROM pokemon; | Aggregate functions take many rows and return a single summary value such as a count, average, minimum, maximum, or total. | Many values go in; one summary number comes out.

When should you use GROUP BY, and how does it organize data?

GROUP BY | Syntax: SELECT classification, COUNT(*) AS n FROM pokemon GROUP BY classification; | GROUP BY collapses rows sharing the same value into groups so aggregate functions can be calculated per group. | If you hear "per" or "each" (sales per region, average score per class), think GROUP BY.

What is the difference between WHERE and HAVING when filtering data?

HAVING vs WHERE | Syntax: GROUP BY classification HAVING COUNT(*) >= 5; | WHERE filters rows before grouping and aggregation. HAVING filters groups after aggregation. | WHERE filters people before the party; HAVING filters rooms after the party starts.

How do you transform values into custom labels or conditionally count subsets right inside a SELECT statement?

CASE WHEN | Syntax: SELECT name, CASE WHEN hp >= 100 THEN 'high' ELSE 'low' END AS band FROM pokemon; | CASE evaluates conditions in order and returns the first matching result. It can also be used inside aggregate functions. | Like a grading rubric built directly into the query.

How do you combine data from multiple tables, and what makes a LEFT JOIN unique?

JOIN & LEFT JOIN | Syntax: SELECT a.name, b.form_name FROM pokemon a LEFT JOIN pokemon_forms b ON a.primary_type = b.primary_type; | INNER JOIN returns matching rows from both tables. LEFT JOIN keeps all rows from the left table and returns NULL where matches don't exist. | INNER JOIN = must be on both guest lists. LEFT JOIN = keep everyone from the main list even if data is missing.

What are Subqueries and CTEs (WITH), and when should you use them?

Subqueries & CTEs (WITH) | Syntax: WITH labelled AS (SELECT * FROM pokemon WHERE classification IS NOT NULL) SELECT name FROM labelled; | A subquery is nested inside another query. A CTE creates a named temporary result set that makes complex queries cleaner and reusable. | A CTE is like naming a scratch-work step and reusing it later.

How do window functions rank rows without collapsing them like GROUP BY does?

Window Functions (RANK, ROW_NUMBER, PARTITION BY) | Syntax: RANK() OVER (PARTITION BY classification ORDER BY hp DESC) AS rnk | Window functions calculate values across related rows while keeping every row visible. PARTITION BY creates groups and ORDER BY sorts within them. | Like ranking runners within each age division while still listing every runner.

How do you add brand new rows of data into an existing database table?

INSERT INTO | Syntax: INSERT INTO pokemon (name, hp) VALUES ('Pikachu', 35); | INSERT INTO adds new rows by specifying target columns and their values. | Like filling out a new row on a signup sheet.

How do you modify existing data rows within a table?

UPDATE | Syntax: UPDATE pokemon SET hp = 40 WHERE name = 'Pikachu'; | UPDATE changes existing values using SET. A WHERE clause targets specific rows. Without WHERE, all rows are updated. | Like erasing an old value and writing in the corrected one.

How do you remove specific rows from a table?

DELETE | Syntax: DELETE FROM pokemon WHERE hp < 10; | DELETE removes rows matching the WHERE condition. The table structure remains intact. Without WHERE, all rows are deleted. | Like cutting unwanted rows out of a ledger.

How do you define a brand new table schema in a database?

CREATE TABLE | Syntax: CREATE TABLE pokemon (id INT PRIMARY KEY, name VARCHAR(50), hp INT); | CREATE TABLE defines the table name, columns, data types, and constraints. | Like building labeled filing cabinets before adding any files.

How do you modify the structure of an existing table after it has already been created?

ALTER TABLE | Syntax: ALTER TABLE pokemon ADD COLUMN secondary_type VARCHAR(50); | ALTER TABLE adds, removes, or modifies columns and constraints without recreating the table. | Like adding another drawer to an existing filing cabinet.

What is the difference between DROP TABLE and TRUNCATE TABLE?

DROP TABLE vs TRUNCATE TABLE | Syntax: DROP TABLE pokemon; / TRUNCATE TABLE pokemon; | DROP TABLE removes the table structure and all data. TRUNCATE TABLE removes all rows but keeps the table structure. | DROP destroys the cabinet; TRUNCATE empties all drawers but keeps the cabinet.

How do database administrators manage user access privileges and permissions?

GRANT & REVOKE | Syntax: GRANT SELECT ON pokemon TO user_name; REVOKE SELECT ON pokemon FROM user_name; | GRANT gives permissions such as SELECT, INSERT, or UPDATE. REVOKE removes those permissions. | Like issuing and deactivating employee access badges.

How do you ensure a group of multiple database changes either all succeed together or all fail safely?

Transactions (BEGIN, COMMIT, ROLLBACK) | Syntax: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; | Transactions make multiple operations atomic. COMMIT saves changes permanently. ROLLBACK undoes all changes if an error occurs. | Like a bank transfer where either both account updates happen or neither does.