Notes

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.
17. Create a view amed v_EMP on the table EMP,DEPT by selecting the followingfields
Emp ID, Dept ID, Emp Name, Job
Where the EMP.Dept ID = DEPT. DeptId.
18. Create a synonym S_EMP on the table EMP.
Basic features, Block Structure of a PL/SQL Programs, Control Structures, Exception
Handling, Cursor, Procedure, function and Triggers, Internet features of
Oracle, Overviews of SQLJ
19. Write a PL/SQL program for:
a. Printing the Fibonacci series from 1 to 50.
b. Printing the smallest number among any three numbers.
c. Printing the table of any specific number entered.
20. Create a trigger named “Client_Master” which keeps track of records deleted or
updated when such operations are carried out. Records in this table are
inserted into table “Audit” when database trigger fires due to an update or delete
statement fired on this table “Client”.
Table: Client
Column name Data type Size
Client_no Varchar2 6
Name Varchar2 20
Address Varchar2 30
Balance_Due Number 10,2
Write a sql query to drop the table EMP, can we drop a table with data in it.