Advanced SQL concepts enhance the ability to solve complex database problems and improve performance. These include topics like subqueries, common table expressions (CTEs), window functions, indexes, and advanced join operations. Understanding advanced SQL equips you to handle large datasets efficiently, write optimized queries, and manage database systems effectively. Practicing multiple-choice questions (MCQs) on these topics will strengthen your problem-solving skills and boost confidence for technical interviews.
1.) What is the purpose of a Common Table Expression (CTE)?
A) To create temporary tables
B) To optimize indexing
C) To perform transactions
D) To simplify complex queries by defining a temporary result set
2.) Which SQL clause is used to filter grouped data?
A) WHERE
B) HAVING
C) GROUP BY
D) ORDER BY
3.) What is a window function in SQL?
A) A function that operates on a subset of rows
B) A function that partitions data for analysis
C) A function that performs calculations across a window of rows
D) All of the above
4.) What is the main difference between a UNION and a UNION ALL?
A) UNION removes duplicates; UNION ALL does not
B) UNION is faster than UNION ALL
C) UNION ALL removes duplicates; UNION does not
D) UNION merges columns; UNION ALL merges rows
5.) What is the purpose of an index in SQL?
A) To store duplicate data
B) To sort data
C) To speed up data retrieval
D) To ensure data integrity
6.) What does the NVL() function do in SQL?
A) Calculates the sum of a column
B) Converts a null value to a specified value
C) Removes duplicate rows from a query
D) Joins two tables based on a condition
7.) How does a CROSS JOIN behave?
A) Matches rows from two tables based on a condition
B) Joins rows with matching primary keys
C) Produces a Cartesian product of two tables
D) Joins rows with null values
8.) What is the primary advantage of a clustered index?
A) Data is stored in order based on the index
B) Faster insertions
C) Supports multiple indexes per table
D) Reduces storage requirements
9.) Which statement about stored procedures is true?
A) They can be used to encapsulate complex SQL queries and business logic
B) They are automatically optimized for performance by the database
C) They cannot accept input parameters
D) They are executed automatically on database startup
10.) How does the SQL MERGE statement work?
A) Merges two tables into one new table
B) Performs insert, update, or delete operations based on a condition
C) Combines columns from two queries into one result set
D) Optimizes indexes for faster retrieval
Related
