programming beginner 10 sample questions

Sql MCQ Practice Test

Basic SELECT statements for fetching data. Using JOINs to combine tables and retrieve related data. Writing complex queries involving IN, BETWEEN, LIKE, and HAVING clauses.

Q1. Consider a SQL database with tables 'Employees' (employeeID INT PRIMARY KEY, departmentID INT, salary DECIMAL) and 'Departments' (departmentID INT PRIMARY KEY, departmentName VARCHAR(50)). You need to identify departments where the average employee salary is more than 1.5 times the overall average salary across all departments. Which query achieves this most efficiently, considering potential performance implications for large datasets and avoiding subqueries where possible?

Explanation: This query groups by the column it selects (valid SQL) and compares each department's average to 1.5× the overall average using an uncorrelated subquery, which the planner evaluates once — clean and efficient.

Q2. You have a SQL table named "ProductSales" with columns "ProductID" (INT, PRIMARY KEY), "ProductName" (VARCHAR(255)), "SalesDate" (DATE), and "QuantitySold" (INT). You need to retrieve the ProductName and total quantity sold for products that had at least 50 units sold in the month of March 2024. Which query would correctly achieve this, assuming the SalesDate is stored in 'YYYY-MM-DD' format?

Explanation: Option 1 correctly uses LIKE to filter dates for March 2024, SUM to aggregate sales, GROUP BY to group by product, and HAVING to filter groups with a total of 50 or more units sold. Option 2 uses COUNT(*) which is inappropriate for summing quantities. Option 3 uses a > operator instead of >=. Option 4 only retrieves products with an individual quantity sold of at least 50 and does not sum the quantities for each product across multiple sales.

Q3. You have a SQL database tracking website visits. The 'visits' table has columns 'visitorID' (INT, primary key), 'visitTimestamp' (TIMESTAMP), and 'userAgent' (VARCHAR(255)). You want to identify the user agents that made more than 10 visits in the last 24 hours. Which query would most efficiently achieve this, assuming appropriate indexes are in place?

Explanation: Options 1 and 4 are less efficient because they perform an unnecessary selection and aggregation of the `visitCount`. Option 3 uses DATETIME which might not work across all SQL dialects and is less readable than using DATE. The correct option directly selects the `userAgent` after grouping and applying the HAVING clause, making it the most efficient.

Q4. You have a SQL database tracking website visits. The 'visits' table has columns 'user_id' (INT, primary key), 'visit_timestamp' (TIMESTAMP), and 'page_url' (VARCHAR). You want to identify users who visited '/product-page' and '/checkout' in any order, but within a 1-hour time window. Which query most efficiently identifies these users?

Explanation: This query efficiently uses IN to check for both pages, DISTINCT COUNT to ensure both were visited, and the time difference calculation to limit to the 1-hour window. Other options are less efficient or incorrect: the INTERSECT approach doesn't handle the time window, while the UNION approach doesn't accurately count visits within the time window. The first option uses AND incorrectly, requiring a single row to contain both page URLs simultaneously.

Q5. You have a table named "ProductSales" with columns "ProductID", "ProductName", "SalesRegion", and "SalesAmount". You need to identify the product with the highest total sales amount across all sales regions. Which SQL query would most efficiently achieve this, assuming that "SalesAmount" is always positive?

Explanation: Option 1 correctly uses aggregate function SUM() to calculate total sales per product, groups by ProductName, orders the results in descending order of TotalSales, and then uses LIMIT 1 to select only the top row representing the product with the highest total sales. Option 2 only considers the highest individual sale, not the total across all regions. Option 3 finds the maximum sales amount for each product individually, not the total across all regions. Option 4 achieves the same result as option 1 but in a less efficient way using a subquery.

Q6. You have a table named "ProductSales" with columns "ProductID" (INT, primary key), "ProductName" (VARCHAR(255)), "SalesDate" (DATE), and "QuantitySold" (INT). You need to find the product with the highest total quantity sold in the month of October 2023. Which SQL query would correctly identify this product and its total sales, assuming that ties are possible and you only want one such product if a tie exists?

Explanation: This query uses the correct date range specification for October 2023, correctly aggregates sales by product, orders the results by total sales in descending order, and uses LIMIT 1 to retrieve only the top product. The other options either use less efficient date comparisons, rely on database-specific functions that might not be universally supported (like TOP or strftime), or might not handle ties correctly.

Q7. You have a SQL database tracking website visits. The `visits` table has columns `visit_id` (INT, PRIMARY KEY), `user_id` (INT), `timestamp` (TIMESTAMP), and `page_url` (VARCHAR(255)). You need a query that returns the number of unique users who visited the '/products' page between '2024-01-01' and '2024-01-31'. Which query achieves this most efficiently?

Explanation: The correct query uses `COUNT(DISTINCT user_id)` to count only the unique user IDs. Other options either count all visits (not unique users) or return a list of user IDs instead of a count.

Q8. You have a table named "ProductSales" with columns "ProductID", "ProductName", "SalesDate", and "QuantitySold". You want to retrieve the product name and total quantity sold for each product, but only include products where the total quantity sold is greater than 100. Which SQL query achieves this most efficiently?

Explanation: The correct query uses the SUM aggregate function to calculate the total quantity sold for each product, groups the results by ProductName, and then uses the HAVING clause to filter out products with a total quantity sold less than or equal to 100. The other options either incorrectly use WHERE instead of HAVING, only consider individual sales rather than totals, or use COUNT(*) which counts the number of sales records, not the total quantity.

Q9. You have a SQL table named "ProductSales" with columns "ProductID", "ProductName", "SalesDate", and "QuantitySold". You need to retrieve the ProductName and total quantity sold for each product, but only include products where the total quantity sold is greater than 100. Which query would achieve this?

Explanation: The correct query uses a `GROUP BY` clause to aggregate sales by product, `SUM()` to calculate the total quantity sold for each product, and a `HAVING` clause to filter out products with total sales less than or equal to 100. The other options either fail to aggregate correctly, apply the filter incorrectly, or count products instead of summing sales.

Q10. You have a SQL table named 'ProductInventory' with columns 'ProductID' (INT, PRIMARY KEY), 'ProductName' (VARCHAR(255)), 'QuantityInStock' (INT), and 'ReorderLevel' (INT). You need to retrieve the ProductName for all products where the QuantityInStock is less than or equal to half of the ReorderLevel. Which query would achieve this most efficiently?

Explanation: Doubling QuantityInStock avoids any integer-division truncation when ReorderLevel is odd (e.g., reorder level 5: q*2 <= 5 correctly excludes q=3, while q <= 5/2 may not depending on dialect).

That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.

Sign Up Free
Sql MCQ Practice Test | Mind Hustle