SELECT * is always optimized by the database engine to only retrieve necessary columns.
Correct Answer
False
False. SELECT * retrieves all columns from the table, which can be inefficient if only a subset of columns is actually needed. The database engine does not intelligently ignore unused columns in the result set.
Question 2
An INNER JOIN returns only rows where there is a match in both tables based on the join condition.
Correct Answer
True
True. An INNER JOIN requires a matching value in the join column(s) of both tables to include a row in the result set. Rows without a match in either table are excluded.
Question 3
Correlated subqueries execute only once for the entire outer query.
Correct Answer
False
False. A correlated subquery executes once for each row processed by the outer query, making it potentially less efficient than a non-correlated subquery for large datasets.
Question 4
Creating an index on a column can significantly speed up data retrieval operations involving that column in WHERE clauses.
Correct Answer
True
True. Indexes allow the database to quickly locate data without scanning the entire table, which is highly beneficial for queries that filter or sort based on indexed columns.
Question 5
A primary key constraint ensures that the column values are unique but does not automatically create an index.
Correct Answer
False
False. A primary key constraint automatically creates a unique index on the primary key column(s). This index enforces uniqueness and provides efficient data retrieval based on the primary key.
Question 6
All columns in the SELECT list that are not aggregate functions must appear in the GROUP BY clause.
Correct Answer
True
True. This is a fundamental rule in SQL when using the GROUP BY clause. Any non-aggregated column in the SELECT list must be part of the grouping criteria.
Question 7
A LEFT JOIN will always return all rows from the left table, regardless of whether a match exists in the right table, and fill unmatched columns with an empty string.
Correct Answer
False
False. A LEFT JOIN returns all rows from the left table. If no match is found in the right table, the columns from the right table will contain NULL values, not empty strings.
Question 8
A subquery can be used in the WHERE clause to filter results based on a condition derived from another query.
Correct Answer
True
True. Subqueries are commonly used in the WHERE clause with operators like IN, EXISTS, or comparison operators to filter the outer query's results based on data retrieved by the subquery.
Question 9
Adding more indexes to a table always improves query performance.
Correct Answer
False
False. While indexes can improve read performance, too many indexes can degrade write performance (INSERT, UPDATE, DELETE) because the database must maintain all indexes whenever data changes. Excessive indexes can also sometimes lead to the query optimizer choosing less efficient plans.
Question 10
SELECT DISTINCT eliminates duplicate rows from the result set.
Correct Answer
True
True. The DISTINCT keyword, when used with SELECT, ensures that only unique combinations of all selected columns are returned, removing any identical rows.
Question 11
The HAVING clause is used to filter individual rows before grouping, similar to a WHERE clause.
Correct Answer
False
False. The WHERE clause filters individual rows before any grouping occurs. The HAVING clause is used to filter groups of rows after the GROUP BY clause has been applied and aggregate functions have been calculated.
Question 12
A UNIQUE constraint ensures that all values in a column or set of columns are different.
Correct Answer
True
True. A UNIQUE constraint enforces that every value in the specified column(s) must be unique across all rows in the table. It allows for NULL values, unlike a primary key.
Question 13
Indexes generally improve the performance of all DML operations (INSERT, UPDATE, DELETE).
Correct Answer
False
False. While indexes improve read performance, they can degrade the performance of DML operations (INSERT, UPDATE, DELETE). This is because the database must update the index structure whenever data in the indexed columns is modified, adding overhead.
Question 14
The database query optimizer is responsible for determining the most efficient execution plan for a given SQL statement.
Correct Answer
True
True. The query optimizer analyzes SQL statements and available database statistics (like indexes, table sizes) to choose the most cost-effective method for retrieving the requested data.
Question 15
The NOT NULL constraint prevents a column from containing any value, including empty strings.
Correct Answer
False
False. The NOT NULL constraint prevents a column from storing NULL values. An empty string is considered a valid value, not a NULL, and is therefore permitted in a NOT NULL column unless other constraints are in place.
Question 16
A CROSS JOIN produces a Cartesian product of the two tables involved.
Correct Answer
True
True. A CROSS JOIN combines every row from the first table with every row from the second table, resulting in a Cartesian product where the number of rows is the product of the number of rows in each table.
Question 17
A CHECK constraint can reference columns in other tables to enforce complex business rules.
Correct Answer
False
False. A CHECK constraint typically operates only on columns within the same row of the table where it is defined. To enforce rules involving multiple tables, FOREIGN KEY constraints or triggers are generally used.
Question 18
GROUP BY can be used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
Correct Answer
True
True. The GROUP BY clause is specifically designed to work with aggregate functions, allowing them to calculate values for each distinct group of rows rather than for the entire result set.
Question 19
A NATURAL JOIN requires explicit ON or USING clauses to specify join columns.
Correct Answer
False
False. A NATURAL JOIN implicitly joins two tables based on all columns that have the same name in both tables, without requiring an explicit ON or USING clause.
Question 20
Limiting the number of rows returned by a query using LIMIT or TOP can improve performance for large result sets.
Correct Answer
True
True. Retrieving only a subset of rows, especially from a potentially large result set, reduces the amount of data transferred and processed, leading to faster query execution and response times.
Question 21
Subqueries can only be used in the WHERE or SELECT clauses, but not in the FROM clause.
Correct Answer
False
False. Subqueries can be used in the FROM clause, where they are often called derived tables or inline views. This allows a subquery's result set to be treated as a temporary table for the outer query.
Question 22
The UNION operator automatically eliminates duplicate rows from the combined result set, while UNION ALL retains all rows, including duplicates.
Correct Answer
True
True. This is the primary distinction between UNION and UNION ALL. UNION performs an implicit DISTINCT operation, while UNION ALL simply concatenates the result sets.