Data Set:
INSERT INTO employee_sales VALUES
(1,'Amit','Sales',35000,'Delhi'),
(2,'Riya','HR',42000,'Mumbai'),
(3,'Rahul','IT',55000,'Pune'),
(4,'Neha','Sales',30000,'Delhi'),
(5,'Karan','IT',60000,'Bangalore'),
(6,'Simran','HR',38000,'Chandigarh'),
(7,'Ankit','Sales',45000,'Mumbai'),
(8,'Pooja','IT',52000,'Delhi'),
(9,'Rohit','Sales',28000,'Jaipur'),
(10,'Sneha','HR',40000,'Pune'),
(11,'Arjun','IT',70000,'Bangalore'),
(12,'Meena','Sales',32000,'Delhi'),
(13,'Sahil','HR',36000,'Jaipur'),
(14,'Nisha','IT',48000,'Mumbai'),
(15,'Vikas','Sales',50000,'Pune'),
(16,'Tina','HR',39000,'Delhi'),
(17,'Mohit','IT',65000,'Noida'),
(18,'Rashmi','Sales',34000,'Chandigarh'),
(19,'Yash','HR',41000,'Delhi'),
(20,'Priya','IT',53000,'Pune'),
(21,'Deepak','Sales',46000,'Mumbai'),
(22,'Komal','HR',37000,'Jaipur'),
(23,'Suresh','IT',58000,'Bangalore'),
(24,'Anu','Sales',29000,'Delhi'),
(25,'Nitin','HR',43000,'Noida'),
(26,'Kriti','IT',62000,'Mumbai'),
(27,'Ajay','Sales',36000,'Pune'),
(28,'Payal','HR',34000,'Delhi'),
(29,'Manoj','IT',75000,'Bangalore'),
(30,'Isha','Sales',41000,'Noida'),
(31,'Ravi','HR',39000,'Mumbai'),
(32,'Divya','IT',54000,'Delhi'),
(33,'Gaurav','Sales',33000,'Jaipur'),
(34,'Shweta','HR',45000,'Pune'),
(35,'Aakash','IT',68000,'Noida'),
(36,'Sonam','Sales',37000,'Delhi'),
(37,'Varun','HR',42000,'Mumbai'),
(38,'Sakshi','IT',51000,'Pune'),
(39,'Harsh','Sales',48000,'Bangalore'),
(40,'Rekha','HR',36000,'Delhi'),
(41,'Pankaj','IT',59000,'Mumbai'),
(42,'Monika','Sales',31000,'Jaipur'),
(43,'Naveen','HR',40000,'Noida'),
(44,'Alka','IT',47000,'Delhi'),
(45,'Sunil','Sales',52000,'Pune'),
(46,'Preeti','HR',38000,'Mumbai'),
(47,'Kunal','IT',66000,'Bangalore'),
(48,'Bhavya','Sales',44000,'Delhi'),
(49,'Ramesh','HR',41000,'Jaipur'),
(50,'Lavanya','IT',56000,'Noida');
QUESTIONS Duration : 1 hour
1 Create a database named company_db.
2 Use the database company_db.
3 Create the table employee_sales with suitable data types.
4 Insert all the given records into the table.
5 Display all records from the table.
6 Display employee name, department, and salary.
7 Display all employees working in the IT department.
8 Display employees whose salary is greater than 50,000.
9 Display employees who belong to the city Delhi.
10 Display employees whose salary is between 40,000 and 60,000.
11 Display employees who are not working in Mumbai.
12 Display employees working in Sales department and living in Delhi.
13 Display employees whose salary is greater than or equal to 45,000.
14 Update the salary of all Sales employees by adding 5,000.
15 Update city as Gurugram for employees working in Noida.
16 Change the department of employee Amit to Marketing.
17 Add a new column experience of type INT to the table.
18 Rename column emp_name to employee_name.
19 Modify the data type of salary to BIGINT.
20 Drop the column experience from the table.
21 Display total number of employees department-wise.
22 Display average salary of each department.
23 Display departments having more than 8 employees.
24 Display cities where average salary is more than 45,000.
25 Display employees ordered by salary in descending order.
26 Display employees ordered by city in ascending order.
27 Display top 5 highest paid employees.
28 Display lowest 5 paid employees.
29 Display employees from 6th to 10th highest salary using LIMIT and OFFSET.
30 Display department-wise highest salary.