RDBMS LAB

Write the commands to perform the following tasks.

1) Find out the names of all the clerks who get a salary more than 2000.
2) Find out the names of the employees whose hire dates are same and they belong to the same dept.
3) Change the PHno of Anand to 4564136523 and salary to 17500.
4) Change the name of the “EMPLOYEE” table to “emp”.
5) Change the size of dept column in emp to varchar (7).
LAB-3

3. Find out the output of select statement using the following parameters on the “emp” table created in Q.2.
i) Order by
ii) Group by
iii) Any
iv) All
v) Exit
vi) Between
vii) ln
viii) Where
4. To create a view VW dept on the “emp” and “dept” table of Q2 for the marketing department and give an 1000 increment to all the employee of the marketing Department
LAB-4

5. Create a sequence ESIno with the following specification minimum value1, maximum value 20, increment by 2, start with 0, with circle and cache 10.
Alter the sequence such that the maximum values is only 15
6. Create a sequence inv_seq which will generate the numbers from 1 to 9999in ascending order.
7. Use the concept of join

i) Find the dept name in which Anand belongs’
ii) What are the salaries of the employees who belong to sales dept.?
iii) Find out the hire date of the manager of the marketing dept.

LAB-5

8. Create a type named personal whose structure is given below.

COLUMN NAME
DATATYPE
Name
Varchar2(20)
Age
Number(3)
Sex
Char(1)

Create a table whose structure is as shown below:

COLUMN NAME
DATATYPE
Id
Number(4)
Center
Varchar2(15)
Per
Personal

Insert into the table the values

3301, Chennai (Vinod, 22, m)
3302, Chennai, (Meena, 22, f)
3302, Bangalore, (Reena, 24, f)

9. Write a PL/SQL block to satisfy the following conditions accepting the empno as the user input
i) If the sal is less than 10000 then give an increment of 1000 to all employees.
ii) If the sal is greater than 10000 then display a message

LAB-6

10. Write a PL/SQL code block to calculate area of a circle for a value of radius varying from 2 to 5. Store the radius and the corresponding value of the calculated area in a table.
11. Write a PL/SQL block to Display the reverse of numbers between 1 and 100.

12. Write a PL/SQL block to satisfy the following conditions accepting deptno as user input. If the “deptno” is D1 then raise an exception to display the corresponding “deptname”.




LAB-7
13. Write a PL/SQL code block that determines the top three highest paid employees from “emp” table. Use appropriate cursor attribute for the name. Display the name and the salaries of the employees.
14. A HRD manager has decided to raise the salary for all the employees in “deptnoD2” by 0.05. When ever any search raise is given to the employees and audit trail of the same is maintained in the “emp_raise” table holds the “empno”, “date” when the raise was given and the raise amount. Write a PL/SQL block to update the salary of each employee of the “deptnoD2” and insert a record in the “emp_raise” table as well.
15. Write a PL/SQL code block that will select all rows from the “emp” table. The block should print the no. of rows returned, the values of the “empno” column and corresponding name.
LAB-8

16. Write a procedure called “proc_update” and store it in the database which is called in a PL/SQL block. When a new employee record is inserted in the “employee” table, to update the numemp in the dept table.
17. Write a function which will accept the empno and return the deptname as output.
18. Write a package containing the following
i) A procedure, which will accept the “empno” as the input and delete that particular record.
ii) A function, which will accept the “ename” as the input and give the “dob” as output.
LAB-9

19. Write a database trigger before delete for each row not allowing deletion and display a message “U r not authorized to delete”.
20. Write a database trigger before insertion to check whether the “deptno” is D1/D2/D3/D4 or not. Display appropriate message otherwise.