60 sql interview questions and answers for fresher and experienced

SQL interview questions and answers for fresher and experienced

Here is a comprehensive list of SQL interview questions along with their answers, covering basic, intermediate, and advanced levels of SQL knowledge.

Basic SQL Interview Questions:

What is SQL?

SQL stands for Structured Query Language. It is used to communicate with and manipulate databases.

What are tables and fields in SQL?

Tables: Collections of related data in rows and columns.

Fields: Columns in a table.

What is a Primary Key?

A unique identifier for each row in a table. No two rows can have the same primary key value.

What is a Foreign Key?

A foreign key is a column that creates a relationship between two tables by linking a primary key from another table.

What is a JOIN?

A JOIN clause is used to combine rows from two or more tables based on a related column between them.

What are the different types of JOINs?

INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), FULL JOIN (or FULL OUTER JOIN).

What is a UNIQUE constraint?

Ensures that all values in a column are unique.

What is the difference between WHERE and HAVING?

WHERE is used to filter rows before grouping, while HAVING is used to filter groups after applying the GROUP BY clause.

What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows, and TRUNCATE removes all rows from a table but keeps the table structure.

What is an Index in SQL?

Indexes improve the speed of data retrieval operations by creating a sorted copy of the data on selected columns.

What is normalization?

The process of organizing data in a database to avoid redundancy and improve data integrity.

What is denormalization?

The process of combining tables to reduce the complexity of database queries at the cost of increased redundancy.

What is a stored procedure?

A stored procedure is a prepared SQL code that you can save and reuse.

What is a trigger in SQL?

A trigger is a set of actions that are automatically executed in response to certain events on a table or view.

What is the difference between UNION and UNION ALL?

UNION combines the result sets of two queries, removing duplicates, whereas UNION ALL includes duplicates.

What is a subquery?

A query within another query.

What is a view in SQL?

A view is a virtual table that contains a result set from a stored query.

What is the difference between a View and a Table?

A table physically stores data; a view does not store data but provides a dynamic result of a query.

What is a constraint in SQL?

Constraints are rules applied to table columns to enforce data integrity (e.g., NOT NULL, UNIQUE, PRIMARY KEY).

What is the use of the DISTINCT keyword?

Used to remove duplicate values from the result set.

Intermediate SQL Interview Questions:

What is a Self Join?

A join where a table is joined with itself.

What is a Cartesian Join?

A Cartesian join returns the Cartesian product of the sets of records from two or more joined tables.

What is a Composite Key?

A key that consists of two or more columns to uniquely identify a row in a table.

What is the difference between CHAR and VARCHAR?

CHAR is a fixed-length character data type, whereas VARCHAR is a variable-length character data type.

What is a NULL value in SQL?

NULL represents a missing or undefined value.

How do you handle NULL values in SQL?

Use the IS NULL and IS NOT NULL conditions or COALESCE to handle NULL values.

What is the difference between GROUP BY and ORDER BY?

GROUP BY groups rows with similar values, while ORDER BY sorts the result set.

What is the purpose of the LIMIT clause?

It is used to restrict the number of rows returned by a query.

What is a transaction in SQL?

A transaction is a sequence of one or more SQL operations treated as a single unit of work.

What are ACID properties in SQL?

Atomicity, Consistency, Isolation, Durability—properties that ensure reliable transaction processing.

What is a cursor in SQL?

A cursor is a database object used to retrieve, manipulate, and traverse the rows of a query result set.

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

A clustered index determines the physical order of data in a table, while a non-clustered index does not.

What is an aggregate function?

A function that performs a calculation on a set of values and returns a single value (e.g., COUNT, SUM, AVG).

What is a scalar function?

A function that returns a single value (e.g., UPPER(), ROUND(), LEN()).

What are common aggregate functions in SQL?

COUNT, SUM, AVG, MAX, MIN.

What is the difference between COUNT and COUNT(*)?

COUNT(column) counts non-NULL values in a column, while COUNT(*) counts all rows, including those with NULLs.

How can you create an auto-incrementing column in SQL?

Use the AUTO_INCREMENT keyword in MySQL or SERIAL in PostgreSQL.

What is the difference between IN and EXISTS?

IN checks if a value exists within a given list, while EXISTS checks for the existence of rows returned by a subquery.

What are wildcards in SQL, and when are they used?

Wildcards are used with the LIKE operator to search for a pattern (e.g., % for any sequence of characters, _ for a single character).

What is the difference between RANK() and DENSE_RANK()?

RANK() leaves gaps in ranking when there are ties, whereas DENSE_RANK() does not leave gaps.

Advanced SQL Interview Questions:

What is a recursive query in SQL?

A query that refers to itself using a common table expression (CTE).

What is a CTE (Common Table Expression)?

A temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

What are window functions in SQL?

Functions that perform calculations across a set of table rows related to the current row (e.g., ROW_NUMBER, RANK, LEAD, LAG).

What is the difference between ROW_NUMBER() and RANK()?

ROW_NUMBER() provides a unique row number for every row, whereas RANK() gives the same rank for tied values but leaves gaps.

What is database partitioning?

The process of splitting a large database table into smaller, more manageable pieces.

What is sharding in databases?

Sharding is a method of distributing data across multiple machines to improve performance and scalability.

What are correlated subqueries?

Subqueries that reference columns from the outer query.

What is indexing, and when should you avoid over-indexing?

Indexing improves query performance, but over-indexing can slow down write operations and consume additional storage.

What is a full-text search in SQL?

A full-text search allows searching within large text fields by indexing words in text columns.

What is the use of SQL's MERGE statement?

It allows you to perform INSERT, UPDATE, and DELETE operations in a single statement based on the condition.

What are the differences between OLTP and OLAP databases?

OLTP (Online Transaction Processing) focuses on quick, transactional operations. OLAP (Online Analytical Processing) is designed for complex queries and reporting.

What are SQL Window Functions?

Functions like ROW_NUMBER, RANK, LEAD, and LAG that perform calculations across a result set without collapsing rows.

What is a materialized view?

A materialized view stores the result of a query and refreshes periodically to keep it up to date.

How do you optimize a slow-running query?

Steps include indexing, analyzing execution plans, reducing joins, avoiding SELECT *, and simplifying queries.

What is a deadlock in SQL?

A situation where two or more transactions are waiting for each other to release locks, preventing progress.

What is a database lock?

A mechanism to control concurrent access to database resources.

What is database replication?

The process of copying data from one database server to another for redundancy or improved performance.

How does SQL handle concurrency?

SQL uses locking mechanisms and isolation levels (e.g., READ COMMITTED, REPEATABLE READ, SERIALIZABLE) to manage concurrency.

What is query execution plan, and how do you interpret it?

It shows how SQL executes a query, detailing the steps and resource consumption for optimizing queries.

What is SQL injection, and how can you prevent it?

SQL injection is a vulnerability that allows attackers to manipulate queries. Use parameterized queries or prepared statements to prevent it.