Scenario
A company maintains employee information in a table named Employees. You are required to
retrieve and filter employee information using SQL SELECT queries.
Step 1: Create Database
CREATE DATABASE CompanyDB;
USE CompanyDB;
Step 2: Create Table
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Employee_Name VARCHAR(50) NOT NULL,
Gender VARCHAR(10) NOT NULL,
Department VARCHAR(30) NOT NULL,
Job_Role VARCHAR(50) NOT NULL,
Salary DECIMAL(10,2) NOT NULL,
City VARCHAR(30) NOT NULL,
Experience INT,
Email VARCHAR(100) UNIQUE
);
Step 3: Insert Records
INSERT INTO Employees
(Employee_ID, Employee_Name, Gender, Department, Job_Role, Salary, City, Experience, Email)
VALUES
(101, 'Arun Kumar', 'Male', 'IT', 'Data Analyst', 65000, 'Chennai', 2, 'arun@[Link]'),
(102, 'Priya Sharma', 'Female', 'IT', 'Software Developer', 72000, 'Mumbai', 3, 'priya@[Link]'),
(103, 'Rahul Mehta', 'Male', 'Finance', 'Financial Analyst', 58000, 'Delhi', 2, 'rahul@[Link]'),
(104, 'Sneha Rao', 'Female', 'HR', 'HR Executive', 50000, 'Chennai', 4, 'sneha@[Link]'),
(105, 'Karan Shah', 'Male', 'IT', 'Data Scientist', 95000, 'Pune', 5, 'karan@[Link]'),
(106, 'Divya Iyer', 'Female', 'Finance', 'Accountant', 55000, 'Mumbai', 3, 'divya@[Link]'),
(107, 'Vivek Nair', 'Male', 'IT', 'Software Developer', 68000, 'Delhi', 2, 'vivek@[Link]'),
(108, 'Anjali Das', 'Female', 'Marketing', 'Marketing Analyst', 62000, 'Pune', 4,
'anjali@[Link]'),
(109, 'Rohan Patel', 'Male', 'Finance', 'Financial Analyst', 60000, 'Chennai', 3, 'rohan@[Link]'),
(110, 'Meena Krishnan', 'Female', 'IT', 'Data Analyst', 70000, 'Mumbai', 5, 'meena@[Link]'),
(111, 'Sanjay Rao', 'Male', 'Marketing', 'Marketing Executive', 48000, 'Delhi', 1,
'sanjay@[Link]'),
(112, 'Neha Kapoor', 'Female', 'HR', 'HR Manager', 85000, 'Pune', 8, 'neha@[Link]'),
(113, 'Vikram Singh', 'Male', 'IT', 'Data Scientist', 105000, 'Chennai', 7, 'vikram@[Link]'),
(114, 'Pooja Menon', 'Female', 'Finance', 'Accountant', 52000, 'Delhi', 2, 'pooja@[Link]'),
(115, 'Aditya Joshi', 'Male', 'Marketing', 'Marketing Analyst', 67000, 'Mumbai', 5,
'aditya@[Link]');
Exercise Questions
Section A – Basic SELECT
1.
Display all the records from the Employees table.
2.
Display only the following details:
Employee Name
Department
Job Role
Salary
3.
Display the employee name, city, and experience of all employees.
4.
Display the employee name and salary, and give the salary column the temporary heading
Annual_Salary.
5.
Display the employee name and calculate a new column called Salary_After_Bonus, assuming every
employee receives a 10% bonus.
Do not modify the actual Salary column in the table.
Section B – DISTINCT
6.
Display the unique departments available in the company.
7.
Display the unique cities where employees are located.
8.
Display the unique combinations of department and city.
Section C – WHERE and Comparison Operators
9.
Display all employees who work in the IT department.
10.
Display employees whose salary is greater than 70000.
11.
Display employees whose salary is less than or equal to 60000.
12.
Display employees who have more than 3 years of experience.
13.
Display employees who have exactly 5 years of experience.
14.
Display employees who are not working in the IT department.
Section D – AND and OR
15.
Display employees who work in the IT department and have a salary greater than 70000.
16.
Display employees who work in the Finance department and have more than 2 years of experience.
17.
Display employees who are located in Mumbai and have a salary greater than 65000.
18.
Display employees who work either in the IT department or the Finance department.
19.
Display employees who are located either in Chennai or Pune.
20.
Display employees who work in the IT department and are located in either Chennai or Mumbai.
Hint: You will need to use AND, OR, and parentheses.
Section E – IN and NOT IN
21.
Display employees who belong to any of the following departments:
IT
Finance
HR
22.
Display employees who are located in any of the following cities:
Chennai
Mumbai
Delhi
23.
Display employees who do not work in the IT or Finance departments.
24.
Display employees whose job role is either Data Analyst or Data Scientist.
Section F – BETWEEN
25.
Display employees whose salary is between 50000 and 70000.
26.
Display employees who have between 2 and 5 years of experience.
27.
Display employees whose salary is not between 60000 and 90000.
Section G – LIKE and Wildcards
28.
Display employees whose names start with the letter A.
29.
Display employees whose names start with the letter P.
30.
Display employees whose names end with the letter a.
31.
Display employees whose names contain the letters an.
32.
Display employees whose job role contains the word Analyst.
33.
Display employees whose job role starts with the word Data.
34.
Display employees whose second character in the name is r.
35.
Display employees whose city name contains the letter i.
Section H – ORDER BY
36.
Display all employees in ascending order of salary.
37.
Display all employees in descending order of salary.
38.
Display all employees in alphabetical order of employee name.
39.
Display all employees in descending order of experience.
40.
Display employees in ascending order of department and, within each department, display
employees in descending order of salary.
41.
Display employees in descending order of salary. If two employees have the same salary, sort them
alphabetically by employee name.
Section I – LIMIT
42.
Display the first 5 employees from the table.
43.
Display the 5 highest-paid employees.
44.
Display the 3 employees with the highest experience.
45.
Display the 5 lowest-paid employees.
Section J –
46.
Display the names, job roles, and salaries of employees who:
Work in the IT department
Have a salary greater than 70000
Sort the result by salary in descending order
47.
Display the names and salaries of employees who:
Are located in Mumbai or Chennai
Have a salary between 60000 and 90000
Sort the result by salary from highest to lowest
48.
Display the names, departments, and job roles of employees who:
Do not work in the HR department
Have more than 2 years of experience
Sort the result alphabetically by department
49.
Display the details of employees who:
Work in IT or Finance
Have a salary greater than 60000
Sort the result by department in ascending order
Within each department, sort salary in descending order
50.
Display the top 3 highest-paid employees who work in the IT department.
51.
Display the top 3 employees with the highest experience who are located in Chennai or Mumbai.
52.
Display employees whose:
Name starts with A, P, or S
Salary is greater than 50000
Sort the result by employee name
Hint: Think about how you can combine LIKE, OR, and AND.
53.
Display employees who:
Work in IT, Finance, or Marketing
Have between 2 and 5 years of experience
Are not located in Delhi
Sort the result by experience in descending order
54.
Display the names, job roles, and salaries of the 3 highest-paid Data-related employees.
Hint: Use LIKE, ORDER BY, and LIMIT.
55.
Display the names, departments, job roles, salaries, and cities of employees who satisfy all of the
following:
They work in either IT or Finance.
Their salary is between 60000 and 100000.
They have at least 3 years of experience.
They are not located in Delhi.
The result should be displayed with the highest salary first.
Display only the top 5 records.