VIDEO LIBRARY MANAGEMENT IN SQL

SQL>create table customer (cno int,fname varchar(10),sname varchar(10),area varchar(10),phone int);

 Table created

SQL>insert table customer values(‘&cno’,’&fname’,’&sname’,’&area’,’&phone’);

Enter value for cno: 1

Enter value for fname: john

Enter value for sname: peter

Enter value for area: cbe

Enter value for phone: 8548547895

1 row created

SQL>select * from customer;

CNO FNAME SNAME AREA PHONE

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

1        john        peter        cbe       8548547895

2       dean      winsister    cbe2     1245789987

3       emma    Watson      cbe4     3455568765

SQL>create table compact (cdno int,title varchar(10),price varchar(10),cname varchar(10));

 Table created

SQL>insert table compact values(‘&cdno’,’& title,’& price’,’& cname’);

Enter value for cdno: c1

Enter value for title: algorithm

Enter value for price: 200

Enter value for cname: john

1 row created

SQL>select * from compact;

CDNO TITLE PRICE  CNAME

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

C1       algorithm    200    john       

C2       program      400    sam

C3       hardware    1000   Watson           

SQL>create table invoice (invno int,cidno int,issuedate date,returndate date);

 Table created

SQL>insert table invoice values(‘&invno’,’&cidno’,’&issuedate’,’&returndate’);

Enter value for invno: 1

Enter value for cidno: 1

Enter value for issuedate: 28/1/10

Enter value for returndate: 28/2/10

1 row created

SQL>select * from invoice;

INVNO  CIDNO  ISSUEDATE  RETURNDATE

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

1                 1            28/1/10           28/2/10

2                 2            14/3/1/10        14/4/10

3                 3            16/4/10            26/5/10

SQL>select area from customer;

AREA

--------

cbe    

cbe2

cbe4

SQL>select max(price) as”maximum” from compact;

MAXIMUM

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

1000

SQL>select min(price) as”minimum” from compact;

MINIMUM

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

200

SQL>select avg(price) as”avg” from compact;

AVG

---------

533.3

SQL>select * from compact where title like ’%algorithm%’;

CDNO TITLE PRICE  CNAME

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

C1       algorithm 200       john       

SQL>select datediff(day,’2010-01-28’,’2010-02-28’) as diffdatebetween from invoice;

DIFFDATEBETWEEN

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

            30

SQL>delete from invoice where returndate =’28/2/10’;

1 row deleted

SQL>select cname from compact;

CNAME

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

  john       

  sam

  Watson               

SQL>select * from customer where phone=’ 8548547895’;

CNO FNAME SNAME AREA PHONE

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

1        john        peter       cbe       8548547895