1 THE ORDER TRACKING DATABASE
The order tracking database consists of the following defined six relation schemes.
1. zipcodes (zip, city)
2. Employees (eno, ename, zip, hdate)
3. Parts (pno, pname, qoh, price, level)
4. Customers (cno, cname, street, zip, phone)
5. Orders (ono, cno, eno, received_date, shipped_date)
6. Odetails (ono, pno, qty)
CREATING TABLES
Table 1
CREATE TABLE zipcodes (
zip number (10) PRIMARYKEY,
city varchar2 (30)),
Insert the following data:
ZIP CITY
517642 DODGE
517715 WICHITA
517643 DELHI
517644 LIBERAL
517501 TIRUPATHI
517640 SRIKALAHASTHI
To insert the rows in the table type the following command at SQL Prompt.
SQL>INSERT INTO Zipcodes Values (&ZIP,’&CITY’);
Table 2:
CREATE TABLE Employees
(ENO VARCHAR2 (5) PRIMARYKEY,
ENAME VARCHAR2 (30),
ZIP NUMBER (10) REFERENCES ZIPCODES,
HDATE DATE);
Insert the following data:
ENO ENAME ZIP HDATE
E1002 ANUSHA 517640 02-APR-10
E1003 SHARMILA 257642 17-MAY-10
E1004 VARDHAN 517644 21-JAN-10
E1000 MADHUSUDHANREDDY 517644 10-JAN-10
E1005 K.SAMBASIVARAO 517501 04-FEB-10
E1001 VIJAYKUMAR 517501 27-MAR-10
E1006 JAYAKRISHNA 517715 16-JUN-10
E1007 ARUNPRASAD 517642 30-JUL-10
E1008 SUDARSAN 517643 26-AUG-10
To insert the rows in the table type the following command at SQL Prompt.
SQL>INSERT INTO Employees Values (‘&ENO’,’&ENAME’,&ZIP,’&HDATE’);
Table 3:
SQL>CREATE TABLE PARTS
(PNO VARCHAR2 (5) PRIMARYKEY,
PNAME VARCHAR2 (30),
QOH NUMBER (5),
PRICE NUMBER (8,2),
LEVEL1 NUMBER (5));
Insert the following data:
PNO PNAME QOH PRICE LEVEL1
P4 MYSORESANDAL 65 18.5 45
P5 PONDS CREAM 35 25 65
P6 CINTHOL 25 26 63
P7 COLGATE 75 28 70
P1 LIRIL 50 10.50 10
P2 TIMEX 100 690.50 25
P3 PEARS 75 33.50 30
P8 SAMSUNG 15 9500 25
P9 NOKIA 10 6500 15
P10 LG 24 7650 25
To insert the rows in the table type the following command at SQL Prompt.
SQL>INSERT INTO Parts Values (‘&PNO’,’&PNAME’,&QOH,&PRICE,&LEVEL1);
Table 4:
CREATE TABLE CUSTOMERS
(CNO VARCHAR2 (5) PRIMARYKEY,
CNAME VARCHAR2 (30),
STREET VARCHAR2 (30),
ZIP NUMBER (10) REFERENCES ZIPCODES,
PHONE NUMBER (11));
Insert the following data:
CNO CNAME STREET ZIP PHONE
C4 SRAVAN PRAKASAM 517643 8108257456
ROAD
C5 CHANDRA NTR NAGAR 517715 8245678236
C6 KUMAR MG STREET 517640 9908754009
C7 VINAYKUMAR PANAGALPARK 517544 9393621414
C1 YAMUNA NETHAJISTREET 517644 9874569871
C2 SUBRAMANYA GANDHISTREET 517501 9885254599
M
C3 VENKATESH JUBLIHILLS 517642 9392456789
C8 MOUNIKA PRAKASAM 517644 9988474870
ROAD
C9 LEENA TRUNK ROAD 517501 9966554472
C10 SIREESHA CHENNAI ROAD 517643 9000110651
To insert the rows in the table type the following command at SQL Prompt.
SQL>INSERT INTO Customers Values
(‘&CNO’,’&CNAME’,’&STREET’,&ZIP,&PHONE);
Table 5:
CREATE TABLE ORDERS
(ONO VARCHAR2 (5) PRIMARYKEY,
CNO VARCHAR2 (5) REFERRENCES CUSTOMERS,
ENO VARCHAR2 (5) REFERENCES EMPLOYEES,
RDATE DATE,
SDATE DATE);
Insert the following data:
ONO CNO ENO RDATE SDATE
O4 C3 E1002 25-MAR-10 02-APR-10
O5 C3 E1003 27-MAY-10 01-JUN-10
O6 C4 E1004 12-AUG-10 17-AUG-10
O7 C5 E1005 28-OCT-10 05-NOV-10
O1 C1 E1000 12-JAN-10 17-JAN-10
O2 C2 E1000 21-FEB-10 24-FEB-10
O3 C1 E1001 12-APR-10 21-APR-10
O8 C6 E1005 26-FEB-10 05-MAR-10
O9 C7 E1006 09-NOV-10 15-NOV-10
O10 C8 E1007 21-DEC-10 01-JAN-10
To insert the rows in the table type the following command at SQL Prompt.
SQL>INSERT INTO Orders Values (‘&ONO’,’&CNO’,’&ENO’,’&RDATE’,’&SDATE’);
Table 6:
CREATE TABLE ODETAILS
(ONO VARCHAR2 (5) REFERENCES ORDERS,
PNO VARCHAR2 (5) REFERENCES PARTS,
QTY NUMBER (5));
Insert the following data:
ONO PNO QTY
O3 P2 6
O3 P8 7
O2 P5 5
O2 P7 9
O4 P1 4
O5 P1 10
O1 P8 2
O1 P7 10
O2 P2 5
To insert the rows in the table type the following command at SQL Prompt.
SQL>INSERT INTO Odetails Values (‘&ONO’,’&PNO’,&QTY);
2 UNIVERSITY DATABASE
University wishes to computerize their operations by using the following relations.
1. Student(snum:Integer,sname:string,major:string,level:string,age:integer)
2. Class(name:string,Hour:integer,room:string,fid:integer)
3. Enrolled(sum:integer,cname:string)
4. Faculty(fid:integer,fname:string,depyid:integer)
5. Depart(deptid:integer,dname:string,loc:string)
Creating Tables and Inserting Data
1 Student Table:
CREATE TABLE student
(snum number(3) primary key,
sname varchar2(20),
major varchar2(20),
level1 varchar2(20),
age number(3));
Insert Data:
SQL>INSERT INTO Student VALUES (&snum,’&sname’,’&major’,’&level1’,&age);
SNU SNAME MAJOR LEVEL1 AGE
M
15 MOHAN TELUGU JR 20
19 RAVI COMPUTERS JR 19
1 HONEY MATHS JR 17
2 MOUNIKA PHYSICS JR 18
3 SREEHITHA TELUGU SR 19
4 NADHIYA ENGLISH SR 20
5 SUJATHA COMPUTERS SR 21
6 KIRAN COMMERCE SR 23
7 ARUN HISTORY SR 25
8 BALAJI STATISTICS SR 26
9 AMARNATH PHYSICS SR 22
10 MOULI PHYSICS JR 21
11 HARI PHYSICS JR 18
12 MANI PHYSICS JR 18
13 SRIKANTH PHYSICS JR 18
14 DHARANI TELUGU SR 19
18 SURESH COMPUTERS SR 20
2 Class Table:
CREATE TABLE class
(cname varchar2(20) primary key,
hour number(3),
room varchar2(20),
fid number(4) references faculty);
Insert Data:
SQL>INSERT INTO class VALUES (‘&cname’, &hour, ’&room’, &fid);
CNAME HOUR ROOM FID
TELUGU 9 R1 510
TELUGU 10 R3 510
COMPUTERS 9 R1 610
COMMERCE 9 R1 555
COMMERCE 11 R3 555
MATHS 10 R2 556
STATISTICS 10 R1 557
HISTORY 11 R1 558
COMMERCE 12 R2 555
TELUGU 11 R2 510
ENGLISH 12 R2 560
PHYSICS 11 R3 559
3 Enrolled Table:
CREATE TABLE enrolled
(snum number(3),
Cnamevarchar2(20),
Primary key(snum,cname));
Insert Data:
SQL>INSERT INTO Enrolled VALUES (&snum, ’&cname’);
SNUM CNAME
1 TELUGU
1 COMPUTERS
2 MATHS
3 PHYSICS
3 ENGLISH
4 COMMERCE
5 HISTORY
6 TELUGU
7 STATISTICS
8 STATISTICS
9 COMMERCE
10 ENGLISH
11 TELUGU
12 TELUGU
13 COMPUTERS
14 MATHS
18 HISTORY
15 TELUGU
4 Faculty Table:
CREATE TABLE faculty
(fid number(3) primary key,
fname varchar2(30),
deptid number(3) references depart);
Insert data:
SQL>INSERT INTO FacultyVALUES(&FID,’&fname’,&deptid);
FID FNAME DEPTID
555 C.MADHUSUDHANREDDY 3
556 K.SHARMILADEVI 4
557 Dr.JAYAKRISHNA 5
558 D.VIJAYKUMAR 6
560 RADHAKRISHNA 7
569 SAIGANESH 8
510 SMITH 1
610 K.SAMBASIVARAO 2
5 Depart Table:
CREATE TABLE depart(
deptid number(3) primary key,
dname varchar2(30),
loc varchar2(7));
Insert data:
SQL>INSERT INTO Depart VALUES(&deptid,’&dname’,’&loc’);
DEPTID DNAME LOC
1 TELUGU BLOCK1
2 COMPUTERS BLOCK2
3 COMMERCE BLOCK3
4 MATHS BLOCK4
5 STATISTICS BLOCK5
6 HISTORY BLOCK6
7 ENGLISH BLOCK7
8 PHYSICS BLOCK8
3 EMPLOYEE DATABASE
Consider Employee Table
EMPNO EMP_NAME DEPT SALARY DOJ BRANCH
E101 ASMA PRODUCTION 45000 12-MAR-01 BANGALORE
E102 NIRANJAN HR 70000 03-JUL-02 BANGALORE
E103 HRUDAI MANAGEMENT 120000 11-JAN-01 MYSORE
E104 DEEPTHI IT 67000 01-AUG-01 MYSORE
E105 MOKESH CIVIL 145000 20-SEP-03 MUMBAI
Perform the following
1. Display all the fields of employee table
2. Retrieve employee number and their salary
3. Retrieve average salary of all employees
4. Retrieve number of employees
5. Retrieve distinct number of employees
6. Retrieve total salary of employee group by employee name and count similar names
7. Retrieve total salary of employee which is greater than >120000
8. Display name of employee in descending order
9. Display details of employee whose name is HRUDAI and salary greater than 50000;
SOLUTION:
1 Display all the fields of employee table
SQL> select * from Employee;
2 Retrieve employee number and their salary
SQL> select EMPNO, SALARY from Employee;
3 Retrieve average salary of all employees.
SQL> select AVG (SALARY) from Employee;
4 Retrieve Numbers of Employees
SQL> select COUNT (*) from Employee;
5 Retrieve distinct number of employee
SQL> select count(DISTINCT EMP_NAME) from Employee;
6 Retrieve total SALARY of Employee GROUP BY employee name and count similar names.
SQL>SELECT EMP_NAME, SUM (SALARY),COUNT(*) from Employee GROUP
BY(EMP_NAME);
7 Retrieve total salary of employee which is greater than > 120000
SQL>SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY(EMP_NAME)
HAVING SUM(SALARY)>120000;
8 Display name of employee in descending order
SQL>select EMP_NAME from Employee ORDER BY EMP_NAME DESC;
9 Display details of employees whose name is HRUDAI and salary greater than 50000.
SQL> select * from Employee where EMP_NAME=’HRUDAI’ AND SALARY>50000;
4 DEVELOP AND DEMONSTRATE EXCHANGE TWO NUMBERS USING PL/SQL
AIM: Write a PL/SQL Program to swap two values of two variables, without using third variable
PROGRAM-4:
DECLARE
A number;
B number;
BEGIN
A;=&a;
B:=&b;
dbms_output.put_line(‘before exchange :’);
dbms_output.put_line(‘a=’||a||’b=’||b);
A:=a+b;
B:=a-b;
A:=a-b;
dbms_ouput.put_line(‘After exchange :’);
dbms_output.put_line (‘a=’||a||’b=’||b);
END;
INPUT & OUTPUT:
SQL/
Enter value for a:=10
Enter value for b:=15
Before exchange:
a=10 b=15
After exchange:
a=15 b=10
5. Write a PL/SQL program to generate multiplication tables for 2,4,6
AIM: To Write a PL/SQL program to generate multiplication tables for 2,4,6
PROGRAM -5:
DECLARE
m number;
a number;
tn number;
BEGIN
tn:=2;
loop
for a in 1..10
LOOP
m:=tn*a;
dbms_output.put_line(tn||’x’||a||’=’||m);
end loop;
tn:=tn+2;
exit when tn>6;
end loop;
END;
OUTPUT:
2 x 1=2 4x1=4 6x1=6
2x2=4 4x2=8 6x2=12
2x3=6 4x3=12 6x3=18
2 x4=8 4x4=16 6x4=24
2x5=10 4x5=20 6x5=30
2x6=12 4x6=24 6x6=36
2 x7=14 4x7=28 6x7=42
2x8=16 4x8=32 6x8=48
2x9=18 4x9=36 6x9=54
2 x10=20 4x10=40 6x10=60
6. Write a PL/SQL program to display sum of even numbers and sum of odd numbers in
the given range.
AIM: To Write a PL/SQL program to display sum of even numbers and sum of odd numbers in
the given range.
PROGRAM-6:
DECLARE
M number;
N number;
Esum number;
Osum number;
BEGIN
M:=&m;
N:=&n;
Esum:=0;
Osum:=0;
For a in m..n
Loop
If(mod(a,2)=0)then
Esum:=esum+a;
Else
Osum:=osum+a;
End if;
End loop;
Dbms_output.put_line(‘even numbers sum=’||esum);
Dbms_output.put_line(‘odd numbers sum=’||osum);
END;
INPUT & OUTPUT:
Enter value for m:=5
Enter value for n:=9
Even numbers sum=14
Odd numbers sum=21
7. Write a PL/SQL program to check the given number is palindrome or not.
AIM : To Write a PL/SQL program to check the given number is palindrome or not.
PROGRAM-7
DECLARE
N number;
N1 number;
Rnum number(15);
Rem number;
BEGIN
N:=&n;
N1:=n;
Rnum:=0;
Dbms_output.put_line(‘given number=’||n);
While n>0
Loop
Rem:=mod(n,10);
Rnum:=rnum*10+rem;
N:=floor(n/10);
End loop;
If(n1=rnum)then
Dbms_output.put_line(n1||’is palindrome number’);
Else
Dbms_output.put_line(n1||’is not palindrome number’);
End if;
END;
INPUT & OUTPUT:
SQL>RUN
Enter value for n: 111
Given number=111
111 is palindrome number
SQL>Run
Enter value for n: 153
Given number=153
153 is not palindrome number
8 TRIGGER OPERATIONS IN PL/SQL
Trigger timing and operations forms different combinations such as BEFORE
INSERT OR BEFORE DELETE OR BEFORE UPDATE.
Aim: Trigger is activated before the operation on the table or view is performed.
CREATE TABLE Geeks (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- Insert into Geeks Table
INSERT INTO Geeks (Id, Name, Score) VALUES (1, 'Sam', 800);
INSERT INTO Geeks (Id, Name, Score) VALUES (2, 'Ram', 699);
INSERT INTO Geeks (Id, Name, Score) VALUES (3, 'Tom', 250);
INSERT INTO Geeks (Id, Name, Score) VALUES (4, 'Om', 350);
INSERT INTO Geeks (Id, Name, Score) VALUES (5, 'Jay', 750);
-- insert statement should be written for each entry in Oracle Sql Developer
CREATE TABLE Affect (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER BEFORE_INSERT
BEFORE INSERT ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:NEW.Id, :NEW.Name, :NEW.Score);
END;
/
INSERT INTO Geeks (Id, Name, Score) VALUES (6, 'Arjun', 500);
EFORE DELETE TRIGGER:
CREATE OR REPLACE TRIGGER BEFORE_DELETE
BEFORE DELETE ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
END;
/
DELETE FROM Geeks WHERE Id = 3;
-- BEFORE UPDATE TRIGGER
CREATE OR REPLACE TRIGGER BEFORE_UPDATE
BEFORE UPDATE ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
END;
/
UPDATE Geeks SET Score = 900 WHERE Id = 5;
SELECT * FROM Affect;
SELECT * FROM Geeks;
OUTPUT:
9. PL/SQL Function
AIM: Defining and Invoking a simple PL/SQL function which will compute and return the
maximum of two values.
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/
OUTPUT:
Maximum of (23,45): 45
Statement processed.
0.02 seconds
10.Procedure
The procedure takes two numbers using the IN mode and returns their minimum using the OUT
parameters.
AIM: Finds the minimum of two values.
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
Output:
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
