In SQL, what types of
joins have you used, and explain how they work?
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:
Atomicityensures that a transaction is either fully processed or not processed at all.
Consistencyensures that data must adhere to defined rules, constraints, and conditions.
Isolationensures that concurrent transactions do not interfere with each other.
Durabilityensures 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?
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.
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.
WHEREclauses for efficient data retrieval.
- Utilizing window functions and pagination when necessary.
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
(UNION, INTERSECT, EXCEPT) in SQl
(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.