SQL
1. Create Database office1
CREATE DATABASE office1;
2. Use the database office1
USE office1;
3. Create table employee
CREATE TABLE employee
(
Eno INTEGER PRIMARY KEY NOT NULL,
Ename VARCHAR(50) NOT NULL,
Salary INTEGER,
Area_code INTEGER,
Bonus DECIMAL,
Date_of_join DATE
);
4. Describe the table
DESC employee;
5. Create a table employee and insert values in it
INSERT INTO employee VALUES
(1, 'Raj Sinha', 30000, 36, 12.00, '2009/07/19');
INSERT INTO employee VALUES
(2, 'Udit Thakur', 50000, 48, 10.00, '2008/03/22');
INSERT INTO employee VALUES
(3, 'R.K. Sharma', 38000, 36, NULL, '2007/03/08');
INSERT INTO employee VALUES
(4, 'Neha Yadav', 80000, 60, 10.00, '2008/12/06');
INSERT INTO employee VALUES
(5, 'Ajay Garg', 20000, 36, 12.50, '2010/10/03');
INSERT INTO employee VALUES
(6, 'Rani Ranjan', 70000, 60, 12.50, '2008/05/05');
INSERT INTO employee VALUES
(7, 'Mohit Dua', 50000, 48, NULL, '2008/03/05');
6. Display the details of all the employees
SELECT * FROM employee;
7. Display the Eno and salary of all the employees who joined before 01-04-2009
SELECT Eno, Salary FROM employee
WHERE Date_of_join < '2009/04/01';
8. Display the employee Eno, name and salary of all the employees
SELECT Eno, Ename, Salary FROM employee;
9. Display the details of all the employees with salary less than 30000
SELECT Ename, Salary FROM employee
WHERE Salary < 30000;
10. Display the Eno, Ename of all the employees who joined after 01-04-2009
SELECT Eno, Ename FROM employee
WHERE Date_of_join > '2009/04/01';
11. Display the details of all the employees whose bonus is NULL
SELECT * FROM employee
WHERE Bonus IS NULL;
12. Display the details of all the employees whose bonus is NOT NULL
SELECT * FROM employee
WHERE Bonus IS NOT NULL;
13. Display the salary of employee from the table removing duplicate values
SELECT DISTINCT Salary FROM employee;
14. Display the area code of employee from the table employee. Area code should
appear only once
SELECT DISTINCT Area_code FROM employee;
15. Display the details of all the employees joined after 31-12-2008 for which the
area code is more than 36
SELECT * FROM employee
WHERE Date_of_join > '2008/12/31'
AND Area_code > 36;
16. Display the name and salary for all the employees which do not have area
code 36
SELECT Ename, Salary FROM employee
WHERE Area_code <> 36;
17. Display the name and salary for all the employees for which the salary is less
than 50000 or bonus is more than 12
SELECT Ename, Salary FROM employee
WHERE Salary < 50000 OR Bonus > 12;
18. Display the details of all the employees who joined in the year 2009
SELECT * FROM employee
WHERE YEAR(Date_of_join) = 2009;
19. Display the details of all the employees whose bonus is in the range 11 to 12
SELECT * FROM employee
WHERE Bonus BETWEEN 11 AND 12;
20. Display the details of all the employees whose salary is in the range 40000 to
50000
SELECT * FROM employee
WHERE Salary BETWEEN 40000 AND 50000;
21. Display the name and salary for all the employees from whom the bonus is
24, 36 or 48
SELECT Ename, Salary FROM employee
WHERE Bonus IN (24, 36, 48);
22. Display the Eno, name and salary for all employees for whom the name ends
with ‘Sharma’
SELECT Eno, Ename, Salary FROM employee
WHERE Ename LIKE '%Sharma';
23. Display the Eno, name and salary for all employees
SELECT Eno, Ename, Salary FROM employee;
24. Display the Eno, name and salary for all the employees for whom the name
ends with ‘a’
SELECT Eno, Ename, Salary FROM employee
WHERE Ename LIKE '%a';
25. Display the Eno, name and salary for all the employees for whom the name
contains ‘a’
SELECT Eno, Ename, Salary FROM employee
WHERE Ename LIKE '%a%';
26. Display the Eno, name and salary for all the employees whose name does not
contain ‘p’
SELECT Eno, Ename, Salary FROM employee
WHERE Ename NOT LIKE '%p%';
27. Display the Eno, name and salary for all the employees for whom the name
contains ‘a’ as the second test character
SELECT Eno, Ename, Salary FROM employee
WHERE Ename LIKE '_a%';
28. Display the details of all the employees in the ascending order of their salary
SELECT * FROM employee
ORDER BY Salary ASC;
29. Display the details of all the employees in the descending order of their join
date
SELECT * FROM employee
ORDER BY Date_of_join DESC;
30. Display the details of all the employees in the ascending order of their salary
and within the descending order of their join date
SELECT * FROM employee
ORDER BY Salary ASC, Date_of_join DESC;
31. Assign the bonus 15.50 from all employee for which bonus is NULL
UPDATE employee
SET Bonus = 15.50
WHERE Bonus IS NULL;
32. Increase the bonus by 0.5 for all the employees for which the salary is more
than 40000
UPDATE employee
SET Bonus = Bonus + 0.5
WHERE Salary > 40000;
33. For every employee replace bonus with (Salary * Bonus) / (12 * 100)
UPDATE employee
SET Bonus = (Salary * Bonus) / (12 * 100);
34. Delete the records employee ‘Raj Sinha’
DELETE FROM employee
WHERE Ename = 'Raj Sinha';
35. Add another column category of type CHAR(15) in the employee table
ALTER TABLE employee
ADD Category CHAR(15);