AIM:
To write a sql program in ddl commands.
PROGRAM:
SQL> create table student(stid number, stname varchar(15), staddr varchar(15),
stmob number(15));
Table created.
SQL> desc student;
Name Null? Type
----------------------------- -------- ------------------------
STID NUMBER
STNAME VARCHAR2(15)
STADDR VARCHAR2(15)
STMOB NUMBER(15)
SQL> alter table student add stmark number;
Table altered.
SQL> desc student;
Name Null? Type
--------------------------------- -------- --------------------
STID NUMBER
STNAME VARCHAR2(15)
STADDR VARCHAR2(15)
STMOB NUMBER(15)
STMARK NUMBER
SQL> alter table student rename to student_info;
Table altered.
SQL> desc student;
ERROR:
ORA-04043: object student does not exist
SQL> desc student_info;
Name Null? Type
-------------------------------- -------- ----------------
STID NUMBER
STNAME VARCHAR2(15)
STADDR VARCHAR2(15)
STMOB NUMBER(15)
STMARK NUMBER
SQL> drop table student_info;
Table dropped.
SQL> desc student_info;
ERROR:
ORA-04043: object student_info does not exist
****************END OF THE PROGRAM****************
RESULT:
Thus the program has been executed successfully.
......... ❤️🩹........ ⚠️
Ex.No: 2
DML COMMANDS Date:
AIM:
To write a sql program in dml commands.
PROGRAM:
SQL> create table students(stid number, stname varchar(30), staddr
varchar(30), stmob number(15));
Table created.
SQL> desc students;
Name Null? Type
--------------------------------- -------- ----------------
STID NUMBER
STNAME VARCHAR2(30)
STADDR VARCHAR2(30)
STMOB NUMBER(15)
SQL> insert into students values(101, 'kavi', 'Ramnad', 9874532156);
1 row created.
SQL> insert into students values(102, 'Mani', 'Madurai', 9876534895);
1 row created.
SQL> insert into students values(103, 'Jenisha', 'Rameswaram', 9326419874);
1 row created.
SQL> insert into students values(104, 'Mona', 'Ramnad', 8452654172);
1 row created.
SQL> insert into students values(105, 'Monisha', 'Chennai', 7458962112);
1 row created.
SQL> select*from students;
STID STNAME STADDR STMOB
---------- ---------------- ---------------- -------------
101 kavi Ramnad 9874532156
102 Mani Madurai 9876534895
103 Jenisha Rameswaram 9326419874
104 Mona Ramnad 8452654172
105 Monisha Chennai 7458962112
SQL> update students set stname='kavitha' where stid =101;
1 row updated.
SQL> select*from students;
STID STNAME STADDR STMOB
---------- ---------------- ---------------- -------------
101 kavitha Ramnad 9874532156
102 Mani Madurai 9876534895
103 Jenisha Rameswaram 9326419874
104 Mona Ramnad 8452654172
105 Monisha Chennai 7458962112
SQL> delete from students where stid=103;
1 row deleted.
SQL> select*from students;
STID STNAME STADDR STMOB
---------- ---------------- ---------------- -------------
101 kavi Ramnad 9874532156
102 Mani Madurai 9876534895
104 Mona Ramnad 8452654172
105 Monisha Chennai 7458962112
****************END OF THE PROGRAM****************
RESULT:
Thus the program has been executed successfully.
....... ⚠️ 😏......
Ex.No: 3
TCL COMMANDS Date:
AIM:
To write a sql program in tcl commands.
PROGRAM:
SQL> select*from studentlist;
STROLLNO STNAME STMARK1 STMARK2 STMARK3 TOTAL
------------- ------------------ ------------- ------------ ------------ ----------
101 kavi 98 66 88 252
102 Mani 98 88 55 241
103 Rajesh 97 87 77 261
104 Mala 87 77 67 285
105 Malini 67 57 47 231
106 Vinoth 97 67 57 261
107 Shalini 87 77 67 289
108 Kalai 98 97 96 289
109 Priya 100 99 98 297
110 Palin 99 75 98 278
10 rows selected.
SQL> commit;
Commit complete.
SQL> delete from studentlist where strollno=109;
1 row deleted.
SQL> select *from studentlist;
STROLLNO STNAME STMARK1 STMARK2 STMARK3 TOTAL
------------- ------------------ ------------- ------------ ------------ ----------
101 kavi 98 66 88 252
102 Mani 98 88 55 241
103 Rajesh 97 87 77 261
104 Mala 87 77 67 285
105 Malini 67 57 47 231
106 Vinoth 97 67 57 261
107 Shalini 87 77 67 289
108 Kalai 98 97 96 289
110 Palin 99 75 98 278
9 rows selected.
SQL> rollback;
Rollback complete.
SQL> select*from studentlist;
STROLLNO STNAME STMARK1 STMARK2 STMARK3 TOTAL
------------- ------------------ ------------- ------------ ------------ ----------
101 kavi 98 66 88 252
102 Mani 98 88 55 241
103 Rajesh 97 87 77 261
104 Mala 87 77 67 285
105 Malini 67 57 47 231
106 Vinoth 97 67 57 261
107 Shalini 87 77 67 289
108 Kalai 98 97 96 289
109 Priya 100 99 98 297
110 Palin 99 75 98 278
10 rows selected.
SQL> savepoint stmark1;
Savepoint created.
SQL> rollback to savepoint stmark1;
Rollback complete.
SQL> select *from studentlist;
STROLLNO STNAME STMARK1 STMARK2 STMARK3 TOTAL
------------- ------------------ ------------- ------------ ------------ ----------
101 kavi 98 66 88 252
102 Mani 98 88 55 241
103 Rajesh 97 87 77 261
104 Mala 87 77 67 285
105 Malini 67 57 47 231
106 Vinoth 97 67 57 261
107 Shalini 87 77 67 289
108 Kalai 98 97 96 289
109 Priya 100 99 98 297
110 Palin 99 75 98 278
****************END OF THE PROGRAM****************
RESULT:
Thus the program has been executed successfully.
...... ⚠️.......
Ex.No: 4
FIBONACCI SERIES Date:
AIM:
To write a sql program to calculate Fibonacci series
PROGRAM:
SQL> set serveroutput on;
SQL> declare
a number(3):=1;
b number(3):=1;
c number(3);
n number(2):=&n;
begin
dbms_output.put_line('Fibonacci Series');
dbms_output.put_line(a);
dbms_output.put_line(b);
for j in 3..n
loop
c:=a+b;
dbms_output.put_line(c);
a:=b;
b:=c;
end loop;
end;
/
*****************End of the Program***********************
OUTPUT:
Enter value for n: 5
old 5: n number(2):=&n;
new 5: n number(2):=5;
Fibonacci Series
1
1
2
3
5
PL/SQL procedure successfully completed.
RESULT:
Thus the program has been executed successfully.
..... ⚠️.....
Ex.No: 5
FACTORIAL Date:
AIM:
To write a sql program to calculate factorial.
PROGRAM:
SQL> set serveroutput on;
SQL> declare
n number;
fact number:=1;
i number;
begin
n:=&n;
for i in 1..n
loopf
act:=fact*i;
end loop;d
bms_output.put_line('Factorial :='||fact);
end;
/
*****************End of the Program***********************
OUTPUT:
Enter value for n: 5
old 6: n:=&n;
new 6: n:=5;
Factorial :=120
PL/SQL procedure successfully completed.
RESULT:
Thus the program has been executed successfully.
...... ⚠️.....
Ex.No: 6
STRING REVERSE Date:
AIM:
To write a sql program to determine String Reverse
PROGRAM:
SQL> set serveroutput on;
SQL> declare
str1 varchar2(50):='&str1';
str2 varchar2(50);
len number;
i number;
begin
len:=length(str1);
for i in reverse 1..len
loop
str2:=str2||substr(str1,i,1);
end loop;
dbms_output.put_line('Reverse of String is:'||str2);
end;
/
*****************End of the Program***********************
OUTPUT:
Enter value for str1: hello world
old 2: str1 varchar2(50):='&str1';
new 2: str1 varchar2(50):='hello world';
Reverse of String is:dlrow olleh
PL/SQL procedure successfully completed.
RESULT:
Thus the program has been executed successfully.
.... ⚠️....
Ex.No: 7
SUM OF SERIES Date:
AIM:
To write a sql program to calculate sum of series
PROGRAM:
SQL> set serveroutput on;
SQL> declare
n number(15);
temp number(15);
r number(15);
begin
n:=&num1;
temp:=0;
while n<>0
loop
r:=mod(n,10);
temp:=temp+r;
n:=trunc(n/10);
end loop;
dbms_output.put_line('Sum of Series='||temp);
end;
/
*****************End of the Program***********************
OUTPUT:
Enter value for num1: 234
old 6: n:=&num1;
new 6: n:=234;
Sum of Series=9
PL/SQL procedure successfully completed.
RESULT:
Thus the program has been executed successfully.
... ⚠️...
Ex.No: 8
TRIGGER Date:
AIM:
To write a sql program to determine trigger.
PROGRAM:
SQL> create table emp1(Emp_name varchar(15),Emp_id number,salary
number);
Table created.
SQL> create table emp2(Emp_name varchar(15),Emp_id number, salary
number);
Table created.
SQL> insert into emp1 values('kavi',101,15000);
1 row created.
SQL> insert into emp1 values('Pavi',102,13000);
1 row created.
SQL> insert into emp1 values('Vino',103,20000);
1 row created.
SQL> insert into emp1 values('Krish',104,18000);
1 row created.
SQL> insert into emp1 values('Rani',105,10000);
1 row created.
SQL> select*from emp1;
EMP_NAME EMP_ID SALARY
------------------- ---------- -------------
kavi 101 15000
Pavi 102 13000
Vino 103 20000
Krish 104 18000
Rani 105 10000
SQL> insert into emp2 values('Kavi',101,15000);
1 row created.
SQL> insert into emp2 values('Pavi',102,13000);
1 row created.
SQL> insert into emp2 values('Vino',103,20000);
1 row created.
SQL> insert into emp2 values('Krish',104,18000);
1 row created.
SQL> insert into emp2 values('Rani',105,10000);
1 row created.
SQL> select*from emp2;
EMP_NAME EMP_ID SALARY
------------------- ---------- -------------
kavi 101 15000
Pavi 102 13000
Vino 103 20000
Krish 104 18000
Rani 105 10000
SQL> truncate table emp2;
Table truncated.
SQL> select*from emp2;
no rows selected
SQL> create or replace trigger first
2 before delete on emp1
3 for each row
4 begin
5 insert into emp2 values(:old.emp_name,:old.emp_id,:old.salary);
6 end;
7 /
Trigger created.
SQL> delete emp1 where emp_id=101;
1 row deleted.
SQL> select *from emp2;
EMP_NAME EMP_ID SALARY
------------------- ---------- -------------
kavi 101 15000
SQL> select *from emp1;
EMP_NAME EMP_ID SALARY
------------------- ---------- -------------
Pavi 102 13000
Vino 103 20000
Krish 104 18000
Rani 105 10000
SQL> delete emp1 where salary=10000;
1 row deleted.
SQL> select*from emp2;
EMP_NAME EMP_ID SALARY
------------------- ---------- -------------
kavi 101 15000
Rani 105 10000
SQL> select * from emp1;
EMP_NAME EMP_ID SALARY
------------------- ---------- -------------
Pavi 102 13000
Vino 103 20000
Krish 104 18000
RESULT:
Thus the program has been executed successfully.
.... ⚠️....
Ex.No: 9
STUDENT MARK ANALYSIS USING CURSOR Date:
AIM:
To write a sql program to determine student mark analysis using cursor
PROGRAM:
SQL> create table studentlist(strollno number,stname varchar2(30), stmark1
number, stmark2 number, stmark3 number, total number);
Table created.
SQL> desc studentlist;
Name Null? Type
--------------------------------- -------- ----------------------------
STROLLNO NUMBER
STNAME VARCHAR2(30)
STMARK1 NUMBER
STMARK2 NUMBER
STMARK3 NUMBER
TOTAL NUMBER
SQL> insert into studentlist values(101, 'kavi',98,66,88,252);
1 row created.
SQL> insert into studentlist values(102, 'Mani',98,88,55,241);
1 row created.
SQL insert into studentlist values(103,'Rajesh',97,87,77,261);
1 row created.
SQL> insert into studentlist values(104,'Mala',87,77,67,285);
1 row created.
SQL> insert into studentlist values(105,'Malini',67,57,47,231);
1 row created.
SQL> insert into studentlist values(106,'Vinoth',97,67,57,261);
1 row created.
SQL> insert into studentlist values(107,'Shalini',87,77,67,289);
1 row created.
SQL> insert into studentlist values(108,'Kalai',98,97,96,289);
1 row created.
SQL> insert into studentlist values(109,'Priya',100,99,98,297);
1 row created.
SQL> insert into studentlist values(110,'Palin',99,75,98,278);
1 row created.
SQL> select*from studentlist;
STROLLNO STNAME STMARK1 STMARK2 STMARK3 TOTAL
----------------- --------------- ------------- -------------- --------------- ----------
101 kavi 98 66 88 252
102 Mani 98 88 55 241
103 Rajesh 97 87 77 261
104 Mala 87 77 67 285
105 Malini 67 57 47 231
106 Vinoth 97 67 57 261
107 Shalini 87 77 67 289
108 Kalai 98 97 96 289
109 Priya 100 99 98 297
110 Palin 99 75 98 278
10 rows selected.
SQL> set serveroutput on;
SQL> declare
strollno studentlist.strollno%type;
stname studentlist.stname%type;
stmark1 studentlist.stmark1%type;
stmark2 studentlist.stmark2%type;
stmark3 studentlist.stmark3%type;
total studentlist.total%type;
cursor temp is select strollno,stname,stmark1,stmark2,stmark3,total from
studentlist order by strollno desc;
begin open temp;
loop
fetch temp into strollno,stname,stmark1,stmark2,stmark3,total;
exit when temp%rowcount>6 or temp%notfound;
dbms_output.put_line(strollno||stname||stmark1||stmark2||stmark3||total);
end loop;
close temp;
end;
/
*****************End of the Program***********************
OUTPUT:
110 Palin 99 75 98 278
109 Priya 100 99 98 297
108 Kalai 98 97 96 289
107 Shalini 87 77 67 289
106 Vinoth 97 67 57 261
105 Malini 67 57 47 231
PL/SQL procedure successfully completed.
RESULT:
Thus the program has been executed successfully.
.... ⚠️.....
Ex.No: 10
LIBRARY MANAGEMENT USING APPLICATION Date:
AIM:
To write a sql program to determine library management using
application.
PROGRAM:
CREATION:
SQL> create table library(bookname varchar(15),author varchar(15),publication
varchar(10),no_ofcopies number);
Table created.
SQL> create table stud(rollno number,name varchar(15),no_card number);
Table created.
SQL> create table bookdetails(book_no number, book_name
varchar(15),available varchar(15),subscribed_to number);
Table created.
SQL> create table subs(book_no number,rollno number, do_sub date, do_return
date,fine_amount number, status varchar(10));
Table created.
DESCRIBE TABLE:
SQL> desc library;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOKNAME VARCHAR2(15)
AUTHOR VARCHAR2(15)
PUBLICATION VARCHAR2(10)
NO_OFCOPIES NUMBER
SQL> desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NUMBER
NAME VARCHAR2(15)
NO_CARD NUMBER
SQL> desc bookdetails;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOK_NO NUMBER
BOOK_NAME VARCHAR2(15)
AVAILABLE VARCHAR2(15)
SUBSCRIBED_TO NUMBER
SQL> desc subscribe;
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOK_NO NUMBER
ROLLNO NUMBER
FINE_AMOUNT NUMBER
STATUS VARCHAR2(10)
INSERTION:
STUDENT:
SQL> insert into stud values(101,'Kavi',1);
1 row created.
SQL> insert into stud values(102,'Pavi',2);
1 row created.
SQL> insert into stud values(103,'Mani',3);
1 row created.
SQL> insert into stud values(104,'Vino',4);
1 row created.
SQL> insert into stud values(105,'Gokul',5);
1 row created.
SQL> insert into stud values(106,'Krish',6);
1 row created.
LIBRARY:
SQL> insert into library values('C++','Balagurusamy','Aruna',6);
1 row created.
SQL> insert into library values('Java','Abraham','Aruna',10);
1 row created.
SQL> insert into library values('CN','Forozon','Sura',7);
1 row created.
SQL> insert into library values('C','Balagurusamy','Surya',5);
1 row created.
SQL> insert into library values('OS','Silberchatz','Surya',8);
1 row created.
BOOKDETAILS:
SQL> insert into bookdetails values(21,'C++','Yes',6);
1 row created.
SQL> insert into bookdetails values(22,'Java','Yes',4);
1 row created.
SQL> insert into bookdetails values(23,'CN','No',1);
1 row created.
SQL> insert into bookdetails values(24,'C','No',2);
1 row created.
SQL> insert into bookdetails values(25,'OS','Yes',5);
1 row created.
SUBSCRIBE:
SQL> insert into subscribe values(11,101,550,'wait');
1 row created.
SQL> insert into subscribe values(12,102,0,'good');
1 row created.
SQL> insert into subscribe values(13,103,0,'good');
1 row created.
SQL> insert into subscribe values(14,104,556,'wait');
1 row created.
SQL> insert into subscribe values(15,105,0,'return');
1 row created.
SELECTION:
SQL> select * from subscribe;
BOOK_NO ROLLNO FINE_AMOUNT STATUS
--------------- ------------- --------------------- ----------
11 101 550 wait
12 102 0 good
13 103 0 good
14 104 556 wait
15 105 0 return
SQL> select*from stud;
ROLLNO NAME NO_CARD
---------- --------------- ----------
101 Kavi 1
102 Pavi 2
103 Mani 3
104 Vino 4
105 Gokul 5
106 Krish 6
6 rows selected.
SQL> select *from bookdetails;
BOOK_NO BOOK_NAME AVAILABLE SUBSCRIBED_TO
----------------- ----------------- ------------------- ------------------
21 C++ Yes 6
22 Java Yes 4
23 CN No 1
24 C No 2
25 OS Yes 5
SQL> select*from library;
BOOKNAME AUTHOR PUBLICATIO NO_OFCOPIES
--------------- --------------- ------------------ ------------------
C++ Balagurusamy Aruna 6
Java Abraham Aruna 10
CN Forozon Sura 7
C Balagurusamy Surya 5
OS Silberchatz Surya 8
SQL> select*from library where author=’balagurusamy’;
BOOKNAME AUTHOR PUBLICATIO NO_OFCOPIES
--------------- --------------- ------------------ ------------------
C++ Balagurusamy Aruna 6
C Balagurusamy Surya 5
SQL> select * from subscribe where fine_amount>0;
BOOK_NO ROLLNO FINE_AMOUNT STATUS
--------------- ------------- --------------------- ----------
11 101 550 wait
14 104 556 wait
SQL> select*from stud order desc;
ROLLNO NAME NO_CARD
---------- --------------- ----------
101 Kavi 1
102 Pavi 2
103 Mani 3
104 Vino 4
105 Gokul 5
106 Krish 6
6 rows selected.
*************END OF THE PROGRAM*************
RESULT:
Thus the program has been executed successfully.
.... ⚠️....
Ex.No: 11
STUDENT MARK ANALYSIS USING APPLICATION Date:
AIM:
To write a sql program to determine student mark analysis using
application.
PROGRAM:
CREATION:
SQL> create table studentlists(student_id number, student_name
varchar(15),department varchar(10), course varchar(10),place varchar(10));
Table created.
SQL> create table subject(subject_id number,subject_name varchar(10));
Table created.
SQL> create table marks(student_id number,subject_id number,marks_obtained
number) ;
Table created.
DESCRIBE TABLE:
SQL> desc studentlists;
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NUMBER
STUDENT_NAME VARCHAR2(15)
DEPARTMENT VARCHAR2(10)
COURSE VARCHAR2(10)
PLACE VARCHAR2(10)
SQL> desc subject;
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBJECT_ID NUMBER
SUBJECT_NAME VARCHAR2(10)
SQL> desc marks;
Name Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NUMBER
SUBJECT_ID NUMBER
MARKS_OBTAINED NUMBER
INSERTION:
SQL> insert into studentlists values(1101,'Kavi','CS','B.Sc','Ramnad');
1 row created.
SQL> insert into studentlists values(1102,'Mani','IT','B.Sc','Ramnad');
1 row created.
SQL> insert into studentlists values(1103,'Vino','BCA','BCA','Ramnad');
1 row created.
SQL> insert into studentlists values(1104,'Krish','CS','B.SC','Ramnad');
1 row created.
SQL> insert into studentlists values(1105,'Gokul','IT','B.SC','Ramnad');
1 row created.
SQL> insert into studentlists values(1106,'Pugal','CA','B.SC','Ramnad');
1 row created.
SQL> insert into studentlists values(1107,'Raja','CA','B.SC','Ramnad');
1 row created.
SUBJECT:
SQL> insert into subject values(100,'Java');
1 row created.
SQL> insert into subject values(101,'C');
1 row created.
SQL> insert into subject values(102,'C++');
1 row created.
SQL> insert into subject values(103,'Phython');
1 row created.
SQL> insert into subject values(104,'DBMS');
1 row created.
SQL> insert into subject values(105,'PHP');
1 row created.
MARKS:
SQL> insert into marks values(1101,100,99);
1 row created.
SQL> insert into marks values(1102,101,78);
1 row created.
SQL> insert into marks values(1103,102,67);
1 row created.
SQL> insert into marks values(1104,103,97);
1 row created.
SQL> insert into marks values(1105,104,45);
1 row created.
SQL> insert into marks values(1106,105,89);
1 row created.
SQL> insert into marks values(1105,106,90);
1 row created.
SELECTION:
SQL> select* from studentlists;
STUDENT_ID STUDENT_NAME DEPARTMENT COURSE PLACE
----------------- ------------------------ ------------------ ------------ ----------
1101 Kavi CS B.Sc Ramnad
1102 Mani IT B.Sc Ramnad
1103 Vino BCA BCA Ramnad
1104 Krish CS B.SC Ramnad
1105 Gokul IT B.SC Ramnad
1106 Pugal CA B.SC Ramnad
1107 Raja CA B.SC Ramnad
7 rows selected.
SQL> select * from subject;
SUBJECT_ID SUBJECT_NA
-------------- -----------------
100 Java
101 C
102 C++
103 Phython
104 DBMS
105 PHP
6 rows selected.
SQL> select * from marks;
STUDENT_ID SUBJECT_ID MARKS_OBTAINED
------------------ ---------------- --------------------------
1101 100 99
1102 101 78
1103 102 67
1104 103 97
1105 104 45
1106 105 89
1105 106 90
7 rows selected.
SQL> select *from marks where marks_obtained>80;
STUDENT_ID SUBJECT_ID MARKS_OBTAINED
------------------ ---------------- --------------------------
1101 100 99
1104 103 97
1106 105 89
1105 106 90
SQL> select sum(marks_obtained) from marks;
SUM(MARKS_OBTAINED)
-------------------
565
*************END OF THE PROGRAM*************
RESULT:
Thus the program has been executed successfully.
... ⚠️..
Comments
Post a Comment