PRACTICAL - 1
1.	To study DDL-create and DML-insert commands.

(i)	Create tables according to the following definition.


(1)	Create Table Deposit (ACTNO VARCHAR2 (4), CNAME VARCHAR2 (20), BNAME VARCHAR2 (20), AMOUNT NUMBER (8, 2), ADATE DATE);

(2)	Create Table Branch (BNAME VARCHAR2 (20), CITY VARCHAR2 (20)); (3) Create Table Customers (CNAME VARCHAR2 (20), CITY VARCHAR2 (20));
(4) Create Table Borrow (LOANNO VARCHAR2 (4), CNAME VARCHAR2 (20), BNAME VARCHAR2 (20), AMOUNT NUMBER (8, 2));

Queries:


Create Table Deposit (ACTNO VARCHAR2 (4), CNAME VARCHAR2 (20), BNAME VARCHAR2 (20), AMOUNT NUMBER (8, 2), ADATE DATE)

Create Table Branch (BNAME VARCHAR2 (20), CITY VARCHAR2 (20))

Create Table Customers (CNAME VARCHAR2 (20), CITY VARCHAR2 (20))

Create Table Borrow (LOANNO VARCHAR2 (4), CNAME VARCHAR2 (20), BNAME VARCHAR2 (20), AMOUNT NUMBER (8, 2))

Table created. Table created. Table created. Table created.

 
(ii)	Insert the data as shown below.
 

Table 1: DEPOSIT
 










 
 
ACTNO	CNAME	BNAME	AMOUNT	ADATE
100	ANIL	VRCE	10000.00	1-MAR-95
101	SUNIL	AJNI	50000.00	4-JAN-96
102	MEHUL	KAROLBAGH	6500.00	17-NOV-95
104	MADHURI	CHANDI	5200.00	17-DEC-95
105	PRMOD	M.G.ROAD	30000.00	27-MAR-96
106	SANDIP	ANDHERI	2500.00	31-MAR-96
107	SHIVANI	VIRAR	10000.00	5-SEP-95
108	KRANTI	NEHRU PLACE	50000.00	2-JUL-95
109	MINU	POWAI	70000.00	10-AUG-95


Table 2: BRANCH
BNAME	CITY
VRCE	NAGPUR
AJNI	NAGPUR
KAROLBAGH	DELHI
CHANDI	DELHI
DHARAMPETH	NAGPUR
M.G.ROAD	BANGLORE
ANDHERI	BOMBAY
VIRAR	BOMBAY
NEHRU PLACE	DELHI
POWAI	BOMBAY


Table 3: CUSTOMER










 
CNAME	CITY
ANIL	CALCUTTA
SUNIL	DELHI
MEHUL	BARODA
MANDAR	PATNA
MADHURI	NAGPUR
PRAMOD	NAGPUR
SANDIP	SURAT
SHIVANI	BOMBAY
KRANTI	BOMBAY
NAREN	BOMBAY


 














Queries:
 
Table 4: BORROW
LOANNO	CNAME	BNAME	AMOUNT
201	ANIL	VRCE	10000.00
206	MEHUL	AJNI	50000.00
311	SUNIL	DHARAMPETH	30000.00
321	MADHURI	ANDHERI	20000.00
375	PRAMOD	VIRAR	80000.00
481	KRANTI	NEHRU PLACE	30000.00
 


INSERT INTO DEPOSIT VALUES('100','ANIL','VRCE',10000,'1-MAR-95'); INSERT INTO DEPOSIT VALUES('101','SUNIL','AJNI',50000,'4-JAN-65');
INSERT INTO DEPOSIT VALUES('102','MEHUL','KAROLBAGH',6500,'17-NOV-95'); INSERT INTO DEPOSIT VALUES('104','MADHURI','CHANDI',5200,'17-DEC-95');



 
INSERT INTO DEPOSIT VALUES('105','PRAMOD','M.G ROAD',30000,'27-MAR-96'); INSERT INTO DEPOSIT VALUES('106','SANDIP','ANDHERI',2500,'31-MAR-96'); INSERT INTO DEPOSIT VALUES('107','SHIVANI','VIRAR',10000,'5-SEP-95');
INSERT INTO DEPOSIT VALUES('108','KRANTI','NEHRU PALACE',50000,'2-JUL-95'); INSERT INTO DEPOSIT VALUES('109','MINU','POWAI',70000,'10-AUG-95');
INSERT INTO BRANCH VALUES('VRCE', 'NAGPUR'); INSERT INTO BRANCH VALUES('AJNI', 'NAGPUR'); INSERT INTO BRANCH VALUES('KAROLBAGH', 'DELHI'); INSERT INTO BRANCH VALUES('CHANDI', 'DELHI');
INSERT INTO BRANCH VALUES('DHARAMPETH', 'NAGPUR'); INSERT INTO BRANCH VALUES('M.G ROAD', 'BANGALORE'); INSERT INTO BRANCH VALUES('ANDERI', 'BOMBAY'); INSERT INTO BRANCH VALUES('VIRAR', 'BOMBAY');
INSERT INTO BRANCH VALUES('NEHRU PALACE', 'DELHI'); INSERT INTO BRANCH VALUES('POWAI', 'BOMBAY'); INSERT INTO CUSTOMERS VALUES ('ANIL','CALCUTTA'); INSERT INTO CUSTOMERS VALUES ('SUNIL','DELHI'); INSERT INTO CUSTOMERS VALUES ('MEHUL','BARODA'); INSERT INTO CUSTOMERS VALUES ('MANDAR','PATNA'); INSERT INTO CUSTOMERS VALUES ('MADHURI','NAGPUR'); INSERT INTO CUSTOMERS VALUES ('PRAMOD','NAGPUR'); INSERT INTO CUSTOMERS VALUES ('SANDIP','SURAT'); INSERT INTO CUSTOMERS VALUES ('SHIVANI','BOMBAY'); INSERT INTO CUSTOMERS VALUES ('KRANTI','BOMBAY');


 
INSERT INTO CUSTOMERS VALUES ('NAREN','BOMBAY'); INSERT INTO BORROW VALUES ('201','ANIL','VRCE',10000); INSERT INTO BORROW VALUES ('206','MEHUL','VRCE',50000);
INSERT INTO BORROW VALUES ('311','SUNIL','DHARAMPETH',30000); INSERT INTO BORROW VALUES ('321','MADHURI','ANDHERI',20000); INSERT INTO BORROW VALUES ('375','PRMOD','VIHAR',80000); INSERT INTO BORROW VALUES ('481','KRANTI','NEHRU PLACE',30000);
1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s)
inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s)
inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s)
inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s)
inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted.




(iii)	From the above given tables perform the following queries:
(1)	Describe deposit, branch.

DESC DEPOSIT

 TABLE DEPOSIT	

Column	Null?	Type
ACTNO	-	VARCHAR2(4)
CNAME	-	VARCHAR2(20)
BNAME	-	VARCHAR2(20)
AMOUNT	-	NUMBER(8,2)
ADATE	-	DATE

DESC BRANCH

 TABLE BRANCH	

Column	Null?	Type
BNAME	-	VARCHAR2(20)
CITY	-	VARCHAR2(20)



 


(2)	Describe borrow, customers.

DESC CUSTOMERS

 TABLE CUSTOMERS	

Column	Null?	Type
CNAME	-	VARCHAR2(20)
CITY	-	VARCHAR2(20)

DESC BORROW

 TABLE BORROW	

Column	Null?	Type
LOANNO	-	VARCHAR2(4)
CNAME	-	VARCHAR2(20)
BNAME	-	VARCHAR2(20)
AMOUNT	-	NUMBER(8,2)






(3)	List all data from table DEPOSIT.

SELECT * FROM DEPOSIT;


ACTNO	CNAME	BNAME	AMOUNT	ADATE
100	ANIL	VRCE	10000	01-MAR-95
101	SUNIL	AJNI	50000	04-JAN-65
102	MEHUL	KAROLBAGH	6500	17-NOV-95
104	MADHURI	CHANDI	5200	17-DEC-95
105	PRAMOD	M.G ROAD	30000	27-MAR-96
106	SANDIP	ANDHERI	2500	31-MAR-96
107	SHIVANI	VIRAR	10000	05-SEP-95
108	KRANTI	NEHRU PALACE	50000	02-JUL-95




 

109	MINU	POWAI	70000	10-AUG-95



(4)	List all data from table BORROW.

SELECT * FROM BORROW;


LOANNO	CNAME	BNAME	AMOUNT
201	ANIL	VRCE	10000
206	MEHUL	VRCE	50000
311	SUNIL	DHARAMPETH	30000
321	MADHURI	ANDHERI	20000
375	PRMOD	VIHAR	80000
481	KRANTI	NEHRU PLACE	30000


(5)	List all data from table CUSTOMERS.

SELECT * FROM CUSTOMERS;


CNAME	CITY
ANIL	CALCUTTA
SUNIL	DELHI
MEHUL	BARODA
MANDAR	PATNA
MADHURI	NAGPUR
PRAMOD	NAGPUR
SANDIP	SURAT
SHIVANI	BOMBAY
KRANTI	BOMBAY
NAREN	BOMBAY


(6)	List all data from table BRANCH.

SELECT * FROM Branch;


BNAME	CITY



 

VRCE	NAGPUR
AJNI	NAGPUR
KAROLBAGH	DELHI
CHANDI	DELHI
DHARAMPETH	NAGPUR
M.G ROAD	BANGALORE
ANDERI	BOMBAY
VIRAR	BOMBAY
NEHRU PALACE	DELHI
POWAI	BOMBAY


(7)	Give name of depositors having amount greater than 10000.

SELECT CNAME FROM DEPOSIT WHERE AMOUNT > 10000;


CNAME
SUNIL
PRAMOD
KRANTI
MINU


(8)	Give name of customers who opened account after date '5-SEP-95.

SELECT CNAME FROM DEPOSIT WHERE ADATE > '5-SEP-95';


CNAME
SUNIL
MEHUL
MADHURI
PRAMOD
SANDIP







 





























PRACTICAL - 2

2.	Create the below given table and insert the data accordingly.
(i)	Create tables according to the following definition.

(1)	Create Table job (job_id, job_title, min_sal, max_sal)

COLUMN NAME	DATA TYPE
job_id	Varchar2(10)
job_title	Varchar2(40)
min_sal	Number(9,2)
max_sal	Number(10,2)

Queries:



 

create table job(job_id varchar(10),job_name varchar(40),min_sal number(9,2),max_sal number(10,2))

 Table created.	





(2)	Create table employee (emp_no, emp_name, emp_sal, emp_comm, dept_no)

COLUMN NAME	DATA TYPE
emp_no	Number(4)
emp_name	Varchar2(50)
emp_sal	Number(9,2)
emp_comm	Number(7,2)
dept_no	Number(3)

create table employee(emp_n number(4),emp_name varchar(50),emp_sal number(9,2),emp_common number(7,2),dept_no number(3))

 Table created.	


(3)	Create table deposit (a_no, cname, bname, amount, a_date).

COLUMN NAME	DATA TYPE
a_no	Varchar2(14)
Cname	Varchar2(20)
Bname	Varchar2(30)
Amount	Number(9,2)
a_date	Date

create table deposit(A_no int, Cname varchar(20), Bname varchar(10),amount int, adate date)

 Table created.	



 

(4)	Create table borrow (loanno, cname, bname, amount).

COLUMN NAME	DATA TYPE
loanno	Varchar2(7)
cname	Varchar2(30)
bname	Varchar2(20)
amount	Number(9,2)


Create Table Borrow (LOANNO int, CNAME VARCHAR2 (30), BNAME VARCHAR2 (20), AMOUNT NUMBER (9, 2))

Table created.

(ii)	Insert the data as shown below.

(1)	Insert following values in the table employee.

emp_n	emp_name	emp_sal	emp_comm	dept _no
101	Smith	80000		120
102	Snehal	160000	300	125
103	Adama	110000	0	120
104	Aman	300000		115
105	Anita	5000000	50,000	110
106	Sneha	2400000	24,500	110
107	Anamika	29075		130

insert into employee values('101','Smith','80000','100','120'); insert into employee values('102','Snehal','160000','300','125'); insert into employee values('103','Adama','110000','0','120'); insert into employee values('104','Aman','30000','100','115'); insert into employee values('105','Anita','5000000','50000','110'); insert into employee values('106','Sneha','2400000','24500','110');
insert into employee values('107','Anamika','29075','100','130');	




 
 
(2)	Insert following values in the table job.

job_id	job_name	min_sal	max_sal
IT_PROG	Programmer	40000	100000
MK_MGR	Marketing manager	90000	150000
FI_MGR	Finance manager	82000	120000
FI_ACC	Account	42000	90000
LEC	Lecturer	6000	170000
COMP_OP	Computer Operator	15000	30000

insert into job values('It_Prog','programmer','40000','100000'); insert into job values('Mk_MGR','Marketing manager','90000','150000'); insert into job values('Fi_MGR','Finance manager','82000','120000'); insert into job values('Fi-ACC','account','42000','90000');
insert into job values('lec','Lecturer','6000','170000');
insert into job values('Comp_op','computer operator','15000','30000');	




(3)	Insert following values in the table deposit.















 
A_no	Cname	Bname	Amount	Date
101	Anil	andheri	7000	1-Jan-16
102	Sunil	virar	5000	15-Jul-12
103	Jay	villeparle	6500	12-Mar-16
104	Vijay	andheri	8000	17-Sep-13
105	Keyur	dadar	7500	19-nov-12
106	Mayor	borivali	5500	21-dec-06

insert into deposit values('101','Anil','andheri','7000','1-Jan-16'); insert into deposit values('102','Sunil','virar','5000','15-Jul-12'); insert into deposit values('103','Jay','villeparle','6500','12-Mar-16'); insert into deposit values('104','Vijay','andheri','8000','17-sep-13'); insert into deposit values('105','Keyur','dadar','7500','19-Nov-12'); insert into deposit values('106','Mayur','borivali','5500','21-Dec-06');

1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted.

(iii)	Perform following queries

(1)	Retrieve all data from employee, jobs and deposit.

select * from employee;

EMP_N	EMP_NAME	EMP_SAL	EMP_COMMON	DEPT_NO
101	Smith	80000	100	120
102	Snehal	160000	300	125
103	Adama	110000	0	120
104	Aman	30000	100	115
105	Anita	5000000	50000	110
106	Sneha	2400000	24500	110
107	Anamika	29075	100	130

select * from job;

JOB_ID	JOB_NAME	MIN_SAL	MAX_SAL
It_Prog	programmer	40000	100000




 

Mk_MGR	Marketing manager	90000	1500000
Fi_MGR	Finance manager	820000	1200000
Fi-ACC	account	42000	90000
lec	Lecturer	6000	170000
Comp_op

select * fro	computer operator

m deposit;	15000	30000

A_NO	CNAME	BNAME	AMOUNT	ADATE
101	Anil	andheri	7000	01-JAN-16
102	Sunil	virar	5000	15-JUL-12
103	Jay	villeparle	6500	12-MAR-16
104	Vijay	andheri	8000	17-SEP-13
105	Keyur	dadar	7500	19-NOV-12
106	Mayur	borivali	5500	21-DEC-06


(2)	Give details of account no. and deposited rupees of customers having account opened between dates 01-01-12 and 25-07-16.

select A_No , AMOUNT from deposit where ADATE Between 01-01-12 and 25-07-16;

A_NO	AMOUNT
101	7000
102	5000
103	6500
104	8000
105	7500


(3)	Display all jobs with minimum salary is greater than 40000.

select JOB_NAME from job where MIN_SAL > 40000;


JOB_NAME	MIN_SAL
Marketing manager	90000
Finance manager	82000
account	42000




 


(4)	Display name and salary of employee whose department no is 120. Give alias name to name of employee.

select EMP_NAME, EMP_SAL from employee where DEPT_NO = 120;


EMP_NAME	EMP_SAL	DEPT_NO
Smith	80000	120
Adama	110000	120




select EMP_NAME AS Name from employee;


Name
Smith
Snehal
Adama
Aman
Anita
Sneha
Anamika



(5)	Display employee no, name and department details of those employee whose department lies in(110,120)



Select EMP_N, EMP_NAME from employee where DEPT_NO IN (110,120);





EMP_N	EMP_NAME	DEPT_NO
101	Smith	120
103	Adama	120




 

105	Anita	110
106	Sneha	110




(iv)	To study various options of LIKE predicate


(1)	Display all employee whose name start with ‘A’ and third character is ‘a’.

select EMP_NAME from employee where EMP_NAME Like ‘A_a%’;

EMP_NAME
Adama
Aman
Anamika




(4)	Display name, number and salary of those employees whose name is 5 characters long and first three characters are ‘Ma’.

select EMP_NAME, EMP_N, EMP_SAL from employee where EMP_NAME like ‘Ma	’;


No results found:
# As there are no words matching the query entered so no results found is displayed!!




(5)	Display the non-null values of employees and also employee name second character should be ‘n’ and string should be 5 character long.

select * from employee where EMP_COMMON IS NOT NULL and EMP_NAME like ‘_n	’;


EMP_N	EMP_NAME	EMP_SAL	EMP_COMMON	DEPT_NO
105	Anita	5000000	50000	110
106	Sneha	2400000	24500	110



 



 

(5) Display the null values of employee and also employee name’s third character should be ‘r’.

select * from employee where EMP_COMMON IS NULL and EMP_NAME like ‘ r%’;	



No Results Found:



























PRACTICAL - 3
3.	To perform various data manipulation commands, aggregate functions and sorting concept on all created tables.

(1)	List total deposit from deposit.




 
SELECT SUM (Amount) FROM DEPOSIT;

SUM(Amount) 234200
(2)	Give maximum loan from branch CHANDI.

SELECT MAX (Amount), Branch FROM DEPOSIT where Branch = ‘CHANDI’ GROUP BY Branch;


MAX(Amount)	BRANCH 5200	CHANDI

(3)	Count total number of customers

Select COUNT (CNAME) from CUSTOMER; COUNT(CNAME)
10

(4)	Create table supplier from employee with all the columns.

Insert into supplier (sup_n, sup_name, sup_sal, sup_common, dept_no) select * from employee;


SUP_N	SUP_NAME	SUP_SAL	SUP_COMMON	DEPT_NO
101	Smith	80000	100	120
102	Snehal	160000	300	125
103	Adama	110000	0	120
104	Aman	30000	100	115
105	Anita	5000000	50000	110
106	Sneha	2400000	24500	110
107	Anamika	29075	100	130


(5)	Delete all the rows from sup1.

Delete from supplier;







 
(6)	Delete the detail of supplier whose sup_no is 104.

(1)	row deleted;	
 

SUP_N	SUP_NAME	SUP_SAL	SUP_COMMON	DEPT_NO
101	Smith	80000	100	120
102	Snehal	160000	300	125
103	Adama	110000	0	120
105	Anita	5000000	50000	110
106	Sneha	2400000	24500	110
107	Anamika	29075	100	130


(7)	Rename the table sup2.

 Rename supplier to sup2;	

Table renamed:

(8)	Destroy table sup1 with all the data.

 Drop table supplier;	

Table dropped;

(9)	Update the value of employee name whose employee number is 105.


EMP_N	EMP_NAME	EMP_SAL	EMP_COMMON	DEPT_NO
101	Smith	80000	100	120
102	Snehal	160000	300	125
103	Adama	110000	0	120




 

104	Aman	30000	100	115
105	Arjun	5000000	50000	110
106	Sneha	2400000	24500	110
107	Anamika	29075	100	130










































PRACTICAL - 4



4.	To study Single-row functions
 



 
 

EMP_NAME
SMITH
SNEHAL
ADAMA
AMAN
ANITA
SNEHA
ANAMIKA

 

EMP_NAME
smith sneha
adama
aman
anita
sneha
anamika


EMP_NAME
Smith
Snehal
Adama
Aman
Anita
Sneha
Anamika



 
 

It_Prog programmer
Mk_MGR Marketing manager
Fi_MGR Finance manager
Fi-ACC account
lec Lecturer
Comp_op computer operator

 

Length(JOB_NAME)
10
17
15
7
8
17


Select lpad(job_name,10,’*’) from job;

JOB_NAME
programmer
Marketing
Finance ma
***account
**Lecturer
computer o


Select rpad (job_name,10,’%’) from job;

JOB_NAME
Programmer



 

Marketing
Finance ma
Account%%%
Lecturer%%
computer o


Select trim (‘a’ from job_name) from job;

JOB_NAME
progrmmer
Mrketing mnger
Finnce mnger
ccount
Lecturer
computer opertor


Select replace (job_name,’t’,’v’) from job;

JOB_NAME
programmer
Markeying manager
Finance manager
Accouny
Lecyurer
compuyer operayor


Select substr (job_name,2,5) from job;

JOB_NAME
rogr
arke
inan
ccou
ectu




 
ompu

JOB_NAME
2,5,10
3,17
15
Invalid
6,8
8,1317


NUMBER FUNCTIONS:
Select round (124.483) from dual;

Round(124.483) 124.5
Select trunc (724.4839768, 3) from dual;

Trunc(724.4839768, 3)

724.483

Select mod (45, 7) from dual;

Mod(45,7) 3

DATE FUNCTIONS:
 Select cname, month_between (sysdate, adate=’01-JAN-16’) from deposit;	
 




CNAME
Anil



 
Jay

ADD_MONTHS(SYSDATE,6)
17-FEB-22

 

NEXT_DAY(SYSDATE,4)
18-AUG-21

 

SYSDATE
17-AUG-21

 



























PRACTICAL-5
Aim: Displaying data from Multiple Tables (JOIN).




 
1)	Give details of customer SANDIP Query:
SELECT C.CNAME, C.CITY, D.ACTNO, B.AMOUNT, D.ADATE,B. LOANNO,D.AMOUNT FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME INNER JOIN BORROW B ON D.CNAME=B.CNAME WHERE C.CNAME='SANDIP';

Output:

CNAME	CITY	AMOUNT	ADATE	LOANNO

SANDIP CALCUTTA	1000	1-MAR-95	201

2)	Give name of customers who are borrowers and depositors and living in city Delhi
Query:
SELECT C.CNAME FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME INNER JOIN BORROW B ON B.CNAME=D.CNAME WHERE C.CITY='DELHI';

Output:

CNAME

SUNIL

3)	Give city as their city name of customers having same living branch
Query:
SELECT C.CITY, C.CNAME, B.CNAME FROM CUSTOMERS C INNER JOIN BRANCH B ON C.CITY=B.CITY INNER JOIN DEPOSIT D ON B.BNAME=D.BNAME;

Output:

CITY	CNAME	BNAME

BOMBAY KRANTI	VIRAR

4)	Write a query to display the last name , department number and department name for all employees
Query:



 
SELECT E.EMP_NAME, D.DNAME, D.DEPT_NO FROM EMPLOYEE E INNER JOIN DEPT D ON E.DEPT_NO=D.DEPT_NO;

Output:	
EMP_NAME	DNAME	DEPT_NO
Smith	Teaching	120
Snehal	Marketing	125
Aman	Computer	115



































PRACTICAL - 6




 
6. To apply the concept of Aggregating Data using Group functions.

(1) List total deposit of customers living in city Delhi.

Query:

SELECT SUM (D.AMOUNT) FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME WHERE CITY='DELHI';
 


2)	Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number.


EMP_N	EMP_NAME	EMP_SAL	EMP_COMMON	DEPT_NO
101	Smith	80000	100	120
102	Snehal	160000	300	125
103	Adama	110000	0	120
104	Aman	30000	100	115
105	Anita	5000000	50000	110
106	Sneha	2400000	24500	110
107	Anamika	29075	100	130

Query:
 

MAXIMUM	MINIMUM	SUM	AVERAGE
5000000	29075	7809075	1115582


3)	Write a query to display the total salary being paid to each job title, within each department.

Query:



 
 

Output:	
SUM(EMP_SAL)	DEPT_NO
190000	120
160000	125
30000	115
7400000	110
29075	130



4)	Find the average salaries > 20000 for each department without displaying the respective department numbers.

Query:


Output:


AVG(EMP_SAL)
95000
160000
30000
3700000
29075













PRACTICAL-7
Aim: To solve queries using the concept of sub query.



 

•	create table employee(emp_n int,emp_name varchar(20),emp_lname varchar(20),
 	emp_sal int,emp_common int,dept_no int, hire_date date)	

insert into employee values('101','Smith','Kumar','80000','100','120','15-Jun- 2002');
insert into employee values('102','Snehal','Parikh','160000','300','125','16-Jul- 2003');
insert into employee values('103','Adama','Parmar','110000','0','120','20-Aug- 2007');
insert into employee values('104','Aman','Bhuvar','30000','100','115','25-Sept- 2010');
insert into employee values('105','Anita','Bagadiya','5000000','50000','110','17- Jan-2002');
insert into employee values('106','Sneha','Patel','2400000','24500','110','27-Dec- 2000');
insert into employee values('107','Anamika','Singh','29075','100','130','10-Mar- 2006');	
insert into employee values('108','Scott','Anderson','76000','100','130','02- Jun-2006');

(1)	Write a query to display the last name and hire date of any employee in the same department as SCOTT. Exclude SCOTT
QUERY:

select emp_lname,hire_date from employee where dept_no in(select dept_no from employee where emp_name = 'Scott') And emp_name <> 'Scott';

Output:


(2)	Give name of customers who are depositors having same branch city of Sunil.

select cname from customers where bname in(select bname from customers where cname
= 'Sunil');

Output:

CNAME
Sunil


(3)	Give deposit details and loan details of customer in same city where


 
Pramod is living.
Query:

select cname from DEPOSIT inner join BORROW on DEPOSIT.BNAME = BORROW.BNAME where DEPOSIT.BNAME in (select BNAME from BRANCH where CITY=(select CITY from CUSTOMER where CNAME='Pramod'));

Output:

CNAME
Madhuri
Pramod

(4)	Create a query to display the employee numbers and last names of all employees who earn more than the average salary. Sort the results in ascending order of salary.

Query:

select emp_n, emp_lname from employee where emp_sal > (select avg(emp_sal) from employee) order by emp_sal;

Output:


(5)	Display the department number, name, and job for every employee in the Accounting department.

select dept_no, emp_name, job_name from employee where dept_name in(select dept_name from employee where dept_name = 'Account');

Dept_No	Emp_name	Job_name
115	Aman	Account







PRACTICAL-8



 

Aim: Manipulating Data Using all DML commands.
1)	Give 20% interest to all depositors.

A_no	Cname	Bname	Amount	date
101	Anil	andheri	7000	1-Jan-16
102	Sunil	virar	5000	15-Jul-12
103	Jay	villeparle	6500	12-Mar-16
104	Vijay	andheri	8000	17-Sep-13
105	Keyur	dadar	7500	19-nov-12
106	Mayor	borivali	5500	21-dec-06

Query:

Update deposit set amount = amount + 0.2*amount; Select * from deposit;

Output:

A_no	Cname	Bname	Amount	date
101	Anil	andheri	84000	1-Jan-16
102	Sunil	virar	6000	15-Jul-12
103	Jay	villeparle	7800	12-Mar-16
104	Vijay	andheri	9600	17-Sep-13
105	Keyur	dadar	9000	19-nov-12
106	Mayor	borivali	6600	21-dec-06










 
2)	Give 15% interest to all depositors living in Nagpur and having branch city Bombay

Query:

Update deposit set amount = amount + 0.15*amount where city = Nagpur and bname = Bombay;
Select * from deposit;

Output:

A_no	Cname	Bname	Amount	date
101	Anil	andheri	8050	1-Jan-16
102	Sunil	virar	5750	15-Jul-12
103	Jay	villeparle	7475	12-Mar-16
104	Vijay	andheri	9200	17-Sep-13
105	Keyur	dadar	8625	19-nov-12
106	Mayor	borivali	6325	21-dec-06


3)	Transfer 1000 rupees from account of Anil to Sunil if both are having same branch

Query:

Update deposit set amount = amount + 1000 where cname = ‘Sunil’ and bname =
‘Ajni’;
Update deposit set amount = amount – 1000 where cname = ‘Anil ’ and bname = ‘VRCE’;
Select cname, amount from deposit;

Output:

Cname	Amount
Anil	6000
Sunil	6000








 

4)	Delete depositors of branches having number of customers between 1-3 Query:
Delete cname from deposit group by bname where count (bname) between 1 and 3;

Output:

all rows deleted.

// because all branches had number of customers between 1 to 3

5)	Delete borrower of branches having average loan less than 10000



A_no	Cname	Bname	Amount	date
101	Anil	andheri	7000	1-Jan-16
102	Sunil	virar	5000	15-Jul-12
103	Jay	villeparle	6500	12-Mar-16
104	Vijay	andheri	8000	17-Sep-13
105	Keyur	dadar	7500	19-nov-12
106	Mayor	borivali	5500	21-dec-06



Query:

Delete cname from deposit group by bname where amount in (select avg (amount) from deposit) < 10000;

Output:

All rows deleted..

// because all rows had average amount less than 10000..








 


PRACTICAL-9
Aim: To apply the concept of security and privileges. Query:
Create user client;

Grant select, insert on customers to client; Revoke select on customers from client;

Output:
























 




PRACTICAL-10

Aim: To study Transaction control command.

+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2000.00 |
| 2 | Khilan	| 25 | Delhi	| 1500.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 4 | Chaitali | 25 | Mumbai	| 6500.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
| 7 | Muffy	| 24 | Indore	| 10000.00 |
+	+	+	+	+	+
.


SQL> DELETE FROM CUSTOMERS WHERE AGE = 25;
SQL> COMMIT;
Output:
+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
| 7 | Muffy	| 24 | Indore	| 10000.00 |
+	+	+	+	+	+

The ROLLBACK Command
The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for a ROLLBACK command is as follows −
ROLLBACK;
Example
Consider the CUSTOMERS table having the following records −



 
+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2000.00 |
| 2 | Khilan	| 25 | Delhi	| 1500.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 4 | Chaitali | 25 | Mumbai	| 6500.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
| 7 | Muffy	| 24 | Indore	| 10000.00 |
+	+	+	+	+	+
.

SQL> DELETE FROM CUSTOMERS WHERE AGE = 25;
SQL> ROLLBACK;
Output:
+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	-+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2000.00 |
| 2 | Khilan	| 25 | Delhi	| 1500.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 4 | Chaitali | 25 | Mumbai	| 6500.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
| 7 | Muffy	| 24 | Indore	| 10000.00 |
+	+	+	+	+	+

The SAVEPOINT Command
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
The syntax for a SAVEPOINT command is as shown below.
SAVEPOINT SAVEPOINT_NAME;
This command serves only in the creation of a SAVEPOINT among all the transactional statements. The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back to a SAVEPOINT is as shown below.
ROLLBACK TO SAVEPOINT_NAME;
Following is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.
Example


 
Consider the CUSTOMERS table having the following records.

+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2000.00 |
| 2 | Khilan	| 25 | Delhi	| 1500.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 4 | Chaitali | 25 | Mumbai	| 6500.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
| 7 | Muffy	| 24 | Indore	| 10000.00 |
+	+	+	+	+	+
.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted. SQL> SAVEPOINT SP2; Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted. SQL> SAVEPOINT SP3; Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.
−

SQL> ROLLBACK TO SP2;
Rollback complete.
Output:.

SQL> SELECT * FROM CUSTOMERS;
+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 2 | Khilan	| 25 | Delhi	| 1500.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 4 | Chaitali | 25 | Mumbai	| 6500.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
| 7 | Muffy	| 24 | Indore	| 10000.00 |
+	+	+	+	+	+
6 rows selected.








 




PRACTICAL-11

Aim: To implement PL/SQL using cursor.

In this chapter, we will discuss the cursors in PL/SQL. Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc.
A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.
You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors
−

•	Implicit cursors
•	Explicit cursors


1.	Declare Cursor

A cursor is a select statement, defined in the declaration section in MySQL. Syntax
DECLARE cursor_name CURSOR FOR Select statement; Parameter: cursor_name: name of the cursor select_statement: select query associated with the cursor
2.	Open Cursor After declaring the cursor the next step is to open the cursor using open statement.

Syntax Open cursor_name;

Parameter: cursor_name: name of the cursor which is already declared.

3.		Fetch Cursor After declaring and opening the cursor, the next step is to fetch the cursor.
It is used to fetch the row or the column.

Syntax FETCH [ NEXT [ FROM ] ] cursor_name INTO variable_list; Parameter: cursor_name: name of the cursor variable_list: variables, comma

 
separated, etc. is stored in a cursor for the result set


4.	Close Cursor The final step is to close the cursor.
Syntax Close cursor_name; Parameter: Cursor_name: name of the cursor Example of cursor:
Select * from customers;

+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2000.00 |
| 2 | Khilan	| 25 | Delhi	| 1500.00 |
| 3 | kaushik | 23 | Kota	| 2000.00 |
| 4 | Chaitali | 25 | Mumbai	| 6500.00 |
| 5 | Hardik	| 27 | Bhopal	| 8500.00 |
| 6 | Komal	| 22 | MP	| 4500.00 |
+	+	+	+	+	+


Query:

DECLARE
total_rows number(2); BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN dbms_output.put_line('no customers selected');
ELSIF sql%found THEN total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected '); END IF;
END;
/ Output:
Select * from customers;

+	+	+	+	+	+
| ID | NAME	| AGE | ADDRESS	| SALARY	|
+	+	+	+	+	+
| 1 | Ramesh	| 32 | Ahmedabad | 2500.00 |
| 2 | Khilan	| 25 | Delhi	| 2000.00 |
| 3 | kaushik | 23 | Kota	| 2500.00 |
| 4 | Chaitali | 25 | Mumbai	| 7000.00 |
| 5 | Hardik	| 27 | Bhopal	| 9000.00 |


 
| 6 | Komal	| 22 | MP	| 5000.00 |
+	+	+	+	+	+


PRACTICAL-12

Aim: To implement PL/SQL using trigger.

In this chapter, we will discuss Triggers in PL/SQL. Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events −
•	A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
•	A database definition (DDL) statement (CREATE, ALTER, or DROP).
•	A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers can be defined on the table, view, schema, or database with which the event is associated.

Benefits of Triggers
Triggers can be written for the following purposes −

•	Generating some derived column values automatically
•	Enforcing referential integrity
•	Event logging and storing information on table access
•	Auditing
•	Synchronous replication of tables
•	Imposing security authorizations
•	Preventing invalid transactions

Creating Triggers
The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n] [FOR EACH ROW]
WHEN (condition) DECLARE
Declaration-statements
BEGIN



 
Executable-statements EXCEPTION
Exception-handling-statements END;


Query:

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW
WHEN (NEW.ID > 0) DECLARE
sal_diff number; BEGIN
sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line('Old salary: ' || :OLD.salary); dbms_output.put_line('New salary: ' || :NEW.salary); dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/ Output:
Trigger created.

// Invoking a trigger program.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Old salary:
New salary: 7500 Salary difference:

// After updation now trigger can work on changes and produce desired result.

UPDATE customers
SET salary = salary + 500 WHERE id = 2;
// Trigger output based on operations Old salary: 1500
New salary: 2000
Salary difference: 500