SQL Basics Interview Questions

SQL is the language for defining, reading and changing data in a relational database; MySQL is one database server that runs it, like PostgreSQL, SQL Server, Oracle and SQLite. The core (SELECT, JOIN, GROUP BY) is the same everywhere, while date functions, pagination and upserts differ by dialect.

They group SQL statements by what they act on: structure, data, permissions or transactions.

Follow-up: can you roll back a DROP TABLE? Not in MySQL or Oracle, where DDL commits the open transaction; in PostgreSQL and SQL Server, yes.

Both reject duplicates. A table has one primary key, which is never NULL and identifies the row; it can have many unique keys, which usually allow NULL.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE);
INSERT INTO users VALUES (1, NULL), (2, NULL), (3, 'asha@example.com');
SELECT COUNT(*) AS total_rows, COUNT(email) AS emails FROM users;

The trap is NULL: two NULLs are not equal, so both NULL emails are accepted and the result is 3 | 1. PostgreSQL, MySQL, Oracle and SQLite allow many NULLs in a unique column by default; SQL Server allows one. See unique constraints.

A foreign key is a column whose values must exist in another table's primary or unique key, so a child row never points at a missing parent. ON DELETE CASCADE deletes the child rows when their parent is deleted.

PRAGMA foreign_keys = ON;
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept_id INTEGER REFERENCES departments(id) ON DELETE CASCADE
);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales');
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'John', 2), (3, 'Priya', 2);
DELETE FROM departments WHERE id = 2;
SELECT id, name FROM employees;

Deleting Sales removes John and Priya, leaving 1 | Asha; without CASCADE the delete would fail. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON. More in foreign keys.

A candidate key is a minimal set of columns that identifies a row. The primary key is the candidate key you choose (never NULL); the others are alternate keys, enforced with UNIQUE. A super key is any unique column set, a composite key spans several columns, and a foreign key references another table's key.

Follow-up: natural or surrogate key? Most schemas use a generated id as the primary key and keep the natural key (email, PAN number) as a UNIQUE alternate key, because natural keys can change.

DELETE removes rows (optionally filtered by WHERE), TRUNCATE removes every row at once, and DROP removes the table itself.

SQLite has no TRUNCATE; DELETE FROM t without a WHERE does the same job fast.

WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so only HAVING can use aggregates such as COUNT(*).

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT dept, COUNT(*) AS people, SUM(salary) AS payroll
FROM employees
WHERE salary > 60000
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY dept;

WHERE drops Karan before grouping, so HR disappears, and HAVING keeps Engineering and Sales. Put any condition that needs no aggregate in WHERE, so fewer rows get grouped. More in GROUP BY and HAVING.

Logically: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT (window functions included), DISTINCT, ORDER BY, then LIMIT. The order explains the classic errors: an aggregate or a window function cannot go in WHERE, and a SELECT alias works in ORDER BY but not in WHERE (SQLite allows it as an extension). The optimizer may execute in another physical order with the same result.

Any comparison with NULL is NULL (unknown), so NULL = NULL and NULL <> 1 are both NULL; only IS NULL tests for a missing value. In WHERE, unknown drops the row, so WHERE manager_id = NULL returns nothing. To treat NULLs as equal, use IS NOT DISTINCT FROM (PostgreSQL, SQLite 3.39+, SQL Server 2022+, see the SQL Server docs) or <=> in MySQL. See operators and NULL.

COUNT(*) counts rows, COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts distinct non-NULL values: 5 rows, 3 bonuses, 2 departments. COUNT(1) is the same as COUNT(*) in every major database, with no speed difference.

BETWEEN includes both ends, so it matches 2, 3 and 4. The trap is timestamps: created_at BETWEEN '2026-01-01' AND '2026-01-31' misses everything after midnight on the 31st. Use created_at >= '2026-01-01' AND created_at < '2026-02-01'.

With two integers, / drops the fraction, so 7 / 2 is 3; make either side a decimal (2.0, x * 1.0) to get 3.5. That holds in SQLite, PostgreSQL and SQL Server; MySQL and Oracle return a decimal. It bites in ratios: write 100.0 * COUNT(*) / total.

CHAR(n) is fixed length and pads shorter values with spaces; VARCHAR(n) stores only what you give it, up to n. Use CHAR for fixed-size codes like INR.

CREATE TABLE countries (code VARCHAR(3));
INSERT INTO countries VALUES ('INDIA');
SELECT code, length(code) FROM countries;

SQLite prints INDIA | 5 because it never enforces a declared length; even STRICT tables only check the base type, and they reject VARCHAR as a column type. PostgreSQL, SQL Server, Oracle and strict-mode MySQL reject the insert. More on type affinity.

Each database has its own syntax for a column that numbers new rows:

-- MySQL
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, item VARCHAR(50));
-- PostgreSQL 10+ and Oracle 12c+ (standard); older PostgreSQL used SERIAL
CREATE TABLE orders (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, item TEXT);
-- SQL Server
CREATE TABLE orders (id INT IDENTITY(1, 1) PRIMARY KEY, item VARCHAR(50));

In SQLite an INTEGER PRIMARY KEY is the row id and gets the largest id plus one, so without AUTOINCREMENT it can reuse a deleted last id; see the SQLite AUTOINCREMENT docs. The others do not reuse a value (MySQL before 8.0 could after a restart) but leave gaps (a rolled-back insert burns its number), so never count rows with MAX(id).

Use COALESCE(a, b, ...), which returns its first non-NULL argument in every database.

CREATE TABLE contacts (name TEXT, phone TEXT, email TEXT);
INSERT INTO contacts VALUES
  ('Asha', NULL, 'asha@example.com'),
  ('Ravi', '98450 12345', NULL),
  ('Meera', NULL, NULL);
SELECT name, COALESCE(phone, email, 'no contact') AS contact FROM contacts;

Two-argument versions: IFNULL (MySQL, SQLite), ISNULL (SQL Server), NVL (Oracle). The reverse, NULLIF(a, b), returns NULL when a = b, which avoids division by zero: total / NULLIF(count, 0).

% matches any run of characters, including none, and _ matches exactly one, so '_a%' means "second letter is a".

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT name FROM employees WHERE name LIKE '_a%' ORDER BY name;

The result is Karan and Ravi. PostgreSQL and Oracle LIKE is case-sensitive (PostgreSQL adds ILIKE); SQLite (ASCII letters only), MySQL and SQL Server ignore case by default. A leading % cannot use a B-tree index; a prefix like 'kum%' can.

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. They make the database reject bad data whichever application writes it.

-- fails on purpose: the CHECK constraint rejects a negative price
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price REAL CHECK (price >= 0),
  stock INTEGER DEFAULT 0
);
INSERT INTO products (name, price) VALUES ('Pen', 20);
INSERT INTO products (name, price) VALUES ('Refund', -5);

The second insert fails with CHECK constraint failed: price >= 0; the first gets stock = 0 from the default. MySQL parsed and ignored CHECK before 8.0.16; see the MySQL CHECK docs.

A view is a saved SELECT you query like a table. It stores the query, not the data, so it always shows current rows.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
CREATE VIEW high_earners AS
  SELECT name, dept, salary FROM employees WHERE salary > 85000;
SELECT * FROM high_earners ORDER BY salary DESC, name;

Use one to hide a complex join behind a name or to expose only some columns. A materialized view (PostgreSQL, Oracle) stores the result and must be refreshed; an indexed view in SQL Server stores it too and is updated on every write. See views.

SQLite, MySQL and SQL Server sort NULL first in ascending order, so the first row is NULL; PostgreSQL and Oracle put NULLs last. Say what you want: ORDER BY x NULLS LAST (PostgreSQL, Oracle, SQLite 3.30+), or ORDER BY CASE WHEN x IS NULL THEN 1 ELSE 0 END, x anywhere.

Sort with ORDER BY, then cut: LIMIT n in MySQL, PostgreSQL and SQLite, TOP n in SQL Server, and the standard FETCH FIRST n ROWS ONLY in PostgreSQL, Oracle 12c+ and SQL Server (after OFFSET 0 ROWS).

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT name, salary
FROM employees
ORDER BY salary DESC, name
LIMIT 3;

The result is Asha, Meera and Ravi. Without ORDER BY you get any 3 rows, and since Ravi and Meera tie at 90000, name is what fixes the order. More in LIMIT and OFFSET.