Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    Dbms Lab

    Uploaded by

    vvce22cse0114

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Dbms Lab

    Uploaded by

    vvce22cse0114
    6 views4 pages

    Original Title

    dbms lab

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    6 views4 pages

    Dbms Lab

    Uploaded by

    vvce22cse0114

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 4

    LIBRARY DATABASE

    Create table book(book_id int,title varchar(20),publisher_name varchar(30),pub_year varchar(20));


    alter table book add primary key(book_id);

    Create table book_author(book_id int,author_name varchar(20));


    alter table book_author add foreign key(book_id) references book(book_id);

    Create table lib_branch(branch_id int primary key,branch_name varchar(20),address varchar(20));

    create table book_copies(noofcopies int, book_id references book(book_id) on delete cascade,


    branch_id references lib_branch(branch_id) on delete cascade, primary key(book_id, branch_id));

    create table card(cardno int primary key);

    create table booklending(dateout date, duedate date, book_id references book(book_id) on delete
    cascade, branch_id references lib_branch(branch_id) on delete cascade, cardno references
    card(cardno) on delete cascade, primary key(book_id, branch_id, cardno));

    create table publisher(name varchar(20) primary key, phone int, address varchar(20));

    insert into book values(1,'DBMS','JAN-2017','Mcgrav=Hill');


    insert into book values(2,'ADBMS','JUN-2016','Mcgrav-Hill');
    insert into book values(3,'CN','SEP-2016','Pearson');
    insert into book values(4,'CG','SEP-2015','Grupo-Planeta');
    insert into book values(5,'OS','MAY-2016','Pearson');

    insert into book_author values(1,'Navathe');


    insert into book_author values(2,'Navathe');
    insert into book_author values(3,'Thanenbaum');
    insert into book_author values(4,'Edward Angel');
    insert into book_author values(5,'Calvin');

    insert into publisher values('Mcgrav-Hill',5412545365,'Banglore');


    insert into publisher values('Pearson',7362476345,'NewDelhi');
    insert into publisher values('Navathe',7485937584,'Mysore');
    insert into publisher values('Edward Angel',7463820572,'Mumbai');
    insert into publisher values('Calvin'.234565436,'kolkata');

    insert into lib_branch values(10,'RRNAGAR','BANGLORE');


    insert into lib_branch values(11,'RMSIT','BANGLORE');
    insert into lib_branch values(12,'RAJAJINAGAR','BANGLORE');
    insert into lib_branch values(13,'NITTE','MANGLORE');
    insert into lib_branch values(14,'MANIPAL','UDUPI');

    insert into card values(100);


    insert into card values(101);
    insert into card values(102);
    insert into card values(103);
    insert into card values(104);
    insert into book_copies values(3,1,10);
    insert into book_copies values(4,2,11);
    insert into book_copies values(5,3,12);
    insert into book_copies values(6,4,13);
    insert into book_copies values(7,5,14);

    insert into booklending values(1,10,101,'01-Jan-17','01-Jun-17');


    insert into booklending values(3,14,101,'11-Jan-17','11-Mar-17');
    insert into booklending values(2,13,101,'21-Feb-17','21-Apr-17');
    insert into booklending values(4,11,101,'15-Mar-17','15-Jul-17');
    insert into booklending values(1,11,104,'12-Apr-17','12-May-17');

    QUERY
    select b.book_id,b.title,b.publisher_name,ba.author_name,bc.noofcopies ,bc.branch_name
    from book b,book_author ba,book_copies bc,lib_branch lb
    where b.book_id=ba.book_id and
    b.book_id=bc.book_id and
    bc.branch_id=lb.branch_id;

    select cardno
    from booklending
    where Date_out between '2017-01-01' AND '2017-06-30'
    Group by cardno
    Having count(*)>3;

    Delete from book


    where book_id='1';
    select * from book;
    select * from book_author;
    select * from booklending;
    select * from book_copies;

    Create view Vbook As


    Select pub_year
    from book;
    Select * from Vbook;

    Create view bookavailable as


    Select b.book_id, b.title, bc.noofcopies
    from book b, book_copies bc, lib_branch lb
    where
    b.book_id = bc.book_id AND
    bc.branch_id=lb.branch_id;
    Select * from bookavailable;
    ORDER DATABASE
    create table salesman(salesman_id int primary key,name varchar(20),city varchar(20),commission
    varchar(20));

    create table customer(customer_id int primary key,cust_name varchar(20),city varchar(20),grade


    varchar(20),salesman_id references salesman(salesman_id) on delete cascade);

    create table orders(ord_no int primary key,purchase_amt varchar(20),ord_date


    varchar(20),customer_id references customer(customer_id) on delete cascade,salesman_id
    references salesman(salesman_id) on delete cascade);

    insert into salesman values(1000,'John','Bangalore','25');


    insert into salesman values(2000,'Ravi','Bangalore','20');
    insert into salesman values(3000,'Kumar','Mysuru','15');
    insert into salesman values(4000,'Smith','Delhi','30');
    insert into salesman values(5000,'Harsha','Hyderabad','15');

    insert into customer values(10,'Preethi','Bangalore','100','1000');


    insert into customer values(11,'Vivek','Mangalore','300','1000');
    insert into customer values(12,'Bhaskar','Chennai','400','2000');
    insert into customer values(13,'Chethan','Bangalore','200','2000');
    insert into customer values(14,'Mamatha','Bangalore','400','3000');

    insert into orders values(50,'5000','04-May-17','10','1000');


    insert into orders values(51,'450','20-Jan-17','10','2000');
    insert into orders values(52,'1000','20-Jan-17','13','2000');
    insert into orders values(53,'3500','13-Apr-17','14','3000');
    insert into orders values(54,'550','09-Mar-17','12','2000');

    QUERY

    select count(customer_id)
    from customer
    where grade > (select Avg(grade)
    from customer
    where city='Bangalore');

    select s.name,count(customer_id)
    from salesman s,customer c
    where s.salesman_id=c.salesman_id
    Group by name
    Having count(customer_id)>1;
    (select name from salesman S,customer C where S.salesman_id=C.salesman_id and S.city=C.city)
    UNION
    (select name
    from salesman S,customer C
    where S.salesman_id NOT IN(select S.salesman_id from salesman S,customer C where
    S.salesman_id=C.salesman_id and S.city=C.city));

    create view highest purchase as


    select salesman_id
    from orders
    where purchase_amt=(select Max(0.purchase_amt)
    from order 0
    where ord_date='20-JAN-17');
    select * from highest purchase;

    delete from salesman


    where salesman_id='1000';
    select * from salesman;
    select * from customer;
    select * from orders;

    You might also like