Top 50 SQL Interview Questions for All Experience Levels

SQL Interview Questions

Getting ready for an SQL interview can be easy with the right help! This guide has a list of SQL interview questions that cover everything you need to know, from basic topics like queries and joins to advanced ones like performance and indexing. Whether you’re new to SQL or have some experience, these SQL interview questions will help you feel confident and ready to do your best in any interview. If you have any doubts, let's explore the SQL Server tutorial to understand the SQL command easily.

In this Interview Tutorial, let's learn about the most commonly asked SQL Interview Questions.

Top SQL Interview Questions and Answers

Q 1. What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It is used for querying, inserting, updating, and deleting data in a database.

Q 2. What are the different types of SQL Commands?

SQL commands are divided into several categories, including:

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

Q 3. What is a primary key?

A primary key is a unique identifier for each record in a table. It ensures that no two rows can have the same value for the primary key column(s). Primary keys also automatically create a unique index for faster query performance.

Q 4. What is a foreign key?

A foreign key is a column or set of columns that establishes a link between data in two tables. It is a reference to the primary key in another table, ensuring referential integrity between the two tables.

Q 5. What is the difference between INNER JOIN and LEFT JOIN?

• INNER JOIN returns only the rows that have matching values in both tables.

• LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the right table.

 SELECT employees.name, departments.department_name
 FROM employees
 LEFT JOIN departments ON employees.department_id = departments.id;   

Q 6. What is normalization?

Normalization is the process of organizing data in a database to reduce redundancy and dependency by dividing large tables into smaller, related tables. This process aims to improve data integrity and avoid anomalies.

Q 7. What are the different normal forms in database normalization?

The main normal forms include:

• First Normal Form (1NF): Eliminate duplicate columns, ensure atomic values.
• Second Normal Form (2NF): Eliminate partial dependencies, ensure each non-key column is fully dependent on the primary key.
• Third Normal Form (3NF): Eliminate transitive dependencies, ensuring that non-key columns are not dependent on other non-key columns.
• Higher normal forms (BCNF, 4NF, 5NF) deal with more advanced data integrity issues.

Q 8. What is an index in SQL?

An index is a database object that speeds up data retrieval operations on a table. It creates a lookup table for faster searching. Indexes can be created on one or more columns of a table.

 CREATE INDEX idx_employee_name ON employees(name);   

Q 9. What is the difference between UNION and UNION ALL?

• UNION combines the results of two queries and removes duplicate rows.

• UNION ALL combines the results of two queries but includes duplicates.

 SELECT name FROM employees
 UNION
 SELECT name FROM contractors;  

Q 10. What is a subquery in SQL?

A subquery is a query nested inside another query. It is used to perform operations that require multiple steps or filters. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.

 SELECT name FROM employees
 WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');   

Q 11. What is the difference between DELETE and TRUNCATE?

• DELETE removes rows from a table based on a condition, and it can be rolled back. It can also fire triggers.

• TRUNCATE removes all rows from a table, and it cannot be rolled back. It does not fire triggers and is faster than DELETE.

Q 12. What is a view in SQL?

A view in SQL is a virtual table that provides a way to simplify complex queries. It is based on the result of a SELECT query, and it doesn’t store data physically but displays it dynamically.

 CREATE VIEW employee_details AS
 SELECT name, department FROM employees;   

Q 13. What is a stored procedure?

A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a unit. Stored procedures are used to encapsulate business logic, improving performance and code reusability.

 CREATE PROCEDURE GetEmployeeInfo()
 BEGIN
    SELECT * FROM employees;
 END;   

Q 14. What is the difference between CHAR and VARCHAR?

• CHAR is a fixed-length string, where the allocated space is always filled, padding with spaces if needed.

• VARCHAR is a variable-length string, storing only the number of characters entered without padding.

Q 15. What is a trigger in SQL?

A trigger in SQL is a special type of stored procedure that automatically executes in response to certain events on a table or view (e.g., INSERT, UPDATE, DELETE).

 CREATE TRIGGER update_salary
 BEFORE UPDATE ON employees
 FOR EACH ROW
 BEGIN
    SET NEW.salary = OLD.salary * 1.1;
 END;    

Q 16. What is a transaction in SQL?

A transaction is a sequence of one or more SQL operations that are executed as a single unit. Transactions ensure that data is consistent and reliable by following the ACID properties (Atomicity, Consistency, Isolation, Durability).

Q 17. 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(): Counts the number of rows.
• SUM(): Sums up numeric values.
• AVG(): Returns the average value.
• MAX(): Returns the maximum value.
• MIN(): Returns the minimum value.

Q 18. What is the difference between WHERE and HAVING?

• WHERE is used to filter rows before grouping.

• HAVING is used to filter groups after the GROUP BY clause has been applied.

 SELECT department, COUNT(*)
 FROM employees
 GROUP BY department
 HAVING COUNT(*) > 10;   

Q 19. What is a composite key?

A composite key is a primary key that consists of two or more columns used together to uniquely identify a record in a table.

 CREATE TABLE employee_project (
    employee_id INT,
    project_id INT,
    PRIMARY KEY (employee_id, project_id)
 );    

Q 20. What are the different types of JOINS in SQL?

The main types of join in SQL are:

• INNER JOIN: Returns only matching rows from both tables.
• LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL OUTER JOIN: Returns all rows from both tables, with NULL values where there is no match.

 SELECT a.name, b.department
 FROM employees a
 LEFT JOIN departments b ON a.department_id = b.id;    

Q 21. What is the difference between a clustered index and a non-clustered index?

A clustered index determines the physical order of data in a table, meaning the rows are stored on the disk in the same order as the clustered index. A non-clustered index, on the other hand, is a separate structure from the data table and only stores pointers to the data rows.

 CREATE CLUSTERED INDEX idx_name ON employees(name);

Q 22. What is a schema in SQL?

A schema is a logical container or grouping of database objects such as tables, views, and stored procedures. It helps organize and manage objects in a database.

 CREATE SCHEMA finance;

Q 23. What is the difference between the DELETE and DROP command?

DELETE removes data from a table based on a condition but does not remove the structure of the table. DROP removes the entire table or database structure, including all its data and associated constraints.

 DELETE FROM employees WHERE employee_id = 5;
 DROP TABLE employees;

Q 24. What is the use of the BETWEEN operator?

The BETWEEN operator is used to filter the result set within a specific range, including the boundary values.

 SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;

Q 25. What is a self-join in SQL?

A self join in SQL is a join where a table is joined with itself. It is useful when you need to compare rows within the same table.

 SELECT e1.name AS Employee, e2.name AS Manager
 FROM employees e1
 JOIN employees e2 ON e1.manager_id = e2.employee_id;

Q 26. What is the difference between IS NULL and IS NOT NULL?

IS NULL is used to check if a value is NULL, while IS NOT NULL is used to check if a value is not NULL.

 SELECT * FROM employees WHERE department_id IS NULL;
 SELECT * FROM employees WHERE department_id IS NOT NULL;

Q 27. What are the different types of constraints in SQL?

SQL constraints are rules applied to columns or tables to enforce data integrity. Types include:

• PRIMARY KEY: Uniquely identifies each record.
• FOREIGN KEY: Ensures referential integrity between two tables.
• UNIQUE: Ensures that all values in a column are unique.
• CHECK: Ensures that values in a column satisfy a specific condition.
• NOT NULL: Ensures that a column cannot have a NULL value.

Q 28. What is the RANK() function in SQL?

The RANK() function assigns a unique rank to each row within a partition of a result set. It allows handling ties, where rows with equal values receive the same rank.

 SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
 FROM employees;

Q 29. What is the difference between CHAR and TEXT data types?

CHAR is used for fixed-length strings, while TEXT is used for variable-length strings that can store large amounts of data.

Q 30. What is the purpose of the GROUP BY clause in SQL?

The GROUP BY clause is used to arrange identical data into groups, typically when using aggregate functions like COUNT, SUM, AVG, etc.

 SELECT department, COUNT(*) AS num_employees
 FROM employees
 GROUP BY department;

Q 31. What is a CROSS JOIN in SQL?

A CROSS JOIN in SQL returns the Cartesian product of two tables. Each row from the first table is joined with each row from the second table.

 SELECT employees.name, departments.department_name
 FROM employees
 CROSS JOIN departments;

Q 32. What is the HAVING clause used for?

The HAVING clause in SQL is used to filter results after an aggregation is applied. It is similar to WHERE, but WHERE works before aggregation, and HAVING works after.

 SELECT department, COUNT(*) AS num_employees
 FROM employees
 GROUP BY department
 HAVING COUNT(*) > 5;

Q 33. What is a CASE statement in SQL?

The CASE statement is used to create conditional logic within SQL queries, similar to an IF-ELSE statement in other programming languages.

 SELECT name, 
       CASE 
           WHEN salary > 50000 THEN 'High'
           WHEN salary <= 50000 THEN 'Low'
       END AS salary_range
 FROM employees;

Q 34. What is a stored function in SQL?

A stored function is a database object that encapsulates a set of SQL queries and operations into a single callable function. It returns a value when executed.

 CREATE FUNCTION calculate_bonus(salary DECIMAL) 
 RETURNS DECIMAL
 BEGIN
    RETURN salary * 0.10;
 END;

Q 35. How do you add a column to an existing table in SQL?

You can use the ALTER TABLE statement to add a column to an existing table.

ALTER TABLE employees ADD email VARCHAR(100);

Q 36. What is the DISTINCT keyword in SQL?

The DISTINCT keyword is used to return only unique values from a query, eliminating duplicates.

 SELECT DISTINCT department FROM employees;

Q 37. What is a UNIQUE constraint in SQL?

A UNIQUE constraint ensures that all values in a column are distinct and no duplicates are allowed.

 ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

Q 38. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN returns all rows from one table and the matching rows from the other table (if any), with NULLs for non-matching rows.

Q 39. What is a NULL value in SQL?

A NULL value represents the absence of any value or unknown data. It is different from an empty string or zero.

Q40. How do you remove duplicates from a query result in SQL?

To remove duplicates, you can use the DISTINCT keyword in your SELECT statement.

 SELECT DISTINCT name FROM employees;

Q 41. What is the difference between a subquery and a join?

A subquery is a query nested inside another query, while a JOIN is a way to combine columns from two or more tables based on a related column. Subqueries are often used for complex conditions or retrieving values to be used by the outer query.

Q 42. What are window functions in SQL?

Window functions are functions that perform a calculation across a set of table rows related to the current row. Examples include ROW_NUMBER(), RANK(), and SUM(). They allow for complex calculations like running totals without needing to use a GROUP BY.

Q 43. Explain the difference between UNION and UNION ALL.

UNION combines the results of two or more queries and removes duplicates. UNION ALL, on the other hand, combines results but retains duplicates. The choice between them depends on whether duplicates are acceptable in the result set.

Q 44. What is the COALESCE function in SQL?

The COALESCE function returns the first non-null value in a list of expressions. It's useful for replacing NULL values with a default value in queries.

 SELECT COALESCE(NULL, 'No Data', 'Available');

Q 45. What is the difference between the LIKE and ILIKE operators in SQL?

LIKE is case-sensitive in some databases (like MySQL), while ILIKE is case-insensitive (in PostgreSQL). Both are used for pattern matching, but ILIKE allows for more flexible comparisons without worrying about case.

Q 46. How do you use indexes to optimize SQL queries?

Indexes are used to improve the speed of data retrieval operations. By creating indexes on frequently searched columns or those used in JOINs or WHERE clauses, query performance can be significantly improved. However, they can slow down data modification operations like INSERT, UPDATE, and DELETE.

Q 47. What is the difference between CHAR and VARCHAR data types in SQL?

CHAR is used for fixed-length strings, and any space beyond the string's actual length is padded with spaces. VARCHAR is used for variable-length strings and stores only the characters entered without padding.

Q 48. What is a transactional database, and what is ACID compliance?

A transactional database ensures that database operations are performed correctly and fully. ACID (Atomicity, Consistency, Isolation, Durability) compliance guarantees that database transactions are processed reliably and ensures data integrity, even in cases of system failure.

Q 49. How can you prevent SQL injection in queries?

SQL injection can be prevented by using prepared statements or parameterized queries, which ensure that input data is treated as data and not executable code. Additionally, using ORM frameworks and validating user inputs can also help prevent this vulnerability.

Q 50. What is the purpose of the EXPLAIN keyword in SQL?

The EXPLAIN keyword is used to obtain information about the query execution plan in databases like MySQL and PostgreSQL. It helps you understand how the database engine executes a query, such as whether it uses indexes or scans the entire table. This is useful for query optimization.

Conclusion

Practicing SQL interview questions is essential for success in database interviews. These questions cover everything from basics like queries to advanced topics like optimization. By focusing on SQL interview questions, you can build confidence and showcase your skills effectively. With the right preparation, you’ll be ready to excel in any SQL interview.

ScholarHat understands how hard & challenging it can be for a fresher or a less experienced candidate even to get their first job. Our masterclasses have been created to help you learn and immerse yourself in the latest technology trends. Join our master classes to upgrade your tech skills with the latest skills trends, design, and practices.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE