PANIMALAR ENGINEERING COLLEGE

DEPARTMENT OF CSE
Reg No: 211423104697
DDL COMMAND

TABLE CREATION:

SQL> create table class(rollno number,name varchar2(10),age number);

Table created.
DESCRIBING TABLE:

SQL> desc class;

Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NUMBER
NAME VARCHAR2(10)
AGE NUMBER
ALTERING A TABLE

ADDING A NEW CLOUMN:

SQL> alter table class add regno number;

Table altered.
SQL> desc class;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NUMBER
NAME VARCHAR2(10)

AGE NUMBER
REGNO NUMBER
MODIFY:

SQL> alter table class modify rollno varchar2(10);

Table altered.
SQL> desc class;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO VARCHAR2(10)
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
NAME VARCHAR2(10)
AGE NUMBER
REGNO NUMBER
DROPING A CLOUMN:

SQL> alter table class drop column age;

Table altered.
SQL> desc class;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO VARCHAR2(10)
NAME VARCHAR2(10)
REGNO NUMBER
RENAME:

SQL> alter table class rename to details;

Table altered.
SQL> desc details;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO VARCHAR2(10)
NAME VARCHAR2(10)
REGNO NUMBER
TRUNCATE:

SQL> truncate table details;

Table truncated.
SQL> desc details;
Name Null? Type
---------------- -------- ---------------------------- ROLLNO
VARCHAR2(10)
NAME VARCHAR2(10)
REGNO NUMBER
SQL> drop table details;
Table dropped.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697

DML COMMAND

SQL> create table worker(name varchar2(10),age number,salary number);

Table created.
SQL> desc worker;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)

AGE NUMBER SALARY

NUMBER
INSERT:

SQL> insert into worker(name,age,salary) values('ANAND',24,50000);

1 row created.
SQL> desc worker;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)

AGE NUMBER SALARY

NUMBER
SELECT:

SQL> select * from worker;

NAME AGE SALARY
---------- ---------- ---------- ANAND 24 50000
PARTICULAR COLUMN INSERTION:

SQL> insert into worker(name,age,salary) values('ARUN',24,50000);

1 row created.
SQL> select * from worker;
NAME AGE SALARY
---------- ---------- ---------- ANAND 24 50000
ARUN 24 50000
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
UPDATE:

SQL> update worker set age=20 where name='ANAND';

1 row updated.
SQL> select * from worker;
NAME AGE SALARY
---------- ---------- ---------- ANAND 20 50000
ARUN 24 50000
DELETE:

SQL> delete from worker where name='ARUN';

1 row deleted.
SQL> select * from worker;
NAME AGE SALARY
---------- ---------- ----------
ANAND 20 50000

TCL COMMAND
SQL> create table items(name varchar2(10),price number,itemid number);
Table created.
SQL> insert into items(name,price,itemid) values('toy',200,50); 1 row created.
SQL> insert into items(name,price,itemid) values('snacks',500,35); 1 row created.
SQL> insert into items(name,price,itemid) values('perfume',500,18);
1 row created.
SQL> select * from items;
NAME PRICE ITEMID
---------- ---------- ---------- toy 200 50
snacks 500 35 perfume 500 18
COMMIT:

SQL> commit;
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
Commit complete.
SQL> delete from items where name='perfume';
1 row deleted.
SQL> select * from items;
NAME PRICE ITEMID
---------- ---------- ---------- toy 200 50
snacks 500 35 SQL> delete from items
where name='perfume';
1 row deleted.
SAVEPOINT:

SQL> savepoint a; Savepoint created.

SQL> update items set price=100 where name='toy';
1 row updated.
SQL> select * from items;
NAME PRICE ITEMID
---------- ---------- ---------- toy 100 50
snacks 500 35
ROLL BACK:

SQL> roll back a;

Rollback complete.
SQL> select * from items;
NAME PRICE ITEMID
---------- ---------- ----------

toy 200 50

snacks 500 35 perfume 500 18

DML COMMAND

CONNECTING TO DATABASE

SQL> connect system/manager@oracle11g;

Connected.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
CREATING A NEW USER

SQL> create user sample identified by sample1;.

User created.
Enter user-name: sample
Enter password:
ERROR:
ORA-01045: user SAMPLENEW lacks CREATE SESSION privilege; logon denied
GRANT COMMAND

SQL> grant create session to sample; Grant succeeded.

SQL> grant connect, resource to sample;
Grant succeeded.
LOGON TO NEW USER AFTER GIVING THE PERMISSION

Enter user-name: sample

Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ALL PRIVILEGES

SQL>grant all privileges on emp (table name) to sample (user name);

Grant succeeded.
REVOKE ALL PRIVILEGES

SQL> revoke all privileges on emp (table name) from sample (user name);
Revoke succeeded.

AGGREATE FUNCTIONS
SQL> create table stud(name varchar2(10),rollno number,marks number);
Table created.
SQL> desc stud;
Name Null? Type
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
ROLLNO NUMBER
MARKS NUMBER
SQL> insert into stud(name,rollno,marks) values('Anand',101,89); 1 row created.
SQL> insert into stud(name,rollno,marks) values('Ram',102,98); 1 row created.
SQL> insert into stud(name,rollno,marks) values('Sam',103,80); 1 row created.
SQL> insert into stud(name,rollno,marks) values('Karan',104,95); 1 row created.
SQL> insert into stud(name,rollno,marks) values('Hari',105,92);
1 row created.
SQL> select * from stud;
NAME ROLLNO MARKS
---------- ---------- ---------- Anand 101 89
Ram 102 98
Sam 103 80
Karan 104 95
Hari 105 92
AVERAGE FUNCTION:

SQL> select avg(marks) from stud;

AVG(MARKS)
----------
90.8
MAXIMUM FUNCTION:

SQL> select max(rollno) from stud;

MAX(ROLLNO)
-----------
105
MINIMUM FUNCTION:

SQL> select min(marks) from stud;

MIN(MARKS)
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
----------
80
SUM FUNCTION:

SQL> select sum(rollno) from stud;

SUM(ROLLNO)
-----------
515
COUNT FUNCTION:

SQL> select count(name) from stud; COUNT(NAME)

-----------
5

ORDER BY,GROUP BY AND HAVING CLAUSE

ORDER BY:

SQL> select * from stud;

NAME ROLLNO MARKS

---------- ---------- ---------- Anand 101 89

Ram 102 98
Sam 103 80
Karan 104 95
Hari 105 92
SQL> select * from stud order by marks asc;
NAME ROLLNO MARKS
---------- ---------- ---------- Sam 103 80
Anand 101 89
Hari 105 92
Karan 104 95
Ram 102 98
SQL> select * from stud order by marks desc;
NAME ROLLNO MARKS
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
---------- ---------- ----------
Ram 102 98
Karan 104 95
Hari 105 92
Anand 101 89
Sam 103 80
SQL> insert into stud(name,rollno,marks) values('Hari',106,99);
1 row created.
SQL> select * from stud;
NAME ROLLNO MARKS
---------- ---------- ---------- Anand 101 89
Ram 102 98
Sam 103 80
Karan 104 95
Hari 105 92
Hari 106 99
6 rows selected.
SQL> select rollno,marks from stud where name='Hari'order by name;
ROLLNO MARKS
---------- ----------
105 92

106 99

SQL> select name from stud order by name;

NAME
----------
Anand
Hari
Hari
Karan Ram
Sam
6 rows selected.
SQL> select * from stud;
NAME ROLLNO MARKS
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
---------- ---------- ---------- Anand 101 89
Ram 102 98
Sam 103 80
Karan 104 95
Hari 105 92
Hari 106 99
6 rows selected.
GROUP BY:

SQL> select name,max(marks) from stud group by name;

NAME MAX(MARKS)
---------- ---------- Anand 89
Sam 80
Karan 95
Ram 98
Hari 99
SQL> select max(marks) from stud group by name;
MAX(MARKS)
----------
89
80
95
98
99

SQL> select name,min(marks) from stud where name='Hari' group by name;

NAME MIN(MARKS)
---------- ----------
Hari 92
HAVING CLAUSE:

SQL> select name,max(marks) from stud group by name having name='Hari';

NAME MAX(MARKS)
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
---------- ---------- Hari 99
select count(rollno),name from stud group by name order
by name;

COUNT(ROLLNO) NAME
------------- ----------
1 Anand

2 Hari

1 Karan
1 Ram
1 Sam

SQL> select count(rollno),name from stud group by name having name='Hari' order by name;
COUNT(ROLLNO) NAME
------------- ----------
2 Hari

SQL OPERATORS:

SQL> select * from stock1;

SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
12 gk pen
12 kk pen
SQL> select *from stock1 where sname='gk';
SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
12 gk pen
SQL> select *from stock1 where sname !='gk';
SNO SNAME ITEM
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
---------- ------------ --------------
7 kkk pen
12 kk pen
SQL> select *from stock1 where sno> 7;
SNO SNAME ITEM
---------- ------------ --------------
12 gk pen
12 kk pen
SQL> select *from stock1 where sno>= 7 and sname='gk';
SNO SNAME ITEM
---------- ------------ --------------
12 gk pen
SQL> select *from stock1 where sno>= 7 or sname='gk';
SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
12 gk pen
12 kk pen
SQL> select *from stock1 where (sno>= 7 and sname='gk') or (item='pen');
SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
12 gk pen
12 kk pen
SQL> select *from stock1 where (sno>= 7 and sname='gk') order by item;
SNO SNAME ITEM
---------- ------------ --------------
12 gk pen
SQL> select all sno from stock1 where sname='kkk';
SNO
----------
7
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> select distinct sno from stock1 where sname='gk';
SNO
----------
4
12
SQL> select * from stock1 where sname in('gk','kkk');
SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
12 gk pen
SQL> select * from stock1 where sname not in('gk','kkk');
SNO SNAME ITEM
---------- ------------ --------------
12 kk pen
SQL> select * from stock1 where sname <> 'gk';
SNO SNAME ITEM
---------- ------------ --------------
7 kkk pen
12 kk pen
SQL> select * from stock1 where sname <> 'null';
SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
12 gk pen
12 kk pen

SQL> select * from stock1 where sname is null; no rows selected

SQL> select * from stock1 where sname is not null;
SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
12 gk pen
12 kk pen
SQL> select * from stock1 where sname like 'k%';
SNO SNAME ITEM
---------- ------------ --------------
7 kkk pen
12 kk pen
SQL> select * from stock1 where sname not like 'k%';
SNO SNAME ITEM
---------- ------------ --------------

4 gk pen 12 gk pen
SQL> select *from stock4;
SNO SNAME ITEM AGE
---------- ------------ -------------- ----------
6 fff watch 9

7 ggg hhh 10

7 ggg hhh 20
7 ggg hhh 22
SQL> select item from stock4 where sname like 'f_%_%';
ITEM
--------------
Watch
SQL> select * from stock4 where age between 10 and 22;
SNO SNAME ITEM AGE
---------- ------------ -------------- ----------
7 ggg hhh 10
7 ggg hhh 20
7 hhh 22
Alias Column
SQL> select sno as stno from stock4;
STNO
----------
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
6
7
7
7
SQL> select * from stock4;
SNO SNAME ITEM AGE
---------- ------------ -------------- ---------
6 fff watch 9

7 ggg hhh 10

7 ggg hhh 20

7 ggg hhh 22

SQL> select * from stock1;

SNO SNAME ITEM
---------- ------------ --------------
4 gk pen
7 kkk pen
12 gk pen
12 kk pen

SQL> select sno,sname from stock4 where sno=any(select sno from stock1 where sno=7);
SNO SNAME
---------- ------------
7 ggg
7 ggg
7 ggg
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697

RESULT:

CREATION OF VIEWS

CREATING A VIEW OF ONE TABLE:

SQL> create table empinfo(empno number,name varchar2(10),state varchar2(20)); Table created.

SQL> insert into empinfo(empno,name,state)values(1,'hari','chennai'); 1 row created.
SQL> insert into empinfo(empno,name,state)values(2,'sam','pune'); 1 row created.
SQL> insert into empinfo(empno,name,state)values(3,'sai','bangalore'); 1 row created.
SQL> insert into empinfo(empno,name,state)values(4,'ram','delhi'); 1 row created.
SQL> insert into empinfo(empno,name,state)values(5,'karan','bihar');
1 row created.
CREATING A VIEW OF ONE TABLE:

SQL> create view empinfoview as select name,state from empinfo; View created.
SQL> select * from empinfoview;
NAME STATE
---------- -------------------- hari
chennai sam pune sai bangalore
ram delhi karan bihar
SQL> insert into empinfoview(name,state)values('suresh','kolkata');
1 row created.
SQL> select * from empinfoview;
NAME STATE
---------- --------------------
hari chennai sam pune
sai bangalore ram delhi
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
karan bihar suresh kolkata
6 rows selected.
CREATING A VIEW OF MANY TABLE:

SQL> create table empdetails(empno number,name varchar2(25),salary number,age number); Table created.
SQL> insert into empdetails(empno,name,salary,age)values(1,'hari',25000,30); 1 row created.
SQL> insert into empdetails(empno,name,salary,age)values(2,'sam',30000,25); 1 row created.
SQL> insert into empdetails(empno,name,salary,age)values(3,'sai',40000,36); 1 row created.
SQL> insert into empdetails(empno,name,salary,age)values(4,'ram',50000,42); 1 row created.
SQL> insert into empdetails(empno,name,salary,age)values(5,'karan',100000,50);
1 row created.

SQL> create view detailsview as select empinfo.name,empinfo.state,empdetails.salary from

empinfo,empdetails where empinfo.name=empdetails.name; View created.

SQL> select * from detailsview;

NAME STATE SALARY ---------- ------
-------------- ---------- hari chennai 25000
sam pune 30000 sai bangalore
40000 ram delhi 50000
karan bihar 100000

SQL> create or replace view detailsview as select

empinfo.name,empinfo.state,empdetails.salary,empdetails.age from empinfo,empdetails where
empinfo.name=empdetails.name; View created.

SQL> select * from detailsview;

NAME STATE SALARY AGE ---------- ------
-------------- ---------- ---------- hari chennai 25000
30 sam pune 30000 25 sai bangalore
40000 36 ram delhi 50000 42 karan
bihar 100000 50
DROPPING A VIEW

SQL> drop view detailsview; View dropped.

DELETING A VIEW:
SQL> delete from empinfoview where name='suresh';
1 row deleted.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> select * from empinfoview;
NAME STATE
---------- -------------------- hari
chennai sam pune sai bangalore
ram delhi karan bihar
SQL> drop view empinfoview; View dropped.
SQL> select * from empdetails;
EMPNO NAME SALARY AGE
---------- ------------------------- ---------- ----------
1 hari 25000 30

2 sam 30000 25

3 sai 40000 36

4 ram 50000 42

5 karan 100000 50 SYNONYM

CREATTION OF SYNONYM:
SQL> create synonym employeedetails for empdetails; Synonym created.
SQL> select * from empdetails;
EMPNO NAME SALARY AGE
---------- ------------------------- ---------- ----------
1 hari 25000 30

2 sam 30000 25

3 sai 40000 36

4 ram 50000 42 5 karan 100000

50
SQL> select * from employeedetails;
EMPNO NAME SALARY AGE
---------- ------------------------- ---------- ----------
1 hari 25000 30

2 sam 30000 25
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
3 sai 40000 36

4 ram 50000 42

5 karan 100000 50

SEQUENCE

SEQUENCE WITH NOCYCLE:

SQL> create sequence seqstu start with 2 increment by 3 maxvalue 15 nocycle; Sequence created.
SQL> insert into student1 values(seqstu.nextval,'rrrr'); 1 row created.
SQL> insert into student1 values(seqstu.nextval,'rrrr'); 1 row created.
SQL> insert into student1 values(seqstu.nextval,'rrrr'); 1 row created.
SQL> insert into student1 values(seqstu.nextval,'rrrr'); 1 row created.
SQL> insert into student1 values(seqstu.nextval,'rrrr'); 1 row created.
SQL> insert into student1 values(seqstu.nextval,'rrrr'); insert into
student1 values(seqstu.nextval,'rrrr')
*ERROR at line 1:

ORA-08004: sequence SEQSTU.NEXTVAL exceeds MAXVALUE and cannot be instantiated

SQL> select * from student1;
ROLLNO NAME
---------- --------------------
1 abi

2 akshu

2 rrrr
5 rrrr
8 rrrr
11 rrrr
14 rrrr
7 rows selected.
SEQUENCE WITH CYCLE:
SQL> create sequence seqstud start with 3 increment by 2 maxvalue 15 cycle cache 6; Sequence created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp'); 1 row created.
SQL> insert into student1 values(seqstud.nextval,'pppp');
1 row created.

SQL> select * from student1;

ROLLNO NAME
---------- --------------------
1 abi

2 akshu

2 rrrr

5 rrrr
8 rrrr
11 rrrr
14 rrrr
3 pppp

5 pppp
7 pppp
9 pppp
ROLLNO NAME
---------- --------------------
11 pppp
13 pppp
15 pppp
1 pppp
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
3 pppp
5 pppp
7 pppp
9 pppp
11 pppp

20 rows selected.

RESULT:

JOINS:
SQL> create table stud(rollno number(5),studname varchar2(10),dept varchar2(5)); Table created.
SQL> create table marks(rollno number(5),marks number(5),cgpa number(5)); Table created.
SQL> insert into stud values(101,'harini','cse'); 1 row created.
SQL> insert into stud values(102,'priya','cse');
1 row created.
SQL> select * from stud;
ROLLNO STUDNAME DEPT
---------- ---------- -----
101 harini cse 102 priya
cse
SQL> insert into marks values(101,77,8.0); 1 row created.
SQL> insert into marks values(103,89,8.6);
1 row created.
SQL> select * from marks;
ROLLNO MARKS CGPA
---------- ---------- ----------
101 77 8 103 89
9

SIMPLE JOIN:
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
EQUI JOIN:
SQL> select * from stud,marks where stud.rollno=marks.rollno;
ROLLNO STUDNAME DEPT ROLLNO MARKS CGPA
---------- ---------- ----- ---------- ---------- ---------- 101 harini cse
101 77 8
NON-EQUI JOIN:
SQL> select * from stud,marks where stud.rollno<=marks.rollno;
ROLLNO STUDNAME DEPT ROLLNO MARKS CGPA
---------- ---------- ----- ---------- ---------- ----------
101 harini cse 101 77 8
101 harini cse 103 89 9 102 priya
cse 103 89 9
SQL> select * from stud s,marks m where s.rollno=m.rollno;
ROLLNO STUDNAME DEPT ROLLNO MARKS CGPA
---------- ---------- ----- ---------- ---------- ---------- 101 harini
cse 101 77 8

INNER JOIN:
SQL> select * from stud inner join marks on stud.rollno=marks.rollno;
ROLLNO STUDNAME DEPT ROLLNO MARKS CGPA
---------- ---------- ----- ---------- ---------- ---------- 101 harini
101 77 8
NATURAL JOIN:
SQL> select * from stud natural join marks;
ROLLNO STUDNAME DEPT MARKS CGPA
---------- ---------- ----- ---------- ---------- 101 harini
cse 77 8
CROSS JOINS:
SQL> select * from stud cross join marks;
ROLLNO STUDNAME DEPT ROLLNO MARKS CGPA
---------- ---------- ----- ---------- ---------- ----------
101 harini cse 101 77 8
101 harini cse 103 89 9

102 priya cse 101 77 8

 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
102 priya cse 103 89 9
CONSTRAINTS:
NOT NULL CONSTRAINT:
SQL> create table emp1(eid number not null,ename varchar2(20)); Table created.
SQL> insert into emp1(eid,ename)values(1,'sruti');
1 row created.
SQL> select * from emp1;
EID ENAME
---------- --------------------
1 sruti
SQL> insert into emp1(eid,ename)values(0,'anu');
1 row created.
SQL> select * from emp1;
EID ENAME
---------- --------------------
1 sruti
0 anu
SQL> insert into emp1(eid,ename)values('0','anu'); 1 row created.
SQL> insert into emp1(eid,ename)values('','anu'); insert into
emp1(eid,ename)values('','anu') ERROR at line 1:
ORA-01400: cannot insert NULL into ("CSE135"."EMP1"."EID")

UNIQUE CONSTRAINT:
SQL> create table emp2(eid number unique,ename varchar2(10) not null); Table created.
SQL> insert into emp2(eid,ename)values(1,'sita'); 1 row created.
SQL> insert into emp2(eid,ename)values(1,'kavi'); insert into
emp2(eid,ename)values(1,'kavi')
*
ERROR at line 1:
ORA-00001: unique constraint (CSE135.SYS_C0014872) violated
SQL> insert into emp2(eid,ename)values(0,'ashi');
1 row created.
SQL> select * from emp2;
EID ENAME
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
---------- ----------
1 sita
0 ashi

DEFAULT CONSTRAINT:
SQL> create table emp3(eid number,ename varchar2(10),salary number default 20000); Table created.
SQL> insert into emp3(eid,ename)values(1,'sam'); 1 row created.
SQL> insert into emp3(eid,ename)values(2,'sumi');
1 row created.

SQL> select * from emp3;

EID ENAME SALARY
---------- ---------- ----------
1 sam 20000

2 sumi 20000

CHECK CONSTRAINT:

SQL> create table emp4(eid number,ename varchar2(10),salary number

check(salary>20000)); Table created.

SQL> insert into emp4(eid,ename,salary)values(1,'geeta',25000); 1 row created.

SQL> insert into emp4(eid,ename,salary)values(1,'geeta',25000);
1 row created.
SQL> insert into emp4(eid,ename,salary)values(2,'sita',18000); insert into
emp4(eid,ename,salary)values(2,'sita',18000) *
ERROR at line 1:
ORA-02290: check constraint (CSE135.SYS_C0014875) violated
SQL> select * from emp4;
EID ENAME SALARY
---------- ---------- ----------
1 geeta 25000
1 geeta 25000

PRIMARY KEY:
SQL> create table order(oid number primary key,cusid number,cname varchar2(10)); create table order(oid
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
number primary key,cusid number,cname varchar2(10))
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> create table or1(oid number primary key,cusid number,cname varchar2(10)); Table created.
SQL> insert into or1(oid,cusid,cname)values(1,2000,'anu'); 1 row created.
SQL> insert into or1(oid,cusid,cname)values(2,2001,'priya'); 1 row created.
SQL> insert into or1(oid,cusid,cname)values(3,2002,'sumi');
1 row created.
SQL> select * from or1;
OID CUSID CNAME
---------- ---------- ----------
1 2000 anu

2 2001 priya

3 2002 sumi

FOREIGN KEY:
SQL> create table or2 (oid number references or1(oid),price number); Table created.
SQL> insert into or2 values(4,1000); insert into or2
values(4,1000)
*
ERROR at line 1:
ORA-02291: integrity constraint (CSE135.SYS_C0014892) violated - parent key not
Found
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697

RESULT:

SIMPLE PL/SQL PROGRAMS

FACTORIAL OF A NUMBER
declare n number(5); i number(5); fact number(20); begin n:=&number;
fact:=1; for i in 1..n loop fact:=fact*i; end loop;
dbms_output.put_line('Factorial of given number is'||fact); end;
OUTPUT:

SQL> set serveroutput on

SQL> @d:\fact.sql;

14 /

Enter value for number: 4 old 6: n:=&number; new 6: n:=4;

Factorial of given number is24

PL/SQL procedure successfully completed.

ODD OR EVEN
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
declare n number(4); begin n:=&number; if n mod 2=0
then
dbms_output.put_line('The number '||n||' is even'); else
dbms_output.put_line('The number '||n||' is odd'); end if; end;
OUTPUT:

SQL> set serveroutput on

SQL> @d:\odd.sql;

11 /

Enter value for number: 5 old 4: n:=&number; new 4: n:=5;

The number 5 is odd

PL/SQL procedure successfully completed.

FIBONACCI SERIES

declare n number(3); a number(3); b number(3); c

number(3); i number(3); begin n:=&number;
a:=-1; b:=1; for i in 1..n loop c:=a+b; dbms_output.put_line(c);
a:=b; b:=c; end loop; end;
OUTPUT:

SQL> set serveroutput on

SQL> @d:\fib.sql;

19 /

Enter value for number: 5 old 8: n:=&number; new 8: n:=5;

0
1

1
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
2

PL/SQL procedure successfully completed.

GREATEST OF THREE NUMBERS

Declare a number(5); b number(5); c number(5); begin

a:=&number; b:=&number; c:=&number; if a>b and a>c
then
dbms_output.put_line('The bigger number is'||a); else if b>c then
dbms_output.put_line('The bigger number is'||b); else
dbms_output.put_line('The bigger number is'||c); end if; end if; end;
OUTPUT:

SQL> set serveroutput on

SQL> @d:\greatest.sql

18 /

Enter value for number: 6 old 6: a:=&number; new 6: a:=6;

Enter value for number: 4 old 7: b:=&number; new 7: b:=4;
Enter value for number: 9
old 8: c:=&number; new 8: c:=9;
The bigger number is9

PL/SQL procedure successfully completed.

ARMSTRONG OR NOT

declare n number(3):=&number; digits number(5):=0; arm

number(5):=0; n1 number(5); begin n1:=n; while n>0 loop
digits:=mod(n,10); arm:=arm+power(digits,3); n:=trunc(n/10);
end loop; if n1=arm then
dbms_output.put_line('The given number is a armstrong number'); else
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
dbms_output.put_line('The given number is not a armstrong number'); end if; end;
OUTPUT:

SQL> set serveroutput on

SQL> @d:\armstrong.sql

20 /

Enter value for number: 153 old 2: n number(3):=&number; new 2:

n number(3):=153;
The given number is a armstrong number

PL/SQL procedure successfully completed.

PALINDROME OR NOT

declare n number(5); num number(5); r number(5); rev

number(5):=0; begin n:=&number; num:=n; while n>0
loop r:=n mod 10; rev:=(rev*10)+r; n:=trunc(n/10); end
loop; if rev=num then
dbms_output.put_line(' The given number is Palindrome'); else
dbms_output.put_line('The given number is Not Palindrome'); end if; end;
OUTPUT:

Enter value for number: 252 old 7: n:=&number; new 7: n:=252;

The given number is Palindrome

PL/SQL procedure successfully completed.

PERFECT NUMBER

declare n number(5); i number(5); f number(5):=0;

begin n:=&number;
for i in 1..n-1 loop if (n mod i=0) then f:=trunc(f+i);
end if; end loop; if (f=n) then
dbms_output.put_line('The given number is a perfect number '); else
dbms_output.put_line('The given number is not a perfect number '); end if; end;
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
OUTPUT:

Enter value for number: 6 old 6: n:=&number; new 6: n:=6;

The given number is a perfect number

PL/SQL procedure successfully completed.

RESULT:

PROCEDURES IN PL/SQL
WITH IN AND OUT PARAMETERS:
FACT1.SQL:

create or replace procedure fact1(n in number,f out number)is

i number;
begin
f:=1;
for i in 2..n loop
f:=f*i;
end loop;
end;
SQL>@z:\fact1.sql;
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> 9/

Procedure created.

FACTORIAL.SQL:

Declare
f number;
n number:=n;
begin
fact1(n,f);
dbms_output.put_line('factorial of '|| n ||' is:'||f);
end;
OUTPUT:

SQL>@z:\factorial.sql;

SQL>8 /

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

number:=5; factorial of 5 is:120
PL/SQL procedure successfully completed.

EXCEPTIONAL HANDLING
STUDENT.SQL:
Declare
sno1 number;
mark1 number;
begin
select sno,mark into sno1,mark1 from studentss where sno=&sno1;
dbms_output.put_line('sno'||sno1||'mark'||mark1);
exception
when no_data_found then
dbms_output.put_line('no data found');
when too_many_rows then
dbms_output.put_line('too many rows');
end;
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
OUTPUT:

SQL> @d:\student.sql;
Enter value for sno1: 101
old 5: select sno,mark into sno1,mark1 from studentss where sno=&sno1;
new 5: select sno,mark into sno1,mark1 from studentss where sno=101;
sno101mark66
PL/SQL procedure successfully completed.
Enter value for sno1: 132
old 5: select sno,mark into sno1,mark1 from studentss where sno=&sno1;
new 5: select sno,mark into sno1,mark1 from studentss where sno=132;
no data found
PL/SQL procedure successfully completed.
Enter value for sno1: 102
old 5: select sno,mark into sno1,mark1 from studentss where sno=&sno1;
new 5: select sno,mark into sno1,mark1 from studentss where sno=102;
too many rows
PL/SQL procedure successfully completed.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
PL/SQL FUNCTIONS

PROGRAM TO DISPLAY HOW MANY EMPLOYEES IN THE EMPLOYEE DATABASE

EMPL.SQL

create or replace function empfn return number is total number(2):=0;

begin
select count(*) into total from emp; return total; end;
SQL> @d:/emp.sql;

7/

Function created.

EMPLMAIN.SQL

declare c number(2); begin

c:=empfn();
dbms_output.put_line('total employees='||c); end;
OUTPUT:

SQL> @z:/empmain.sql; SQL>7 / total

employees=6
PL/SQL procedure successfully completed.

RESULT:

IMPLICIT CURSORS

create table customers(id number(5),name varchar2(10),age number(3),address varchar2(10),salary

number(6));
SQL> insert into customers values(101,'harini',19,'chennai',5000); 1 row created.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> insert into customers values(101,'ramya',22,'delhi',10000); 1 row created.
SQL> insert into customers values(103,'divya',21,'bangalore',15000); 1 row created.
SQL> insert into customers values(104,'krithika',19,'kolkata',15000); 1 row created.
SQL> insert into customers values(106,'vino',19,'mumbai',35000);
2 row created.

IMPLICIT.SQL DECLARE
total_rows number(2);
BEGIN
UPDATE customers SET salary=salary+500; IF sql%notfound THEN
dbms_output.put_line('no customer selected'); ELSIF sql%found THEN
total_rows:=sql%rowcount;
dbms_output.put_line(total_rows||'customers selected');
END IF;
END;
OUTPUT:

SQL> set serveroutput on

SQL> @z:\implicit.sql;
SQL> /
5customers selected
PL/SQL procedure successfully completed.
SQL> select * from customers;

ID NAME AGE ADDRESS SALARY

---------- ---------- ---------- --------------- ----------
101 harini 19 chennai 5500
101 ramya 22 delhi 10500
103 divya 21 bangalore 15500

104 krithika 19 kolkata 15500

106 vino 19 mumbai 35500

EXPLICIT CURSORS
EXPLICIT.SQL
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
declare c_id customers.id%type;
c_name customers.name%type;c_addr
customers.address%type; cursor
c_customers is select id,name,address
from customers; begin open
c_customers; loop
fetch c_customers into c_id,c_name,c_addr; exit
when c_customers%notfound;
dbms_output.put_line(c_id|| ' ' ||c_name|| ' ' ||c_addr);
end loop; close c_customers; end;
OUTPUT:

SQL> @z:explicit.sql;
SQL> 16/
101 harini chennai
101 ramya delhi
103 divya bangalore

104 krithika kolkata

106 vino mumbai

PL/SQL procedure successfully completed.

RESULT:

PACKAGES

PACK.SQL:
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
CREATE OR REPLACE PACKAGE math_operations AS

PROCEDURE add_numbers(x NUMBER, y NUMBER, result OUT NUMBER);

FUNCTION multiply_numbers(x NUMBER, y NUMBER) RETURN NUMBER;

END math_operations;

BODY.SQL:

CREATE OR REPLACE PACKAGE BODY math_operations AS

PROCEDURE add_numbers(x NUMBER, y NUMBER, result OUT NUMBER) IS BEGIN

result := x + y;

END add_numbers;

FUNCTION multiply_numbers(x NUMBER, y NUMBER) RETURN NUMBER IS BEGIN

RETURN x * y;

END multiply_numbers;

END math_operations;

MAIN.SQL: DECLARE
sum_result NUMBER; product_result
NUMBER; BEGIN
math_operations.add_numbers(5, 7, sum_result);

DBMS_OUTPUT.PUT_LINE('Sum Result: ' || sum_result); product_result :=

math_operations.multiply_numbers(3, 4);
DBMS_OUTPUT.PUT_LINE('Product Result: ' || product_result);

END;

OUTPUT:

SQL>set serveroutput on

SQL>@d:\pack.sql;
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
5/

Package created.

SQL>set serveroutput on

SQL>@d:\body.sql;

14/

Package body created.

SQL>set serveroutput on

SQL>@d:\main.sql;

16/

Sum Result:12

Product Result:12

PL/SQL Procedure successfully completed.

RESULT:

TRIGGERS
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
TRIGGER.SQL:

create or replace trigger dmlo after

update or insert or delete on emp for
each row begin if updating then
dbms_output.put_line('table is updated');
elsif inserting then
dbms_output.put_line('table is inserted');
elsif deleting then
dbms_output.put_line('table is deleted');
end if; end;
OUTPUT

SQL> set serveroutput on

SQL>@d:\trigger.sql;
14/
Trigger created.
SQL> select *from emp;
ENO ENAME BP HRA DA
---------- ------- ---------- ---------- ----------
101 a 30000 100 50

102 b 17000 70 30

103 c 13000 50 25

SQL> insert into emp values(107,'e',13000,170,30); table is inserted

1 row created.
SQL> select *from emp;
ENO ENAME BP HRA DA
---------- ------- ---------- ---------- ---------
107 e 13000 170 30
101 a 30000 100 50

102 b 17000 70 30

103 c 13000 50 25
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> delete from emp where eno='107'; table is deleted
1 row deleted.
SQL> select *from emp;
ENO ENAME BP HRA DA
---------- ------- ---------- ---------- ----------
101 a 30000 100 50

102 b 17000 70 30

103 c 13000 50 25

SQL> update emp set bp=27000 where eno=101; table is updated

1 row updated.
SQL> select *from emp;
ENO ENAME BP HRA DA
---------- ------- ---------- ---------- ---------- 101 a 27000 100 50
102 b 17000 70 30

103 c 13000 50 25

TRIGGER1.SQL:

create trigger t1age before insert or update of age on trig

for each row begin if(:new.age<0) then
raise_application_error(-20000,'no negative age allowed');
else
dbms_output.put_line('valid age');
end if; end;

OUTPUT

SQL>@d:\trigger1.sql;
11/
Trigger created.
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
SQL> desc trig;
Name Null? Type
----------------------------------------- -------- -------------------------
NAME VARCHAR2(7)
AGE NUMBER(3) SQL> insert into trig values('d',4); valid
age
1 row created.
SQL> insert into trig values('d',-4); insert into trig values('d',-4)
*
ERROR at line 1:
ORA-20000: no negative age allowed
ORA-06512: at "CSE135.T1AGE", line 3
ORA-04088: error during execution of trigger 'CSE135.T1AGE'

RESULT:
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697

IMPLEMENTATION OF NO SQL

BASIC COMMANDS OF MongoDB

Please enter a MongoDB connection string (Default: mongodb://localhost/): mongosh

mongosh> use admin
switched to db admin
CREATION OF NEW DATABASE:

admin> use student

switched to db student
CREATE COLLECTION:

student> db.createCollection("student details")

{ ok: 1 }
SHOW DATABASES:

student> show dbs

admin 40.00 KiB
config 72.00 KiB
local 88.00KiB
name_age 92.00 KiB
school 108.00 KiB
staff 72.00 KiB
student 8.00 KiB
INSERT SINGLE DOCUMENT:

student> db.student.insertOne({name:"anu",age:18,cgpa:9})
{
acknowledged: true, insertedId:
ObjectId('66ea50405e06e50bacc73bf8') }
INSERT MANY DOCUMENT:

student>
db.student.insertMany([{name:"shalu",age:19,cgpa:9.1},{name:"sara",age:20,cgpa:9.2}])
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
{
acknowledged: true, insertedIds: {
'0': ObjectId('66ea509c5e06e50bacc73bf9'),
'1': ObjectId('66ea509c5e06e50bacc73bfa')
}
}
GET COLLECTION DOCUMENT:

student> db.student.find()
[
{
_id: ObjectId('66ea50405e06e50bacc73bf8'), name: 'anu',
age: 18, cgpa: 9
},
{
_id: ObjectId('66ea509c5e06e50bacc73bf9'), name: 'shalu',
age: 19, cgpa: 9.1
},
{
_id: ObjectId('66ea509c5e06e50bacc73bfa'), name: 'sara',
age: 20, cgpa: 9.2
}
]
GET PARTICULAR COLLECTION DOCUMENT:

student> db.student.find({name:"anu"})
[
{
_id: ObjectId('66ea50405e06e50bacc73bf8'), name:
'avanthica', age: 18, cgpa: 9
}
]
student> db.student.insertOne({registerDate:new Date(19-12-2024)})
{ acknowledged: true, insertedId:
ObjectId('66ea513e5e06e50bacc73bfb')
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
}
SORT DOCUMENT:

student> db.student.find().sort("cgpa")
[
{
_id: ObjectId('66ea513e5e06e50bacc73bfb'), registerDate: ISODate('1969-
12-31T23:59:57.983Z')
},
{
_id: ObjectId('66ea50405e06e50bacc73bf8'), name: 'anu',
age: 18, cgpa: 9
},
{
_id: ObjectId('66ea509c5e06e50bacc73bf9'), name: 'shalu',
age: 19,
cgpa: 9.1
},
{
_id: ObjectId('66ea509c5e06e50bacc73bfa'), name: 'sara',
age: 20, cgpa: 9.2
}
]
DELETE DOCUMENT:

student> db.student.remove({'name':'anu'})
{ acknowledged: true, deletedCount: 0 }

UPDATE DOCUMENT:

student> db.student.update({'name':'shalu'},{$set:{'name':'shalini'}})

DeprecationWarning: Collection.update() is deprecated. Use updateOne, updateMany, or bulkWrite.

{
acknowledged: true,
insertedId: null,
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
matchedCount: 1,
modifiedCount: 1,
upsertedCount: 0
}
student> db.student.find()
[
{
_id: ObjectId('66ea509c5e06e50bacc73bf9'),
name: 'shalini',
age: 19,
cgpa: 9.1
},
{
_id: ObjectId('66ea509c5e06e50bacc73bfa'),
name: 'sara',
age: 20,
cgpa: 9.2
},
{
_id: ObjectId('66ea513e5e06e50bacc73bfb'),
registerDate: ISODate('1969-12-31T23:59:57.983Z')
}
]
SAVE DOCUMENT:

student> db.student.save
student.student.save
DROP DATABASE:

student> db.dropDatabase()
{ ok: 1, dropped: 'student' }
student>
 PANIMALAR ENGINEERING COLLEGE
DEPARTMENT OF CSE
Reg No: 211423104697
RESULT: