DBMS LAB 2 SOLUTION

SQL>create table student(stud_no number(5) primary key,stud_name varchar2(15));

SQL>desc student;

   Name                                      Null?    Type

………………………………………………………………………………………..

STUD_NO                                     NOT NULL  NUMBER(5)

STUD_NAME                                                   VARCAHR2(15)

Valid Test Data:

SQL>insert into student values(&stud_no,’&stud_name’);

SQL>select * from student;

STUD_NO    STUD_NAME

....................................................................

508                   HARISH

513                   BALAJI

518                   RAKESH

524                   PAVAN

534                   JOYCE

SQL>create table membership(mem_no number(5) primary key,stud_no number(5) references student(stud)no));

SQL>dsec membership;

Name                                      Null?              Type

…………………………………………………………………………………………………….

MEM_NO                            NOT NULL          NUMBER(5)

STUD_NO                                                   NUMBER(5)

SQL>insert into membership values(&mem_no,&stud_no);

Enter value for mem_no:5440

Enter value for stud_no:510

old  1:insert into membership values(&mem_no,&stud_no)

new 1:insert into membership values(5440,510)

insert into membership values(5440,510)

*

Errors Observed:

ERROR at line 1:

ORA-02291:integrity constraint(HARISH.SYS_C002724)violated-primary key not found

SQL>select * from membership;

       MEM_NO           STUD_NO

………………………………………………………………………..

5440                                                   513

5441                                                   508

5442                                                   518

5443                                                   534

5444                                                   524

SQL>create table book(book_no number(5) primary key,book_name varchar2(20),author varchar2(2));

SQL>desc book;

Name                                      Null?    Type

………………………………………………………………………………………..

BOOK_NO                                  NOT NULL          NUMBER(5)

BOOK_NAME                                                       VARCHAR2(20)

AUTHOR                                                             VARCHAR2(20)

SQL>insert into book values(&book_no,’&book_name’,’&author’);

SQL>select * from book;

BOOK_NO         BOOK_NAME          AUTHOR

………………………………………………………………………………………………..

9123                 DBMS                Rama Krishna

2342                 JAVA                 Robett wilkins

4523                 Fearless tales      Alfred

8723                 my ambition       Harish

7821                 Harry Potter        JK Rowling

SQL>create table lss_rec(iss_no number primary key,iss_date date,mem_no number(5) references membership(mem_no),book_no number(5) references book(book_no));

SQL>desc iss_rec;

Name                                      Null?              Type

………………………………………………………………………………………………………

ISS_NO                             NOT NULL          NUMBER

ISS_DATE                                                  DATE

MEM_NO                                                    NUMBER(5)

BOOK_NO                                                  NUMBER(5)

SQL>select * from iss_rec;

ISS_NO        ISS_DATE       MEM_NO       BOOK_NO

…………………………………………………………………………………………………

43             05-JAN-06          5443                 4523

81             28-DEC-05          5441                 8723

22             08-DEC-05          5440                 7821

53             07-JAN-06          5442                 9123

35             06-JAN-06          5444                 2342

c) List all the student names with their membership numbers

SQL> select s.studname, m.memno from student s, membership m where m.studno=s.studno;

STUDNAME MEMNO

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

abhijeet             1001

arun                  1002

arvind                1003

ashish                1004

ashwin               1005

d)  List all the issues for the current date with student and Book names

SQL> select i.issno, s.studname, b.bookname from iss_rec I, membership m, student s, book b

2 where i.memno=m.memno and m.studno=s.studno and

i.issdate=to_char(sysdate);

ISSNO       STUDNAME BOOKNAME

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

13             arvind                P&S

e)  List  the details of students who borrowed book whose author is CJDATE

SQL> select * from student where studno in(select studno from membership where memno in

2 (select memno from iss_rec where bookno in(select bookno from book where author=’CJDATE’)));

STUDNO    STUDNAME

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

505                      ashwin

f)   Give a count of how many books have  been bought by each student

SQL> select s.studno, count(i.bookno) from student s.membership m, book b, 2 iss_rec I where s.studno=m.studno and b.bookno=i.bookno group by s.studno;

STUDNO    COUNT(I.BOOKNO)

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

501           5

502           5

503           5

504           5

505           5

g)  Give a list of books taken by student with stud_no as 5

SQL> select bookname from book where bookno in (select bookno from iss_rec where

2 memno in(select memno from membership where

3 studno in(select studno from student where studno=5)));

BOOKNAME

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

NT

h)  List the book  details which are issued  as of today

SQL> delete from book where bookno in(select bookno from iss_rec where issdate=to_char(sysdate));

delete from book where bookno in (select bookno from iss_rec where issdate=to_char(sysdate))

Errors Observed:

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.SYS_C00840) violated – child record found

i)    Create a view which lists out the iss_no, iss _date, stud_name, book name

j)   Create a view which lists the daily issues-date wise for the last one week