Notes

3) Create table EMP and DEPT with the below mentioned structure
Structure for EMP table
EmpID NUMBER(4)
DeptID VARCHAR2(10)
EmpName CHAR(10)
Job CHAR(10)
HireDate DATE
Salary NUMBER(7, 2)
Commission NUMBER (7, 2)
Structure for DEPT table
DeptId VARCHAR2(10)
Deptname VARCHAR2(20)
No_of_Faculties NUMBER(2)
In table EMP : EmpID should be the Primary Key and DeptID should be the foreign
key.
In table DEPT : DeptId should be the primary key.
4) INSERT the following values in the EMP table:
a) 1001,SET_01,Harrey,SE,01-Jan-2009,15000,3
b) 1002,SET_02, Ron, SSE,15-Feb-1998,20000,4
c) 1003,SEM_05, Peter, Manager,15-April-1999,40000,5
d) 1002,SED_07, Jolie,Assistant Manager,15-Dec-1998,50000,5
e) 1008,SET_08, Santy, SSE,15-Feb-2000,20000,4
f) 1008,SED_10, San, SE,10-Feb-2009,22000,5
5) Considering the above table i.e EMP write the queries for the following:
a) Find out the number of employees having “manager” as job.
b) Display only the jobs with maximum salary greater than or equal to 3000
c) Find all those employees whose job does not start with ‘M’.
d) Find the names of the employees whose name starts with ‘S’.
e) Find the names of the employees who are Managers and their date of joining is after
“02-Jan-2006”.
f) For describing the structure of the EMP table and DEPT table.
g) For getting the average salary of the employees from EMP table.
h) For displaying the current date and give the column a name “DATE”.
i) For converting the name of the employee into uppercase where the employee name is
“Santy”
j) Create a sequence with name SEQ_EMP , which will generate numbers from 1 to 99
in ascending order with an interval of 1. The sequence must
start from 1 after generating the number 99.
k) Displaying the names of the employees who have an a and an e in their names.
6. Considering the table DEPT in question 3, find the total number of departments.
7. Alter the EMP table for the changing the width of the field EmpID from 4 to 10.
8. .Alter the DEPT table for changing the width of the field No_of_Faculties from 2 to
4.
9. Delete all the records from the EMP table where the EmpName starts with “S”,
10. Insert some values in the PRODUCTS table created in Question 1 and then DROP
the table PRODUCTS.
11. Update the EMP table for the following values:
a. Increase the salary of all the employees by 10% where the job is SE and
SSE.
b. Change the hiredate of the employee “Harry” to 01-Feb-2009.
c. Update the salary of the employees to an increase of 15% where deptid is SED_07.
12. Alter the table EMP for the following:
a. Add one more field in the table i.e DOB DATE
b. Drop the column named Commission from the EMP table.
13. Write a query to select all the records from the EMP table.
14. Write a query to select all the records from the DEPT table.
15. Write a query to select the distinct deptid from EMP table.
16. Write a query to find the name and salary of the employee from EMP table where
the salary is maximum.