PRACTICLE - 1
Aim: Introduction about DBMS.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRACTICLE - 2
Aim:To study DDL-create and DML-insert commands.
CREATE TABLE BRANCH (BNAME varchar2(30),CITY varchar2(30));
INSERT INTO BRANCH (BNAME,CITY) VALUES('VRCE','NAGPUR');
INSERT INTO BRANCH (BNAME,CITY) VALUES('AJNI','NAGPUR');
INSERT INTO BRANCH (BNAME,CITY) VALUES('KAROLBAGH','DELHI');
INSERT INTO BRANCH (BNAME,CITY) VALUES('CHANDI','DELHI');
INSERT INTO BRANCH (BNAME,CITY) VALUES('M.G.ROAD','BANGLORE');
INSERT INTO BRANCH (BNAME,CITY) VALUES('ANDHERI','BOMBAY');
INSERT INTO BRANCH (BNAME,CITY) VALUES('VIRAR','BOMBAY');
INSERT INTO BRANCH (BNAME,CITY) VALUES('NEHRU PLACE','DELHI');
INSERT INTO BRANCH (BNAME,CITY) VALUES('POWAI','BOMBAY');
INSERT INTO BRANCH (BNAME,CITY) VALUES('VRCE','NAGPUR');
INSERT INTO BRANCH (BNAME,CITY) VALUES('AJNI','NAGPUR');
INSERT INTO BRANCH (BNAME,CITY) VALUES('KAROLBAGH','DELHI');
INSERT INTO BRANCH (BNAME,CITY) VALUES('CHANDI','DELHI');
INSERT INTO BRANCH (BNAME,CITY) VALUES('M.G.ROAD','BANGLORE');
INSERT INTO BRANCH (BNAME,CITY) VALUES('ANDHERI','BOMBAY');
INSERT INTO BRANCH (BNAME,CITY) VALUES('VIRAR','BOMBAY');
INSERT INTO BRANCH (BNAME,CITY) VALUES('NEHRU PLACE','DELHI');
INSERT INTO BRANCH (BNAME,CITY) VALUES('POWAI','BOMBAY');
CREATE TABLE CUSTOMERS (CNAME varchar2(30),CITY varchar2(30));
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('ANIL','CALCUTTA');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('SUNIL','DELHI');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('MEHUL','BARODA');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('MANDAR','PATNA');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('MADHURI','NAGPUR');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('PRAMOD','NAGPUR');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('SANDIP','SURAT');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('SHIVANI','BOMABY');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('KRANTI','BOMBAY');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('NAREN','BOMBAY');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('ANIL','CALCUTTA');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('SUNIL','DELHI');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('MEHUL','BARODA');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('MANDAR','PATNA');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('MADHURI','NAGPUR');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('PRAMOD','NAGPUR');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('SANDIP','SURAT');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('SHIVANI','BOMABY');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('KRANTI','BOMBAY');
INSERT INTO CUSTOMERS (CNAME,CITY) VALUES('NAREN','BOMBAY');
CREATE TABLE DEPOSIT(ACTNO NUMBER(3), CNAME varchar2(30), BNAME varchar2(30), AMOUNT NUMBER(6,2), ADATE DATE);
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(100,'ANIL','VRCE',1000.00,'1-MAR-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(101,'SUNIL','AJNI',5000.00,'4-JAN-96');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(102,'MEHUL','KAROLBAGH',3500.00,'17-NOV-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(104,'MADHURI','CHANDI',1200.00,'17-DEC-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(105,'PRAMOD','M.G.ROAD',3000.00,'27-MAR-96');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(106,'SANDIP','ANDHERI',2000.00,'31-MAR-96');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(107,'SHIVANI','VIRAR',1000.00,'5-SEP-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(108,'KRANTI','NEHRU PLACE',5000.00,'2-JUL-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(109,'MINU','POWAI',7000.00,'10-AUG-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(100,'ANIL','VRCE',1000.00,'1-MAR-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(101,'SUNIL','AJNI',5000.00,'4-JAN-96');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(102,'MEHUL','KAROLBAGH',3500.00,'17-NOV-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(104,'MADHURI','CHANDI',1200.00,'17-DEC-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(105,'PRAMOD','M.G.ROAD',3000.00,'27-MAR-96');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(106,'SANDIP','ANDHERI',2000.00,'31-MAR-96');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(107,'SHIVANI','VIRAR',1000.00,'5-SEP-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(108,'KRANTI','NEHRU PLACE',5000.00,'2-JUL-95');
INSERT INTO DEPOSIT (ACTNO,CNAME,BNAME,AMOUNT,ADATE) VALUES(109,'MINU','POWAI',7000.00,'10-AUG-95');
CREATE TABLE BORROW (LOANNO NUMBER(3),CNAME varchar2(30),BNAME varchar2(30),AMOUNT NUMBER(6,2));
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(201,'ANIL','VRCE',1000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(206,'MEHUL','AJNI',5000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(311,'SUNIL','DHARAMPETH',3000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(321,'MADHURI','ANDHERI',2000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(375,'PRAMOD','VIRAR',8000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(481,'KRANTI','NEHRU PLACE',3000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(201,'ANIL','VRCE',1000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(206,'MEHUL','AJNI',5000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(311,'SUNIL','DHARAMPETH',3000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(321,'MADHURI','ANDHERI',2000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(375,'PRAMOD','VIRAR',8000.00);
INSERT INTO BORROW (LOANNO,CNAME,BNAME,AMOUNT) VALUES(481,'KRANTI','NEHRU PLACE',3000.00);
#Perform Following Query#
(1) Describe deposit, branch.
Query:DESCRIBE BRANCH;
DESCRIBE DEPOSIT;
(2) Describe borrow, customers.
Query: DESCRIBE BORROW;
DESCRIBE CUSTOMERS;
(3) List all data from table DEPOSIT.
Query: SELECT * FROM DEPOSIT;
(4) List all data from table BORROW.
Query: SELECT * FROM BORROW;
(5) List all data from table CUSTOMERS.
Query: SELECT * FROM CUSTOMERS ;
(6) List all data from table BRANCH.
Query: SELECT * FROM BRANCH;
(7) Give account no and amount of depositors. :
Query: SELECT ACTNO,AMOUNT FROM DEPOSIT;
Output:
Query:DESCRIBE BRANCH;
DESCRIBE DEPOSIT;
(2) Describe borrow, customers.
Query: DESCRIBE BORROW;
DESCRIBE CUSTOMERS;
(3) List all data from table DEPOSIT.
Query: SELECT * FROM DEPOSIT;
(4) List all data from table BORROW.
Query: SELECT * FROM BORROW;
(5) List all data from table CUSTOMERS.
Query: SELECT * FROM CUSTOMERS ;
(6) List all data from table BRANCH.
Query: SELECT * FROM BRANCH;
(7) Give account no and amount of depositors. :
Query: SELECT ACTNO,AMOUNT FROM DEPOSIT;
Output:
(8) Give name of depositors having amount greater than 4000.
Query: SELECT CNAME FROM DEPOSIT WHERE AMOOUNT>4000;
Output:
(9) Give name of customers who opened account after date '1-12-96'.
Query: SELECT CNAME FROM DEPOSIT WHERE ADATE>'1-DEC-96';
Output:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRACTICLE - 3
AIM: Create the below given table and insert the data accordingly
Query:
CREATE TABLE JOB(JOB_ID varchar2(25),JOB_NAME varchar2(25),MIN_SAL NUMBER(4),MAX_SAL NUMBER(5));
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('IT_PROG','Programmer',4000,10000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('MK_MGR','Marketing manager',9000,15000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('FI_MGR','Finance Manager',8200,12000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('FI_ACC','Account',4200,9000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('LEC','Lecture',6000,17000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('COMP_OP','Computer Operator',1500,3000);
CREATE TABLE JOB(JOB_ID varchar2(25),JOB_NAME varchar2(25),MIN_SAL NUMBER(4),MAX_SAL NUMBER(5));
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('IT_PROG','Programmer',4000,10000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('MK_MGR','Marketing manager',9000,15000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('FI_MGR','Finance Manager',8200,12000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('FI_ACC','Account',4200,9000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('LEC','Lecture',6000,17000);
INSERT INTO JOB(JOB_ID,JOB_NAME,MIN_SAL,MAX_SAL) VALUES('COMP_OP','Computer Operator',1500,3000);
CREATE TABLE MANAGER(MNO NUMBER(1),MNAME varchar2(25));
INSERT INTO MANAGER(MNO,MNAME) VALUES(1,'Rahul');
INSERT INTO MANAGER(MNO,MNAME) VALUES(2,'kaushal');
INSERT INTO MANAGER(MNO,MNAME) VALUES(3,'Bharat');
INSERT INTO MANAGER(MNO,MNAME) VALUES(1,'Rahul');
INSERT INTO MANAGER(MNO,MNAME) VALUES(2,'kaushal');
INSERT INTO MANAGER(MNO,MNAME) VALUES(3,'Bharat');
CREATE TABLE DEPT (DEPT_NO NUMBER(2),DNAME varchar2(30),LOCATION varchar2(30),MNO NUMBER(1));
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(10,'Account','India',2);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(15,'Computer','New York',1);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(20,'Teaching','Canada',3);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(25,'Marketing','Landon',2);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(30,'Development','Australia',1);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(10,'Account','India',2);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(15,'Computer','New York',1);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(20,'Teaching','Canada',3);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(25,'Marketing','Landon',2);
INSERT INTO DEPT (DEPT_NO,DNAME,LOCATION,MNO) VALUES(30,'Development','Australia',1);
CREATE TABLE EMPLOYEE (EMP_NO NUMBER(3),EMP_NAME varchar2(30),EMP_SAL NUMBER(4),EMP_COMM NUMBER(5),DEPT_NO NUMBER(2), HIRE_DATE DATE);
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(101,'Smith',800,NULL,20,'1-JUN-1999');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(102,'Snehal',1600,300,25,'28-MAR-2005');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(103,'Adama',1100,0,20,'3-AUG-2003');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(104,'Aman',3000,NULL,15,'17-DEC-1995');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(105,'Anita',5000,50000,10,'18-NOV-2000');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(106,'Sneha',2450,24500,10,'5-APR-1993');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(107,'Anamika',2975,NULL,30,'23-FEB-2010');
#Perform Following Query#
(1) Retrieve all data from employee, jobs and deposit.
Query: SELECT * FROM EMPLOYEE;
SELECT * FROM JOBS;
SELECT * FROM DEPOSIT;
(2) Give details of account no. and deposited rupees of customers having account opened between dates 01-01-06 and 25-07-06.
Query: SELECT EMP_NO,EMP_SAL FROM EMPLOYEE WHERE HIRE_DATE BETWEEN '01-JUN-99' AND '03-AUG-03';
Output:
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(101,'Smith',800,NULL,20,'1-JUN-1999');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(102,'Snehal',1600,300,25,'28-MAR-2005');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(103,'Adama',1100,0,20,'3-AUG-2003');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(104,'Aman',3000,NULL,15,'17-DEC-1995');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(105,'Anita',5000,50000,10,'18-NOV-2000');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(106,'Sneha',2450,24500,10,'5-APR-1993');
INSERT INTO EMPLOYEE(EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE) VALUES(107,'Anamika',2975,NULL,30,'23-FEB-2010');
#Perform Following Query#
(1) Retrieve all data from employee, jobs and deposit.
Query: SELECT * FROM EMPLOYEE;
SELECT * FROM JOBS;
SELECT * FROM DEPOSIT;
(2) Give details of account no. and deposited rupees of customers having account opened between dates 01-01-06 and 25-07-06.
Query: SELECT EMP_NO,EMP_SAL FROM EMPLOYEE WHERE HIRE_DATE BETWEEN '01-JUN-99' AND '03-AUG-03';
Output:
(3) Display all jobs with minimum salary is greater than 4000.
Query: SELECT JOB_ID,JOB_NAME FROM JOB WHERE MIN_SAL<4000;
Output:
Query: SELECT JOB_ID,JOB_NAME FROM JOB WHERE MIN_SAL<4000;
Output:
(4) Display name and salary of employee whose department no is 20. Give alias name to name of employee.
Query: SELECT EMP_NAME,ENAME,EMP_SAL FROM EMPLOYEE WHERE DEPT_NO=20;
Output:
(5) Display employee no, name and department details of those employee whose department lies in(10,20)
Query: SELECT EMP_NAME,EMP_NO,DEPTNO FROM EMPLOYEE WHERE DEP_NO BETWEEN 10 AND 20;
Output:
#To study various options of LIKE predicate#
(1) Display all employee whose name start with ‘A’ and third character is ‘ ‘a’.
Query: SELECT EMP_NAME FROM EMPLOYEE WHERE EMP_NAME LIKE 'A_a%';
Output: EMP_NAME
--------------------
Adama
Aman
2 rows selected
(2) Display name, number and salary of those employees whose name is 5 characters long and first three characters are ‘Ani’.
Query: SELECT EMP_NO,EMP_NAME,EMP_SAL FROM EMPLOYEE WHERE EMP_NAME LIKE 'Ani__';
Output:
(3) Display the non-null values of employees and also employee name second character should be ‘n’ and string should be 5 character long.
Query: SELECT EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO,HIRE_DATE FROM EMPLOYEE LIKE '_n___' AND EMP_COMM IS NOT NULL;
Output:
(4) Display the null values of employee and also employee name’s third character should be ‘a’.
Query: SELECT EMP_NO,EMP_NAME,EMP_SAL,EMP_COMM,DEPT_NO FROM EMPLOYEE WHERE EMP_NAME LIKE '__a%' AND EMP_COMM IS NULL;
Output:
(5) What will be output if you are giving LIKE predicate as ‘%\_%’
( ESCAPE sequence character ‘\’ )
Query: SELECT MNO FROM MANAGER WHERE MNO LIKE '%\_%' ESCAPE '\';
Output:
1. RAM_PATEL
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRACTICAL-4
Aim: To study Single-row functions.
Perform Following Query:
(1) List total deposit from deposit.
Query:SELECT COUNT (AMOUNT) "TOTAL AMOUNT" FROM DEPOSIT;
Output: No. Total Amount
1 8
(2) List total loan from karolbagh branch
Query: SELECT COUNT (LOAN NO) "TOTAL LOAN" FROM BORROW WHERE BNAME='KAROLBAGH';
Output: No. Total Loan
1 1
(3) Give maximum loan from branch vrce.
Query:SELECT MAX (AMOUNT)"MAX. LOAN" FROM BORROW WHERE BNAME='VIRAR';
Output: No. Max.Loan
1 8000
(4) Count total number of customers
Query: SELECT COUNT (CNAME) "TOTAL NUMBERS" FROM CUSTOMERS;
Output: No. Total Numbers
1 10
(5) Count total number of customer’s cities.
Query: SELECT COUNT(DISTINCT CITY) "TOTAL NUMBERS" FROM CUSTOMERS;
Output: No. Total Number
1 7
(6) Create table supplier from employee with all the columns.
Query:CREATE TABLE SUPPLIER (EMP_NO,EMP_NAME,EMP_SAL) AS SELECT EMP_NO,EMP_NAME,EMP_SAL FROM EMPLOYEE;
Output: EMP_NO EMP_NAME EMP_SAL
101 Smith 800
102 Snehal 1600
(7) Create table sup1 from employee with first two columns.
Query: CREATE TABLE SUP1 (EMP_NO,EMP_NAME) AS SELECT EMP_NO,EMP_NAME FROM EMPLOYEE;
Output: EMP_NO EMP_NAME
101 Smith
102 Snehal
(8) Create table sup2 from employee with no data
Query: CREATE TABLE SUP2 (EMP_NO,EMP_NAME,EMP_SAL) AS SELECT EMP_NO,EMP_NAME,EMP_SAL FROM EMPLOYEE WHERE 1=0;
Output: EMP_NO WMP_NAME EMP_SAL
(9) Insert the data into sup2 from employee whose second character should be ‘n’ and string should be 5 characters long in employee name field.
Query:INSERT INTO SUP2(EMP_NO,EMP_NAME,EMP_SAL) SELECT EMP_NO,EMP_NAME,EMP_SAL WHERE EMP_NAME '_n___';
Output: -------------------------------------
(10) Delete all the rows from sup1.
Query:DELETE FROM SUP1;
Output: 8 rows deleted.
(11) Delete the detail of supplier whose sup_no is 103.
Query: DELETE FROM SUP1 WHERE SUP_NO = 103;
Output: -----------------------------------
(12) Rename the table sup2.
Query:RENAME SUP1 TO SUP2;
Output: SUP2
(13) Destroy table sup1 with all the data.
Query: DROP TABLE SUP1;
Output: -----------------
(14) Update the value dept_no to 10 where second character of emp. name is ‘m’.
Query: UPDATE EMPLOYEE SET DEPT_NO=10 WHERE EMP_NAME LIKE ('_m%');
Output: EMP_NAME DEPT_NO
Rmaj 10
(15) Update the value of employee name whose employee number is 103.
Query:UPDATE EMPLOYEE SET EMP_NAME='RAM' WHERE EMP_NO=103;
Output: EMP_NAME EMP_NO
RAM 103
Perform Following Query:
(1) List total deposit from deposit.
Query:SELECT COUNT (AMOUNT) "TOTAL AMOUNT" FROM DEPOSIT;
Output: No. Total Amount
1 8
(2) List total loan from karolbagh branch
Query: SELECT COUNT (LOAN NO) "TOTAL LOAN" FROM BORROW WHERE BNAME='KAROLBAGH';
Output: No. Total Loan
1 1
(3) Give maximum loan from branch vrce.
Query:SELECT MAX (AMOUNT)"MAX. LOAN" FROM BORROW WHERE BNAME='VIRAR';
Output: No. Max.Loan
1 8000
(4) Count total number of customers
Query: SELECT COUNT (CNAME) "TOTAL NUMBERS" FROM CUSTOMERS;
Output: No. Total Numbers
1 10
(5) Count total number of customer’s cities.
Query: SELECT COUNT(DISTINCT CITY) "TOTAL NUMBERS" FROM CUSTOMERS;
Output: No. Total Number
1 7
(6) Create table supplier from employee with all the columns.
Query:CREATE TABLE SUPPLIER (EMP_NO,EMP_NAME,EMP_SAL) AS SELECT EMP_NO,EMP_NAME,EMP_SAL FROM EMPLOYEE;
Output: EMP_NO EMP_NAME EMP_SAL
101 Smith 800
102 Snehal 1600
(7) Create table sup1 from employee with first two columns.
Query: CREATE TABLE SUP1 (EMP_NO,EMP_NAME) AS SELECT EMP_NO,EMP_NAME FROM EMPLOYEE;
Output: EMP_NO EMP_NAME
101 Smith
102 Snehal
(8) Create table sup2 from employee with no data
Query: CREATE TABLE SUP2 (EMP_NO,EMP_NAME,EMP_SAL) AS SELECT EMP_NO,EMP_NAME,EMP_SAL FROM EMPLOYEE WHERE 1=0;
Output: EMP_NO WMP_NAME EMP_SAL
(9) Insert the data into sup2 from employee whose second character should be ‘n’ and string should be 5 characters long in employee name field.
Query:INSERT INTO SUP2(EMP_NO,EMP_NAME,EMP_SAL) SELECT EMP_NO,EMP_NAME,EMP_SAL WHERE EMP_NAME '_n___';
Output: -------------------------------------
(10) Delete all the rows from sup1.
Query:DELETE FROM SUP1;
Output: 8 rows deleted.
(11) Delete the detail of supplier whose sup_no is 103.
Query: DELETE FROM SUP1 WHERE SUP_NO = 103;
Output: -----------------------------------
(12) Rename the table sup2.
Query:RENAME SUP1 TO SUP2;
Output: SUP2
(13) Destroy table sup1 with all the data.
Query: DROP TABLE SUP1;
Output: -----------------
(14) Update the value dept_no to 10 where second character of emp. name is ‘m’.
Query: UPDATE EMPLOYEE SET DEPT_NO=10 WHERE EMP_NAME LIKE ('_m%');
Output: EMP_NAME DEPT_NO
Rmaj 10
(15) Update the value of employee name whose employee number is 103.
Query:UPDATE EMPLOYEE SET EMP_NAME='RAM' WHERE EMP_NO=103;
Output: EMP_NAME EMP_NO
RAM 103
PRACTICAL - 5
(1) Write a query to display the current date. Label the column Date
Query: SELECT SYSDATE "DATE" FROM DUAL;
Output:
DATE
---------------
1. 11-AUG-16
(2) For each employee, display the employee number, job, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary
Query: SELECT EMP_NO, EMP_SAL, ROUND (EMP_SAL * 0.15+EMP_SAL) "NEW_SALARY" FROM EMPLOYEE;
Output:
EMP_NO EMP_SAL NEW_SALARY
------------------------------------------
1 101 800 920
2 102 1600 1840
3 103 1100 1265
4 104 3000 3450
5 105 5000 5750
(3) Modify your query no (2) to add a column that subtracts the old salary from the new salary. Label the column Increase
Query: SELECT EMP_NO , EMP_SAL, ROUND(EMP_SAL * 0.15+EMP_SAL) "NEW_SALARY" , (EMP_SAL * 0.15+EMP_SAL - EMP_SAL) "NEW_SALARY 1 " FROM EMPLOYEE;
Output:
EMP_NO EMP_SAL NEW_SALARY NEW_SALARY1
-----------------------------------------------------
1 101 800 920 120
2 102 1600 1840 240
3 103 1100 1265 165
4 104 3000 3450 450
(4) Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.
Query: SELECT INITCAP (EMP_NAME)"NAME" , LENGTH(EMP_NAME)"LENGTH" FROM EMPLOYEE WHERE EMP_NAME LIKE 'J%' OR EMP_NAME LIKE 'A%' OR EMP_NAME LIKE 'M%' ORDER BY EMP_NAME;
Output:
NAME LENGTH
-------------------------
1 Axay 4
2 Adama 5
3 Aman 4
4 Anamika 7
(5) Write a query that produces the following for each employee: <employee last name> earns <salary> monthly
Query: SELECT (EMP_NAME || 'EARNS' || EMP_SAL || 'MONTHLY') "SALARY" FROM DEPT;
Output:
SALARY
-----------------
Smith EARNS 800 MONTHLY
Snehal EARNS 1600 MONTHLY
Adama EARNS 1100 MONTHLY
Aman EARNS 3000 MONTHLY
(6) Display the name, hire date, number of months employed and day of the week on which the employee has started. Order the results by the day of the week starting with Monday.
Query: SELECT EMP_NAME , HIRE_DATE , MONTHS_BETWEEN (SYSDATE , HIRE_DATE) "MONTHS" , EXTRACT (DAY FROM HIRE_DATE) "DAY" FROM EMPLOYEE ORDER BY HIRE_DATE;
Output:
EMP_NAME HIRE_DATE MONTHS DAYS
---------------------------------------------------
1 Snehal 05-APR-93 280 5
2 Aman 17-DEC-93 248 17
3 Smith 01-JUN-99 206 1
(7) Display the hiredate of emp in a format that appears as Seventh of June 1994 12:00:00 AM.
Query:SELECT TO_CHAR(HIRE_DATE,'DDSPTH "of" MON YYYY hh:mi:ss AM') FROM EMPLOYEE;
Output:
TO_CHAR(HIRE_DATE,'DDSPTH' "of" MONYYYY hh:mi:ss AM')
-------------------------------------
1 FIRST of JUN 1999 12:OO:OO AM
2 FIRST of AUG 2003 12:OO:OO AM
3 FIRST of APR 1993 12:OO:OO AM
(8) Write a query to calculate the annual compensation of all employees (sal+comm.).
Query: SELECT EMP_NO , EMP_SAL , (EMP_SAL+EMP_COMM) "COMPENSATION" FROM EMPLOYEE;
Output:
EMP_NO EMP_SAL COMPENSATION
-----------------------------------------------
1 101 800 (null)
2 102 1600 1900
3 103 1100 1100
4 104 3000 (null)
5 105 5000 5500
6 106 2450 26950
7 107 2975 (null)
Query: SELECT SYSDATE "DATE" FROM DUAL;
Output:
DATE
---------------
1. 11-AUG-16
(2) For each employee, display the employee number, job, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary
Query: SELECT EMP_NO, EMP_SAL, ROUND (EMP_SAL * 0.15+EMP_SAL) "NEW_SALARY" FROM EMPLOYEE;
Output:
EMP_NO EMP_SAL NEW_SALARY
------------------------------------------
1 101 800 920
2 102 1600 1840
3 103 1100 1265
4 104 3000 3450
5 105 5000 5750
(3) Modify your query no (2) to add a column that subtracts the old salary from the new salary. Label the column Increase
Query: SELECT EMP_NO , EMP_SAL, ROUND(EMP_SAL * 0.15+EMP_SAL) "NEW_SALARY" , (EMP_SAL * 0.15+EMP_SAL - EMP_SAL) "NEW_SALARY 1 " FROM EMPLOYEE;
Output:
EMP_NO EMP_SAL NEW_SALARY NEW_SALARY1
-----------------------------------------------------
1 101 800 920 120
2 102 1600 1840 240
3 103 1100 1265 165
4 104 3000 3450 450
(4) Write a query that displays the employee’s names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.
Query: SELECT INITCAP (EMP_NAME)"NAME" , LENGTH(EMP_NAME)"LENGTH" FROM EMPLOYEE WHERE EMP_NAME LIKE 'J%' OR EMP_NAME LIKE 'A%' OR EMP_NAME LIKE 'M%' ORDER BY EMP_NAME;
Output:
NAME LENGTH
-------------------------
1 Axay 4
2 Adama 5
3 Aman 4
4 Anamika 7
(5) Write a query that produces the following for each employee: <employee last name> earns <salary> monthly
Query: SELECT (EMP_NAME || 'EARNS' || EMP_SAL || 'MONTHLY') "SALARY" FROM DEPT;
Output:
SALARY
-----------------
Smith EARNS 800 MONTHLY
Snehal EARNS 1600 MONTHLY
Adama EARNS 1100 MONTHLY
Aman EARNS 3000 MONTHLY
(6) Display the name, hire date, number of months employed and day of the week on which the employee has started. Order the results by the day of the week starting with Monday.
Query: SELECT EMP_NAME , HIRE_DATE , MONTHS_BETWEEN (SYSDATE , HIRE_DATE) "MONTHS" , EXTRACT (DAY FROM HIRE_DATE) "DAY" FROM EMPLOYEE ORDER BY HIRE_DATE;
Output:
EMP_NAME HIRE_DATE MONTHS DAYS
---------------------------------------------------
1 Snehal 05-APR-93 280 5
2 Aman 17-DEC-93 248 17
3 Smith 01-JUN-99 206 1
(7) Display the hiredate of emp in a format that appears as Seventh of June 1994 12:00:00 AM.
Query:SELECT TO_CHAR(HIRE_DATE,'DDSPTH "of" MON YYYY hh:mi:ss AM') FROM EMPLOYEE;
Output:
TO_CHAR(HIRE_DATE,'DDSPTH' "of" MONYYYY hh:mi:ss AM')
-------------------------------------
1 FIRST of JUN 1999 12:OO:OO AM
2 FIRST of AUG 2003 12:OO:OO AM
3 FIRST of APR 1993 12:OO:OO AM
(8) Write a query to calculate the annual compensation of all employees (sal+comm.).
Query: SELECT EMP_NO , EMP_SAL , (EMP_SAL+EMP_COMM) "COMPENSATION" FROM EMPLOYEE;
Output:
EMP_NO EMP_SAL COMPENSATION
-----------------------------------------------
1 101 800 (null)
2 102 1600 1900
3 103 1100 1100
4 104 3000 (null)
5 105 5000 5500
6 106 2450 26950
7 107 2975 (null)
PRACTICAL - 6
Aim: Displaying data from Multiple Tables (join)
(1) Give details of customers ANIL.
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='ANIL';
Output:
CNAME CITY ACTNO AMOUNT ADATE LOANNO
ANIL CALCUTTA 100 1000 1-MAR-95 201
(2) Give name of customer who are borrowers and depositors and having living city nagpur
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='NAGPUR';
Output:
CNAME
MADHURI
PRAMOD
(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 SHIVANI VIRAR
(4) Write a query to display the 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 20
Snehal Marketing 25
Aman Computer 15
(5) Create a unique listing of all jobs that are in department 30. Include the location of the department in the output.
Query:SELECT J.JOB_ID,J.JOB_NAME,J.MIN_SAL FROM JOB J WHERE MIN_SAL<(SELECT EMP_SAL FROM EMPLOYEE WHERE DEPT_NO=30);
Output:
JOB_ID JOB_NAME MIN_SAL
COMP_OP Computer operator 1500
(6) Write a query to display the employee name, department number, and department name for all employees who work in NEW YORK.
Query: SELECT E.EMP_NAME,D.DEPT_NO,D.DNAME FROM EMPLOYEE E INNER JOIN DEPT D ON E.DEPT_NO=D.DEPT_NO WHERE D.LOCATION='NEW YORK';
Output:
EMP_NAME DEPT_NO DNAME
Aman 15 Computer
Jolly 15 Computer
Meet 15 Computer
(7) Display the employee name and employee number along with their manager’s name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
Query: SELECT E.EMP_NAME "EMP" , E.EMP_NO , M.MNAME "Manager" , M.MNO "Mgr#" FROM MANAGER M INNER JOIN DEPT D ON M.MNO = D.MNO INNER JOIN EMPLOYEE E ON D.DEPT_NO=E.DEPT_NO;
Output:
EMP EMP_NO Manager Mgr#
Smith 101 Bharat 3
Snehal 102 Kaushal 2
(8) Create a query to display the name and hire date of any employee hired after employee SCOTT.
Query: SELECT EMP_NAME,HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE > (SELECT HIRE_DATE FROM EMPLOYEE WHERE EMP_NAME='Aman');
Output:
EMP_NAME HIRE_DATE
Smith 01-JUN-99
Snehal 28-MAR-05
Adama 03-AUG-03
(1) Give details of customers ANIL.
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='ANIL';
Output:
CNAME CITY ACTNO AMOUNT ADATE LOANNO
ANIL CALCUTTA 100 1000 1-MAR-95 201
(2) Give name of customer who are borrowers and depositors and having living city nagpur
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='NAGPUR';
Output:
CNAME
MADHURI
PRAMOD
(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 SHIVANI VIRAR
(4) Write a query to display the 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 20
Snehal Marketing 25
Aman Computer 15
(5) Create a unique listing of all jobs that are in department 30. Include the location of the department in the output.
Query:SELECT J.JOB_ID,J.JOB_NAME,J.MIN_SAL FROM JOB J WHERE MIN_SAL<(SELECT EMP_SAL FROM EMPLOYEE WHERE DEPT_NO=30);
Output:
JOB_ID JOB_NAME MIN_SAL
COMP_OP Computer operator 1500
(6) Write a query to display the employee name, department number, and department name for all employees who work in NEW YORK.
Query: SELECT E.EMP_NAME,D.DEPT_NO,D.DNAME FROM EMPLOYEE E INNER JOIN DEPT D ON E.DEPT_NO=D.DEPT_NO WHERE D.LOCATION='NEW YORK';
Output:
EMP_NAME DEPT_NO DNAME
Aman 15 Computer
Jolly 15 Computer
Meet 15 Computer
(7) Display the employee name and employee number along with their manager’s name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
Query: SELECT E.EMP_NAME "EMP" , E.EMP_NO , M.MNAME "Manager" , M.MNO "Mgr#" FROM MANAGER M INNER JOIN DEPT D ON M.MNO = D.MNO INNER JOIN EMPLOYEE E ON D.DEPT_NO=E.DEPT_NO;
Output:
EMP EMP_NO Manager Mgr#
Smith 101 Bharat 3
Snehal 102 Kaushal 2
(8) Create a query to display the name and hire date of any employee hired after employee SCOTT.
Query: SELECT EMP_NAME,HIRE_DATE FROM EMPLOYEE WHERE HIRE_DATE > (SELECT HIRE_DATE FROM EMPLOYEE WHERE EMP_NAME='Aman');
Output:
EMP_NAME HIRE_DATE
Smith 01-JUN-99
Snehal 28-MAR-05
Adama 03-AUG-03
PRACTICAL -7
SELECT Aim: To apply the concept of Aggregating Data using Group functions.
[1] List total deposit of customer having account date after 1-jan-96.
Query: SELECT SUM(AMOUNT) "AMOUNT" FROM DEPOSIT WHERE ADATE > 'A-JAN-96';
Output:
AMOUNT
10000
(2) List total deposit of customers living in city Nagpur.
Query: SELECT SUM(D.AMOUNT) "AMOUNT" FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME WHERE CITY='NAGPUR';
Output:
AMOUNT
4200
(3) List maximum deposit of customers living in bombay.
Query: SELECT MAX(D.AMOUNT)"AMOUNT" FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME WHERE CITY='BOMBAY';
Output:
AMOUNT
5000
(4) 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.
Query: SELECT ROUND (AVG(EMP_SAL)) "Average", MAX(EMP_SAL) "Maximum" , MIN(EMP_SAL) "Minimum" SUM(EMP_SAL) "Sum" FROM EMPLOYEE;
Output:
Average Maximum Minimum Sum
2418 5000 800 16925
(5) Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.
Query: SELECT MAX(EMP_SAL)-MIN(EMP_SAL) "DIFFERENCE" FROM EMPLOYEE;
Output:
DIFFERENCE
4200
(6) Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998
Query: SELECT COUNT (EMP_SAL) "COUNT" FROM EMPLOYEE WHERE EXTRACT (YEAR FROM HIRE_DATE) = 1995 OR EXTRACT (YEAR FROM HIRE_DATE) = 2000;
Output:
COUNT
2
(7) Find the average salaries for each department without displaying the respective department numbers.
Query: SELECT AVG (EMP_SAL) "AVG" , DEPT_NO FROM EMPLOYEE GROUP BY DEPT_NO;
Output:
AVG DEPT_NO
1600 25
2975 30
950 20
3000 15
3725 10
(8) Write a query to display the total salary being paid to each job title, within each department.
Query: SELECT SUM(EMP_SAL) "SUM" , DEPT_NO FROM EMPLOYEE GROUP BY (DEPT_NO);
Output:
SUM DEPT_NO
1600 25
2450 30
1100 20
11250 10
(9) Find the average salaries > 2000 for each department without displaying the respective department numbers.
Query: SELECT ROUND (AVG(EMP_SAL)) "EMP_SAL" FROM EMPLOYEE WHERE (EMP_SAL>2000) GROUP BY DEPT_NO;
Output:
EMP_SAL
2475
4333
3725
(10) Display the job and MAX salary for each job with a MAX salary amount exceeding 3000, sorts the list by the total salary.
Query: SELECT JOB_NAME , MAX_SAL FROM JOB WHERE MAX_SAL > 3000 ORDER BY (MAX_SAL);
Output:
JOB_NAME MAX_SAL
9000 Account
10000 Programmer
12000 Finance Manager
15000 Marketing Manager
17000 Lecture
(11) List the branches having sum of deposit more than 5000 and located in city bombay.
Query: SELECT SUM(AMOUNT) FROM BRANCH B INNER JOIN DEPOSIT D ON B.BNAME=D.BNAME WHERE D.AMOUNT>5000 AND B.CITY='BOMBAY';
Output:
SUM(AMOUNT)
7000
[1] List total deposit of customer having account date after 1-jan-96.
Query: SELECT SUM(AMOUNT) "AMOUNT" FROM DEPOSIT WHERE ADATE > 'A-JAN-96';
Output:
AMOUNT
10000
(2) List total deposit of customers living in city Nagpur.
Query: SELECT SUM(D.AMOUNT) "AMOUNT" FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME WHERE CITY='NAGPUR';
Output:
AMOUNT
4200
(3) List maximum deposit of customers living in bombay.
Query: SELECT MAX(D.AMOUNT)"AMOUNT" FROM CUSTOMERS C INNER JOIN DEPOSIT D ON C.CNAME=D.CNAME WHERE CITY='BOMBAY';
Output:
AMOUNT
5000
(4) 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.
Query: SELECT ROUND (AVG(EMP_SAL)) "Average", MAX(EMP_SAL) "Maximum" , MIN(EMP_SAL) "Minimum" SUM(EMP_SAL) "Sum" FROM EMPLOYEE;
Output:
Average Maximum Minimum Sum
2418 5000 800 16925
(5) Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.
Query: SELECT MAX(EMP_SAL)-MIN(EMP_SAL) "DIFFERENCE" FROM EMPLOYEE;
Output:
DIFFERENCE
4200
(6) Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998
Query: SELECT COUNT (EMP_SAL) "COUNT" FROM EMPLOYEE WHERE EXTRACT (YEAR FROM HIRE_DATE) = 1995 OR EXTRACT (YEAR FROM HIRE_DATE) = 2000;
Output:
COUNT
2
(7) Find the average salaries for each department without displaying the respective department numbers.
Query: SELECT AVG (EMP_SAL) "AVG" , DEPT_NO FROM EMPLOYEE GROUP BY DEPT_NO;
Output:
AVG DEPT_NO
1600 25
2975 30
950 20
3000 15
3725 10
(8) Write a query to display the total salary being paid to each job title, within each department.
Query: SELECT SUM(EMP_SAL) "SUM" , DEPT_NO FROM EMPLOYEE GROUP BY (DEPT_NO);
Output:
SUM DEPT_NO
1600 25
2450 30
1100 20
11250 10
(9) Find the average salaries > 2000 for each department without displaying the respective department numbers.
Query: SELECT ROUND (AVG(EMP_SAL)) "EMP_SAL" FROM EMPLOYEE WHERE (EMP_SAL>2000) GROUP BY DEPT_NO;
Output:
EMP_SAL
2475
4333
3725
(10) Display the job and MAX salary for each job with a MAX salary amount exceeding 3000, sorts the list by the total salary.
Query: SELECT JOB_NAME , MAX_SAL FROM JOB WHERE MAX_SAL > 3000 ORDER BY (MAX_SAL);
Output:
JOB_NAME MAX_SAL
9000 Account
10000 Programmer
12000 Finance Manager
15000 Marketing Manager
17000 Lecture
(11) List the branches having sum of deposit more than 5000 and located in city bombay.
Query: SELECT SUM(AMOUNT) FROM BRANCH B INNER JOIN DEPOSIT D ON B.BNAME=D.BNAME WHERE D.AMOUNT>5000 AND B.CITY='BOMBAY';
Output:
SUM(AMOUNT)
7000
PRACTICAL -9
AIM: Manipulating Data
(1) Give 10% interest to all depositors.
Query: SELECT ACTNO,CNAME,BNAME, (AMOUNT *0.10+AMOUNT) "NEW AMOUNT" FROM DEPOSIT;
Output:
ACTNO CNAME BNAME NEW AMOUNT
100 ANIL VRCE 1100
101 SUNIL AJNI 5500
102 MEHUL KAROLBAGH 3850
(2) Give 10% interest to all depositors having branch vrce
Query: SELECT ACTNO,CNAME,BNAME, (AMOUNT * 0.10 +AMOUNT) "NEW AMOUNT" FROM DEPOSIT WHERE BNAME='VRCE';
Output:
ACTNO CNAME BNAME NEW AMOUNT
100 ANIL VRCE 1100
(3) Give 10% interest to all depositors living in nagpur and having branch city bombay.
Query: SELECT D.ACTNO , D.CNAME , D.BNAME , (D.AMOUNT * 0.10 + D.AMOUNT) "NEW AMOUNT" FROM DEPOSIT D INNER JOIN CUSTOMERS C ON C.CNAME = D.CNAME INNER JOIN BRANCH B ON D.BNAME=B.BNAME WHERE B.CITY = 'BOMBAY' AND C.CITY = 'NAGPUR';
Output:
*WAIT OUTPUT IN COMING SOON*
(4) Write a query which changes the department number of all employees with empno 105 job to employee 201 current department number.
Query: UPDATE EMPLOYEE SET EMP_NO = 201 WHERE EMP_NO = 105;
Output:
EMP_NO EMP_NAME EMP_COM
201 Anita 50000
(5) Transfer 10 Rs from account of anil to sunil if both are having same branch.
Query: SELECT BNAME , (CASE WHEN CNAME IN ('SUNIL') THEN AMOUNT +10 ELSE AMOUNT-10 END) FROM DEPOSIT WHERE BNAME = (SELECT BNAME FROM DEPOSIT WHERE CNAME='ANIL');
Output:
BNAME AMOUNT
VRCE 990
VRCE 5010
(6) Give 100 Rs more to all depositors if they are maximum depositors in their respective branch.
Query: SELECT CNAME , (AMOUNT+100) FROM DEPOSIT WHERE AMOUNT =(SELECT MAX(AMOUNT) FROM DEPOSIT );
Output:
CNAME AMOUNT
MINU 7100
(7) Delete depositors of branches having number of customers between 1 to 3.
Query: DELETE FROM DEPOSIT WHERE BNAME=(SELECT BNAME FROM (SELECT BNAME , COUNT (CNAME) "D" FROM DEPOSIT GROUP BY (BNAME))B WHERE B.D IN (2));
Output:
ACTNO CNAME BNAME
102 MEHUL KAROLBAGH
104 MADHURI CHANDI
(8) Delete deposit of ANIL.
Query: DELETE FROM DEPOSIT WHERE CNAME='ANIL';
Output:
DNO ACTNO CNAME BNAME AMOUNT ADATE
2 101 SUNIL AJNI 5000 04-JAN-96
3 102 MEHUL KAROLBAGH 3500 17-NOV-95
(9) Delete borrower of branches having average loan less than 1001.
Query: DELETE FROM BORROW WHERE AMOUNT < 1001;
Output:
LOANNO CNAME BNAME AMOUNT
206 MEHUL AJNI 5000
311 SUNIL DHARAMPETH 3000
321 MADHURI ANDHERI 2000
(1) Give 10% interest to all depositors.
Query: SELECT ACTNO,CNAME,BNAME, (AMOUNT *0.10+AMOUNT) "NEW AMOUNT" FROM DEPOSIT;
Output:
ACTNO CNAME BNAME NEW AMOUNT
100 ANIL VRCE 1100
101 SUNIL AJNI 5500
102 MEHUL KAROLBAGH 3850
(2) Give 10% interest to all depositors having branch vrce
Query: SELECT ACTNO,CNAME,BNAME, (AMOUNT * 0.10 +AMOUNT) "NEW AMOUNT" FROM DEPOSIT WHERE BNAME='VRCE';
Output:
ACTNO CNAME BNAME NEW AMOUNT
100 ANIL VRCE 1100
(3) Give 10% interest to all depositors living in nagpur and having branch city bombay.
Query: SELECT D.ACTNO , D.CNAME , D.BNAME , (D.AMOUNT * 0.10 + D.AMOUNT) "NEW AMOUNT" FROM DEPOSIT D INNER JOIN CUSTOMERS C ON C.CNAME = D.CNAME INNER JOIN BRANCH B ON D.BNAME=B.BNAME WHERE B.CITY = 'BOMBAY' AND C.CITY = 'NAGPUR';
Output:
*WAIT OUTPUT IN COMING SOON*
(4) Write a query which changes the department number of all employees with empno 105 job to employee 201 current department number.
Query: UPDATE EMPLOYEE SET EMP_NO = 201 WHERE EMP_NO = 105;
Output:
EMP_NO EMP_NAME EMP_COM
201 Anita 50000
(5) Transfer 10 Rs from account of anil to sunil if both are having same branch.
Query: SELECT BNAME , (CASE WHEN CNAME IN ('SUNIL') THEN AMOUNT +10 ELSE AMOUNT-10 END) FROM DEPOSIT WHERE BNAME = (SELECT BNAME FROM DEPOSIT WHERE CNAME='ANIL');
Output:
BNAME AMOUNT
VRCE 990
VRCE 5010
(6) Give 100 Rs more to all depositors if they are maximum depositors in their respective branch.
Query: SELECT CNAME , (AMOUNT+100) FROM DEPOSIT WHERE AMOUNT =(SELECT MAX(AMOUNT) FROM DEPOSIT );
Output:
CNAME AMOUNT
MINU 7100
(7) Delete depositors of branches having number of customers between 1 to 3.
Query: DELETE FROM DEPOSIT WHERE BNAME=(SELECT BNAME FROM (SELECT BNAME , COUNT (CNAME) "D" FROM DEPOSIT GROUP BY (BNAME))B WHERE B.D IN (2));
Output:
ACTNO CNAME BNAME
102 MEHUL KAROLBAGH
104 MADHURI CHANDI
(8) Delete deposit of ANIL.
Query: DELETE FROM DEPOSIT WHERE CNAME='ANIL';
Output:
DNO ACTNO CNAME BNAME AMOUNT ADATE
2 101 SUNIL AJNI 5000 04-JAN-96
3 102 MEHUL KAROLBAGH 3500 17-NOV-95
(9) Delete borrower of branches having average loan less than 1001.
Query: DELETE FROM BORROW WHERE AMOUNT < 1001;
Output:
LOANNO CNAME BNAME AMOUNT
206 MEHUL AJNI 5000
311 SUNIL DHARAMPETH 3000
321 MADHURI ANDHERI 2000