javapandit.net
  • Home
  • Quick Java
    • Exception Handling
    • Collections Framework
  • Java Best Practices
  • Web Services
    • Web Service Basics
    • Ten Basic webservice concepts
    • XML
    • Apache Axis
    • Restful Web Services
  • JMS Concepts
    • JMS- MySQL
  • Hadoop
    • NoSQL DATABASEs
    • Apache Sqoop
    • Hadoop Interview Questions
  • Java 5
  • Java 8
    • Java 8 : Lambda Expressions
  • JDBC
  • Java Architect
    • Enterprise application re-platforming strategies
    • Java Memory Management
  • Java Programs
  • Technical Tips
    • How to set JAVA_HOME environment variable
    • How to create an auto increment field in Oracle?
    • Linux Commands
  • Best Java Interview Questions
    • Java Interview Questions- YouTube
  • Interview Questions
    • Java Tech interview Questions
    • Core Java Interview Questions >
      • core tech questions1
      • Java Collection interview questions
      • Java Concurrency
    • Servlets Interview Questions
    • JSP Interview Questions
    • Java Web Services Interview Questions
    • EJB Interview Questions
    • XML Interview Questions
    • JMS Interview Questions
  • Struts Interview Questions
    • Struts 2 Interview Questions
  • Java EE Architects Interview Questions
    • Java Architect Interview Questions
    • Top 10 reasons for Java Enterprise Application Performance Problems
    • Web Application Scalability Questions for IT Architect
  • JavaPandit's Blog
  • Web Services Interview Questions
  • Servlets And JSP
  • Oracle SOA Interview Questions
    • Open ESB /JBI
    • BPEL Language
  • Log4J
  • Ant
  • Maven
  • JMeter
  • JUnit
  • Apache POI Framework
  • ORCALE SERVICE BUS (OSB) Interview Questions
  • J2EE Patterns
    • Model-View-Controller (MVC)
    • Front Controller
    • DAO
    • Business Delegate
    • Session Facade
    • Service Locator
    • Transfer Object
    • Design Patterns >
      • Creational Patterns >
        • Singleton
      • Behavioural Patterns
      • Structural Patterns
    • Intercepting Filter
  • SQL Interview Questions/Lab
  • Best Wall Papers
    • Devotional Songs
  • Java Community
  • HIBERNATE
  • ORACLE CC&B
    • Oracle CC&B Interview Questions
  • Docker
  • Little Princess
    • Sai Tanvi Naming Ceremony Celebrations
    • Rice Feeding Ceremony
    • Sai Tanvi Gallery
  • APPSC Career Guidance
    • AP History
    • Indian Polity
    • Indian Economy
    • Science & Technology
    • Mental Ability and Reasoning
    • Disaster Management
    • Current Affairs and Events
    • General Sciences >
      • Biology
      • Physics
      • Chemistry
    • Previous Question Papers
  • About Us
  • Contact US

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.


Powered by Create your own unique website with customizable templates.