Guides

Jan 29, 2024 · 6 MIN READ

SQL Interview Preparation Guide: Master Core Concepts and Queries

Here is a compact, “no‑gaps” revision summary of the transcript for you.

1. SQL interview mindset and structure

Interviews are often pen‑and‑paper or purely conversational, even for SQL.
Aim to explain clearly, not just “get the right answer”; interviewers care about your thinking process and examples.
Build confidence with four habits:
- Study terminology: know clear definitions and be able to give a concrete example for each concept.
- Comment your thought process: when practicing, write comments describing each step and why you do it.
- Find “good enough”: master a solid core, then expand; avoid trying to learn “everything” at once.
- Ask for clarification: if a question is unclear or underspecified, ask follow‑ups before answering.

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 the table in many dialects).

Relationship types

One‑to‑one: one row relates to exactly one row in another table (e.g., one teacher → one subject).
One‑to‑many: one row relates to many rows (e.g., one teacher → 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:
- Stacks results from multiple SELECTs.
- Requires same number of columns, compatible data types, and same column order.
- Removes duplicate rows.
UNION ALL:
- Same structural requirements.
- Keeps duplicates; generally faster.

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 ….

Actual order of execution

Logical execution order:
1. FROM – choose tables / joins.
2. WHERE – row‑level filtering.
3. GROUP BY – create groups.
4. HAVING – filter groups (often on aggregates).
5. SELECT – project columns / expressions.
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

SUM() – 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 (CREATE, ALTER, DROP).
DML (Data Manipulation Language): changes data (INSERT, UPDATE, DELETE).
DCL (Data Control Language): permissions (GRANT, REVOKE) – not covered deeply in the course.
TCL (Transaction Control Language): manages transactions (COMMIT, ROLLBACK, savepoints).
DQL (Data Query Language): querying data (primarily SELECT).

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):

1CREATE TABLE employee_info (
2    id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
3    first_name NVARCHAR(75),
4    last_name  NVARCHAR(75),
5    email      NVARCHAR(125)
6);

Use comments (--) to describe purpose of each block; this helps in written interviews.

Inserting (seeding) data

Pattern:

1INSERT INTO employee_info (first_name, last_name, email)
2VALUES ('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:

1ALTER TABLE employee_info
2DROP COLUMN email;

Add columns (can add multiple at once):

1ALTER TABLE employee_info
2ADD city NVARCHAR(100), department NVARCHAR(100);

Change data type of a column:

1ALTER TABLE some_table
2ALTER 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:

1SELECT first_name, email
2FROM some_table
3WHERE email LIKE '%6@gmail.com';

Exclude first names with exactly one character followed by “ar” then anything:
```
1WHERE first_name NOT LIKE '_ar%';
```
First name starting with B followed by I/R/O and any characters, and city with no “UL” anywhere:
```
1WHERE first_name LIKE 'B[IRO]%'
2  AND city NOT LIKE '%UL%';
```

6. IN and BETWEEN in practice

IN – simplifies multiple equality checks:

1WHERE shirt_size IN ('M', 'L', 'XL', 'XXL')
2ORDER BY shirt_size ASC;

BETWEEN – range on numbers, dates, etc. (inclusive of endpoints):
```
1WHERE 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:

1SELECT first_name,
2       pant_size,
3       DATENAME(MONTH, birthday) AS birthmonth
4FROM friends
5WHERE dress_size BETWEEN 2 AND 16
6  AND pant_size LIKE '%L%'
7ORDER 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.

1SELECT first_name,
2       favorite_color,
3       COALESCE(shirt_size, '4XL') AS shirt_size_filled
4FROM 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:

1SELECT first_name,
2       favorite_color,
3       CASE 
4           WHEN shirt_size IS NULL THEN '4XL'
5           ELSE shirt_size
6       END AS shirt_size_filled
7FROM 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: