SQL> create table customer



PRACTICAL-1

Q1) Create the Customer table-

SQL CAMMAND> create table customer

2 (

3 custid varchar(3) primary key,

4 lname varchar(15),

5 fname varchar(15),

6 area varchar(2),

7 phone number(8)

8 );

Table created.

Q2) Create the Movie Table-

SQL CAMMAND > create table movie

2 (

3 mvno number(2) primary key,

4 title varchar(25),

5 type varchar(10),

6 star varchar(25),

7 price number(8,2)

8 );

Table created.

Q3) Create the Invoice table-

SQL CAMMAND > create table invoice

2 (

3 invno varchar(3) primary key,

4 mvno number(2) references movie(mvno),

5 custid varchar(3) references customer(custid),

6 issuedate date,

7 returndate date

8 );

Table created.

Q4) DISPLAY ALL THE RECORDS OF CUSTOMER TABLE.

SQL CAMMAND > select * from customer;

CUS LNAME FNAME ADD PHONE

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

c1 gupta pradeep SC 28245678

c2 maurya avinash AD 28765439

c3 singh rajiv KL 29876439

c4 sinde swati GT 29871234

c5 singh vivek AD 29001234

c6 shaikh ali BN 29001122

6 rows selected.

Q5) DISPLAY ALL THE RECORDS OF MOVIE TABLE.

SQL CAMMAND > select * from movie

MVNO TITLE TYPE STAR PRICE

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

10 wanted action salman 200

11 welcome comedy akshay 150

12 help horror bobydeol 100

13 sholey action amitab 500

14 3idiot comedy amir 400

Q6) DISPLAY ALL THE RECORDS OF INVOICE TABLE.

SQL CAMMAND > select * from invoice;

INV MVNO CUS ISSUEDATE RETURNDAT

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

iv1 11 c2 05-MAY-10 06-JUN-10

iv2 13 c2 24-NOV-10 23-DEC-10

iv3 13 c4 01-JAN-09 28-FEB-09

iv4 14 c4 21-APR-09 20-MAY-09

iv5 10 c5 24-NOV-08 25-DEC-08

iv6 12 c5 23-FEB-07 24-MAR-07

iv7 12 c1 28-FEB-08 29-MAR-08

iv8 14 c2 03-AUG-09 04-SEP-09

iv9 10 c6 31-DEC-09 01-JAN-10

9 rows selected.

Q7):- FIND THE MOVIES THAT COST MORE THAN 159 AND ALSO FIND THE NEW COST AS ORIGINAL COST *15?

SQL CAMMAND > Select Price*15 as newprice from movie where price > 159

TITLE NEWPRICE

Wanted 3000

sholey 7500

3idiot 6000

Q8) PRINT THE NAMES AND TYPES OF ALL THE MOVIES EXCEPT HORROR MOVIES ?

SQL CAMMAND > select title, type from movie where not type='horror';

TITLE TYPE

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

wanted action

welcome comedy

sholey action

3idiot comedy

Q9) LIST THE VARIOUS MOVIES TYPES AVAILABLE ?

SQL CAMMAND > select distinct type from movie;

TYPE

----------

action

comedy

horror

Q10) LIST THE MVNO,TITLE,TYPE OF MOVIES WHOSE STARS BEGINS WITH LETTER ‘A’.

SQL CAMMAND > select mvno,title,type from movie where star like 'a%';

MVNO TITLE TYPE

11 welcoME comedy

13 sholey action

14 3idiot comedy

Q11) DETERMINE THE MAXIMUM AND MINIMUM OF PRICE. RENAME THE TITLE AS MAX_PRICE AND MIN_PRICE RESPECTIVELY ?

SQL CAMMAND > select max(price) as max_price,min(price) as min_prince from movie;

MAX_PRICE MIN_PRINCE

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

500 100

Q12) FIND THE NUMBER OF MOVIES IN EACH TYPE ?

SQL CAMMAND > select type,count(type) from movie group by type;

TYPE COUNT(TYPE)

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

action 2

comedy 2

horror 1

PRACTICAL-2

Q13) FIND THE LNAME,FNAME AND MVNO OF CUSTOMER WHO HAVE BEEN ISSUED A MOVIE.

SQL CAMMAND > select lname,fname,mvno from customer,invoice where customer.custid=invoice.custid;

LNAME FNAME MVNO

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

maurya avinash 11

maurya avinash 13

sinde swati 13

sinde swati 14

singh vivek 10

singh vivek 12

gupta pradeep 12

maurya avinash 14

shaikh ali 10

9 rows selected.

Q14) PRINT THE INFORMATION OF INVOICE TABLE IN THE FOLLOWING FORMAT FOR ALL RECORDS. THE INVOICE NO. OF CUSTOMER ID. {CUSTID} IS {INVNO} AND MOVIE NO. IS {MVNO}.

SQL CAMMAND > select 'Invoice No. Of Customer id. '||custid||' is '||invno||' and Movie No. is. '||mvno from invoice;

'INVOICENO.OFCUSTOMERID.'||CUSTID||'IS'||INVNO||'ANDMOVIENO.IS.'||MVNO

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

Invoice No. Of Customer id. c1 is iv1 and Movie No. is. 11

Invoice No. Of Customer id. c2 is iv2 and Movie No. is. 13

Invoice No. Of Customer id. c4 is iv3 and Movie No. is. 13

Invoice No. Of Customer id. c4 is iv4 and Movie No. is. 14

Invoice No. Of Customer id. c5 is iv5 and Movie No. is. 10

Invoice No. Of Customer id. c5 is iv6 and Movie No. is. 12

Invoice No. Of Customer id. c1 is iv7 and Movie No. is. 12

Invoice No. Of Customer id. c2 is iv8 and Movie No. is. 14

Invoice No. Of Customer id. c6 is iv9 and Movie No. is. 10

9 rows selected.

Q15) SELECT THE TITLE, CUSTID, MVNO FORV ALL THE MOVIES THAT ARE ISSUED.

SQL CAMMAND > select movie.title,invoice.custid,movie.mvno from movie,invoice where movie.mvno=invoice.mvno ;

TITLE CUS MVNO

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

welcome c2 11

sholey c2 13

sholey c4 13

3idiot c4 14

wanted c5 10

help c5 12

help c1 12

3idiot c2 14

wanted c6 10

9 rows selected.

Q16 ) FIND OUT WHICH CUSTOMER HAVE BEEN ISSUED MOVIE NUMBER(MVNO) 12.

SQL CAMMAND > select fname,mvno from customer,invoice where invoice.mvno=12 and invoice.custid=customer.custid;

FNAME MVNO

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

pradeep 12

vivek 12

Q17) DISPLAY THE MONTH (IN ALPHABETS) IN WHICH CUSTOMER ARE SUPPOSED TO RETURN THE MOVIES.

SQL CAMMAND > select to_char(returndate,'month') custid from invoice order by to_char (returndate);

CUSTID

---------

january

september

june

may

december

march

december

february

march

9 rows selected.

PRACTICAL-3

Q18 ) FIND OUT IF THE MOVIE STARRING AMIR IS ISSUED TO ANY CUSTOMER AND LIST THE CUSTID TO WHOM IT IS ISSUED.

SQL CAMMAND > select movie.title,customer.custid from movie,customer,invoice where movie.mvno=invoice.mvno and invoice.custid=customer.custid and movie.star='amir';

TITLE CUS

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

3idiot c4

3idiot c2

Q19) FIND THE NAMES OF CUSTOMERS WHO HAVE BEEN ISSUED MOVIE OF TYPE ‘HORROR’.

SQL CAMMAND > select fname from customer,invoice where mvno=(select mvno from movie where type='horror') and customer.custid=invoice.custid

FNAME

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

vivek

pradeep

Q20). FIND OUT THE TITLE OF THE MOVIE THAT HAVE BEEN ISSUED TO THE CUSTOMER WHOSE FNAME IS VIVEK.

SQL CAMMAND > select title from customer,invoice,movie where fname='vivek' and movie.mvno=invoice.mvno and invoice.custid=customer.custid;

TITLE

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

wanted

help

Q.21) ADDA COLUMN REMARK OF TYPE VARCHAR AND SIZE 25 TO THE INVOICE TABLE.

SQL CAMMAND > alter table invoice add(remark varchar(25));

Table altered.

SQL CAMMAND > desc invoice

Name Null? Type

INVNO NOT NULL VARCHAR2(3)

MVNO NUMBER(2)

CUSTID VARCHAR2(3)

ISSUEDATE DATE

RETURNDATE DATE

REMARK VARCHAR2(25)

Q22). FIND THE NAMES OF ALL CUSTOMERS HAVING ‘A’ IN THE SECOND LETTER IN THEIR FNAME.

SQL CAMMAND > select fname from customer where fname like '_a%';

FNAME

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

Rajiv

Q.23) FIND OUT THE MOVIE NUMBER WHICH HAS BEEN ISSUED TO CUSTOMER WHOSE FIRST NAME IS ‘VIVEK’.

SQL CAMMAND > select invoice.mvno from customer,invoice where customer.custid=invoice.custid and customer.fname=’vivek’;

MVNO

----------

10

12

Q24) DISPLAY THE TITLE ,LNAME,FNAME FOR CUSTOMERS HAVING MOVIE NUMBER GREATER THAN OR EQUAL TO THREE IN THE FOLLOWING FORMAT. THE MOVIE TAKEN BY {FNAME} {LNAME} IS {TITLE}

SQL CAMMAND > select 'the movie taken by '||fname||' '||lname||' is '||title from customer,movie,invoice where mvno>13

'THEMOVIETAKENBY'||FNAME||''||LNAME||'IS'||TITLE

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

the movie taken by swati sinde is 3idiot

the movie taken by avinash maurya is 3idiot

PRACTICAL-4

Q1.) CREATE THE CLASSMASTER TABLE.

SQL CAMMAND > CREATE TABLE CLASSMASTER

2 (

3 CLASSID VARCHAR(4) PRIMARY KEY,

4 CLASSNAME VARCHAR(20) NOT NULL,

5 INTAKE NUMBER(4)

6* )

7 /

Table created.

Q2) CREATE THE CASTMASTER TABLE.

SQL CAMMAND > CREATE TABLE CASTMASTER

2 (

3 CASTCODE NUMBER(4) PRIMARY KEY,

4 CASTNAME VARCHAR(20) NOT NULL,

5 RESERVATION NUMBER(2)

6 );

Table created.

Q3) DISPLAY ALL THE RECORDS OF STUDMASTER TABLE.

SQL CAMMAND > CREATE TABLE STUDMASTER

2 (

3 REGNO VARCHAR(4) PRIMARY KEY,

4 CLASSID VARCHAR(4) REFERENCES CLASSMASTER(CLASSID),

5 CASTCODE NUMBER(4) REFERENCES CASTMASTER(CASTCODE),

6 STUDFNAME VARCHAR(10),

7 STUDLNAME VARCHAR(10),

8 DDAMT NUMBER(5),

9 DDDATE DATE,

10 CASHPAID NUMBER(5),

11 ACTUAL_FEES NUMBER(5),

12 ADMDATE DATE,

13 ADMSTATUS CHAR(1) CHECK (ADMSTATUS='A' OR ADMSTATUS='C' OR ADMSTATUS='P'),

14 FREESHIP CHAR(1) CHECK (FREESHIP='Y' OR FREESHIP='N')

15 );

Table created.

Q4) CREATE THE STUDDETAIL TABLE.

SQL CAMMAND > CREATE TABLE STUDDETAIL

2 (

3 REGNO VARCHAR(4) REFERENCES STUDMASTER(REGNO),

4 DOB DATE,

5 ADDRESS VARCHAR(20),

6 CITY VARCHAR(20),

7 EMAILID VARCHAR(20),

8 GENDER CHAR(1) CHECK(GENDER='M' OR GENDER='F')

9 )

10 ;

Table created.

Q5) CREATE THE CANCELATION TABLE.

SQL CAMMAND > CREATE TABLE CANCELATION

2 (

3 REGNO VARCHAR(4) REFERENCES STUDMASTER(REGNO),

4 CANCELLATIONDATE DATE,

5 AMTREFUNDED NUMBER

6 );

Table created.

Q6) DISPLAY ALL THE RECORDS OF CLASSMASTER TABLE.

SQL CAMMAND > SELECT * FROM CLASSMASTER;

CLAS CLASSNAME INTAKE

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

IT BSCIT 80

COM BCOM 100

ART BA 200

CS BSCCS 120

Q7) DISPLAY ALL THE RECORDS OF CASTMASTERTABLE.

SQL CAMMAND > SELECT * FROM CASTMASTER

CASTCODE CASTNAME RESERVATION

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

111 GENERAL 40

222 OBC 27

333 SC 33

Q8) DISPLAY ALL THE RECORDS OF STUDMASTER TABLE.

SQL CAMMAND > SELECT * FROM STUDMASTER

REGN CLAS CASTCODE STUDFNAME STUDLNAME DDAMT DDDATE CASHPAID ACTUAL_FEES ADMDATE A F

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

IT01 IT 111 VIVEK SINGH 15000 15-JUN-10 5000 23000 01-JUN-10 A N

IT02 IT 222 AVINASH MAURYA 10000 10-JUN-10 6000 23000 12-JUN-10 A N

COM1 COM 333 AMAR ZENDE 2000 30-JUN-10 600 5000 01-JUL-10 C Y

ART1 ART 111 VISHNU JHA 1000 22-JUL-10 600 3000 31-JUL-10 P N

CS1 CS 111 SWATI SINDE 10000 22-JUL-10 300 20000 20-JUL-10 A Y

Q9) DISPLAY ALL THE RECORDS OF STUDDETAIL TABLE.

SQL CAMMAND > SELECT * FROM STUDDETAIL;

REGN DOB ADDRESS CITY EMAILID GENDER

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

IT01 24-NOV-90 ANDHEERI MUMBAI VIVEK1124@ M

IT02 01-MAR-90 ANDHEERI MUMBAI STARAVI27@ M

COM1 01-APR-89 BADLAPUR RATANAGIRI AMAR1111@ M

ART1 12-OCT-88 SANTACRUZ MUMBAI JHASTART@ M

CS1 22-OCT-91 GATHKOPE NEWMUMBAI SWATISE@ F

Q10) DISPLAY ALL THE RECORDS OF CANCELATION TABLE.

SQL CAMMAND > SELECT * FROM CANCELATION;

REGN CANCELLATIONDATE AMTREFUNDED

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

COM1 01-AUG-10 500

Q11) ADD A COLUMN DIR VARCHAR(25) TO THE CLASSMASTER TABLE.

SQL CAMMAND > ALTER TABLE CASTMASTER ADD DIR VARCHAR(10);

Table altered.

Q12) WIRTE A QUERY TO CHANGE THE RESERVATION FOR OBC CASTE FROM 27% TO 23%.

SQL CAMMAND > UPDATE CASTMASTER SET RESERVATION=23 WHERE RESERVATION=27 AND CASTNAME='OBC

1 row updated.

Q14) LIST ALL STUDENT WHOSE LAST NAME ENDS WITH ‘E’.

SQL CAMMAND > SELECT * FROM STUDMASTER WHERE STUDLNAME LIKE '%E';

REGN CLAS CASTCODE STUDFNAME STUDLNAME DDAMT DDDATE CASHPAID ACTUAL_FEES ADMDATE A F

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

COM1 COM 333 AMAR ZENDE 2000 30-JUN-10 600 5000 01-JUL-10 C Y

CS1 CS 111 SWATI SINDE 10000 22-JUL-10 3000 20000 20-JUL-10 A Y

Q15) LIST THE INFORMATION IN THE FOLLOWING FORMAT.

REGISTRATION NO.|CLASS|STUDENT|TOTAL_FEES_PAID

SQL CAMMAND > SELECT STUDMASTER.REGNO AS REGESTRATION_NO,CLASSMASTER.CLASSNAME AS CLASS,STUDMASTER.STUDFNAME ||' '|| STUDMASTER.STUDLNAME AS STUDENT,STUDMASTER.CASHPAID AS TOTAL_FEES_PAID FROM STUDMASTER LEFT OUTER JOIN CLASSMASTER ON STUDMASTER.CLASSID=CLASSMASTER.CLASSID

REGE CLASS STUDENT TOTAL_FEES_PAID

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

IT02 BSCIT AVINASH MAURYA 6000

IT01 BSCIT VIVEK SINGH 5000

COM1 BCOM AMAR ZENDE 600

ART1 BA VISHNU JHA 600

CS1 BSCCS SWATI SINDE 3000

Q16) CALCULATE THE 19% SEAT FOR OBC CATEGORIES WHEN THE TOTAL INTAKE IS 60 .

SQL CAMMAND > SELECT (19*60/100) FROM DUAL;

(19*60/100)

-----------

11.4

PRACTICAL-5

Q17) DISPLAY ALL THE STUDENT WHO WERE ADMITTED BETWEEN 12-JUN-2010 TO 30-JUN-2010.

SQL CAMMAND > SELECT STUDFNAME,STUDLNAME FROM STUDMASTER WHERE ADMDATE BETWEEN '12-JUN-2010' AND '30-JUN-2010';

STUDFNAME STUDLNAME

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

AVINASH MAURYA

Q18) LIST ALL THE CLASSES HAVING A TOTAL FEES MORE THAN 18000.

SQL CAMMAND > SELECT CLASSMASTER.CLASSNAME FROM CLASSMASTER,STUDMASTER WHERE STUDMASTER.ACTUAL_FEES > 18000 AND STUDMASTER.CLASSID=CLASSMASTER.CLASSID

CLASSNAME

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

BSCIT

BSCIT

BSCCS

Q19) LIST THE TOTAL NO OF GIRLS .ADMITTED IN BSCCS CLASS.

SQL CAMMAND > SELECT COUNT(*) FROM STUDMASTER LEFT OUTER JOIN CLASSMASTER ON CLASSMASTER.CLASSID=STUDMASTER.CLASSID LEFT OUTER JOIN STUDDETAIL ON STUDMASTER.REGNO=STUDDETAIL.REGNO WHERE STUDDETAIL.GENDER='F' AND CLASSMASTER.CLASSNAME='BSCCS';

COUNT(*)

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

1

Q20) CLACULATE THE TOTAL FEES COLLECTED ON THE DATE 1-JUN-2010.

SQL CAMMAND > SELECT SUM(CASHPAID) FROM STUDMASTER WHERE ADMDATE='01-JUN-2010';

SUM(CASHPAID)

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

15000

Q21) LIST THE TOTAL NO. OF CANCELATION IN THE MONTH OF JULY AND YEAR 2010.

SQL CAMMAND > SELECT COUNT(CANCELATION.REGNO) AS TOTAL_CANCEL FROM STUDMASTER LEFT OUTER JOIN CANCELATION ON CANCELATION.REGNO=STUDMASTER.REGNO WHERE STUDMASTER.ADMSTATUS='C' AND CANCELATION.CANCELLATIONDATE BETWEEN '29-JUL-2010' AND '28-AUG-2010'

TOTAL_CANCEL

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

1

Q22) LIST THE TOTAL AMOUNT REFUNDED FOR EACH CLASS.

SQL CAMMAND > SELECT STUDMASTER.CLASSID,SUM(CANCELATION.AMTREFUNDED) FROM STUDMASTER LEFT OUTER JOIN CANCELATION ON STUDMASTER.REGNO=CANCELATION.REGNO GROUP BY STUDMASTER.CLASSID;

CLAS SUM(CANCELATION.AMTREFUNDED)

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

ART

COM 500

CS

IT

Q23) LIST ALL THE STUDENT WHO HAVE BALANCE FEES FOR CLASS BSCIT.

SQL CAMMAND > SELECT STUDMASTER.STUDFNAME FROM STUDMASTER,CLASSMASTER WHERE ( STUDMASTER.ACTUAL_FEES > DDAMT AND CLASSMASTER.CLASSNAME='BSCIT') AND CLASSMASTER.CLASSID=STUDMASTER.CLASSID

STUDFNAME

----------

VIVEK

AVINASHBHAGYA

Q24) CREATE A VIEW V_STUD FROM THE TABLE STUDMASTER WITH THE FOLLOWING FIELD (REGNO,CLASSID,STUDLNAME,STUDFNAME,ADMDATE,FREESHIP,ADMSTATUS).

SQL CAMMAND > CREATE VIEW V_STUD AS

SELECT REGNO,STUDFNAME,STUDLNAME,CLASSID,ADMDATE,FREESHIP,ADMSTATUS

FROM STUDMASTER;

View created.

Q25) DISPLAY THE VIEW RECORDS.

SQL CAMMAND > SELECT * FROM V_STUD;

REGN STUDFNAME STUDLNAME CLAS ADMDATE F A

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

IT01 VIVEK SINGH IT 01-JUN-10 N A

IT02 AVINASH MAURYA IT 12-JUN-10 Y A

COM1 AMAR ZENDE COM 01-JUL-10 Y C

ART1 VISHNU JHA ART 31-JUL-10 N P

CS1 SWATI SINDE CS 20-JUL-10 Y A

IT03 BHAGYA NAGPURE IT 01-JUN-10 N A

6 rows selected.

PRACTICAL-6

Q.1 Consider the following schema:Suppliers(sid:integer, sname varchar,address varchar) Parts(pid:integer, pname:varchar, color:varchar) Catalog(sid:integer,pid:integer,cost:money) using these table write the select statement for following queries:

Solution:

1. Find the names of suppliers who supply some red parts

SQL> select sname from suppliers where sid in (select sid from catalog where pid in (select pid from parts where COLOR='Red'));

Output:

SNAME

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

Tata Group

Reliance Group

2. Find the sids of suppliers who supply some red or green part

SQL> select sname from suppliers where sid in (select sid from catalog where pid in(select pid from parts where color='Red' or Color='Green'));

Output:

SNAME

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

Tata Group

Reliance Group

Birla Group

3. Find the sids of suppliers who supply some red part or are at 221 packet street

SQL> select sname from suppliers where sid in (select sid from catalog where pid in (select pid from parts where color='Red')) or address='Jogeshwari';

Output:

SNAME

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

Tata Group

Reliance Group

4. Finds the sids of suppliers who supply some red part and some green par

SQL> select sid from catalog where pid = Any(select pid from parts where color='Red' or Color='Green');

Output:

SID

----------

10

10

20

10

30

10

30

10

5. Find the sids of suppliers who supply every part.

SQL> select sid from catalog group by sid having count(*)=(select count(*) from parts);

Output:

SID

----------

10

6. Grant the select delete and update authority on book to user 'Alex'

SQL>grant select,delete,update on scott.Book to Alex;

Output:

Grant Succeeded

7. Grant the select, insert authority with the capability to grant those privilegs to other users on Book table to user 'Alex'

SQL> grant select,insert on scott.Book to Alex with Grant option;

Output:

Grant succeeded.

8. Revoke the update privilege on Book table from all users

SQL> Revoke update on scott.BOOK from Alex CASCADE Constraints

Output:

Revoke succeeded.

PRACTICAL-7

Q.1 Consider the following schemas

Book(Title,Author,Publisher,Category,Year,Price)

Distributor(DistID, Distributor,City,Discount,Credit)

Orders(Ordno,Title,DistID,QTY) and write the statements for the following

queries.

1. Get the details of all the books whose price is greater than the average price of the books

SQL>Select Title,Author,Publisher,price from book where price>(select Avg(price) from book);

Output:

TITLE AUTHOR PUBLISHER PRICE

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

SOFTWARE ENG PANKAJ GALGOTIA 600

2. Get the name of all distributors who are supplying the book titled 'software Testing' to the bookshop

SQL> select DISTRIBUTOR From distributor where DISTID IN (select DISTID FROM Orders WHERE TITLE='SOFTWARE ENG.');

Output:

DISTRIBUTOR

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

KRISHNA

3. Get the name of all distributors who give more discount than the average discount

SQL> Select Distributor from Distributor where Discount>(select Avg(Discount) from Distributor);

Output:

DISTRIBUTOR

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

Nirali

4. Get the details of all the books whose price is greater than the maximum of the category average.

SQL> select Title,Author,Publisher from book where price > any (select max(Avg(price)) from book group by category);

Output:

TITLE AUTHOR PUBLISHER

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

LDMS KATE Penguin Book

5. Get the names of all distributors who are supplying the books whose author is 'FROUZAN'

SQL> select Distributor from Distributor where DistID in (Select DistID from orders where Title=(select Title from book where Author='FROUZAN'));

Output:

DISTRIBUTOR

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

NURALI

6. Display the orders, which were issued in first quarter of current year

SQL> select orderno,orderdate from sales_header where orderdate between '01-jan-2010' and 2 '30-mar-2010';

Output:

ORDERNO ORDERDATE

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

6 25-FEB-10

7. 25-JAN-10

7. Display the order number, order date, customer name and order amount for orders having minimum value of 500 Rs

SQL> select sales_header.orderNo,orderDate,customerID,amt from sales_Header,Sales_Detail

where sales_header.orderNo=Sales_Detail.OrderNo and sales_Detail.amt=500;

Output:

ORDERNO ORDERDATE CUSTOMERID AMT

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

5. 29-AUG-10 104 500

8. Display the order detail where RIN001 soap is sold minimum 200 Rs

SQL> select sales_Header.orderno,orderdate,customerID from sales_Detail1,Sales_Header where productId=22 and sales_detail1.rate=50 and sales_detail1.orderno=sales_header.orderno;

Output:

ORDERNO ORDERDATE CUSTOMERID

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

6 25-FEB-10 104

PRACTICAl-8

1. Use the given table and Solve the Queries

(Table Emp(Empid,ename,compId,Salary,Joindate)

Company(CompId,CompName,city)

1. Give name of companies located in those cities where 'TATA' is located

SQL> Select compname from company where city=(select city from company where Compname='TATA GROUP') and compname'TATA GROUP';

Output:

COMPNAME

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

Reliance Group

2. Give the name of the employees living in the same city where their company is located

SQL> select ename,compid from emp natural join company;

Output:

ENAME COMPID

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

Pradeep 101

Prathmesh 102

Amar 103

Ashwin 104

3. update salary of employee 'Raj' by giving him the salary of 'Radha; working in same company

SQL> update emp1 set salary=(select A.SALARY+B.SALARY FROM emp1 A,emp1 B where A.ename='Raj' and B.ename='Radha') where ename='Raj';

Output:

1 row updated

4. Display how many male and female memebers have joined in january 2006

SQL> SELECT COUNT(GENDER) FROM EMP1 WHERE TO_CHAR(JOINDATE,'MON YYYY')='JAN 2006' GROUP BY GENDER;

Output:

COUNT(GENDER)

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

1

1

2. Use the following table Solve The Given Query

(Table Book(BookId,Title,Author,Publisher,Year,Price,DistID)

Distributor(DistId,Name,City)

1. Display the details of all books whose price is more than the average price of all books

SQL> Select bookid,author,publisher,price from book where price > (select Avg(price) from book);

Output:

BOOKID AUTHOR PUBLISHER PRICE

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

2. GROFF TMG 700

3 JULIA BRADLEY TMG 600

2. Display the details of all books written by Groff and supplied by 'TMG'

SQL> select Bookid,Year,Price,DistID,Title from book where Author='GROFF' and Publisher='TMG';

Output:

BOOKID YEAR PRICE DISTID TITLE

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

2. 2008 700 11 SQL-II

3. Create a View to show Title,Author,Publisher and Distributor's Name and name this view as showDetails

SQL> create view showDetails

As

Select Title,Author,Publisher,Distributors.Name from Book,Distributors

where Book.DistId=Distributors.Distid;

View created.

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

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download