|
Question: Can you explain the difference between the EXISTS and IN operators in SQL?
|
Answer: The EXISTS operator is used to test for the existence of any rows in a subquery, and it returns true
if the subquery returns one or more rows.
The IN operator is used to specify multiple values in a WHERE clause and returns true
if the value matches any value in the list.
|
|
Question: What is the difference between a clustered and a non-clustered index in SQL?
|
Answer: A clustered index determines the physical order of data rows in a table, and there can only be one
clustered index per table.
In contrast, a non-clustered index does not affect the physical order of the rows and creates a separate data structure
containing pointers to the rows. A table can have multiple non-clustered indexes.
|
|
Question: Can you explain the difference between a LEFT JOIN and a RIGHT JOIN in SQL?
|
Answer: A LEFT JOIN returns all records from the left table (the first table mentioned in the query) and the matched
records from the right table.
Whereas a RIGHT JOIN returns all records from the right table and the matched records from the left table.
|
|
Question: What is the purpose of the LIKE operator?
|
Answer: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
It allows for pattern matching using wildcards such as '%' (matches any sequence of characters)
and '_' (matches any single character).
|
|
|
|
|
|
Question: Can you explain the difference between a scalar function and a table function in SQL?
|
Answer: A 'scalar' function returns a single value based on the input parameters,
while a 'table' function returns a set of rows as a result.
Scalar functions are typically used in SELECT, WHERE, and ORDER BY clauses,
whereas table functions can be used like tables in FROM clauses.
|
|
Question: What is a trigger in SQL?
|
Answer: A trigger is a special type of stored procedure that automatically executes in response to certain database events,
such as INSERT, UPDATE, or DELETE operations on a table.
Triggers can be used to enforce business rules, maintain referential integrity, and automate tasks.
|
|
Question: What is the difference between a view and a materialized view in SQL?
|
Answer: A view is a virtual table based on the result set of a SELECT query,
while a materialized view is a physical copy of the result set stored as a table-like structure.
Materialized views are updated periodically to reflect changes in the underlying data,
whereas views are dynamically generated at query time.
|
|
|
|
|
|
Question: Can you explain the purpose of the SQL CASE statement?
|
Answer: The CASE statement is used to perform conditional logic in SQL queries.
It allows you to evaluate multiple conditions and return different values based on those conditions.
The CASE statement can be used in SELECT, WHERE, and ORDER BY clauses.
|
|
Question: What is the difference between the COUNT(*) and COUNT(column_name) functions in SQL?
|
Answer: The COUNT(*) returns the total number of rows in a table, including rows with NULL values,
whereas COUNT(column_name) returns the number of non-NULL values in the specified column.
|