The SELECT DISTINCT keyword is used to return only unique values in the specified column(s) from a query's result set, eliminating duplicate rows.
Correct Answer
True
The DISTINCT keyword ensures that each row in the result set is unique, effectively removing any duplicate rows based on the selected columns.
Question 2
Using SELECT * is always the most efficient way to retrieve data from a table, especially in production environments, because it requires less typing.
Correct Answer
False
SELECT * retrieves all columns, which can be inefficient if only a subset of columns is needed, as it consumes more network bandwidth and memory. It is generally more efficient to explicitly list only the required columns.
Question 3
The WHERE clause is applied to filter individual rows before any grouping or aggregation functions are performed in a query.
Correct Answer
True
The WHERE clause filters rows based on specified conditions before they are grouped by the GROUP BY clause or processed by aggregate functions.
Question 4
The HAVING clause can be used to filter individual rows based on a condition, similar to the WHERE clause, but it is applied after the SELECT statement's projection.
Correct Answer
False
The HAVING clause is used to filter *groups* of rows after the GROUP BY clause has aggregated them, not individual rows. Individual row filtering is handled by the WHERE clause.
Question 5
An INNER JOIN returns all rows from the left table and all rows from the right table, regardless of whether a match is found in the join condition.
Correct Answer
False
An INNER JOIN returns only the rows where there is a match in *both* tables based on the join condition. Rows without a match in either table are excluded.
Question 6
A LEFT JOIN returns all rows from the left table, and for each row, it returns the matching rows from the right table; if no match exists in the right table, NULLs are returned for the right table's columns.
Correct Answer
True
This correctly describes the behavior of a LEFT JOIN (also known as LEFT OUTER JOIN), ensuring all rows from the 'left' side are included.
Question 7
The COUNT(*) aggregate function counts only the non-NULL values present in a specified column.
Correct Answer
False
COUNT(*) counts the total number of rows in a group or result set, including rows that contain NULL values in any column. To count non-NULL values in a specific column, one would use COUNT(column_name).
Question 8
All non-aggregated columns present in the SELECT list of a query that uses a GROUP BY clause must also appear in the GROUP BY clause itself.
Correct Answer
True
This is a fundamental rule for GROUP BY clauses in standard SQL. Any column in the SELECT list that is not an aggregate function must be included in the GROUP BY clause to define the groups.
Question 9
If a SELECT query contains an aggregate function like SUM() but does not include a GROUP BY clause, the query will always result in a syntax error.
Correct Answer
False
If a SELECT query includes an aggregate function without a GROUP BY clause, the aggregate function will operate on the entire set of rows returned by the FROM and WHERE clauses, treating it as a single group. It will not cause a syntax error.
Question 10
A subquery can be used in the FROM clause of an outer query, in which case it is often referred to as a derived table or inline view.
Correct Answer
True
Subqueries in the FROM clause are common and allow the result of a subquery to be treated as a temporary table for the outer query.
Question 11
A correlated subquery executes only once, and its result set is then used by the outer query for all subsequent row processing.
Correct Answer
False
A correlated subquery executes once for *each row* processed by the outer query, as its execution depends on values from the outer query.
Question 12
The IN operator is frequently used with subqueries to test if a value matches any value in the set returned by the subquery.
Correct Answer
True
The IN operator is a standard and efficient way to compare a value against a list or a result set provided by a subquery.
Question 13
Adding an index to every column in a large table is always recommended to significantly improve the performance of all types of queries.
Correct Answer
False
Adding an index to every column is generally not recommended. While indexes can speed up data retrieval, too many indexes can degrade performance for INSERT, UPDATE, and DELETE operations, and consume excessive storage space, without guaranteeing query performance improvement.
Question 14
An index can improve the speed of data retrieval (SELECT operations) but may introduce overhead that slows down data modification operations (INSERT, UPDATE, DELETE).
Correct Answer
True
Indexes must be maintained when data changes, which adds overhead to DML operations. However, they can dramatically speed up search and sort operations during data retrieval.
Question 15
A table is in Second Normal Form (2NF) if it is in 1NF and all its non-key attributes are fully functionally dependent on *any* candidate key, even if it's only a part of a composite key.
Correct Answer
False
A table is in 2NF if it is in 1NF and all its non-key attributes are fully functionally dependent on the *entire* primary key, meaning there are no partial dependencies on only a part of a composite primary key.
Question 16
A table is in First Normal Form (1NF) if all its attributes contain atomic (indivisible) values, and there are no repeating groups within the table.
Correct Answer
True
This accurately defines the requirements for a table to be in First Normal Form, ensuring each column holds a single value and rows are unique.
Question 17
Denormalization is a database design process primarily aimed at breaking down large tables into smaller, more focused tables to reduce data redundancy.
Correct Answer
False
Denormalization is the opposite process; it intentionally introduces redundancy into a database by combining tables or adding duplicate data to improve read performance for specific queries, often at the expense of write performance and increased data redundancy.
Question 18
The primary goal of database normalization is to reduce data redundancy and improve data integrity by organizing columns and tables efficiently.
Correct Answer
True
Normalization systematically structures a database to minimize data duplication and prevent anomalies, thereby ensuring data consistency and integrity.
Question 19
A FULL OUTER JOIN is logically equivalent to performing an INNER JOIN and then using a UNION operator to combine it with the results of a LEFT JOIN on the same tables.
Correct Answer
False
A FULL OUTER JOIN returns all rows from both tables, with NULLs for non-matching columns. It is equivalent to a LEFT JOIN UNION ALL a RIGHT JOIN, but not an INNER JOIN UNION a LEFT JOIN, as the latter would miss non-matching rows from the right table.
Question 20
A CROSS JOIN produces the Cartesian product of the two tables involved, where each row from the first table is combined with every row from the second table.
Correct Answer
True
This is the correct definition of a CROSS JOIN, resulting in a combination of every possible row pairing between the two tables.
Question 21
A table is in Third Normal Form (3NF) if it is in 2NF and has no transitive dependencies, meaning no non-key attribute is dependent on another non-key attribute.
Correct Answer
True
This correctly defines Third Normal Form. Eliminating transitive dependencies further reduces redundancy and improves data integrity.
Question 22
The AVG() aggregate function calculates the arithmetic mean of a set of values, and it automatically includes NULL values in its calculation by treating them as zero.
Correct Answer
False
The AVG() aggregate function calculates the arithmetic mean of a set of values but *ignores* NULL values in its calculation. It does not treat them as zero.