close

Interview Questions for SQL Developers: Common SQL Interview Q&A - Part 3

In SQL, what types of joins have you used, and explain how they work?

Answer:

  • INNER JOIN: Returns rows with matching data 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 JOIN: Returns all rows from both tables, including non-matching rows.

 

Explain the ACID concepts in SQL and their role in transaction management

Answer: ACID stands for Atomicity, Consistency, Isolation, Durability. These are essential properties in transaction management in SQL:

  • Atomicity ensures that a transaction is either fully processed or not processed at all.
  • Consistency ensures that data must adhere to defined rules, constraints, and conditions.
  • Isolation ensures that concurrent transactions do not interfere with each other.
  • Durability ensures that once a transaction is completed, the changes in the database are saved securely and permanently.

 

What is the difference between ROW_NUMBER(), RANK(), DENSE_RANK() functions in SQL?

Answer: ROW_NUMBER(), RANK(), DENSE_RANK() are all used for numbering rows in a query result, but they have the following differences:

  • ROW_NUMBER(): Assigns continuous numbers to rows in the query result, without considering duplicates.
  • RANK(): Assigns numbers to rows in the query result and skips the next number in case of ties.
  • DENSE_RANK(): Assigns numbers to rows in the query result and does not skip the next number in case of ties.

 

How to use window functions in SQL, and provide an example.

Answer: Window functions allow performing calculations on a set of related rows without changing the main query's result. For example, we can use window functions to calculate running totals or retrieve top N rows within a result set.

SELECT ProductID, UnitPrice, 
       SUM(UnitPrice) OVER (ORDER BY ProductID) AS RunningTotal
FROM Products;

 

How to use regular expressions in SQL for pattern matching

Answer: Regular expressions in SQL are used for complex text pattern searches. They are often used with the LIKE operator or functions like REGEXP_LIKE (in Oracle) or REGEXP_MATCHES (in PostgreSQL).

SELECT * FROM Employees WHERE LastName LIKE '%son%';

 

How to use JSON functions in SQL to work with JSON data

Answer: JSON functions in SQL allow querying, inserting, updating, and deleting data in JSON format in the database.

For instance, we can use functions like JSON_VALUE, JSON_QUERY, JSON_MODIFY (in SQL Server), or operators like ->, ->>, #>, #>>, etc. (in PostgreSQL) to manipulate JSON properties.

SELECT JSON_VALUE(CustomerInfo, '$.Name') AS CustomerName
FROM Customers;

 

Advanced techniques for SQL query optimization and database performance tuning

Answer: To optimize SQL queries and database performance, we can use techniques such as:

  • Using indexes for frequently queried columns.
  • Optimizing JOIN and WHERE clauses for efficient data retrieval.
  • Utilizing window functions and pagination when necessary.
  • Avoiding SELECT * to retrieve only necessary columns.
  • Using query hints in some cases.
  • Ensuring data normalization and removing duplicates.
  • Employing performance monitoring tools to track and fine-tune the database.

 

Explain how to use SET operations (UNION, INTERSECT, EXCEPT) in SQl

Answer: SET operations (UNION, INTERSECT, EXCEPT) are used to combine and manipulate result sets of different queries.

  • UNION: Combines the results of two or more queries into a single data set and removes duplicates.
  • INTERSECT: Returns rows that appear in both query result sets.
  • EXCEPT: Returns rows that appear in the first query result set but not in the second one.

 

How to use query functions like LEAD, LAG, FIRST_VALUE, LAST_VALUE in SQL

Answer: Query functions like LEAD, LAG, FIRST_VALUE, LAST_VALUE are used to retrieve values from related rows within the same query result.

  • LEAD: Gets the value of a column from the next row in the query result.
  • LAG: Gets the value of a column from the previous row in the query result.
  • FIRST_VALUE: Retrieves the first value of a column in the query result.
  • LAST_VALUE: Retrieves the last value of a column in the query result.