SQL – Common Interview Questions and Answers

SQL Interview questions and preparation can be a challenging task, specially if we are short on time. As we know, SQL (Structured Query Language) is an important skill for managing database, data analysis and building live dashboards in excel. SQL will be the building block of your journey to become a data analyst, along with Excel. So if you’re preparing for an interview, prepare well for SQL and Excel. This article can help you in getting ready for some of the most frequently asked questions in SQL interview.

I have prepared this list of questions basis my experience and as per discussion with colleagues. I have also provided clear explanations with screenshots. This list is not exhaustive so after you’ve practiced these, do visit the other articles to brush up your basics and build practical knowledge.

Q1. What is SQL? (Most Basic SQL Interview Question)

SQL stands for – Structured Query Language. We use SQL to communicate with large databases and to manage databases. We use SQL to fetch raw data from the database. Once we have the raw data, we can analyse it in Excel, Power BI etc to build interactive dashboards, beautiful summaries and presentations.

SQL also helps in building databases so we can store, modify and even delete data from databases. Today, business of all sizes, from small scale to large companies, use SQL to manage data.

Q2. What are the main types of SQL commands? (Popular SQL Interview Question)

SQL commands are basically of 5 main categories:

1. DDL (Data Definition Language) –

   These are used for defining and modifying database structure. They are of following types –

   a. CREATE – for creating tables and indexes. For ex- (CREATE TABLE users……).

   b. ALTER – for modifying existing databases, like adding or deleting columns. For ex – (ALTER TABLE users ADD COLUMN age INT….).

   c. DROP – permanently deletes tables, indexes etc. For ex – (DROP TABLE users).

   d. TRUNCATE – deletes only records without deleting the structure. For Ex – (TRUNCATE TABLE users).

2. DML (Data Manipulation Language) –

   These commands are used for operations like inserting, deleting and updating data. They are of following types –

   a. MERGE – merges two tables based on a condition.

   b. INSERT – adds new rows to the table.

   c. UPDATE – for changing / updating the values of columns.

   d. DELETE – removes records that meet a specific criteria.

3. DCL (Data Control Language) –

   These commands help in managing who can access the data by limiting access control and permissions. They are –

   a. GRANT – provides access to certain users to run commands like SELECT, INSERT etc.

   b. REVOKE – removes previously provided accesses.

4. TCL (Transaction Control Language) –

   These commands ensure data consistency.

   a. COMMIT – permanently saves all the changes.

   b. ROLLBACK – used for undoing current changes.

   c. SAVEPOINT – pinpoints a specific point in the transaction and we can undo up to that point.

     5. DQL (Data Query Language) –

DQL is used to fetch and display data from database. It consists of SELECT- the most basic and always the first command in SQL. DQL fetches data from tables which you can further filter and sort.

Q3. What is the difference among DELETE, TRUNCATE and DROP?

1. DELETE – it removes specific rows depending on a condition and also allows rollback (undo).
2. TRUNCATE – it deletes all rows from a table but can’t be undone.
3. DROP – Removes the entire table and its structure permanently but this too can’t be undone.

DELETE is commonly used when we need to delete only selective rows. Whereas TRUNCATE is required for deleting all rows but retaining the table structure. DROP is the most drastic operation as it removes the table entirely. Hence it should be used carefully. Practice these well for your SQL interview questions.

Q4. Explain INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. (Most important SQL Interview Question).

Prepare well for JOINs, not just for SQL interview questions, but also to enhance your skill-sets as they are widely used.

1. INNER JOIN – Retrieves only the matching rows from both tables.
2. LEFT JOIN – Returns all records from the left table and matched rows from the right table.
3. RIGHT JOIN – Returns all records from the right table and matched rows from the left table.
4. FULL JOIN – Combines all records from both tables, filling in NULLs where there is no match.

Example: Consider these two input tables –

Let’s run INNER JOIN on these two in following manner –

“Output Table (Result of INNER JOIN)
SELECT customers.name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;”

Here is the out put –

Learn more about JOINs in this article

Q5. What are Primary Keys and Foreign Keys?

1. Primary Key – these are unique identifier for each row in a table (can’t contain NULL values).
2. Foreign Key – serve as a reference to a Primary Key in another table, thereby ensuring referential integrity.

Primary keys ensure uniqueness in records while foreign keys maintain relationships between tables which enforce data consistency.

Q6. What is Normalization? What are its forms?

Normalization is done for organizing data to remove redundancy and improve consistency. It is of following types –

1. 1NF – it ensures that each column has atomic values by removing duplicates.
2. 2NF – it eliminates partial dependency. Thus ensuring that all non key attributes depend on the whole primary key.
3. 3NF – Removes transitive dependency, hence ensuring attributes depend only on the primary key.
4. BCNF – it is a stricter form of 3NF addressing anomalies.

Basically, normalization enhances efficiency of database and removes unnecessary duplication, improving data integrity.

Q7. What is an Index in SQL? What are its types?

Index plays a critical part in improving query performance by reducing the time it takes to run the query. Index is therefore about organizing the data in a systematic manner. Indexes are following types –

1. Clustered Index – this index is used for sorting and storing data physically (one per table).
2. Non-Clustered Index – maintains a separate structure from the data (multiple allowed per table).
3. Unique Index – ensures uniqueness by checking that all values in a column are distinct.
4. Composite Index – Covers multiple columns.

Indexes thus provide major improvement in the performance of SELECT queries, but we should avoid too much indexing because it can slow down INSERT and UPDATE operations.

Q8. When should you use WHERE vs. HAVING? (Another popular SQL Interview Question)

1. WHERE – is used for filtering rows before aggregation. It is used with SELECT, UPDATE and DELETE.
2. HAVING – does the same job but it filters aggregated results. It is used with GROUP BY.

1st Example – “select customer_id, SUM(amount) from payment group by customer_id having sum(amount)>100″

2nd Ex – “select customer_id, count(amount) from payment group by customer_id having count(amount)>400”

3rd Example – “select customer_id, staff_id, sum(amount) from payment where staff_id=2 group by customer_id having sum(amount)>110”

Q9. What are ACID properties in SQL?

These properties maintain reliability of database transactions. ACID stands for –

1. Atomicity – Ensures that all operations in a transaction succeed or fail together.
2. Consistency – Maintains integrity of database.
3. Isolation – Prevents interference between concurrent transactions.
4. Durability – Guarantees committed changes remain permanently.

ACID properties ensure that databases remain accurate and secure even if there is a failure.

Q10. Difference between UNION and UNION ALL?

1. UNION – is used for merging query results by removing duplicates.
2. UNION ALL – Merges query results without removing duplicates.

Example:
“SELECT city FROM customers UNION
SELECT city FROM suppliers;”

Q11. What is a Stored Procedure?

We can think of Stored Procedures as a set of SQL statements which are executed together as a program.
Example:

“CREATE PROCEDURE GetEmployees()
AS
BEGIN
SELECT * FROM employees;
END;”

Thus, we can avoid using repetitive queries via Stored procedures, thereby improving performance and security.

Q12. What is a View in SQL?

A view is a temporary virtual table used of simplifying complex queries. However, a view does not store any data.

Example:

“CREATE VIEW ActiveCustomers AS
SELECT name, email FROM customers WHERE status = ‘Active’;”

Views also help in improving security by restricting direct access to tables.

Q13. Explain SQL Subqueries.

A subquery is a nested query inside another query that helps filter or aggregate data.

Example:

“SELECT name FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);”

Q14. Explain Recursive CTEs (Common Table Expressions).

Recursive CTEs allow queries to repeatedly call themselves, hence they are useful in hierarchical data.

Example:

“WITH EmployeeHierarchy AS (
SELECT employee_id, manager_id, name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;”

Q15. What is the difference between CROSS JOIN and NATURAL JOIN?

1. CROSS JOIN – these are used for creating a Cartesian product of both tables.
2. NATURAL JOIN – automatically joins tables based on common column names.

Q16. Explain Window Functions in SQL.

Window functions are certainly super helpful in performing calculations across rows without collapsing them.

Example:

“SELECT name, salary, RANK () OVER (ORDER BY salary DESC) AS rank FROM employees;”

Q17. Explain the difference between CHAR and VARCHAR?

1. CHAR is a fixed length string. For example – CHAR(10) will always store 10 characters.
2. VARCHAR, on the other hand, is a variable length string.

Final Thoughts

Thus, being proficient in SQL is important for database professionals, data analysts and software engineers. Practicing these frequently asked SQL interview questions will help you in preparing effectively. Therefore, stay consistent with practice and explore real world SQL scenarios to sharpen your skills. All the best!

Also read up on Microsoft SQL server here