Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement