SQL Programming Lab for Engineering Students & Programmers.
DDL Commands
SQL>Create Table Student (Rno Number(10),Name Varchar(15),M Number(3),P Number(3),C Number(3));
Table Created.
SQL> Desc Student
Name Null? Type
------------------ -------- ----------------------------
Rno Number(10)
Name Varchar2(15)
M Number(3)
P Number(3)
C Number(3)
SQL> Alter Table Student Add(Tot Number(6),Avg Number(6,2));
Table Altered.
SQL> Desc Student
Name Null? Type
------------------ -------- ----------------------
Rno Number(10)
Name Varchar2(15)
M Number(3)
P Number(3)
C Number(3)
Tot Number(6)
Avg Number(6,2)
SQL> Alter Table Student Modify(Rno Varchar2(10));
Table Altered.
SQL> Desc Student
Name Null? Type
------------------ -------- ----------------------
Rno Varchar2(10)
Name Varchar2(15)
M Number(3)
P Number(3)
C Number(3)
Tot Number(6)
Avg Number(6,2)
SQL> Alter Table Student Add(Res Varchar2(6));
Table Altered.
SQL> Desc Student
Name Null? Type
------------------ -------- ----------------------
Rno Varchar2(10)
Name Varchar2(15)
M Number(3)
P Number(3)
C Number(3)
Tot Number(6)
Avg Number(6,2)
Res Varchar2(6)
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90 A
03295a0561 Miller 80 78 70 237 79 A
03295a0570 Scott 70 60 80 210 70 B
SQL> Truncate Table Student;
Table Truncated.
SQL> Select * From Student;
No Rows Selected.
SQL> Drop Table Student;
Table Dropped.
SQL> Desc Student
Error:
Ora-04043: Object Student Does Not Exist
DML Commands
SQL>Insert Into Student(Rno,Name,M,P,C) Values('03295a0561','Miller',89,78,70);
1 Row Created.
SQL> Insert Into Student(Rno,Name,M,P,C) Values('03295a0570','Scott',70,60,80);
1 Row Created.
SQL> Insert Into Student(Rno,Name,M,P,C) Values('&Rno','&Name',&M,&P,&C);
Enter Value For Rno: 04295a0501
Enter Value For Name: Martin
Enter Value For M: 90
Enter Value For P: 95
Enter Value For C: 98
Old 1: Insert Into Student(Rno,Name,M,P,C) Values('&Rno','&Name',&M,&P,&C)
New 1: Insert Into Student(Rno,Name,M,P,C) Values('04295a0501','Martin',90,95,98)
1 Row Created.
SQL> /
Enter Value For Rno: 03295a05b3
Enter Value For Name: Adams
Enter Value For M: 90
Enter Value For P: 90
Enter Value For C: 90
Old 1: Insert Into Student(Rno,Name,M,P,C) Values('&Rno','&Name',&M,&P,&C)
New 1: Insert Into Student(Rno,Name,M,P,C) Values('03295a05b3','Adams',90,90,90)
1 Row Created.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98
03295a05b3 Adams 90 90 90
03295a0561 Miller 80 78 70
03295a0570 Scott 70 60 80
SQL> Update Student Set Tot=M+P+C;
4 Rows Updated.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283
03295a05b3 Adams 90 90 90 270
03295a0561 Miller 80 78 70 237
03295a0570 Scott 70 60 80 210
SQL> Update Student Set Avg=Tot/3;
4 Rows Updated.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33
03295a05b3 Adams 90 90 90 270 90
03295a0561 Miller 80 78 70 237 79
03295a0570 Scott 70 60 80 210 70
SQL> Update Student Set Res='A+' Where Avg>90;
1 Row Updated.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90
03295a0561 Miller 80 78 70 237 79
03295a0570 Scott 70 60 80 210 70
SQL> Update Student Set Res='A' Where Avg<=90 And Avg>75;
2 Rows Updated.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90 A
03295a0561 Miller 80 78 70 237 79 A
03295a0570 Scott 70 60 80 210 70
SQL> Update Student Set Res='B' Where Avg<=75 And Avg>=60;
1 Row Updated.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90 A
03295a0561 Miller 80 78 70 237 79 A
03295a0570 Scott 70 60 80 210 70 X
SQL> Update Student Set Res='C' Where Avg<60;
0 Rows Updated.
SQL> Select Name,Rno From Student;
Name Rno
------- ----------
Martin 04295a0501
Adams 03295a05b3
Miller 03295a0561
Scott 03295a0570
SQL> Select Name From Student Where Avg>=85;
Name
---------------
Martin
Adams
SQL> Select Name,Rno From Student Order By Name;
Name Rno
------- ----------
Adams 03295a05b3
Miller 03295a0561
Scott 03295a0570
Martin 04295a0501
SQL> Select Name,Rno From Student Order By Name Desc;
Name Rno
-------- ----------
Scott 03295a0570
Martin 04295a0501
Miller 03295a0561
Adams 03295a05b3
SQL> Delete From Student Where M<=70;
1 Row Deleted.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90 A
03295a0561 Miller 80 78 70 237 79 A
SQL> Delete From Student;
4 Rows Deleted.
SQL> Select * From Student;
No Rows Selected
TCL Commands
SQL> Select * From Student ;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90 A
03295a0561 Miller 80 78 70 237 79 A
03295a0570 Scott 70 60 80 210 70 B
SQL> Delete From Student ;
4 Rows Deleted.
SQL> Select * From Student;
No Rows Selected
SQL> Rollback;
Rollback Complete.
SQL> Select * From Student;
Rno Name M P C Tot Avg Res
---------- ----- --- ---- --- ----- ------ ----
04295a0501 Martin 90 95 98 283 94.33 A+
03295a05b3 Adams 90 90 90 270 90 A
03295a0561 Miller 80 78 70 237 79 A
03295a0570 Scott 70 60 80 210 70 B
SQL> Delete From Student;
4 Rows Deleted.
SQL> Commit ;
Commit Complete.
SQL> Select * From Student;
No Rows Selected
SQL> Rollback;
Rollback Complete.
SQL> Select * From Student;
No Rows Selected
DCL Commands
SQL> Create User Cse501 Identified By Mahesh;
User Created.
SQL> Grant Connect,Resource To Cse501;
Grant Succeeded.
SQL> Connect Cse501
Enter Password: ******
Connected.
SQL> Show User
User Is "Cse501"
SQL> Create Table Stu (Rno Number(3),Name Varchar2(20));
Table Created.
SQL> Connect System
Enter Password: *******
Connected.
SQL> Show User
User Is "System"
SQL> Revoke Resource From Cse501;
Revoke Succeeded.
SQL> Connect Cse501
Enter Password: ******
Connected.
SQL> Show User
User Is "Cse501"
SQL> Create Table Stu (Rno Number(3),Name Varchar2(20));
Create Table Stu (Rno Number(3),Name Varchar2(20))
*
Error At Line 1:
Ora-01536: Space Quota Exceeded For Tablespace 'System'
Character Functions
SQL> Select Initcap(Dname) From Dept;
Initcap(Dname)
--------------
Accounting
Research
Sales
Operations
SQL>Select Substr ('Vijay Rural Engg College',7,5) From Dual;
Subst
-----
Rural
SQL> Select Instr('Vijay Rural Engg College','A',1,2) From Dual;
Instr('Vijayruralenggcollege','A',1,2)
--------------------------------------
10
SQL> Select Dname,Lower(Dname) From Dept;
Dname Lower(Dname)
-------------- --------------
Accounting accounting
Research research
Sales sales
Operations operations
SQL> Select Upper(‘Computer’) From Dept;
Upper(‘Computer)
--------------
COMPUTER
SQL> Select Rpad('Ganesh',10,'*') From Dual;
Rpad('Gane
----------
Ganesh****
SQL> Select Lpad('Ganesh',10,'*') From Dual
Lpad('Gane
----------
****Ganesh
SQL> Select Rtrim('Tarun',’N’) From Dual;
Rtri
----
Taru
SQL> Select Length('Rajashekar') From Dual;
Length('Rajashekar')
--------------------
10
SQL> Select Ename,Length(Rtrim(Ename))From Emp;
Ename Length(Rtrim(Ename))
------ --------------------
King 4
Blake 5
Clark 5
Jones 5
Martin 6
Allen 5
Turner 6
SQL> Select Ltrim('Tarun','T') From Dual
Ltri
----
Arun
SQL> Select Ename From Emp Where Soundex(Ename)=Soundex('Clark');
Ename
----------
Clark
SQL> Select Translate('Haresh','H','S') From Dual
Transl
------
Saresh
SQL> Select Translate('Haresh','Ha','Se') From Dual
Transl
------
Seresh
SQL> Select Translate('Haresh','Ha','Se') From Dual
Transl
------
Seress
SQL> Select Translate('Haresh','Has','Sen') From Dual
Transl
------
Serens
SQL> Select Translate('Haresh','Has','Se') From Dual
Trans
-----
Seres
SQL> Select Chr(65) From Dual;
C
-
A
SQL> Select Ascii('A') From Dual
Ascii('A')
----------
65
SQL> Select Concat('Uday','Kiran') From Dual;
Concat('U
---------
Udaykiran
SQL> Select Ename,Replace(Job,'Salesman','Salesperson') From Emp Where Deptno=30 And Comm=500;
Ename Replace(Job,'Salesman','Salesperson')
------ --------------------------------------
Ward Salesperson
SQL> Select Replace('Hyderabad','Hy','Secun') From Dual;
Replace('Hyd
------------
Secunderabad
Date Functions
SQL> Select Add_Months('15-Jan-98',5) From Dual;
Add_Month
---------
15-Jun-98
SQL> Select Ename,Hiredate,Add_Months(Hiredate,5) From Emp Where Deptno=10;
Ename Hiredate Add_Month
------- ------- ---------
King 17-Nov-81 17-Apr-82
Clark 09-Jun-81 09-Nov-81
Miller 23-Jan-82 23-Jun-82
SQL> Select Ename,Months_Between(Sysdate,Hiredate) Exp_In_Months From Emp
Where Deptno=10;
Ename Exp_In_Months
---------- -------------
King 279.550133
Clark 284.808197
Miller 277.356584
SQL>Select Months_Between(Sysdate,'10-Dec-04') From Dual;
Months_Between(Sysdate,'10-Dec-04')
-----------------------------------
2.9842201
SQL> Select Last_Day(Sysdate) From Dual;
Last_Day(
---------
31-Mar-05
SQL> Select Last_Day('10-Dec-04') From Dual;
Last_Day(
---------
31-Dec-04
SQL> Select Next_Day(Sysdate,1) From Dual;
Next_Day(
---------
06-Mar-05
SQL> Select Next_Day(Sysdate,7) From Dual;
Next_Day(
---------
05-Mar-05
Numerical Functions
SQL> Select Least(10,-7,-9,0,20,5000) From Dual;
Least(10,-7,-9,0,20,5000)
-------------------------
-9
SQL> Select Greatest(10,-7,-9,0,20,5000) From Dual
Greatest(10,-7,-9,0,20,5000)
----------------------------
5000
SQL> Select Greatest(-10,-7,-9,-20,-5000) From Dual
Greatest(-10,-7,-9,-20,-5000)
-----------------------------
-7
SQL> Select Abs(-20),Abs(0),Abs(5000) From Dual;
Abs(-20) Abs(0) Abs(5000)
----- ----- -----
20 0 5000
SQL> Select Ceil(7),Ceil(3.14),Ceil(-3.14) From Dual;
Ceil(7) Ceil(3.14) Ceil(-3.14)
---------- ------- -----------
7 4 -3
SQL>Select Floor(7),Floor(3.14),Floor(-3.14) From Dual;
Floor(7) Floor(3.14) Floor(-3.14)
--------- ----------- ------------
7 3 -4
SQL> Select Mod(100,10),Mod(10,0),Mod(3,1),Mod(33,36)From Dual;
Mod(100,10) Mod(10,0) Mod(3,1) Mod(33,36)
--------- --------- -------- ----------
0 10 0 33
SQL> Select Power(5,2),Power(-6,2) From Dual;
Power(5,2) Power(-6,2)
---------- -----------
25 36
SQL> Select Round(100/6,0),Round(100/6,2),Round(100/3,1) From Dual;
Round(100/6,0) Round(100/6,2) Round(100/3,1)
----------- --------- --------
17 16.67 33.3
SQL> Select Sign(234),Sign(0),Sign(-324) From Dual;
Sign(234) Sign(0) Sign(-324)
-------- ------- ----------
1 0 -1
SQL> Select Sqrt(49),Sqrt(66.666),Sqrt(Null),Sqrt(-4) From Dual;
Select Sqrt(49),Sqrt(66.666),Sqrt(Null),Sqrt(-4) From Dual
*Error At Line 1:
Ora-01428: Argument '-4' Is Out Of Range
SQL>Select Sqrt(49),Sqrt(66.666),Sqrt(Null) From Dual;
Sqrt(49) Sqrt(66.666) Sqrt(Null)
-------- ------------ ----------
7 8.16492498
SQL> Select Trunc(45.932,1),Trunc(45.932),Trunc(43.32,1),Trunc(42.333,-1),Trunc(423.323,-2) From Dual;
Trunc(45.932,1) Trunc(45.932) Trunc(43.32,1) Trunc(42.333,-1) Trunc(423.323,-2)
--------------- ------------- -------------- ---------------- -----------------
45.9 45 43.3 40 400
-
General Functions
SQL> Select Ename,Comm,Nvl(Comm,100) From Emp;
Ename Comm Nvl(Comm,100)
---------- ---------- -------------
King 100
Blake 100
Clark 100
Jones 100
Martin 1400 1400
Allen 300 300
Turner 0 0
James 100
Ward 500 500
SQL> Select Ename,Vsize(Rtrim(Ename)) From Emp Where Deptno=10;
Ename Vsize(Rtrim(Ename))
---------- ------------------
King 4
Clark 5
Miller 6
SQL> Select Dump(Rtrim(Ename),10,3,2) Decimal From Emp Where Ename='Scott'
Decimal
---------------------------------------------------
Typ=1 Len=5: 79,84
SQL> Select Dump(Rtrim(Ename),8,3,2) Octal From Emp Where Ename='Scott';
Octal
----------------------------------------------------
Typ=1 Len=5: 117,124
SQL> Select Dump(Rtrim(Ename),10,3,2) Single From Emp Where Ename='Scott'
Single
--------------------------------------------------
Typ=1 Len=5: O,T
SQL> Select Dump(Rtrim(Ename),16,3,2) Hex From Emp Where Ename='Scott'
Hex
------------------------------------------------
Typ=1 Len=5: 4f,54
Group Functions
SQL> Select Deptno,Sum(Sal) From Emp Group By Deptno;
Deptno Sum(Sal)
--------- ----------
10 8750
20 10875
30 9400
SQL>Select Deptno,Avg(Sal) From Emp Group By Deptno;
Deptno Avg(Sal)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
SQL> Select Sum(Sal) From Emp;
Sum(Sal)
----------
29025
SQL> Select Avg(Sal) From Emp;
Avg(Sal)
----------
2073.21429
SQL> Select Max(Sal) From Emp;
Max(Sal)
----------
5000
SQL> Select Min(Sal) From Emp;
Min(Sal)
----------
800
SQL> Select Deptno,Max(Sal) From Emp Group By Deptno;
Deptno Max(Sal)
-------- ----------
10 5000
20 3000
30 2850
SQL> Select Deptno,Min(Sal) From Emp Group By Deptno;
Deptno Min(Sal)
---------- ----------
10 1300
20 800
30 950
SQL>Select Count(*) From Emp Where Sal>2500;
Count(*)
----------
5
SQL> Select Count(All Job) From Emp;
Count(Alljob)
-------------
14
SQL> Select Count(Distinct Job) From Emp;
Count(Distinctjob)
------------------
5
SQL> Select Stddev(Sal) From Emp;
Stddev(Sal)
-----------
1182.50322
SQL> Select Variance(Sal) From Emp;
Variance(Sal)
-------------
1398313.87
DCL Commands
SQL> create user cse501 identified by mahesh;
User created.
SQL> grant connect,resource to cse501;
Grant succeeded.
SQL> connect cse501
Enter password: ******
Connected.
SQL> show user
user is "CSE501"
SQL> create table stu (rno number(3),name varchar2(20));
Table created.
SQL> connect system
Enter password: *******
Connected.
SQL> show user
user is "SYSTEM"
SQL> revoke resource from cse501;
Revoke succeeded.
SQL> connect cse501
Enter password: ******
Connected.
SQL> show user
user is "CSE501"
SQL> create table stu (rno number(3),name varchar2(20));
create table stu (rno number(3),name varchar2(20))
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'SYSTEM'
Joins
Equi Join
Q1.Display Empno,Ename,Dname,Loc From Emp And Dept ?
SQL> Select Empno,Ename,Dname,Loc From Emp,Dept Where Emp.Deptno=Dept.Deptno;
Empno Ename Dname Loc
---------- ---------- -------------- -------------
7839 King Accounting New York
7698 Blake Sales Chicago
7782 Clark Accounting New York
7566 Jones Research Dallas
7654 Martin Sales Chicago
7499 Allen Sales Chicago
7844 Turner Sales Chicago
7900 James Sales Chicago
7521 Ward Sales Chicago
7902 Ford Research Dallas
7369 Smith Research Dallas
Non Equi Join
Q1.Display Empno,Ename,Sal,Grade from Emp and SalGrade ?
SQL> Select Empno,Ename,Sal,Grade From Emp,Salgrade Where Sal Between Losal And Hisal;
Empno Ename Sal Grade
---------- ---------- ---------- ----------
7900 James 950 1
7369 Smith 800 1
7876 Adams 1100 1
7654 Martin 1250 2
7521 Ward 1250 2
7934 Miller 1300 2
7499 Allen 1600 3
7844 Turner 1500 3
7698 Blake 2850 4
7782 Clark 2450 4
7566 Jones 2975 4
Q2.Display Empno,Ename,D.Deptno,Dname,Sal,Grade Using EQUI And NON-EQUI Join ?
Sql> Select Empno,Ename,D.Deptno,Dname,Sal,Grade From Emp E,Dept D,
Salgrade S Where E.Deptno=D.Deptno And E.Sal Between S.Losal And S.Hisal ;
Empno Ename Deptno Dname Sal Grade
---------- ---------- ---------- -------------- ---------- ----------
7900 James 30 Sales 950 1
7369 Smith 20 Research 800 1
7876 Adams 20 Research 1100 1
7654 Mart 30 Sales 1250 2
7521 Ward 30 Sales 1250 2
7934 Miller 10 Accounting 1300 2
7499 Allen 30 Sales 1600 3
7844 Turner 30 Sales 1500 3
7698 Blake 30 Sales 2850 4
7782 Clark 10 Accounting 2450 4
7566 Jones 20 Research 2975 4
Cartesian Join
Q1.Display Empno,Ename,Dname,Loc From Emp,Dept Where Deptno=10 ?
SQL> Select Empno,Ename,Dname,Loc From Emp ,Dept Where Emp.Deptno=10;
Empno Ename Dname Loc
--------- ---------- -------------- -------------
7839 King Accounting New York
7782 Clark Accounting New York
7934 Miller Accounting New York
7839 King Research Dallas
7782 Clark Research Dallas
7934 Miller Research Dallas
7839 King Sales Chicago
7782 Clark Sales Chicago
7934 Miller Sales Chicago
7839 King Operations Boston
7782 Clark Operations Boston
7934 Miller Operations Boston
Outer Join
SQL> Select Empno,Ename,D.Deptno From Emp E,Dept D Where E.Deptno(+)=D.Deptno;
Empno Ename Deptno
---------- ---------- ----------
7839 King 10
7782 Clark 10
7934 Miller 10
7566 Jones 20
7788 Scott 20
7876 Adams 20
7369 Smith 20
7902 Ford 20
7698 Blake 30
7654 Martin 30
7499 Allen 30
7844 Turner 30
7900 James 30
7521 Ward 30
40
15 rows selected.
SQL> Select Empno,Ename,D.Deptno From Emp E,Dept D Where E.Deptno=D.Deptno(+);
Empno Ename Deptno
---------- ---------- ----------
7839 King 10
7782 Clark 10
7934 Miller 10
7566 Jones 20
7788 Scott 20
7876 Adams 20
7369 Smith 20
7902 Ford 20
7698 Blake 30
7654 Martin 30
7499 Allen 30
7844 Turner 30
7900 James 30
Self Join
Q1.Display Ename ,Salary For Those Employees Whose Job Is CLERK And Salary Is 950,Releated To This Record Display The Other Employees Whose Job Is CLERK & Sal>950 ?
SQL> Select E.Ename,E.Sal,M.Ename,M.Sal From Emp E,Emp M
2 Where (E.Job='CLERK' And E.Sal=950)And
3 (E.Job=M.Job And M.Sal>E.Sal);
Ename Sal Ename Sal
---------- ------- ---------- ----------
James 950 Miller 1300
James 950 Adams 1100
Q2.Display Empno,Ename ,Manager Name Of An Employee ?
SQL> Select E.Empno,E.Ename,E.Mgr,M.Ename MgrNameFrom Emp E,Emp M
2 Where E.Mgr=M.Empno;
Empno Ename Mgr Mgrname
---------- ---------- ------- ----------
7698 Blake 7839 King
7782 Clark 7839 King
7566 Jones 7839 King
7654 Martin 7698 Blake
7499 Allen 7698 Blake
7844 Turner 7698 Blake
7900 James 7698 Blake
7521 Ward 7698 Blake
7902 Ford 7566 Jones
7369 Smith 7902 Ford
7788 Scott 7566 Jones
7876 Adams 7788 Scott
7934 Miller 7782 Clark
13 rows selected.
SchemaObjects
VIEWS
SQL> Create View Empdet As Select Empno,Ename,Job From Emp;
View Created.
SQL> Select * From Empdet;
Empno Ename Job
--------- ----------- ----------
7369 Smith Clerk
7499 Allen Salesman
7521 Ward Salesman
7566 Jones Manager
7654 Martin Salesman
7698 Blake Manager
7782 Clark Manager
7 Rows Selected.
SQL> Drop View Empdet;
View Dropped.
SQL> Create View Emp1(Name,Ecode,Dnumber,Dname) As
2 Select Empno,Ename,Emp.Deptno,Dname From Emp,Dept
3 Where Emp.Deptno=Dept.Deptno;
View Created.
SQL> Select * From Emp1;
Ecode Name Deptno Dname
--------- ---------- --------- --------------
7369 Smith 20 Research
7499 Allen 30 Sales
7521 Ward 30 Sales
7566 Jones 20 Research
7654 Martin 30 Sales
7698 Blake 30 Sales
7839 King 10 Accounting
Sequences
SQL>Create Sequence Seq1
Start With 1
Increment By 1
Maxvalue 1000
Nocycle Order;
Sequence Created.
SQL> Select Seq1.Nextval From Dual
NEXTVAL
---------
1
SQL> Select Seq1.Currval From Dual;
CURRVAL
---------
1
SQL> Select Seq1.Nextval From Dual;
NEXTVAL
---------
2
SQL> Alter Sequence Seq1
2 Increment By 2
3 Maxvalue 500
Sequence Altered.
SQL> Select Seq1.Nextval From Dual;
NEXTVAL
---------
4
SQL>Select Seq1.Currval From Dual
CURRVAL
---------
4
SQL> Select Seq1.Nextval From Dual;
NEXTVAL
---------
6
SQL> Drop Sequence Seq1;
Sequence Dropped.
SQL> Select Seq1.Nextval From Dual;
Select Seq1.Nextval From Dual
*ERROR At Line 1:
ORA-02289: Sequence Does Not Exist
Synonym
SQL> Create Synonym Xyz For Dept;
Synonym Created.
SQL> Select * From Xyz;
DEPTNO DNAME LOC
--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> Drop Synonym Xyz;
Synonym Dropped.
Indexes
SQL> Create Index Emp_Ndx On Emp(Deptno);
Index Created.
SQL> Drop Index Emp_Ndx;
Index Dropped.
SQL> Create Index Emp_Ndx On Emp(Mgr);
Index Created.
SQL> Drop Index Emp_Ndx ;
Index Dropped.
Integrity Constraint
SQL>create table stu (rno number(10) ,name varchar2(10) not null,Admino number(5) unique, branch varchar2(5) check(branch in ('cse','csit','CSE','CSIT')),primary key(rno));
Table created.
SQL> insert into stu values(202,'mahesh',204,'ece');
insert into stu values(202,'mahesh',204,'ece')
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C00375) violated
SQL> insert into stu values(202,'mahesh',204,'CSE');
1 row created.
SQL> insert into stu(rno,admino,branch) values(201,123,'cse');
insert into stu(rno,admino,branch) values(201,123,'cse')
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
SQL> insert into stu values(203,'raj',123,'cse');
1 row created.
SQL> insert into stu values(205,'ganesh',123,'csit');
insert into stu values(205,'ganesh',123,'csit')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C00374) violated
SQL> insert into stu values(203,'rama',234,'cse');
insert into stu values(203,'rama',234,'cse')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C00376) violated
SQL> insert into stu(name,admino,branch) values('rama',4567,'cse');
insert into stu(name,admino,branch) values('rama',4567,'cse')
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
SQL> create table bank(acno number(4) Not Null,ahn varchar2(20),bal number(8) check(bal>500));
Table created.
SQL>Alter table bank(acno number(4) Not null);
Table Altered.
SQL>create table stu(rno number(4)Primary key,name varchar2(20) not null,
street varchar2(14) unique,city varchar2(15));
Table created.
SQL>create table stu1(rno number(4),name varchar2(20) not null,street varchar2(14) unique,city varchar2(15),Primary key(rno));
Table created.
SQL> create table stu3(rno number(4),name varchar2(20) not null,street varchar2(14) unique,city varchar2(15),Constraint r_k primary key(rno))
Table created.
SQL> alter table stu3 drop constraint r_k
Table altered.
SQL> alter table stu3 add primary key(rno);
Table altered.
SQL> Create table stu4 (rno number ,address varchar2(30),foreign key(rno) references stu3(rno);
Table Created
SQL> create table emp2 (eno number primary key,name varchar2(20) not null,sal number not null,
2 deptno number,foreign key(deptno) references dept(deptno));
Table created.
SQL> select * from dept;
Deptno Dname Loc
---------- -------------- -------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
SQL> insert into emp2 values(101,'Mahesh',15000,50);
insert into emp2 values(101,'Mahesh',15000,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (MAHESH.SYS_C002531) violated - parent key not found
SQL> insert into emp2 values(101,'Mahesh',15000,40)
1 row created.
