QUERIES TO IMPLEMENT JOINS IN SQL

SQL> CREATE TABLE PERSONS18(PID INTEGER PRIMARY KEY, NAME CHAR(30), MOBILE NUMBER(10));

Table created.

SQL> DESC PERSONS18;

 Name                                      Null?            Type

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

 PID                                          NOT NULL  NUMBER(38)

 NAME                                                          CHAR(30)

 MOBILE                                                       NUMBER(10)

SQL>  CREATE TABLE ORDERS18(OID INTEGER PRIMARY KEY , ORDERNO NUMBER(7) NOT NULL,  PID INTEGER,

  2  CONSTRAINT FKPOR FOREIGN KEY (PID)

  3   REFERENCES PERSONS18(PID));

Table created.

SQL> desc persons18;

 Name                                      Null?           Type

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

 PID                                          NOT NULL NUMBER(38)

 NAME                                                        CHAR(30)

 MOBILE                                                     NUMBER(10)

SQL> desc orders18;

 Name                                      Null?           Type

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

 OID                                         NOT NULL NUMBER(38)

 ORDERNO                             NOT NULL NUMBER(7)

 PID                                                             NUMBER(38)

SQL> INSERT INTO PERSONS18 VALUES(1,'SANKET',8745698547);

1 row created.

SQL> INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB);

Enter value for id: 1

Enter value for name: sanket

Enter value for mob: 8547896547

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(1,'sanket',8547896547)

INSERT INTO PERSONS18 VALUES(1,'sanket',8547896547)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C003011) violated

SQL> /

Enter value for id: 2

Enter value for name: sanket2

Enter value for mob: 8547125647

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(2,'sanket2',8547125647)

1 row created.

SQL> /

Enter value for id: 3

Enter value for name: sanket3

Enter value for mob: 9658745214

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(3,'sanket3',9658745214)

1 row created.

SQL> /

Enter value for id: 4

Enter value for name: sanket4

Enter value for mob: 8574125468

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(4,'sanket4',8574125468)

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: sanket5

Enter value for mob: 7458965478

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(5,'sanket5',7458965478)

1 row created.

SQL> ;

  1* INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

SQL> select * from persons18;

       PID NAME                               MOBILE

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

         1 SANKET                        8745698547

         2 sanket2                        8547125647

         3 sanket3                        9658745214

         4 sanket4                        8574125468

         5 sanket5                        7458965478

SQL> INSERT INTO ORDERS18 VALUES(1,101,1);

1 row created.

SQL> INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID);

Enter value for oid: 2

Enter value for ono: 102

Enter value for pid: 2

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(2,102,2)

1 row created.

SQL> /

Enter value for oid: 3

Enter value for ono: 103

Enter value for pid: 3

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(3,103,3)

1 row created.

SQL> /

Enter value for oid: 4

Enter value for ono: 104

Enter value for pid: 4

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(4,104,4)

1 row created.

SQL> /

Enter value for oid: 5

Enter value for ono: 105

Enter value for pid: 5

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(5,105,5)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from persons18;

       PID NAME                               MOBILE

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

         1 SANKET                         8745698547

         2 sanket2                        8547125647

         3 sanket3                        9658745214

         4 sanket4                        8574125468

         5 sanket5                        7458965478

SQL> select * from orders18;

       OID    ORDERNO        PID

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

         1        101                   1

         2        102                   2

         3        103                   3

         4        104                   4

         5        105                   5

SQL> select persons18.name,

  2  persons18.mobile,

  3  orders18.orderno

  4  from persons18

  5  inner join orders18

  6  on persons18.pid=orders18.pid

  7  order by persons18.name;

NAME                               MOBILE        ORDERNO

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

SANKET                        8745698547        101

sanket2                        8547125647        102

sanket3                        9658745214        103

sanket4                        8574125468        104

sanket5                        7458965478        105

SQL> commit;

Commit complete.