Intermediate

SQL Questions

Programming Language

10 questions

Please login before attempting the quiz

Question 1

You're asked to retrieve the names of all employees who earn more than the average salary of their respective departments. This necessitates calculating the average salary per department and then comparing it to individual employee salaries. Which type of subquery would be most appropriate for this scenario?

Scalar Subquery
Inline View
Correlated Subquery
Non-Correlated Subquery

Question 2

You need to find all customers in your 'Customers' table who haven't provided a phone number. The 'PhoneNumber' column can contain NULL values. Which query achieves this?

SELECT * FROM Customers WHERE PhoneNumber IS NULL;

SELECT * FROM Customers WHERE PhoneNumber <> '';

SELECT * FROM Customers WHERE PhoneNumber = NULL;

SELECT * FROM Customers WHERE PhoneNumber NOT IN (NULL);

Question 3

You are tasked with identifying products that have sold more units than the average units sold for all products in their respective category. Which SQL query structure is best suited for this scenario?

None of the above
Simple Subquery
Subquery in the FROM Clause
Correlated Subquery

Question 4

You have a table 'Customers' with a column 'LastPurchaseDate' that can contain NULL values. Write a query to retrieve all customers who have NOT made a purchase in the last 90 days.

SELECT *
FROM Customers
WHERE LastPurchaseDate < DATEADD(day, -90, GETDATE());

SELECT *
FROM Customers
WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());

SELECT *
FROM Customers
WHERE LastPurchaseDate IS NULL;

SELECT *
FROM Customers
WHERE LastPurchaseDate < GETDATE() - 90;

Question 5

You have a large table with millions of records. You frequently query the table based on a specific column 'customer_city'. What is the most effective way to improve the performance of these queries?

Optimize the database server's configuration parameters.
Add an index to the 'customer_city' column.
Increase the memory allocated to the database server.
Use a stored procedure instead of ad-hoc queries.

Question 6

What is a key difference between a regular subquery and a correlated subquery?

Regular subqueries can modify data, while correlated subqueries are read-only.
Correlated subqueries are executed only once for the entire outer query, while regular subqueries are executed for each row.
Correlated subqueries depend on the outer query for their results, while regular subqueries are independent.
Regular subqueries can access columns from the outer query, while correlated subqueries cannot.

Question 7

What is a key advantage of using a subquery in the FROM clause to create a derived table?

It can enhance performance by pre-calculating and storing intermediate results.
All of the above
It allows you to reuse the derived table multiple times within the same query.
It improves the readability of complex queries by breaking them down into smaller, more manageable parts.

Question 8

You have a table named 'Orders' with columns 'OrderID' and 'CustomerID'. You need to find the customers who have placed more than 5 orders. Which query achieves this using a subquery in the FROM clause?

SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 5
SELECT CustomerID FROM Orders WHERE EXISTS (SELECT 1 FROM Orders WHERE CustomerID = Orders.CustomerID GROUP BY CustomerID HAVING COUNT(*) > 5)
SELECT DISTINCT CustomerID FROM Orders WHERE OrderID IN (SELECT OrderID FROM Orders GROUP BY OrderID HAVING COUNT(*) > 5)
SELECT CustomerID FROM (SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID) AS CustomerOrders WHERE OrderCount > 5

Question 9

What is the primary purpose of using a Common Table Expression (CTE) in SQL?

To replace the need for views in a database.
To create a temporary, named result set that exists only within the scope of the current query.
To improve the performance of complex queries by storing intermediate results.
To define a recursive relationship within a table.

Question 10

You have a table of orders and want to calculate the running total of order amounts for each customer, ordered by order date. Which SQL feature would be most efficient for this task?

Recursive CTE
Self Join
Window Function
Cross Join

SQL Questions

Please login before attempting the quiz

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Please login before attempting the quiz

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Login Account