S.

NO DATE NAME OF THE PROGRAM PAGE STAFF

NO SIGNATURE

10

CONTENTS
 CONTENTS

S.NO DATE NAME OF THE PROGRAM PAGE STAFF

NO SIGNATURE

9
 GROUP-A

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

AIM:

PROCEDURE:

QUERIES:
Table Creation student_table:

CREATE TABLE student11(

name VARCHAR(51),

register_number INT PRIMARY KEY,

department VARCHAR(51),

subject1 INT,

subject2 INT,

subject3 INT,

subject4 INT,

subject5 INT,

total INT

);

Table created.

desc student11
Object Type TABLE ObjectSTUDENT11

Data Leng Precisi Sca Primary Nulla Defa Comme

Table Column Type th on le Key ble ult nt

STUDEN
NAME Varchar2 51 - - - - -
T11

REGISTER_NU
Number - - 0 1 - - -
MBER

DEPARTMENT Varchar2 51 - - - - -

SUBJECT1 Number - - 0 - - -

SUBJECT2 Number - - 0 - - -

SUBJECT3 Number - - 0 - - -

SUBJECT4 Number - - 0 - - -

SUBJECT5 Number - - 0 - - -

TOTAL Number - - 0 - - -
(a) Insert few records into student table.

INSERT INTO
student11(name,register_number,department,subject1,subject2,subject3,subject4,subject5,total)

VALUES('Alice',101,'Computer Science',85,90,88,92,87,442);

1 row(s) inserted.

INSERT INTO
student11(name,register_number,department,subject1,subject2,subject3,subject4,subject5,total)

VALUES('Bob',102,'Mechanical Engineering',75,80,78,82,77,392);

1 row(s) inserted.

INSERT INTO
student11(name,register_number,department,subject1,subject2,subject3,subject4,subject5,total)

VALUES('Charlie',103,'Electrical Engineering',65,70,68,72,67,342);

1 row(s) inserted.

(b) Display all the records

SELECT*FROM student11;

NA REGISTER_NU DEPARTM SUBJE SUBJE SUBJE SUBJE SUBJE TOT

ME MBER ENT CT1 CT2 CT3 CT4 CT5 AL
Computer
Alice 101 85 90 88 92 87 442
Science
Mechanical
Bob 102 75 80 78 82 77 392
Engineering
Charli Electrical
103 65 70 68 72 67 342
e Engineering

Total

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

SELECT name,register_number,department,subject1,subject2,subject3,subject4, subject5,

(subject1+subject2+subject3+subject4+subject5) AS total FROM student11;

NA REGISTER_NU DEPARTM SUBJE SUBJE SUBJE SUBJE SUBJE TOT

ME MBER ENT CT1 CT2 CT3 CT4 CT5 AL
Computer
Alice 101 85 90 88 92 87 442
Science
Mechanical
Bob 102 75 80 78 82 77 392
Engineering
Charli Electrical
103 65 70 68 72 67 342
e Engineering
(d) Display the information of student name, register number and total
only.
SELECT name,register_number,total FROM student11;

NAME REGISTER_NUMBER TOTAL

Alice 101 442
Bob 102 392
Charlie 103 342

RESULT:
EX.NO : 2
Create a student table with the following attributes name, register number,
department, marks in 5 subjects and total
DATE :

AIM:

PROCEDURE:
QUERIES:
Table Creation student_table:

CREATE TABLE student15(

name VARCHAR(50),

register_number INT PRIMARY KEY,

department VARCHAR(50),

subject1 INT,

subject2 INT,

subject3 INT,

subject4 INT,

subject5 INT,

total INT

);

Table created

desc student15

Object TypeTABLE ObjectSTUDENT15

Data Leng Precisi Scal Primary Nullab Defau Comme

Table Column Type th on e Key le lt nt

STUDEN
NAME Varchar2 50 - - - - -
T15

REGISTER_NU
Number - - 0 1 - - -
MBER

DEPARTMENT Varchar2 50 - - - - -

SUBJECT1 Number - - 0 - - -

SUBJECT2 Number - - 0 - - -

SUBJECT3 Number - - 0 - - -

SUBJECT4 Number - - 0 - - -

SUBJECT5 Number - - 0 - - -

TOTAL Number - - 0 - - -
 (a) Insert few records into student table.

INSERT INTO
student15(name,register_number,department,subject1,subject2,subject3,subject4,subject5,total)

VALUES('Alice',211278019,'Computer Science',85,90,88,92,87,442);

1 row(s) inserted.

INSERT INTO
student15(name,register_number,department,subject1,subject2,subject3,subject4,subject5,total)

VALUES('Bob',211278005,'Mechanical Engineering',75,90,78,82,77,392);

1 row(s) inserted.

INSERT INTO
student15(name,register_number,department,subject1,subject2,subject3,subject4,subject5,total)

VALUES('Charlie',211278003,'Electrical Engineering',65,70,68,72,67,342);

1 row(s) inserted.

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

UPDATE student15

SET name='Vignesh'

WHERE register_number=211278019;

1 row(s) updated
.
(c) Delete the records whose register number is 211278005.

DELETE FROM student15

WHERE register_number=211278005;
1 row(s) deleted.
(d) Display all the records.

SELECT*FROM student15;

NA REGISTER_NU DEPARTM SUBJE SUBJE SUBJE SUBJE SUBJE TOT

ME MBER ENT CT1 CT2 CT3 CT4 CT5 AL
Vigne Computer
211278019 85 90 88 92 87 442
sh Science
Charli Electrical
211278003 65 70 68 72 67 342
e Engineering

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

AIM:

PROCEDURE:
QUERIES:
Table Creation student_table:

CREATE TABLE student17(

name VARCHAR(100)CHECK(name=UPPER(name)),

roll_number INT CHECK(roll_number>0),

gender VARCHAR(10)CHECK(gender IN('Male','Female','Transgend')),

age INT NOT NULL,

mobile_number VARCHAR(15)

);
Table created.

desc student17

Object TypeTABLE ObjectSTUDENT17

Data Leng Precisi Sca Primary Nulla Defa Comme

Table Column Type th on le Key ble ult nt

STUDENT107 NAME Varchar2 100 - - - - -

ROLL_NUMB
Number - - 0 - - -
ER

GENDER Varchar2 10 - - - - -

AGE Number - - 0 - - - -

MOBILE_NU
Varchar2 15 - - - - -
MBER

1-5

INSERT INTO student17(name,roll_number,gender,age,mobile_number)

VALUES('ALICE',2024,'Male',12,1234567891);

1 row(s) inserted

INSERT INTO student17(name,roll_number,gender,age,mobile_number)

VALUES('KAVITHA',2025,'Female',12,1234567845);

1 row(s) inserted
INSERT INTO student17(name,roll_number,gender,age,mobile_number)

VALUES('KAYAL',2026,'Female',12,1234567234);
1 row(s) inserted

SELECT*FROM student17;

NAME ROLL_NUMBER GENDER AGE MOBILE_NUMBER

ALICE 2024 Male 12 1234567891
KAVITHA 2025 Female 12 1234567845
KAYAL 2026 Female 12 1234567234

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

SELECT name FROM student17;

NAME
ALICE
KAVITHA
KAYAL

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

SELECT roll_number FROM student17;

ROLL_NUMBER
2024
2025
2026

(c) The age cannot be a null value.

SELECT age FROM student17;

AGE
12
12
12

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

SELECT gender FROM student17;

GENDER
Male
Female
Female
 (e) The mobile number may contain null values.

SELECT mobile_number FROM student17;

MOBILE_NUMBER
1234567891
1234567845
1234567234

RESULT:

EX.NO : 4
 Create a table student_master with the following attributes name,
DATE :
regno, dept and year of joining with suitable data types. Use Select
command to do the following.

AIM:

PROCEDURE:

QUERIES:
create table student35(

name varchar(10),

regno number(3),

dept varchar(5),

year number(4));

Table created.

insert into student35 values('nagaraj',123,'tamil',1978);

1 row(s) inserted

insert into student35 values('durga',124,'eng',1980);

1 row(s) inserted

insert into student35 values('rathi',125,'cs',1999);

1 row(s) inserted

insert into student35 values('rathi',125,'cs',1999);

1 row(s) inserted

insert into student35 values('sathya',126,'mat',2008);

1 row(s) inserted

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

select*from student35;
NAME REGNO DEPT YEAR
nagaraj 123 tamil 1978
durga 124 eng 1980
rathi 125 cs 1999
rathi 125 cs 1999
sathya 126 mat 2008

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

select name from student35;

NAME
nagaraj
durga
rathi
rathi
sathya
(c) Eliminate the duplicate entry in student_mastertable.

select distinct name,regno,dept,year from student35;

NAME REGNO DEPT YEAR

rathi 125 cs 1999
sathya 126 mat 2008
nagaraj 123 tamil 1978
durga 124 eng 1980

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

Department

select*from student35 where dept='cs';

NAME REGNO DEPT YEAR

rathi 125 cs 1999
rathi 125 cs 1999

(e) Sort the attribute name in alphabetical order

select*from student35 order by name ASC;

NAME REGNO DEPT YEAR

durga 124 eng 1980
nagaraj 123 tamil 1978
rathi 125 cs 1999
rathi 125 cs 1999
sathya 126 mat 2008

RESULT:
EX.NO : 5
Create a table sales_order_details with the s_order_no as primary key and
itcontains the following fields: product_no, description, qty_ordered,
DATE :
qty_disp,product_rate, profit_percent, sell_price, supplier_name. Use Select
command to do the following

AIM:

PROCEDURE:

QUERIES:
 Create table sales_order_details22:

CREATE TABLE sales_order_details22(

s_order_no INT PRIMARY KEY,
product_no INT,
description VARCHAR(225),
qty_ordered INT,
qty_disp INT,
product_rate DECIMAL(10,2),
profit_percent DECIMAL(5,2),
sell_price DECIMAL(10,2),
supplier_name VARCHAR(100)
);

Table created.

Desc sales_order_details22

Data Leng Precis Sca Primary Nulla Defa Comm

Table Column Type th ion le Key ble ult ent
SALES_ORDER_DE S_ORDER_N
Number - - 0 1 - - -
TAILS22 O
PRODUCT_
Number - - 0 - - -
NO
DESCRIPTIO
Varchar2 225 - - - - -
N
QTY_ORDE
Number - - 0 - - -
RED

QTY_DISP Number - - 0 - - -

PRODUCT_R
Number - 10 2 - - -
ATE
PROFIT_PER
Number - 5 2 - - -
CENT

SELL_PRICE Number - 10 2 - - -

SUPPLIER_N
Varchar2 100 - - - - -
AME

Table created.

INSERT INTO
sales_order_details22(s_order_no,product_no,description,qty_ordered,qty_disp,product_rate,p
rofit_percent,sell_price,supplier_name)
VALUES(3456,123,'notes',45,56,67.0,0.50,1.50,'priya');

1 row(s) inserted.

INSERT INTO
sales_order_details22(s_order_no,product_no,description,qty_ordered,qty_disp,product_rate,profit_pe
rcent,sell_price,supplier_name)
VALUES(3455,125,'paper',45,56,68.0,0.50,1.50,'dharma');

1 row(s) inserted.
INSERT INTO
sales_order_details22(s_order_no,product_no,description,qty_ordered,qty_disp,product_rate,profit_pe
rcent,sell_price,supplier_name)
VALUES(3457,126,'book',45,56,69.0,0.50,1.50,'jim');

1 row(s) inserted.

SELECT*FROM sales_order_details22;

S_ORD PRODU DESCRI QTY_OR QTY_ PRODUC PROFIT_P SELL_ SUPPLIE

ER_NO CT_NO PTION DERED DISP T_RATE ERCENT PRICE R_NAME
3456 123 notes 45 56 67 .5 1.5 priya
3455 125 paper 45 56 68 .5 1.5 dharma
3457 126 book 45 56 69 .5 1.5 jim

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

SELECT s_order_no,product_no,description,
sell_price*0.50 AS sell_price_half,
sell_price*1.50 AS sell_price_one_and_half
FROM sales_order_details22;

S_ORDER_ PRODUCT_ DESCRIPTI SELL_PRICE_H SELL_PRICE_ONE_AND_

NO NO ON ALF HALF
3456 123 notes .75 2.25
3455 125 paper .75 2.25
3457 126 book .75 2.25

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

and 20 both inclusive.

SELECT product_no,profit_percent,sell_price
FROM sales_order_details22;

PRODUCT_NO PROFIT_PERCENT SELL_PRICE

123 .5 1.5
125 .5 1.5
126 .5 1.5

(c) Select product_no, description, profit_percent, sell_price where profit_percent is

not between 20 and 30.

SELECT product_no,description,profit_percent,sell_price
FROM sales_order_details22

WHERE profit_percent NOT BETWEEN 10 AND 22;

PRODUCT_NO DESCRIPTION PROFIT_PERCENT SELL_PRICE

 123 notes .5 1.5
125 paper .5 1.5
126 book .5 1.5

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

SELECT supplier_name,product_no
FROM sales_order_details22
WHERE supplier_name LIKE'_r%'OR supplier_name LIKE'_h%';

SUPPLIER_NAME PRODUCT_NO
priya 123
dharma 125

RESULT:
EX.NO : 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.
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table Employee:

CREATE TABLE Employee12(

Employee_number INT PRIMARY KEY,

name VARCHAR(100),

job VARCHAR(100),

manager_id VARCHAR(100)

);

Table created.

desc Employee12

Object TypeTABLE ObjectEMPLOYEE12

Data Len Preci Sca Primary Nulla Defa Comm

Table Column Type gth sion le Key ble ult ent

EMPLOYEE_
EMPLOYEE12 Number - - 0 1 - - -
NUMBER

NAME Varchar2 100 - - - - -

JOB Varchar2 100 - - - - -

MANAGER_I
Varchar2 100 - - - - -
D

1-4

(a) Insert few records

INSERT INTO Employee12(name,Employee_number,job,manager_id)

VALUES('Bharathi',93456,'Financial Analystic',2203416);

1 row(s) inserted.

INSERT INTO Employee12(name,Employee_number,job,manager_id)

VALUES('Kavitha',12346,'Administrator',2276549);

1 row(s) inserted.
INSERT INTO Employee12(name,Employee_number,job,manager_id)

VALUES('Pothumani',12347,'Information Technology',222456);

1 row(s) inserted.

(b) Display all the records

SELECT*FROM Employee12;

EMPLOYEE_NUMBER NAME JOB MANAGER_ID

93456 Bharathi Financial Analystic 2203416
12346 Kavitha Administrator 2276549
Pothuman
12347 Information Technology 222456
i

(c) Display the employee details who are working under particular
manager_id.
SELECT manager_id FROM Employee12;

MANAGER_ID
2203416
2276549
222456

RESULT:
EX.NO : 7 Create an Employee table with the following attributes: employee_number,
employee_name, department_number, job and salary.
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table Employee:
CREATE TABLE Employee29(
employee_number INT PRIMARY KEY,

employee_name VARCHAR(100),

department_number INT,

job VARCHAR(50),

salary DECIMAL(10,2)

);

Table created.

desc Employee29

Object TypeTABLE ObjectEMPLOYEE29

Data Len Preci Sc Primar Null Def Com

Table Column Type gth sion ale y Key able ault ment

EMPLOYEE2 EMPLOYEE_N Numbe

- - 0 1 - - -
9 UMBER r

EMPLOYEE_N Varcha
100 - - - - -
AME r2

DEPARTMENT Numbe
- - 0 - - -
_NUMBER r

Varcha
JOB 50 - - - - -
r2

Numbe
SALARY - 10 2 - - -
r

1-5

INSERT INTO Employee29(employee_number,employee_name,department_number,job,salary)

VALUES(56789,'saran',123,'IT',32000.00);

1 row(s) inserted.

INSERT INTO Employee29(employee_number,employee_name,department_number,job,salary)

VALUES(56786,'ranjani',126,'tamil',34000.00);

1 row(s) inserted.
SELECT*FROM Employee29;

EMPLOYEE_NUMBER EMPLOYEE_NAME DEPARTMENT_NUMBER JOB SALARY

56789 saran 123 IT 32000
56786 ranjani 126 tamil 34000

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

FROM Employee29

WHERE salary>30000;

EMPLOYEE_NAME SALARY
saran 32000
ranjani 34000

b) Query to display employee_name and department_number for the particular

employee _number.
SELECT employee_name,department_number

FROM Employee29

WHERE employee_number=56786;

EMPLOYEE_NAME DEPARTMENT_NUMBER
ranjani 126

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

SELECT employee_name,salary

FROM Employee29

WHERE salary<15000 OR salary>30000;

EMPLOYEE_NAME SALARY
saran 32000
ranjani 34000

RESULT:
EX.NO : 8 Create an Employee table with the following attribute employee_number,
employee_name, job_type, hire_date, department_number and salary.

DATE :

AIM:

PROCEDURE:
QUERIES:
Create table Employee:

create table emp36(

empno int,
name varchar(10),
job varchar(10),
hire_date date,
deptno int,
salary int);

Table created

desc emp36

Tabl Data Lengt Precisi Scal Primary Nullab Defau Comme

e Column Type h on e Key le lt nt
EMP3
EMPNO Number - - 0 - - -
6

NAME Varchar2 10 - - - - -

JOB Varchar2 10 - - - - -

HIRE_DA
Date 7 - - - - -
TE

DEPTNO Number - - 0 - - -

SALARY Number - - 0 - - -

insert into emp35 values(123,'aadhi','hr',to_date('23/07/2024','dd/mm/yyyy'),10,40000);

1 row(s) inserted.

insert into emp35 values(124,'raji','sales',to_date('12/07/2024','dd/mm/yyyy'),20,10000);

1 row(s) inserted.

insert into emp35 values(125,'shanmuga','manager',to_date('3/01/2024','dd/mm/yyyy'),30,30000);

1 row(s) inserted.

insert into emp35 values(126,'perumal','supervisor',to_date('24/12/2024','dd/mm/yyyy'),23,20000);

1 row(s) inserted.

select*from emp35;

EMPNO NAME JOB HIRE_DATE DEPTNO SALARY

123 aadhi hr 23-JUL-24 10 40000
124 raji sales 12-JUL-24 20 10000
125 shanmuga manager 03-JAN-24 30 30000
126 perumal supervisor 24-DEC-24 23 20000
123 aadhi hr 23-JUL-24 10 40000
124 raji sales 12-JUL-24 20 10000
125 shanmuga manager 03-JAN-24 30 30000
126 perumal supervisor 24-DEC-24 23 20000
(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.

select name,deptno from emp35 where deptno in(10,20) order by name ASC;

NAME DEPTNO
aadhi 10
aadhi 10
raji 20
raji 20

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

select*from emp35 where name like'__a%';

EMPNO NAME JOB HIRE_DATE DEPTNO SALARY

125 shanmuga manager 03-JAN-24 30 30000
125 shanmuga manager 03-JAN-24 30 30000

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

select initcap(name)from emp35;

INITCAP(NAME)
Aadhi
Raji
Shanmuga
Perumal
Aadhi
Raji
Shanmuga
Perumal
(d) Query to display Name of all employees either have two R‘s
or have two A‘s in their Name.

select*from emp35 where name like'%rr%'or name like'%aa%';

EMPNO NAME JOB HIRE_DATE DEPTNO SALARY

123 aadhi hr 23-JUL-24 10 40000
123 aadhi hr 23-JUL-24 10 40000

RESULT:
EX.NO : 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.
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table Employee:

create table employee9(employee_number int

primary key,name varchar(10),

job varchar(10),hiredate date,

manager_id int,foreign key(manager_id)

references employee9(employee_number));

Table created.

desc employee9;

Table Column Data Type Length Precision Scale Primary Key Nullable Default Comment
EMPLOYEE_NUMBE
EMPLOYEE9 Number - - 0 1 - - -
R
NAME Varchar2 10 - - - - -

JOB Varchar2 10 - - - - -

HIREDATE Date 7 - - - - -

MANAGER_ID Number - - 0 - - -

1-5

insert into employee9 values(111,'maddy','manager',to_date('29/11/2004','dd/mm/yyyy'),111);

1 row(s) inserted.

insert into employee9 values(112,'kannan','manager',to_date('05/07/2004','dd/mm/yyyy'),112);

1 row(s) inserted.

insert into employee9 values(113,'pavi','assistant',to_date('01/05/2005','dd/mm/yyyy'),113);

1 row(s) inserted.

insert into employee9 values(114,'ramesh','supervisor',to_date('21/12/2006','dd/mm/yyyy'),null);

1 row(s) inserted.

insert into employee9 values(115,'agash','supervisor',to_date('21/12/2007','dd/mm/yyyy'),null);

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

select name,hiredate from employee9

where extract(year from hiredate)

=2007;

NAME HIREDATE

agash 21-DEC-07

(b) Query to display name and calculate the number of

months between today and the date each employee was
hired.

select name,hiredate,months_between

(sysdate,hiredate)"months"

from employee9;

NAME HIREDATE Months

maddy 29-NOV-04 237.918338933691756272401433691756272401
kannan 05-JUL-04 242.692532482078853046594982078853046595
pavi 01-MAY-05 232.821564740143369175627240143369175627
ramesh 21-DEC-06 213.176403449820788530465949820788530466
agash 21-DEC-07 201.176403449820788530465949820788530466

(c) Query to display name and job of all employees who

don‘t have a current Manager.

select name,job from employee9

where manager_id is null;

NAME JOB
ramesh supervisor
agash supervisor

RESULT:
GROUP-B
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
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table Employee:

create table sales_order1(sales_order_no int,

client_no int,delivery_address varchar(30),

delivery_date date,order_status varchar(10),

primary key(sales_order_no));

Table created.

desc sales_order1

Colum Data Len Preci Sc Primar Null Def Com

Table n Type gth sion ale y Key able ault ment

SALES_OR
SALES_ORDER1 Number - - 0 1 - - -
DER_NO

CLIENT_N
Number - - 0 - - -
O

DELIVERY_
Varchar2 30 - - - - -
ADDRESS

DELIVERY_
Date 7 - - - - -
DATE

ORDER_ST
Varchar2 10 - - - - -
ATUS

1-5

insert into sales_order1

values(11,21,' voc street palani',to_date('12/01/2024','dd/mm/yyyy'),'delivered');

1 row(s) inserted.

insert into sales_order1

values(12,22,'k,k,nagar chennai',to_date('14/02/2024','dd/mm/yyyy'),'shipped');

1 row(s) inserted.
insert into sales_order1

values(13,33,'kandhinagar',to_date('24/03/2024','dd/mm/yyyy'),'delivered');

1 row(s) inserted.

select*from sales_order1;

SALES_ORDER_N CLIENT_N DELIVERY_ADDRES DELIVERY_DAT ORDER_STATU

O O S E S
11 21 voc street palani 12-JAN-24 delivered
12 22 k,k,nagar chennai 14-FEB-24 shipped
13 33 kandhinagar 24-MAR-24 delivered

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

create table sales_order1_copy parallel as

select*from sales_order1;

Table created.

select*from sales_order1_copy;

SALES_ORDER_N CLIENT_N DELIVERY_ADDRES DELIVERY_DAT ORDER_STATU

O O S E S
11 21 voc street palani 12-JAN-24 delivered
12 22 k,k,nagar chennai 14-FEB-24 shipped
13 33 kandhinagar 24-MAR-24 delivered

alter table sales_order1_copy add unique(sales_order_no);

Table altered.

(b) Add a new column for storing salesman_number in

sales_order using ALTER Command.

alter table sales_order1 add salesman_no int;

Table altered.

update sales_order1 set salesman_no=50

where sales_order_no in('11');

1 row(s) updated.
update sales_order1 set salesman_no=52

where sales_order_no in('12','14');

1 row(s) updated.

update sales_order1 set salesman_no=55

where sales_order_no in('13','15');

1 row(s) updated.

select*from sales_order1;

SALES_ORDER CLIENT_ DELIVERY_ADD DELIVERY_D ORDER_STA SALESMAN_

_NO NO RESS ATE TUS NO
11 21 voc street palani 12-JAN-24 delivered 50
12 22 k,k,nagar chennai 14-FEB-24 shipped 52
13 33 kandhinagar 24-MAR-24 delivered 55

(c) Modify the size of delivery_address in sales_order table

using ALTER command.

alter table sales_order1 modify delivery_address

varchar(20);

Table altered.

(d) Display the structure of sales_order table

select*from sales_order1;

SALES_ORDER CLIENT_ DELIVERY_ADD DELIVERY_D ORDER_STA SALESMAN_

_NO NO RESS ATE TUS NO
11 21 voc street palani 12-JAN-24 delivered 50
12 22 k,k,nagar chennai 14-FEB-24 shipped 52
13 33 kandhinagar 24-MAR-24 delivered 55

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

AIM:

PROCEDURE:
QUERIES:
Create table Employee:

create table Employee09(

employee_no int

,employee_name varchar(10),

job_type varchar(10),

hire_date date,

dept_no int,

salary number(10,2),

commission number(10,2));

Table created.

desc Employee09;

Data Lengt Precisio Scal Primary Nullabl Defaul Commen

Table Column Type h n e Key e t t

EMPLOYEE09 EMPLOYEE_NO Number - - 0 - - -

EMPLOYEE_NA
Varchar2 10 - - - - -
ME

JOB_TYPE Varchar2 10 - - - - -

HIRE_DATE Date 7 - - - - -

DEPT_NO Number - - 0 - - -

SALARY Number - 10 2 - - -

COMMISSION Number - 10 2 - - -

1-7

insert into Employee09

values(111,'perumal','manager',to_date('23/12/24','dd/mm/yyy'),10,19999.00,4567.00);
1 row(s) inserted.

insert into Employee09

values(114,'nagaraj','supervisor',to_date('12/11/24','dd/mm/yyy'),20,15476.00,3500.00);
1 row(s) inserted.
insert into Employee09
values(123,'kavi','incharge',to_date('13/3/24','dd/mm/yyy'),30,43567.00,5467.00);
1 row(s) inserted.

select*from Employee09;

EMPLOYEE_NO EMPLOYEE_NAME JOB_TYPE HIRE_DATE DEPT_NO SALARY COMMISSION

111 perumal manager 23-DEC-24 10 19999 4567
111 perumal manager 23-DEC-24 10 19999 4567
114 nagaraj supervisor 12-NOV-24 20 15476 3500
123 kavi incharge 13-MAR-24 30 43567 5467

(a) Query to display the Highest, Lowest, Sum and Average

Salaries of
all the Employees
select max(salary) as highest_salary from Employee09;

HIGHEST_SALARY
43567
select min(salary) as lowest_salary from Employee09;

LOWEST_SALARY
15476
select sum(salary) as total_salary from Employee09;

TOTAL_SALARY
99041
select avg(salary) as average_salary from Employee09;

AVERAGE_SALARY
24760.25

(b) Query to display the employee_number and

employee_name for all employees who earn more than
the average salary.

select employee_no,employee_name from Employee09 where salary > (select avg(salary) from
Employee09);

EMPLOYEE_NO EMPLOYEE_NAME
123 kavi

(c) Query to display the employee_name, salary and

commission for all the employees who earn commission.

select employee_name,salary,commission from Employee09 where commission is not null

and commission > 0;
 EMPLOYEE_NAME SALARY COMMISSION
perumal 19999 4567
perumal 19999 4567
nagaraj 15476 3500
kavi 43567 5467

(c) Sort the data in descending order of salary and

commission

select *from Employee09 order by salary ,commission DESC;

EMPLOYEE_NO EMPLOYEE_NAME JOB_TYPE HIRE_DATE DEPT_NO SALARY COMMISSION

114 nagaraj supervisor 12-NOV-24 20 15476 3500
111 perumal manager 23-DEC-24 10 19999 4567
111 perumal manager 23-DEC-24 10 19999 4567
123 kavi incharge 13-MAR-24 30 43567 5467

(d) Query to display employee_name, salary and

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

select employee_name,salary,commission ,salary*0.05 as"salary*0.05"from Employee09 where

commission > salary* 0.05;

EMPLOYEE_NAME SALARY COMMISSION Salary*0.05

perumal 19999 4567 999.95
perumal 19999 4567 999.95
nagaraj 15476 3500 773.8
kavi 43567 5467 2178.35
 RESULT:

EX.NO : 12
Create a DEPARTMENT table with the attributes of department_number
and department_name. Set the department_ number as a primary key.
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table Department:

create table department12(department_number int primary key,

department_name varchar(15));
Table created.

desc department12;

Data Lengt Precisio Scal Primary Nullabl Defaul Comme

Table Column Type h n e Key e t nt

DEPARTMENT_NUM
DEPARTMENT12 Number - - 0 1 - - -
BER

DEPARTMENT_NAM
Varchar2 15 - - - - -
E

1-2

(a) Insert few records

insert into department12 values(10,'cs');

1 row(s) inserted.

insert into department12 values(20,'his');

1 row(s) inserted.

insert into department12 values(30,'eco');

1 row(s) inserted

insert into department12 values(40,'phy');

1 row(s) inserted

insert into department12 values(50,'eng');

1 row(s) inserted

insert into department12 values(60,'tam');

1 row(s) inserted

(b) Display all the records

select*from department12;
 DEPARTMENT_NUMBER DEPARTMENT_NAME
10 cs
20 his
30 eco
40 phy
50 eng
60 tam

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

create table employee12(employee_number int,

employee_name varchar(10),job varchar(10),

department_number int,primary key(employee_number),

foreign key(department_number)references

department12(department_number));
Table created.

desc employee12;

Data Lengt Precisio Scal Primary Nullabl Defaul Commen

Table Column Type h n e Key e t t
EMPLOYEE12 EMPLOYEE_NUMBER Number - - 0 1 - - -
EMPLOYEE_NAME Varchar2 10 - - - - -

JOB Varchar2 10 - - - - -
DEPARTMENT_NUMB
Number - - 0 - - -
ER
1-4

insert into employee12 values(11,'vadivel','hod',10);

1 row(s) inserted.

insert into employee12 values(12,'kannan','teacher',20);

1 row(s) inserted.
insert into employee12 values(13,'maddy','incharge',30);

1 row(s) inserted.

insert into employee12 values(14,'agash','staff',40);

1 row(s) inserted.

insert into employee12 values(15,'pavi','supervisor',50);

1 row(s) inserted.

insert into employee12 values(16,'ramesh','incharge',60);

1 row(s) inserted.

select*from employee12;

EMPLOYEE_NUMBER EMPLOYEE_NAME JOB DEPARTMENT_NUMBER

11 vadivel hod 10
12 kannan teacher 20
13 maddy incharge 30
14 agash staff 40
15 pavi supervisor 50
16 ramesh incharge 60

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

the particular department_number.

select*from employee12 where employee_number='12';

EMPLOYEE_NUMBER EMPLOYEE_NAME JOB DEPARTMENT_NUMBER

12 kannan teacher 20

(e) Query to display employee_number, employee_name and

job from the employee table

select employee_number,employee_name,job from employee12;

EMPLOYEE_NUMBER EMPLOYEE_NAME JOB

11 vadivel hod
12 kannan teacher
13 maddy incharge
14 agash staff
15 pavi supervisor
16 ramesh incharge

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

select distinct job from employee12;

JOB
incharge
 teacher
staff
hod
supervisor

(g) Query to display the employee_name concatenated by a

job separated by a comma.
select employee_name ||','|| job as

"employee_name_job" from employee12;

Employee_name_job
vadivel,hod
kannan,teacher
maddy,incharge
agash,staff
pavi,supervisor
ramesh,incharge

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

DATE :

AIM:

PROCEDURE:
QUERIES:

Create table client_master:

Create table client_master1(client_no number(4),

name varchar(6),address varchar(15),

city varchar(10),state varchar(10),

pincode number(9),remarks varchar(3),

bal_due number(5));

Table created.

desc client_master1;

Le
Data ng Precis Sca Primary Nulla Defa Comm
Table Column Type th ion le Key ble ult ent

CLIENT_ CLIENT_N
Number - 4 0 - - -
MASTER1 O

NAME Varchar2 6 - - - - -

ADDRESS Varchar2 15 - - - - -

CITY Varchar2 10 - - - - -

STATE Varchar2 10 - - - - -

PINCODE Number - 9 0 - - -

REMARKS Varchar2 3 - - - - -

BAL_DUE Number - 5 0 - - -

(a) Create another table supplier_master from client_master.

Create table supplier_master1 as select * from client_master1;

Table created.
desc supplier_master1;

Data Leng Precisi Scal Primary Nullab Defau Comme

Table Column Type th on e Key le lt nt

SUPPLIER_MA CLIENT_
Number - 4 0 - - -
STER NO

NAME Varchar2 6 - - - - -

ADDRES
Varchar2 15 - - - - -
S

CITY Varchar2 10 - - - - -

STATE Varchar2 10 - - - - -

PINCOD
Number - 9 0 - - -
E

REMARK
Varchar2 3 - - - - -
S

BAL_DU
Number - 5 0 - - -
E

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

alter table supplier_master1 rename column client_no to supplier_no;

Table altered.

alter table supplier_master1 rename column name to supplier_name;

Table altered.

desc supplier_master1;
 Data Len Preci Sc Primar Null Def Com
Table Column Type gth sion ale y Key able ault ment

SUPPLIER_ SUPPLIER
Number - 4 0 - - -
MASTER _NO

SUPPLIER
Varchar2 6 - - - - -
_NAME

ADDRESS Varchar2 15 - - - - -

CITY Varchar2 10 - - - - -

STATE Varchar2 10 - - - - -

PINCODE Number - 9 0 - - -

REMARKS Varchar2 3 - - - - -

BAL_DUE Number - 5 0 - - -

(b) Insert data into client_master

insert into client_master1 values

(111,'kannan','kk street','sivagangi','tamilnadu',656466,'no',1090);

1 row(s) inserted.

insert into client_master1 values

(111,'agash','sri street','madurai','tamilnadu',624601,'no',1200);
1 row(s) inserted.

insert into client_master1 values

(111,'kishor','circle street','kovai','tamilnadu',624601,'no',1030);
1 row(s) inserted.

insert into client_master1 values

(111,'logesh','loki street','chennai','tamilnadu',624601,'no',1009);
1 row(s) inserted.

select*from client_master1;

CLIENT_NO NAME ADDRESS CITY STATE PINCODE REMARKS BAL_DUE

tamilnad
111 kannan kk street sivagangi 656466 no 1090
u
tamilnad
111 agash sri street madurai 624601 no 1200
u
tamilnad
111 kishor circle street kovai 624601 no 1030
u
tamilnad
111 logesh loki street chennai 624601 no 1009
u

(d) Insert data into supplier_master from client_master.

insert into supplier_master 1 select * from client_master1;

4 row(s) inserted.

select *from supplier_master;

SUPPLIER_N SUPPLIER_NA ADDRES STAT PINCOD REMARK BAL_DU

O ME S CITY E E S E
sivagan tamilnad
111 kannan kk street 656466 no 1090
gi u
tamilnad
111 agash sri street madurai 624601 no 1200
u
tamilnad
111 kishor circle street kovai 624601 no 1030
u
tamilnad
111 logesh loki street chennai 624601 no 1009
u

(f) Delete the row which is having the value chennai in the city attribute of
client_master table.

delete from client_master1 where city='chennai';

1 row(s) deleted.

(f) Drop the client_master table

select*from client_master3;

CLIENT_NO NAME ADDRESS CITY STATE PINCODE REMARKS BAL_DUE

tamilnad
111 kannan kk street sivagangi 656466 no 1090
u
tamilnad
111 agash sri street madurai 624601 no 1200
u
tamilnad
111 kishor circle street kovai 624601 no 1030
u
 RESULT:

EX.NO : 14 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
DATE :
operations on the above table

AIM:

PROCEDURE:
QUERIES:
Create table master book:

CREATE TABLE master_book6(

magazine_code INT PRIMARY KEY,

magazine_name VARCHAR(100),

magazine_type VARCHAR(100),

magazine_price VARCHAR(100)

);

Table created.

desc master_book5

Data Leng Precisi Sca Primary Nulla Defa Comme

Table Column Type th on le Key ble ult nt

MASTER_BO MAGAZINE_C
Number - - 0 1 - - -
OK5 ODE

MAGAZINE_N
Varchar2 100 - - - - -
AME

MAGAZINE_T
Varchar2 100 - - - - -
YPE

MAGAZINE_P
Varchar2 100 - - - - -
RICE

INSERT INTO master_book6(magazine_code,magazine_name,magazine_type,magazine_price)

VALUES(12345,'kalki','monthly',2000);

1 row(s) inserted.
INSERT INTO master_book6(magazine_code,magazine_name,magazine_type,magazine_price)

VALUES(12346,'science','weekly',4000);

1 row(s) inserted.

INSERT INTO master_book7(magazine_code,magazine_name,magazine_type,magazine_price)

VALUES(12347,'maths','biweekly',3000);

1 row(s) inserted.

SELECT*FROM master_book7;

MAGAZINE_CODE MAGAZINE_NAME MAGAZINE_TYPE MAGAZINE_PRICE

12345 kalki monthly 2000
12346 science weekly 4000
12347 maths biweekly 3000

INSERT INTO master_book7(magazine_code,magazine_name,magazine_type,magazine_price)

VALUES(12348,'kumutham','weekly',10);

1 row(s) inserted

SELECT*FROM master_book8;

MAGAZINE_CODE MAGAZINE_NAME MAGAZINE_TYPE MAGAZINE_PRICE

12345 kalki monthly 2000
12346 science weekly 4000
12347 maths biweekly 3000
12348 kumutham weekly 10

(a) PL/SQL block to perform insert, update and delete operations on the above table

UPDATE master_book7

SET magazine_name='India Today'

WHERE magazine_name='science';

1 row(s) inserted

SELECT*FROM master_book7;

MAGAZINE_CODE MAGAZINE_NAME MAGAZINE_TYPE MAGAZINE_PRICE

12345 kalki monthly 2000
12346 India Today weekly 4000
12347 maths biweekly 3000
12348 kumutham weekly 10
DELETE FROM master_book5

WHERE magazine_code=12346;

1 row(s) deleted.

SELECT*FROM master_book7;

MAGAZINE_CODE MAGAZINE_NAME MAGAZINE_TYPE MAGAZINE_PRICE

12345 kalki monthly 2000
12347 maths biweekly 3000
12348 kumutham weekly 10
 RESULT:

EX.NO : 15 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.
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table contain:

CREATE TABLE contain1(

phone_number INT PRIMARY KEY,

user_name VARCHAR(100),

address VARCHAR(100)

);

Table created.

desc contain1

Data Leng Precisi Sca Primary Nulla Defa Comme

Table Column Type th on le Key ble ult nt

PHONE_NUM
CONTAIN1 Number - - 0 1 - - -
BER

USER_NAME Varchar2 100 - - - - -

ADDRESS Varchar2 100 - - - - -

1-3

INSERT INTO contain1(phone_number ,user_name ,address)

VALUES(9923456172,'DHARMA','NO.556,chengalpattu,chennai,658360');

1 row(s) inserted.

INSERT INTO contain1(phone_number ,user_name ,address)

VALUES(9923453452,'POTHU','NO.545,ram nagar,trichy,630720');

1 row(s) inserted.
INSERT INTO contain1(phone_number ,user_name ,address)

VALUES(9923421673,'KAVITHA','NO.560,allapy,kerala,360230');

1 row(s) inserted.

SELECT*FROM contain1;

PHONE_NUMBER USER_NAME ADDRESS

9923456172 DHARMA NO.556,chengalpattu,chennai,658360
9923453452 POTHU NO.545,ram nagar,trichy,630720
9923421673 KAVITHA NO.560,allapy,kerala,360230

SELECT address,user_name FROM contain1

WHERE phone_number=9923453452;

ADDRESS USER_NAME
NO.545,ram nagar,trichy,630720 POTHU
 RESULT:

EX.NO : 16
Create a table to store the salary details of the employees in a company.
Declare the cursor to contain employee_number, employee_name and
DATE :
net_salary. Use cursor to update the employee salaries.

AIM:

PROCEDURE:
QUERIES:
Create table Employee:

CREATE TABLE Employee5(

employee_number INT PRIMARY KEY,
employee_name VARCHAR(100),
net_salary int
);

Table created.

desc Employee51

Object TypeTABLE ObjectEMPLOYEE51

Data Leng Precisi Sca Primary Nulla Defa Comm

Table Column Type th on le Key ble ult ent

EMPLOYEE5 EMPLOYEE_NU
Number - - 0 1 - - -
1 MBER

EMPLOYEE_NA
Varchar2 100 - - - - -
ME

NET_SALARY Number - - 0 - - -

1-3

INSERT INTO Employee51(employee_number,employee_name,net_salary)

VALUES(8072707504,'DHARMA',12000);

1 row(s) inserted.

INSERT INTO Employee51(employee_number,employee_name,net_salary)

VALUES(6323451232,'POTHU',19000);

1 row(s) inserted.

INSERT INTO Employee51(employee_number,employee_name,net_salary)

VALUES(6323519987,'KAVITHA',11000);
1 row(s) inserted.

SELECT*FROM Employee51;

EMPLOYEE_NUMBER EMPLOYEE_NAME NET_SALARY

8072707504 DHARMA 12000
6323451232 POTHU 19000
6323519987 KAVITHA 11000

UPDATE Employee51

SET net_salary=24000

WHERE Employee_name='DHARMA';

1 row(s) updated.

UPDATE Employee51

SET net_salary=25000

WHERE Employee_name='KAVITHA';

1 row(s) updated.

UPDATE Employee51
SET net_salary=30000
WHERE Employee_name='POTHU';

1 row(s) updated.

SELECT*FROM Employee51;

EMPLOYEE_NUMBER EMPLOYEE_NAME NET_SALARY

8072707504 DHARMA 24000
6323451232 POTHU 30000
6323519987 KAVITHA 25000
 RESULT:

EX.NO : 17 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.
DATE :

AIM:

PROCEDURE:
QUERIES:
Create table voters:

CREATE TABLE voters(

name VARCHAR(100),
voter_id VARCHAR(100),
gender VARCHAR(100),
Date_of_Birth VARCHAR(100),
Address VARCHAR(100)
);

Table created.

desc voters

Object TypeTABLE ObjectVOTERS

Data Lengt Precisi Scal Primary Nullab Defau Comme

Table Column Type h on e Key le lt nt

VOTERS NAME Varchar2 100 - - - - -

VOTER_ID Varchar2 100 - - - - -

GENDER Varchar2 100 - - - - -

DATE_OF_BI
Varchar2 100 - - - - -
RTH

ADDRESS Varchar2 100 - - - - -

1-
5

INSERT INTO voters(name,voter_id,gender,Date_of_Birth,Address)

VALUES('RAMESH','ABC162458','Male','07:01:2000','chennai');

1 row(s) inserted.

INSERT INTO voters(name,voter_id,gender,Date_of_Birth,Address)

VALUES('PAVITHIRAN','ACB136279','Male','22:05:2002','trichy');
1 row(s) inserted.

INSERT INTO voters(name,voter_id,gender,Date_of_Birth,Address)

VALUES('MADHAVAN','ADC749356','Male','03:06:2008','madurai');

1 row(s) inserted.

SELECT*FROM voters;

NAME VOTER_ID GENDER DATE_OF_BIRTH ADDRESS

RAMESH ABC162458 Male 07:01:2000 chennai
PAVITHIRAN ACB136279 Male 22:05:2002 trichy
MADHAVAN ADC749356 Male 03:06:2008 madurai

DELETE FROM voters

WHERE name='MADHAVAN';

1 row(s) deleted.

SELECT*FROM voters;

NAME VOTER_ID GENDER DATE_OF_BIRTH ADDRESS

RAMESH ABC162458 Male 07:01:2000 chennai
PAVITHIRAN ACB136279 Male 22:05:2002 trichy

RESULT: