1. Write a query to select all employees ordered by last_name in ascending order. 2. Write a query to find the 3rd highest salary without using LIMIT or TOP. 3. Write a query to find duplicate email IDs in the Employee table.
Write a query to select all columns from the Employee table.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT * FROM Employee;
Key Points:
Write a query to get first_name and last_name from the Employee table.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT first_name, last_name
FROM Employee;
Key Points:
Write a query to get employees whose salary is greater than 50000.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT *
FROM Employee
WHERE salary > 50000;
Key Points:
Write a query to select all employees ordered by last_name in ascending order.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT *
FROM Employee
ORDER BY last_name ASC;
-- You can also omit ASC as it's default
SELECT *
FROM Employee
ORDER BY last_name; 
Key Points:
Write a query to count the number of employees in the Employee table.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT COUNT(*) AS total_employees
FROM Employee;
-- Count non-null values in specific column
SELECT COUNT(salary) AS employees_with_salary
FROM Employee;
-- Count distinct values
SELECT COUNT(DISTINCT city) AS unique_cities
FROM Employee;
Key Points:
Write a query to find the total salary paid per department.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT department_id, SUM(salary) AS total_salary
FROM Employee
GROUP BY department_id;
-- With department name if joined with Department table
SELECT d.department_name, SUM(e.salary) AS total_salary
FROM Employee e
JOIN Department d ON e.department_id = d.id
GROUP BY d.department_name;
Key Points:
Write a query to list all distinct cities where employees live.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT DISTINCT city
FROM Employee;
-- DISTINCT on multiple columns
SELECT DISTINCT city, department_id
FROM Employee;
-- Count distinct cities
SELECT COUNT(DISTINCT city) AS unique_cities
FROM Employee; 
Key Points:
Given tables Employee and Department, write a query to get all employee names along with their department name.
Table Structures:
Employee(id, first_name, last_name, salary, department_id, city)
Department(id, department_name, location)-- INNER JOIN (default)
SELECT e.first_name, e.last_name, d.department_name
FROM Employee e
INNER JOIN Department d ON e.department_id = d.id;
-- LEFT JOIN (includes all employees even without department)
SELECT e.first_name, e.last_name, d.department_name
FROM Employee e
LEFT JOIN Department d ON e.department_id = d.id;
Key Points:
Write a query to find all employees whose name starts with 'A'.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)SELECT *
FROM Employee
WHERE first_name LIKE 'A%';
Key Points:
Write a query to get the top 5 highest-paid employees.
Table Structure:
Employee(id, first_name, last_name, salary, department_id, city)-- MySQL, PostgreSQL, SQLite
SELECT *
FROM Employee
ORDER BY salary DESC
LIMIT 5;
-- SQL Server
SELECT TOP 5 *
FROM Employee
ORDER BY salary DESC;
-- Oracle
SELECT *
FROM Employee
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
-- With offset (skip first 5, get next 5)
SELECT *
FROM Employee
ORDER BY salary DESC
LIMIT 5 OFFSET 5;
Key Points: