Top 50 SQL Interview Questions for Entry-Level Developers

Top 50 SQL Interview Questions for Entry-Level Developers

SQL (Structured Query Language) remains one of the most in-demand skills for software developers, data analysts, and database administrators. Whether you're applying for a backend developer role, a data engineering position, or a full-stack job, you'll likely face SQL questions during the technical interview. This tutorial covers the top 50 SQL interview questions that entry-level developers encounter, complete with explanations, code examples, and best practices.

Why SQL Interview Preparation Matters

SQL is the universal language of relational databases. Almost every application you build will interact with a database at some point. Interviewers use SQL questions to assess your ability to think logically about data, understand relationships between tables, and write efficient queries. Mastering these fundamentals demonstrates that you can work with real-world data systems effectively.

Section 1: Basic SQL Concepts (Questions 1-10)

Q1: What is SQL and what are its main subsets?

SQL is a standard programming language designed for managing and manipulating relational databases. Its main subsets are:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DQL (Data Query Language): SELECT
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

Q2: What is the difference between SQL and MySQL?

SQL is a query language standard, while MySQL is a relational database management system (RDBMS) that implements SQL. SQL defines the rules and syntax; MySQL is one of many software products (along with PostgreSQL, SQLite, SQL Server) that use SQL to manage databases.

Q3: What is a primary key?

A primary key is a column (or set of columns) that uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values. A table can have only one primary key.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

Q4: What is a foreign key?

A foreign key is a column that references the primary key of another table, establishing a relationship between the two tables. It enforces referential integrity.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    emp_id INT,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

Q5: What is the difference between DELETE and TRUNCATE?

• DELETE: Removes rows one by one, can use a WHERE clause, can be rolled back, and triggers are fired.
• TRUNCATE: Removes all rows at once, cannot use WHERE, is faster, resets auto-increment counters, and cannot be rolled back in some databases.

-- DELETE: removes specific rows
DELETE FROM employees WHERE emp_id = 5;

-- TRUNCATE: removes all rows
TRUNCATE TABLE employees;

Q6: What is the difference between DROP and TRUNCATE?

DROP removes the entire table structure along with its data, indexes, and constraints. TRUNCATE only removes the data while keeping the table structure intact.

-- Removes table completely
DROP TABLE employees;

-- Empties the table but keeps structure
TRUNCATE TABLE employees;

Q7: What are the different types of SQL commands?

SQL commands are categorized into five types: DDL, DML, DQL, DCL, and TCL, as described in Q1. Understanding these categories helps you organize your knowledge of SQL operations.

Q8: What is a NULL value in SQL?

NULL represents missing or unknown data. It is not the same as zero or an empty string. You cannot use comparison operators like = or != with NULL; instead, use IS NULL or IS NOT NULL.

-- Find employees with no email
SELECT * FROM employees WHERE email IS NULL;

-- Find employees who have an email
SELECT * FROM employees WHERE email IS NOT NULL;

Q9: What is the difference between CHAR and VARCHAR?

CHAR is a fixed-length data type that pads with spaces, while VARCHAR is a variable-length data type that stores only the actual characters. Use CHAR when all values are roughly the same length, and VARCHAR when lengths vary significantly.

CREATE TABLE example (
    code CHAR(5),        -- Always uses 5 characters
    description VARCHAR(100)  -- Uses only what's needed
);

Q10: What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after aggregation. HAVING is typically used with GROUP BY.

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 5;

Section 2: Data Retrieval and Filtering (Questions 11-20)

Q11: How do you select all columns from a table?

Use the asterisk (*) wildcard to select all columns. However, in production code, it's best practice to list specific columns explicitly for performance and clarity.

-- Select all columns
SELECT * FROM employees;

-- Best practice: specify columns
SELECT emp_id, first_name, last_name FROM employees;

Q12: How do you filter data using the WHERE clause?

The WHERE clause filters records based on specified conditions. You can use operators like =, !=, <, >, <=, >=, BETWEEN, IN, LIKE, and logical operators AND, OR, NOT.

SELECT * FROM employees
WHERE salary >= 50000
  AND department = 'Engineering'
  AND hire_date BETWEEN '2020-01-01' AND '2023-12-31';

Q13: What does the DISTINCT keyword do?

DISTINCT removes duplicate rows from the result set, returning only unique values for the specified columns.

-- Get unique departments
SELECT DISTINCT department FROM employees;

-- Get unique department and job title combinations
SELECT DISTINCT department, job_title FROM employees;

Q14: How does the LIKE operator work?

LIKE is used for pattern matching with wildcards. The percent sign (%) matches zero or more characters, and the underscore (_) matches exactly one character.

-- Names starting with 'J'
SELECT * FROM employees WHERE first_name LIKE 'J%';

-- Names ending with 'son'
SELECT * FROM employees WHERE last_name LIKE '%son';

-- Names with 'a' as the second character
SELECT * FROM employees WHERE first_name LIKE '_a%';

Q15: What is the IN operator?

The IN operator allows you to specify multiple values in a WHERE clause, serving as a shorthand for multiple OR conditions.

SELECT * FROM employees
WHERE department IN ('Engineering', 'Sales', 'Marketing');

Q16: How do you sort results with ORDER BY?

ORDER BY sorts the result set by one or more columns, either in ascending (ASC, default) or descending (DESC) order.

SELECT * FROM employees
ORDER BY department ASC, salary DESC;

Q17: What is the LIMIT clause and how is it used?

LIMIT restricts the number of rows returned. It's useful for pagination and testing. Different databases use different syntax (LIMIT in MySQL/PostgreSQL, TOP in SQL Server, ROWNUM in Oracle).

-- MySQL/PostgreSQL: Get top 5 highest paid employees
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

-- SQL Server
SELECT TOP 5 * FROM employees
ORDER BY salary DESC;

Q18: What is the difference between UNION and UNION ALL?

UNION combines result sets from two queries and removes duplicates. UNION ALL combines result sets without removing duplicates, making it faster.

-- Removes duplicates
SELECT city FROM employees
UNION
SELECT city FROM customers;

-- Keeps duplicates (faster)
SELECT city FROM employees
UNION ALL
SELECT city FROM customers;

Q19: How do you use aliases in SQL?

Aliases provide temporary names for columns or tables, making results more readable and queries more concise.

SELECT 
    e.first_name AS "First Name",
    e.last_name AS "Last Name",
    e.salary AS "Annual Salary"
FROM employees AS e;

Q20: What are CASE statements and how do you use them?

CASE statements provide conditional logic similar to if-else statements in programming languages.

SELECT 
    first_name,
    last_name,
    salary,
    CASE 
        WHEN salary >= 100000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band
FROM employees;

Section 3: Joins and Relationships (Questions 21-30)

Q21: What is a JOIN in SQL?

A JOIN combines rows from two or more tables based on a related column between them. Joins are fundamental for querying normalized databases where data is spread across multiple tables.

Q22: What is an INNER JOIN?

INNER JOIN returns only the rows that have matching values in both tables. It's the most common join type.

SELECT e.first_name, e.last_name, o.order_id, o.order_date
FROM employees e
INNER JOIN orders o ON e.emp_id = o.emp_id;

Q23: What is a LEFT JOIN?

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. Unmatched rows from the right table appear as NULL.

SELECT e.first_name, e.last_name, o.order_id
FROM employees e
LEFT JOIN orders o ON e.emp_id = o.emp_id;

Q24: What is a RIGHT JOIN?

RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and matched rows from the left table. It's essentially the reverse of LEFT JOIN.

SELECT e.first_name, o.order_id, o.order_date
FROM employees e
RIGHT JOIN orders o ON e.emp_id = o.emp_id;

Q25: What is a FULL OUTER JOIN?

FULL OUTER JOIN returns all rows when there's a match in either the left or right table. Unmatched rows from either table will have NULL for the columns of the other table.

SELECT e.first_name, o.order_id
FROM employees e
FULL OUTER JOIN orders o ON e.emp_id = o.emp_id;

Q26: What is a CROSS JOIN?

CROSS JOIN returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table.

SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

Q27: What is a SELF JOIN?

A SELF JOIN is when a table is joined with itself. It's useful for hierarchical data, like finding an employee's manager.

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Q28: What is the difference between JOIN and UNION?

JOIN combines columns from different tables horizontally based on a condition. UNION combines rows from different queries vertically, stacking result sets on top of each other.

Q29: Can you join more than two tables?

Yes, you can join multiple tables by chaining JOIN clauses. Each JOIN connects one additional table to the result.

SELECT e.first_name, d.department_name, o.order_id
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
INNER JOIN orders o ON e.emp_id = o.emp_id;

Q30: What is a natural join?

A NATURAL JOIN automatically joins tables based on columns with the same name and data type. While convenient, it's generally discouraged because implicit behavior can lead to unexpected results.

-- Joins on all columns with matching names
SELECT * FROM employees
NATURAL JOIN departments;

Section 4: Aggregation and Grouping (Questions 31-40)

Q31: What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.

SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_payroll
FROM employees;

Q32: How does GROUP BY work?

GROUP BY groups rows that have the same values into summary rows. It's typically used with aggregate functions to produce per-group statistics.

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Q33: What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

• COUNT(*): Counts all rows including NULLs.
• COUNT(column): Counts non-NULL values in the specified column.
• COUNT(DISTINCT column): Counts unique non-NULL values.

SELECT 
    COUNT(*) AS total_rows,
    COUNT(email) AS employees_with_email,
    COUNT(DISTINCT department) AS unique_departments
FROM employees;

Q34: How do you use HAVING with GROUP BY?

HAVING filters groups after aggregation. Use it when you need to filter based on aggregate function results.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

Q35: What is the order of execution of SQL clauses?

The logical execution order differs from the written order: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Understanding this helps you write correct queries.

Q36: How do you find the second highest salary?

This is a classic interview question. There are multiple approaches:

-- Using LIMIT and OFFSET (MySQL/PostgreSQL)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Using a subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Q37: What is a subquery?

A subquery is a query nested inside another query. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses.

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Q38: What is the difference between a correlated and non-correlated subquery?

A non-correlated subquery can run independently of the outer query. A correlated subquery references columns from the outer query and executes once for each row of the outer query.

-- Non-correlated subquery
SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'NYC');

-- Correlated subquery
SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

Q39: What are the ANY and ALL operators?

ANY returns true if any subquery value meets the condition. ALL returns true only if all subquery values meet the condition.

-- Employees earning more than at least one employee in Sales
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Sales');

-- Employees earning more than all employees in Sales
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');

Q40: What is the EXISTS operator?

EXISTS checks whether a subquery returns any rows. It's often more efficient than IN for large datasets because it stops scanning once a match is found.

SELECT first_name, last_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id
);

Section 5: Advanced Concepts and Best Practices (Questions 41-50)

Q41: What is normalization?

Normalization is the process of organizing data to reduce redundancy and improve data integrity. Common normal forms include 1NF (atomic values), 2NF (no partial dependencies), and 3NF (no transitive dependencies).

Q42: What is denormalization?

Denormalization is the deliberate process of adding redundancy to a database to improve read performance. It's often used in data warehousing and reporting systems where read speed matters more than write efficiency.

Q43: What is an index and why is it used?

An index is a data structure that improves the speed of data retrieval operations. Indexes are created on columns frequently used in WHERE, JOIN, and ORDER BY clauses. However, they slow down INSERT, UPDATE, and DELETE operations.

-- Create an index
CREATE INDEX idx_employee_email ON employees(email);

-- Create a composite index
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

Q44: What is the difference between clustered and non-clustered indexes?

A clustered index determines the physical order of data in a table; a table can have only one clustered index. A non-clustered index is a separate structure with pointers to the data; a table can have multiple non-clustered indexes.

Q45: What are ACID properties?

ACID properties ensure reliable transaction processing in databases:

• Atomicity: All operations in a transaction succeed or fail together.
• Consistency: Transactions bring the database from one valid state to another.
• Isolation: Concurrent transactions don't interfere with each other.
• Durability: Committed transactions are permanent even after system failures.

Q46: What are transactions and how do you use them?

A transaction is a unit of work that may include multiple operations. Use BEGIN, COMMIT, and ROLLBACK to manage transactions.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- If both succeed
COMMIT;

-- If something goes wrong
-- ROLLBACK;

Q47: What is a view and when would you use one?

A view is a virtual table based on the result of a query. Views are used to simplify complex queries, provide data security by restricting access to specific columns, and present data in a consistent format.

CREATE VIEW employee_summary AS
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Use the view like a regular table
SELECT * FROM employee_summary;

Q48: What are stored procedures?

Stored procedures are precompiled SQL statements stored in the database. They improve performance, promote code reuse, and enhance security by controlling data access.

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END;

-- Call the procedure
CALL GetEmployeesByDepartment('Engineering');

Q49: What are common SQL performance optimization techniques?

• Use indexes strategically on frequently queried columns.
• Avoid SELECT *; specify only needed columns.
• Use EXISTS instead of IN for large subqueries.
• Limit result sets with WHERE and LIMIT clauses.
• Avoid functions on indexed columns in WHERE clauses.
• Use JOINs instead of subqueries when possible.
• Normalize tables to reduce redundancy but denormalize for read-heavy workloads.

-- Bad: function on indexed column prevents index usage
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- Good: uses the index efficiently
SELECT * FROM employees 
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

Q50: What are window functions and how do they differ from aggregate functions?

Window functions perform calculations across a set of rows related to the current row, without collapsing the result set like aggregate functions do. They use the OVER() clause to define the window of rows.

SELECT 
    first_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Window functions like ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), and LAG() are powerful tools for analytics queries and frequently appear in more advanced interviews.

Best Practices for SQL Interviews

Write Clean, Readable Queries

Format your SQL with proper indentation, use meaningful aliases, and add comments where necessary. Interviewers appreciate code that's easy to read and understand.

-- Clear and well-formatted
SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
INNER JOIN departments d 
    ON e.dept_id = d.dept_id
WHERE e.salary > 50000
ORDER BY e.salary DESC;

Clarify Requirements Before Writing Code

Ask questions about the data, expected output, and edge cases before writing your query. This demonstrates analytical thinking and prevents you from solving the wrong problem.

Test Your Logic Mentally

Walk through your query step by step with sample data to verify correctness. Consider edge cases like NULL values, empty result sets, and duplicate records.

Understand the Data Model

Before writing joins or subqueries, make sure you understand the table relationships, primary keys, foreign keys, and data types. Ask for a schema diagram if one isn't provided.

Practice Regularly

Use platforms like LeetCode, HackerRank, and SQLZoo to practice SQL problems. Build a sample database and experiment with different query approaches to deepen your understanding.

Conclusion

Mastering these 50 SQL interview questions gives you a solid foundation for entry-level developer interviews. The key themes to remember are understanding basic SQL syntax, knowing the differences between similar concepts (like JOIN types and DELETE vs TRUNCATE), being able to write queries for common data retrieval scenarios, and following best practices for performance and readability. Remember that interviewers care not just about getting the right answer, but also about how you think through problems and communicate your approach. Practice these concepts hands-on with a real database, build your own sample datasets, and work through problems until writing SQL feels natural. With consistent practice and a clear understanding of these fundamentals, you'll be well-prepared to tackle SQL questions confidently in any technical interview.