report2 by blogmajeed

VIEWS: 0 PAGES: 2

oracle, sql

More Info
									2. Generate a report which contains the book details and the number of copies supplied,
supplies last year.

  SQL> create table bookdetails(bookno number(4),bookname varchar2(30),publisher
  varchar2(30),price number(4,2),stock number(4),year number(4));
  SQL>insert into bookdetails values(1001,'AWK Programming Language','Addison-
  Wesley',10,625,1999);
  SQL>insert into bookdetails values(1002,'C Programming Handbook','Prentice-
  Hall',15,1000,2000);
  SQL> insert into bookdetails values(1003,'Set theory and Logic','Dover
  Publication',8.5,750,2002);
  SQL> insert into bookdetails values(1004,'C Premier Plus','Honeared
  WSams',24.95,500,2001);
  SQL> insert into bookdetails values(1001,'AWK Programming Language','Addison-
  Wesley',10,500,2000);
  SQL> insert into bookdetails values(1002,'C Programming Handbook','Prentice_
  Hall',15,700,2001);
  SQL> select * from bookdetails;

BOOKNO BOOKNAME                                     PUBLISHER          PRICE STOCK YEAR
------------- -----------------------------------   ---------------- ---------- --------- --------
  1001       AWK Programming Language               Addison-Wesley          10       625 1999
  1002       C Programming Handbook                   Prentice-Hall          15      1000 2000
  1003      Set theory and Logic                     Dover Publication     8.5       750 2002
  1004      C Premier Plus                           Honeared W Sams 24.95           500 2001
  1001      AWK Programming Language                 Addison-Wesley          10      500 2000
  1002      C Programming Handbook                   Prentice-Hall           15       700 2001

6 rows selected.

SQL> ttitle center "BOOK REPORT"
SQL> btitle center "THANK YOU"

SQL> select m.bookno, (n.stock-m.stock) "STOCK_SUPPLY", m.year, n.year from
bookdetails m, bookdetails n where m.bookno=n.bookno and ((m.year-n.year)=1) and
m.year>n.year and n.stock>m.stock;

Sat Nov 02
                               BOOK REPORT

         BOOKNO STOCK_SUPPLY YEAR YEAR
         ------------ ---------------------- -------- --------
                1001              125          2000     1999
                1002              300          2001     2000

                            THANK YOU

								
To top