Common SQL Questions

How to update a table?

UPDATE table_name
SET col_1 = value_1, column_2 = value_2
WHERE condition;

627. Swap Salary : Write a solution to swap all 'f' and 'm' values (i.e., change all 'f' values to 'm' and vice versa) with a single update statement and no intermediate temporary tables.

update Salary
set sex = case
            when sex = 'f' then 'm'
            when sex = 'm' then 'f'
            else sex
        end;

How to delete a table from a database?

DROP TABLE table_name

How to add a column to a table?

ALTER TABLE table_name
ADD column_name datatype;

How to rename a column of a table?

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

How to find the nth highest value in a column of a table?

Using the OFFSET clause. For example, to find the 6th highest value from a column, we would use the following syntax:

OFFSET 5: Skip the first 5 rows of the result set.

SELECT DISTINCT number
FROM numbers
ORDER BY number DESC
OFFSET 2
LIMIT 1;

Question 1: SQL Query to find the second highest salary of Employee(176. Second Highest Salary)

SELECT MAX(Salary)
FROM Employee
WHERE Salary NOT IN (
    SELECT MAX(Salary)
    FROM Employee);

Question 2: SQL Query to find the third highest salary of Employee

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (
    SELECT MAX(Salary) FROM Employee
    WHERE Salary NOT IN (
        SELECT MAX(Salary) FROM Employee
    )
);

Question 3: SQL query to find the Nth highest salary (177. Nth Highest Salary)

SELECT Salary 
FROM Employee a 
WHERE N = ( 
    SELECT COUNT(Salary) 
    FROM Employee b 
    WHERE a.Salary <= b.Salary );

For example, given the above Employee table, the nth highest salary where N = 2 is 200. If there is no Nth highest salary, then the query should return null. You can see that we have used the above query to find the highest, second-highest, and third-highest salaries from the employee table.

Question 4: SQL Query to find Max Salary from each department.

SELECT DeptID, MAX(Salary) 
FROM Employee 
GROUP BY DeptID;

These questions become more interesting if the Interviewer will ask you to print the department name instead of the department id, in that case, you need to join the Employee table with Department using the foreign key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to include departments without any employee as well. Here is the query

SELECT DeptName, MAX(Salary) 
FROM Employee e RIGHT JOIN Department d 
ON e.DeptId = d.DeptID 
GROUP BY DeptName;

Question 5: Finding duplicate emails (182. Duplicate Emails)

select email as Email
from Person
group by email
having count(email) > 1;

SELECT DISTINCT p1.Email
FROM Person p1
WHERE EXISTS(
    SELECT *
    FROM Person p2
    WHERE p2.Email = p1.Email
    AND p2.Id != p1.Id
)

196. Delete Duplicate Emails: Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id

Delete P1
from Person P1, Person P2
where P1.email = P2.email and P1.id > P2.id;

Question 6: Find Customers Who Never Order

SELECT C.Name 
FROM Customers C 
WHERE NOT EXISTS 
    (SELECT 1 
    FROM Orders O 
    WHERE C.Id = O.CustomerId);

Question 7: Write an SQL Query to print the name of the distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

SELECT DISTINCT EmpName 
FROM Employees 
WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Question 8: Write an SQL Query to find the number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

SELECT COUNT(*), sex 
FROM Employees  
WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' 
GROUP BY sex;

Question 9: Write an SQL Query to find the name of an employee whose name Start with ‘M’

SELECT * 
FROM Employees 
WHERE EmpName like 'M%';

Question 10: Find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

SELECT * 
from Employees  
WHERE UPPER(EmpName) like '%JOE%';

Question 11: There is a table which contains two columns Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above-average students.

SELECT student, marks 
FROM table 
WHERE marks > (SELECT AVG(marks) from table)

Question 12: Write a SQL query to find the names of employees who have not been assigned to any project.

SELECT employees.name
FROM employees
LEFT JOIN projects
ON employees.employee_id = projects.employee_id
WHERE projects.employee_id IS NULL;

Question 13 : Create a Customer and Order Table and Connect Them

CREATE TABLE customers_Table (
	customer_id INT PRIMARY KEY,
	name VARCHAR(50),
	email VARCHAR(100)
);
INSERT INTO customers_Table (customer_id, name, email)
VALUES (1, 'Pallavi Tiwari', '[e-mail]');

CREATE TABLE orders_Table (
	order_id INT PRIMARY KEY,
	customer_id INT,
	order_date DATE,
	total_amount DECIMAL(10, 2),
	FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO orders_Table(order_id, customer_id, order_date, total_amount)
VALUES (101, 1, '2023-01-05', 100.00);

INSERT INTO orders_Table (order_id, customer_id, order_date, total_amount)
VALUES (102, 1, '2023-02-12', 50.00);

Question 14: How to get unique records from the table without using distinct keywords

SELECT product
FROM sales
GROUP BY product;

Question 15: Write a SQL query to print the FIRST_NAME and LAST_NAME from Student table into single column COMPLETE_NAME.

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS COMPLETE_NAME 
FROM Student;

Question 16: Employees Earning More Than Their Managers

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

Select name as Employee
from Employee E1
where salary > (
    select salary
    from Employee E2
    where E1.managerId = E2.id
);