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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- create table sql in python
- db2 sql create table example
- sql create table examples
- sql create table within a table
- create table sql query
- sql server create table syntax
- sql server create table from another table
- sql server create table as query
- sql create table with data
- t sql get table schema
- microsoft sql create table example
- sql get table schema