1. INNER JOIN and NATURAL JOIN are not the same**. NATURAL JOIN is an implicit INNER JOIN based on matching column names. INNER JOIN is explicit and safer, so it is preferred in practice. 👉 Always use INNER JOIN INNER JOIN → You tell SQL how to join NATURAL JOIN → SQL guesses how to join 2. Key SQL terminology and core concepts Aliases Aliases rename columns or tables in a query to make results clearer or queries shorter. Column alias: SELECT COUNT(*) AS total_students (uses AS). Table alias: FROM students s [no AS (for Oracle) for the table in many dialects] (best avoid AS). Relationship types One‑to‑one: one row relates to exactly one row in another table [e.g., one teacher (teaches)→ one subject]. One‑to‑many: one row relates to many rows [e.g., one teacher (teaches) → many students]. Many‑to‑many: many rows on each side relate to many on the other [e.g., many students (enrolled in) → many courses]. UNION vs UNION ALL UNION: Combines (stacks) results from multiple SELECT statements. Requires same number of columns, compatible data types, and same column order. Removes duplicate rows (implicit DISTINCT). May be slower due to duplicate removal (sorting/hash) UNION ALL: Same structural requirements as UNION. Keeps duplicates; generally faster [than UNION (no duplicate check)]. Extra Note: INTERSECT: Common rows MINUS / EXCEPT: Rows in first query not in second 1 2 3 4 5 6 7 8 9 -- Using UNION (duplicates removed) SELECT city FROM customers UNION SELECT city FROM suppliers; -- Using UNION ALL (duplicates kept) SELECT city FROM customers UNION ALL SELECT city FROM suppliers; Main clauses in a SQL query Common main clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Typical logical order you write: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …. Only SELECT and FROM are mandatory Actual order of execution Logical execution order: FROM – choose tables / joins. [where data is coming from] WHERE – row‑level filtering. [what is returned] GROUP BY – create groups. [how data is grouped] HAVING – filter groups (often on aggregates). [which characteristics] SELECT – project columns / expressions. [what data is returned] ORDER BY – sort result. Constraints A constraint controls what data is allowed in a table. Common constraints: primary key, foreign key, UNIQUE, NOT NULL. Aggregate functions (Used with GROUP BY, Aggregate functions are allowed only in HAVING.) SUM() – returns total. AVG() – average. MIN() – smallest value or oldest date. MAX() – largest value or newest date. COUNT() – number of values (or rows when using COUNT(*)). Categories of SQL commands DDL (Data Definition Language): defines/changes table structure and objects such as databases, tables, views, and indexes (CREATE, ALTER, DROP. TRUNCATE). DDL commands usually auto-commit. Exmple: CREATE DATABASE, CREATE TABLE. DML (Data Manipulation Language): changes data (INSERT, UPDATE, DELETE). DML commands are transaction-controlled (can be committed or rolled back). Example: INSERT INTO. DCL (Data Control Language): Controls access and permissions on database objects (GRANT, REVOKE). Typically handled by DBAs. TCL (Transaction Control Language): manages transactions (BEGIN / START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT). DQL (Data Query Language): querying data (primarily SELECT, along with clauses like WHERE, ORDER BY, GROUP BY, HAVING, and LIMIT). 3. Creating, seeding, and modifying tables Creating a table Think through: table name, columns, data types, NULL vs NOT NULL, constraints (PK / FK), identity/auto‑increment. Example pattern (SQL Server style): 1 2 3 4 5 6 CREATE TABLE employee_info ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, first_name NVARCHAR(75), last_name NVARCHAR(75), email NVARCHAR(125) ); Use comments (--) to describe purpose of each block; this helps in written interviews. Inserting (seeding) data Pattern: 1 2 INSERT INTO employee_info (first_name, last_name, email) VALUES ('John', 'Arthur', 'john.arthur@example.com'); When the id column is identity/auto‑increment, you omit it from the column list. Verify with: SELECT * FROM employee_info;. ALTER – changing table structure Purpose: alter structure/relationships, including adding, dropping, or changing columns. Drop a column: 1 2 ALTER TABLE employee_info DROP COLUMN email; Add columns (can add multiple at once): 1 2 ALTER TABLE employee_info ADD city NVARCHAR(100), department NVARCHAR(100); Change data type of a column: 1 2 ALTER TABLE some_table ALTER COLUMN country NVARCHAR(100); 4. TRUNCATE vs DROP (and constraints) TRUNCATE TRUNCATE TABLE table_name removes all data but keeps the table structure (columns and their constraints remain). Often faster than DELETE without a WHERE clause. Cannot truncate a table that is referenced by a foreign key constraint; you must drop or disable the constraint first. Handling foreign key constraints If TRUNCATE fails due to a foreign key, you: ALTER TABLE child_table DROP CONSTRAINT fk_name; Then TRUNCATE TABLE parent_table;. You typically find fk_name in the database’s key/constraint metadata. DROP DROP TABLE table_name removes the table structure and all data. ALTER TABLE table_name DROP COLUMN column_name removes just one column. Think of TRUNCATE as clearing rows, and DROP as demolishing the table (or column). 5. Logical/comparison operators and wildcards Core keyword operators BETWEEN – values in a range (inclusive). IN – values in a specific list. LIKE – pattern matching (commonly with wildcards). IS NULL / IS NOT NULL – test for nulls. EXISTS – checks if a subquery returns any rows. Wildcards (used with LIKE) % – any number of characters (including zero). Example: WHERE name LIKE '%AMES' returns anything ending in “AMES”. _ – exactly one character per underscore. Example: WHERE code LIKE '_2_6' matches patterns like A2B6. [] – one of the characters inside the brackets. Example: WHERE name LIKE 'Wi[lk]%' matches “Will…”, “Wik…”, etc. Practice patterns with LIKE Return emails with any characters before 6 and ending in @gmail.com: 1 2 3 SELECT first_name, email FROM some_table WHERE email LIKE '%6@gmail.com'; Exclude first names with exactly one character followed by “ar” then anything: 1 WHERE first_name NOT LIKE '_ar%'; First name starting with B followed by I/R/O and any characters, and city with no “UL” anywhere: 1 2 WHERE first_name LIKE 'B[IRO]%' AND city NOT LIKE '%UL%'; 6. IN and BETWEEN in practice IN – simplifies multiple equality checks: 1 2 WHERE shirt_size IN ('M', 'L', 'XL', 'XXL') ORDER BY shirt_size ASC; BETWEEN – range on numbers, dates, etc. (inclusive of endpoints): 1 WHERE dress_size BETWEEN 2 AND 16; Combine with functions like DATENAME to extract parts of dates: Birth month: DATENAME(MONTH, birthday) AS birthmonth. Birth year: DATENAME(YEAR, birthday) AS birthyear. Example: people with dress size 2–16 and pant size containing L, ordered by birth month: 1 2 3 4 5 6 7 SELECT first_name, pant_size, DATENAME(MONTH, birthday) AS birthmonth FROM friends WHERE dress_size BETWEEN 2 AND 16 AND pant_size LIKE '%L%' ORDER BY birthmonth; 7. COALESCE and its relation to CASE COALESCE COALESCE(expr1, expr2, …) returns the first non‑null expression; if all are null, returns null (or the last specified default). Classic use: replace nulls with a default value in a result set. 1 2 3 4 SELECT first_name, favorite_color, COALESCE(shirt_size, '4XL') AS shirt_size_filled FROM company_shirts; Existing non‑null values are preserved; only nulls get the default replacement. Equivalent CASE pattern The same logic can be expressed with a CASE expression: 1 2 3 4 5 6 7 SELECT first_name, favorite_color, CASE WHEN shirt_size IS NULL THEN '4XL' ELSE shirt_size END AS shirt_size_filled FROM company_shirts; CASE is more human‑readable and flexible but more verbose; COALESCE is succinct but slightly less explicit. 8. Quick revision checklist for interview Use this as a rapid checklist when revising:
...