SQL Interview Preparation Guide: Master Core Concepts and Queries

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:
1. FROM – choose tables / joins. [where data is coming from]
2. WHERE – row‑level filtering. [what is returned]
3. GROUP BY – create groups. [how data is grouped]
4. HAVING – filter groups (often on aggregates). [which characteristics]
5. SELECT – project columns / expressions. [what data is returned]
6. 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:

Can I define and give an example of: alias, primary key, foreign key, constraint, one‑to‑one / one‑to‑many / many‑to‑many, union vs union all?
Do I remember the six main clauses and the logical execution order?
Can I write from scratch: CREATE TABLE, INSERT, ALTER ADD/DROP/ALTER COLUMN, TRUNCATE, DROP TABLE?
Can I explain when to use TRUNCATE vs DROP, and what to do about foreign key constraints?
Am I comfortable with LIKE + %, _, [], and with IN and BETWEEN in realistic queries?
Can I use COALESCE and equivalent CASE to handle nulls?
Am I prepared to talk through my approach, comment my logic, and ask for clarification when something is unclear?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

CREATE TABLE orders (
    order_id     INT IDENTITY(1,1) PRIMARY KEY,          -- Primary Key / Most common inline syntax (column-level)
           -- Other weays to define primary key
    CONSTRAINT pk_orders PRIMARY KEY (order_id)         -- Other ways to define primary key
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id) -- Composite primary key

    customer_id  INT NOT NULL,                            -- FK column (no nulls)
    order_email  NVARCHAR(100) UNIQUE,                    -- Unique key



-- PRIMARY KEY (PK): Uniquely identifies each row
order_id INT PRIMARY KEY,                      -- Simple inline primary key
CONSTRAINT pk_orders PRIMARY KEY(order_id),    -- Named primary key
CONSTRAINT pk_enrollments PRIMARY KEY(student_id, course_id),  -- Composite primary key (multiple columns)

-- FOREIGN KEY (FK): Ensures referential integrity
CONSTRAINT fk_orders_customers 
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id),  -- Named table-level FK
customer_id INT REFERENCES customers(customer_id),               -- Inline FK (unnamed)

-- UNIQUE: No duplicate values
order_email NVARCHAR(100) UNIQUE,               -- Inline unique
CONSTRAINT uq_orders_email UNIQUE(order_email), -- Named unique

-- NOT NULL: Column must have a value
customer_id INT NOT NULL,                       -- Cannot be NULL

-- CHECK: Restricts values
order_amount DECIMAL(10,2) CHECK(order_amount>0),  -- Inline check constraint
CONSTRAINT ck_orders_amount CHECK(order_amount>0), -- Named check constraint

-- DEFAULT: Provides default value if none supplied
order_date DATETIME DEFAULT GETDATE(),          -- Inline default
CONSTRAINT df_orders_date DEFAULT GETDATE() FOR order_date  -- Named default constraint

How to explain this in one sentence (interview gold)

“order_id uniquely identifies each row, customer_id enforces a relationship to customers, order_email prevents duplicates, order_amount validates data with CHECK, order_date auto-fills using DEFAULT, and NOT NULL ensures required data.”

A Brief Overview of SQL:

// single line comment
/* multi line comment */
USING DBNameMovies;

SELECT TOP 10 WITH TIES   // result have first 10 rows. If WITH TIES is used -> same values rows will be returned
    filmName  Title / AS Title / [Movie Title]   // alias(use [] if there is any space in alias, AS optional)
    filmRunTimeMins/60        [Hours]                // 2   (ex 127 min)
    filmRunTimeMins%60        [minutes]             // 7  ➜ modulus operator
    filmRunTimeMins/60.0      [HoursInDecimal]       // 2.1
    (filmProductionCost-filmBudgetDollar)   [Profit]

    ,CASE
        WHEN filmRunTimeMin <= 90  THEN ‘Short’
        WHEN filmRunTimeMin <= 150 THEN ‘Medium’
        WHEN filmRunTimeMin <= 180 THEN ‘Long’
        ELSE ‘Epic’
    END AS [FilmDuration]

FROM
    dbo.filmTable          // dbo – schema (optional)

WHERE
    movieID % 2 != 0        // returns the odd rows of the table
    filmRunTime BETWEEN 100 AND 120   // returns a range 100-120
    filmRunTime IN (10, 20, 100)
    filmName = ‘Nemo’  AND/OR filmName=’B’     // text match
    filmName LIKE / NOT LIKE / <> ‘%nemo%’   // _ single character, % wildcard
    MONTH(filmDate) = 4         // returns film of April
    YEAR(filmDate) = 2012       // returns film of 2012 only

used operator: <, >, <=, >=, <>   // except that one ie id<>20 returns all except 20

    ,CASE
        WHEN filmRunTimeMin <= 90  THEN ‘Short’
        WHEN filmRunTimeMin <= 150 THEN ‘Medium’
        WHEN filmRunTimeMin <= 180 THEN ‘Long’
        ELSE ‘Epic’
    END = ‘Medium’

ORDER BY
    Duration ASC/DESC      // asc(abcd…) optional/by default. Desc(zyx…)
    ,filmName ASC           // if we need to filter by multiple columns

1. INNER JOIN and NATURAL JOIN are not the same**.#

2. Key SQL terminology and core concepts#

Aliases#

Relationship types#

UNION vs UNION ALL#

Main clauses in a SQL query#

Actual order of execution#

Constraints#

Aggregate functions (Used with GROUP BY, Aggregate functions are allowed only in HAVING.)#

Categories of SQL commands#

3. Creating, seeding, and modifying tables#

Creating a table#

Inserting (seeding) data#

ALTER – changing table structure#

4. TRUNCATE vs DROP (and constraints)#

TRUNCATE#

Handling foreign key constraints#

DROP#

5. Logical/comparison operators and wildcards#

Core keyword operators#

Wildcards (used with LIKE)#

Practice patterns with LIKE#

6. IN and BETWEEN in practice#

7. COALESCE and its relation to CASE#

COALESCE#

Equivalent CASE pattern#

8. Quick revision checklist for interview#

How to explain this in one sentence (interview gold)#

A Brief Overview of SQL:#