Relational Database Management System (RDBMS) LAB - GROUP B

15. Create a table master_book to contain the information of magazine_code,
magazine_name and publisher, magazine_type (Weekly/biweekly/monthly) and price. Write
a PL/SQL block to perform insert, update and delete operations on the above table
16. Create a table to contain phone_number, user_name, address of the phone user.
Write a function to search for an address using phone numbers.
17. Create a table to store the salary details of the employees in a company. Declare the
cursor to contain employee_number, employee_name and net_salary. Use cursor to update
the employee salaries.
18. Create a table to contain the information about the voters in a particular constituency.
Write a proper trigger to update or delete a row in the table.
19. Create a table employee to contain the information of employee_name,
employee_number and salary.
(a) Write a procedure to increase 10% of salary to all employees (procedure without
argument).
(b) Write a procedure to increase specific percentage for specific department number
(procedure with argument). 10. Create a table sales_order with s_order_no, client_number, delivery_address,
delivery_date and order_status. Define the s_order_no as primary key using column level
Constraints.
(a) Create another table named as sales_order_copy with the same structure of
sales_order table. Define the s_order_no as primary key using table level constraints.
(b) Add a new column for storing salesman_number in sales_order using ALTER
Command.
(c) Modify the size of delivery_address in sales_order table using ALTER command.
(d) Display the structure of sales_order table
11. Create an Employee table with the following attribute employee_number,
employee_name, job_type, hire_date, department_number, salary and commission.
(a) Query to display the Highest, Lowest, Sum and Average Salaries of all the
Employees
(b) Query to display the employee_number and employee_name for all employees who
earn more than the average salary.
(c) Query to display the employee_name, salary and commission for all the employees
who earn commission.
(d) Sort the data in descending order of salary and commission
(e) Query to display employee_name, salary and commission for all employees whose
commission is greater than their salary increased by 5%.
12. Create a DEPARTMENT table with the attributes of department_number and
department_name. Set the department_ number as a primary key.
(a) Insert few records
(b) Display all the records
(c) Create an employee table with the following attribute employee_number,
employee_name, job and department_number. Set the employee_number as a primary
key and set the department_number as a foreign key.
(d) Query to display the employee details who are working in the particular
department_number.
(e) Query to display employee_number, employee_name and job from the employee
table
(f) Query to display unique jobs from the employee Table
(g) Query to display the employee_name concatenated by a job separated by a comma

1. R.Mathan - Bsc.CS (2022-2025)
AlagappaGovernment Arts College, Karaikudi.
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]

2. Ex. No: 10. Create a table sales_order with s_order_no, client_number, delivery_address,
delivery_date and order_status. Define the s_order_no as primary key using column level Constraints.

(a) Create another table named as sales_order_copy with the same structure of
sales_order table. Define the s_order_no as primary key using table level constraints.
(b) Add a new column for storing salesman_number in sales_order using ALTER
Command.
(c) Modify the size of delivery_address in sales_order table using ALTER command.
(d) Display the structure of sales_order table.

3. Create a table sales_order with s_order_no, client_number, delivery_address,
delivery_date and order_status. Define the s_order_no as primary key using column
level Constraints.
SQL> create table sales_order (
2 s_order_no number(10) primary key,
3 client_no number(10),
4 delivery_address char(100),
5 delivery_date date,
6 order_status char(30) );
Table created.

4. Insert a record:
SQL> insert into sales_order values(1001,2238,'sriram,No1,12th main road,kk nagar,chennai-42','12-aug-
24','order has been shipped');
1 row created.
SQL> insert into sales_order values(1002,2267,'prathap,No.8,Bazaar lane,mylapore,chennai-04','05-aug-
24','out for delivery');
1 row created.
SQL> insert into sales_order values(1003,2277,'Mathan,5/29,jayanthi nagar,rangiem,pudukkottai-
622409','08-aug-24','order has been shipped');
1 row created.
SQL> insert into sales_order values(1004,2290,'salini,5/345,mgr nagar,pudukkottai-622001','01-sep-
24','your order has been placed');
1 row created.

5. SQL> insert into sales_order values(1005,22001,'yazhini,No.10,william Rd,cantoment,Trichy-
620003','03-aug-24','Delivered');
1 row created.
Verification for Primary Key: (Duplication Not allowed)
SQL> insert into sales_order values(1004,2288,'mahi,No.5,srirangam,Trichy-620006','01-sep-24','your
order has been placed');
insert into sales_order values(1004,2288,'mahi,No.5,srirangam,Trichy-620006','01-sep-24','your order
has been placed')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004105) violated

6. Display the records:
SQL> set linesize 180
SQL> select * from sales_order;
S_ORDER_NO CLIENT_NO DELIVERY_ADDRESS DELIVERY_ ORDER_STATUS
---------- -------- ------------------------------------------------------------------------------------ --------- ------------------------------
1001 2238 sriram,No1,12th main road,kk nagar,chennai-42 12-AUG-24 order has been shipped
1002 2267 prathap,No.8,Bazaar lane,mylapore,chennai-04 05-AUG-24 out for delivery
1003 2277 Mathan,5/29,jayanthi nagar,rangiem,pudukkottai-622409 08-AUG-24 order has been shipped
1004 2290 salini,5/345,mgr nagar,pudukkottai-622001 01-SEP-24 your order has been placed
1005 22001 yazhini,No.10,william Rd,cantoment,Trichy-620003 03-AUG-24 Delivered

7. (a) Create another table named as sales_order_copy with the same structure of
sales_order table. Define the s_order_no as primary key using table level constraints.
SQL> create table sales_order_copy (
2 s_order_no number(10),
3 client_no number(10),
4 delivery_address char(100),
5 delivery_date date,
6 order_status char(30),
7 primary key(s_order_no) );
Table created.

8. Display the structure of the sales_order_copy table:
SQL> describe sales_order_copy;
Name Null? Type
--------------------------- --------------------------------------------- ----------------
S_ORDER_NO NOT NULL NUMBER(10)
CLIENT_NO NUMBER(10)
DELIVERY_ADDRESS CHAR(100)
DELIVERY_DATE DATE
ORDER_STATUS CHAR(30)

9. Insert a few records:
SQL> insert into sales_order_copy values(1001,22001,'yazhini,No.10,william Rd,cantoment,Trichy-
620003','03-aug-24','Delivered');
1 row created.
SQL> insert into sales_order_copy values(1002,2290,'salini,5/345,mgr nagar,pudukkottai-622001','01-sep-
24','your order has been placed');
1 row created.
Verification for Primary Key: (Duplication Not allowed)
SQL> insert into sales_order_copy values(1002,2288,'mahi,No.5,srirangam,Trichy-620006','01-sep-
24','your order has been placed');
insert into sales_order_copy values(1002,2288,'mahi,No.5,srirangam,Trichy-620006','01-sep-24','your
order has been placed')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004106) violated

10. (b) Add a new column for storing salesman_number in sales_order using ALTER Command.
Before Alter Command:
SQL> describe sales_order;
Name Null? Type
-------------------------------------------------- --------------- ----------
S_ORDER_NO NOT NULL NUMBER(10)
CLIENT_NO NUMBER(10)
DELIVERY_ADDRESS CHAR(100)
DELIVERY_DATE DATE
ORDER_STATUS CHAR(30)
SQL> alter table sales_order add salesman_number number(10);
Table altered.

11. After Alter Command:
SQL> describe sales_order;
Name Null? Type
------------------------------------------- -------------- -----------
S_ORDER_NO NOT NULL NUMBER(10)
CLIENT_NO NUMBER(10)
DELIVERY_ADDRESS CHAR(100)
DELIVERY_DATE DATE
ORDER_STATUS CHAR(30)
SALESMAN_NUMBER NUMBER(10)

12. (c) Modify the size of delivery_address in sales_order table using ALTER command.
SQL> alter table sales_order modify delivery_address char(200);
Table altered.
SQL> describe sales_order;
Name Null? Type
--------------------------------------------- -------------------- ----------------
S_ORDER_NO NOT NULL NUMBER(10)
CLIENT_NO NUMBER(10)
DELIVERY_ADDRESS CHAR(200)
DELIVERY_DATE DATE
ORDER_STATUS CHAR(30)
SALESMAN_NUMBER NUMBER(10)

13. (d) Display the structure of sales_order table.
SQL> describe sales_order;
Name Null? Type
--------------------------------------------- -------------------- ----------------
S_ORDER_NO NOT NULL NUMBER(10)
CLIENT_NO NUMBER(10)
DELIVERY_ADDRESS CHAR(200)
DELIVERY_DATE DATE
ORDER_STATUS CHAR(30)
SALESMAN_NUMBER NUMBER(10)
SQL> spool off

14. Ex. No: 11. Create an Employee table with the following attribute employee_number,
employee_name, job_type, hire_date, department_number, salary and commission.
(a)Query to display the Highest, Lowest, Sum and Average Salaries of all the
Employees
(b)Query to display the employee_number and employee_name for all employees who
earn more than the average salary.
(c)Query to display the employee_name, salary and commission for all the employees
who earn commission.
(d)Sort the data in descending order of salary and commission
(e)Query to display employee_name, salary and commission for all employees whose
commission is greater than their salary increased by 5%.

15. Create an Employee table with the following attribute employee_number,
employee_name, job_type, hire_date, department_number, salary and commission.
SQL> create table employee2 (
2 employee_number number(10),
3 employee_name char(10),
4 job_type char(10),
5 hire_date date,
6 department_number number(5),
7 salary number(7,2),
8 commission number(8,2) );
Table created.

16. Insert a few records:
SQL> insert into employee2 values(1001,'mathan','MGR','30-jul-2022',128,75000.00,
60000.00);
1 row created.
SQL> insert into employee2 values(1002,'raj','designer','22-may-2021',184,65000.00,
50000.00);
1 row created.
SQL> insert into employee2 values(1003,'charu','developer','10-oct-2019',134,60000.00,
NULL);
1 row created.

17. SQL> insert into employee2 values(1004,'mahi','sales','18-aug-2020',224,70000.00,
60000.00);
1 row created.
SQL> insert into employee2 values(1005,'aadhavan','technicion','20-may-2024',254,55000.00,
65000.00);
1 row created.
SQL> insert into employee2 values(1006,'sri','developer','01-jan-2018',321,58000.00,
NULL);
1 row created.

18. Display the records:
SQL> select * from employee2;
EMPLOYEE_NUMBER EMPLOYEE_N JOB_TYPE HIRE_DATE DEPARTMENT_NUMBER SALARY COMMISSION
----------------------------- -------------------- -------------- ----------------- --------------------------------- ---------- ------------------
1001 mathan MGR 30-JUL-22 128 75000 60000
1002 raj designer 22-MAY-21 184 65000 50000
1003 charu developer 10-OCT-19 134 60000
1004 mahi sales 18-AUG-20 224 70000 60000
1005 aadhavan technicion 20-MAY-24 254 55000 65000
1006 sri developer 01-JAN-18 321 58000
6 rows selected.

19. (a)Query to display the Highest, Lowest, Sum and Average Salaries of all the
Employees
SQL> select max(salary) as highest_salary,
2 min(salary) as lowest_salary,
3 sum(salary) as total_salary,
4 avg(salary) as average_salary from employee2;
HIGHEST_SALARY LOWEST_SALARY TOTAL_SALARY AVERAGE_SALARY
----------------------- ----------------------- --------------------- --------------------------
75000 55000 383000 63833.3333

20. (b)Query to display the employee_number and employee_name for all employees who
earn more than the average salary
SQL> select employee_number,employee_name from employee2 where salary>(select avg(salary) from
employee);
EMPLOYEE_NUMBER EMPLOYEE_N
--------------- ------------ ---------------------
1001 mathan
1002 raj
1004 mahi

21. (c)Query to display the employee_name, salary and commission for all the employees
who earn commission.
SQL> select employee_name,salary,commission from employee2 where commission is not null;
EMPLOYEE_N SALARY COMMISSION
------------------ ---------- -------------------
mathan 75000 60000
raj 65000 50000
mahi 70000 60000
aadhavan 55000 65000

22. (d) Sort the data in descending order of salary and commission
SQL> select employee_name,salary,commission from employee2 order by salary desc,commission desc;
EMPLOYEE_N SALARY COMMISSION
------------------ ---------- -------------------
mathan 75000 60000
mahi 70000 60000
raj 65000 50000
charu 60000
sri 58000
aadhavan 55000 65000
6 rows selected.

23. (e) Query to display employee_name, salary and commission for all employees whose
commission is greater than their salary increased by 5%.
SQL> select employee_name,salary,commission from employee2 where commission >
salary+(salary*0.05);
EMPLOYEE_N SALARY COMMISSION
------------------ ----------- --------------------
aadhavan 55000 65000
SQL> spool off

24. Ex. No:12. Create a DEPARTMENT table with the attributes of department_number and
department_name. Set the department_ number as a primary key.
(a)Insert few records
(b)Display all the records
(c) Create an employee table with the following attribute employee_number,
employee_name, job and department_number. Set the employee_number as a primary
key and set the department_number as a foreign key.
(d) Query to display the employee details who are working in the particular
department_number.
(e) Query to display employee_number, employee_name and job from the employee
table
(f) Query to display unique jobs from the employee Table
(g) Query to display the employee_name concatenated by a job separated by a comma.

25. Create a DEPARTMENT table with the attributes of department_number and
department_name. Set the department_ number as a primary key.
SQL> create table department (
2 dept_no number(5) primary key,
3 dept_name char(30) );
Table created.
(a) Insert few records
SQL> insert into department values(1,'Human Resources');
1 row created.
SQL> insert into department values(2,'finance');
1 row created.

26. SQL> insert into department values(3,'engineering');
1 row created.
SQL> insert into department values(4,'marketing');
1 row created.
Verification for Primary Key: (Duplication Not allowed)
SQL> insert into department values(5,'sales');
1 row created.
SQL> insert into department values(5,'hr');
insert into department values(5,'hr')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004131) violated

27. (b)Display all the records
SQL> select * from department;
DEPT_NO DEPT_NAME
---------- ------------------------------
1 Human Resources
2 finance
3 engineering
4 marketing
5 sales

28. (c) Create an employee table with the following attribute employee_number, employee_name, job and
department_number. Set the employee_number as a primary key and set the department_number as
a foreign key.
SQL> create table employee3 (
2 emp_no number(5) primary key,
3 emp_name char(30),
4 job char(20),
5 dept_no number(5),
6 foreign key(dept_no) references department(dept_no) );
Table created.
Insert a few records:
SQL> insert into employee3 values(101,'raj','manager',1);
1 row created.

29. SQL> insert into employee3 values(102,'deva','accountant',2);
1 row created.
SQL> insert into employee3 values(103,'surya','engineering',3);
1 row created.
SQL> insert into employee3 values(104,'siva','marketing executive',4);
1 row created.
SQL> insert into employee3 values(105,'kalyan','sales',5);
1 row created.
Verification for Primary Key: (Duplication Not allowed)
SQL> insert into employee3 values(105,'mohan','sales',5);
insert into employee3 values(105,'mohan','sales',5)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004132) violated

30. Verification for Foreign Key:
SQL> insert into employee3 values(106,'mohan','sales',10);
insert into employee3 values(106,'mohan','sales',10)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.SYS_C004133) violated - parent key not found
(d) Query to display the employee details who are working in the particular
department_number.
SQL> select * from employee3 where dept_no=1;
EMP_NO EMP_NAME JOB DEPT_NO
---------- ------------------------------ -------------------- ---------------
101 raj manager 1

31. (e) Query to display employee_number, employee_name and job from the employee table

SQL> select emp_no,emp_name,job from employee3;
EMP_NO EMP_NAME JOB
--------------- ------------------------------ --------------------
101 raj manager
102 deva accountant
103 surya engineering
104 siva marketing executive
105 kalyan accountant
(f) Query to display unique jobs from the employee Table
SQL> select distinct job from employee3;

32. JOB
--------------------
manager
engineering
marketing executive
accountant
(g) Query to display the employee_name concatenated by a job separated by a comma.
SQL> select emp_name || ',' || job as employee_job from employee3;
EMPLOYEE_JOB
---------------------------------------------------
raj ,manager
deva ,accountant
surya ,engineering
siva ,marketing executive
kalyan ,accountant

33. SQL> select concat(concat(emp_name,','),job) as employee_job from employee3;
EMPLOYEE_JOB
---------------------------------------------------
raj ,manager
deva ,accountant
surya ,engineering
siva ,marketing executive
kalyan ,accountant
SQL> spool off

34. Ex. No:13. Create a DEPARTMENT table with the attributes of department_number and
department_name. Set the department number as a primary key.
(a) Create an Employee table with the following attributes: employee_number,
name, job_type, department_number and location.
(b) Query to display Unique Listing of all Jobs that are in department_number 20.
(c) Query to display employee name, department_name and department_number
for all the employees.
(d) Query to display name, Job, department_number and department_name for all
the employees working at the Mumbai location.

35. Create a DEPARTMENT table with the attributes of department_number and
department_name. Set the department number as a primary key
SQL> create table department1 (
2 dept_no number(5) primary key,
3 dept_name char(20) );
Table created.
Insert a few records:
SQL> insert into department1 values(10,'human resources');
1 row created.
SQL> insert into department1 values(20,'finance');
1 row created.

36. SQL> insert into department1 values(30,'engineering');
1 row created.
SQL> insert into department1 values(40,'marketing');
1 row created.
SQL> insert into department1 values(50,'sales');
1 row created.
Verification for Primary Key: (Duplication Not allowed)
SQL> insert into department1 values(50,'hr');
insert into department1 values(50,'hr')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C004140) violated

37. Display the records:
SQL> select * from department1;
DEPT_NO DEPT_NAME
---------- --------------------
10 human resources
20 finance
30 engineering
40 marketing
50 sales
(a) Create an Employee table with the following attributes: employee_number,
name, job_type, department_number and location.

38. SQL> create table employee4 (
2 emp_no number(5) primary key,
3 name char(20),
4 job_type char(20),
5 dept_no number(10),
6 loc char(20),
7 foreign key (dept_no) references department1(dept_no) );
Table created.
Insert a few records:
SQL> insert into employee4 values(101, 'Johnson', 'MGR', 10, 'Mumbai');
1 row created.
SQL> insert into employee4 values(102, 'Smith', 'Accountant', 20, 'Delhi');
1 row created.

39. SQL> insert into employee4 values(103, 'Brown', 'Engineer', 30, 'Mumbai');
1 row created.
SQL> insert into employee4 values(104, 'Prince', 'Marketing Executive', 40, 'Mumbai');
1 row created.
SQL> insert into employee4 values(105, 'Frank', 'Accountant', 20, 'Chennai');
1 row created.
SQL> insert into employee4 values(106, 'Lee', 'Engineer', 30, 'Pune');
1 row created.
SQL> insert into employee4 values(107, 'Harish', 'Clerk', 20, 'Pune');
1 row created.
SQL> insert into employee4 values(108, 'James', 'Clerk', 20, 'kerala');
1 row created.

40. Display the records:
SQL> select * from employee4;
EMP_NO NAME JOB_TYPE DEPT_NO LOC
------------- ------------------- -------------------- ---------------- ----------
101 Johnson MGR 10 Mumbai
102 Smith Accountant 20 Delhi
103 Brown Engineer 30 Mumbai
104 Prince Marketing Executive 40 Mumbai
105 Frank Accountant 20 Chennai
106 Lee Engineer 30 Pune
107 Harish Clerk 20 Pune
108 James Clerk 20 kerala
8 rows selected.

41. (b) Query to display Unique Listing of all Jobs that are in department_number 20.
SQL> select distinct job_type from employee4 where dept_no=20;
JOB_TYPE
--------------------
Clerk
Accountant
(c) Query to display employee name, department_name and department_number
for all the employees.

SQL> select employee4.name as emp_name,department1.dept_name,employee4.dept_no from
employee4,department1 where employee4.dept_no=department1.dept_no;

42. EMP_NAME DEPT_NAME DEPT_NO
-------------------- -------------------- --------------
Johnson human resources 10
Smith finance 20
Brown engineering 30
Prince marketing 40
Frank finance 20
Lee engineering 30
Harish finance 20
James finance 20
8 rows selected.

43. (d) Query to display name, Job, department_number and department_name for all
the employees working at the Mumbai location.
SQL> select employee4.name as emp_name,employee4.job_type,employee4.dept_no,
department1.dept_name from employee4,department1 where
employee4.dept_no=department1.dept_no and employee4.loc='Mumbai';
EMP_NAME JOB_TYPE DEPT_NO DEPT_NAME
-------------------- -------------------- -------------- ----------------
Johnson MGR 10 human resources
Brown Engineer 30 engineering
Prince Marketing Executive 40 marketing
SQL> spool off

44. Ex. No:14. Create a table client-master with the following fields: client_no, name, address, city,
state, pincode, remarks, bal_due with suitable data types.
(a) Create another table supplier_master from client_master.
(b) rename the attribute client_no with supplier_no and the attribute name with
supplier_name in the supplier_master table
(c) Insert data into client_master
(d) Insert data into supplier_master from client_master.
(e) Delete the row which is having the value chennai in the city attribute of
client_master table.
(f) Drop the client_master table

45. Create a table client-master with the following fields: client_no, name, address, city,
state, pincode, remarks, bal_due with suitable data types.
SQL> create table client_master (
2 client_no number(6) primary key,
3 name char(20),
4 address char(30),
5 city char(20),
6 state char(20),
7 pincode number(6),
8 remarks char(30),
9 bal_due number(10,2) );
Table created.
(a) Create another table supplier_master from client_master.
SQL> create table supplier_master as select * from client_master;
Table created.

46. Display the structure of the supplier_master:
SQL> desc supplier_master;
Name Null? Type
---------------------------------------------------- ------------------------ --------------------------------------------------
CLIENT_NO NUMBER(6)
NAME CHAR(20)
ADDRESS CHAR(30)
CITY CHAR(20)
STATE CHAR(20)
PINCODE NUMBER(6)
REMARKS CHAR(30)
BAL_DUE NUMBER(10,2)

47. (b) rename the attribute client_no with supplier_no and the attribute name with
supplier_name in the supplier_master table.
SQL> alter table supplier_master rename column client_no to supplier_no;
Table altered.
SQL> alter table supplier_master rename column name to supplier_name;
Table altered.
SQL> desc supplier_master;
Name Null? Type
---------------------------------------------------- ------------------ --------------
SUPPLIER_NO NUMBER(6)
SUPPLIER_NAME CHAR(20)
ADDRESS CHAR(30)
CITY CHAR(20)

48. STATE CHAR(20)
PINCODE NUMBER(6)
REMARKS CHAR(30)
BAL_DUE NUMBER(10,2)
(c) Insert data into client_master
SQL> insert into client_master values(1,'jack','123 main st','chennai','tamil nadu',600028,'regular
client',25000.00);
1 row created.
SQL> insert into client_master values(2,'prathap','456 market st','bangalore','karnadaka',560001,'regular
client',15000.00);
1 row created.
SQL> insert into client_master values(3,'mathan','789 king st','mumbai','maharashra',400001,'new
client',7500.00);
1 row created.

49. SQL> insert into client_master values(4,'williams','321 elm st','kolkata','west bengal',700001,'premium
client',20000.00);
1 row created.
SQL> insert into client_master values(5,'robert','654 oak st','chennai','tamil nadu',110001,'occasional
client',3000.00);
1 row created.
Display the records from client_master:
SQL> set linesize 200;
SQL> select * from client_master;

50. CLIENT_NO NAME ADDRESS CITY STATE PINCODE REMARKS BAL_DUE
---------------- --------- -------------- --------------- -------------- --------------- ------------------ ----------------
1 jack 123 main st chennai tamil nadu 600028 regular client 25000
2 prathap 456 market st bangalore karnadaka 560001 regular client 15000
3 mathan 789 king st mumbai maharashra 400001 new client 7500
4 williams 321 elm st kolkata west bengal 700001 premium client 20000
5 robert 654 oak st chennai tamil nadu 110001 occasional client 3000
(d) Insert data into supplier_master from client_master.
SQL> insert into supplier_master(supplier_no,supplier_name,address,city,state,pincode,remarks,bal_due) select
client_no,name,address,city,state,pincode,remarks,bal_due from client_master;
5 rows created.
Diplay the inserted records:
SQL> select * from supplier_master;

51. CLIENT_NO NAME ADDRESS CITY STATE PINCODE REMARKS BAL_DUE
---------------- --------- -------------- --------------- -------------- --------------- ------------------ ----------------
1 jack 123 main st chennai tamil nadu 600028 regular client 25000
2 prathap 456 market st bangalore karnadaka 560001 regular client 15000
3 mathan 789 king st mumbai maharashra 400001 new client 7500
4 williams 321 elm st kolkata west bengal 700001 premium client 20000
5 robert 654 oak st chennai tamil nadu 110001 occasional client 3000
(e) Delete the row which is having the value chennai in the city attribute of client_master table.

SQL> delete from client_master where city='chennai';
2 rows deleted.
Display the records:

52. CLIENT_NO NAME ADDRESS CITY STATE PINCODE REMARKS BAL_DUE
---------------- --------- -------------- --------------- -------------- --------------- ------------------ ----------------
2 prathap 456 market st bangalore karnadaka 560001 regular client 15000
3 mathan 789 king st mumbai maharashra 400001 new client 7500
4 williams 321 elm st kolkata west bengal 700001 premium client 20000
(f) Drop the client_master table
SQL> drop table client_master;
Table dropped.
Verification for the table is deleted or not.
SQL> desc client_master;
ERROR:
ORA-04043: object client_master does not exist

53. Procedure to type and execute pl/sql program:
1. Type the program by using the following command:
sql> edit F:\RDBMS\PROGRAM.SQL
2. Save the program.
3.set serveroutput on (for displaying the result):
sql> set serveroutput on
4.get the program by using the following command: get Execute the program:
sql> get F:\RDBMS\PROGRAM.SQL
5.Execute the program
sql> @ F:\RDBMS\PROGRAM.SQL

54. Ex. No: 15. Create a table master_book to contain the information of magazine_code,magazine_name
and publisher, magazine_type (Weekly/biweekly/monthly) and price. Write a PL/SQL block to perform
insert, update and delete operations on the above table .
Table creation:
SQL> create table master_book (
2 magazine_code char(10) primary key,
3 magazine_name char(50),
4 publisher char(50),
5 magazine_type char(20),
6 price number(10,2) );
Table created.

55. Insert a few records:
SQL> insert into master_book values('M001','tech weekly','tech publishing','weekly',49.99);
1 row created.
SQL> insert into master_book values('M002','helth monthly','wellness media','monthly',79.99);
1 row created.
Display the records:
SQL> select * from master_book;
MAGAZINE_C MAGAZINE_NAME PUBLISHER MAGAZINE_TYPE PRICE
-------------------- --------------------------- -------------------- ------------------------------ ----------------
M001 tech weekly tech publishing weekly 49.99
M002 helth monthly wellness media monthly 79.99

56. SQL> set serveroutput on;
SQL> edit F:\RDBMS\pro15insert.sql
Program for insertion:
DECLARE
v_magazine_code CHAR(10);
v_magazine_name CHAR(50);
v_publisher CHAR(50);
v_magazine_type CHAR(10);
v_price NUMBER(10, 2);
BEGIN
v_magazine_code := UPPER('&Enter_Magazine_Code'); -- Convert input to uppercase
v_magazine_name := '&Enter_Magazine_Name';

57. v_publisher := '&Enter_Publisher';
v_magazine_type := '&Enter_Magazine_Type'; -- Weekly, Biweekly, Monthly
v_price := &Enter_Price;

INSERT INTO master_book (magazine_code, magazine_name, publisher, magazine_type, price)
VALUES (v_magazine_code, v_magazine_name, v_publisher, v_magazine_type, v_price);

COMMIT; -- Commit the transaction
DBMS_OUTPUT.PUT_LINE('Record Inserted');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

58. Execute the program:
SQL> @ F:\RDBMS\pro15insert.sql
SQL> @ F:\RDBMS\pro15insert.sql
Enter value for enter_magazine_code: m003
old 8: v_magazine_code := UPPER('&Enter_Magazine_Code'); -- Convert input to uppercase
new 8: v_magazine_code := UPPER('m003'); -- Convert input to uppercase
Enter value for enter_magazine_name: business
old 9: v_magazine_name := '&Enter_Magazine_Name';
new 9: v_magazine_name := 'business';
Enter value for enter_publisher: biz publishers
old 10: v_publisher := '&Enter_Publisher';
new 10: v_publisher := 'biz publishers';
Enter value for enter_magazine_type: biweekly

59. old 11: v_magazine_type := '&Enter_Magazine_Type'; -- Weekly, Biweekly, Monthly
new 11: v_magazine_type := 'biweekly'; -- Weekly, Biweekly, Monthly
Enter value for enter_price: 120.00
old 12: v_price := &Enter_Price;
new 12: v_price := 120.00;
Record Inserted
PL/SQL procedure successfully completed.
Display the records:
MAGAZINE_C MAGAZINE_NAME PUBLISHER MAGAZINE_TYPE PRICE
-------------------- --------------------------- -------------------- ------------------------------ ----------------
M001 tech weekly tech publishing weekly 49.99
M002 helth monthly wellness media monthly 79.99
M003 business biz publishers biweekly 120

60. Program for update:
SQL> edit F:\RDBMS\pro15update.sql
DECLARE
v_magazine_code CHAR(10);
v_price NUMBER(10, 2);
BEGIN
v_magazine_code := UPPER('&Enter_Magazine_Code'); -- Convert input to uppercase
v_price := &Enter_New_Price;

UPDATE master_book
SET price = v_price
WHERE UPPER(magazine_code) = v_magazine_code; -- Ensure comparison is case-insensitive

61. IF SQL%ROWCOUNT > 0 THEN
COMMIT; -- Commit the transaction
DBMS_OUTPUT.PUT_LINE('Record Updated');
ELSE
DBMS_OUTPUT.PUT_LINE('No record found with the provided magazine code.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

62. Execute the program:
SQL> @ F:\RDBMS\pro15update.sql
Enter value for enter_magazine_code: m001
old 5: v_magazine_code := UPPER('&Enter_Magazine_Code'); -- Convert input to uppercase
new 5: v_magazine_code := UPPER('m001'); -- Convert input to uppercase
Enter value for enter_new_price: 99.99
old 6: v_price := &Enter_New_Price;
new 6: v_price := 99.99;
Record Updated
PL/SQL procedure successfully completed.

63. Display the records:
SQL> select * from master_book;
MAGAZINE_C MAGAZINE_NAME PUBLISHER MAGAZINE_TYPE PRICE
-------------------- --------------------------- -------------------- ------------------------------ ----------------
M001 tech weekly tech publishing weekly 99.99
M002 helth monthly wellness media monthly 79.99
M003 business biz publishers biweekly 120
Program for delete:
SQL> edit F:\RDBMS\pro15delete.sql

64. DECLARE
v_magazine_code CHAR(10);
BEGIN
v_magazine_code := UPPER('&Enter_Magazine_Code'); -- Convert input to uppercase

DELETE FROM master_book
WHERE UPPER(magazine_code) = v_magazine_code; -- Ensure comparison is case-insensitive

IF SQL%ROWCOUNT > 0 THEN
COMMIT; -- Commit the transaction
DBMS_OUTPUT.PUT_LINE('Record Deleted');
ELSE
DBMS_OUTPUT.PUT_LINE('No record found with the provided magazine code.');
END IF;

65. EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Execute the program:
SQL> @ F:\RDBMS\pro15delete.sql
Enter value for enter_magazine_code: m003
old 4: v_magazine_code := UPPER('&Enter_Magazine_Code'); -- Convert input to uppercase
new 4: v_magazine_code := UPPER('m003'); -- Convert input to uppercase
Record Deleted
PL/SQL procedure successfully completed.

66. Display the records:
SQL> select * from master_book;
MAGAZINE_C MAGAZINE_NAME PUBLISHER MAGAZINE_TYPE PRICE
-------------------- --------------------------- -------------------- ------------------------------ ----------------
M001 tech weekly tech publishing weekly 99.99
M002 helth monthly wellness media monthly 79.99
SQL> spool off

67. Ex. No: 16. Create a table to contain phone_number, user_name, address of the phone user.
Write a function to search for an address using phone numbers.
Table creation:
SQL> create table phone_users (
2 phone_number number(10) primary key,
3 user_name char(20),
4 address char(30) );
Table created.

68. Insert a few records:
SQL> insert into phone_users values(7921847610,'vijay','123 main st ,mumbai');
1 row created.
SQL> insert into phone_users values(1234567890,'rahul','456 park st ,bangalore');
1 row created.
SQL> insert into phone_users values(7383415161,'raj','758 nehru st ,delhi');
1 row created.
SQL> insert into phone_users values(8176198410,'mohan','17 king st ,chennai');
1 row created.
SQL> insert into phone_users values(7619845121,'surya','100 mgr st ,chennai');
1 row created.

69. Display the records:
SQL> select * from phone_users;
PHONE_NUMBER USER_NAME ADDRESS
------------------------ ----- ------------ --------------------------
7921847610 vijay 123 main st ,mumbai
1234567890 rahul 456 park st ,bangalore
7383415161 raj 758 nehru st ,delhi
8176198410 mohan 17 king st ,chennai
7619845121 surya 100 mgr st ,chennai
Function to search for an address using phone numbers :
SQL> edit d:\rdbms_lab\pro16.sql

70. CREATE OR REPLACE FUNCTION search_address_by_phone(
phone IN phone_users.phone_number%TYPE
) RETURN CHAR
IS
user_address CHAR(30);
BEGIN
SELECT address INTO user_address
FROM phone_users
WHERE phone_number = phone;
RETURN user_address;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Address not found';

71. WHEN OTHERS THEN
RETURN 'An error occurred';
END;
/
Execute the program:
SQL> @ d:\rdbms_lab\pro16.sql
Function created.
SQL> set serveroutput on;
SQL> select search_address_by_phone('1234567890') as address from dual;
ADDRESS
--------------------------------------------------------------------------------
456 park st ,bangalore

72. SQL> select search_address_by_phone('8667512345') as address from dual;
ADDRESS
--------------------------------------------------------------------------------
Address not found
SQL> spool off;

73. Ex. No: 17. Create a table to store the salary details of the employees in a company. Declare the
cursor to contain employee_number, employee_name and net_salary. Use cursor to update
the employee salaries.
Table creation:
SQL> create table emp_salary (
2 employee_number number(10),
3 employee_name char(10),
4 salary number(10),
5 hra number(10,2),
6 ma number(10,2),
7 lic number(10,2),
8 pf number(10,2),
9 gs number(10,2),

74. 10 ns number(10,2),
11 deduction number(10,2) );
Table created.
Insert a few records:
SQL> insert into emp_salary values(7329,'meera',56000,10000,2000,15000,3000,'','','');
1 row created.
SQL> insert into emp_salary values(7110,'mathan',70000,15000,3000,2500,4000,'','','');
1 row created.
SQL> insert into emp_salary values(7112,'ram',60000,12000,2500,2000,3500,'','','');
1 row created.

75. SQL> insert into emp_salary values(7531,'sri',45000,8000,1800,1200,2500,'','','');
1 row created.
SQL> insert into emp_salary values(7331,'mahi',55000,7000,1200,1100,2100,'','','');
1 row created.
Update GS , DEDUCTION AND NS:
SQL> update emp_salary set gs=salary+hra+ma;
5 rows updated.
SQL> update emp_salary set deduction=lic+pf;
5 rows updated.
SQL> update emp_salary set ns=gs-deduction;
5 rows updated

76. SQL> set linesize 180;
SQL> select * from emp_salary;
EMPLOYEE_NUMBER EMPLOYEE_N SALARY HRA MA LIC PF GS NS DEDUCTION
---------------------------- --------------------- ------------- ----------- ---------- -------- ------- ------- --------- -------------------
7329 meera 56000 10000 2000 15000 3000 68000 50000 18000
7110 mathan 70000 15000 3000 2500 4000 88000 81500 6500
7112 ram 60000 12000 2500 2000 3500 74500 69000 5500
7531 sri 45000 8000 1800 1200 2500 54800 51100 3700
7331 mahi 55000 7000 1200 1100 2100 63200 60000 3200
Cursor to update the employee salaries:
SQL> edit d:\rdbms_lab\pro17.sql

77. DECLARE
CURSOR employee_cursor IS
SELECT employee_number, employee_name, NS
FROM emp_salary;
v_employee_number emp_salary.employee_number%TYPE;
v_employee_name emp_salary.employee_name%TYPE;
v_salary emp_salary.salary%TYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO v_employee_number, v_employee_name, v_salary;
EXIT WHEN employee_cursor%NOTFOUND;

78. -- Update NS for each employee
UPDATE emp_salary
SET salary = salary * 1.10
WHERE employee_number = v_employee_number;
END LOOP;
CLOSE employee_cursor;
-- Print the updated table
FOR rec IN (SELECT * FROM emp_salary) LOOP
DBMS_OUTPUT.PUT_LINE('Employee Number: ' || rec.employee_number ||
', Name: ' || rec.employee_name ||
', Salary: ' || rec.salary ||

79. ', HRA: ' || rec.HRA ||
', MA: ' || rec.MA ||
', LIC: ' || rec.LIC ||
', PF: ' || rec.PF ||
', GS: ' || rec.GS ||
', NS: ' || rec.NS ||
', DEDUCTION: ' || rec.DEDUCTION);
END LOOP;
END;
Execute the program:
SQL> set serveroutput on;
SQL> @ d:\rdbms_lab\pro17.sql
37 /

80. Employee Number: 7329, Name: meera , Salary: 61600, HRA: 10000, MA: 2000, LIC: 15000, PF: 3000, GS: 68000,
NS: 50000, DEDUCTION: 18000
Employee Number: 7110, Name: mathan , Salary: 77000, HRA: 15000, MA: 3000, LIC: 2500, PF: 4000, GS: 88000,
NS: 81500, DEDUCTION: 6500
Employee Number: 7112, Name: ram , Salary: 66000, HRA: 12000, MA: 2500, LIC: 2000, PF: 3500, GS: 74500, NS:
69000, DEDUCTION: 5500
Employee Number: 7531, Name: sri , Salary: 49500, HRA: 8000, MA: 1800, LIC: 1200, PF: 2500, GS: 54800, NS:
51100, DEDUCTION: 3700
Employee Number: 7331, Name: mahi , Salary: 60500, HRA: 7000, MA: 1200, LIC: 1100, PF: 2100, GS: 63200, NS:
60000, DEDUCTION: 3200
PL/SQL procedure successfully completed.
SQL> spool off

81. Ex. No: 18. Create a table to contain the information about the voters in a particular constituency.
Write a proper trigger to update or delete a row in the table.
Table creation:
SQL> create table voters (
2 v_code char(10) primary key,
3 name char(20),
4 father_name char(20),
5 gender char(15),
6 dob date,
7 address char(40),
8 phone_no number(10) );
Table created.

82. modify_voters table creation:
SQL> create table modify_voters as select * from voters;
Table created.
Insert a record into voters Table :-
SQL> insert into voters values('ka001','sriram','ram','male','08-jan-05','123 main st Bangalore,Karnataka-
560001',6345322489);
1 row created.
SQL> insert into voters values('tn110','mathan','raja','male','10-oct-04','22 mgr st chennai,Tamilnadu-
600028',7866665698);
1 row created.

83. SQL> insert into voters values('tn730','aadhvi','ram','female','18-aug-04','22 nehru st chennai,Tamilnadu-
600028',8667543210);
1 row created.
SQL> insert into voters values('ko113','rose','jack','female','10-jan-06','123 oak st kolkata,bengal-
700001',6386606061);
1 row created.
SQL> insert into voters values('ma171','willam','johnson','male','13-jan-03','78 king st
mumbai,maharashra-400001',9878675645);
1 row created.
Display the records:
SQL> set linesize 180;
SQL> select * from voters;

84. V_CODE NAME FATHER_NAME GENDER DOB ADDRESS PHONE_NO
---------- ----------- ------------------ ----------- ------------ ---------------- ------------------------
ka001 sriram ram male 08-JAN-05 123 main st Bangalore,Karnataka-560001 6345322489
tn110 mathan raja male 10-OCT-04 22 mgr st chennai,Tamilnadu-600028 7866665698
tn730 aadhvi ram female 18-AUG-04 22 nehru st chennai,Tamilnadu-600028 8667543210
ko113 rose jack female 10-JAN-06 123 oak st kolkata,bengal-700001 6386606061
ma171 willam johnson male 13-JAN-03 78 king st mumbai,maharashra-400001 9878675645

85. Create a trigger for update:
SQL> create or replace trigger trg_update
2 AFTER update on voters
3 FOR EACH ROW
4 BEGIN
5 insert into modify_voters values(:NEW.v_code,:NEW.name,:NEW.father_name,:NEW.gender,
:NEW.dob,:NEW.address,:NEW.phone_no);
6 END;
7 /
Trigger created.

86. Update a record in the voters table:
SQL> update voters set phone_no=6381761984 where v_code='tn110';
1 row updated.
Verification of Trigger Execution for update operation :
Display the records from modify_voters:
sql> select * from modify_voters;
V_CODE NAME FATHER_NAME GENDER DOB ADDRESS PHONE_NO
---------- ----------- ------------------ ----------- ------------ -------------------------------------------------- ------------------------
tn110 mathan raja male 10-OCT-04 22 mgr st chennai,Tamilnadu-600028 6381761984

87. Create a trigger for delete:
SQL> create or replace trigger tgr_delete
2 BEFORE DELETE ON voters
3 FOR EACH ROW
4 BEGIN
5 insert into modify_voters values(:OLD.v_code,:OLD.name,:OLD.father_name,:OLD.gender,
:OLD.dob,:OLD.address,:OLD.phone_no);
6 END;
7 /
Trigger created.

88. SQL> delete from voters where v_code='ma171';
1 row deleted.
Verification of Trigger Execution for delete operation :
Display the records from modify_voters:
V_CODE NAME FATHER_NAME GENDER DOB ADDRESS PHONE_NO
---------- ----------- ------------------ ----------- ------------ ---------------- ------------------------
tn110 mathan raja male 10-OCT-04 22 mgr st chennai,Tamilnadu-600028 6381761984
ma171 willam johnson male 13-JAN-03 78 king st mumbai,maharashra-400001 9878675645

89. Verification for the teble record is deleted or not :
Display the records from voters:
V_CODE NAME FATHER_NAME GENDER DOB ADDRESS PHONE_NO
---------- ----------- ------------------ ----------- ------------ ---------------- ------------------------
ka001 sriram ram male 08-JAN-05 123 main st Bangalore,Karnataka-560001 6345322489
tn110 mathan raja male 10-OCT-04 22 mgr st chennai,Tamilnadu-600028 7866665698
tn730 aadhvi ram female 18-AUG-04 22 nehru st chennai,Tamilnadu-600028 8667543210
ko113 rose jack female 10-JAN-06 123 oak st kolkata,bengal-700001 6386606061

90. Ex. No: 19. Create a table employee to contain the information of employee_name,
employee_number and salary.
(a) Write a procedure to increase 10% of salary to all employees (procedure without argument).
(b) Write a procedure to increase specific percentage for specific department number
(procedure with argument).

91. Create a table employee to contain the information of employee_name, employee_number and salary.
SQL> create table emp (
2 employee_name char(10),
3 employee_no number(10),
4 salary number(10) );
Table created.
Insert a few records:
SQL> insert into emp values('mathan',1001,50000);
1 row created.
SQL> insert into emp values('sri',1002,45000);
1 row created.
SQL> insert into emp values('ram',1003,20000);
1 row created.

92. SQL> insert into emp values('rose',1004,80000);
1 row created.
SQL> insert into emp values('jack',1005,60000);
1 row created.
Display the records Before Executing the Procedure:
SQL> select * from emp;
EMPLOYEE_N EMPLOYEE_NO SALARY
----------- ------ -------------------- ---------------
mathan 1001 50000
sri 1002 45000
ram 1003 20000
rose 1004 80000
jack 1005 60000

93. (a) Write a procedure to increase 10% of salary to all employees (procedure without argument).
SQL> create or replace PROCEDURE increase_sal
2 IS
3 BEGIN
4 UPDATE emp
5 SET salary=salary*1.1;
6 END;
7 /
Procedure created.

94. Execute the procedure:
SQL> EXEC increase_sal;
PL/SQL procedure successfully completed.
Display the records from the table After Executing the Procedure:
SQL> select * from emp;
EMPLOYEE_N EMPLOYEE_NO SALARY
----------- ------ -------------------- ---------------
mathan 1001 55000
sri 1002 49500
ram 1003 22000
rose 1004 88000
jack 1005 66000

95. (b) Write a procedure to increase specific percentage for specific department number (procedure with argument).
SQL> CREATE PROCEDURE increase_sal2 (vdept IN emp.employee_no%TYPE,percentage number)
2 IS
3 BEGIN
4 update emp
5 set salary=salary*(1+percentage/100)
6 WHERE employee_no=vdept;
7 END;
8 /
Procedure created.

96. Execute the procedure:
SQL> exec increase_sal2(1003,10);
PL/SQL procedure successfully completed.
Display the records from the table:
SQL> select * from emp;
EMPLOYEE_N EMPLOYEE_NO SALARY
----------- ------ -------------------- ---------------
mathan 1001 55000
sri 1002 49500
ram 1003 24200
rose 1004 88000
jack 1005 66000