SQL Query

Question : SQL query that will return the employees who earn more than their managers?

SELECT e1.EmpName 
FROM Employee e1 
INNER JOIN Employee e2 
ON e1.managerId = e2.EmpId 
WHERE e1.salary > e2.salary;

In this query:

• Employee e1 and Employee e2 are aliases for the Employee table.
• The INNER JOIN clause combines rows from e1 and e2 based on the condition e1.managerId = e2.EmpId.
• The WHERE clause filters out the records where an employee’s salary is greater than their manager’s salary.

This will give you the names of employees who earn more than their managers.

Question: Find nth highest salary in sql?

Using LIMIT and OFFSET: You can use LIMIT with OFFSET to skip the (n-1) highest salaries and get the nth highest salary.

SELECT DISTINCT(salary) AS salary
FROM tbl_salary
ORDER BY salary DESC
LIMIT 1 OFFSET (n - 1);

Question: Highest Salary in each department?

SELECT DEPT_ID, MAX(SALARY) 
FROM department 
GROUP BY DEPT_ID;

Question: Display records 3 to 9 from the Student table.

SELECT * 
FROM Student 
LIMIT 7 OFFSET 2;

In this query, Student is the name of the table. This query skips the first 2 records and then retrieves the next 7 records.

Question: Customer who never orders

To find all customers who never order anything, you can use a LEFT JOIN clause to combine the Customers and Orders tables, and then filter for customers who have no matching records in the Orders table. Ref

SELECT Customers.name AS 'Customers'
FROM Customers
LEFT JOIN Orders ON Customers.id = Orders.customerId
WHERE Orders.id IS NULL;

SELECT name AS Customers 
FROM Customers 
WHERE id NOT IN (
  SELECT customerId 
  FROM Orders
);

Get the 3 Highest salaries records from the Employee table?

To get the 3 highest salaries from the Employee table, you can use the LIMIT clause in combination with the ORDER BY clause. Here’s an example query:

SELECT * 
FROM Employee 
ORDER BY salary DESC 
LIMIT 3;