Page 1 :
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 RajSinha 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 AjayGarg 200000 36 12.50 03-01-2010, 6 RaviRanjan 700000 60 12.50 05-06-2008, 7 MohitDua 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

Page 2 :
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

Page 3 :
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;, , select * from employee;, , cw ee tipi’ lol ]o [213] 2009-87-19, Nees ys ta [51] = 2088-83-22, aes esol) 3] 2687-63-88, feels) 0) { 2008-12-06 |, era 51) H ‘ bie 8, gota ls]o) 3) y. PAs) teste, [i] aa, , blo toTTs}, , Tbe ry tt, , ry. k. sharma }, eet et, REM ts, ravi ranjan, mohit dua, , { 2016-61-83 |, —+, , i row in set (8.008, , , , 7. select ENO, SALARY from employee where DATE_OF_JOIN <”01-04-2009”;

Page 5 :
t distinct AREACODE from EMPLOYEE;, , 3 rows in set (@.08 sec>, , , , 13. select * from EMPLOYEE where doj > 2008-12-31” AND AREACODE>36;, , lect * from EMPLOYEE where doj>"2@@8-12-31" AND AREACODE>36;, VLD GL To, , , , 14. select ENAME, SALARY from EMPLOYEE WHERE AREACODE!=36;, , udit thakur pa] [15, neha yadav pro) la [i] 3], 7 760000, , , , 15. select ENAME, SALARY from EMPLOYEE WHERE SALARY<500000 AND, BONUS>1 2;, , 1> select ENAME, SALARY from EMPLOYEE WHERE SALARY<56G@@0 AND BONUS>12;, , ajay garg | 260008, , i row in set (@.0@ sec>, , , , 16. select* from EMPLOYEE WHERE DATE_OF_JOIN BETWEEN “01-01-2009”, AND “31-12-2009”;, , "2089-12-31";, , Ri) s)3/)), , i vow in set, , , , 17. select * from EMPLOYEE WHERE SALARY BETWEEN 400000 AND, 500000;, , STDP Dis ame) UMC ASL, MS) od S159), , bys 1o [213] : 2668-@3-22, bed 15 )5) 514) 2668-63-85 {, , +, , SES 3 Get ed, , , , 18. select*from EMPLOYEE WHERE BONUS BETWEEN 11 AND 12;