SQL ADVANCED JOINS QUESTIONS

 Question 1:

Given two tables, Employees and Departments, where Employees has columns EmployeeID, EmployeeName, and DepartmentID, and 

Departments has columns DepartmentID and DepartmentName, write a query to find all employees along with their department names

. Also, include employees who do not belong to any department.


SELECT e.EmployeeName, d.DepartmentName

FROM Employees e

LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;


Question 2:

Assume you have a Customers table with CustomerID and CustomerName columns, and an Orders table with OrderID, CustomerID, and OrderDate.

How would you write a query to find customers who have placed more than 5 orders? 


SELECT c.CustomerName, COUNT(o.OrderID) AS OrderCount

FROM Customers c

JOIN Orders o ON c.CustomerID = o.CustomerID

GROUP BY  c.CustomerName

HAVING COUNT(o.OrderID) > 5;




Question 3: Write a query to list all products that have never been ordered. Assume you have a Products table with ProductID and ProductName 

columns, and an OrderDetails table with OrderID, ProductID, and Quantity columns


SELECT p.ProductName

FROM Products p

LEFT JOIN OrderDetails od ON p.ProductID = od.ProductID

WHERE od.ProductID IS NULL;



Question 4: How would you find the second highest salary in a Salaries table with EmployeeID and Salary columns?


SELECT MAX(Salary) AS SecondHighestSalary

FROM Salaries

WHERE Salary < (SELECT MAX(Salary) FROM Salaries);




Question 5: Given a Projects table with ProjectID and ProjectName columns, and an EmployeeProjects table with EmployeeID and ProjectID columns

, write a query to find all projects that have more than 10 employees assigned.


SELECT p.ProjectName, COUNT(ep.EmployeeID) AS EmployeeCount

FROM Projects p

JOIN EmployeeProjects ep ON p.ProjectID = ep.ProjectID

GROUP BY  p.ProjectName

HAVING COUNT(ep.EmployeeID) > 10;


Question 6: Write a query to find the department with the highest average salary. Assume you have an Employees table with EmployeeID, EmployeeName

, DepartmentID, and Salary columns, 

and a Departments table with DepartmentID and DepartmentName columns.


SELECT TOP 1 d.DepartmentName, AVG(e.Salary) AS AvgSalary

FROM Employees e

JOIN Departments d ON e.DepartmentID = d.DepartmentID

GROUP BY    d.DepartmentName

ORDER BY AvgSalary DESC


Question 7: How would you write a query to find all pairs of employees who work in the same department? Assume you have an Employees table

with EmployeeID, EmployeeName, and DepartmentID columns.


SELECT e1.EmployeeName AS Employee1, e2.EmployeeName AS Employee2, e1.DepartmentID

FROM Employees e1

JOIN Employees e2 ON e1.DepartmentID = e2.DepartmentID AND e1.EmployeeID < e2.EmployeeID;


DROP TABLE IF EXISTS Employee

CREATE TABLE Employee

(

ID [int],Name [varchar](max),Dep_ID INT

)


INSERT INTO Employee

VALUES (1 ,'ABC', 10)

,(2 ,'PQR' ,10)

,(3 ,'XYZ', 10)


SELECT *

FROM Employee E1 

JOIN Employee E2

ON e1.Dep_ID =e2.Dep_ID

WHERE E1.ID<E2.ID


Question 8: Given a Sales table with SaleID, ProductID, and SaleAmount columns, and a Products table with ProductID and ProductName columns

, write a query to find the top 3 products by total sales amount.


SELECT TOP 3 p.ProductName, SUM(s.SaleAmount) AS TotalSales

FROM Sales s

JOIN Products p ON s.ProductID = p.ProductID

GROUP BY  p.ProductName

ORDER BY TotalSales DESC

 

Comments

Post a Comment

Popular posts from this blog

50 Essential SQL Questions to Land Your Dream Job

 CREATE TABLE worker (     first_name VARCHAR(50),     last_name VARCHAR(50),     department VARCHAR(50),     salary DECIMAL(10, 2),     joining_date DATE ); INSERT INTO worker (first_name, last_name, department, salary, joining_date) VALUES  ('John', 'Doe', 'HR', 55000, '2020-01-15'), ('Jane', 'Smith', 'Finance', 67000, '2019-03-22'), ('Robert', 'Johnson', 'IT', 72000, '2018-06-10'), ('Emily', 'Davis', 'Marketing', 59000, '2021-07-01'), ('Michael', 'Wilson', 'Sales', 63000, '2020-09-15'), ('Sarah', 'Brown', 'HR', 56000, '2022-02-19'), ('David', 'Miller', 'Finance', 68000, '2019-10-05'), ('Laura', 'Garcia', 'IT', 75000, '2020-11-22'), ('James', 'Martinez', 'Marketing', 60000, '2021-03-18'), ('Olivia...
Read more

How to find all the customers who placed orders on three consecutive days | SQL Scenario questions

  DML Script: DROP TABLE IF EXISTS Orders CREATE TABLE Orders ( customer_name VARCHAR(255), order_date DATETIME ); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-01'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-02'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-03'); INSERT INTO Orders (customer_name, order_date) VALUES ('Alice', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Bob', '2024-04-04'); INSERT INTO Orders (customer_name, order_date) VALUES ('Charlie', '2024-04-05'); INSER...
Read more

ACCENTURE SQL INTERVIEW QUESTION | Change the ProductIDs

 DROP TABLE IF EXISTS Products; CREATE TABLE Products (     ProductID INT PRIMARY KEY,     Product VARCHAR(255),     Category VARCHAR(100) ); INSERT INTO Products (ProductID, Product, Category) VALUES     (1, 'Laptop', 'Electronics'),     (2, 'Smartphone', 'Electronics'),     (3, 'Tablet', 'Electronics'),     (4, 'Headphones', 'Accessories'),     (5, 'Smartwatch', 'Accessories'),     (6, 'Keyboard', 'Accessories'),     (7, 'Mouse', 'Accessories'),     (8, 'Monitor', 'Accessories'),     (9, 'Printer', 'Electronics');
Read more