Top 50+ SQL Query Interview Questions and Answers

SQL stands for Structured Query Language and is used to manage and manipulate relational databases. SQL enables the creation, querying, updating, and administration of databases.

To retrieve data from a single table in SQL, the SELECT statement is utilized along with the FROM clause to specify the table from which to select the data.

The WHERE clause in SQL filters rows based on specified conditions before any groupings are made. The HAVING clause is used to filter groups after the GROUP BY clause has been applied.

In SQL, the ORDER BY clause sorts the results of a query based on specified columns either in ascending or descending order.

SQL joins combine rows from two or more tables based on a related column between them. The different types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

A new record is inserted into a database table using the INSERT INTO statement in SQL, specifying the table and the values for each column.

The UPDATE statement in SQL modifies existing records in a table based on specified conditions.

Records are deleted from a table using the DELETE FROM statement in SQL, where conditions specify which records to remove.

The SELECT DISTINCT statement in SQL retrieves unique records by removing duplicate entries from a result set.

Results in SQL queries are grouped using the GROUP BY clause, which aggregates records by one or more columns.

A primary key in SQL uniquely identifies each record in a table and ensures that no duplicate values exist for that key, enforcing data integrity and facilitating efficient data retrieval.

Foreign keys in SQL are used to link two tables together, referencing the primary key in another table to enforce referential integrity between the tables.

The LIKE operator in SQL queries is used to search for a specified pattern in a column, where wildcard characters can represent one or more characters.

The LIMIT clause in SQL specifies the maximum number of records to return from a query, useful for pagination in result sets.

The COUNT() function in SQL is used to count the number of rows in a table, optionally applying conditions with the WHERE clause to filter the rows counted.

An INNER JOIN in SQL returns rows when there is at least one match in both tables compared, whereas an OUTER JOIN returns all rows from one table and the matched rows from the second table.

A column value is updated in SQL using the UPDATE statement, specifying the table, the column to update, and the new value, along with conditions to select the appropriate record.

A subquery in SQL is a query within another SQL query, used to perform operations that require multiple steps, such as filtering, comparison, or exists-check in the outer query.

SQL injection can be prevented by using prepared statements and parameterized queries, which separate SQL logic from data, ensuring that user input cannot alter the structure of a SQL query.

The GROUP BY statement in SQL groups rows that have the same values in specified columns into summary rows, like "find the number of customers in each country".

To perform a self-join in SQL, use the JOIN operation on a table with itself, aliasing the table so SQL treats it as if it were two separate tables. Self-join is useful for comparing rows within the same table, such as finding duplicate records or establishing hierarchical relationships.

Normalization in SQL is the process of organizing data to reduce redundancy and improve data integrity. The different forms of normalization, primarily first (1NF), second (2NF), and third (3NF) normal forms, each impose increasingly strict rules. 1NF requires the elimination of duplicate columns from the same table. 2NF requires that all non-key attributes are fully functional and dependent on the primary key. 3NF requires that all the attributes are only dependent on the primary key.

Indexing in SQL is implemented by creating an index on one or more columns of a table to speed up the retrieval of data. The primary types of indexes are clustered and non-clustered. A clustered index reorders the physical storage of the table data to match the index, which allows for efficient data retrieval. Non-clustered indexes maintain a separate structure from the data rows, with pointers to the physical data.

A transaction in SQL is a sequence of operations performed as a single logical unit of work. Transaction control is managed through SQL commands such as BEGIN TRANSACTION, COMMIT, and ROLLBACK. COMMIT finalizes the changes made during the transaction, while ROLLBACK undoes them if an error occurs or the transaction cannot be completed successfully.

The WITH clause in SQL is used to define Common Table Expressions (CTEs), which are temporary result sets that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs simplify complex queries by breaking them into simpler, reusable components.

Window functions in SQL perform calculations across a set of table rows that are somehow related to the current row. An example of their use is the ROW_NUMBER() function, which assigns a unique number to each row based on the partition or order specified in the OVER clause.

To optimize a slow-running SQL query, analyze the query execution plan to identify bottlenecks, such as full table scans or inefficient joins. Improve performance by adding indexes, rewriting the query to reduce complexity, or adjusting database configurations for better resource utilization.

The UNION combines the results of two queries and removes duplicate rows. The UNION ALL combines the results of two queries but does not remove duplicates, which makes it faster as it does not require the extra step of identifying and removing duplicates.

PIVOT in SQL is used to transform rows into columns, turning unique values from one column in the input query into multiple columns in the output. UNPIVOT performs the reverse operation, turning columns into rows, which can be useful for the normalization of denormalized tables.

A correlated subquery is a subquery that uses values from the outer query in its WHERE clause, executing once for each row processed by the outer query. This differs from a regular subquery, which runs independently of the outer query and provides a one-time set of results that the outer query can use.

Hierarchical data in SQL is handled using recursive queries or Common Table Expressions (CTEs) with the WITH RECURSIVE clause. This approach allows the representation of data with parent-child relationships, such as organizational charts or category trees, by recursively querying the table.

Using NULL values in SQL operations affects data integrity and query results, as NULL represents unknown or missing information. Operations involving NULL, such as comparisons or arithmetic calculations, typically result in NULL, requiring careful handling using functions like COALESCE or IS NULL to ensure correct results.

Data integrity in SQL is ensured using constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. These constraints enforce the rules governing the allowable values in columns, preventing invalid data entry and maintaining relationships between tables.

The MERGE statement in SQL, also known as upsert, is used to combine the INSERT, UPDATE, and DELETE operations into a single statement. It evaluates each row in a source table against a matching row in a target table to determine whether to update existing records, insert new ones, or delete those that no longer match the source.

Recursive queries in SQL are implemented using Common Table Expressions (CTEs) with the WITH RECURSIVE clause. This technique allows a query to refer to itself, enabling the execution of complex hierarchical queries, such as traversing parent-child relationships in a table.

To secure SQL queries against injection attacks, use prepared statements with parameterized queries, which separate SQL logic from the data. Additionally, apply the principle of least privilege by restricting database access and permissions, and validate all input data to ensure it conforms to expected formats.

Batch update or delete in SQL involves executing a single UPDATE or DELETE statement that affects multiple rows based on a specific condition. This approach is more efficient than processing each row individually, especially when dealing with large volumes of data.

Database partitioning in SQL involves dividing a table into smaller, more manageable pieces, called partitions, based on specified criteria such as range, list, or hash. This can improve performance and manageability for large tables by allowing operations to target specific subsets of data.

Data replication across databases using SQL can be achieved through built-in replication features provided by the database management system, such as SQL Server's replication, MySQL's replication, or PostgreSQL's streaming replication. These features synchronize data across databases in real-time or near real-time, ensuring data consistency and availability.

A SQL JOIN is a means by which two or more tables are combined based on a related column between them. The main types of JOINs include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

An INNER JOIN in SQL returns rows when there is at least one match in both tables being joined. An OUTER JOIN returns all rows from one table and matching rows from the second table, including rows with no match, which are filled with NULLs.

A LEFT JOIN in SQL returns all rows from the left table, and the matched rows from the right table. For example, joining table A (employees) to table B (departments) with a LEFT JOIN returns all employees, including those without a department.

A RIGHT JOIN in SQL returns all rows from the right table and the matched rows from the left table. Use a RIGHT JOIN when you need all records from the right table and only the matched records from the left table.

Perform a FULL OUTER JOIN in SQL by using the FULL OUTER JOIN keyword to combine rows from two tables. It returns all rows when there is a match in one of the tables, including rows with no match in either table, which are filled with NULLs.

A CROSS JOIN in SQL produces a Cartesian product of the two tables, joining every row of the first table with every row of the second table. It differs from other joins as it does not require a condition to match rows.

Use the ON clause in a JOIN operation to specify the condition that determines how rows from the combined tables should match.

The ON clause is used to specify the join condition. The WHERE clause is used to filter the rows after the join has been performed.

Joining a table to itself is called a self-join, achieved by using an alias for the table to treat it as if it were two separate tables. A self-join is useful for comparing rows within the same table.

A non-equijoin in SQL is a join that uses a condition other than equality to relate rows between two tables. Perform it using comparison operators like <, >, or BETWEEN in the ON clause.

Filter records after performing a JOIN by using the WHERE clause to specify conditions that the resulting rows must meet.

Yes, you can perform a JOIN operation between more than two tables by sequentially joining additional tables using the JOIN clause, specifying the join condition at each step.

Joining large tables can significantly impact performance due to the increased computational load. Optimize it by using indexes on the join columns, limiting the number of rows with WHERE clauses before joining, and selecting only the necessary columns.

Handle NULL values in JOIN operations by using the COALESCE function to provide alternative values for NULLs or by specifying conditions in the ON or WHERE clause to treat NULLs according to the query requirements.

A NATURAL JOIN in SQL automatically joins tables based on columns with the same names and data types in both tables. It eliminates the need to specify the join condition explicitly.

A USING clause simplifies JOIN conditions by allowing the specification of the column(s) to join on when the columns have the same names in both tables, eliminating the need to qualify column names with table names.

Perform an ANTI JOIN in SQL by using a LEFT JOIN combined with a WHERE clause that checks for NULL in the right table's key column. This returns rows from the left table that have no corresponding rows in the right table.

Self-joins are significant for querying hierarchical data or comparing rows within the same table. A practical example includes finding employees who work in the same department by joining the employee table based on the department ID.

Implement complex joins involving subqueries by using the subquery as a derived table in the FROM clause and then joining it with other tables. This allows for filtering and aggregation within the subquery before the join.

Common mistakes to avoid when writing SQL JOIN queries include neglecting to specify the join type, leading to unintended CROSS JOINs; not using table aliases for clarity; and forgetting to include join conditions or filtering criteria, resulting in incorrect or inefficient queries.