Top 10 SQL Questions for Interviews

Joins (Inner, Outer, Cross, Self)

1. Inner Join: Retrieve a list of customers who have placed orders.

   SELECT Customers.CustomerName, Orders.OrderID 
   FROM Customers 
   INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
   

2. Left Join: Find employees who have not been assigned to any projects.

   SELECT Employees.EmployeeName 
   FROM Employees 
   LEFT JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID 
   WHERE Projects.EmployeeID IS NULL;
   

3. Right Join: Get a list of projects without assigned employees.

   SELECT Projects.ProjectName 
   FROM Employees 
   RIGHT JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID 
   WHERE Employees.EmployeeID IS NULL;
   

4. Cross Join: Generate all possible pairs of products.

   SELECT P1.ProductName AS Product1, P2.ProductName AS Product2 
   FROM Products P1 
   CROSS JOIN Products P2;
   

5. Self Join: Match employees to themselves to find pairs from the same department.

   SELECT E1.EmployeeName AS Employee1, E2.EmployeeName AS Employee2 
   FROM Employees E1, Employees E2 
   WHERE E1.DepartmentID = E2.DepartmentID AND E1.EmployeeID <> E2.EmployeeID;
   

Subqueries

1. Find employees earning more than the average salary in their department.

   SELECT EmployeeName, Salary 
   FROM Employees 
   WHERE Salary > (SELECT AVG(Salary) FROM Employees GROUP BY DepartmentID);
   

2. Retrieve customers who have placed more than 5 orders.

   SELECT CustomerName 
   FROM Customers 
   WHERE CustomerID IN (SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5);
   

3. List products that have never been ordered.

SELECT ProductName 
FROM Products 
WHERE ProductID NOT IN (SELECT ProductID FROM OrderDetails);

1. Identify the second-highest salary in the company using a subquery.

   SELECT MAX(Salary) AS SecondHighestSalary 
   FROM Employees 
   WHERE Salary < (SELECT MAX(Salary) FROM Employees);
   

2. Find departments where all employees earn above a specific threshold.

   SELECT DepartmentName 
   FROM Departments 
   WHERE DepartmentID NOT IN (SELECT DepartmentID FROM Employees WHERE Salary <= 50000);
   

What Undercode Say

SQL is a powerful tool for managing and querying relational databases. Mastering SQL joins and subqueries is essential for data analysis and database management. Here are some additional Linux and Windows commands to enhance your SQL and database management skills:

• Linux Commands:
• Use `mysql` to access MySQL databases:

  mysql -u username -p
  

• Backup a MySQL database:

  mysqldump -u username -p database_name > backup.sql
  

• Restore a MySQL database:

  mysql -u username -p database_name < backup.sql
  

• Windows Commands:

• Start MySQL service:
  [cmd]
  net start mysql
  [/cmd]
• Stop MySQL service:
  [cmd]
  net stop mysql
  [/cmd]
• Access MySQL via Command
  [cmd]
  mysql -u username -p
  [/cmd]

For further learning, explore these resources:

• SQL Joins Explained
• SQL Subqueries Guide

Practice these commands and queries to strengthen your SQL skills and prepare for technical interviews.

References:

Hackers Feeds, Undercode AI