Which SQL keyword is used to retrieve only unique values from a specified column in a SELECT statement?
DISTINCT
✓
SINGLE
DIFFERENT
UNIQUE
Correct Answer
DISTINCT
The DISTINCT keyword is used to eliminate duplicate rows from the result set of a SELECT statement, ensuring that only unique values are returned for the specified column or combination of columns. The other options like UNIQUE, SINGLE, and DIFFERENT are not standard SQL keywords for this purpose.
Question 2
A database table 'Products' has columns ProductID (primary key), ProductName, and Category. To retrieve all product names that belong to the 'Electronics' category and cost more than 500, which WHERE clause is appropriate?
WHERE Category IS 'Electronics' AND Price > 500
WHERE Category = 'Electronics' AND Price > 500
✓
WHERE Category = 'Electronics' OR Price > 500
WHERE Category LIKE 'Electronics' AND Price > 500
Correct Answer
WHERE Category = 'Electronics' AND Price > 500
The correct WHERE clause uses the AND operator to combine two conditions: the Category must be 'Electronics' AND the Price must be greater than 500. Using OR would return products from 'Electronics' OR any product with a price over 500, which is not the requirement. Using LIKE 'Electronics' is unnecessarily broad when an exact match is needed. The IS keyword is used for checking NULL values, not for equality comparisons with non-NULL values.
Question 3
Consider two tables: 'Orders' (OrderID, CustomerID, OrderDate) and 'Customers' (CustomerID, CustomerName). To list all orders along with the name of the customer who placed each order, including orders that might not have a matching customer (due to data errors), which type of join should be used?
FULL OUTER JOIN
LEFT JOIN
✓
INNER JOIN
RIGHT 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 in the 'Customers' table for an OrderID, the customer-related columns will be NULL, thus fulfilling the requirement to include all orders. An INNER JOIN would only return orders that have a matching customer. A RIGHT JOIN would return all customers, even those without orders, and matching orders. A FULL OUTER JOIN would return all rows from both tables, matching where possible, and showing NULLs where no match exists in either direction, which is broader than needed.
Question 4
Which SQL aggregate function is used to count the number of rows that are not NULL in a specified column?
AVG(column_name)
SUM(column_name)
COUNT(*)
COUNT(column_name)
✓
Correct Answer
COUNT(column_name)
COUNT(column_name) specifically counts the number of non-NULL values in the specified column. SUM(column_name) calculates the sum of values. AVG(column_name) calculates the average of values. COUNT(*) counts all rows, including those with NULL values in any column, and does not specifically filter by non-NULL values in a particular column.
Question 5
A table 'Sales' has columns Region and SalesAmount. To find the total sales for each region, which clause must be used with the SUM(SalesAmount) aggregate function?
WHERE Region
ORDER BY Region
HAVING Region
GROUP BY Region
✓
Correct Answer
GROUP BY Region
The GROUP BY clause is essential when you want to apply an aggregate function like SUM, AVG, or COUNT to groups of rows that share the same value in one or more columns. In this case, 'GROUP BY Region' will calculate the sum of SalesAmount for each distinct region. ORDER BY is used for sorting the result set. HAVING is used to filter groups based on aggregate conditions, which is not the primary requirement here. WHERE is used to filter individual rows before aggregation.
Question 6
Which of the following statements about SQL indexes is true?
Indexes always improve the performance of data modification operations.
Indexes can speed up data retrieval operations.
✓
Indexes consume no additional disk space.
Indexes are automatically created for all columns in a table.
Correct Answer
Indexes can speed up data retrieval operations.
Indexes are primarily designed to speed up data retrieval operations by providing a quick lookup mechanism for rows. They are not automatically created for all columns; typically, they are created on primary keys, foreign keys, or frequently queried columns. Indexes can sometimes slow down data modification operations (INSERT, UPDATE, DELETE) because the index itself must also be updated. Indexes do consume additional disk space to store the index structure.
Question 7
A table 'Employees' has columns EmployeeID, Name, Salary, and DepartmentID. To find the average salary for departments with an average salary greater than 60000, which SQL construct should be used after the GROUP BY clause?
HAVING AVG(Salary) > 60000
✓
WHERE AVG(Salary) > 60000
FILTER AVG(Salary) > 60000
GROUP BY AVG(Salary) > 60000
Correct Answer
HAVING AVG(Salary) > 60000
The HAVING clause is used to filter groups of rows based on conditions applied to aggregate functions, after the GROUP BY clause has aggregated the data. The condition 'AVG(Salary) > 60000' correctly filters the groups (departments) based on their average salary. The WHERE clause filters individual rows before aggregation. FILTER is not a standard SQL clause for this purpose. GROUP BY defines the groups, it does not filter them based on aggregates.
Question 8
Which normal form requires that a table have no partial dependencies of non-key attributes on a composite primary key?
Third Normal Form (3NF)
Second Normal Form (2NF)
✓
First Normal Form (1NF)
Boyce-Codd Normal Form (BCNF)
Correct Answer
Second Normal Form (2NF)
Second Normal Form (2NF) specifically addresses partial dependencies, requiring that all non-key attributes must be fully functionally dependent on the entire primary key. First Normal Form (1NF) deals with atomic values and the absence of repeating groups. Third Normal Form (3NF) addresses transitive dependencies. Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF that handles cases where the determinant is a candidate key but not the primary key.
Question 9
You need to retrieve the names of all employees whose 'DepartmentID' is present in the 'Departments' table where 'Location' is 'New York'. Which subquery type is most efficient for this task?
A subquery in the SELECT clause (scalar subquery)
A correlated subquery using EXISTS
A subquery in the WHERE clause using IN
✓
A subquery in the FROM clause (derived table)
Correct Answer
A subquery in the WHERE clause using IN
A subquery in the WHERE clause using IN is generally the most straightforward and often efficient way to filter results based on a list of values returned by another query. A subquery in the FROM clause (derived table) is used when you need to treat the subquery's result set as a temporary table. A subquery in the SELECT clause (scalar subquery) returns a single value per row. A correlated subquery using EXISTS can also achieve this but might be less intuitive or performant than IN for simple list membership checks, depending on the database system and specific query.
Question 10
What is the primary purpose of a clustered index in a database?
To physically sort the data rows in the table based on the indexed column(s).
✓
To create a separate, unsorted list of pointers to data rows.
To allow faster full table scans.
To enforce uniqueness on a non-primary key column.
Correct Answer
To physically sort the data rows in the table based on the indexed column(s).
A clustered index physically sorts the data rows in the table itself based on the key values of the index. This means the table's data is stored in the order of the clustered index. Creating a separate, unsorted list of pointers describes a non-clustered index. Enforcing uniqueness on a non-primary key column can be done with a unique non-clustered index. Faster full table scans are not the primary purpose; clustered indexes optimize range scans and specific lookups.
Question 11
Which SQL statement will retrieve the 'Name' and 'Email' of customers from the 'Customers' table, but only if their 'CustomerID' is greater than 100 AND less than 200?
SELECT Name, Email FROM Customers WHERE CustomerID >= 101 AND CustomerID <= 199;
SELECT Name, Email FROM Customers WHERE CustomerID BETWEEN 101 AND 199;
SELECT Name, Email FROM Customers WHERE CustomerID IN (101 TO 199);
SELECT Name, Email FROM Customers WHERE CustomerID > 100 AND CustomerID < 200;
✓
Correct Answer
SELECT Name, Email FROM Customers WHERE CustomerID > 100 AND CustomerID < 200;
The condition 'CustomerID > 100 AND CustomerID < 200' correctly specifies the range, excluding 100 and 200. The BETWEEN operator includes the boundary values, so 'BETWEEN 101 AND 199' would work, but 'BETWEEN 100 AND 200' would include 100 and 200, which is incorrect for 'greater than 100 AND less than 200'. The 'IN (101 TO 199)' syntax is not standard SQL for ranges. The condition 'CustomerID >= 101 AND CustomerID <= 199' is equivalent to 'BETWEEN 101 AND 199', which excludes 100 and 200 but includes 101 and 199, which is not strictly 'greater than 100 AND less than 200' in the sense of open intervals.
Question 12
A table 'Employees' has columns EmployeeID, Name, and ManagerID. ManagerID is a foreign key referencing EmployeeID. To list each employee along with their manager's name, which type of join is required?
CROSS JOIN
OUTER JOIN with ON clause
SELF JOIN
✓
NATURAL JOIN
Correct Answer
SELF JOIN
A SELF JOIN is required when you need to join a table to itself. In this scenario, you join the 'Employees' table to itself (using aliases) to match an employee's ManagerID with another employee's EmployeeID to find the manager's name. A CROSS JOIN produces a Cartesian product, which is not suitable here. A NATURAL JOIN automatically joins on columns with the same name, which might not be appropriate or safe for a self-join. An OUTER JOIN with an ON clause could be part of a self-join if you wanted to include employees without a manager, but 'SELF JOIN' is the specific term for this pattern.
Question 13
In the context of normalization, what does First Normal Form (1NF) primarily require?
There must be no transitive dependencies.
Every non-key attribute must depend on a candidate key.
All non-key attributes must be fully dependent on the primary key.
All attributes must be atomic and there should be no repeating groups.
✓
Correct Answer
All attributes must be atomic and there should be no repeating groups.
First Normal Form (1NF) requires that all attributes in a table must be atomic (indivisible) and that there are no repeating groups of columns. This means each cell should contain a single value. Full dependency of non-key attributes on the primary key is a requirement for Second Normal Form (2NF). No transitive dependencies is a requirement for Third Normal Form (3NF). Every non-key attribute depending on a candidate key relates to BCNF.
Question 14
Which of the following SQL clauses is used to filter the results of aggregate functions?
GROUP BY
HAVING
✓
ORDER BY
WHERE
Correct Answer
HAVING
The HAVING clause is specifically used to filter groups of rows based on conditions applied to aggregate functions (e.g., SUM, AVG, COUNT). The WHERE clause filters individual rows before any aggregation occurs. The GROUP BY clause is used to group rows for aggregation. The ORDER BY clause is used to sort the final result set.
Question 15
A table 'Orders' contains OrderID, ProductID, and Quantity. To find the total quantity for a specific OrderID (e.g., OrderID = 101) using an aggregate function, which query is correct?
SELECT SUM(Quantity) FROM Orders WHERE OrderID = 101;
✓
SELECT Quantity FROM Orders WHERE OrderID = 101;
SELECT SUM(Quantity) FROM Orders HAVING OrderID = 101;
SELECT Quantity FROM Orders GROUP BY OrderID WHERE OrderID = 101;
Correct Answer
SELECT SUM(Quantity) FROM Orders WHERE OrderID = 101;
To find the total quantity for a specific order, you need to SUM the Quantity column and filter for that specific OrderID using a WHERE clause. The query 'SELECT SUM(Quantity) FROM Orders WHERE OrderID = 101;' correctly achieves this. Grouping by OrderID and then using WHERE is syntactically incorrect; WHERE filters before grouping. Using HAVING with OrderID = 101 would be incorrect because HAVING filters groups, and OrderID is not an aggregate function. Simply selecting Quantity without SUM would return individual quantities, not the total.
Question 16
What is a major disadvantage of having too many indexes on a table?
Slower data modification operations (INSERT, UPDATE, DELETE).
✓
Automatic database normalization.
Reduced disk space usage due to efficient data access.
Faster data retrieval for all types of queries.
Correct Answer
Slower data modification operations (INSERT, UPDATE, DELETE).
A major disadvantage of having too many indexes is that it can significantly slow down data modification operations like INSERT, UPDATE, and DELETE. This is because every time data in the base table is changed, the corresponding indexes must also be updated, incurring overhead. Indexes increase, not reduce, disk space usage. While indexes improve data retrieval for specific queries, they do not guarantee faster retrieval for all types of queries, especially full table scans. Indexes do not automatically normalize a database; normalization is a design process.
Question 17
A table 'Employees' has EmployeeID, FirstName, LastName, and Salary. To display the full name (FirstName and LastName concatenated) and salary of all employees, which SELECT clause is correct?
SELECT FirstName || ' ' || LastName AS FullName, Salary FROM Employees;
SELECT FirstName, LastName AS FullName, Salary FROM Employees;
SELECT CONCAT(FirstName, ' ', LastName) AS FullName, Salary FROM Employees;
✓
SELECT (FirstName + ' ' + LastName) AS FullName, Salary FROM Employees;
Correct Answer
SELECT CONCAT(FirstName, ' ', LastName) AS FullName, Salary FROM Employees;
The most universally accepted and standard SQL function for concatenating strings is CONCAT(). While some database systems use '||' (e.g., PostgreSQL, Oracle) or '+' (e.g., SQL Server), CONCAT() is widely supported across many SQL dialects for string concatenation. Simply listing 'FirstName, LastName AS FullName' would not concatenate them. The question asks for a correct SELECT clause, and CONCAT is the most robust option.
Question 18
You need the names of customers who have placed at least one order, from Customers (CustomerID, CustomerName) and Orders (OrderID, CustomerID). Which of these queries does NOT return that list?
SELECT CustomerName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders);
SELECT CustomerName FROM Customers WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);
✓
SELECT CustomerName FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);
SELECT C.CustomerName FROM Customers C JOIN Orders O ON C.CustomerID = O.CustomerID GROUP BY C.CustomerName;
Correct Answer
SELECT CustomerName FROM Customers WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);
NOT EXISTS inverts the test, so that query returns customers who have never placed an order, the exact opposite of what was asked. The IN subquery, the EXISTS subquery and the JOIN with GROUP BY all return customers with at least one order; they differ in style and in how the optimiser may execute them, not in result.
Question 19
A table 'Employees' has columns EmployeeID, DepartmentID, and HireDate. To retrieve the names of employees who were hired after the year 2020, which WHERE clause is correct?
WHERE YEAR(HireDate) > 2020
WHERE HireDate LIKE '2021%' OR HireDate LIKE '2022%'
WHERE HireDate > '2020-12-31'
✓
WHERE DATE_PART('year', HireDate) > 2020
Correct Answer
WHERE HireDate > '2020-12-31'
Comparing a date column directly to a date literal is the most standard and generally efficient way to filter dates. 'WHERE HireDate > '2020-12-31'' correctly selects all dates after December 31, 2020. Functions like YEAR() or DATE_PART('year', HireDate) are database-specific and can sometimes prevent the use of indexes on the HireDate column, making them less efficient for large datasets. Using LIKE '2021%' OR LIKE '2022%' is cumbersome, specific to string representation of dates, and incomplete if future years are included.
Question 20
Which SQL clause is used to sort the result set of a query?
SORT BY
ARRANGE BY
ORDER BY
✓
GROUP BY
Correct Answer
ORDER BY
The ORDER BY clause is the standard SQL keyword used to sort the rows of the result set in ascending (ASC) or descending (DESC) order based on one or more columns. SORT BY and ARRANGE BY are not standard SQL clauses for this purpose. GROUP BY is used for aggregating rows into groups.
Question 21
A table 'Students' has columns StudentID, Name, and CourseID. Another table 'Courses' has CourseID and CourseName. To list all students and their enrolled course names, including students who might not be enrolled in any course, which join should be used, assuming 'Students' is the left table?
FULL OUTER JOIN Students ON Students. CourseID = Courses. CourseID
INNER JOIN Students ON Students. CourseID = Courses. CourseID
RIGHT JOIN Students ON Students. CourseID = Courses. CourseID
LEFT JOIN Students ON Students. CourseID = Courses. CourseID
✓
Correct Answer
LEFT JOIN Students ON Students. CourseID = Courses. CourseID
A LEFT JOIN (or LEFT OUTER JOIN) will return all rows from the left table ('Students') and the matching rows from the right table ('Courses'). For students not enrolled in any course (i.e., no matching CourseID in 'Courses'), the course-related columns will be NULL, thus including all students. An INNER JOIN would only show students who are enrolled in a course. A RIGHT JOIN would show all courses, even those with no students, and matching students. A FULL OUTER JOIN would show all students and all courses, matching where possible, which is broader than needed.
Question 22
Which of the following describes a table that is in Third Normal Form (3NF) but not necessarily Boyce-Codd Normal Form (BCNF)?
A table with no transitive dependencies, but where a non-key attribute determines part of a candidate key.
✓
A table where every determinant is a candidate key, and no partial dependencies exist.
A table with no repeating groups and all attributes are atomic.
A table where every non-key attribute is fully functionally dependent on the primary key, but transitive dependencies exist.
Correct Answer
A table with no transitive dependencies, but where a non-key attribute determines part of a candidate key.
A table is in 3NF if it is in 2NF and has no transitive dependencies. However, BCNF is stricter: it requires that every determinant in the table must be a candidate key. A table can be in 3NF but not BCNF if there is a non-key attribute that determines part of a candidate key, or if a non-key attribute determines another non-key attribute, but the determinant is not a candidate key. The option 'A table with no transitive dependencies, but where a non-key attribute determines part of a candidate key' fits this description. 'A table with no repeating groups and all attributes are atomic' describes 1NF. 'A table where every non-key attribute is fully functionally dependent on the primary key, but transitive dependencies exist' describes 2NF, not 3NF. 'A table where every determinant is a candidate key, and no partial dependencies exist' describes BCNF.