Wednesday, 16 May 2012

Hospital management system - mini project

CODING:
import java.awt.*;
import java.sql.*;
import java.awt.event.*;
import java.lang.String;
public class Insert extends Frame implements ActionListener
{
TextField t1,t2, t3;
int i=1,s=0;
Button b1,b2,b3,b4,b5;
Label l1,l2, l3;
Statement stmt;
Connection conn;
ResultSet rs;
public Insert()
{
super("HOSPITAL MANAGEMENT");
setSize(800,500);
l1=new Label("NAME");
l2=new Label("WARD");
l3=new Label("ADDRESS");

b1=new Button("INSERT");
b2=new Button("SELECT");
b3=new Button("UPDATE");
b4=new Button("DELETE");
b5=new Button("EXIT");

t1=new TextField(10);
t2=new TextField(10);
t3=new TextField(10);

l1.setBounds(30,50,150,30);
l2.setBounds(30,100,150,30);
l3.setBounds(30,150,150,30);

t1.setBounds(200,50,250,30);
t2.setBounds(200,100,250,30);
t3.setBounds(200,150,250,30);

b1.setBounds(100,400,150,30);
b2.setBounds(250,400,120,30);
b3.setBounds(350,400,120,30);
b4.setBounds(450,400,120,30);
b5.setBounds(550,400,120,30);

setVisible(true);
add(l1);add(l2);add(l3);
add(t1);add(t2);add(t3);
add(b1);add(b2);add(b3);add(b4);add(b5);

setLayout(null);

b1.addActionListener(this);
b2.addActionListener(this);
b3.addActionListener(this);
b4.addActionListener(this);
b5.addActionListener(this);

addWindowListener(new WindowAdapter()
{
public void WindowClosing(WindowEvent e1)
{
System.exit(0);
}
});

try
{
System.out.println("Connecting to the database");
conn=DriverManager.getConnection("jdbc:odbc:stud;UID=10itr062;PWD=10itr");
stmt=conn.createStatement();
}
catch(Exception e){}
}

public void actionPerformed(ActionEvent e1)
{
String xnam=" ";
String xwar=" ";
String xadd=" ";

if(e1.getSource()==b1)
{
try
{
xnam=t1.getText();
xwar=t2.getText();
xadd=t3.getText();
String str="INSERT INTO HOSPITAL VALUES ('" + xnam +"','" + xwar + "','" + xadd

+"')";
stmt.executeUpdate(str);
t1.setText("");
t2.setText("");
t3.setText("");
}
catch(Exception e){}
}

if(e1.getSource()==b2)
{
try
{
xnam=t1.getText();
rs=stmt.executeQuery("SELECT * FROM HOSPITAL WHERE name= '" +xnam+"'" );
rs.next();
t2.setText(" "+rs.getString("ward"));
t3.setText(" "+rs.getString("address"));
}
catch(Exception e){}
}

if(e1.getSource()==b3)
{
try
{
xnam=t1.getText();
xadd=t3.getText();
stmt.executeUpdate("UPDATE HOSPITAL SET ADDRESS='" +xadd+ "' WHERE
NAME= '"+ xnam + "'");
t1.setText("");
t3.setText("");
}
catch(Exception e){}
}

if(e1.getSource()==b4)
{
try
{
xnam=t1.getText();
stmt.executeUpdate("DELETE FROM HOSPITAL WHERE NAME= '"+ xnam + "'");
t1.setText(" ");
t2.setText(" ");
}

catch(Exception e){}
}
if(e1.getSource()==b5)
{
System.exit(0);
}}
public static void main(String args[])
{new Insert();
}}

OUTPUT:
SQL>select * from hospital;
NAME
WARD
ADDRESS
-------------------- -------------------- --------------------
A
general
salem
B
Special
covai

SQL>/
NAME
WARD
ADDRESS
-------------------- -------------------- --------------------
C
General
Madurai
A
general
salem
B
Special
covai

IMPLEMENTATION OF PAYROLL DATABASE


SQL> create or replace package pack as
2 function s(i number) return number;
3 function b(i1 number) return number;
4 function det(i2 number) return number;
5 end pack;
6 /
Package created.

SQL> create or replace package body pack as
2 function s(i number) return number as
3 a number(10);
4 begin
5 select salary into a from emp14 where empid=i;
6 return (a);
7 end;
8 function b(i1 number) return number as
9 sa number(10);
10 begin
11 update emp14 set salary=salary+salary*0.1 where empid=i1;
12 select salary into sa from emp14 where empid=i1;
13 return (sa);
14 end;
15 function det(i2 number) return number as
16 na varchar(10);
17 id number(10);
18 j varchar(10);
19 s1 number(10);
20 begin
21 select fname into na from emp14 where empid=i2;
22 select empid into id from emp14 where empid=i2;
23 select job into j from emp14 where empid=i2;
24 select salary into s1 from emp14 where empid=i2;
25 dbms_output.put_line('Name: '||na||' Empid: ' || id||' Job: '||j||' salary:'||s1);
26 return(i2);
27 end;
28 end pack;
29 /
Package body created.

SQL> declare
2 n number:=&n;

3 op number:=&op;
4 begin
5 if op=1 then
6 dbms_output.put_line('Salary:' ||pack.s(n));
7 end if;
8 if op=2 then
9 dbms_output.put_line('Bonus: '||pack.b(n));
10 end if;
11 if op=3 then
12 dbms_output.put_line('Details: '||pack.det(n));
13 end if;
14 end;
15 /
Enter value for n: 146
old 2: n number:=&n;
new 2: n number:=146;
Enter value for op: 1
old 3: op number:=&op;
new 3: op number:=1;
PL/SQL procedure successfully completed.

SQL> declare
2 n number:=&n;
3 op number:=&op;
4 begin
5 if op=1 then
6 dbms_output.put_line('Salary:' ||pack.s(n));
7 end if;
8 if op=2 then
9 dbms_output.put_line('Bonus: '||pack.b(n));
10 end if;
11 if op=3 then
12 dbms_output.put_line('Details: '||pack.det(n));
13 end if;
14 end;
15 /
Enter value for n: 146
old 2: n number:=&n;
new 2: n number:=146;
Enter value for op: 1
old 3: op number:=&op;
new 3: op number:=1;
Salary:20000

PL/SQL procedure successfully completed.

SOL>/
Enter value for n: 321
old 2: n number:=&n;
new 2: n number:=321;
Enter value for op: 2
old 3: op number:=&op;
new 3: op number:=2;
Bonus: 35200

PL/SQL procedure successfully completed.

SQL>/
Enter value for n: 143
old 2: n number:=&n;
new 2: n number:=143;
Enter value for op: 3
old 3: op number:=&op;
new 3: op number:=3;
Name: rahul Empid: 143 Job: developer salary: 14000
Details: 143
PL/SQL procedure successfully completed.

Normalisation

NORMAL FORM - 1

SQL> create table em1(ename varchar(20),eid number(38),phone number(38));
Table created.

SQL> select * from em1;

ENAME
EID
PHONE
-------------------- ---------- ----------
A
1 9008763452
B
2 9000783456
C
3 9012376541

SQL> create table em2(ename varchar(20),eid number(38),phone1 number(38),
phone2 number(38));
Table created.

SQL> create table em4(ename varchar(20),eid number(38),phone number(38));
Table created.

SQL> declare
2 cursor c is select * from em1;
3 r1 em1%rowtype;
4 r2 em1%rowtype;
5 r3 em1%rowtype;
6 p1 em1.phone%type;
7 p2 em1.phone%type;
8 begin
9 for r1 in c
10 loop
11 p1:=substr(r1.phone,1,5);
12 p2:=substr(r1.phone,6,10);
13 insert into em2 values(r1.ename,r1.eid,p1,p2);
14 dbms_output.put_line(r1.ename||' '||r1.eid||' '||p1||' '||p2);
15 end loop;
16 dbms_output.put_line('DUPLICATE : ');
17 dbms_output.put_line(r1.ename||' '||r1.eid||' '||r1.phone);
18 for r2 in c
19 loop
20 p1:=substr(r2.phone,1,5);
21 p2:=substr(r2.phone,6,10);
22 if(p1<>0) then
23 dbms_output.put_line(r2.ename||' '||r2.eid||' '||p1);

insert into em4 values(r2.ename,r2.eid,p1);
end if;
if(p2<>0) then
dbms_output.put_line(r2.ename||' '||r2.eid||' '||p2);
insert into em4 values(r2.ename,r2.eid,p2);
end if;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from em2;

ENAME
EID PHONE1 PHONE2
-------------------- ---------- ---------- ----------
A
1
90087
63452
B
2
90007
83456
C
3
90123
76541

SQL> select * from em4;

ENAME
EID
PHONE
-------------------- ---------- ----------
A
1
90087
A
1
63452
B
2
90007
B
2
83456
C
3
90123
C
3
76541

NORMAL FORM - 2

SQL> create table stu(stuid int,stuname varchar(15),profid int,profname
varchar(20),grade varchar(3));
Table created.

SQL>select * from stu;

STUID STUNAME PROFID
PROFNAME GRA
------ --------------- ---------- -------------------- -----------------------
1
A
123
Z
a1
2
B
456
Y
b2
3
C
678
X
c1

SQL> create table stu1(stuid int,stuname varchar(20));
Table created.

SQL> create table stu2(profid int,profname varchar(20));
Table created.

SQL> create table stu3(profname varchar(20),stuname varchar(20),grade
varchar(3));
Table created.

SQL> declare
2 cursor c is select * from stu;
3 r1 stu%rowtype;
4 r2 stu%rowtype;
5 r3 stu%rowtype;
6 begin
7 for r1 in c
8 loop
9 insert into stu1 values(r1.stuid,r1.stuname);
10 dbms_output.put_line(r1.stuid||' '||r1.stuname);
11 end loop;
12 for r2 in c
13 loop
14 dbms_output.put_line(r2.profid||' '||r2.profname);
15 insert into stu2 values(r2.profid,r2.profname);
16 end loop;
17 for r3 in c
18 loop
19 dbms_output.put_line(r3.profname||' '||r3.stuname||' '||r3.grade);

20 insert into stu3 values(r3.profname,r3.stuname,r3.grade);
21 end loop;
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> select * from stu1;

STUID STUNAME
---------- --------------------
1
A
2
B
3
C

SQL> select * from stu2;

PROFID PROFNAME
---------- --------------------
123
Z
456
Y
678
X

SQL> select * from stu3;

PROFNAME
STUNAME GRA
-------------------- -------------------- ---
Z
A
a1
Y
B
b2
X
C
c1

NORMAL FORM - 3

SQL>create table bank(bankname varchar(20),ename varchar(20),address
varchar(20),amount number(38));
Table created.

SQL> select * from bank;

BANKNAME ENAME ADDRESS AMOUNT
-------------------- -------------------- -------------------- ----------
KVB
A
Salem
40000
ICICI
B
Madurai
38000
IOB
C
Erode
50000

SQL>create table detail1(bankname varchar(20),ename varchar(20),amount
number(38));
Table created.

SQL>create table bank(bankname varchar(20),address varchar(20),amount
number(38));
Table created.

SQL> declare
2 cursor c is select * from bank;
3 r1 bank%rowtype;
4 r2 bank%rowtype;
5 p2 bank.amount%type;
6 p1 bank.ename%type;
7 begin
8 for r1 in c
9 loop
10 p1:=r1.ename;
11 insert into detail1 values(r1.bankname,p1,r1.amount);
12 dbms_output.put_line(r1.bankname||' '||p1||' ' ||r1.amount);
13 end loop;
14 for r2 in c
15 loop
16 p2:=r2.amount;
17 if(p2<>0)then
18 dbms_output.put_line(r2.bankname||' ' ||r2.address||' '||p2);
19 insert into detail2 values(r2.bankname,r2.address,p2);
20 end if;
21 end loop;

PL/SQL procedure successfully completed.

SQL> select * from detail1;

BANKNAME ENAME
AMOUNT
-------------------- -------------------- ----------
KVB
A
40000
ICICI
B
38000
IOB
C
50000

SQL> select * from detail2;

BANKNAME ADDRESS
AMOUNT
-------------------- -------------------- ----------
KVB
Salem
40000
ICICI
Madurai
38000
IOB
Erode
50000

cursors

SQL> select * from emp4;
ENAME EID SALARY JOD
---------- ---------- ---------- ---------
A
123
20000 25-DEC-10
B
456
25000 02-OCT-08
C
789
35000 07-JAN-06
D
753
4000 12-AUG-09
SQL> create table emp6(fname varchar(20),empid number(10),sal number(6)
);
Table created.

1.Create a PL/SQL code to copy the tuples with salary>5000 to another table
& print the same on the screen.Rise an exception when no rows are selected
SQL> declare
2 fname emp4.ename%type;
3 empid emp4.eid%type;
4 sal emp4.salary%type;
5 cursor c1 is select ename,eid,salary from emp4
6 where salary<5000;
7 begin
8 open c1;
9 loop
10 fetch c1 into fname,empid,sal;
11 if c1 %notfound then
12 dbms_output.put_line('no rows have salary <5000');
13 end if;
14 exit when c1 %notfound;
15 dbms_output.put_line('fname:'||fname);
16 dbms_output.put_line('empid:'||empid);
17 dbms_output.put_line('sal:'||sal);
18 insert into emp6 values(fname,empid,sal);end loop;
19 close c1;
20 end;
21 /

fname:D
empid:753
sal:4000
no rows have salary <5000
PL/SQL procedure successfully completed.

SQL> select * from emp6;
FNAME
EMPID
SAL
-------------------- ---------- ----------
D
753
4000

2. Create a PL/SQL code to retrieve the name,salary & hiredate of the
employee , whose id is obtained as input from the user
SQL> declare
2 fname emp4.ename%type;
3 empid emp4.eid%type;
4 sal emp4.salary %type;
5 hiredate emp4.jod%type;
6 cursor c2 is select ename,eid,salary,jod from emp4
7 where eid=&eid;
8 begin
9 open c2;
10 loop
11 fetch c2 into fname,empid,sal,hiredate;
12 if c2 %notfound then
13 dbms_output.put_line('no rows selected');
14 end if;
15 exit when c2 %notfound;
16 dbms_output.put_line('fname:'||fname);
17 dbms_output.put_line('empid:'||empid);
18 dbms_output.put_line('salary:'||sal);
19 dbms_output.put_line('hiredate:'||hiredate);
20 end loop;
21 close c2;
22 end;
23 /
Enter value for eid: 123
old 7: where empid=&eid;
new 7: where eid=123;
fname:A
empid:123
salary:20000
hiredate:25-DEC-10
no rows selected
PL/SQL procedure successfully completed.
3. Create a PL/SQL code to insert a row into a new table for any value of

employee name obtained as input from the user
SQL> declare
2 fname emp.ename%type;
3 empid emp.eid%type;
4 salary emp.salary%type;
5 hiredate emp.joindate%type;
6 cursor c3 is select ename,eid,salary,joindate from emp
7 where ename='&ename';
8 begin
9 open c3;
10 loop
11 fetch c3 into fname,empid,salary,hiredate;
12 if c3%notfound then
13 dbms_output.put_line('No rows found');
14 end if;
15 exit when c3%notfound;
16 dbms_output.put_line('fname:'||fname);
17 dbms_output.put_line('empid:'||empid);
18 dbms_output.put_line('salary:'||salary);
19 dbms_output.put_line('hiredate:'||hiredate);
20 insert into emp1 values(fname,empid,salary,hiredate);
21 end loop;
22 close c3;
23 end;
24 /

Enter value for ename: A
old 7: where ename='&ename';
new 7: where ename='A';
fname:A
empid:123
salary:20000
hiredate:25-DEC-10
No rows found
PL/SQL procedure successfully completed.

4. Create a PL/SQL code to delete records where salary greater than the given
input and also output on the screen as the employee number which has been

deleted.
SQL> declare
2 eid emp.eid%type;
3 salary emp.salary%type;
4 cursor c4 is select eid,salary from emp
5 where salary>&salary;
6 begin
7 open c4;
8 loop
9 fetch c4 into eid,salary;
10 if c4%notfound then
11 dbms_output.put_line('No rows found');
12 end if;
13 exit when c4%notfound;
14 dbms_output.put_line('Deleted record eid:'||emp);
15 delete from emp where eid=eid;
16 end loop;
17 close c4;
18 end;
19 /

Enter the value for salary:25000
old 5: where salary > & salary;
new 5: where salary > 25000
deleted record eid 753
no rows found
PL/SQL procedure successfully completed.

SQL> select * from emp4;
ENAME
EID SALARY JOD
---------- ---------- ---------- ---------
A
123
20000 25-DEC-10
B
456
25000 02-OCT-08
C
789
35000 07-JAN-06

5. Create a PL/SQL code to display the id,name & salary of two highly paid
employee.
SQL> declare

2 i number;
3 empid emp4.eid%type;
4 fname emp4.ename%type;
5 sal emp4.salary%type;
6 cursor c5 is select ename,eid,salary from emp4
7 where salary in((select max(salary)from emp4) union(select max(salary)
from emp4 where salary not in(select max(salary)from emp4))) order by salary
desc;
8 begin
9 open c5;
10 dbms_output.put_line('Top two paid employee');
11 for i in 1..2
12 loop
13 fetch c5 into fname,empid,sal;
14 dbms_output.put_line('emp_name:'||fname);
15 dbms_output.put_line('emp_id:'||empid);
16 dbms_output.put_line('salary:'||sal);
17 end loop;
18 close c5;
19 end;
20 /

Top two paid employee
emp_name:C
emp_id:789
salary:35000
emp_name:B
emp_id:456
salary:25000
PL/SQL procedure successfully completed.

pl/sql procedures

PL/SQL
1.Write a PL/SQL statements to find out the area of circle.
SQL> declare
2 r number(2,1);
3 ar number(4,2);
4 begin
5 r:=&r;
6 ar:=3.14*r*r;
7 dbms_output.put_line('Area:'||ar);
8 end;
9 /

OUTPUT:
Enter value for r: 1
old 5: r:=&r;
new 5: r:=1;
Area:3.14

PL/SQL procedure successfully completed.

2. Write a PL/SQL statements to find fibonacci series upto the given number
elements.
SQL> declare
2 n number:=&n;
3 a number:=-1;
4 b number:=1;
5 c number(7);
6 i number;
7 begin
8 for i in 1..n
9 loop
10 c:=a+b;
11 dbms_output.put_line('Number:'||c);
12 a:=b;
13 b:=c;
14 end loop;
15 end;
16 /

OUTPUT:
Enter value for n: 3
old 2: n number:=&n;
new 2: n number:=3;

Number:0
Number:1
Number:1

PL/SQL procedure successfully completed.

3. Write a PL/SQL code to find the factorial of a given number.
SQL> declare
2 n number:=&n;
3 a number:=n;
4 f number:=1;i number;
5 begin
6 for i in 1..n
7 loop
8 f:=f*a;
9 a:=a-1;
10 end loop;
11 dbms_output.put_line('Factorial:'||f);
12 end;
13 /

OUTPUT:
Enter value for n: 3
old 2: n number:=&n;
new 2: n number:=3;
Factorial:6

PL/SQL procedure successfully completed.

4. Write a PL/SQL statements to find whether the given no. is prime or not.
SQL> declare
2 n number;
3 i number;
4 ar number:=1;
5 begin
6 n:=&n;
7 for i in 2..n/2
8 loop
9 if mod(n,i)=0 then
10 ar:=0;
11 end if;
12 end loop;
13 if ar=1 then

14
15
16
17
18
19

dbms_output.put_line(n||' is Prime');
else
dbms_output.put_line(n||' is not Prime');
end if;
end;
/

OUTPUT:
Enter value for n: 2
old 6: n:=&n;
new 6: n:=2;
2 is Prime
PL/SQL procedure successfully completed.

5. To find whether a given string is palindrome or not.
SQL> declare
2 g varchar(20);
3 r varchar(20);
4 begin
5 g:='&g';
6 dbms_output.put_line('Given String : '|| g);
7 for i in reverse 1..length(g)
8 loop
9 r:=r||substr(g,i,1);
10 end loop;
11 dbms_output.put_line('Reversed String : '||r);
12 if r=g then
13 dbms_output.put_line(g ||' is Palindrome');
14 else
15 dbms_output.put_line(g ||' is not Palindrome');
16 end if;
17 end;
18 /

OUTPUT:
Enter value for g: pop
old 5: g:='&g';
new 5: g:='pop';
Given String : pop
Reversed String : pop
pop is Palindrome

PL/SQL procedure successfully completed.

1. Write a PL/SQL fucntions to find the area of circle.
SQL> create or replace function f(r number)
2 return number is
3 begin
4 return(3.14*r*r);
5 end;
6 /

Function created.

SQL> declare
2 ra number:=&ra;
3 begin
4 dbms_output.put_line('Area of Circle :' ||f(ra));
5 end;
6 /

OUTPUT:
Enter value for ra: 10
old 2: ra number:=&ra;
new 2: ra number:=10;
Area of Circle :314

PL/SQL procedure successfully completed.

2. Write a PL/SQL functions to find the factorial of given number.
SQL> create or replace function fact(n number)
2 return number is
3 a number:=n;
4 f number:=1;
5 i number;
6 begin
7 for i in 1..n
8 loop
9 f:=f*a;
10 a:=a-1;
11 end loop;
12 return f;
13 end;
14 /

Function created.

SQL> declare
2 nu number:=&nu;
3 begin
4 dbms_output.put_line('Factorial:'||fact(nu));
5 end;
6 /

OUTPUT:
Enter value for nu: 3
old 2: nu number:=&nu;
new 2: nu number:=3;
Factorial:6

PL/SQL procedure successfully completed.

3. Write a PL/SQL functions to find the fibonacci series of given number.
SQL> create or replace function fib(n number)
2 return number is
3 a number:=-1;
4 b number:=1;
5 c number(10);
6 i number;
7 begin
8 for i in 1..n
9 loop
10 c:=a+b;
11 dbms_output.put_line('Fibonacci series :'||c);
12 a:=b;
13 b:=c;
14 end loop;
15 end;
16 /

Function created.

SQL> declare
2 num number:=&num;
3 begin
4 dbms_output.put_line(fib(num));
5 end;
6 /

OUTPUT:
Enter value for num: 3
old 2: num number:=&num;
new 2: num number:=3;
Fibonacci series :0
Fibonacci series :1
Fibonacci series :1

PL/SQL procedure successfully completed.

4. Write a PL/SQL functions to find whether the given number is prime or not.
SQL> create or replace function prime(n number)
2 return number is
3 i number;
4 pr number:=1;
5 begin
6 for i in 2..n/2
7 loop
8 if mod(n,i)=0 then
9 pr:=0;
10 end if;
11 end loop;
12 if pr=1 then
13 return 1;
14 else
15 return 0;
16 end if;
17 end;
18 /

Function created.

SQL> declare
2 num number:=&num;
3 begin
4 if prime(num)=1 then
5 dbms_output.put_line(num || ' is Prime');
6 else
7 dbms_output.put_line(num ||' is not Prime');
8 end if;
9 end;
10 /

OUTPUT:
Enter value for num: 6
old 2: num number:=&num;
new 2: num number:=6;
6 is not Prime

PL/SQL procedure successfully completed.

5. Write a PL/SQL functions to find whether the given string is palindrome or not.
SQL> create or replace function pd(a varchar)
2 return varchar is
3 s varchar(25);
4 begin
5 dbms_output.put_line('Given string:'||a);
6 for i in reverse 1..length(a)
7 loop
8 s:=s||substr(a,i,1);
9 end loop;
10 dbms_output.put_line('Reversed string:'||s);
11 return s;
12 end;
13 /

Function created.

SQL> declare
2 r varchar(20);
3 begin
4 r:='&r';
5 if pd(r)=r then
6 dbms_output.put_line('Given string is palindrome');
7 else
8 dbms_output.put_line('Given string is not palindrome');
9 end if;
10 end;
11 /

OUTPUT:
Enter value for r: dog
old 4: r:='&r';
new 4: r:='dog';
Given string:dog

Reversed string:god
Given string is not palindrome

PL/SQL procedure successfully completed.

6. Write a PL/SQL statements to find nCr using functions.

SQL> create or replace function fact(n number)
2 return number is
3 a number:=n;
4 f number:=1;
5 i number;
6 begin
7 for i in 1..n
8 loop
9 f:=f*a;
10 a:=a-1;
11 end loop;
12 return f;
13 end;
14 /

Function created.

SQL> create or replace function ncr(n number ,r number)
2 return number is
3 n1 number:=fact(n);
4 r1 number:=fact(r);
5 nr1 number:=fact(n-r);
6 result number;
7 begin
8 result:=(n1)/(r1*nr1);
9 return result;
10 end;
11 /

Function created.

SQL> declare
2 n number;
3 r number;
4 result number;
5 begin

6
7
8
9
10
11

n:=&n;
r:=&r;
result:=ncr(n,r);
dbms_output.put_line('nCr:'||result);
end;
/

OUTPUT:
Enter value for n: 3
old 6: n:=&n;
new 6: n:=3;
Enter value for r: 2
old 7: r:=&r;
new 7: r:=2;
nCr:3

PL/SQL procedure successfully completed.

7. Write a PL/SQL statements to find the sum(salary) from an employee table using
functions.
SQL> select * from emp4;

ENAME
EID
---------- ----------
A
123
B
456
C
789
D
753

SQL> create or replace function loy(i number)
2 return number is
3 s number;
4 begin
5 select sum(salary) into s from emp4;
6 return s;
7 end;
8 /

Function created.

SQL> declare
2 k number;
3 begin

4 k:=loy(0);
5 dbms_output.put_line('Sum of salary:'||k);
6 end;
7 /
OUTPUT:
Sum of salary:84000

PL/SQL procedure successfully completed.

8. Write a PL/SQL statements to find the max(salary) from an employee table using
functions.
SQL> select * from emp4;

ENAME EID SALARY JOD
---------- ---------- ---------- ---------
A
123 20000 25-DEC-10
B
456 25000 02-OCT-08
C
789 35000 07-JAN-06
D
753
4000 12-AUG-09

SQL> create or replace function em(i number)
2 return number is
3 m number;
4 begin
5 select max(salary) into m from emp4;
6 return m;
7 end;
8 /

Function created.

SQL> declare
2 v number;
3 begin
4 v:=em(0);
5 dbms_output.put_line('Maximum salary:'||v);
6 end;
7 /

OUTPUT:
Maximum salary:35000

PL/SQL procedure successfully completed.

PL/SQL - PROCEDURES:
1.a) Write a procedure to find the sum of salary of an employee when it is given as
input.
SQL> create or replace procedure p1(n number)
2 is
3 cursor c is select eid,salary,commission,salary+commission as sum from emp4
where eid=n;
4 r c%rowtype;
5 begin
6 open c;
7 fetch c into r;
8 dbms_output.put_line('Salary for '||r.eid||' is '||r.salary);
9 dbms_output.put_line('Commission for '||r.eid||' is '||r.commission);
10 dbms_output.put_line('Total Salary for '||r.eid||' is '||r.sum);
11 close c;
12 end;
13 /

Procedure created.

SQL> execute p1(123);

OUTPUT:
Salary for 123 is 20000
Commission for 123 is 2000
Total Salary for 123 is 22000

PL/SQL procedure successfully completed.

1.b) Write a procedure to update the total income of the employee in employee
relation
SQL> create or replace procedure p4 is
2 begin
3 update emp4 set income=salary+commission;
4 end p4;
5 /

Procedure created.

SQL> execute p4;

OUTPUT:
PL/SQL procedure successfully completed.

SQL> select * from emp4;

ENAME EID SALARY JOD
COMMISSION SUM INCOME
---------- ---------- ---------- ----------- ---------- ---------- ----------------------
A
123 20000 25-DEC-10
2000
22000
B
456 25000 02-OCT-08
3500
28500
C
789 35000 07-JAN-06
50000
85000
D
753 4000 12-AUG-09
1500
5500

2.Write a procedure to calculate average & total when name is given as input.
SQL> select * from stu1;

NAME
RNO
M1
M2
M3
-------------------- ---------- ---------- ---------- ----------
A
1
100
100
100
B
2
95
89
99
C
3
78
81
75

SQL> alter table stu1 add(tot int,avg float);
Table altered.

SQL> create or replace procedure p2(n char) is
2 begin
3 update stu1 set tot=m1+m2+m3,avg=(m1+m2+m3)/3 where name=n;
4 end p2;
5 /

Procedure created.

SQL> execute p2('A');

OUTPUT:
PL/SQL procedure successfully completed.

SQL> select * from stu1;

NAME RNO M1
M2
M3
TOT AVG
----------------------------------------------------------------------
A
1
100
100
100
300 100
B
2
95
89
99
C
3
78
81
75

TRIGGERS



SQL>create table emp(ename varchar(15),eid number(4),salary
number(6),join_date date);
Table created.

SQL>create table backup(ename varchar(15),eid number(4),salary number(6));
Table created.

SQL>create table recycle(ename varchar(15),eid number(4),salary number(6));
Table created.

SQL>create table altered(ename varchar(15),eid number(4),salary number(6));
Table created.

SQL>create table emp2(ename varchar(15),eid number(4),salary number(6));
Table created.

1.Create a trigger to set the valueof salary as 20000 whenever the salary exceeds
25000.
SQL> insert into emp values('&ename',&eid,&salary,'&join_date');
Enter value for ename: A
Enter value for eid: 123
Enter value for salary: 25000
Enter value for join_date: 25-dec-10
old 1: insert into emp values('&ename',&eid,&salary,'&join_date')
new 1: insert into emp values('A',123,25000,'25-dec-10')
1 row created.

SQL> /
Enter value for ename: B
Enter value for eid: 456
Enter value for salary: 20000
Enter value for join_date: 02-oct-08
old 1: insert into emp values('&ename',&eid,&salary,'&join_date')
new 1: insert into emp values('B',456,20000,'02-oct-08')
1 row created.

SQL> /
Enter value for ename: C
Enter value for eid: 789
Enter value for salary: 35000
Enter value for join_date: 01-jan-06
old 1: insert into emp values('&ename',&eid,&salary,'&join_date')
new 1: insert into emp values('C',789,35000,'01-jan-06')
1 row created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 25-DEC-10
B
456
20000 02-OCT-08
C
789
35000 01-JAN-06

SQL> create trigger t1
2 after insert on emp
3 begin update emp set salary = 20000
4 where salary>25000;
5 end;
6 /
Trigger created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 25-DEC-10
B
456
20000 02-OCT-08
C
789
20000 01-JAN-06
3 rows selected.

2.Create a trigger to set the join date of employee to system date whenever a record
is inserted.
SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 25-DEC-10
B
456
20000 02-OCT-08
C
789
20000 01-JAN-06
3 rows selected.

SQL> create or replace trigger t2
2 after insert on emp
3 begin
4 update emp set joindate=(select sysdate from dual);
5 end;
6 /
Trigger created.

SQL> insert into emp values('D',159,29000,'26-jul-09');
1 row created.

SQL> insert into emp values('E',753,15000,'21-feb-11');
1 row created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 23-JAN-12
B
456
20000 23-JAN-12
C
789
20000 23-JAN-12
D
159
20000 23-JAN-12
E
753
15000 23-JAN-12
5 rows selected.

3. Create a trigger to store the data which are updated in the employee table
SQL> insert into emp values('E',852,65000,'27-mar-08');
1 row created.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 23-JAN-12
B
456
20000 23-JAN-12
C
789
20000 23-JAN-12
D
159
20000 23-JAN-12
E
753
15000 23-JAN-12
E
852
20000 23-JAN-12
6 rows selected.

SQL> create trigger t3
2 after update on emp
3 for each row begin
4 insert into altered values(:old.ename,:old.eid,:old.salary);
5 end;
6 /
Trigger created.

SQL> update emp set ename='G' where eid=852;
1 row updated.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------

25000 23-JAN-12
20000 23-JAN-12
20000 23-JAN-12
20000 23-JAN-12
15000 23-JAN-12
20000 23-JAN-12

4.Create a trigger to take backup whenever a record is inserted into a table and also
take the deleting record of a table to another.
SQL> create trigger t4
2 after insert or delete on emp
3 for each row begin
4 if inserting then
5 insert into backup values(:new.ename,:new.eid,:new.salary);
6 elsif deleting then
7 insert into recycle values(:old.ename,:old.eid,:old.salary);
8 end if;
9 end;
10 /
Trigger created.

SQL> insert into emp values('H',657,26000,'09-nov-11');
1 row created.

SQL> delete from emp where eid=852;
1 row deleted.

SQL> select * from emp;
ENAME
EID SALARY JOINDATE
--------------- ---------- ---------- ---------
A
123
25000 23-JAN-12
B
456
20000 23-JAN-12
C
789
20000 23-JAN-12

D
159
20000 23-JAN-12
E
753
15000 23-JAN-12
H
657
20000 23-JAN-12
6 rows selected.
5.Create a trigger to check the salary value when a new record is inserted into the
table and if the salary greater than 25000,set it to 25000.
SQL> create or replace trigger t5
2 before insert on emp2
3 for each row
4 declare
5 sa emp2.salary% type;
6 begin
7 sa:=:new.salary;
8 if sa>25000 then
9 :new.salary:=25000;
10 end if;
11 end;
12 /
Trigger created.

SQL> insert into emp2 values('I',951,36000);
1 row created.

SQL> insert into emp2 values('J',357,18000);
1 row created.

MPLEMENTATION OF JOIN AND SET OPERATIONS AND INTEGRITY CONSTRAINTS

SQL> select *from classroom;
BUILDING
ROOM_NU CAPACITY
--------------- ------- ----------
packard
101
500
painter
514
10
taylor
3128
70
watson
100
30
watson
120
50

SQL> select *from department;
DEPT_NAME BUILDING
---------- --------------- ----------
biology watson
90000
comp.sci taylor
100000
elec.eng. taylor
85000
finance painter
120000
history painter
50000
music
packard
80000
physics watson
70000

7 rows selected.

SQL> select *from course;
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
bio-101 intro. to biology
biology
4

bio-399 computational biology
3

cs-101 intro. to computer science
comp.sci
4
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
cs-190 game design
comp.sci
4

cs-315 robotics
3

cs-319 image processing
comp.sci
3
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
cs-347 database system concepts
comp.sci
3

ee-181 intro. to digital systems
3

fin-201 investment banking
finance
3
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
his-351 world history
history
3

mu-199 music video production
3

phy-101 physical principles
physics
4
COURSE_I TITLE
DEPT_NAME
-------- -------------------------------------------------- ----------------
CREDITS
----------
bio-301 genetics
biology
4

13 rows selected.

SQL> select *from instructor;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 srinivasan
comp.sci
65000
12121 wu
finance
90000
15151 mozart
music
40000
22222 einsten
physics
95000
32343 el said
history
60000

33456 gold
physics
87000
45565 katz
comp.sci
75000
58583 califieri
history
62000
76543 singh
finance
80000
76766 crick
biology
72000
83821 brandt
comp.sci
92000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
98345 kim
elec.eng.
80000

12 rows selected.

SQL> select *from section;
COURSE_I SEC_ID SEMEST
YEAR BUILDING
-------- -------- ------ ---------- --------------- ------- ----
bio-101 1
summer
2009 painter
514 b
bio-301 1
summer
2010 painter
514 a
cs-101 1
fall
2009 packard
101 h
cs-101 1
spring
2010 packard
101 f
cs-190 1
spring
2009 taylor
3128 e
cs-190 2
spring
2009 taylor
3128 a
cs-315 1
spring
2010 watson
120 d
cs-319 1
spring
2010 watson
100 b
cs-319 2
spring
2010 taylor
3128 c
cs-347 1
fall
2009 taylor
3128 a
ee-181 1
spring
2009 taylor
3128 c
COURSE_I SEC_ID SEMEST
YEAR BUILDING
-------- -------- ------ ---------- --------------- ------- ----
fin-201 1
spring
2010 packard
101 b
his-351 1
spring
2010 painter
514 c
mu-199 1
spring
2010 packard
101 d
phy-101 1
fall
2009 watson
100 a

15 rows selected.

SQL> select *from teaches;
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ----------
10101 cs-101 1
fall
2009
10101 cs-315 1
spring
2010
10101 cs-347 1
fall
2009
12121 fin-201 1
spring
2010
15151 mu-199 1
spring
2010
22222 phy-101 1
fall
2009
32343 his-351 1
spring
2010
45565 cs-101 1
spring
2010

45565 cs-319 1
spring
2010
76766 bio-101 1
summer
2009
76766 bio-301 1
summer
2010
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ----------
83821 cs-190 1
spring
2009
83821 cs-190 2
spring
2009
83821 cs-319 2
spring
2010
98345 ee-181 1
spring
2009

15 rows selected.

SQL> select *from student;
ID NAME
DEPT_NAME
TOT_CRED
----- -------------------- -------------------- ----------
00128 zhang
comp.sci
102
12345 shankar
comp.sci
32
19991 brandt
history
80
23121 chavez
finance
110
44553 peltier
physics
56
45678 levy
physics
46
54321 williams
comp.sci
54
55739 sanchez
music
38
70557 snow
physics
0
76543 brown
comp.sci
58
76653 aoi
elec.eng.
60
ID NAME
DEPT_NAME
TOT_CRED
----- -------------------- -------------------- ----------
98765 bourikas
elec.eng.
98
98988 tanaka
biology
120

13 rows selected.

SQL> select *from takes;
ID COURSE_I SEC_ID SEMEST
----- -------- -------- ------ ---------- --
00128 cs-101 1
fall
2009 a
00128 cs-347 1
fall
2009 a-
12345 cs-101 1
fall
2009 c
12345 cs-190 2
spring
2009 a
12345 cs-315 1
spring
2010 a
12345 cs-347 1
fall
2009 a
19991 his-351 1
spring
2010 b
23121 fin-201 1
spring
2010 c+
44553 phy-101 1
fall
2009 b-
45678 cs-101 1
fall
2009 f

45678 cs-101 1

ID COURSE_I SEC_ID SEMEST
YEAR GR
----- -------- -------- ------ ---------- --
45678 cs-319 1
spring
2010 b
54321 cs-101 1
fall
2009 a-
54321 cs-190 2
spring
2009 b+
55739 mu-199 1
spring
2010 a-
76543 cs-101 1
fall
2009 a
76543 cs-319 2
spring
2010 a
76653 ee-181 1
spring
2009 c
98765 cs-101 1
fall
2009 c-
98765 cs-315 1
spring
2010 b
98988 bio-101 1
summer
2009 a
98988 bio-301 1
summer
2010

22 rows selected.

DML QUERIES

1) SQL> select distinct dept_name from instructor;
DEPT_NAME
--------------------
finance
biology
comp.sci
music
physics
elec.eng.
history

7 rows selected.

2)SQL> select all title from course;
TITLE
--------------------------------------------------
intro. to biology
computational biology
intro. to computer science
game design
robotics
image processing
database system concepts
intro. to digital systems
investment banking
world history

spring

2010 b+

music video production
TITLE
--------------------------------------------------
physical principles
genetics

13 rows selected.

3) SQL> select name from instructor where dept_name='comp.sci' and
salary>70000;
NAME
--------------------
katz
brandt

4) SQL> select name,instructor.dept_name,building from instructor,department
where instructor.dept_name=department.dept_name;
NAME
DEPT_NAME
BUILDING
-------------------- -------------------- ---------------
srinivasan
comp.sci
taylor
wu
finance
painter
mozart
music
packard
einsten
physics
watson
el said
history
painter
gold
physics
watson
katz
comp.sci
taylor
califieri
history
painter
singh
finance
painter
crick
biology
watson
brandt
comp.sci
taylor
NAME
DEPT_NAME
BUILDING
-------------------- -------------------- ---------------
kim
elec.eng.
taylor

12 rows selected.

5) SQL> select name,course_id from instructor,teaches where
instructor.ID=teaches.ID and instructor.dept_name='comp.sci';
NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
katz
cs-101
katz
cs-319
brandt
cs-190

brandt
brandt

8 rows selected.

6) SQL> select name from student where name like '%an%';
NAME
--------------------
zhang
shankar
brandt
sanchez
tanaka

7) SQL> select name from student where tot_cred between 50 and 80;
NAME
--------------------
brandt
peltier
williams
brown
aoi

JOIN AND SET OPERATIONS

WHERE CLAUSE PREDICATES
1) SQL> select name,course_id from instructor,teaches where
instructor.ID=teaches.ID;

NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301
NAME
COURSE_I
-------------------- --------
brandt
cs-190
brandt
cs-190

cs-190
cs-319

brandt
kim

15 rows selected.

NATURAL JOIN
2) SQL> select name, course_id from instructor natural join teaches;

NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301

NAME
COURSE_I
-------------------- --------
brandt
cs-190
brandt
cs-190
brandt
cs-319
kim
ee-181

15 rows selected.

3) SQL> select name,title from instructor natural join teaches,course where
teaches.course_id=course.course_id;

NAME
TITLE
-------------------- --------------------------------------------------
srinivasan
intro. to computer science
srinivasan
robotics
srinivasan
database system concepts
wu
investment banking
mozart
music video production
einsten
physical principles
el said
world history
katz
intro. to computer science
katz
image processing
crick
intro. to biology

cs-319
ee-181

crick
genetics
15 rows selected.
RENAME
a)As

4) SQL> select name as instructor_name,course_id from instructor,teaches where
instructor.ID=teaches.ID;
INSTRUCTOR_NAME
COURSE_I
-------------------- --------
srinivasan
cs-101
srinivasan
cs-315
srinivasan
cs-347
wu
fin-201
mozart
mu-199
einsten
phy-101
el said
his-351
katz
cs-101
katz
cs-319
crick
bio-101
crick
bio-301

15 rows selected.

STRING OPERATIONS
a)Like
5) SQL> select dept_name from department where building like '%watson%';
DEPT_NAME
----------
biology
physics

ATTRIBUTE SPECIFICATION IN SELECT CLAUSE
6) SQL> select instructor .* from instructor,teaches where
instructor.ID=teaches.ID;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
10101 srinivasan
comp.sci
65000
10101 srinivasan
comp.sci
65000
10101 srinivasan
comp.sci
65000
12121 wu
finance
90000
15151 mozart
music
40000
22222 einsten
physics
95000
32343 el said
history
60000
45565 katz
comp.sci
75000
45565 katz
comp.sci
75000
76766 crick
biology
72000

76766 crick
biology
72000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
83821 brandt
comp.sci
92000
83821 brandt
comp.sci
92000
83821 brandt
comp.sci
92000
98345 kim
elec.eng.
80000

15 rows selected.

ORDERING THE DISPLAY OF TUPLES
7) SQL> select name from instructor where dept_name='physics' order by name;
NAME
--------------------
einsten
gold

8) SQL> select * from instructor order by salary desc,name asc;
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
22222 einsten
physics
95000
83821 brandt
comp.sci
92000
12121 wu
finance
90000
33456 gold
physics
87000
98345 kim
elec.eng.
80000
76543 singh
finance
80000
45565 katz
comp.sci
75000
76766 crick
biology
72000
10101 srinivasan
comp.sci
65000
58583 califieri
history
62000
32343 el said
history
60000
ID NAME
DEPT_NAME
SALARY
----- -------------------- -------------------- ----------
15151 mozart
music
40000

12 rows selected.

SET OPERATIONS
a)Union
9) SQL> (select course_id from section where semester='fall' and year=2009) union
(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-315
cs-319
cs-347
fin-201
his-351
mu-199
phy-101
8 rows selected.
b)Union all
10) SQL> (select course_id from section where semester='fall' and year=2009) union
all(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-101
cs-315
cs-319
cs-347
fin-201
his-351
mu-199
phy-101
c)Intersect
11) SQL> (select course_id from section where semester='fall' and year=2009)
intersect (select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
d)Intersect all
12) SQL> (select course_id from section where semester='fall' and year=2009)
intersect all(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
cs-101
cs-101
e)Except
13) SQL> (select course_id from section where semester='fall' and year=2009)
except(select course_id from section where semester='spring' and year=2010);
COURSE_I

Cs-347
Phy-101

f)Except all
14) SQL> (select course_id from section where semester='fall' and year=2009)
exceptall(select course_id from section where semester='spring' and year=2010);
COURSE_I
--------
Cs-347
Phy-101
NULL VALUES
14) SQL> select name from instructor where salary is null;
no rows selected

AGGREGATE FUNCTIONS
a)Basic aggregation
15) SQL> select avg(salary) as avg_salary from instructor where
dept_name='comp.sci';
AVG_SALARY
----------
77333.3333

16) SQL> select count(distinct ID) from teaches where semester='spring' and
year=2010;
COUNT(DISTINCTID)
-----------------
6

b)Aggregation with grouping
17) SQL> select dept_name,avg(salary) as avg_salary from instructor group by
dept_name;
DEPT_NAME
AVG_SALARY
-------------------- ----------
finance
85000
biology
72000
comp.sci
77333.3333
music
40000
physics
91000
elec.eng.
80000
history
61000

7 rows selected.

18) SQL> select dept_name,count(distinct ID) as instr_count from instructor
natural join teaches where semester='spring' and year=2010 group by dept_name;

DEPT_NAME
INSTR_COUNT
-------------------- -----------
comp.sci
3
finance
1
history
1
music
1

c)Having clause
19) SQL> select dept_name,avg(salary) as avg_salary from instructor group by
dept_name having avg(salary)>42000;
DEPT_NAME
AVG_SALARY
-------------------- ----------
finance
85000
biology
72000
comp.sci
77333.3333
physics
91000
elec.eng.
80000
history
61000
6 rows selected.

20) SQL> select course_id,semester,year,sec_id,avg(tot_cred) from takes natural
join student where year=2009 group by course_id,semester,year,sec_id having
count(ID)>=2;
COURSE_I SEMEST
YEAR SEC_ID AVG(TOT_CRED)
-------- ------ ---------- -------- -------------
cs-101 fall
2009 1
65
cs-347 fall
2009 1
67
cs-190 spring
2009 2
43

SET MEMBERSHIP
a)In
21) SQL> select distinct course_id from section where semester='fall' and year=2009
and course_id in(select course_id from section where semester='spring' and
year=2010);
COURSE_I
--------
cs-101

b)Not in
22) SQL> select distinct course_id from section where semester='fall' and year=2009
and course_id not in(select course_id from section where semester='spring' and
year=2010);
COURSE_I
--------
cs-347
phy-101

c)Distinct
23) SQL> select count(distinct ID) from takes
where(course_id,sec_id,semester,year) in(select course_id,sec_id,semester,year from
teaches where teaches.ID=10101);
COUNT(DISTINCTID)
-----------------
6
SET COMPARISON
a)>some
24) SQL> select name from instructor where salary>some(select salary from
instructor where dept_name='biology');
NAME
--------------------
einsten
brandt
wu
gold
kim
singh
katz

7 rows selected.

b)>all
25) SQL> select name from instructor where salary>all(select salary from instructor
where dept_name='biology');
NAME
--------------------
wu
einsten
gold
katz
singh
brandt
kim

7 rows selected.

c)>=all
26) SQL> select dept_name from instructor group by dept_name having avg(salary)
>=all(select avg(salary) from instructor group by dept_name);
DEPT_NAME
--------------------
Physics

IMPLEMENTATION OF BUILT-IN FUNCTIONS

DATE FUNCTIONS:

SQL> select sysdate from dual;
SYSDATE
---------
22-DEC-11
SQL> select last_day(sysdate) from dual;
LAST_DAY(
---------
31-DEC-11
SQL> select next_day(sysdate,'thursday') from dual;
NEXT_DAY(
---------
29-DEC-11
SQL> select greatest(sysdate,'15-DEC-11') from dual;
GREATEST(
---------
22-DEC-11
SQL> select greatest('20-DEC-11','10-DEC-11') from dual;
GREATEST(
---------
20-DEC-11
SQL> select greatest('Monday','Friday') from dual;
GREATE
------
Monday
SQL> select add_months(sysdate,3) from dual;
ADD_MONTH
---------
22-MAR-12
SQL> select last_day('12-FEB-11') from dual;
LAST_DAY(
---------
28-FEB-11
SQL> select sysdate,round(sysdate,'month')from dual;
SYSDATE ROUND(SYS
--------- ---------
22-DEC-11 01-JAN-12
SQL> select sysdate,round(sysdate,'year')from dual;
SYSDATE ROUND(SYS
--------- ---------
22-DEC-11 01-JAN-12

AGGREGATE FUNCTIONS:

SQL> create table table1(name char(20),roll int,mark int);
Table created.
SQL> select * from table1;
NAME
ROLL
MARK
-------------------- ---------- ----------
Abi
1
98
Pooja
2
89
Venu
3
91
3 rows selected.
SQL> select sum(mark) from table1;
SUM(MARK)
----------
278
SQL> select max(mark) from table1;
MAX(MARK)
----------
98
SQL> select min(mark) from table1;
MIN(MARK)
----------
89
SQL> select avg(mark) from table1;
AVG(MARK)
----------
92.6666667
SQL> select count(*) from table1;
COUNT(*)
----------
3
CHARACTER FUNCTIONS:

SQL> select initcap('good') from dual;
INIT
----
Good
SQL> select lower('GOOD') from dual;
LOWE
----
good

SQL> select upper('good')from dual;
UPPE
----
GOOD
SQL> select translate('good','g','h') from dual;
TRAN
----
hood
SQL> select replace('good','go','g') from dual;
REP
---
god
SQL> select substr('good',2,2) from dual;
SU
--
oo
SQL> select soundex('good')from dual;
SOUN
----
G300
SQL> select trim(' good ') from dual;
TRIM
----
good
SQL> select lpad('good','10','*')from dual;
LPAD('GOOD
----------
******good
SQL> select rpad('good','10','*')from dual;
RPAD('GOOD
----------
good******
SQL> select length('good')from dual;
LENGTH('GOOD')
--------------
4
NUMERIC FUNCTIONS

SQL> select abs(-15) from dual;
ABS(-15)
----------
15

SQL> select cos(90) from dual;
COS(90)
----------
-.44807362
SQL> select ceil(10.33) from dual;
CEIL(10.33)
-----------
11
SQL> select floor(10.57) from dual;
FLOOR(10.57)
------------
10
SQL> select exp(0) from dual;
EXP(0)
----------
1
SQL> select sqrt(4) from dual;
SQRT(4)
----------
2
MISCELLANEOUS FUNCTIONS:

SQL> select uid from dual;
UID
----------
462
SQL> select user from dual;
USER
------------------------------
10ITR062

IMPLEMENTATION OF DDL , DML, DCL AND TCL COMMANDS

IMPLEMENTATION OF DDL AND DML COMMANDS

DML COMMANDS

1. TO CREATE A TABLE:

SQL> create table table1(name varchar(20),roll int);

Table created.

2. TO VIEW THE DESCRIPTION OF THE TABLE:

SQL> desc table1;
Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
VARCHAR2(20)
ROLL
NUMBER(38)

3. TO ALTER THE TABLE:

A) TO ADD A FIELD INTO THE TABLE:

SQL> alter table table1 add(age int);

Table altered.

SQL> desc table1;
Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
VARCHAR2(20)
ROLL
NUMBER(38)
AGE
NUMBER(38)

B) TO MODIFY THE DATATYPE OF THE FIELD:

SQL> alter table table1 modify(name char(20));

Table altered.

SQL> desc table1;

----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)
AGE
NUMBER(38)

C) TO DROP A FIELD FROM THE TABLE:

SQL>alter table table1 drop(age);

Table altered;

SQL> desc table1;

Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)

4. TO TRUNCATE THE TABLE:

SQL> truncate table table1;

Table truncated.

SQL> select * from table1;

no rows selected

SQL> desc table1;

Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)
MARK
NUMBER(38)

5. TO DROP THE ENTIRE TABLE:

SQL>drop table table1;

Table dropped.

DML COMMANDS

1. TO INSERT VALUES INTO THE TABLE:

SQL> insert into table1 values('Abi',1,10);

1 row created.

2. TO VIEW THE TABLE:

SQL> select * from table1;

NAME
ROLL
AGE
-------------------- ---------- ----------
Abi
1
10

SQL> insert into table1 values('&name',&roll,&age);
Enter value for name: Anu
Enter value for roll: 2
Enter value for age: 11
old 1: insert into table1 values('&name',&roll,&age)
new 1: insert into table1 values('Anu',2,11)

1 row created.

SQL> /
Enter value for name: Devi
Enter value for roll: 3
Enter value for age: 12
old 1: insert into table1 values('&name',&roll,&age)
new 1: insert into table1 values('Devi',3,12)

1 row created.

SQL> select * from table1;

NAME
ROLL
AGE
-------------------- ---------- ----------
Abi
1
10
Anu
2
11
Devi
3
12
Priya
4
13

SQL> alter table table1 add(mark int);

Table altered.

SQL> desc table1;

Name
Null? Type
----------------------------------------- -------- ----------------------------
NAME
CHAR(20)
ROLL
NUMBER(38)
AGE
NUMBER(38)
MARK
NUMBER(38)

SQL> insert into table1 values('&name',&roll,&mark);
Enter value for name: Raji
Enter value for roll: 5
Enter value for mark: 89
old 1: insert into table1 values('&name',&roll,&mark)
new 1: insert into table1 values('Raji',5,89)

1 row created.

SQL> /
Enter value for name: Vedha
Enter value for roll: 6
Enter value for mark: 78
old 1: insert into table1 values('&name',&roll,&mark)
new 1: insert into table1 values('Vedha',6,78)

1 row created.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- ----------
Abi
1
Anu
2
Devi
3
Raji
4
89
Vedha
5
78
5 rows selected.

3. TO UPDATE A FIELD:

SQL> update table1 set mark=98 where name='Abi';

1 row updated.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- -----------
Abi
1
98
Anu
2
Devi
3
Raji
4
89
Vedha
5
78
5 rows selected.

SQL> update table1 set mark=79 where name='Anu';
1 row updated.

SQL> update table1 set mark=90 where name='Devi';

1 row updated.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- ----------
Abi
1
98
Anu
2
79
Devi
3
90
Raji
4
89
Vedha
5
78
5 rows selected.

SQL> select name from table1 where mark>=90;

NAME
--------------------
Abi
Devi

4. TO DELETE A PARTICULAR ROW:

SQL>delete from table1 where name =’Anju’;

1 row deleted.

SQL> select roll from table1;

ROLL
----------
1
3
4
5

4 rows selected.

SQL> select * from table1;

NAME
ROLL MARK
-------------------- ---------- ----------
Abi
1
98
Devi
3
90
Raji
4
89
Vedha
5
78
4 rows selected.

IMPLEMENTATION OF DCL AND TCL COMMANDS

DCL COMMANDS

SQL> select * from tab;

TABTYPE CLUSTERID

------------------------------ ------- ----------

BIN$f0ZVcbwyRaG0Lh5iR+n+8Q==$0 TABLE

SQL> create table table1(name varchar(20),roll int);

Table created.

GRANT:

SQL> grant all on table1 to public;

Grant succeeded.

REVOKE:

SQL> revoke all on table1 from public;

Revoke succeeded.

TCL COMMANDS

SAVEPOINT:

SQL> insert into table1 values('&name',&roll);

Enter value for name: Abi

Enter value for roll: 1

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Abi',1)

1 row created.

Enter value for name: Devi

Enter value for roll: 2

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Devi',2)

1 row created.

SQL> savepoint s1;

Savepoint created.

SQL> insert into table1 values('&name',&roll);

Enter value for name: Priya

Enter value for roll: 3

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Priya',3)

1 row created.

Enter value for name: Tanu

Enter value for roll: 4

old 1: insert into table1 values('&name',&roll)

new 1: insert into table1 values('Tanu',4)

1 row created.

SQL> savepoint s2;

Savepoint created.

SQL> select * from table1;

ROLL

------------------- ----------

1

2

3

4

ROLLBACK:

SQL> rollback to s1;

Rollback complete.

SQL> select * from table1;

ROLL

-------------------- ----------

1

2

COMMIT:

SQL> commit;

Commit complete.

Wednesday, 2 May 2012

Download OPERATING SYSTEM CONCEPTS ebook

C++ lab programs


Fibonacci numbers
#include<iostream.h>
#include<conio.h>
int fibo(int n)
{
 int f;
 if(n==0)
  return 0;
 else if(n==1)
  return 1;
 else
  f=fibo(n-1)+fibo(n-2);
 return f;
}
main()
{
 int m,f;
 clrscr();
 cout<<"\n Enter a no.";
 cin>>m;
 for(int i=0;i<m;i++)
  {
   f=fibo(i);
   cout<<f<<endl;
  }
 getch();
 return 0;
}
Simple Interest
#include<iostream.h>
#include<conio.h>
float si(float p1,float n1,float r1)
{
 float i;
 i=p1*n1*r1/100;
 return i;
}
main()
{
 float p,n,r,s;
 clrscr();
 cout<<"\n Enter the Principal,No. of years,Rate of Interest";
 cin>>p>>n>>r;
 s=si(p,n,r);
 cout<<"\n Simple Interest = "<<s;
 getch();
 return 0;
}
Prime numbers between 1 to 100
#include<iostream.h>
#include<conio.h>
main()
{
 int r;
 clrscr();
 for(int i=1;i<100;i++)
 {
  for(int j=2;j<=i;j++)
   {
    r=i%j;
    if(r==0)
     break;
    }
  if(i==j)
  cout<<j<<endl;
  }
 getch();
 return 0;
}
Sorting
#include<iostream.h>
#include<conio.h>
main()
{
 int a[5],i,j,t,n;
 clrscr();
 cout<<"\n Enter size of the array";
 cin>>n;
 cout<<"\n Enter the array values";
 for(i=0;i<n;i++)
  cin>>a[i];
 for(i=0;i<n;i++)
 {
  for(j=i+1;j<5;j++)
   {
     if(a[i]>a[j])
     {
     t=a[i];
     a[i]=a[j];
     a[j]=t;
   } }
  }
  cout<<"\n Sorted Array";
  for(i=0;i<5;i++)
   cout<<a[i]<<"\n";
  getch();
//  return 0;
 }
Bank Transactions
#include<iostream.h>
#include<conio.h>
class bank
{
 public:

  char name[20],type[10];int ch;
  long int no,nbalance,ebalance,deposit,debit;

  void get()
  {
   cout<<"\n\nA/C Holder Name:";
   cin>>name;
   cout<<"\n\nA/C Number:";
   cin>>no;
   cout<<"\n\nA/C type:";
   cin>>type;
   cout<<"\n\nA/C Balance:";
   cin>>ebalance;
  }
  void trans();
};

  void bank::trans()
  {
   cout<<"\n1.Deposit \n\n2.Debit";
   cout<<"\n\nEnter choice:";
   cin>>ch;
   switch(ch)
   {
    case 1: cout<<"\n\nEnter amount to be deposited:";
                cin>>deposit;
                nbalance=ebalance+deposit;
                cout<<"\n\nnew balance :"<<nbalance;
                break;

    case 2: cout<<"\n\nenter the debiting amount:";
                cin>>debit;
                if(debit>ebalance)
                {
                 cout<<"\n\namount can't be withdrawn:";
                }
                 else

                {
                 nbalance=ebalance-debit;
                 cout<<"\n\nnew balance:"<<nbalance;
                }
                break;
    }
   }

 main()
 {
  bank b;
  clrscr();
  b.get();
  b.trans();
  getch();
  return 0;
 }
Quick sort using class template
#include<iostream.h>
#include<conio.h>
template<class T>
class sort
{
public:
T a[20];
//public:
int n;
void get();
void quick(T a[],int lb,int ub);
void disp();
};
template<class T>
void sort <T>::get()
{int i;
cout<<"Enter array size:";
cin>>n;
1cout<<"\nEnter values:";
for(i=0;i<n;i++)
cin>>a[i];
}
template<class T>
void sort<T>::quick(T a[],int lb,int ub)
{
 int i,j,temp,pivot;
 if(lb<ub)
 {
  i=lb+1;
  j=ub;
  pivot=a[lb];
  while(1)
  {
   while((a[i]<pivot)&&(i<=ub))
    i++;
   while((a[j]>pivot)&&(j>lb))
    j--;
   if(i<j)
   {
    temp=a[i];
    a[i]=a[j];
    a[j]=temp;
    i++;j--;
   }
   else
    break;
  }
  a[lb]=a[j];
  a[j]=pivot;
  quick(a,lb,j-1);
  quick(a,j+1,ub);
 }
}
template<class T>
void sort<T>::disp()
{      int i;
cout<<"\n Elements after sorting:";
 for(i=0;i<n;i++)
  cout<<a[i];
}
int main()
{
clrscr();
sort<int>s1;
s1.get();
s1.quick(s1.a,0,s1.n-1);
s1.disp();
sort<float>s2;
s2.get();
s2.quick(s2.a,0,s2.n-1);
s2.disp();
sort<char>s3;
s3.get();
s3.quick(s3.a,0,s3.n-1);
s3.disp();
getch();
}
Implementation of dynamic casting
#include<iostream.h>
#include<conio.h>
class sample1
{
public:
virtual void disp()
{
}
};
class sample2:public sample1
{
public:
void disp()
{
cout<<"Hi";
}
};
main()
{
sample1 *ptr1,s1;
sample2 *ptr2,s2;
ptr1=&s2;
ptr2=dynamic_cast<sample2 *>(ptr1);
ptr1=dynamic_cast<sample1 *>(&s2);
if(!ptr2)
cout<<"\nCasting succeeded";
else
cout<<"\nCasting failed";
getch();
return 0;
}

Dynamic polymorphism using friend functions
#include<iostream.h>
#include<conio.h>
class matrix
{
public:
 int **a;
 int c,r,i,j,k;

  matrix()
  {
  }
  matrix(int r1,int c1);
  matrix operator =(matrix );
  friend matrix operator *(matrix,matrix );
  void disp();
  };

matrix::matrix(int r1,int c1)
{
 r=r1;c=c1;
 *a=new int[r];
 for(i=0;i<r;i++)
 {
  a[i]=new int[c];
 }
 cout<<"enter elements:";
 for(i=0;i<r;i++)
 {
  for(j=0;j<c;j++)
  {
   cin>>a[i][j];
  }
 }
}

matrix matrix :: operator=(matrix m)
{
 r=m.r;c=m.c;
 *a=new int[r];
 for(i=0;i<r;i++)
 {
  for(j=0;j<c;j++)
  {
   a[i][j]=m.a[i][j];
  }
 }return *this;
}

matrix operator *(matrix m1,matrix m2)
{
 matrix m;int i,j,k;
 m.r=m2.r;m.c=m2.c;
 for(i=0;i<m.r;i++)
 {
  for(j=0;j<m.c;j++)
  {
   m.a[i][j]=0;
   for(k=0;k<m.c;k++)
    {
     m.a[i][j]+=m1.a[i][k]*m2.a[k][j] ;
    }
   }
  }return m;
}

void matrix :: disp()
{
 cout<<"resultant matrix:\n";
 for(i=0;i<r;i++)
 {
  for(j=0;j<c;j++)
  {
   cout<<a[i][j]<<"\t";
  }
  cout<<"\n";
 }
}

int main()
{
 int a,b;
 clrscr();
 cout<<"enter row & column:";
 cin>>a>>b;
 matrix m1(a,b);
 matrix m2=m1;
 matrix m3;
 m3=m1*m2;
 m3.disp();
 getch();
 return 0;
}

Generation of random numbers for Operations on Complex numbers
#include<iostream.h>
#include<conio.h>
#include<fstream.h>
#include<stdlib.h>
class complex
{
 public:
  int real,img;
  complex()
  {    }
  complex(int i,int j)
  {
   real=i;
   img=j;                                                ;
void main()
{
 clrscr();
 ofstream out("file1.txt");
 int s,n;
 cout<<"\n Enter the range ";
 cin>>n;
 randomize();
 int real=random(n);
 int img=random(n);
 out<<"("<<real<<"+i"<<img<<")";
 s=random(4);
 switch(s)
 {
  case 0:  out<<"+"; break;
  case 1:  out<<"-"; break;
  case 2:  out<<"*"; break;
  case 3:  out<<"/"; break;
  default: break;
 }
 real=random(n);
 img=random(n);
 out<<"("<<real<<"+i"<<img<<")";
 out.close();
 ifstream in("file1.txt");
 char sy,op;
 in>>sy>>real>>sy>>sy>>img>>sy;
 in>>op;
 complex c1(real,img);
 in>>sy>>real>>sy>>sy>>img>>sy;
 complex c2(real,img);
 switch(op)
 {
  case '+': real=c1.real+c2.real;
                img=c1.img+c2.img;
                break;
  case '-': real=c1.real-c2.real;
                img=c1.img-c2.img;
                break;
  case '*': real=(c1.real*c2.real)-(c1.img*c2.img);
                img=(c1.real+c2.img)+(c1.img*c2.real);
                break;
  case '/': real=((c1.real*c2.real)+(c1.img*c2.img))/((c2.real*c2.real)+(c2.img*c2.img));
                img=((c1.img*c2.real)-(c1.real*c2.img))/((c2.real*c2.real)+(c2.img*c2.img));
                break;
  default : break;
 }
 ofstream nout("file2.txt");
 nout<<"("<<real<<"+i"<<img<<")";
 cout<<"\nOutput  File has been Created!";
 getch();
}
Implementation of function overloading
#include<iostream.h>
#include<conio.h>

class func
{
public:
 int a,l,b;
 int a1,a2;
 float r;
 float a3;
 void area(int );
 void area(int ,int );
 void area(float );
};

void func::area(int a)
{
 a1=a*a;
 cout<<"\n\narea of square:"<<a1;
}

void func::area(int l,int b)
{
 a2=l*b;
 cout<<"\n\narea of rectangle:"<<a2;
}

void func::area(float r)
{
 a3=3.14*r*r;
 cout<<"\n\narea of circle:"<<a3;
}

int main()
{
 func f;
 clrscr();
 cout<<"\n\nenter side a:";
 cin>>f.a;
 cout<<"\n\nenter length l:";
 cin>>f.l;
 cout<<"\n\nenter breadth b:";
 cin>>f.b;
 cout<<"\n\nenter radius r:";
 cin>>f.r;
 f.area(f.a);
 f.area(f.l,f.b);
 f.area(f.r);
 getch();
 return(0);
}

Implementation of function template in various types of sorting
#include<iostream.h>
#include<conio.h>

                               /*BUBBLE SORT*/
template<class T>
void bubble(T a[],int n)
{
 int i,j;
 for(i=0;i<n;i++)
 {
  for(j=i+1;j<n;j++)
  {
   if(a[i]>a[j])
   {
    T temp=a[i];
    a[i]=a[j];
    a[j]=temp;
   }  } }
 cout<<"\n Elements after sorting :";
 for(i=0;i<n;i++)
  cout<<a[i]<<"\t";
}                             /*INSERTION SORT*/
template<class T1>
void insert(T1 a[],int n)
{
 int j,p,i;
 for(p=1;p<n;p++)
 {
  T1 temp=a[p];
  for(j=p;j>0 && a[j-1]>temp;j--)
   a[j]=a[j-1];
  a[j]=temp;
 }
 cout<<"\n Elements after sorting :";
 for(i=0;i<n;i++)
  cout<<a[i]<<"\t";
}
                                      /* MERGE SORT*/
int leftend,tmppos,numelem,i;
template<class T2>
void mergesort(T2 a[],int n)
{ int i;
 T2 *tmparray;
 tmparray=new T2;
 if(tmparray!=NULL)
 {  msort(a,tmparray,0,n-1);
    delete tmparray;
 }
 cout<<"\n Elements after sorting :";
 for(i=0;i<n;i++)
 cout<<a[i]<<"\t";
}template<class T2>
void msort(T2 a[],T2 tmparray[],int left,int right)
{ int center;
 if(left<right)
 {  center=(left+right)/2;
  msort(a,tmparray,left,center);
  msort(a,tmparray,center+1,right);
  merge(a,tmparray,left,center+1,right);
 }}
template<class T2>
void merge(T2 a[],T2 tmparray[],int lpos,int rpos,int rightend)
{ leftend=rpos-1;
 tmppos=lpos;
 numelem=rightend-lpos+1;
 while(lpos<=leftend && rpos<=rightend)
 {  if(a[lpos]<=a[rpos])
   tmparray[tmppos++]=a[lpos++];
  else
   tmparray[tmppos++]=a[rpos++];
 }
 while(lpos<=leftend)
  tmparray[tmppos++]=a[lpos++];
  while(rpos<=rightend)
   tmparray[tmppos++]=a[rpos++];
   for(i=0;i<numelem;i++,rightend--)
    a[rightend]=tmparray[rightend];
}
int main()
{
 int n,i,ch;int a[10];float b[10];char c[10];
 clrscr();
 cout<<"\n Enter size of array ";
 cin>>n;
 cout<<"\n Enter integer elements:";
 for(i=0;i<n;i++)
  cin>>a[i];
  cout<<"\n Enter float  elements:";
 for(i=0;i<n;i++)
  cin>>b[i];
 cout<<"\n Enter character elements:";
 for(i=0;i<n;i++)
  cin>>c[i];
 cout<<"\n1.Bubble Sort\n2.Insertion\n3.Merge Sort\n";
 do
 {
 cout<<"\nEnter your choice:";
 cin>>ch;
 switch(ch)
 {
  case 1: bubble(a,n);
              bubble(b,n);
              bubble(c,n);
              break;
  case 2: insert(a,n);
              insert(b,n);
              insert(c,n);
              break;
  case 3: mergesort(a,n);
              mergesort(b,n);
              mergesort(c,n);
              break;
  default:
              cout<<"\n\nEnter correct choice";
              break;
 } }while(ch<4);
 getch();
 return 0;
}

Implementation of stack  using class
#include<iostream.h>
#include<conio.h>
           //STACK
class stack
{
 int a[20],top,size;
};
main()
{
            stack s;
            int ch,n,i;
            top=-1;
            cout<<"\nEnter the size of the stack: ";
            cin>>s.size;
            cout<<"\n1.Push  2.Pop  3.Display";
            do
            {
            cout<<"\nEnter the choice:";
            cin>>ch;
            switch(ch)
            {
            case 1:try
                           {
                           if(s.top<s.size-1)
                           {
                           cout<<"\nEnter the no to push:";
                           cin>>n;
                           s.top=s.top+1;
                           s.a[top]=n;
                           }
                           else
                           {
                           throw(s.size);
                           }
                           }
                           catch(int)
                           {
                        cout<<"\nStack overflow";
                           }
                           break;
            case 2:try
                           {
                           if(s.top!=-1)
                           {
                           cout<<"\nPoped element is ",s.a[top];
                           s.top--;
                           }
                           else
                           {
                           throw(s.top);
                           }
                           }
                           catch(int)
                           {
                           cout<<"\nStack underflow";
                           }
                           break;
            case 3:try
                           {
                           if(s.top!=-1)
           {
           cout<<"\nElements in the stack:";
                           for(i=s.top;i>=0;i--)
                           cout<<s.a[i]<<"\t";
                           }                              
                           else
                           {
           throw(s.top);
                           }
                           }
                           catch(int)
                           {
                           cout<<"Stack Underflow";
                           }
                           break;
    default:cout<<"\nEnter valid choice...";
                            break;
                           }
                           }
                           while(ch<4);
           return 0;
                           getch();
                           }

Overloading of [] operator

#include<iostream.h>
#include<conio.h>
#include<assert.h>
class sample
{
int a[10];
public:
int& operator[](int );
};
int& sample::operator[](int n)
{
assert(n>=0 && n<10);
return a[n];
}
main()
{
sample s;
int i;
clrscr();
cout<<"\nEnter the index:";
cin>>i;
s[i]=5;
cout<<"\nValue is"<<s[i];
getch();
return 0;
}

OUTPUT:
Enter the index:15
Assertion failed: n>=0 && n<10, file F:OOPS\INDEX.CPP, line 12
Abnormal program termination

Enter the index:5
Value is 5


Student mark list using class
 #include<iostream.h>
 #include<conio.h>
 #include<string.h>
 class student
 {
 public:
 char name[50], result[10];
 int flag,rno,i,j,m[5],tot;
 float avg;
 void get();
 void calc();
 void disp();
 };
 void student::get()
 {
 cout<<"Enter name:";
 cin>>name;
 cout<<"\n\nenter rno:";
 cin>>rno;
 for(i=0;i<5;i++)
 {
 cout<<"\n\nenter mark "<<(i+1)<<": ";
 cin>>m[i];
 }
 }
 void student::calc()
 {
 tot = 0;
 for(i=0;i<5;i++)
 tot=tot+m[i];
 avg=float(tot/5);
 for(i=0;i<5;i++)
 {
 if(m[i]<50)
 {
 flag = 0;
 strcpy(result,"fail");
 }
 }
 if(flag!=0)
 {
 if((avg>=85)&&(avg<=100))
 strcpy(result,"distinction");
 else if((avg>=65)&&(avg<85))
 strcpy(result,"first class");
 else if((avg>=50)&&(avg<65))
 strcpy(result,"second class");
}
}
void student::disp()
{
cout<<rno<<"\t"<<name;
for(i=0;i<5;i++)
{
cout<<"\t"<<m[i];
}
cout<<"\t"<<tot<<"\t"<<avg;
cout<<"\t"<<result;
}

 int main()
{
 student s[23];
 int n,j;
 clrscr();
 cout<<"enter no of students : ";
 cin>>n;
 for(j=0;j<n;j++)
 {
 cout<<endl<<endl<<"Enter details of student "<<(j+1)<<endl<<endl;
 s[j].get();
 s[j].calc();
 }
 cout<<"\n\nrno\tname\tm1\tm2\tm3\tm4\tm5\ttotal\tavg\tclass\n\n";
 for(j=0;j<n;j++)
 {
 s[j].disp();

 }
    getch();
    return 0;
 }


Matrix multiplication using friend functions and default arguments
#include<iostream.h>
#include<conio.h>
class vector;
class matrix
{
 public:
    int a[5][5],r,c;
    void get1(int r1,int c1=2);
    void disp1();
    friend void multiply(matrix ,vector );
};
class vector
{
  public:
      int b[5];int j,n;
      void get2();
      void disp2();
      friend void multiply(matrix ,vector );
};
void matrix::get1()
{
//cout<<"\n\nenter rows & columns m & n:";
//cin>>m>>n;
cout<<"\n\nenter matrix values : \n\n";

 for(i=0;i<r;i++)
 {
  for(j=0;j<c;j++)
  {
   cin>>a[i][j];
  }
 }
}

void matrix::disp1()
{
 cout<<"\n\nmatrix: \n\n";

 for(i=0;i<r;i++)
 {
  for(j=0;j<c;j++)
  {
   cout<<a[i][j]<<"\t";
  }
  cout<<"\n";
 }
}

void vector::get2()
{
 cout<<"\n\nenter value equal to n:";
 cin>>n;
 cout<<"\n\nenter matrix values : \n\n";

 for(j=0;j<n;j++)
 {
  cin>>b[j];
 }
}

void vector::disp2()
{
 cout<<"\n\nvector: \n\n";

 for(j=0;j<n;j++)
 {
  cout<<b[j]<<"\n";
 }
}

void multiply(matrix m1,vector v1)
{
if(m1.c==v1.n)
{
 int c[5],i,j;
 cout<<"\n\nresultant vector:\n\n";
 for(i=0;i<m1.r;i++)
 {
  c[i]=0;
  for(j=0;j<v1.c;j++)
  {
  c[i]+=m1.a[i][j]*v1.b[j];
  }
  cout<<c[i]<<"\n";
 }
}

else
cout<<"multiplication is not possible:";

int main()
{
 int p;
 cout<<"enter row:";
 cin>>p;
 cout<<"enter "
 matrix m1;
 vector v1;
 clrscr();
 m1.get1(p);
 m1.disp1();
 v1.get2();
 v1.disp2();
 multiply(m1,v1);
 getch();
 return 0;
}

Overloading of new and delete operator
#include<iostream.h>
#include<conio.h>
class sample
{
char name[20];
int rno;int i,m[5],total;float avg;
public:
 void *operator new(size_t s)
 {
  void *ptr=::new sample[s];
  cout<<"\n\nMemory Allocated:"<<s<<"bytes";
  return ptr;
 }
  void get();
  void calc();
  void disp();
  void operator delete(void *p)
  {
   ::delete p;
   cout<<"\n\n Memory deleted";
   }
};
void sample::get()
  {
  cout<<"\n\n enter name:";
  cin>>name;
  cout<<"\n enter roll no:";
  cin>>rno;
  cout<<"\nEnter 3 marks:\n";
  for(i=0;i<3;i++)
  {
  cin>>m[i];
  }
  }
  void sample::calc()
  {
  total=0;
  for(i=0;i<3;i++)
  {
  total=total+m[i];
  }
  avg=total/3;
  }
  void sample::disp()
  {
  cout<<"name\trollno\tm1\tm2\tm3\ttotal\taverage";
  cout<<"\n"<<name<<"\t"<<rno<<"\t";
  for(i=0;i<3;i++)
  {
  cout<<m[i]<<"\t";
  }
  cout<<total<<"\t"<<avg<<"\n";
  }
int main()
{
 int n,j;
 clrscr();
 cout<<"\nEnter nof students:";
 cin>>n;
 sample *s=new sample;
 for(j=0;j<n;j++)
 {
 s[j].get();
 s[j].calc();
 s[j].disp();
 }
 delete s;
 getch();
 return 0;
}

Implementation of typeid using non polymorphic objects

 #include<iostream.h>
 #include<conio.h>
 #include<typeinfo>
 class sample
 {
 public:
  void disp()
  {
  cout<<"Hai";
  }
  };
  int main()
  {
  int x,y;
  char a;
  sample s;
  clrscr();
  cout<<"typeid of x:"<<typeid(x).name ;
  if(typeid(x)==typeid(y))
  cout<<"Same data type";
  if(typeid(a)!=typeid(y))
  cout<<"Different data type";
  return 0;
  getch();
  }

Implementation of Arithmetic operator overloading
#include<iostream.h>
#include<conio.h>
class complex
{
 int a,b;
 //double a2,b2;
 public:
  complex(int x)
  {
   a=x;
   b=x;
  }
  complex(double y)
  {
   a=y;
   b=y;
  }
  complex()
  {
  }
  void disp()
  {
   //if(b>0)
   cout<<"\n\ncomplex no : ";
   cout<<a<<"+i"<<b;
  }
  complex operator +(complex c2)
  {
   complex c;
   c.a=a+c2.a;
   c.b=b+c2.b;
   return c;
  }
  complex operator -(complex c2)
  {
   complex c;
   c.a=a-c2.a;
   c.b=b-c2.b;
   return c;
  }
  complex operator *(complex c2)
  {
   complex c;
   c.a=((a*c2.a)-(b*c2.b));
   c.b=((a*c2.b)+(b*c2.a));
   return c;
  }
  complex operator /(complex c2)
  {
   complex c;
   c.a=((a*c2.a)+(b*c2.b))/((c2.a*c2.a)+(c2.b*c2.b));
   c.b=((a*c2.b)-(b*c2.a))/((c2.a*c2.a)+(c2.b*c2.b));
   return c;
  }
};

main()
{
 int n;double m;
 clrscr();
 cout<<"\n\nenter integer value:";
 cin>>n;
 cout<<"\n\nenter float value:";
 cin>>m;
 complex c1=complex(n);
 complex c2=complex(m);
 complex c3;
 c1.disp();
 c2.disp();
 cout<<"\n\ncomplex addition : \n";
 c3=c1.operator +(c2);
 c3.disp();
 cout<<"\n\ncomplex subtraction :\n";
 c3=c1.operator -(c2);
 c3.disp();
 cout<<"\n\ncomplex multiplication :\n";
 c3=c1.operator *(c2);
 c3.disp();
 cout<<"\n\ncomplex division :\n";
 c3=c1.operator /(c2);
 c3.disp();
 getch();
 return 0;
}



Implementation of static functions and static data members

#include<iostream.h>
#include<conio.h>
                             /*static data members*/
class sample
{
public:
static int a;
void get()
{
a++;
}
static void disp1()
{
cout<<"Using static d"
cout<<"a:"<<a<<"\n";
}
void disp()
{
cout<<"a:"<<a<<"\n";
}
};
int sample::a=5;

main()
{
 sample s1;
 clrscr();

 s1.get();
 s1.disp();
 sample::disp1();
 getch();
 return 0;
}

Implementation of namespace

#include<iostream>
using namespace std;
namespace sample
{
 int ub;
 int lb;
 class counter
 {
  int count;
  public:
  counter (int n)
  {
   if(n<=ub)
    count=n;
   else
    count=ub;
  }
  void reset(int n)
  {
   if(n<=ub)
    count=n;
  }
  int run()
  {
   if(count>lb)
    return count--;
   else
    return lb;
  }
 };
}
int main()
{
 using namespace sample;
 ub=100;
 lb=0;
 counter c1(10);
 int i;
 cout<<"\n Values of object c1:";
 do
 {
  i=c1.run();
  cout<<i<<"\t";
 }while(i>lb);
 counter c2(20);
 cout<<"\n Values of object c2:";
 do
 {
  i=c2.run();
  cout<<i<<"\t";
 }while(i>lb);
 c2.reset(100);
 lb=90;
cout<<”\n  Values of object c2:
 cout<<"\n After Resetting  :";
 do
 {
  i=c2.run();
  cout<<i<<"\t";
 }while(i>lb);
}

OUTPUT:
 Values of object c1 :10     9       8       7       6       5       4       3       2       1       0
Values of object c2 :20     19      18      17      16      15      14      13      12      11      10           9       8       7       6       5       4       3        2       1       0
 Values of object c2:
After Resetting  :100       99      98      97      96      95      94      93      92      91      90