Dbms JOIN Opn

SQL> create table exp7book(bid int, title varchar(20), author varchar(20), status varchar(10));

Table created.

SQL> desc exp7book;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BID                            NUMBER(38)
 TITLE                          VARCHAR2(20)
 AUTHOR                         VARCHAR2(20)
 STATUS                         VARCHAR2(10)

SQL> insert into exp7book values(101,'Fasting Feasting','Anitha Desai','Issued');

1 row created.

SQL> insert into exp7book values(102,'Untouchable','Mukulraj Anand','Not Issued');

1 row created.

SQL> insert into exp7book values(103,'Zahir','Paulo Coelo','Not Issued');

1 row created.

SQL> insert into exp7book values(104,'Ancient Promises','Jayasree Misra','Issued');

1 row created.

SQL> insert into exp7book values(105,'I Dare','Kiran Bedi','Issued');

1 row created.

SQL> select * from exp7book;

       BID TITLE        AUTHOR           STATUS
---------- -------------------- -------------------- ----------
       101 Fasting Feasting Anitha Desai         Issued
       102 Untouchable      Mukulraj Anand       Not Issued
       103 Zahir        Paulo Coelo      Not Issued
       104 Ancient Promises Jayasree Misra       Issued
       105 I Dare       Kiran Bedi       Issued

SQL> create table exp7bissued(bid int, uid int, issuedate date);
create table exp7bissued(bid int, uid int, issuedate date)
                                  *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create table exp7bissued(bid int,uid int,issuedate date);
create table exp7bissued(bid int,uid int,issuedate date)
                                 *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create table exp7bissued(bid int, U_id int, issuedate date);

Table created.

SQL> desc exp7bissued;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BID                            NUMBER(38)
 U_ID                           NUMBER(38)
 ISSUEDATE                      DATE

SQL> insert into exp7bissued values(101,32,'12-06-05');

1 row created.

SQL> insert into exp7bissued values(104,67,'30-06-05');

1 row created.

SQL> insert into exp7bissued values(105,67,'10-07-05');

1 row created.

SQL> select * from exp7bissued;

       BID   U_ID ISSUEDAT
---------- ---------- --------
       101     32 12-06-05
       104     67 30-06-05
       105     67 10-07-05

SQL> delete from exp7bissued where bid=101;

1 row deleted.

SQL> delete from xp7bissued where bid=104;
delete from xp7bissued where bid=104
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> delete from exp7bissued where bid=104;

1 row deleted.

SQL> delete from exp7bissued where bid=105;

1 row deleted.

SQL> insert into exp7bissued values(101,32,'');

1 row created.

SQL> insert into exp7bissued(issuedate) values(STR_TO_DATE("09-Sep-2014","%d-%b-%Y")) where bid=101;
insert into exp7bissued(issuedate) values(STR_TO_DATE("09-Sep-2014","%d-%b-%Y")) where bid=101
                                                                                 *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> insert into exp7bissued(issuedate) values(STR_TO_DATE("09-Sep-2014","%d-%b-%Y"));
insert into exp7bissued(issuedate) values(STR_TO_DATE("09-Sep-2014","%d-%b-%Y"))
                                          *
ERROR at line 1:
ORA-00904: "STR_TO_DATE": invalid identifier


SQL> insert into exp7bissued values(101,32,'12-june-05');

1 row created.

SQL> select * from exp7bissued;

       BID   U_ID ISSUEDAT
---------- ---------- --------
       101     32
       101     32 12-06-05

SQL> delete from exp7bissued where bid=101;

2 rows deleted.

SQL> insert into exp7bissued values(101,32,'12-06-05');

1 row created.

SQL> insert into exp7bissued values(104,67,'30-06-05');

1 row created.

SQL> insert into exp7bissued values(105,67,'10-07-05');

1 row created.

SQL> select * from exp7bissued;

       BID   U_ID ISSUEDAT
---------- ---------- --------
       101     32 12-06-05
       104     67 30-06-05
       105     67 10-07-05

SQL> create table exp7buser(U_id int, Name varchar(10), course varchar(4), semester int, nob int);

Table created.

SQL> desc exp7buser;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 U_ID                           NUMBER(38)
 NAME                           VARCHAR2(10)
 COURSE                         VARCHAR2(4)
 SEMESTER                       NUMBER(38)
 NOB                            NUMBER(38)

SQL> insert into exp7buser values(32,'Flozy','ECE',3,10);

1 row created.

SQL> insert into exp7buser values(33,'Selina','ECE',4,0);

1 row created.

SQL> insert into exp7buser values(34,'Ajith','CS',4,0);

1 row created.

SQL> insert into exp7buser values(67,'Grace','IT',7,2);

1 row created.

SQL> select * from exp7buser;

      U_ID NAME       COUR   SEMESTER        NOB
---------- ---------- ---- ---------- ----------
    32 Flozy      ECE       3         10
    33 Selina     ECE       4          0
    34 Ajith      CS        4          0
    67 Grace      IT        7          2

SQL> select * from exp7buser,exp7bissued where bid=101 and exp7bissued.U_id=exp7buser.U_id;

      U_ID NAME       COUR   SEMESTER        NOB    BID   U_ID ISSUEDAT
---------- ---------- ---- ---------- ---------- ---------- ---------- --------
    32 Flozy      ECE       3         10    101     32 12-06-05

SQL> select Bid,Title,Author,Status from exp7book NATURAL JOIN exp7bissued where issuedate<'15-06-05';

       BID TITLE        AUTHOR           STATUS
---------- -------------------- -------------------- ----------
       101 Fasting Feasting Anitha Desai         Issued

SQL> select * from exp7buser where Semester=4 and NOB>1;

no rows selected

SQL> select Title from exp7book NATURAL JOIN exp7bissued where issuedate='12-06-05';

TITLE
--------------------
Fasting Feasting

SQL> select Title from exp7book NATURAL JOIN exp7bissued where IssueDate='10-07-05';

TITLE
--------------------
I Dare

SQL> select * from exp7Buser where U_id in (select U_id from exp7Book INNER JOIN exp7bissued on exp7Book.Bid=exp7bissued.Bid where Title='Fasting Feasting');

      U_ID NAME       COUR   SEMESTER        NOB
---------- ---------- ---- ---------- ----------
    32 Flozy      ECE       3         10

SQL> select Name from exp7Buser NATURAL JOIN exp7bissued where IssueDate LIKE'%07%';

NAME
----------
Grace

SQL> select Name from exp7Buser NATURAL JOIN exp7bissued NATURAL JOIN exp7Book where Author='Kiran Bedi';

NAME
----------
Grace

SQL> select Title from exp7Book NATURAL JOIN exp7bissued NATURAL JOIN exp7Buser where Name='Flozy';

TITLE
--------------------
Fasting Feasting