[1] Print the absolute value of –15.35.
SQL> SELECT ABS(-15.25) FROM DUAL;
ABS(-15.25)
-----------
15.25
-------------------------------------------------------------------------------------------------------------------
[2] Calculate 3.25 raised to 2.25
SQL> SELECT POWER(3.25,2.25) FROM DUAL;
POWER(3.25,2.25)
----------------
14.1820027
-------------------------------------------------------------------------------------------------------------------
[3] Display the rounded value of 3.1417 upto 3 decimal places.
SQL> SELECT ROUND(3.1417,3) FROM DUAL;
ROUND(3.1417,3)
---------------
3.142
-------------------------------------------------------------------------------------------------------------------
[4] Display the truncated value of 3.1417 upto 3 decimal places.
SQL> SELECT TRUNC(3.1417,3) FROM DUAL;
TRUNC(3.1417,3)
---------------
3.141
-------------------------------------------------------------------------------------------------------------------
[5] Find the square root of 17 and –13 if possible.
SQL> SELECT SQRT(17) FROM DUAL;
SQRT(17)
----------
4.12310563
SQL> SELECT SQRT(-13) FROM DUAL;
SELECT SQRT(-13) FROM DUAL
*
ERROR at line 1:
ORA-01428: argument '-13' is out of range
-------------------------------------------------------------------------------------------------------------------
[6] Print the value of e to the 5th power
SQL> SELECT EXP(5) FROM DUAL;
EXP(5)
----------
148.413159
-------------------------------------------------------------------------------------------------------------------
[7] Print the ceil value and floor value of 15.72
SQL> SELECT CEIL(15.72) FROM DUAL;
CEIL(15.72)
-----------
16
SQL> SELECT FLOOR(15.72) FROM DUAL;
FLOOR(15.72)
------------
15
-------------------------------------------------------------------------------------------------------------------
[8] Find the value of 13 mod 5
SQL> SELECT MOD(13,5) FROM DUAL;
MOD(13,5)
----------
3
-------------------------------------------------------------------------------------------------------------------
[9] Add 275 months to your date of birth and display it.
SQL> SELECT ADD_MONTHS('27-Nov-90',275) FROM DUAL;
ADD_MONTH
---------
27-OCT-13
-------------------------------------------------------------------------------------------------------------------
[10] Find the value sin of 100, and log 100 to the base 10
SQL> SELECT SIN(100),LOG(100,10) FROM DUAL;
SIN(100) LOG(100,10)
---------- -----------
-.50636564 .5
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
* CREATE A DATABASE OF BOOKS : (no, title, author, publication, price, edition)
-------------------------------------------------------------------------------------------------------------------
-: CREATING TABLE books :-
____________________________
SQL> CREATE TABLE books
2 (
3 no NUMBER(3),
4 title VARCHAR2(20),
5 author VARCHAR2(20),
6 publication VARCHAR2(20),
7 price NUMBER(5),
8 edition NUMBER(2)
9 );
Table created.
-------------------------------------------------------------------------------------------------------------------
-: INSERTING DATA IN books :-
_______________________________
SQL> insert into books values(101,'Programming in C++','e Balagurusamy','Tata McGraw Hill',200,9);
1 row created.
SQL> insert into books values(102,'Let us C++','Yashwant Kanitkar','PHI',450,4);
1 row created.
SQL> insert into books values(103,'Let us C++','manhas Katre','Pearson',240,5);
1 row created.
SQL> insert into books values(104,'Digital Computers','Morris Mano','MH ',445,3);
1 row created.
SQL> insert into books values(105,'Mockingbird','E Tolstoy','Excel',660,1);
1 row created.
SQL> insert into books values(106,'inside your memory','p S Jalal','PHI',778,12);
1 row created.
-------------------------------------------------------------------------------------------------------------------
[11] List the names of author starting with capital letter.
SQL> SELECT author FROM books
2 WHERE AUTHOR LIKE INITCAP(author);
AUTHOR
--------------------
Yashwant Kanitkar
Morris Mano
E Tolstoy
-------------------------------------------------------------------------------------------------------------------
[12] Display the name of book in both lower and upper case using a single query.
SQL> SELECT UPPER(title),LOWER(title) FROM books;
UPPER(TITLE) LOWER(TITLE)
-------------------- --------------------
PROGRAMMING IN C++ programming in c++
LET US C++ let us c++
LET US C++ let us c++
DIGITAL COMPUTERS digital computers
MOCKINGBIRD mockingbird
INSIDE YOUR MEMORY inside your memory
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[13] Display the output : ‘BAD’ using CHR function.
SQL> SELECT CHR(66),CHR(65),CHR(68) FROM DUAL;
C C C
- - -
B A D
-------------------------------------------------------------------------------------------------------------------
[14] Display the price of books in the following format : ****450
SQL> SELECT LPAD(price,7,'*') FROM books;
LPAD(PRICE,7,'*')
----------------------------
****200
****450
****240
****445
****660
****778
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[15] Display the price of books in the following format : 450*/*/
SQL> SELECT RPAD(price,7,'*/') FROM books;
RPAD(PRICE,7,'*/')
----------------------------
200*/*/
450*/*/
240*/*/
445*/*/
660*/*/
778*/*/
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[16] Display the publication of PHI as Pearson (Use replace…)
SQL> SELECT REPLACE(publication,'PHI','Pearson') publication FROM books;
PUBLICATION
--------------------------------------------------------------------------------
Tata McGraw Hill
Pearson
Pearson
MH
Excel
Pearson
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[17] Display the title of books and trim the ending part if it ends with prog.
SQL> SELECT TRIM(title) FROM books
2 WHERE title LIKE '%prog';
no rows selected
-------------------------------------------------------------------------------------------------------------------
[18] Display the titles of books which contains ‘++’ in it.
SQL> SELECT title FROM books
2 WHERE title LIKE '%++%';
TITLE
--------------------
Programming in C++
Let us C++
Let us C++
-------------------------------------------------------------------------------------------------------------------
[19] Modify the column size of author.
SQL> ALTER TABLE books MODIFY(author VARCHAR2(40));
Table altered.
-------------------------------------------------------------------------------------------------------------------
[20] Add a new column author2 to the table.
SQL> ALTER TABLE books ADD(author2 VARCHAR2(30));
Table altered.
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
[1] Create a table stud_marks which contains (no, fname, lname, m1, m2, m3, dob)
m1,m2,m3 :- marks of 3 subjects, Enter atleast 10 values in this table
-------------------------------------------------------------------------------------------------------------------
-: CREATING TABLE stud_marks :-
_________________________________
SQL> CREATE TABLE stud_marks
2 (
3 rollno NUMBER(3),
4 fname VARCHAR2(20),
5 lname VARCHAR2(30),
6 m1 NUMBER(3),
7 m2 NUMBER(3),
8 m3 NUMBER(3),
9 dob DATE
10 );
Table created.
-------------------------------------------------------------------------------------------------------------------
-: INSERTING DATA INTO stud_marks :-
______________________________________
SQL> insert into stud_marks values(1,'Sandeep','Negi',65,34,98,'27-Nov-90');
1 row created.
SQL> insert into stud_marks values(2,'Divyesh','Patel',88,43,56,'22-Apr-94');
1 row created.
SQL> insert into stud_marks values(3,'Sandip','Patel',99,36,78,'3-Apr-95');
1 row created.
SQL> insert into stud_marks values(4,'Jitendra','Patel',78,25,90,'12-Dec-93');
1 row created.
SQL> insert into stud_marks values(5,'Brijesh ','Chavda',90,44,12,'10-Mar-94');
1 row created.
SQL> insert into stud_marks values(6,'Navin','Gelot',87,45,23,'9-Sep-93');
1 row created.
SQL> insert into stud_marks values(7,'Mukesh','Vaza',70,80,34,'10-Mar-92');
1 row created.
SQL> insert into stud_marks values(8,'Manoj','Gohel',89,88,40,'28-Nov-92');
1 row created.
SQL> insert into stud_marks values(9,'Ravi','Prajapati',45,89,23,'12-Jun-92');
1 row created.
SQL> insert into stud_marks values(10,'Shalin','Shah',99,40,67,'14-Aug-91');
1 row created.
-------------------------------------------------------------------------------------------------------------------
[2] Display the details in following order : RollNo. Lname, Fname
SQL> SELECT no,lname,fname FROM stud_marks;
ROLLNO LNAME FNAME
---------- --------------------------- --------------------
1 Negi Sandeep
2 Patel Divyesh
3 Patel Sandip
4 Patel Jitendra
5 Chavda Brijesh
6 Gelot Navin
7 Vaza Mukesh
8 Gohel Manoj
9 Prajapati Ravi
10 Shah Shalin
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[3] Find the current age of each student.
SQL> SELECT TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(dob,'YYYY') FROM stud_marks,DUAL;
TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(DOB,'YYYY')
-------------------------------------------
25
21
20
22
21
22
23
23
23
24
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[4] Display the total marks of each student along with the rollno
SQL> SELECT no,m1+m2+m3 FROM stud_marks;
NO M1+M2+M3
---------- ----------
1 197
2 187
3 213
4 193
5 146
6 155
7 184
8 217
9 157
10 206
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[5] Display the percentage of each student
SQL> SELECT (m1+m2+m3)/3 "PERCENTAGE" FROM stud_marks;
PERCENTAGE
----------
65.6666667
62.3333333
71
64.3333333
48.6666667
51.6666667
61.3333333
72.3333333
52.3333333
68.6666667
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[6] Display the students scoring highest marks in each subject.
SQL> SELECT fname,m1"MAX MARKS IN m1" FROM stud_marks
2 WHERE m1 IN(SELECT MAX(m1) FROM stud_marks);
FNAME MAX MARKS IN m1
-------------------- ---------------
Sandip 99
Shalin 99
SQL> SELECT fname,m2"MAX MARKS IN m2" FROM stud_marks
2 WHERE m2 IN(SELECT MAX(m2) FROM stud_marks);
FNAME MAX MARKS IN m2
-------------------- ---------------
Ravi 89
SQL> SELECT fname,m3"MAX MARKS IN m3" FROM stud_marks
2 WHERE m3 IN(SELECT MAX(m3) FROM stud_marks);
FNAME MAX MARKS IN m3
-------------------- ---------------
Sandeep 98
-------------------------------------------------------------------------------------------------------------------
[7] Display the students scoring highest total marks
SQL> SELECT fname,m1+m2+m3"MAX TOTAL MARKS" FROM stud_marks
2 WHERE m1+m2+m3 IN(SELECT MAX(m1+m2+m3) FROM stud_marks);
FNAME MAX TOTAL MARKS
-------------------- ---------------
Manoj 217
-------------------------------------------------------------------------------------------------------------------
[8] Display the students whose name starts with ‘S’.
SQL> SELECT fname FROM stud_marks
2 WHERE fname LIKE 'S%';
FNAME
--------------------
Sandeep
Sandip
Shalin
-------------------------------------------------------------------------------------------------------------------
[9] Display the students whose surname ends with ‘kar’
SQL> SELECT fname,lname FROM stud_marks
2 WHERE lname LIKE '%kar';
no rows selected
-------------------------------------------------------------------------------------------------------------------
[10] List all the students who fails in any one of the subjects.
SQL> SELECT fname FROM stud_marks
2 WHERE m1<35 OR m2<35 OR m3<35;
FNAME
--------------------
Sandeep
Jitendra
Brijesh
Navin
Mukesh
Ravi
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[11] List all the students who are passing in all subjects but the percentage < 60
SQL> SELECT fname FROM stud_marks
2 WHERE m1>35 AND m2>35 AND m3>35
3 AND (m1+m2+m3)/3 < 60;
no rows selected
-------------------------------------------------------------------------------------------------------------------
[12] List the students whose total marks are between 50 and 60
SQL> SELECT fname FROM stud_marks
2 WHERE (m1+m2+m3)>=50 AND (m1+m2+m3)<=60;
no rows selected
-------------------------------------------------------------------------------------------------------------------
[13] List all the students whose name does not start with ‘S’
SQL> SELECT fname FROM stud_marks
2 WHERE fname NOT LIKE 'S%';
FNAME
--------------------
Divyesh
Jitendra
Brijesh
Navin
Mukesh
Manoj
Ravi
7 rows selected.
-------------------------------------------------------------------------------------------------------------------
[14] Update the table, set marks m1=40 for those students who scored a total of atleast 100 marks in m2 and m5
SQL> UPDATE stud_marks
2 SET m1=40 WHERE m2+m3>=100;
7 rows updated.
-------------------------------------------------------------------------------------------------------------------
[15] Display the students whose marks are either 50, 60 or 70.
SQL> SELECT fname FROM stud_marks
2 WHERE m1 IN(50,60,70) OR
3 m2 IN(50,60,70) OR
4 m3 IN(50,60,70);
no rows selected
-------------------------------------------------------------------------------------------------------------------
[16] List all the students born in the month of January.
SQL> SELECT fname FROM stud_marks
2 WHERE TO_CHAR(dob,'MON')='JAN';
FNAME
--------------------
Ravi
Shalin
-------------------------------------------------------------------------------------------------------------------
[17] List all the students whose date of birth is even no.
SQL> SELECT fname FROM stud_marks
2 WHERE MOD(TO_CHAR(dob,'DD'),2)=0;
FNAME
--------------------
Divyesh
Jitendra
Brijesh
Mukesh
Manoj
Shalin
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[18] Find the age of students in terms of months passed
SQL> SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,dob))"AGE IN MONTHS" FROM stud_marks;
AGE IN MONTHS
-------------
300
259
248
264
261
267
285
276
286
298
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[19] Display the students whose date of birth falls in the first quarter of the year
SQL> SELECT fname FROM stud_marks
2 WHERE EXTRACT(MONTH FROM dob) BETWEEN 1 AND 4;
FNAME
--------------------
Divyesh
Sandip
Brijesh
Mukesh
Ravi
Shalin
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
[20] Find the date, 15 days after today’s date.
SQL> SELECT SYSDATE+15"DATE" FROM DUAL;
DATE
---------
27-DEC-15
-------------------------------------------------------------------------------------------------------------------
[21] List the students whose name contains vowels (small)
SQL> SELECT fname FROM stud_marks
2 WHERE fname LIKE '%a%'
3 OR fname LIKE '%e%'
4 OR fname LIKE '%i%'
5 OR fname LIKE '%o%'
6 OR fname LIKE '%u%';
FNAME
--------------------
Sandeep
Divyesh
Sandip
Jitendra
Brijesh
Navin
Mukesh
Manoj
Ravi
Shalin
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[22] List the students whose name does not contain vowels
SQL> SELECT fname FROM stud_marks
2 WHERE fname NOT LIKE'%a%'
3 AND fname NOT LIKE '%e%'
4 AND fname NOT LIKE '%i%'
5 AND fname NOT LIKE '%o%'
6 AND fname NOT LIKE '%u%';
no rows selected
-------------------------------------------------------------------------------------------------------------------
[23] Count the no. of students whose name starts with ‘S’
SQL> SELECT COUNT(fname) FROM stud_marks
2 WHERE fname LIKE 'S%';
COUNT(FNAME)
------------
3
-------------------------------------------------------------------------------------------------------------------
[24] Count the no. of students whose name ends with ‘kar’
SQL> SELECT COUNT(fname) FROM stud_marks
2 WHERE fname LIKE '%kar';
COUNT(FNAME)
------------
0
-------------------------------------------------------------------------------------------------------------------
[25] Display the names of student in following format Eg. If name is ‘pradip’ & surname is ‘suroo’ then display ‘praroo’
SQL> SELECT CONCAT(SUBSTR(fname,1,3),SUBSTR(lname,LENGTH(lname)-2,3))
2 FROM stud_marks;
CONCAT(SUBSTR(FNAME,1,3)
------------------------
Sanegi
Divtel
Santel
Jittel
Brivda
Navlot
Mukaza
Manhel
Ravati
Shahah
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[26] Display the rows which contains null values in m1, m2 or m3
SQL> SELECT * FROM stud_marks
2 WHERE m1 IS NULL OR m2 IS NULL or m3 IS NULL;
no rows selected
-------------------------------------------------------------------------------------------------------------------
[27] Display the rows which does not contain null values in name
SQL> SELECT * FROM stud_marks
2 WHERE fname IS NOT NULL;
NO FNAME LNAME M1 M2 M3 DOB
--- ------------- ------------ ----- ----- ----- ---------
1 Sandeep Negi 40 34 98 27-NOV-90
2 Divyesh Patel 88 43 56 22-APR-94
3 Sandip Patel 40 36 78 03-APR-95
4 Jitendra Patel 40 25 90 12-DEC-93
5 Brijesh Chavda 90 44 12 10-MAR-94
6 Navin Gelot 87 45 23 09-SEP-93
NO FNAME LNAME M1 M2 M3 DOB
--- ------------- ------------ ----- ----- ----- ---------
7 Mukesh Vaza 40 80 34 10-MAR-92
8 Manoj Gohel 40 88 40 28-NOV-92
9 Ravi Prajapati 40 89 23 13-JAN-92
10 Shalin Shah 40 40 67 14-JAN-91
10 rows selected.
-------------------------------------------------------------------------------------------------------------------
[28] Display the rows where name sounds like ‘Yash’ (use soundex function)
SQL> SELECT * FROM stud_marks
2 WHERE SOUNDEX(fname)=SOUNDEX('Yash');
no rows selected
-------------------------------------------------------------------------------------------------------------------
[29] Generate a random no. using date
SQL> SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
TO
--
38
-------------------------------------------------------------------------------------------------------------------
[30] Display the output in following form :- <Name was born on Day> Eg. Karan was born on Thursday.
SQL> SELECT fname||' was born on ',TO_CHAR(dob,'Day') FROM stud_marks;
FNAME||'WASBORNON'
------------------------------
TO_CHAR(DOB,'DAY')
------------------------------
Sandeep was born on
Tuesday
Divyesh was born on
Friday
Sandip was born on
Monday
FNAME||'WASBORNON'
------------------------------
TO_CHAR(DOB,'DAY')
------------------------------
Jitendra was born on
Sunday
Brijesh was born on
Thursday
Navin was born on
Thursday
FNAME||'WASBORNON'
------------------------------
TO_CHAR(DOB,'DAY')
------------------------------
Mukesh was born on
Tuesday
Manoj was born on
Saturday
Ravi was born on
Monday
Shalin was born on
Monday
10 rows selected.
///////////////////////////////////////////////////////////////////////////
[1] CREATING TABLE :
--------------------------------------------------------------------------------------------------------------------
[A] Table Name : Client_master :
SQL> create table Client_master
2 (Client_No varchar2(6) primary key check(Client_No like 'C%'),
3 Name varchar2(20) not null,
4 Address1 varchar2(30),
5 Address2 varchar2(30),
6 City varchar2(15),
7 Pincode number(8),
8 State varchar2(15),
9 Bal_Due number(10,2));
Table created.
--------------------------------------------------------------------------------------------------------------------
[B] Table Name : product_master :
SQL> create table Product_Master
2 (Product_No varchar2(6) primary key check(Product_No like 'P%'),
3 Description varchar2(15) not null,
4 Profit_Percent number(4,2) not null,
5 Unit_Measure varchar2(10) not null,
6 Qty_no_hand number(8) not null,
7 Recorder_lvl number(8) not null,
8 Sell_Price number(8,2) not null check(Sell_Price > 0),
9 Cost_Price number(8,2) not null check(Cost_Price > 0));
Table created.
-------------------------------------------------------------------------------------------------------------------
[C] Table Name : salesman_master :
SQL> create table Salesman_Master
2 (Salesman_no varchar2(6) primary key check(Salesman_no like 'S%'),
3 Salesman_name varchar2(20) not null,
4 Address1 varchar2(30) not null,
5 Address2 varchar2(30),
6 City varchar2(20),
7 Pincode varchar2(8),
8 State varchar2(20),
9 Sal_amt number(8,2) not null check(Sal_amt > 0),
10 Tgt_to_get number(6,2) not null check(tgt_to_get > 0),
11 Ytd_sales number(6,2) not null,
12 Remarks varchar2(60));
Table created.
-------------------------------------------------------------------------------------------------------------------
[D] Table Name : sale_order :
SQL> create table Sale_order
2 (Order_no varchar2(6) primary key check(order_no like 'O%'),
3 Order_date date,
4 Client_no varchar2(6),
5 Dely_Addr varchar2(25),
6 Salesman_no varchar2(6),
7 Dely_type char(1) default 'F' check(Dely_type like 'P' or Dely_type like 'F'),
8 Billed_yn char(1),
9 Dely_date Date,
10 Order_status varchar2(10) check(Order_status like 'In Process' or Order_status like 'Fulfilled'
or Order_status like 'Back Order' or Order_status like 'Cancelled'),
11 check(Dely_date>=Order_date),
12 foreign key(client_no) references Client_master(Client_no),
13 foreign key(salesman_no) references Salesman_master(salesman_no));
Table created.
-------------------------------------------------------------------------------------------------------------------
[E] Table Name : sale_order_details :
SQL> create table Sale_Order_Details
2 (Order_no varchar2(6),
3 Product_no varchar2(6),
4 Qty_ordered number(8),
5 Qty_disp number(8),
6 Product_rate number(10,2),
7 foreign key(Order_no) references sale_order(order_no),
8 foreign key(Product_no) references product_master(product_no),
9 primary key (order_no,product_no));
Table created.
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
[1.A] DESCRIBING TABLE :
-------------------------------------------------------------------------------------------------------------------
[A] Table Name : Client_master :
SQL> desc client_master;
Name Null? Type
----------------------- --------------- ----------------
CLIENT_NO NOT NULL VARCHAR2(6)
NAME NOT NULL VARCHAR2(20)
ADDRESS1 VARCHAR2(30)
ADDRESS2 VARCHAR2(30)
CITY VARCHAR2(15)
PINCODE NUMBER(8)
STATE VARCHAR2(15)
BAL_DUE NUMBER(10,2)
-------------------------------------------------------------------------------------------------------------------
[B] Table Name : product_master :
SQL> desc product_master;
Name Null? Type
------------------------- -------------- ------------------
PRODUCT_NO NOT NULL VARCHAR2(6)
DESCRIPTION NOT NULL VARCHAR2(15)
PROFIT_PERCENT NOT NULL NUMBER(4,2)
UNIT_MEASURE NOT NULL VARCHAR2(10)
QTY_NO_HAND NOT NULL NUMBER(8)
RECORDER_LVL NOT NULL NUMBER(8)
SELL_PRICE NOT NULL NUMBER(8,2)
COST_PRICE NOT NULL NUMBER(8,2)
-------------------------------------------------------------------------------------------------------------------
[C] Table Name : salesman_master :
SQL> desc salesman_master;
Name Null? Type
------------------------ ------------ ------------------
SALESMAN_NO NOT NULL VARCHAR2(6)
SALESMAN_NAME NOT NULL VARCHAR2(20)
ADDRESS1 NOT NULL VARCHAR2(30)
ADDRESS2 VARCHAR2(30)
CITY VARCHAR2(20)
PINCODE VARCHAR2(8)
STATE VARCHAR2(20)
SAL_AMT NOT NULL NUMBER(8,2)
TGT_TO_GET NOT NULL NUMBER(6,2)
YTD_SALES NOT NULL NUMBER(6,2)
REMARKS VARCHAR2(60)
--------------------------------------------------------------------------------------------------------------------
[D] Table Name : sale_order :
SQL> desc sale_order;
Name Null? Type
---------------------- ------------ ---------------------
ORDER_NO NOT NULL VARCHAR2(6)
ORDER_DATE DATE
CLIENT_NO VARCHAR2(6)
DELY_ADDR VARCHAR2(25)
SALESMAN_NO VARCHAR2(6)
DELY_TYPE CHAR(1)
BILLED_YN CHAR(1)
DELY_DATE DATE
ORDER_STATUS VARCHAR2(10)
--------------------------------------------------------------------------------------------------------------------
[E] Table Name : sale_order_details :
SQL> desc sale_order_details;
Name Null? Type
---------------------- ------------- -------------------
ORDER_NO NOT NULL VARCHAR2(6)
PRODUCT_NO NOT NULL VARCHAR2(6)
QTY_ORDERED NUMBER(8)
QTY_DISP NUMBER(8)
PRODUCT_RATE NUMBER(10,2)
--------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
--------------------------------------------------------------------------------------------------------------------
[2] INSERTING VALUES IN TABLE :
--------------------------------------------------------------------------------------------------------------------
[A] Data for client_master table :
SQL> insert into client_master(client_no,name,city,pincode,state,bal_due)
2 values('C00001','Ivan Bayross','Bombay',400054,'Maharastra',15000);
1 row created.
SQL> insert into client_master(client_no,name,city,pincode,state,bal_due)
2 values('C00002','Vandana Saitwal','Madras',780001,'Tamil Nadu',0);
1 row created.
SQL> insert into client_master(client_no,name,city,pincode,state,bal_due)
2 values('C00003','Pramada Jaguste','Bombay',400057,'Maharastra',5000);
1 row created.
SQL> insert into client_master(client_no,name,city,pincode,state,bal_due)
2 values('C00004','Basu Navindgi','Bombay',400056,'Maharastra',0);
1 row created.
SQL> insert into client_master(client_no,name,city,pincode,state,bal_due)
2 values('C00005','Ravi Sreedharan','Delhi',100001,'Delhi',2000);
1 row created.
SQL> insert into client_master(client_no,name,city,pincode,state,bal_due)
2 values('C00006','Rukmini','Bombay',400050,'Maharastra',0);
1 row created.
SQL> commit;
Commit complete.
--------------------------------------------------------------------------------------------------------------------
[B] Data for product_master table :
SQL> insert into product_master values('P00001','1.44 Floppies',5,'Piece',100,20,525,500);
1 row created.
SQL> insert into product_master values('P03453','monitors',6,'Piece',10,3,12000,11280);
1 row created.
SQL> insert into product_master values('P06734','mouse',5,'Piece',20,5,1050,1000);
1 row created.
SQL> insert into product_master values('P07865','1.22 Floppies',5,'Piece',100,20,525,500);
1 row created.
SQL> insert into product_master values('P07868','keyboards',2,'Piece',10,3,3150,3050);
1 row created.
SQL> insert into product_master values('P07885','CD Drive',2.5,'Piece',10,3,5250,5100);
1 row created.
SQL> insert into product_master values('P07965','540 HHD',4,'Piece',10,3,8400,8000);
1 row created.
SQL> insert into product_master values('P07975','1.44 Drive',5,'Piece',10,3,1050,1000);
1 row created.
SQL> insert into product_master values('P08865','1.22 Drive',5,'Piece',2,3,1050,1000);
1 row created.
SQL> commit;
Commit complete.
--------------------------------------------------------------------------------------------------------------------
[C] Data for salesman_master table :
SQL> insert into salesman_master values('S00001','Kiran','A/14','Worli','Bombay',400002,
'Maharastra',3000,100,50,'Good');
1 row created.
SQL> insert into salesman_master values('S00002','Manish','65','Nariman','Bombay',400001,
'Maharastra,3000,200,100,'Good');
1 row created.
SQL> insert into salesman_master values('S00003','Ravi','P-7','Bandra','Bombay',400032,
'Maharastra',3000,200,100,'Good');
1 row created.
SQL> insert into salesman_master values('S00004','Aashish','A/5','Juhu','Bombay',400044,
'Maharastra',3500,200,150,'Good');
1 row created.
SQL> commit;
Commit complete.
--------------------------------------------------------------------------------------------------------------------
[D] Data for sales_order table :
SQL> insert into sale_order values('O19001','12-Jan-96','C00001','F','N','S00001','20-Jan-96','In Process');
1 row created.
SQL> insert into sale_order values('O19002','25-Jan-96','C00002','P','N','S00002','27-Jan-96','Cancelled');
1 row created.
SQL> insert into sale_order values('O46865','18-Feb-96','C00003','F','Y','S00003','20-Feb-96','Fulfilled');
1 row created.
SQL> insert into sale_order values('O19003','03-Apr-96','C00001','F','Y','S00001','07-Apr-96','Fulfilled');
1 row created.
SQL> insert into sale_order values('O46866','20-May-96','C00004','P','N','S00002','22-May-96','Cancelled');
1 row created.
SQL> insert into sale_order values('O19008','24-May-96','C00005','F','N','S00004','26-May-96','In Process');
1 row created.
SQL> commit;
Commit complete.
--------------------------------------------------------------------------------------------------------------------
[E] Data for sale_order_details table :
SQL> insert into sale_order_details values('O19001','P00001',4,4,525);
1 row created.
SQL> insert into sale_order_details values('O19001','P07965',2,1,8400);
1 row created.
SQL> insert into sale_order_details values('O19001','P07885',2,1,5250);
1 row created.
SQL> insert into sale_order_details values('O19002','P00001',10,0,525);
1 row created.
SQL> insert into sale_order_details values('O46865','P07868',3,3,3150);
1 row created.
SQL> insert into sale_order_details values('O46865','P07885',3,1,5250);
1 row created.
SQL> insert into sale_order_details values('O46865','P00001',10,10,525);
1 row created.
SQL> insert into sale_order_details values('O46865','P03453',4,4,1050);
1 row created.
SQL> insert into sale_order_details values('O19003','P03453',2,2,1050);
1 row created.
SQL> insert into sale_order_details values('O19003','P06734',1,1,12000);
1 row created.
SQL> insert into sale_order_details values('O46866','P07965',1,0,8400);
1 row created.
SQL> insert into sale_order_details values('O46866','P07975',1,0,1050);
1 row created.
SQL> insert into sale_order_details values('O19008','P00001',10,5,525);
1 row created.
SQL> insert into sale_order_details values('O19008','P07975',5,3,1050);
1 row created.
SQL> commit;
Commit complete.
--------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
--------------------------------------------------------------------------------------------------------------------
[2.A] DISPLAYING DATA :
--------------------------------------------------------------------------------------------------------------------
[A] OUTPUT OF CLIENT_MASTER TABLE :
SQL> select * from client_master;
CLIENT_NO NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BAL_DUE
--------- ------------ -------- -------- --------- --------- ---------- ---------
C00001 Ivan Bayross Bombay 400054 Maharastra 15000
C00002 Vandana Saitwal Madras 780001 Tamil Nadu 0
C00003 Pramada Jaguste Bombay 400057 Maharastra 5000
C00004 Basu Navindgi Bombay 400056 Maharastra 0
C00005 Ravi Sreedharan Delhi 100001 Delhi 2000
C00006 Rukmini Bombay 400050 Maharastra 0
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
[B] OUTPUT OF PRODUCT_MASTER TABLE :
SQL> select * from product_master;
PRODUCT_NO DESCRIPTION PROFIT_PERCENT UNIT_MEASURE QTY_NO_HAND RECORDER_LVL SELL_PRICE COST_PRICE
---------- --------------- -------------- ------------ ----------- ------------ ---------- ----------
P00001 1.44 Floppies 5 Piece 100 20 525 500
P03453 Monitors 6 Piece 10 3 12000 11280
P06734 Mouse 5 Piece 20 5 1050 1000
P07865 1.22 Floppies 5 Piece 100 20 525 500
P07868 Keyboards 2 Piece 10 3 3150 3050
P07885 CD Drive 2.5 Piece 10 3 5250 5100
P07965 540 HHD 4 Piece 10 3 8400 8000
P07975 1.44 Drive 5 Piece 10 3 1050 1000
P08865 1.22 Drive 5 Piece 2 3 1050 1000
9 rows selected.
--------------------------------------------------------------------------------------------------------------------
[C] OUTPUT OF SALESMAN_MASTER TABLE :
SQL> select * from salesman_master;
SALESMAN_NO SALESMAN_NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE SAL_AMT TGT_TO_GET YTD_SALES REMARKS
----------- ------------- -------- -------- ------- ------- ---------- ------- ---------- --------- --------
S00001 Kiran A/14 Worli Bombay 400002 Maharastra 3000 100 50 Good
S00002 Manish 65 Nariman Bombay 400001 Maharastra 3000 200 100 Good
S00003 Rani P-7 Bandra Bombay 400032 Maharastra 3000 200 100 Good
S00004 Aashish A/5 Juhu Bombay 400044 Maharastra 3500 200 150 Good
--------------------------------------------------------------------------------------------------------------------
[D] OUTPUT OF SALES_ORDER TABLE :
SQL> select * from sales_order;
ORDER_NO ORDER_DATE CLIENT_NO DELY_ADDR SALESMAN_NO DELY_TYPE BILLED_YN DELY_DATE ORDER_STATUS
------- ---------- --------- --------- ----------- --------- --------- --------- -------------
O19001 12-JAN-96 C00001 S00001 F N 20-JAN-96 In Process
O19002 25-JAN-96 C00002 S00002 P N 27-JAN-96 Cancelled
O46865 18-FEB-96 C00003 S00003 F Y 20-FEB-96 Fulfilled
O46866 20-MAY-96 C00004 S00002 P N 22-MAY-96 Cancelled
O19008 24-MAY-96 C00005 S00004 F N 26-MAY-96 In Process
O19003 03-APR-96 C00001 S00001 F Y 07-APR-96 Fulfilled
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
[E] OUTPUT OF SALES_ORDER_DETAILS TABLE :
SQL> select * from sale_order_details;
ORDER_NO PRODUCT_NO QTY_ORDERED QTY_DISP PRODUCT_RATE
-------- ---------- ----------- -------- ------------
O19001 P00001 4 4 525
O19001 P07965 2 1 8400
O19001 P07885 2 1 5250
O19002 P00001 10 0 525
O46865 P07868 3 3 3150
O46865 P07885 3 1 5250
O46865 P00001 10 10 525
O46865 P03453 4 4 1050
O19003 P03453 2 2 1050
O19003 P06734 1 1 12000
O46866 P07965 1 0 8400
O46866 P07975 1 0 1050
O19008 P00001 10 5 525
O19008 P07975 5 3 1050
14 rows selected.
--------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
--------------------------------------------------------------------------------------------------------------------
[3] EXERCISES COMPUTATION ON TABLE DATA
--------------------------------------------------------------------------------------------------------------------
A) Find the name of all clients having ‘a’ as the second letter in their names.
SQL> select name from client_master where name like('_a%');
CLIENT_NO NAME ADDRESS1 ADDRESS2 CITY PINCODE STATE BAL_DUE
--------- ---------------- -------- -------- ------- ------- ----------- -------
C00002 Vandana Saitwal Madras 780001 Tamil Nadu 0
C00004 Basu Navindgi Bombay 400056 Maharastra 0
C00005 Ravi Sreedharan Delhi 100001 Delhi 2000
3 rows selected.
--------------------------------------------------------------------------------------------------------------------
B) Find out the clients who stay in a city whose second letter is ‘a’.
SQL> select name from client_master where city like('_a%');
NAME CITY
-------------------- ------------
Vandana Saitwal Madras
--------------------------------------------------------------------------------------------------------------------
C) Find the list of all client who stay in ‘Bombay’ or ‘Delhi’.
SQL> select name,city from client_master where city in('Bombay','Delhi');
NAME CITY
-------------------- ---------------
Ivan Bayross Bombay
Pramada Jaguste Bombay
Basu Navindgi Bombay
Ravi Sreedharan Delhi
Rukmini Bombay
--------------------------------------------------------------------------------------------------------------------
D) Print the list of client whose bal_due is greater then value 10000.
SQL> select name,bal_due from client_master;
NAME BAL_DUE
-------------------- -------
Ivan Bayross 15000
Vandana Saitwal 0
Pramada Jaguste 5000
Basu Navindgi 0
Ravi Sreedharan 2000
Rukmini 0
6 rows selected.
SQL> select name,bal_due from client_master where (bal_due>10000);
NAME BAL_DUE
---------------- -------
Ivan Bayross 15000
--------------------------------------------------------------------------------------------------------------------
E) Print the information from sales_oeder table for order placed in the month of January.
SQL> select * from sale_order where (to_char(order_date,'mon'))='jan';
ORDER_NO ORDER_DATE CLIENT_NO SALESMAN_NO DELY_TYPE BILLED_YN DELY_DATE ORDER_STATUS
-------- ---------- -------- ----------- --------- --------- --------- ------------
O19001 12-JAN-96 C00001 S00001 F N 20-JAN-96 In Process
O19002 25-JAN-96 C00002 S00002 P N 27-JAN-96 Cancelled
--------------------------------------------------------------------------------------------------------------------
F) Display the order information for client_no ‘C00001’ and ‘C00002’.
SQL> select * from sale_order where client_no in('C00001','C00002');
ORDER_NO ORDER_DATE CLIENT_NO SALESMAN_NO DELY_TYPE BILLED_YN DELY_DATE ORDER_STATUS
-------- ---------- --------- ----------- --------- --------- --------- ------------
O19001 12-JAN-96 C00001 S00001 F N 20-JAN-96 In Process
O19002 25-JAN-96 C00002 S00002 P N 27-JAN-96 Cancelled
O19003 03-APR-96 C00001 S00001 F Y 07-APR-96 Fulfilled
--------------------------------------------------------------------------------------------------------------------
G) Find products whose selling price is greater than 2000 and less than or equal to 5000.
SQL> select * from product_master where sell_price>2000 and sell_price<=5000;
PRODUCT_NO DESCRIPTION PROFIT_PERCENT UNIT_MEASURE QTY_NO_HAND RECORDER_LVL SELL_PRICE COST_PRICE
---------- ----------- -------------- ------------ ----------- ------------ ---------- ----------
P07868 Keyboards 2 Piece 10 3 3150 3050
--------------------------------------------------------------------------------------------------------------------
H) Find products whose selling price is more than 1500. Calculate a new selling price as, original selling
price * .15. Rename the new column in the above query as new_price.
SQL> select sell_price * .15 new_price from product_master where sell_price > 1500;
NEW_PRICE
---------
1800
472.5
787.5
1260
--------------------------------------------------------------------------------------------------------------------
I) List the names, city and state of clients who r not in the state of ‘Maharastra’.
SQL> select name,city,state from client_master where state not like('Maharastra');
NAME CITY STATE
-------------------- --------------- ---------------
Vandana Saitwal Madras Tamil Nadu
Ravi Sreedharan Delhi Delhi
--------------------------------------------------------------------------------------------------------------------
J) Count the total number of orders.
SQL> select count(order_no) Total_Order from sale_order;
TOTAL_ORDER
-----------
6
--------------------------------------------------------------------------------------------------------------------
K) Calculate the average price of all the products.
SQL> select sum(sell_price)/count(sell_price) average from product_master;
AVERAGE
---------
3666.6667
--------------------------------------------------------------------------------------------------------------------
L) Determine the maximum and minimum product prices. Rename the output as max_price and min_price respectively.
SQL> select max(sell_price) max_price, min(sell_price) min_price from product_master;
MAX_PRICE MIN_PRICE
--------- ---------
12000 525
--------------------------------------------------------------------------------------------------------------------
M) Count the number of products having price greater than or equal to 1500.
SQL> select count(sell_price) Total from product_master where sell_price>=1500;
TOTAL
---------
4
--------------------------------------------------------------------------------------------------------------------
N) Find all the products whose qty_on_hand is less than recorder level.
SQL> select Description from product_master where Qty_no_hand < Recorder_lvl;
DESCRIPTION
---------------
1.22 Drive
--------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************
[4] EXERCISE ON DATE MANIPULATION. :
**********************************************************************************************************
--------------------------------------------------------------------------------------------------------------------
A) Display the order number and day on which clients placed their order.
SQL> select o.order_no,n.name,to_char(Order_date,'DAY') Day from Sale_order o,Client_master n where
n.client_no=o.client_no;
ORDER_NO NAME DAY
-------- -------------------- ---------
O19001 Ivan Bayross THURSDAY
O19002 Vandana Saitwal WEDNESDAY
O46865 Pramada Jaguste SATURDAY
O46866 Basu Navindgi SUNDAY
O19008 Ravi Sreedharan THURSDAY
O19003 Ivan Bayross TUESDAY
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
B) Display the month (in alphabets) and date when the order must be delivered.
SQL> select order_no,to_char(dely_date,'Month') Month,to_char(dely_date,'DD') from sale_order;
ORDER_NO MONTH TO
------ --------- ---
O19001 January 20
O19002 January 27
O46865 February 20
O46866 May 22
O19008 May 26
O19003 April 07
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
C) Display the order_date in the format ‘DD-Month-yy’. e.g. 12-February-96.
SQL> select order_no,to_char(order_date,'DD-Month-YY') from sale_order;
ORDER_NO ORDER_DATE
-------- -----------------
O19001 12-January - 96
O19002 25-January - 96
O46865 18-February - 96
O46866 20-May - 96
O19008 24-May - 96
O19003 03-April - 96
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
D) Find the date, 15 days after today’s date.
SQL> select sysdate+15 from dual;
SYSDATE+1
---------
14-DEC-13
--------------------------------------------------------------------------------------------------------------------
E) Find the number of days elapsed between today’s date and the delivery date of the orders
placed by the clients.
SQL> select dely_date-sysdate Different from sale_order;
DIFFERENT
---------
30001.528
30008.528
30032.528
30124.528
30128.528
30079.528
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************
[5] EXERCISE ON USING HAVING AND GROUP BY CLAUSES. :
**********************************************************************************************************
--------------------------------------------------------------------------------------------------------------------
A) Print the description and total qty sold for each product.
SQL> select p.description,sum(s.qty_disp) from product_master p,sale_order_details s where
2 p.product_no=s.product_no group by p.description;
DESCRIPTION SUM(S.QTY_DISP)
--------------- ---------------
1.44 Drive 3
1.44 Floppies 19
540 HHD 1
CD Drive 2
Keyboards 3
Monitors 6
Mouse 1
7 rows selected.
--------------------------------------------------------------------------------------------------------------------
B) Find the value of each product sold.
SQL> select p.Description,sum(s.product_rate*s.qty_ordered) from product_master p,
2 sale_order_details s where p.product_no=s.product_no group by p.description;
DESCRIPTION SUM(S.PRODUCT_RATE*S.QTY_ORDERED)
--------------- ---------------------------------
1.44 Drive 6300
1.44 Floppies 17850
540 HHD 25200
CD Drive 26250
Keyboards 9450
Monitors 6300
Mouse 12000
7 rows selected.
--------------------------------------------------------------------------------------------------------------------
C) Calculate the average qty sold for each client that has a maximum order value of 15000.00.
SQL> select cm.name,avg(sod.qty_disp) from client_master cm,sale_order_details sod,sale_order sd
2 where sd.client_no=cm.client_no and sd.order_no=sod.order_no
3 group by cm.name having max(sod.qty_disp*sod.product_rate)=12000;
NAME AVG(SOD.QTY_DISP)
-------------------- -----------------
Ivan Bayross 1.8
--------------------------------------------------------------------------------------------------------------------
D) Find out the sum total of all the billed orders for the month of January.
SQL> select sum(sod.qty_disp*sod.product_rate) from sale_order_details sod,sale_order so
2 where to_char(so.order_date,'Mon')='Jan' and so.order_no=sod.order_no and so.billed_yn='y';
SUM(SOD.QTY_DISP*SOD.PRODUCT_RATE)
----------------------------------
--------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************
[6] EXERCISE ON JOINS AND CORRELATION. :
**********************************************************************************************************
--------------------------------------------------------------------------------------------------------------------
A) Find out the products, which have been sold to ‘Ivan Bayross’.
SQL> select p.description from product_master p, sale_order s, sale_order_details sd, client_master c
2 where p.product_no = sd.product_no and sd.order_no = s.order_no and sd.qty_disp > 0
3 and s.client_no = c.client_no and c.name = 'Ivan Bayross';
DESCRIPTION
---------------
1.44 Floppies
540 HHD
CD Drive
Monitors
Mouse
--------------------------------------------------------------------------------------------------------------------
B) Find out the products and their quantities that will have to be delivered in the current month.
SQL> select p.description,s.qty_ordered from product_master p,sale_order sd,sale_order_Details s
2 where p.product_no = s.product_no and sd.order_no = s.order_no and
3 to_char(sd.order_date,'Mon') = to_char(sysdate,'Mon');
no rows selected
--------------------------------------------------------------------------------------------------------------------
C) Find the product_no and description of constantly sold i.e. rapidly moving products.
SQL> select Distinct sd.product_no,p.description,sum(qty_disp)
2 from product_master p, sale_order_details sd where p.product_no=sd.product_no
3 group by sd.product_no, p.description having sum(qty_disp )= (select max(sum(qty_disp))
4 from sale_order_details group by product_no);
PRODUC DESCRIPTION SUM(QTY_DISP)
------ --------------- -------------
P00001 1.44 Floppies 19
--------------------------------------------------------------------------------------------------------------------
D) Find the name of clients who have purchase ‘CD Drive’.
SQL> select c.name from client_master c,product_master p,sale_order s,sale_order_details sd
2 where c.client_no = s.client_no and s.order_no = sd.order_no and p.product_no = sd.product_n
3 and p.description = 'CD Drive';
NAME
--------------------
Ivan Bayross
Pramada Jaguste
--------------------------------------------------------------------------------------------------------------------
E) List the product_no and order_no of customers having qty_ordered less than 5 from
the sales_order_details table for the product ‘1.44 Floppies’.
SQL> select p.product_no,s.order_no from product_master p,sale_order_details s
2 where s.product_no=p.product_no and s.qty_ordered<5 and p.description='1.44 Floppies';
PRODUC ORDER_
------ ------
P00001 O19001
--------------------------------------------------------------------------------------------------------------------
F) Find the products and their quantities for the orders placed by ‘lvan Bayross’ and ‘Vandana Saitwal’.
SQL> select p.description,s.qty_ordered from product_master p,sale_order_details s,client_master c,
2 sale_order sd where p.product_no = s.product_no and sd.order_no = s.order_no and
3 sd.client_no = c.client_no and c.name in ('Vandana Saitwal','lvan Bayross');
DESCRIPTION QTY_ORDERED
-------------- -----------
1.44 Floppies 10
--------------------------------------------------------------------------------------------------------------------
G) Find the products and their quantities for the orders placed by client_no ‘C00001’ and ‘C00002’.
SQL> select p.description,s.qty_ordered from product_master p,sale_order_details s,sale_order sd
2 where p.product_no = s.product_no and sd.order_no = s.order_no and
3 sd.client_no in('C00001','C00002');
DESCRIPTION QTY_ORDERED
--------------- -----------
1.44 Floppies 4
CD Drive 2
540 HHD 2
1.44 Floppies 10
Monitors 2
Mouse 1
6 rows selected.
--------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************
[7] EXERCISE ON SUB-QUERIES. :
**********************************************************************************************************
--------------------------------------------------------------------------------------------------------------------
A) Find the product_no and description of non-moving products i.e. products not being sold.
SQL> select description,product_no from product_master
2 where product_no not in(select product_no from sale_order_details);
DESCRIPTION PRODUC
--------------- ------
1.22 Floppies P07865
1.22 Drive P08865
--------------------------------------------------------------------------------------------------------------------
B) Find the customer name, address1, address2, city and pin code for the client who
has placed order no ‘O19001’.
SQL> select name,address1,address2,city,pincode from client_master
2 where client_no in(select client_no from sale_order where order_no='O19001');
NAME ADDRESS1 ADDRESS2 CITY PINCODE
-------------------- ------------------------------ ----------------------- ------------ -------
Ivan Bayross Bombay 400054
--------------------------------------------------------------------------------------------------------------------
C) Find the client names who have placed orders before the month of May’96.
SQL> select name from client_master where client_no in (select client_no from sale_order
2 where to_char(order_date,'mon-yy')<'may-96');
NAME
--------------------
Ivan Bayross
Vandana Saitwal
Pramada Jaguste
--------------------------------------------------------------------------------------------------------------------
D) Find out if the product ‘1.44 Drive’ has been ordered by any client and
print the clint_no, name to whom it was sold.
SQL> select client_no,name from client_master where client_no in
2 (select client_no from sale_order where order_no in
3 (select order_no from sale_order_details where product_no in
4 (select product_no from product_master where description = '1.44 Drive')));
CLIENT NAME
------ --------------------
C00004 Basu Navindgi
C00005 Ravi Sreedharan
--------------------------------------------------------------------------------------------------------------------
E) Find the names of clients who have placed orders worth Rs.10000 or more.
SQL> select name from client_master where client_no in
2 (select client_no from sale_order where order_no in
3 (select order_no from sale_order_details where qty_ordered*product_rate > =10000));
NAME
--------------------
Ivan Bayross
Pramada Jaguste
--------------------------------------------------------------------------------------------------------------------
**********************************************************************************************************
[8]EXERCISE ON CONSTRUCTING SENTENCES WITH DATA. :
**********************************************************************************************************
--------------------------------------------------------------------------------------------------------------------
A) Print information from product_master, sales_order_detail tables in the following format
for all the records :
{Description} worth Rs. {total sales for the product} was sold.
SQL> select p.description || ' worth Rs. '|| sum(s.qty_disp*s.product_rate) || ' was sold '
2 from product_master p,sale_order_details s group by p.description;
P.DESCRIPTION||'WORTHRS.'||SUM(S.QTY_DISP*S.PRODUCT_RATE)||'WASSOLD'
----------------------------------------------------------------------------
1.22 Drive worth Rs. 59775 was sold
1.22 Floppies worth Rs. 59775 was sold
1.44 Drive worth Rs. 59775 was sold
1.44 Floppies worth Rs. 59775 was sold
540 HHD worth Rs. 59775 was sold
CD Drive worth Rs. 59775 was sold
Keyboards worth Rs. 59775 was sold
Monitors worth Rs. 59775 was sold
Mouse worth Rs. 59775 was sold
9 rows selected.
--------------------------------------------------------------------------------------------------------------------
B) Print information from product_master, sales_order_detail tables in the following format for all the records:
{Description} worth Rs. {total sales for the product} was ordered in the month of {order_date in month format}.
SQL> select p.description || ' worth Rs. ' || sum(s.qty_disp*s.product_rate) ||
2 ' was ordered in the month of ' || to_char(sd.order_date,'month')
3 from product_master p,sale_order_details s,sale_order sd
4 where p.product_no=s.product_no and sd.order_no=s.order_no
5 group by p.description,to_char(sd.order_date,'month');
P.DESCRIPTION||'WORTHRS.'||SUM(S.QTY_DISP*S.PRODUCT_RATE)||'WASORDEREDINTHEMONTHOF'||TO_CHAR(SD.ORDE
----------------------------------------------------------------------------------------------------
1.44 Drive worth Rs. 3150 was ordered in the month of may
1.44 Floppies worth Rs. 5250 was ordered in the month of february
1.44 Floppies worth Rs. 2100 was ordered in the month of january
1.44 Floppies worth Rs. 2625 was ordered in the month of may
540 HHD worth Rs. 8400 was ordered in the month of january
540 HHD worth Rs. 0 was ordered in the month of may
CD Drive worth Rs. 5250 was ordered in the month of february
CD Drive worth Rs. 5250 was ordered in the month of january
Keyboards worth Rs. 9450 was ordered in the month of february
Monitors worth Rs. 2100 was ordered in the month of april
Monitors worth Rs. 4200 was ordered in the month of february
Mouse worth Rs. 12000 was ordered in the month of april
12 rows selected.
--------------------------------------------------------------------------------------------------------------------
C) Print information from client_master, product_master, sales_order tables in the following format for
all the records:
{cust_name} has placed order {order_no} on {order_date}.
SQL> select c.name || ' has placed order ' || s.order_no || ' on ' || s.order_date
2 from client_master c,sale_order s where c.client_no=s.client_no;
C.NAME||'HASPLACEDORDER'||S.ORDER_NO||'ON'||S.ORDER_DATE
---------------------------------------------------------
Ivan Bayross has placed order O19001 on 12-JAN-96
Vandana Saitwal has placed order O19002 on 25-JAN-96
Pramada Jaguste has placed order O46865 on 18-FEB-96
Basu Navindgi has placed order O46866 on 20-MAY-96
Ravi Sreedharan has placed order O19008 on 24-MAY-96
Ivan Bayross has placed order O19003 on 03-APR-96
6 rows selected.
////////////////////////////////////////////////////////////////////////
: CREATE THE FOLLOWING TABLES (NO CONSTRAINTS) :
-------------------------------------------------------------------------------------------------------------------
[1] CREATE THE FOLLOWING TABLES
-------------------------------------------------------------------------------------------------------------------
Deposite Table :
SQL> create table Deposit
2 (ACTNO varchar(5),
3 CNAME varchar(18),
4 BNAME varchar(18),
5 AMOUNT number(8,2),
6 ADATE date);
Table created.
-------------------------------------------------------------------------------------------------------------------
Borrow Table:
SQL> create table Borrow
2 (LOANNO varchar(5),
3 CNAME varchar(18),
4 BNAME varchar(18),
5 AMOUNT number(8,2));
Table created.
-------------------------------------------------------------------------------------------------------------------
Customer Table :
SQL> create table Customer
2 (CNAME varchar(18),
3 CITY varchar(18));
Table created.
-------------------------------------------------------------------------------------------------------------------
Branch Table :
SQL> create table Branch
2 (BNAME varchar(18),
3 CITY varchar(18));
Table created.
-------------------------------------------------------------------------------------------------------------------
[2] INSERTING DATA IN TABLE :
-------------------------------------------------------------------------------------------------------------------
Deposite tABLE :
SQL>insert into Deposit values(100,'ANIL','VRCE',1000,'1-MAR-95');
1 row created.
SQL>insert into Deposit values(101,'SUNIL','AJNI',5000,'4-JAN-98');
1 row created.
SQL>insert into Deposit values(102,'MEHUL','KAROLBAGH',3500,'17-NOV-95');
1 row created.
SQL>insert into Deposit values(104,'MADHURI','CHANDNI',1200,'17-DEC-95');
1 row created.
SQL>insert into Deposit values(105,'PRAMOD','MGROAD',3000,'27-MAR-96');
1 row created.
SQL>insert into Deposit values(106,'SANDIP','ANDHERI',2000,'31-MAR-96');
1 row created.
SQL>insert into Deposit values(107,'SHIVANI','VIRAR',1000,'5-SEP-95');
1 row created.
SQL>insert into Deposit values(108,'KRANTI','NEHRUPLACE',5000,'2-JUL-95');
1 row created.
SQL>insert into Deposit values(109,'NAREN','POWAI',7000,'10-AUG-95');
1 row created.
-------------------------------------------------------------------------------------------------------------------
Customer tABLE :
SQL>insert into customer values('ANIL','CALCUTTA');
1 row created.
SQL>insert into customer values('SUNIL','DELHI');
1 row created.
SQL>insert into customer values('MEHUL','BARODA');
1 row created.
SQL>insert into customer values('MANDAR','PATNA');
1 row created.
SQL>insert into customer values('MADHURI','NAGPUR');
1 row created.
SQL>insert into customer values('PRAMOD','NAGPUR');
1 row created.
SQL>insert into customer values('SANDIP','SURAT');
1 row created.
SQL>insert into customer values('SHIVANI','BOMBAY');
1 row created.
SQL>insert into customer values('KRANTI','BOMBAY');
1 row created.
SQL>insert into customer values('NAREN','BOMBAY');
1 row created.
-------------------------------------------------------------------------------------------------------------------
Branch tABLE :
SQL>insert into Branch values('VRCE ','NAGPUR');
1 row created.
SQL>insert into Branch values('AJNI','NAGPUR');
1 row created.
SQL>insert into Branch values('KAROLBAGH','DELHI');
1 row created.
SQL>insert into Branch values('CHANDNI','DELHI');
1 row created.
SQL>insert into Branch values('DHARAMPTEH','NAGPUR');
1 row created.
SQL>insert into Branch values('MGROAD','BANGALORE');
1 row created.
SQL>insert into Branch values('MGROAD','BANGALORE');
1 row created.
SQL>insert into Branch values('ANDHERI','BOMBAY');
1 row created.
SQL>insert into Branch values('VIRAR','BOMBAY');
1 row created.
SQL>insert into Branch values('NEHRUPLACE','DELHI');
1 row created.
SQL>insert into Branch values('POWAI','BOMBAY');
1 row created.
-------------------------------------------------------------------------------------------------------------------
Borrow tABLE :
SQL>insert into Borrow values('201','ANIL','VRCE','1000');
1 row created.
SQL>insert into Borrow values('206','MEHUL','AJNI','5000');
1 row created.
SQL>insert into Borrow values('311','SUNIL','DHARAMPET','3000');
1 row created.
SQL>insert into Borrow values('321','MADHURI','ANDHERI','2000');
1 row created.
SQL>insert into Borrow values('375','PRAMOD','VIRAR','8000');
1 row created.
SQL>insert into Borrow values('481','KRANTI','NEHRUPLACE','3000');
1 row created.
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
[3] QUERIES :
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
[A] PART - I :
-------------------------------------------------------------------------------------------------------------------
1. Give all the details of customer Anil.
SQL> select d.actno,d.cname,d.bname,d.amount,d.adate,b.loanno,b.amount from deposit d,borrow b
2 where d.cname='ANIL' and b.cname='ANIL';
ACTNO CNAME BNAME AMOUNT ADATE LOANN AMOUNT
----- ----- ----- ------ --------- ----- ------
100 ANIL VRCE 1000 01-MAR-95 201 1000
-------------------------------------------------------------------------------------------------------------------
2. Give the name of customer having living city = Bombay and branch city = nagpur.
SQL> select c.cname,c.city,d.cname, d.bname, b.bname, b.city from customer c,deposit d,branch b
2 where c.cname=d.cname and b.bname=d.bname and c.city='BOMBAY' and b.city='NAGPUR';
no rows selected
-------------------------------------------------------------------------------------------------------------------
3. Give name of customer having same living city as their branch city.
SQL> select c.cname,c.city,d.bname from customer c,deposit d,branch b
2 where c.cname=d.cname and d.bname=b.bname and c.city=b.city;
CNAME CITY BNAME
-------- ---------- -----------
NAREN BOMBAY POWAI
SHIVANI BOMBAY VIRAR
-------------------------------------------------------------------------------------------------------------------
4. Give the name of customer who are borrowers and depositers and having living city = Nagpur.
SQL> select d.cname,c.city,b.cname from customer c,deposit d,borrow b where d.cname=b.cname and d.cn
ame=c.cname and c.city='Nagpur';
CNAME CITY CNAME
------------------ ------------------ ------------------
Madhuri Nagpur Madhuri
Pramod Nagpur Pramod
SQL>
-------------------------------------------------------------------------------------------------------------------
5. Give the name of customers who are depositors having same branch city of Sunil.
SQL> select c.cname,b.bname from deposit d,customer c,branch b where d.bname=b.bname and c.cname=d.c
2 name and b.city=(select b.city from branch b,deposit d ,customer c
3 where c.cname=d.cname and d.bname=b.bname and c.cname='Sunil');
CNAME BNAME
------------------ ------------------
Sunil Ajni
SQL>
-------------------------------------------------------------------------------------------------------------------
6. Give name of depositors having same living city as Anil and having deposit amount greater than 2000.
SQL> select d.cname,d.amount from deposit d,customer c
2 where d.cname=c.cname and c.city=(select city from customer where c.cname='anil') and d.amount>2000;
no rows selected
-------------------------------------------------------------------------------------------------------------------
7. Give name of depositors having same branch as branch of Sunil.
SQL> select cname,bname from deposit where bname=(select d.bname from deposit d,customer c
2 where c.cname=d.cname and c.cname='Sunil');
CNAME BNAME
------------------ ------------------
Sunil Ajni
-------------------------------------------------------------------------------------------------------------------
8. Give name of borrowers having loan amount greater than amount of Parmod.
SQL> select cname from borrow where amount>(select amount from borrow where cname='Parmod');
no rows selected
-------------------------------------------------------------------------------------------------------------------
9. Give name of Customers living in same city where branch of depositor sunil is located.
SQL> select cname from customer where city=(select b.city from branch b,deposit d
2 where d.bname=b.bname and d.cname='Sunil');
CNAME
------------------
Madhuri
Pramod
-------------------------------------------------------------------------------------------------------------------
10. Give name of borrowers having deposit amount greater than 1000 and loan amount greater than 2000.
SQL> select b.cname from borrow b,deposit d where d.cname=b.cname and d.amount>1000 and b.amount>2000;
CNAME
------------------
Kranti
Mehul
Pramod
Sunil
-------------------------------------------------------------------------------------------------------------------
11. Give loan no., loan amount, account no. deposit amount of customers living in city Nagpur.
SQL> select d.cname,d.actno,d.amount "Deposit",b.loanno,b.amount "Loan" from deposit d,borrow b,customer c
2 where d.cname=b.cname and d.cname=c.cname and c.city='Nagpur';
CNAME ACTNO Deposit LOANN Loan
------------------ ----- --------- ----- ---------
Madhuri 104 1200 321 2000
Pramod 105 3000 375 8000
-------------------------------------------------------------------------------------------------------------------
12. Give loan no., loan amount, account no., deposit amount of customers having branch located at Bombay.
SQL> select d.cname,d.actno,d.amount "Deposit",b.loanno,b.amount "Loan" from deposit d,borrow b,branch b1
2 where d.cname=b.cname and d.bname=b1.bname and b1.city='bombay';
no rows selected
-------------------------------------------------------------------------------------------------------------------
13. Give loan no, loan amount, account no., deposit amount, branch name, branch city and living city of Pramod.
SQL> select d.cname,c.city,d.actno,d.amount,b.loanno,b.amount,b1.bname,b1.city from deposit d,
2 borrow b,branch b1,customer c where c.cname=d.cname and d.cname=b.cname
3 and d.bname=b1.bname and c.cname='Pramod';
CNAME CITY ACTNO AMOUNT LOANN AMOUNT BNAME CITY
------------------ ------------------ ----- --------- ----- ------ ------- ----------
Pramod Nagpur 105 3000 375 8000 Mgroad Banglore
-------------------------------------------------------------------------------------------------------------------
14. Give deposit details and Loan details of Customer in same city where Pramod is living.
SQL> select d.*,b.* from deposit d,borrow b,customer c where d.cname=b.cname and
2 c.cname=d.cname and c.city=(select city from customer where cname='Pramod');
ACTNO CNAME BNAME AMOUNT ADATE LOANN AMOUNT
----- -------- ----------- ------ --------- ----- ------
104 MADHURI CHANDNI 1200 17-DEC-95 321 2000
105 PRAMOD MGROAD 3000 27-MAR-96 375 8000
-------------------------------------------------------------------------------------------------------------------
15. Give Name of Depositors having same Branch city as Sunil and having same Living city as Anil.
SQL> select d.cname from deposit d,branch b,customer c where d.bname=b.bname and c.cname=d.cname
2 and b.city=(select b.city from customer c,branch b,deposit d where
3 c.cname=d.cname and d.bname=b.bname and c.cname='Sunil')
4 and c.city=(select city from customer where cname='Anil');
CNAME
------------------
Anil
-------------------------------------------------------------------------------------------------------------------
16. Give name of depositors having amount greater than 5000 and having same living city as Pramod.
SQL> select d.cname from deposit d,customer c where d.cname=c.cname and amount>5000 and
2 c.city=(select city from customer where cname='Pramod');
no rows selected
-------------------------------------------------------------------------------------------------------------------
17. Give city of customer having branch city same as Pramod.
SQL> select c.city from customer c,branch b,deposit d where c.cname=d.cname and d.bname=b.bname
2 and b.city=(select b.city from branch b,customer c,deposit d where d.cname=c.cname
3 and d.bname=b.bname and c.cname='Pramod');
CITY
------------------
Nagpur
-------------------------------------------------------------------------------------------------------------------
18. Give branch city and living city of Pramod
SQL> select c.cname "Name",b.city "Branch city",c.city "Living city" from customer c,branch b,deposit d
2 where c.cname=d.cname and d.bname=b.bname and c.cname='Pramod';
Name Branch city Living city
------------------ ------------------ ------------------
Pramod Banglore Nagpur
-------------------------------------------------------------------------------------------------------------------
19. Give branch city of sunil and branch city of Anil.
SQL> select c.cname "Name",b.city "Branch city" from branch b,customer c,deposit d
2 where c.cname=d.cname and d.bname=b.bname and c.cname in('Sunil','Anil');
Name Branch city
------------------ ------------------
Sunil Nagpur
Anil NAgpur
-------------------------------------------------------------------------------------------------------------------
20. Give living city of Ashok and Living city of Ajay.
SQL> select city from customer where cname in('Ashok','Ajay');
no rows selected
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
[B] PART - II :
-------------------------------------------------------------------------------------------------------------------
1. List all the customers who are depositors but are not borrowers.
SQL> select cname from deposit where cname not in(select cname from borrow);
CNAME
------------------
Sandip
Shivani
Naren
-------------------------------------------------------------------------------------------------------------------
2. List all the customers who are depositors and borrowers.
SQL> select d.cname,d.amount "Deposit",b.amount "Loan" from deposit d,borrow b where b.cname=d.cname;
CNAME Deposit Loan
------------------ --------- ---------
Anil 1000 1000
Kranti 5000 3000
Madhuri 1200 2000
Mehul 3500 5000
Pramod 3000 8000
Sunil 5000 3000
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
3. List all the customers with their amount who are borrowers or depositors and living in city Nagpur.
SQL> select d.cname,d.amount "Deposit",b.cname,b.amount "Loan" from deposit d,borrow b,customer c
2 where c.cname=d.cname and c.cname=b.cname and c.city='Nagpur';
CNAME Deposit CNAME Loan
------------------ --------- ------------------ ---------
Madhuri 1200 Madhuri 2000
Pramod 3000 Pramod 8000
-------------------------------------------------------------------------------------------------------------------
4. List all the depositors having deposit in all the branches were Sunil is having branches.
SQL> select * from deposit where bname=(select b.bname from branch b,customer c,deposit d
2 where c.cname=d.cname and d.bname=b.bname and c.cname='Sunil');
ACTNO CNAME BNAME AMOUNT ADATE
----- ------------------ ------------------ --------- ---------
101 Sunil Ajni 5000 04-JAN-98
-------------------------------------------------------------------------------------------------------------------
5. List all the customers living in city Nagpur and having branch city Bombay or delhi.
SQL> select c.cname from customer c,deposit d,branch b where c.cname=d.cname and d.bname=b.bname
2 and c.city='Nagpur' and b.city in('Bombay','Delhi');
CNAME
------------------
Madhuri
-------------------------------------------------------------------------------------------------------------------
6. List all the depositors living in city Nagpur.
SQL> select d.cname from deposit d,customer c where c.cname=d.cname and c.city='Nagpur';
CNAME
------------------
Madhuri
Pramod
-------------------------------------------------------------------------------------------------------------------
7. List all the depositors living in city nagpur and having branch city Bombay.
SQL> select d.cname from deposit d,customer c,branch b where c.cname=d.cname and d.bname=b.bname
2 and c.city='Nagpur' and b.city='Bombay';
no rows selected
-------------------------------------------------------------------------------------------------------------------
8. List the branch cities of anil and sunil.
SQL> select distinct b.city from branch b,customer c,deposit d where c.cname=d.cname
2 and d.bname=b.bname and c.cname in('Anil','Sunil');
CITY
------------------
NAgpur
Nagpur
-------------------------------------------------------------------------------------------------------------------
9. list the customers having deposit greater than 1000 and loan less than 10000.
SQL> select cname from deposit where amount>1000 and amount<10000;
CNAME
------------------
Sunil
Mehul
Madhuri
Pramod
Sandip
Kranti
Naren
7 rows selected.
SQL> select cname from deposit where amount>=1000 and amount<10000;
CNAME
------------------
Anil
Sunil
Mehul
Madhuri
Pramod
Sandip
Shivani
Kranti
Naren
9 rows selected.
-------------------------------------------------------------------------------------------------------------------
10. List the borrowers having branch city same as sunil.
SQL> select b.cname from borrow b,branch b1 where b.bname=b1.bname and
2 b1.city=(select b1.city from borrow b,branch b1 where b.bname=b1.bname and b.cname='sunil');
CNAME
------------------
Mehul
Sunil
Anil
-------------------------------------------------------------------------------------------------------------------
11. List the cities of depositors having branch VRCE.
SQL> select c.* from customer c,deposit d where c.cname=d.cname and d.bname='Vrce';
CNAME CITY
------------------ ------------------
Anil Calcutta
-------------------------------------------------------------------------------------------------------------------
12. List the depositors having same living city as sunil, and same branch city as anil.
SQL> select d.cname from deposit d,branch b,customer c where d.cname=c.cname and d.bname=b.bname
2 and c.city=(select city from customer where cname='Sunil')
3 and b.city=(select b.city from branch b,customer c,deposit d
4 where d.cname=c.cname and d.bname=b.bname and c.cname='Anil');
CNAME
------------------
Sunil
-------------------------------------------------------------------------------------------------------------------
13. List the depositors having amount less than 1000 and living in same city as anil.
SQL> select d.cname from deposit d,customer c
2 where d.amount<1000 and c.city=(select city from customer where cname='Sunil');
no rows selected
-------------------------------------------------------------------------------------------------------------------
14. List all the customers who are depositors and borrowers and living in same city as anil.
SQL> select d.cname from deposit d,borrow b,customer c where d.cname=b.cname
2 and c.cname=d.cname and c.city=(select city from customer where cname='Anil');
CNAME
------------------
Anil
-------------------------------------------------------------------------------------------------------------------
15. List all the cities where branches of Anil and Sunil are located.
SQL> select distinct b.city from branch b
2 where b.city in(select distinct b.city from customer c,deposit d,branch b
3 where d.bname=b.bname and c.cname=d.cname and c.cname in('Anil','Sunil'));
CITY
------------------
NAgpur
Nagpur
-------------------------------------------------------------------------------------------------------------------
16. List all the customer name, amount of depositor living in city where anil or sunil is living.
SQL> select d.cname,d.amount from deposit d,customer c where d.cname=c.cname
2 and c.city in((select city from customer where cname='Sunil' OR cname='Anil'));
CNAME AMOUNT
------------------ ---------
Anil 1000
Sunil 5000
-------------------------------------------------------------------------------------------------------------------
17. List amount of depositors living in city where anil is living.
SQL> select d.cname,d.amount from deposit d,customer c where c.cname=d.cname
2 and c.city=(select city from customer where cname='Anil');
CNAME AMOUNT
------------------ ---------
Anil 1000
-------------------------------------------------------------------------------------------------------------------
18. List the cities which are branch city of anil or living city of sunil.
SQL> select c.city from customer c where c.cname='Sunil' UNION select b.city
2 from branch b,customer c,deposit d where d.cname=c.cname and d.bname=b.bname and c.cname='Anil';
CITY
------------------
Delhi
NAgpur
-------------------------------------------------------------------------------------------------------------------
19. List the customer who are borrowers or depositors and having living city nagpur and branch city same as sunil.
SQL> select d.cname,b.cname from deposit d,borrow b,customer c,branch b1
2 where d.bname=b1.bname and c.cname=d.cname and d.cname=b.cname and c.city='Nagpur'
3 and b1.city=(select city from customer where cname='Sunil');
CNAME CNAME
------------------ ------------------
Madhuri Madhuri
-------------------------------------------------------------------------------------------------------------------
20. List the customer who are borrowers and depositors and having same branch city as anil.
SQL> select d.cname,b.cname from deposit d,borrow b,branch b1
2 where d.cname=b.cname and d.bname=b1.bname and
3 b1.city=(select b.city from deposit d,branch b where d.bname=b.bname and d.cname='Anil');
CNAME CNAME
------------------ ------------------
Anil Anil
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
[C] PART - III :
-------------------------------------------------------------------------------------------------------------------
1. List total deposit of customers living in city Nagpur.
SQL> select sum(d.amount) as total from deposit d,customer c where c.cname=d.cname and c.city='Nagpur';
TOTAL
---------
4200
-------------------------------------------------------------------------------------------------------------------
2. List total deposit of customers having branch city delhi.
SQL> select sum(d.amount) as total from deposit d,branch b where d.bname=b.bname and b.city='Delhi';
TOTAL
---------
9700
-------------------------------------------------------------------------------------------------------------------
3. List total deposit of customers living in same city where sunil is living.
SQL> select sum(d.amount) as total from deposit d,customer c
2 where c.cname=d.cname and c.city=(select city from customer where cname='Sunil');
TOTAL
---------
5000
-------------------------------------------------------------------------------------------------------------------
4. Give city name and citywise deposit. (group by city)
SQL> select b.city,sum(d.amount) as deposit from deposit d,
2 branch b where d.bname=b.bname group by(b.city);
CITY DEPOSIT
------------------ ---------
Banglore 3000
Bombay 10000
Delhi 9700
NAgpur 1000
Nagpur 5000
-------------------------------------------------------------------------------------------------------------------
5. Give citywise name and branchwise deposit.
SQL> select b.city,b.bname,sum(d.amount) as deposit from deposit d,branch b
2 where d.bname=b.bname group by b.bname,b.city;
CITY BNAME DEPOSIT
------------------ ------------------ ---------
Nagpur Ajni 5000
Bombay Andheri 2000
Delhi Chandni 1200
Delhi Karolbagh 3500
Banglore Mgroad 3000
Delhi Nehruplace 5000
Bombay Powai 7000
Bombay Virar 1000
NAgpur Vrce 1000
9 rows selected.
-------------------------------------------------------------------------------------------------------------------
6. Give the branchwise deposit of customer after account date 1-jan-96.
SQL> select b.bname,sum(d.amount) as deposit from deposit d,branch b
2 where d.bname=b.bname and d.adate>'1-jan-96' group by (b.bname);
BNAME DEPOSIT
------------------ ---------
Ajni 5000
Andheri 2000
Mgroad 3000
-------------------------------------------------------------------------------------------------------------------
7. Give branchwise loan of customer living in Nagpur.
SQL> select b.cname,sum(b.amount) from borrow b,customer c
2 where b.cname=c.cname and c.city='Nagpur' group by (b.cname);
CNAME SUM(B.AMOUNT)
------------------ -------------
Madhuri 2000
Pramod 8000
-------------------------------------------------------------------------------------------------------------------
8. Give living citywise loan of borrowers.
SQL> select c.city,sum(b.amount) from customer c,borrow b where c.cname=b.cname group by(c.city);
CITY SUM(B.AMOUNT)
------------------ -------------
Baroda 5000
Bombay 3000
Calcutta 1000
Delhi 3000
Nagpur 10000
-------------------------------------------------------------------------------------------------------------------
9. Give Number of customers who are depositors and borrowers.
SQL> select count(d.cname) from deposit d,borrow b,customer c where c.cname=d.cname and d.cname=b.cname;
COUNT(D.CNAME)
--------------
6
-------------------------------------------------------------------------------------------------------------------
10. Count total number of branch cities.
SQL> select count(distinct city) from branch;
COUNT(DISTINCTCITY)
-------------------
5
-------------------------------------------------------------------------------------------------------------------
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-------------------------------------------------------------------------------------------------------------------
[C] PART - IV :
-------------------------------------------------------------------------------------------------------------------
1. Delete depositors of branches having number of customers between 1 and 3.
SQL> select count(*),b.bname from branch b,deposit d where d.bname=b.bname group by b.bname;
COUNT(*) BNAME
--------- ------------------
1 Ajni
1 Andheri
1 Chandni
1 Karolbagh
1 Mgroad
1 Nehruplace
1 Powai
1 Virar
1 Vrce
9 rows selected.
-------------------------------------------------------------------------------------------------------------------
2. Delete branches having average deposit less than 5000.
SQL> select avg(d.amount),b.bname from branch b,deposit d
2 where d.bname=b.bname group by b.bname HAVING avg(d.amount)<5000;
AVG(D.AMOUNT) BNAME
------------- ------------------
2000 Andheri
1200 Chandni
3500 Karolbagh
3000 Mgroad
1000 Virar
1000 Vrce
6 rows selected.
-------------------------------------------------------------------------------------------------------------------
3. Delete branches having maximum loan more than 5000.
SQL> select max(b.amount),b1.bname from borrow b,branch b1
2 where b.bname=b1.bname group by b.amount, b1.bname;
MAX(B.AMOUNT) BNAME
------------- ------------------
5000 Ajni
2000 Andheri
3000 Nehruplace
8000 Virar
1000 Vrce
SQL> select max(b.amount),b1.bname from borrow b,branch b1
2 where b.bname=b1.bname group by b.amount, b1.bname HAVING max(b.amount)>5000;
MAX(B.AMOUNT) BNAME
------------- ------------------
8000 Virar
-------------------------------------------------------------------------------------------------------------------
4. Delete branches having deposit from nagpur.
SQL> select b.bname from branch b,deposit d where d.bname=b.bname and b.city='Nagpur';
BNAME
------------------
Ajni
-------------------------------------------------------------------------------------------------------------------
5. Delete deposit of anil and sunil if both are having branch virar.
SQL> select d.cname,d.bname from deposit d,branch b
2 where d.cname='Anil' or d.cname='Sunil' group by d.cname,d.bname having d.bname='Virar';
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
6. Delete deposit of anil and sunil if both are having living city nagpur.
SQL> select * from deposit d,customer c where c.cname in('Anil','Sunil') and c.city='Nagpur';
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
7. Delete deposit of anil and sunil if both are having same living city.
SQL> select * from deposit d,customer c where c.cname=d.cname
2 and (select city from customer where cname='Anil')=(select city from customer where cname='Sunil');
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
8. Delete deposit of anil and sunil if they are having less deposit than vijay.
SQL> delete from deposit2 where cname IN(select cname from deposit2 where cname IN('ANIL','SUNIL') AND amount < (select amount
from deposit2 where cname = 'VIJAY'));
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
9. Delete deposit of ajay if vijay is not depositor.
SQL> delete from deposit2 where cname = 'AJAY' and NOT EXISTS ( select cname from deposit2 where cname = 'VIJAY');
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
10. Delete depositors if branch is virar and name is ajay.
SQL> delete from deposit2 where bname = 'VIRAR' and cname = 'AJAY';
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
11. Delete depositors of VRCE branch and living in city Bombay.
SQL> delete from deposit2 where bname = 'VRCE' and cname IN (select cname from customer2 where city = 'BOMBAY');
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
12. Delete borrower of branches having average loan less than 1000.
SQL> delete from borrow2 where cname IN ( select cname from borrow2 group by (cname) having avg(amount) < 1000);
0 rows deleted.
-------------------------------------------------------------------------------------------------------------------
13. Delete borrower of branches having minimum number of customers.
SQL> delete from borrow2 where cname IN (select cname from borrow2 group By (cname)
2 having count(cname) <= ALL(select count(cname) from borrow2 group by(bname)));
6 rows deleted.
SQL> select * from Borrow2;
no rows selected
-------------------------------------------------------------------------------------------------------------------
END
-------------------------------------------------------------------------------------------------------------------
0 comments:
Post a Comment