A database contains a 'Products' table with columns 'ProductID', 'ProductName', and 'Price'. To retrieve the names and prices of all products costing more than 50.00, which SQL query is most appropriate?
SELECT ProductName, Price FROM Products WHERE Price > 50.00;
✓
SELECT ProductName, Price FROM Products GROUP BY Price > 50.00;
SELECT ProductName, Price FROM Products HAVING Price > 50.00;
SELECT ProductName, Price WHERE Price > 50.00 FROM Products;
Correct Answer
SELECT ProductName, Price FROM Products WHERE Price > 50.00;
The correct query uses the WHERE clause to filter individual rows based on the 'Price' column before any grouping or aggregation occurs. Using HAVING Price > 50.00 is incorrect because HAVING is used to filter groups after aggregation, not individual rows. The syntax SELECT ProductName, Price WHERE Price > 50.00 FROM Products is grammatically incorrect as the WHERE clause must follow the FROM clause. The option GROUP BY Price > 50.00 would attempt to group rows based on a boolean condition, which is not the standard way to filter individual records by price.
Question 2
Given two tables, 'Orders' (OrderID, CustomerID, OrderDate) and 'Customers' (CustomerID, CustomerName, City). To list all orders along with the name of the customer who placed each order, including orders that might not have a matching customer in the 'Customers' table, which type of JOIN should be used?
RIGHT JOIN
INNER JOIN
LEFT JOIN
✓
FULL OUTER JOIN
Correct Answer
LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) between 'Orders' and 'Customers' will return all rows from the left table ('Orders') and the matching rows from the right table ('Customers'). If there is no match for an order in the 'Customers' table, the customer-related columns will show NULL, thus including all orders. An INNER JOIN would only return orders that have a matching customer, excluding any orders without a customer entry. A RIGHT JOIN would return all customers and their matching orders, potentially excluding orders without customers if 'Customers' is the right table. A FULL OUTER JOIN would return all rows from both tables, matching where possible, and showing NULLs for non-matches, which is more than what is requested (all orders plus customer name).
Question 3
A table named 'Sales' has columns 'Region', 'Product', and 'Amount'. To find the total sales amount for each region, which SQL construct is most appropriate?
SELECT SUM(Amount) FROM Sales ORDER BY Region;
SELECT Region, AVG(Amount) FROM Sales GROUP BY Region;
SELECT Region, SUM(Amount) FROM Sales WHERE Region IS NOT NULL;
SELECT Region, SUM(Amount) FROM Sales GROUP BY Region;
✓
Correct Answer
SELECT Region, SUM(Amount) FROM Sales GROUP BY Region;
To calculate the total sales amount for each region, you need to group the sales data by 'Region' and then apply the SUM aggregate function to the 'Amount' column within each group. The query SELECT Region, SUM(Amount) FROM Sales GROUP BY Region achieves this. The option using WHERE Region IS NOT NULL would filter out null regions but not group the data for sum calculation. The query SELECT SUM(Amount) FROM Sales ORDER BY Region would calculate a single total sum for all sales and then attempt to order by region, which is not applicable to a single aggregate value. The option using AVG(Amount) would calculate the average sales amount per region, not the total sales amount.
Question 4
Consider a 'Employees' table with 'EmployeeID', 'Name', 'DepartmentID', and 'Salary'. To find the names of employees whose salary is greater than the average salary of all employees, which SQL approach is best?
SELECT Name FROM Employees WHERE Salary > AVG(Salary);
SELECT Name FROM Employees JOIN (SELECT AVG(Salary) AS AvgSalary FROM Employees) AS AvgTable ON Employees. Salary > AvgTable. AvgSalary;
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
✓
SELECT Name FROM Employees GROUP BY DepartmentID HAVING Salary > AVG(Salary);
Correct Answer
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
The most direct way to find employees with a salary greater than the overall average is to use a scalar subquery in the WHERE clause. This subquery calculates the single average salary value, which is then used to filter the outer query's results. The option using GROUP BY DepartmentID HAVING Salary > AVG(Salary) is incorrect because HAVING applies to groups, and 'Salary' without an aggregate function inside HAVING is not valid in most SQL dialects for this purpose. The query SELECT Name FROM Employees WHERE Salary > AVG(Salary) is invalid because AVG(Salary) is an aggregate function and cannot be used directly in a WHERE clause without a GROUP BY. While a JOIN with a subquery (SELECT AVG(Salary) AS AvgSalary FROM Employees) could work, the scalar subquery in the WHERE clause is generally simpler and more idiomatic for this specific problem.
Question 5
An index is created on a column. What is the primary benefit of adding an index to a table column?
To automatically sort the data physically on disk.
To speed up data retrieval operations (SELECT queries).
✓
To reduce the storage space required by the table.
To enforce uniqueness constraints on the indexed column.
Correct Answer
To speed up data retrieval operations (SELECT queries).
The primary benefit of an index is to significantly speed up data retrieval operations, especially for WHERE clauses, JOIN conditions, and ORDER BY clauses, by providing a quick lookup mechanism similar to a book's index. While a UNIQUE index does enforce uniqueness, that is a secondary function; the core purpose of indexing is performance improvement for reads. Indexes typically increase, not reduce, the storage space required by the table because the index itself consumes disk space. Indexes do not automatically sort the data physically on disk; they provide a logical ordering that helps with search, but the physical storage order of the data rows in the table may remain unchanged, especially for non-clustered indexes.
Question 6
Which of the following statements about a PRIMARY KEY constraint is true?
A PRIMARY KEY can contain NULL values, but all values must be unique.
A table can have only one PRIMARY KEY, and it must contain unique, non-NULL values.
✓
A PRIMARY KEY is primarily used for speeding up data insertion.
A table can have multiple PRIMARY KEYs, provided each is unique.
Correct Answer
A table can have only one PRIMARY KEY, and it must contain unique, non-NULL values.
A table can have only one PRIMARY KEY, and it fundamentally ensures that all values in the key column(s) are unique and not NULL, serving as the unique identifier for each row. The idea that a table can have multiple PRIMARY KEYs is incorrect; multiple unique identifiers can be enforced using UNIQUE constraints, but only one can be the designated PRIMARY KEY. A PRIMARY KEY cannot contain NULL values, as this would violate its role as a unique row identifier. While a PRIMARY KEY often has an associated index that can speed up searches, its primary role is data integrity (uniqueness and non-nullability), and it can sometimes slightly slow down data insertion due to index maintenance.
Question 7
You execute an EXPLAIN (or EXPLAIN PLAN) command on a complex SQL query. What is the main purpose of reviewing the output of this command?
To automatically rewrite the query for optimal performance.
To understand how the database server intends to execute the query and identify potential performance bottlenecks.
✓
To determine the total execution time of the query without running it.
To check for syntax errors in the SQL query before execution.
Correct Answer
To understand how the database server intends to execute the query and identify potential performance bottlenecks.
The main purpose of reviewing the output of an EXPLAIN command is to understand the query execution plan that the database optimizer has chosen. This plan details the steps the database will take, such as table scans, index usage, join methods, and filtering operations, which is crucial for identifying potential performance bottlenecks and optimizing the query. EXPLAIN does not automatically rewrite the query; it only shows the plan. Syntax errors are typically caught by the SQL parser before an execution plan is generated. While it provides insight into operations that contribute to execution time, it does not provide the actual execution time; that requires running the query and measuring its duration.
Question 8
A table 'Events' has columns 'EventID', 'EventType', and 'EventDate'. To retrieve a list of all unique event types that occurred, which query should be used?
SELECT UNIQUE EventType FROM Events;
SELECT EventType FROM Events GROUP BY EventType;
SELECT TOP 1 EventType FROM Events ORDER BY EventType;
SELECT DISTINCT EventType FROM Events;
✓
Correct Answer
SELECT DISTINCT EventType FROM Events;
To retrieve a list of all unique event types, the SELECT DISTINCT EventType FROM Events query is the most direct and idiomatic method. While SELECT EventType FROM Events GROUP BY EventType would also produce unique event types, DISTINCT is syntactically clearer for simply requesting unique values without aggregation. The query SELECT TOP 1 EventType FROM Events ORDER BY EventType would only return a single event type, the first one alphabetically. The keyword UNIQUE is not a standard SQL keyword for this purpose in a SELECT statement; it is typically used in constraints.
Question 9
Consider 'Departments' (DeptID, DeptName) and 'Employees' (EmpID, EmpName, DeptID). You want to list all departments, including those that currently have no employees, along with the names of any employees in them. Which JOIN type is appropriate?
LEFT JOIN from Departments to Employees
✓
INNER JOIN between Departments and Employees
FULL OUTER JOIN between Departments and Employees
RIGHT JOIN from Departments to Employees
Correct Answer
LEFT JOIN from Departments to Employees
To include all departments, even those without employees, 'Departments' must be the 'preserving' table. A LEFT JOIN with 'Departments' as the left table and 'Employees' as the right table will return all rows from 'Departments' and matching rows from 'Employees'. Departments without employees will have NULLs in the employee-related columns. An INNER JOIN would exclude departments with no employees. A RIGHT JOIN from 'Departments' to 'Employees' would mean 'Employees' is the preserving table, returning all employees and their departments, but not departments without employees. A FULL OUTER JOIN would return all departments and all employees, including employees not assigned to any department (if that were possible), which is more extensive than requested.
Question 10
A table 'Orders' has columns 'OrderID', 'CustomerID', 'OrderDate', and 'TotalAmount'. To find customers who have placed more than 5 orders, what SQL clause should follow a GROUP BY CustomerID?
WHERE COUNT(OrderID) > 5
GROUP BY COUNT(OrderID) > 5
ORDER BY COUNT(OrderID) DESC
HAVING COUNT(OrderID) > 5
✓
Correct Answer
HAVING COUNT(OrderID) > 5
To filter groups based on an aggregate condition, such as the count of orders per customer, the HAVING clause is required. The query would group by CustomerID, and then HAVING COUNT(OrderID) > 5 would filter these groups to only include customers with more than 5 orders. Using WHERE COUNT(OrderID) > 5 is incorrect because WHERE filters individual rows before grouping, and aggregate functions like COUNT cannot be used directly in a WHERE clause. ORDER BY COUNT(OrderID) DESC would sort the groups by their order count but not filter them. GROUP BY COUNT(OrderID) > 5 is syntactically incorrect and does not achieve the desired filtering.
Question 11
A table 'Products' has 'ProductID', 'Category', and 'Price'. To list products whose price is higher than the average price of products within their own category, which query structure is most appropriate?
SELECT p1. ProductName FROM Products p1 WHERE p1. Price > (SELECT AVG(p2. Price) FROM Products p2 WHERE p2. Category = p1. Category);
✓
SELECT ProductName FROM Products p1 JOIN Products p2 ON p1. Category = p2. Category WHERE p1. Price > AVG(p2. Price);
SELECT ProductName FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
SELECT ProductName FROM Products WHERE Price > AVG(Price) GROUP BY Category;
Correct Answer
SELECT p1. ProductName FROM Products p1 WHERE p1. Price > (SELECT AVG(p2. Price) FROM Products p2 WHERE p2. Category = p1. Category);
This scenario requires a correlated subquery. The outer query selects product names, and for each product, the inner subquery calculates the average price specifically for that product's category (p2. Category = p1. Category). This allows comparison of each product's price against its category's average. The option using WHERE Price > AVG(Price) GROUP BY Category is incorrect because AVG(Price) cannot be used directly in a WHERE clause without a subquery, and GROUP BY applies to the outer query. The JOIN with AVG(p2. Price) would be invalid as AVG is an aggregate function and cannot be used in the WHERE clause of a join in this manner. The option using SELECT AVG(Price) FROM Products calculates the overall average price, not the average for each product's specific category, which is not what the question asks for.
Question 12
What is a significant disadvantage of creating too many indexes on a table?
Inability to create new columns on the table.
Increased query execution time for SELECT statements.
Increased risk of data corruption.
Slower data modification (INSERT, UPDATE, DELETE) operations.
✓
Correct Answer
Slower data modification (INSERT, UPDATE, DELETE) operations.
A significant disadvantage of too many indexes is that they can slow down data modification operations (INSERT, UPDATE, DELETE). Each time data is modified in the table, the database system must also update all associated indexes to maintain their integrity and accuracy, which adds overhead. Indexes are generally designed to decrease, not increase, query execution time for SELECT statements. Creating indexes does not inherently increase the risk of data corruption, nor does it prevent the creation of new columns on the table.
Question 13
A 'Orders' table has a FOREIGN KEY 'CustomerID' referencing the 'Customers' table. If the FOREIGN KEY constraint is defined with ON DELETE RESTRICT, what happens when you try to delete a customer from the 'Customers' table who has existing orders in the 'Orders' table?
The customer will be deleted, and all associated orders will also be deleted.
The customer will be deleted, but the orders will remain with an invalid CustomerID.
The deletion of the customer will be prevented.
✓
The customer will be deleted, and the 'CustomerID' in the associated orders will be set to NULL.
Correct Answer
The deletion of the customer will be prevented.
When a FOREIGN KEY constraint is defined with ON DELETE RESTRICT (or NO ACTION in some systems), attempting to delete a row from the parent table ('Customers') that has dependent rows in the child table ('Orders') will result in an error, and the deletion will be prevented. The database enforces referential integrity by not allowing the parent record to be removed while child records still reference it. ON DELETE CASCADE would delete associated orders. ON DELETE SET NULL would set the 'CustomerID' in associated orders to NULL. The customer will not be deleted while orders remain with an invalid CustomerID; this would violate referential integrity.
Question 14
Which query optimization technique helps the database optimizer choose the most efficient execution plan by providing statistical information about the data distribution?
Rewriting all subqueries as JOINs.
Updating table statistics (e.g., ANALYZE TABLE or UPDATE STATISTICS).
✓
Adding more indexes to all columns.
Increasing the database server's RAM.
Correct Answer
Updating table statistics (e.g., ANALYZE TABLE or UPDATE STATISTICS).
Updating table statistics (often via commands like ANALYZE TABLE or UPDATE STATISTICS) provides the database optimizer with crucial information about the data distribution, cardinality, and density of columns. This information allows the optimizer to make better decisions about which indexes to use, which join methods are most efficient, and the order of operations, leading to a more efficient execution plan. Adding more indexes can sometimes help but also has downsides, and it's not a general optimization for the optimizer's decision-making process. Rewriting subqueries as JOINs might be an optimization in specific cases but is not a universal rule or a technique for providing statistics. Increasing RAM improves performance by allowing more data to be cached, but it doesn't directly help the optimizer choose a better plan based on data characteristics.
Question 15
A table 'Scores' has columns 'StudentID', 'Subject', and 'Score'. To retrieve the top 5 students with the highest scores in 'Mathematics', which SQL clause or function is most appropriate to combine with ORDER BY?
TOP 5
FETCH FIRST 5 ROWS ONLY
All of these (syntax varies by database system)
✓
LIMIT 5
Correct Answer
All of these (syntax varies by database system)
To retrieve the top N rows after sorting, various SQL keywords are used depending on the database system. LIMIT 5 is common in MySQL and PostgreSQL. TOP 5 is used in SQL Server. FETCH FIRST 5 ROWS ONLY is part of the SQL standard and used in Oracle, PostgreSQL, and others. Therefore, 'All of these (syntax varies by database system)' is the most accurate answer as they all achieve the same result with different syntax across different SQL implementations.
Question 16
You have a table 'Employees' with 'EmployeeID', 'Name', 'ManagerID'. 'ManagerID' refers to 'EmployeeID' to indicate who manages whom. To list each employee along with their manager's name, which SQL technique is required?
Self-join
✓
Subquery in the SELECT clause
LEFT JOIN with a separate 'Managers' table
Correlated subquery in the WHERE clause
Correct Answer
Self-join
To relate employees to their managers within the same 'Employees' table, a self-join is the appropriate technique. You join the 'Employees' table to itself, typically aliasing one instance as 'Employee' and the other as 'Manager', on the condition Employee. ManagerID = Manager. EmployeeID. A subquery in the SELECT clause could work but is generally less efficient than a join for this type of relational lookup. A LEFT JOIN with a separate 'Managers' table is not applicable as the manager information is already within the 'Employees' table. A correlated subquery in the WHERE clause would be used for filtering based on a condition related to the outer query, not for direct relational mapping of a manager's name.
Question 17
A table 'Transactions' has 'TransactionID', 'CustomerID', 'TransactionDate', and 'Amount'. To find the count of transactions and the average transaction amount for each customer, what is the correct GROUP BY and aggregate function usage?
SELECT CustomerID, COUNT(TransactionID), AVG(Amount) FROM Transactions GROUP BY CustomerID;
✓
SELECT CustomerID, COUNT(TransactionID) FROM Transactions GROUP BY CustomerID, AVG(Amount);
SELECT CustomerID, AVG(Amount) FROM Transactions WHERE COUNT(TransactionID) > 0 GROUP BY CustomerID;
SELECT CustomerID, COUNT(TransactionID) + AVG(Amount) FROM Transactions GROUP BY CustomerID;
Correct Answer
SELECT CustomerID, COUNT(TransactionID), AVG(Amount) FROM Transactions GROUP BY CustomerID;
To calculate multiple aggregate values (count and average) for each customer, you group the data by 'CustomerID' and apply the respective aggregate functions (COUNT and AVG) in the SELECT list. The query SELECT CustomerID, COUNT(TransactionID), AVG(Amount) FROM Transactions GROUP BY CustomerID correctly achieves this. Grouping by CustomerID, AVG(Amount) is syntactically incorrect because AVG(Amount) is an aggregate function and cannot be directly used in a GROUP BY clause in this context. Using WHERE COUNT(TransactionID) > 0 is incorrect because aggregate functions cannot be used in a WHERE clause. Adding COUNT(TransactionID) + AVG(Amount) would sum the count and average, which is not what 'count of transactions and the average transaction amount' implies.
Question 18
When comparing 'IN' and 'EXISTS' with subqueries, which statement is generally true regarding their performance characteristics?
IN is always more efficient than EXISTS for all types of subqueries.
EXISTS is often more efficient than IN when the subquery returns a very large number of rows.
✓
Both IN and EXISTS always perform identically, so choice is only stylistic.
EXISTS is primarily used for scalar subqueries, while IN is for row subqueries.
Correct Answer
EXISTS is often more efficient than IN when the subquery returns a very large number of rows.
EXISTS is often more efficient than IN when the subquery returns a very large number of rows. This is because EXISTS short-circuits: it stops processing as soon as it finds the first match in the subquery, returning TRUE. IN, on the other hand, typically evaluates the entire subquery first and then performs a comparison against the resulting list. Conversely, IN can sometimes be faster when the subquery returns a small, fixed list of values. The statement that IN is always more efficient is incorrect. EXISTS is used for checking the existence of rows, not just for scalar subqueries, and IN is used for checking if a value is within a list of values, which can be derived from a subquery. The performance of IN and EXISTS is not always identical; it depends on factors like data volume, indexing, and the database optimizer's implementation.
Question 19
What is the primary difference between a UNIQUE constraint and a PRIMARY KEY constraint?
A table can have multiple PRIMARY KEYs, but only one UNIQUE constraint.
A PRIMARY KEY is enforced only on numeric columns, while UNIQUE can be on any data type.
A UNIQUE constraint automatically creates a clustered index, while a PRIMARY KEY does not.
A UNIQUE constraint allows NULL values, while a PRIMARY KEY does not.
✓
Correct Answer
A UNIQUE constraint allows NULL values, while a PRIMARY KEY does not.
The primary difference is that a UNIQUE constraint allows for NULL values (though typically only one NULL per column, depending on the database system's implementation), whereas a PRIMARY KEY strictly disallows NULL values. A table can only have one PRIMARY KEY, but it can have multiple UNIQUE constraints. Both PRIMARY KEY and UNIQUE constraints typically create indexes, but whether it's clustered or non-clustered depends on the database system's default behavior and specific configuration, not a universal rule that UNIQUE always creates a clustered index while PRIMARY KEY does not (often, PRIMARY KEY creates a clustered index by default). Both PRIMARY KEY and UNIQUE constraints can be enforced on columns of various data types, not just numeric ones.
Question 20
A 'Products' table has a 'StockQuantity' column. You want to ensure that 'StockQuantity' can never be less than zero. Which type of constraint should be applied to the 'StockQuantity' column?
CHECK constraint
✓
NOT NULL constraint
UNIQUE constraint
FOREIGN KEY constraint
Correct Answer
CHECK constraint
A CHECK constraint is specifically designed to enforce domain integrity by ensuring that all values in a column satisfy a specified boolean condition. In this case, a CHECK constraint like CHECK (StockQuantity >= 0) would prevent negative stock quantities. A NOT NULL constraint ensures that the column cannot contain NULL values, but it does not prevent negative numbers. A UNIQUE constraint ensures that all values in the column are distinct, which is unrelated to the value's range. A FOREIGN KEY constraint establishes a link between two tables and enforces referential integrity, which is not relevant for ensuring a non-negative value within a single column.
Question 21
When optimizing a query, why is it generally recommended to avoid applying functions to indexed columns in the WHERE clause (e.g., WHERE YEAR(OrderDate) = 2023)?
The result of a function is always considered non-deterministic by the optimizer.
Applying functions on indexed columns prevents the database from using the index effectively.
✓
Functions always cause syntax errors when used in a WHERE clause.
Functions significantly increase the amount of data transferred over the network.
Correct Answer
Applying functions on indexed columns prevents the database from using the index effectively.
Applying functions to an indexed column in the WHERE clause (e.g., YEAR(OrderDate) = 2023) generally prevents the database from using the index effectively. The database must first compute the function for every row in the table, effectively transforming the column's values, before it can compare them to the search condition. This often forces a full table scan instead of using the much faster index lookup, a process known as 'index suppression'. Functions do not always cause syntax errors; they are valid SQL. While functions can add processing overhead, their primary impact in this context is on index usage, not directly on network data transfer. Functions are not always considered non-deterministic; many are deterministic but still suppress index usage.
Question 22
Given two tables, 'Customers' (CustomerID, CustomerName) and 'Orders' (OrderID, CustomerID, OrderDate). To find all customers and all orders, including customers with no orders and orders with no matching customer, which JOIN type should be used?
LEFT JOIN
INNER JOIN
FULL OUTER JOIN
✓
RIGHT JOIN
Correct Answer
FULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both the left and right tables, with NULL values in the columns of the table that does not have a match. This ensures that all customers are listed (even without orders) and all orders are listed (even without a matching customer), fulfilling the requirement. An INNER JOIN would only show customers with matching orders. A LEFT JOIN would show all customers and their orders, but not orders without a matching customer. A RIGHT JOIN would show all orders and their customers, but not customers without orders.