Procoder

Top 3 Winners:

Congratulations!!

Quiz Questions and answers are below:

1. In SQL query optimization, what is a "covering index"? 
   A) An index that includes all columns in a table 
   B) An index used exclusively for sorting data 
   C) An index created by the SQL server automatically 
   D) An index that covers multiple tables
Correct Answer: Option A

2. When optimizing SQL queries, what is "index fragmentation," and how can it impact query performance? 
   A) Index fragmentation is the process of creating duplicate indexes for redundancy.
   B) Index fragmentation occurs when indexes are not used in query execution.   
   C) Index fragmentation is the inefficiency caused by a disordered structure of data in an index, leading to slower queries.
   D) Index fragmentation refers to the use of non-unique indexes in a database schema.
Correct Answer: Option C

3. Which query is optimized one 

   A) Select c.Name, c.City,(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c.
   B) SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co   ON c.CompanyID = co.CompanyID.
   C) SELECT c.Name, c.City, co.CompanyName FROM Customer c INNER JOIN Company co ON c.CompanyID = co.CompanyID.
   D) SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c INNER JOIN Company co ON c.CompanyID = co.CompanyID.
Correct Answer: Option B

4. Which query will use index  
   A) SELECT *  FROM Customer WHERE MembershipCode = '258410';  
   B) SELECT *  FROM Customer  WHERE MembershipCode = '258410'  AND MembershipCode IS NOT NULL; 
   C) SELECT * FROM Customer WHERE MembershipCode = '258410' AND LastName = 'Smith'; 
   D) SELECT * FROM Customer WHERE FirstName = 'John' AND MembershipCode IS NOT NULL;
Correct Answer: Option B

5. You have a table called "Orders" with millions of rows. You want to retrieve the total number of orders placed. Which query will optimize the code?   
   A) SELECT COUNT(*) FROM Orders  
   B) SELECT COUNT(OrderID) FROM Orders 
   C) SELECT SUM(1) FROM Orders   
   D) SELECT TOP 1 OrderID FROM Orders ORDER BY OrderID DESC
Correct Answer: Option A

6. You have a table called "Orders" with columns "OrderID," "OrderDate," and "CustomerID." You need to retrieve the total number of orders placed by each customer in the year 2023. Which query will optimize the code?
   A) SELECT CustomerID, COUNT(OrderID) FROM Orders WHERE YEAR(OrderDate) = 2023 GROUP BY CustomerID;
   B) SELECT CustomerID, COUNT(*) FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY CustomerID;
   C) SELECT CustomerID, SUM(CASE WHEN YEAR(OrderDate) = 2023 THEN 1 ELSE 0 END) AS OrderCount FROM Orders GROUP BY CustomerID;
   D) SELECT DISTINCT CustomerID, (SELECT COUNT(*) FROM Orders o WHERE o.CustomerID = c.CustomerID AND YEAR(o.OrderDate) = 2023) AS OrderCount FROM Customers c;
Correct Answer: Option B

7. You have a table called "Products" with columns "ProductID," "ProductName," and "CategoryID." You need to retrieve the names of products that belong to multiple categories. Which query will optimize the code?
   A) SELECT ProductName FROM Products WHERE ProductID IN ( SELECT ProductID FROM Products GROUP BY ProductID HAVING COUNT(DISTINCT CategoryID) > 1 ); 
   B) SELECT DISTINCT p1.ProductName FROM Products p1 JOIN Products p2 ON        p1.ProductID = p2.ProductID AND p1.CategoryID <> p2.CategoryID;
   C) SELECT DISTINCT ProductName FROM Products WHERE CategoryID IN ( SELECT CategoryID FROM Products GROUP BY CategoryID HAVING COUNT(*) > 1 );
   D) SELECT DISTINCT ProductName FROM Products WHERE CategoryID IN ( SELECT CategoryID FROM Products GROUP BY CategoryID HAVING COUNT(DISTINCT ProductID) > 1 ); 
Correct Answer: Option A

8. Which query is optimized one and why

a.       Select * from Customer where YEAR(AccountCreatedOn) == 2005 and  MONTH(AccountCreatedOn) = 6

b.       Select * From Customer Where AccountCreatedOn between ‘6/1/2005’ and ‘6/30/2005

   A) Query ‘a’ is optimized as we are using functions with integer values
   B) Query ‘b’ is optimized as it is not using any functions
   C) Query ‘a’ is optimized and will give more accurate result 
   D) Query ‘b’ is optimized as it is not using functions and index will also work
Correct Answer: Option D

9. You have a table with millions of records, and you need to retrieve the top 10 records ordered by a timestamp column named "created_at." Which query will optimize the code?
   A) SELECT * FROM TableName ORDER BY created_at DESC LIMIT 10;
   B) SELECT TOP 10 * FROM TableName ORDER BY created_at DESC;
   C) SELECT * FROM TableName WHERE ROW_NUMBER() <= 10 ORDER BY created_at DESC;
   D) SELECT * FROM TableName WHERE created_at >= (SELECT MIN(created_at) FROM TableName ORDER BY created_at DESC LIMIT 10);
Correct Answer: Option A

10. You have a table with a column "email" containing email addresses, and you want to find all the unique email domains. Which query will optimize the code?
   A) SELECT DISTINCT SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain FROM TableName;
   B) SELECT DISTINCT RIGHT(email, LEN(email) - CHARINDEX('@', email)) AS domain FROM TableName;
   C) SELECT DISTINCT email FROM TableName;
   D) SELECT DISTINCT LEFT(email, CHARINDEX('@', email) - 1) AS domain FROM TableName;
Correct Answer: Option A

11. You have a table with a column "score," and you want to retrieve the average score rounded to the nearest integer. Which query will optimize the code?
   A) SELECT ROUND(AVG(score)) AS average_score FROM TableName;
   B) SELECT CAST(AVG(score) AS INT) AS average_score FROM TableName;
   C) SELECT AVG(score) AS average_score FROM TableName;
   D) SELECT ROUND(AVG(score), 0) AS average_score FROM TableName;
Correct Answer: Option B

12. You want to retrieve the names of employees who have not attended any training sessions from two tables: "employees" and "training_sessions." Which query will optimize the code?
   A) SELECT e.name FROM employees e LEFT JOIN training_sessions t ON e.employee_id = t.employee_id WHERE t.training_id IS NULL;
   B) SELECT e.name FROM employees e INNER JOIN training_sessions t ON e.employee_id = t.employee_id WHERE t.training_id IS NULL;
   C) SELECT e.name FROM employees e WHERE e.employee_id NOT IN (SELECT DISTINCT employee_id FROM training_sessions);
   D) SELECT e.name FROM employees e WHERE e.employee_id IN (SELECT DISTINCT employee_id FROM employees) AND e.employee_id NOT IN (SELECT DISTINCT employee_id FROM training_sessions);
Correct Answer: Option A

13. You need to retrieve the count of orders placed by each customer along with their names. Which query will optimize the code?
   A) SELECT c.name, COUNT(o.order_id) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;
   B) SELECT c.name, SUM(1) AS order_count FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;
   C) SELECT c.name, COUNT(*) FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;
   D) SELECT c.name, COUNT(DISTINCT o.order_id) FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;
Correct Answer: Option A

14. You have three tables: "Customers," "Orders," and "OrderDetails." The "Customers" table contains customer information, the "Orders" table contains order information, and the "OrderDetails" table contains details about the items ordered in each order. You need to retrieve the names of customers who have placed orders containing at least one item with a price greater than $100 and the total number of such orders for each customer. Write an optimized SQL query to achieve this.
   A) SELECT c.CustomerName, COUNT(o.OrderID) AS TotalOrders FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE od.Price > 100 GROUP BY c.CustomerName HAVING COUNT(DISTINCT o.OrderID) > 0;
   B) SELECT c.CustomerName, COUNT(o.OrderID) AS TotalOrders FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID LEFT JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE od.Price > 100 GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 0;
   C) SELECT c.CustomerName, COUNT(o.OrderID) AS TotalOrders FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE EXISTS ( SELECT 1 FROM OrderDetails od2 WHERE od2.OrderID = o.OrderID AND od2.Price > 100 ) GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 0;
   D) SELECT c.CustomerName, COUNT(o.OrderID) AS TotalOrders FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE EXISTS ( SELECT 1 FROM OrderDetails od2 WHERE od2.OrderID = o.OrderID AND od2.Price > 100 ) GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 0;
Correct Answer: Option A

15. You are managing a database for an e-commerce platform with tables for products, orders, and customers. You need to find the top 10 customers who have spent the most on products in the "Electronics" category in a specific year (e.g., 2023). Write an optimized SQL query to achieve this. Here are the table structures:

"Customers" (CustomerID, CustomerName)

"Orders" (OrderID, CustomerID, OrderDate)

"OrderDetails" (OrderDetailID, OrderID, ProductID, Quantity)

"Products" (ProductID, ProductName, Category, Price)

   A) SELECT c.CustomerName, SUM(od.Quantity * p.Price) AS TotalSpent FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID JOIN Products p ON od.ProductID = p.ProductID WHERE p.Category = 'Electronics' AND YEAR(o.OrderDate) = 2023 GROUP BY c.CustomerName ORDER BY TotalSpent DESC LIMIT 10;
   B) SELECT c.CustomerName, SUM(p.Price) AS TotalSpent FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID JOIN Products p ON od.ProductID = p.ProductID WHERE p.Category = 'Electronics' AND YEAR(o.OrderDate) = 2023 GROUP BY c.CustomerName HAVING SUM(p.Price) IN ( SELECT TOP 10 SUM(p2.Price) AS TotalSpent FROM Customers c2 JOIN Orders o2 ON c2.CustomerID = o2.CustomerID JOIN OrderDetails od2 ON o2.OrderID = od2.OrderID JOIN Products p2 ON od2.ProductID = p2.ProductID WHERE p2.Category = 'Electronics' AND YEAR(o2.OrderDate) = 2023 GROUP BY c2.CustomerName ORDER BY TotalSpent DESC );
   C) SELECT c.CustomerName, SUM(p.Price) AS TotalSpent FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID JOIN Products p ON od.ProductID = p.ProductID WHERE p.Category = 'Electronics' AND YEAR(o.OrderDate) = 2023 GROUP BY c.CustomerName ORDER BY TotalSpent DESC LIMIT 10;
   D) SELECT c.CustomerName, SUM(p.Price) AS TotalSpent FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID JOIN Products p ON od.ProductID = p.ProductID WHERE p.Category = 'Electronics' AND YEAR(o.OrderDate) = 2023 GROUP BY c.CustomerName HAVING COUNT(DISTINCT p.ProductID) > 0 ORDER BY TotalSpent DESC LIMIT 10;
Correct Answer: Option A