STUDENT TABLE

AIM:

1. Create a student table with the following attributes name, register number,
department, marks in 5 subjects and total.

 Syntax to create table:

CREATE TABLE <table_name> (column1 data_type,column2 data_type,…);

 Creating table:

Create table student (name varchar(15),regno number(10),dept varchar(10),M1

number(3),M2 number(3),M3 number(3),M4 number(3),M5 number(3),total
number(3));

(a) Insert few records into student table.

 Syntax to insert value:

INSERT INTO <table_name> (column1,column2,…) VALUES (value1,value2,…);

 Inserting values:

Insert into student values(‘Kathir’,101,’comp-sci’,88,78,97,86,67,’ ’);

Insert into student values(‘Krishna’,102,’maths’,98,89,78,76,87,’ ’);
Insert into student values(‘Radha’,103,’physics’,87,93,74,92,81,’ ’);
Insert into student values(‘Madhu’,104,’chemistry’,93,81,64,72,78,’ ’);
Insert into student values(‘Cindy’,105,’comp-sci’,85,79,85,93,81,’ ’);

(b) Display all the records.

 Syntax to Display Records:

SELECT column1,column2,… FROM <table_name> WHERE condition;

  Displaying records:

Select * from student;

(c) Calculate the total marks for all the records.

 Syntax :

UPDATE <table_name> SET (column1 = value1,column2 = value2) WHERE condition;

 QUERY:

Update student set total=M1+M2+M3+M4+M5;

(d) Display the information of student name, register number and total only.

 QUERY:

Select name,regno,total from student;

 OUTPUT:
 STUDENT TABLE WITH FIVE SUBJECT & TOTAL

AIM:

2. Create a student table with the following attributes name, registernumber,

department, marks in 5 subjects and total.

 QUERY:

Create table student (name varchar(15),regno number(10),dept varchar(10),M1

number(3),M2 number(3),M3 number(3),M4 number(3),M5 number(3),total
number(3));

(a) Insert few records into student table.

 QUERY:

Insert into student values(‘Kathir’, 211278001,’comp-sci’,88,78,97,86,67,’ ’);

Insert into student values(‘Krishna’, 211278003,’maths’,98,89,78,76,87,’ ’);
Insert into student values(‘Radha’, 211278005,’physics’,87,93,74,92,81,’ ’);
Insert into student values(‘Madhu’, 211278011,’chemistry’,93,81,64,72,78,’ ’);
Insert into student values(‘Cindy’, 211278019,’comp-sci’,85,79,85,93,81,’ ’);

hence the table after inserting values will be…

(b) Modify the name of the student as vignesh whose register number is
211278019.

 QUERY:

Update student set name=’vignesh’ where regno=211278019;

We need to calculate the total also

 QUERY:

Update student set total=M1+M2+M3+M4+M5;

( c) Delete the records whose register number is 211278005.

 Syntax:

DELETE FROM <table_name> WHERE (condition);

 QUERY:

delete from student where regno=211278005;

(d) Display all the records.

 QUERY:

Select * from student;

 STUDENT table with integrity rule

AIM:

3. Create a table student with name, roll number, gender, age and mobile number.
Apply the following integrity rules to the student table.

 QUERY:

Create table student (

name varchar(15)not null check (name=upper(name)),
rollno number(10)not null check (rollno>0),
gender varchar(15)not null check(gender in(‘male’,’female’,’transgender’)),
age number(3)not null,
mobile_no number(10) null);

(a) The student name must be in capital letter.

 QUERY:

Insert into student values(‘kathir’,1801,’male’,20,8749304820);

 Constraint error! because the name must be in uppercase.

 Correct QUERY:

Insert into student values(‘KATHIR’,1801,’male’,20,8749304820);

 OUTPUT:
(b) The roll number must be greater than zero.

 QUERY:

Insert into student values(‘MADHU’,0,’female’,20,8572949204);

 Constraint error! Because the rollno must be greater than 0.

 Correct QUERY:

Insert into student values(‘MADHU’,1802,’female’,20,8572949204);

 OUTPUT:

(c) The age cannot be a null value.

 QUERY:

Insert into student values(‘DHRUV’,1803,’male’,null,8462894602);

 Error! Because the age must not be a null value.

 Correct QUERY:

Insert into student values(‘DHRUV’,1803,’male’,21,8462894602);

  OUTPUT:

(d) The gender must be “Male” or “Female” or “Transgender”

 QUERY:

Insert into student values(‘STEVE’,1804,’transmale’,22,7395036530);

 Constrain error! Because the gender is not like given in the table.

 Correct QUERY:

Insert into student values(‘STEVE’,1804,’male’,22,7395036530);

 OUTPUT:

(e) The mobile number may contain null values.

 QUERY:

Insert into student values(‘NANCY’,1805,’female’,22,null);

 OUTPUT:
 STUDENT_MASTER

AIM:

4. Create a table student_master with the following attributes name, regno,

dept and year of joining with suitable data types. Use Select QUERY to do the
following.

 QUERY:

Create table student_master (name varchar(15),regno number(10),dept

varchar(10),years_of_joining number(10));

 Insert some values.

Insert into student_master values(‘kathir’,1901,’comp_sci’,2022);

Insert into student_master values(‘sathya’,1902,’maths’,2022);
Insert into student_master values(‘sindhu’,1903,’comp_sci’,2023);
Insert into student_master values(‘preetha’,1904,’physics’,2024);
Insert into student_master values(‘sudan’,1905,’botony’,2022);
Insert into student_master values(‘sudan’,1905,’botony’,2022);

(a) Display all the column in the student_ master table .

 QUERY:

Select * from student_master;

 OUTPUT:
(b) Display the student’s name column only.

 QUERY:

Select name from student_master;

 OUTPUT:

(c) Eliminate the duplicate entry in student_master table.

 QUERY:

Select distinct * from student_master;

 OUTPUT:

(d) Select the details of student who is studying computer science

department.

 QUERY:

Select * from student_master where dept=’comp_sci’;

  OUTPUT:

(e) Sort the attribute name in alphabetical order.

 QUERY:

Select * from student_master order by name asc;

 OUTPUT:
 SALES_ORDER_DETAILS

AIM:

5. Create a table sales_order_details with the s_order_no as primary key and it

contains the following fields: product_no, description, qty_ordered, qty_disp,
product_rate, profit_percent, sell_price, supplier_name. Use Select QUERY to do
the following.

 QUERY:

Create table sales_order_details(

s_order_no number(10) primary key,
product_no number(10),
description varchar(15),
qty_ordered number(15),
qty_disp number(6),
product_rate number(5,2),
profit_percent number(7,2),
sell_price number(8,2),
supplier_name varchar(15));

 Insert some values:

insert into sales_order_details values (1, 101, 'product-a', 100, 74, 520,’ ’, 555.50, 'kathir');
insert into sales_order_details values (2, 102, 'product-b', 50, 40, 300,’ ’, 330.00, 'radha');
insert into sales_order_details values (3, 103, 'product-c', 75, 60, 400,’ ‘, 460.00, 'sharma');
insert into sales_order_details values (4, 104, 'product-d', 120, 100, 250,’ ’, 312.50, 'rahul');
insert into sales_order_details values (5, 105, 'product-e', 200, 180, 150,’ ’, 157.50, 'kiran');

 To Calculate profit_percent for inserted values.

Update sales_order_details set profit_percent = ((sell_price-

product_rate)/product_rate)*100 where product_no>100;
  OUTPUT:

(a) Select each row and compute sell_price*.50 and sell_price*1.50 for each
row selected.

 QUERY:

Select sell_price * 0.50,sell_price * 1.50 from sales_order_details;

 OUTPUT:

(b) Select product_no, profit_percent, Sell_price where profit_per is not

between 10 and 20 both inclusive.

 QUERY:

Select product_no,profit_percent,sell_price from sales_order_details where

profit_percent not between 10 and 20;

 OUTPUT:
(c) Select product_no, description, profit_percent, sell_price where
profit_percent is not between 20 and 30.

 QUERY:

Select product_no, description, profit_percent, sell_price from

sales_order_details where profit_percent not between 20 and 30;

 OUTPUT:

(d) Select the suppliername and product_no where suppliername has ‘r’ or
‘h’as second character.

 QUERY:

Select supplier_name,product_no from sales_order_details

where(substr(supplier_name,2,1)='r' or substr(supplier_name,2,1)= 'h');

 OUTPUT:
 EMPLOYEE TABLE with foreign key self-
referential structure

AIM:

6.Create an Employee table with the following attributes:

employee_number, name, job and manager_id. Set the manager_id as a
foreign key for creating self referential structure.

 QUERY:
create table employee(
emp_no number(10) primary key,
emp_name varchar(20),
job varchar(30),
manager_id number,
foreign key(manager_id)references employee(emp_no));

(a) Insert few records.

 QUERY:

insert into employee values(7329,’alice’,’ceo’,null);

insert into employee values(7499,’boby’,’manager’,7329);
insert into employee values(7531,’charlie’,’developer’,7329);
insert into employee values(7623,’david’,’tester’,7329);
insert into employee values(7672,’henry’,’designer’,7499);

(b) Display all the records.

 QUERY:
Select * from employee;
 OUTPUT:
(c) Display the employee details who are working under particular
manager_id.

 QUERY:

Select * from employee where manager_id=7329;

 OUTPUT:
 EMPLOYEE TABLE:

AIM:

7. Create an Employee table with the following attributes:

employee_number, employee_name, department_number, job and salary.

 QUERY:

create table employee(employee_number number(10),employee_name

varchar(15),department_number number(15),job varchar(15),salary number(10,4));

 Insert some values:

Insert into employee values(1,’meera’,101,’manager’,45000.00);

Insert into employee values(2,’jack’,102,’developer’,22000.00);
Insert into employee values(3,’grace’,103,’analyst’,30000.00);
Insert into employee values(4,’henry’,104,’clerk’,18000.00);
Insert into employee values(5,’boby’,105,’designer’,20000.00);
Insert into employee values(6,’alice’,106,’boby’,50000.00);

(a) Query to display the employee_name and Salary of all the employees
earning more than 20000 INR.

 QUERY:

Select employee_name,salary from employee where salary>20000;

 OUTPUT:
(b) Query to display employee_name and department_number for the
particular employee _number.

 QUERY:

Select employee_name,department_number from employee where

employee_number=6;

 OUTPUT:

(c) Query to display employee_name and Salary for all employees whose
salary is not in the range of INR 15000 and INR 30000.

 QUERY:

Select employee_name, salary from employee where salary not between

15000 and 30000;

 OUTPUT:
 EMPLOYEE TABLE with ATTRIBUTES

AIM:

8. Create an Employee table with the following attribute employee_number,

employee_name, job_type, hire_date, department_number and salary.

 QUERY:

Create table employee(employee_number number(10),employee_name

varchar(15),job_type varchar(15),hire_date varchar(15),department_number
number(10),salary number(6));

 Insert some values:

insert into employee values (1, 'arasi', 'Manager', '2020-01-15', 10, 75000);
insert into employee values (2, 'ananya', 'Analyst', '2021-02-20', 20, 65000);
insert into employee values (3, 'robert', 'Developer', '2019-06-01', 10, 80000);
insert into employee values (4, 'varun', 'Designer', '2020-11-30', 20, 62000);
insert into employee values (5, 'carie', 'Engineer', '2018-04-12', 30, 72000);

(a) Query to display employee_name and department_number of all the

employees in department_number 10 and Department number 20 in the
alphabetical order by name.

 QUERY:

Select employee_name,department_number from employee where

department_number in(10,20) order by employee_name asc;

 OUTPUT:

(b) Query to display Name of all the employees where the third letter of their
name is =A.

 QUERY:

Select employee_name from employee where

substr(employee_name,3,1)=’a’;
  OUTPUT:

(c) Query to display Name with the 1st letter capitalized and all other letter
lowercase.

 QUERY:

Select initcap(employee_name) as formatted_name from employee;

 OUTPUT:

(d) Query to display Name of all employees either have two R‘s or have two
A‘s in their Name.

 QUERY:

Select ename from employee where ename like ‘%r%r%’or ename like ‘%a%a%’;

 OUTPUT:
 EMPLOYEE TABLE WITH MANAGER_ID AS A FOREIGN KEY

AIM:

9. Create an Employee table with the following attributes:

employee_number, name, job, hire_date and manager_id. Set the
manager_id as a forein key for creating selfreferential structure.

 QUERY:

Create table employee(employee_number number(10) primary key,name

varchar(15),job varchar(15),hire_date varchar(15),manager_id number,foreign
key(manager_id) references employee(employee_number));

 Insert some values.

insert into employee values (1, ‘alice’, ‘engineer’, ‘24-jul-2007’, 1);

insert into employee values (2, ‘bob’, ‘manager’, ’10-sep-2007’, null);
insert into employee values (3, ‘charlie’, ‘data analyst’, ‘20-jan-2020’, 1);
insert into employee values (4, ‘diana’, ‘hr specialist’, ‘18-oct-2009’, 2);
insert into employee values (5, ‘eve’, ‘product manager ’, ‘ 05-nov-2018’, null);
insert into employee values (6, ‘frank’, ‘marketing’, ‘22-may-2019’, null);

(a) Query to display name and Hire Date of every Employee who was hired in
2007.

 QUERY:

Select name,hire_date from employee where hire_date BETWEEN TO_DATE(

’2007-01-01’) AND TO_DATE(‘2007-12-31’);

 OUTPUT:
 (b) Query to display name and calculate the number of months between
today and the date each employee was hired.

 QUERY:

Select name,round(months_between(sysdate,hiredate)) as months_since_hire from

employee;

 OUTPUT:

(c) Query to display name and job of all employees who don‘t have a current
Manager.

 QUERY:

Select name,job from employee where manager_id is null;

 OUTPUT:
 SALES_ORDER

AIM:

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.

 QUERY:

Create table sales_order(

s_order_no int primary key,
client_number int,
delivery_address varchar(200),
delivery_date Date,
order_status varchar(50));

 Insert some values:

Insert into sales_order values(1,101,’45-elm street’,’21-02-06’,’shipped’);

Insert into sales_order values(2,102,’oak street’,04_mar_07,’ordered’);
Insert into sales_order values(3,103,’palm street’,’02-jul-05’,’delivered’);
Insert into sales_order values(4,104,’wood streer’,’01-sep-06’,’ordered’);
Insert into sales_order values(5,105,’honey street’,’03-apr-08’,’shipped’);

(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.

 QUERY:

Create table sales_order_copy as select * from sales_order;

 OUTPUT:
(b)Add a new column for storing salesman_number in sales_order using ALTER
Command.

 QUERY:

Alter table sales_order add salesman_number int;

 OUTPUT:

(c)Modify the size of delivery_address in sales_order table using ALTER command.

 QUERY:

Alter table sales_order modify delivery_address varchar(50);

 OUTPUT:

(d)Display the structure of sales_order table.

 QUERY:

Describe sales_order;

 OUTPUT:
 EMPLOYEE TABLE WITH ATTRIBUTES

AIM:
11. Create an Employee table with the following attribute employee_number,
employee_name, job_type, hire_date, department_number, salary and
commission.

 QUERY:

Create table employee(employee_number number(3),employee_name varchar(15),

job_type varchar(15),hire_date varchar(15),department_number number(6),salary
decimal(10,2),commission decimal(10,2));

 Insert some values:

insert into employee values(1,’bela’,’devops’,’20-dec-07’,101,60000.00,5000.00);

insert into employee values(2,'sri','designer','02-feb-05',102,80000.00,null);
insert into employee values(3,'devi','teacher','07-apr-05',103,55000.00,4000.00);
insert into employee values(4,'mitha','editor','02-mar-03',104,90000.00,6000.00);
insert into employee values(5,'david','tailor','10-jan-09',105,25000.00,30000.00);

(a)Query to display the Highest, Lowest, Sum and Average Salaries of all the
Employees.

 QUERY:

Select max(salary) as highest_salary,min(salary) as lowest_salary,sum(salary) as

sum_salary,avg(salary) as avg_salary from employee.

 OUTPUT:
(b)Query to display the employee_number and employee_name for all employees
who earn more than the average salary.

 QUERY:

Select empno,ename from employee where salary>(select avg(salary) from

employee);

 OUTPUT:

(c)Query to display the employee_name, salary and commission for all the
employees who earn commission.

 QUERY:

Select employee_name,salary,commission,from employee where commission

is not null;

 OUTPUT:

(d)Sort the data in descending order of salary and commission.

 QUERY:

Select * from employee order by salary desc;

  OUTPUT:

 QUERY TO DISPLAY COMMISSION IN DESCENDING ORDER.

Select * from employee order by commission desc;

 OUTPUT:

(e)Query to display employee_name, salary and commission for all employees

whose commission is greater than their salary increased by 5%.

 QUERY:

Select employee_name,salary,commission from employee where

commission>(salary*1.05);

 OUTPUT:
 DEPARTMENT TABLE

AIM:

12. Create a DEPARTMENT table with the attributes of department_number and

department_name. Set the department_ number as a primary key.

 QUERY:

Create table department(department_number int primary key,

department_name varchar(15));

(a)Insert few records.

 QUERY:

Insert into department values(1,’hr’);

Insert into department values(2,’it’);
Insert into department values(3,’sales’);
Insert into department values(4,’finance’);
Insert into department values(5,’package’);

(b)Display all the records.

 QUERY:

Select * from department;

 OUTPUT:
(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.

 QUERY:
Create table employee(
employee_number number primary key,
employee_name varchar(15),
job varchar(15),
department_number,foreign key(department_number)
references department(department_number));

 Insert some values:

Insert into employee values(101,’alice’,’manager’,1);

Insert into employee values(102,’smith’,’developer’,2);
Insert into employee values(103,’steve’,’artist’,2);
Insert into employee values(104,’adhi’,’ceo’,3);
Insert into employee values(105,’george’,artist,3);

(d) Query to display the employee details who are working in the particular
department_number.

 QUERY:

Select * from employee where department_number=2;

 OUTPUT:

(e) Query to display employee_number, employee_name and job from the

employee table.

 QUERY:

Select employee_number, employee_name, job from employee;

  OUTPUT:

(f) Query to display unique jobs from the employee Table.

 QUERY:

Select distinct job from employee;

 OUTPUT:

(g) Query to display the employee_name concatenated by a job separated by a

comma.

 QUERY:

Select employee_name||’,’|| job as employee_deatils from employee;

 OUTPUT:
 DEPARTMENT TABLE WITH DEPARTMENT_
NUMBER AS A PRIMARY KEY

AIM:

13. Create a DEPARTMENT table with the attributes of department_number and

department_name. Set the department number as a primary key.

 QUERY:

Create table department(

department_number int primary key,
department_name varchar(15));

 Insert some values:

Insert into department values(10,’sales’);

Insert into department values(20,’designing’);
Insert into department values(30,’marketing’);

(a) Create an Employee table with the following attributes: employee_number,

name, job_type, department_number and location.

 QUERY:

Create table employee(

employee_number int primary key,
name varchar(15),
job_type varchar(15),
department_number int,
location varchar(15),
foreign key(department_number)
references department(department_number));
  insert some values:

insert into employee values(1,’john’,’manager’,10,’mumbai’);

insert into employee values(2,’daisy’,’developer’,20,’delhi’);
insert into employee values(3,’princy’,’designer’,30,’mumbai’);
insert into employee values(4,’steve’,’artist’,20,’mumbai’);
insert into employee values(5,’jeni’,’writer’,10,’chennai’);

(b) Query to display Unique Listing of all Jobs that are in department_number 20.

 QUERY:

Select distinct job_type from employee where department_number=20;

 OUTPUT:

(c) Query to display employee name, department_name and department_number

for all the employees.

 QUERY:

Select e.name, d.department_name, e.department_number from employee e join

department d on e.department_number = d.department_number;

 OUTPUT:
(d) Query to display name, Job, department_number and department_name for all
the employees working at the Mumbai location.

 QUERY:

Select e.name, e.Job_type,e.department_number,d.department_name from

employee e join department d on e.department_number=d.department_number
where e.location=’mumbai’;

 OUTPUT:
 CLIENT_MASTER

AIM:

14.Create a table client-master with the following fields: client_no, name, address,
city, state, pincode, remarks, bal_due with suitable data types.

 QUERY:

Create table client_master (

client_no int primary key,
name varchar(15),
address varchar(20),
city varchar(10),
state varchar(15),
pincode varchar(10),
remarks varchar(20),
bal_due decimal(10, 2));

(a) Create another table supplier_master from client_master.

 QUERY:

Create table supplier_master as select * from client_master;

 OUTPUT:

(b) rename the attribute client_no with supplier_no and the attribute name with
supplier_name in the supplier_master table.

 QUERY:

Alter table supplier_master rename column client_no to supplier_no;

Alter table supplier_master rename column name to supplier_name;
  OUTPUT:

(c) Insert data into client_master.

 QUERY:

Insert into client_master

values(1,’sri’,’68,nellaistreet’,’karaikudi’,’TN’,630001,’regular client’,100.50);
Insert into client_master
values(2,’madhu’,’72,raja street,’,’thiruchur’,’kerala’,631002,’regular client’,250.50);
Insert into client_master
values(3,’sam’,’37,palm street’,’madurai’,’TN’,630003,’new client’,350.03);
Insert into client_master values
(4,’ravi’,’29,oak street’,’palakad’,’kerela’,631004,’frequent client’,150.50);
insert into client_master values
(5,’raja’,’12,wood street’,’chennai’,’TN’,630003,’new client’,175.00);

(d) Insert data into supplier_master from client_master.

 QUERY:

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;

 OUTPUT:
 (e) Delete the row which is having the value chennai in the city attribute of
client_master table.

 QUERY:

delete from client_master where city = 'chennai';

 OUTPUT:

(f) Drop the client_master table.

 QUERY:

drop table client_master;

 OUTPUT:
 Insert,Update,delete operations

AIM:

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.

 QUERY:

Create table master_book (

magazine_code int primary key,
magazine_name varchar(100),
publisher varchar(100),
magazine_type varchar(20) check (magazine_type in ('weekly',
'biweekly', 'monthly')),
price decimal(10, 2));

 Open note pad and insert values:

begin
insert into master_book values (101, 'tech times', 'tech publishers', 'monthly',150.00);
insert into master_book values (102, 'health weekly', 'health media', 'weekly', 50.00);
insert into master_book values (103, 'business digest', 'biz publishers', 'biweekly', 100.00);
insert into master_book values (104, 'science explorer', 'science world', 'monthly', 200.00);
insert into master_book values (105, 'travel globe', 'adventure press', 'weekly', 75.00);
end;

 Values inserted in notepad:

  Steps for PL/SQL to perform insert,update and delete:

 Step1: After inserting all the values in Notepad, save the file with the .sql
extension, and store it in the correct directory where Oracle is installed on
your system.

 Step2: Then use < set serveroutput on; > to enable the display of output
from PL/SQL block.

 Step3: < get > is used to retrieve the data’s from the database.

 QUERY:
set serveroutput on;
get F:\mb1.sql;
 OUTPUT:

 Step4: < / > It tells the system to run the PL/SQL code that has been entered.

 Step5: Then use select command to display the inserted values in the table.

 QUERY:
Select * from master_book;

 OUTPUT:
  Step6: Repeat the same process as we did for inserting values, but this time
for updating records.

 Step7: Type the SQL commands in Notepad to update values.

 QUERY:

begin
update master_book set price=350 where magazine_code=101;
update master_book set price=150 where magazine_name=’Tech times’;
update master_book set publisher= 'JK Press' where magazine_code=103;
end;

 OUTPUT:

 Step8: Repeat the same process that we did before.

 QUERY:

set serveroutput on;

get F:\mb2.sql;

 OUTPUT:
  Step9: use SELECT command to view the table after update.

 QUERY:

Select * from master_book;

 OUTPUT:

 Step10: Repeat the same process for deletion.

 Stpe11: Type the SQL command in Notepad.

 QUERY:

begin
delete from master_book where magazine_code=105;
end;

 OUTPUT:

 QUERY:

set serveroutput on;

get F:\mb3.sql;
 OUTPUT:

 QUERY:

Select * from master_book;

 OUTPUT:
 FUNCTION

AIM:

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.

 QUERY:

create table phone_users (

phone_number varchar(15) primary key,
user_name varchar(15),
address varchar(20));

 Insert some values:

insert into phone_users values ('1234567890', 'john', '123 maple st');

insert into phone_users values ('9876543210', 'jane', '456 oak st');
insert into phone_users values ('5551234567', 'alice', '789 pine st');
insert into phone_users values ('4449876543', 'bob', '321 cedar st');
insert into phone_users values ('3332221111', 'charlie', '654 elm st');

 Steps to create function:

 Step1: Open a new Notepad file.

 Step2: Type the following function code.

 QUERY:
  Step3: Save the file as create_function.sql.

 Step4: Run this file in your SQL environment to create the function.

 OUTPUT:

 Test the Function

 Step5: Open a new Notepad file.

 Step6: Type the following block to test the function:

 OUTPUT:

 Step7: Save the file as ph1.sql.

 Step8: Run this file in your SQL environment. It will prompt you to enter a
phone number and then display the address.
 OUTPUT:
 CURSOR

AIM:

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.

 QUERY:

create table salary_details (

employee_number number primary key,
employee_name varchar(15),
bp number(8),
pf number(8),
da number(8),
lic number(8),
hra number(8),
gp number(8),
np number(8));

 Insert some values:

insert into salary_details values

(1001, 'john', 50000, 5000,10000,2000,8000,null,null);
insert into salary_details values
(1002, 'jane', 60000,6000,12000,2500,9000,null,null);
insert into salary_details values
(1003, 'alice', 55000,5500,11000,1800,8500,null,null);
insert into salary_details values
(1004, 'bob', 65000,6500,13000,2200,9500,null,null);
insert into salary_details values
(1005, 'charlie',70000,7000,14000,2700,10000,null,null);

 Update gp and np

 QUERY:

update salary_details set gp=bp+(da+hra);

update salary_details set np=gp-(pf+lic);
 OUTPUT:

 After updating the values:

 QUERY:

Select * from salary_details;

 OUTPUT:

 Steps to create the procedure:

 Step1: Open Notepad.

 Step2: Type the procedure definition.

 OUTPUT:
  Step3: Save the file with a .sql extension, e.g.sal1.sql.

 Step4: Run the file in your SQL environment to create or replace the
procedure.

 OUTPUT:

 Step5:To run the procedure, use the following PL/SQL query.

 QUERY:

exec update_salaries;

 OUTPUT:
 DISPLAY THE VALUES AFTER UPDATING:

 QUERY:

Select * from salary_details;

 OUTPUT:
 TRIGGER
AIM:

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.

 QUERY:
Create table voters (
voter_no varchar(5),
voter_name varchar(10),
dob date,
age number(4),
gender varchar2(10),
address varchar2(20));
 Insert some values:

insert into voters values

('vn21', 'shruthi', '09-mar-2004', 19, 'female', 'no.22, thirunagar');
insert into voters values
('vn22', 'raj', '01-jun-2020', 21, 'male',’18, kalainagar');
insert into voters values
('vn23', 'devi', '19-jan-2005', 22, 'female', '02, main st');
insert into voters values
('vn24', 'prem', '20-feb-2006', 20, 'male', 'no:7, kalainagar');
insert into voters values
('vn25', 'sana', '19-mar-2011', 24, 'female', 'no.10, kknagar');

 Display the values:

 QUERY:
Select * from voters;
 OUTPUT:
 Create voters1 Table from voters to backup the data.

 QUERY:

Create table from voters1 as select * from voters;

 Output:

 Create Trigger for Deletion:

 QUERY:

 OUTPUT:

 OUTPUT:
 Delete a Row from voters:

 QUERY:
delete from voters where voter_number=’vn24’
 OUTPUT:

 Create Trigger for Updates:

 QUERY:

 OUTPUT:

 OUTPUT:
 Update a Row in voters:

 QUERY:

update voters set address='no.5,palaninagar' where voter_no='vn22';

 OUTPUT:
 PROCEDURE

AIM:

19. Create a table employee to contain the information of employee_name,

employee_number and salary.

 QUERY:
create table employee(
employee_no number (5),
employee_name varchar(15),
deptno number (5),
Salary decimal (10,2));

 Insert some values:

insert into employee values (101, 'srija', 10, 50000.00);

insert into employee values (102, 'thara', 20, 45000.00);
insert into employee values (103, 'premji', 10, 30000.00);
insert into employee values (104, 'kavin', 30, 55000.00);
insert into employee values (105, 'lalitha', 10, 40000.00);

 Query to display the table:

Select * from employee;

 OUTPUT:
(a) Write a procedure to increase 10% of salary to all employees (procedure
without argument).

 QUERY:

 OUTPUT:

 Query to display the updated values.

exec inc_sal;
 OUTPUT:

 Display the values:

 QUERY:
Select * from employee;
 OUTPUT:
(b) Write a procedure to increase specific percentage for specific department
number (procedure with argument).

 QUERY:

 OUTPUT:

 Query to display the updated percentage values.

exec inc_sal_dept(10,15);

 OUTPUT:
 Display the values:

Select * from employee;

 OUTPUT: