MySQL - ASSIGNMENT
Consider a table EMPLOYEE with the following data and answer the SQL
queries:
ENO
ENAME SALARY AREA
BONUS DATE OF JOIN
CODE (%)
1
Raj Sinha
300000
36 12.00 19-07-2009
2
Udit Thakur
500000 48
10.00 22-03-2008
3
R. K. Sharma
300000 36 NULL
08-03-2007
4
Neha Yadav
800000 60
10.00 06-12-2008
5
Ajay Garg
200000 36
12.50 03-01-2010
6
Ravi Ranjan
700000 60
12.50 05-06-2008
7
Mohit Dua
500000 48 NULL 05-03-2008
Create Database and use it
1. Create a database OFFICE.
2. Use the database OFFICE.
Create Table / Insert Into
3. Create a table EMPLOYEE and insert tuples in it.
Simple Select
4. Display the details of all the EMPLOYEES.
5. Display the employee no, Name, and salary of all the employees.
Conditional Select using Where Clause
6. Display the details of all the employees with salary less than 300000.
7. Display the eno and salary of all the employees who joined before
01-04-2009.
8. Display the eno, ename of all the employees who joined after 01-04-2009.
Using NULL
9. Display the details of all the employees whose bonus is NULL.
10. Display the details of all the employees whose bonus is not NULL.
Using DISTINCT Clause
11. Display the salary of employees from the table removing duplicate
values.
12. Display the area code of employees from the table Employees. An area
code should appear only once.
Using Logical Operators (NOT, AND, OR)
13. Display the details of all the employees joined after 31-12-2008 for
which the area code is more than 36.
14. Display the Name and salary for all the employees which do not have area
code 36.
15. Display the Name and salary for all the employees for which the salary
is less than 500000 or bonus is more than 12.
16. Display the details of all the employees who joined in the year 2009.
17. Display the details of all the employees whose salary is in the range
400000 to 500000.
18. Display the details of all the employees whose bonus is in the range 11%
to 12%.
Using IN Operator
19. Display the Name and salary for all the employees for whom the bonus is
24, 36, or 48. (Using IN operator)
Using BETWEEN Operator
20. Display the details of all the employees whose salary is in the range
400000 to 500000. (Using BETWEEN operator)
21. Display the details of all the employees whose bonus is in the range 11%
to 12%. (Using BETWEEN operator)
Using LIKE Operator
22. Display the Eno, Name, and salary for all the employees for whom the
Name ends with 'Sharma'.
23. Display the Eno, Name, and salary for all the employees for whom the
Name ends with 'a'.
24. Display the Eno, Name, and Salary for all the employees for whom the
Name contains 'a'.
25. Display the Eno, Name, and Salary for all the employees for whom the
Name does not contain 'P'.
26. Display the Eno, Name, and Salary for all the employees for whom the Name
contains 'a' as the second last character.
Using ORDER BY clause
27. Display the details of all the employees in the ascending order of their
salary.
28. Display the details of all the employees in the descending order of
their join date.
29. Display the details of all the employees in the ascending order of their
salary and within salary in the descending order of their join Date.
Using UPDATE, DELETE, ALTER TABLE
30. Assign the bonus 15.50% for all the employees for which bonus is NULL.
31. Increase the bonus by 0.5% for all the employees for which the salary is
more than 400000.
32. For every employees replace bonus with (salary*bonus)/ 12*100.
33. Delete the records of employee, 'N.P. Jain'
34. Add another column Category of type CHAR(5) in the Employees table.
SOLUTIONS: MYSQL
ASSIGNMENT
1. Create database OFFICE;
2. Use OFFICE;
3. Create table EMPLOYEE (ENO integer(5),ENAME varchar(50), SALARY
integer(10),AREA CODE integer(5),BONUS decimal(4,2) DATE_OF_JOIN date); Insert
into EMPLOYEE values(1120, “Mahesh”, 54000,12,45.5,”01-12-2010”)
4. select * from EMPLOYEE;
5. select ENO, ENAME, SALARY from EMPLOYEE;
6. select * from EMPLOYEE where salary<300000;
7. select ENO, SALARY where DATE_OF_JOIN <”01-04-2009”;
8. select ENO, NAME where DATE_OF_JOIN >”01-04-2009”;
9. select * from EMPLOYEE where BONUS IS NULL;
10. select * from EMPLOYEE where BONUS IS NOT NULL;
11. select distinct SALARY from EMPLOYEE;
12. select distinct AREA CODE from EMPLOYEE;
13. select * from EMPLOYEE where DATE_OF_JOIN>”31-12-2008” AND AREA
CODE>36;
14. select ENAME, SALARY from EMPLOYEE WHERE AREA CODE!=36;
15. select ENAME, SALARY from EMPLOYEE WHERE SALARY<500000 AND
BONUS>12;
16. select* from EMPLOYEE WHERE DATE_OF_JOIN BETWEEN “01-01-2009” AND
“31-12-2009”;
17. select * from EMPLOYEE WHERE SALARY BETWEEN 400000 AND 500000;
18. select*from EMPLOYEE WHERE BONUS BETWEEN 11 AND 12;
19. select*from EMPLOYEE where BONUS >= 11 and BONUS<=12;
20. select ENAME, SALARY from EMPLOYEE where BONUS in (24,36,48) ;
21. select * from EMPLOYEE where SALARY between 400000 and 500000;
22. select *from EMPLOYEE where BONUS between 11 and 12;
23. select ENO, ENAME, SALARY from EMPLOYEE where ENAME like “%Sharma”;
24. select ENO, ENAME, SALARY from EMPLOYEE where ENAME like ‘%a’ ;
25. select ENO, ENAME, SALARY from EMPLOYEE where ENAME like ‘%a%’ ;
26. select ENO, ENAME, SALARY from EMPLOYEE where ENAME not like ‘%P%’ ;
27. select ENO, ENAME, SALARY from EMPLOYEE where ENAME like ‘%a_’ ;
28. select * from EMPLOYEE order by SALARY asc;
29. select * from EMPLOYEE order by DATE OF JOIN desc;
30. select * from EMPLOYEE order by SALARY asc, DATE OF JOIN desc;
31. update EMPLOYEE set BONUS=15.50 where BONUS IS NULL;
32. update EMPLOYEE set BONUS=BONUS+(0.5/100)*BONUS, where SALARY>400000;
33. update EMPLOYEE, set BONUS=(SALARY*BONUS)/12*100;
34. delete from EMPLOYEE where ENAME=’N. P. JAIN’;
35. alter table EMPLOYEE add CATEGORY char(5);
Comments
Post a Comment