Beginner SQL Interview Questions and Answers

SQL (Structured Query Language) is the standard language for reading and changing data in relational databases. A relational database stores data in tables of rows and columns, and connects tables through keys. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are relational database systems that all speak SQL. See What is SQL?.

A primary key uniquely identifies each row in its own table; it must be unique and not NULL. A foreign key is a column that refers to the primary key of another (or the same) table, linking related rows and preventing references to rows that do not exist. In our table, id is the primary key and manager_id is a foreign key pointing back to id.

Think of reading data, changing data, changing structure, and permissions.

Some sources fold SELECT into DML; mention that both conventions exist.

• DELETE removes selected rows (with WHERE), one by one, and can be rolled back.
• TRUNCATE removes all rows quickly and usually resets auto-increment counters; it cannot have a WHERE.
• DROP removes the whole table: data and structure.

See DELETE and DROP TABLE.

NULL means a value is missing or unknown. It is not zero and not an empty string. Any comparison with NULL using = or <> gives "unknown", so WHERE manager_id = NULL returns nothing. Use IS NULL / IS NOT NULL, and COALESCE(column, default) to replace missing values. See NULL Values.

WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and is where conditions like COUNT(*) > 1 go. See HAVING.

SELECT DISTINCT removes duplicate rows from the result. With several columns, it removes rows where the whole combination is repeated. COUNT(DISTINCT column) counts unique non-NULL values. See DISTINCT.

CHAR(n) always stores exactly n characters, padding shorter values with spaces; it suits fixed-length codes. VARCHAR(n) stores up to n characters with no padding; it is the normal choice for names, emails and other variable text. See Data Types.

The second-highest salary is the highest salary that is lower than the maximum.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

second_highest
--------------
78000

This handles a tie at the top correctly. Mention the alternative ORDER BY salary DESC LIMIT 1 OFFSET 1, and that it gives the wrong answer if two people share the top salary. DENSE_RANK generalises it to the Nth highest.

SELECT name
FROM employees
WHERE name LIKE 'A%' OR name LIKE '%a';

Asha matches both patterns, Meera and Priya match the second. % matches any number of characters; _ matches exactly one.

Group by the column and keep groups with more than one row.

Cities shared by more than one employee:

SELECT city, COUNT(*) AS n
FROM employees
GROUP BY city
HAVING COUNT(*) > 1;

city   | n
-------+--
Mumbai | 2

The same pattern finds duplicate emails, phone numbers or order references.