Intermediate

SQL Questions

Programming Language

10 questions

Please login before attempting the quiz

Question 1

Imagine you need to find pairs of employees who share the same job title without displaying the same employee twice (e.g., Employee A paired with Employee B is the same as Employee B with Employee A). Which SQL concept would be most efficient for this task?

UNION
INTERSECT
Self-Join
Cross Join

Question 2

You have a table named 'Employees' with columns 'EmployeeID', 'FirstName', 'LastName', and 'Salary'. You need to categorize employees based on their salary. Write a SQL query using CASE statement to display 'High Earner' if the salary is above 50000, 'Average Earner' if the salary is between 30000 and 50000, and 'Low Earner' otherwise.

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary = 50000 THEN 'High Earner'
    WHEN Salary BETWEEN 30000 AND 50000 THEN 'Average Earner'
    ELSE 'Low Earner'
END AS SalaryCategory
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary > 50000 THEN 'High Earner'
    ELSE 'Low Earner'
END AS SalaryCategory
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary > 50000 THEN 'High Earner'
    WHEN Salary < 30000 THEN 'Low Earner'
    ELSE 'Average Earner'
END AS SalaryCategory
FROM Employees;

SELECT EmployeeID, FirstName, LastName,
CASE
    WHEN Salary > 50000 THEN 'High Earner'
    WHEN Salary BETWEEN 30000 AND 50000 THEN 'Average Earner'
    ELSE 'Low Earner'
END AS SalaryCategory
FROM Employees;

Question 3

Which of the following SQL statements is used to definitively save changes made within a transaction?

SAVE
COMMIT
PERSIST
UPDATE

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 IS NULL;

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 < GETDATE() - 90;

Question 5

You are tasked with finding the total sales for each month of the year. You have a table 'Orders' with columns 'OrderID', 'OrderDate', and 'SalesAmount'. Which query correctly calculates the monthly sales totals?

SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
ORDER BY Month;

SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY Month;

SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
ORDER BY MONTH(OrderDate);

SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales
FROM Orders
GROUP BY Month;

Question 6

Which type of subquery can access columns from the outer query, potentially leading to performance implications?

Scalar subquery
Correlated subquery
Inline view
Non-correlated subquery

Question 7

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?

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

Question 8

You need to represent a hierarchical organization structure in a relational database table. Which approach is most suitable for querying and traversing this hierarchical data?

Using multiple tables with foreign key relationships to represent different levels
Storing the entire hierarchy as a single string in a column
Implementing a Recursive CTE to query and navigate the hierarchy
Creating a separate table for each level of the hierarchy

Question 9

What SQL statement is used to apply the changes made within a transaction permanently to the database?

COMMIT
EXECUTE
UPDATE
SAVE

Question 10

You are given a table 'EmployeeHierarchy' with columns 'EmployeeID' and 'ManagerID', representing an employee hierarchy. Write a recursive SQL query to retrieve the entire hierarchy starting from the CEO (EmployeeID = 1).

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
WHERE ManagerID IS NULL;

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
WHERE EmployeeID = 1;

WITH RecursiveHierarchy AS (
    SELECT EmployeeID, ManagerID
    FROM EmployeeHierarchy
    WHERE EmployeeID = 1
    UNION ALL
    SELECT eh.EmployeeID, eh.ManagerID
    FROM EmployeeHierarchy eh
    JOIN RecursiveHierarchy rh ON eh.ManagerID = rh.EmployeeID
)
SELECT *
FROM RecursiveHierarchy;

SELECT EmployeeID, ManagerID
FROM EmployeeHierarchy
ORDER BY ManagerID;

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