Ex 4:
To create table book using SQL command to store ISBN, TITLE, PUBLISHER, CATEGORY, YEAR, PRICE.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> CREATE TABLE BOOK(
ISBN VARCHAR2(10) PRIMARY KEY,
TITLE VARCHAR2(20) NOT NULL,
PUBLISHER VARCHAR2(15) NOT NULL,
CATEGORY VARCHAR2(10) NOT NULL,
YEAR NUMBER(04) NOT NULL,
PRICE NUMBER(8,2) NOT NULL);
Insert values into table:
INSERT INTO BOOK
VALUES(‘C01001’,’JAVA’,’MICROSOFTPRESS’,
’COMP SC’,2009,2000);
SQL> SELECT * FROM BOOK;
ISBN TITLE PUBLISHER CATEGORY YEAR PRICE
-------------------------------------------------------------------------------------------------------
C01001 JAVA MICROSOFT PRESS COMP SC 2009 2000
C01002 C++ MICRO COMP SC 2009 1500
CO1003 INDIAN CIVIL SUMUKHA HISTORY 2011 999
C01004 ELECTRONICS RAMAN PHYSICS 2006 500
C01005 LIVING ORGANISMS STROUPTOS BIOLOGY 2010 450
C01006 INDIAN CIVIL C.V CIVICS 2003 200
C01007 COA MICRO COMP SC 2009 150
C01008 FORMULAS MATHEMATICS MATHS 2000 100
C01009 JAVA MICROSOFT PRESS COMP SC 2004 156
C01010 FORMULAS MATHEMATICS MATHS 2001 250
10 rows selected.
1. List the details of the book whose publishers name start with m.
SQL> SELECT * FROM BOOK
WHERE PUBLISHER LIKE 'M%';
ISBN TITLE PUBLISHER CATEGORY YEAR PRICE
-----------------------------------------------------------------------------------------------
C01001 JAVA MICROSOFT PRESS COMP SC 2009 2000
C01002 C++ MICRO COMP SC 2009 1500
C01007 COA MICRO COMP SC 2009 150
C01008 FORMULAS MATHEMATICS MATHS 2000 100
C01009 JAVA MICROSOFT PRESS COMP SC 2004 156
C01010 FORMULAS MATHEMATICS MATHS 2001 250
6 rows selected.
2. List the details of the publisher having A as second character in their name.
SQL> SELECT * FROM BOOK
WHERE PUBLISHER LIKE '_A%';
ISBN TITLE PUBLISHER CATEGORY YEAR PRICE
-------------------------------------------------------------------------------------------------------
C01004 ELECTRONICS RAMAN PHYSICS 2006 500
C01008 FORMULAS MATHEMATICS MATHS 2000 100
C01010 FORMULAS MATHEMATICS MATHS 2001 250
3. Find the books published in 2010,2011 and 2012.
SQL> SELECT * FROM BOOK
WHERE YEAR IN(2010,2011,2012);
ISBN TITLE PUBLISHER CATEGORY YEAR PRICE
-------------------------------------------------------------------------------------------------------
C01001 JAVA MICROSOFT PRESS COMP SC 2009 2000
C01002 C++ MICRO COMP SC 2009 1500
CO1003 INDIAN CIVIL SUMUKHA HISTORY 2011 999
C01005 LIVING ORGANISMS STROUPTOS BIOLOGY 2010 450
C01007 COA MICRO COMP SC 2009 150
4. Display the book_id, title, publisher of all the books in the descending order in the year.
SQL> SELECT ISBN,TITLE,PUBLISHER FROM BOOK
ORDER BY YEAR DESC;
ISBN TITLE PUBLISHER
--------------------------------------------------------------------------
CO1003 INDIAN CIVIL SUMUKHA
C01005 LIVING ORGANISMS STROUPTOS
C01001 JAVA MICROSOFT PRESS
C01002 C++ MICRO
C01007 COA MICRO
C01004 ELECTRONICS RAMAN
C01009 JAVA MICROSOFT PRESS
C01006 INDIAN CIVILIZ C.V
C01010 FORMULAS MATHEMATICS
C01008 FORMULAS MATHEMATICS
10 rows selected.
5. Display the details of all the books other than ‘Microsoft press’ publishers.
SQL> SELECT * FROM BOOK
WHERE PUBLISHER NOT IN('MICROSOFT PRESS');
ISBN TITLE PUBLISHER CATEGORY YEAR PRICE
-------------------------------------------------------------------------------------------------------
C01002 C++ MICRO COMP SC 2009 1500
CO1003 INDIAN CIVIL SUMUKHA HISTORY 2011 999
C01004 ELECTRONICS RAMAN PHYSICS 2006 500
C01005 LIVING ORGANISMS STROUPTOS BIOLOGY 2010 450
C01006 INDIAN CIVIL C.V CIVICS 2003 200
C01007 COA MICRO COMP SC 2009 150
C01008 FORMULAS MATHEMATICS MATHS 2000 100
C01010 FORMULAS MATHEMATICS MATHS 2001 250
8 rows selected.
6. Display title, price of all the books with the price more than 2000 and less than 3000 (using BETWEEN) operator.
SQL> SELECT TITLE, PRICE FROM BOOK
WHERE PRICE BETWEEN 2000 AND 3000;
TITLE PRICE
---------------------------------
JAVA 2000
No comments:
Post a Comment