ASSIGNMENT NO :- 03
SQL*Plus: Release 21.0.0.0.0 - Production on Sun Aug 11 00:08:38 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Last Successful login time: Tue Aug 06 2024 08:55:25 +05:30
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
1)1)Create table Department with fields deptno, dname, location.
SQL> create table Department1(Deptno int,Dname varchar(15),location varchar(15));
Table created.
2)Insert the following records by using any one method
Deptno Dname Location
10 Accounting Mumbai
20 Research Pune
30 Sales Nashik
40 Operations Nagpur
SQL> insert into Department1 values(10,'Accounting','Mumbai');
1 row created.
SQL> insert into Department1 values(20,'Research','Pune');
1 row created.
SQL> insert into Department1 values(30,'Sales','Nashik');
1 row created.
SQL> insert into Department1 values(40,'Operations','Nagpur');
1 row created.
3)List the department information.
SQL> select * from Department1;
DEPTNO DNAME LOCATION
---------- --------------- ---------------
10 Accounting Mumbai
20 Research Pune
30 Sales Nashik
40 Operations Nagpur
4) Create table employee as shown below.
SQL> create table employeee(Empno int primary key,Ename varchar(20),job varchar(15),Mgr
int,Joined_dat date,Salary int,Commission int,Deptno int,Address varchar(20));
Table created.
SQL> insert into employeee values (1001,'Nilesh
joshi','Clerk',1005,'17/dec/95',2800,600,20,'Nashik');
1 row created.
SQL> insert into employeee values (1002,'Avinash
pawar','Salesman',1003,'20/feb/96',5000,1200,30,'Nagpur');
1 row created.
SQL> insert into employeee values (1003,'Amit
kumar','Manager',1004,'2/apr/86',2000,null,30,'Pune');
1 row created.
SQL> insert into employeee values (1004,'Nitin
kurkarni','President',null,'19/apr/86',50000,null,10,'Mumbai');
1 row created.
SQL> insert into employeee values (1005,'Niraj sharma','Analyst',1003,'3/dec/98', 12000,
null,20,'Satara');
1 row created.
SQL> insert into employeee values (1006,'Pushkar
deshpande','Salesman',1003,'1/sep/96',6500,1500,30,'Pune');
1 row created.
SQL> insert into employeee values(1007,'Sumit
patil','Manager',1004,'1/may/91',25000,null,20,'Mumbai');
1 row created
SQL> insert into employeee values (1008,'Ravi
sawant','Analyst',1007,'17/nov/95',10000,null,null,'Amaravati');
1 row created.
5) Write a query to display employee information. Write a name of column explicitly.
SQL> select * from employeee;
EMPNO ENAME JOB MGR JOINED_DA SALARY COMMISSION DEPTNO
ADDRESS
---------- ------------ ---------- ------ -------- ----- -------------
1001 Nilesh joshi Clerk 1005 17-DEC-95 2800 600 20 Nashik
1002 Avinash pawar Salesman 1003 20-FEB-96 5000 1200 30 Nagpur
1003 Amit kumar Manager 1004 02-APR-86 2000 -- 30 Pune
1004 Nitin kurkarni President ---- 19-APR-86 50000 -- 10 Mumbai
1005 Niraj sharma Analyst 1003 03-DEC-98 12000 -- 20 Satara
1006 Pushkar deshpande Salesm 1003 01-SEP-96 6500 1500 30 Pune
1007 Sumit patil Manager 1004 01-MAY-91 25000 -- 20 Mumbai
1008 Ravi sawant Analyst 1007 17-NOV-95 10000 -- -- Amaravati
8 rows selected.
6) Create a query to display unique jobs from the table.
SQL> select distinct(job) from employeee;
JOB
---------------
Clerk
Salesman
Manager
President
Analyst
7) Change the location of dept 40 to Banglore instead of Nagpur.
SQL> update Department1 set location='Banglore' where deptno=40;
1 row updated.
SQL> select * from Department1;
DEPTNO DNAME LOCATION
---------- --------------- ---------------
10 Accounting Mumbai
20 Research Pune
30 Sales Nashik
40 Operations Banglore
8) Change the name of the employees 1003 to Nikhil Gosavi.
SQL> update employeee set ename = 'nikhil gosavi' where empno =1003;
1 row updated.
SQL> select * from employeee;
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1001 Nilesh joshi Clerk 1005 17-DEC-95 2800
600 20 Nashik
1002 Avinash pawar Salesman 1003 20-FEB-96 5000
1200 30 Nagpur
1003 nikhil gosavi Manager 1004 02-APR-86 2000
30 Pune
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1004 Nitin kurkarni President 19-APR-86 50000
10 Mumbai
1005 Niraj sharma Analyst 1003 03-DEC-98 12000
20 Satar
1006 Pushkar deshpande Salesman 1003 01-SEP-96 6500
1500 30 Pune
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1007 Sumit patil Manager 1004 01-MAY-91 25000
20 Mumbai
1008 Ravi sawant Analyst 1007 17-NOV-95 10000
Amaravati
8 rows selected.
9) Delete Pushkar deshpande from employee table.
SQL> delete from employeee where ename = 'Pushkar deshpande';
1 row deleted.
SQL> select * from employeee;
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1001 Nilesh joshi Clerk 1005 17-DEC-95 2800
600 20 Nashik
1002 Avinash pawar Salesman 1003 20-FEB-96 5000
1200 30 Nagpur
1003 nikhil gosavi Manager 1004 02-APR-86 2000
30 Pune
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1004 Nitin kurkarni President 19-APR-86 50000
10 Mumbai
1005 Niraj sharma Analyst 1003 03-DEC-98 12000
20 Satara
1007 Sumit patil Manager 1004 01-MAY-91 25000
20 Mumbai
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
--------- ---------- --------------------
1008 Ravi sawant Analyst 1007 17-NOV-95 10000
Amaravati
7 rows selected.
10) Create a table department_temp table from deptarment table, only create the structure not
content.
SQL> create table Department1_temp as select * from Department1 where 1 = 2;
Table created.
SQL> desc Department1_temp;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(38)
DNAME VARCHAR2(15)
LOCATION VARCHAR2(15)
11) Insert rows into department_temp table from deptarment table
SQL> insert into Department1_temp select * from Department1;
4 rows created.
SQL> select * from Department1_temp;
DEPTNO DNAME LOCATION
---------- --------------- ---------------
10 Accounting Mumbai
20 Research Pune
30 Sales Nashik
40 Operations Banglore
12) Display the list of employee whose salary between 5000 and 20000.
SQL> select * from employeee where salary between 5000 and 20000;
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1002 Avinash pawar Salesman 1003 20-FEB-96 5000
1200 30 Nagpur
1005 Niraj sharma Analyst 1003 03-DEC-98 12000
20 Satara
1008 Ravi sawant Analyst 1007 17-NOV-95 10000
Amaravati
13)Display the list of employee excluding job title as ‘salesman’.
SQL> select * from employeee where job!='Salesman';
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1001 Nilesh joshi Clerk 1005 17-DEC-95 2800
600 20 Nashik
1003 nikhil gosavi Manager 1004 02-APR-86 2000
30 Pune
1004 Nitin kurkarni President 19-APR-86 50000
10 Mumbai
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1005 Niraj sharma Analyst 1003 03-DEC-98 12000
20 Satara
1007 Sumit patil Manager 1004 01-MAY-91 25000
20 Mumbai
1008 Ravi sawant Analyst 1007 17-NOV-95 10000
Amaravati
6 rows selected.
14) Display all those employees whose job title is either ‘manager’ or ‘analyst’(write by using OR &
IN operator).
SQL> select * from employeee where job='Manager' or job= 'Analyst';
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1003 nikhil gosavi Manager 1004 02-APR-86 2000
30 Pune
1005 Niraj sharma Analyst 1003 03-DEC-98 12000
20 Satara
1007 Sumit patil Manager 1004 01-MAY-91 25000
20 Mumbai
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1008 Ravi sawant Analyst 1007 17-NOV-95 10000
Amaravati
15) Display the employee name & department number of all employees in dept 10,20,30 & 40.
SQL> select Empno,Deptno from employeee where Deptno in (10,20,30,40);
EMPNO DEPTNO
---------- ----------
1001 20
1002 30
1003 30
1004 10
1005 20
1007 20
6 rows selected.
16) Display the employee number, name, job & commission of all employees who do not get any
commission.
SQL> select Empno, Ename,job commission from employeee where commission is null;
EMPNO ENAME COMMISSION
---------- -------------------- ---------------
1003 nikhil gosavi Manager
1004 Nitin kurkarni President
1005 Niraj sharma Analyst
1007 Sumit patil Manager
1008 Ravi sawant Analyst
17) Display the name & salary of all employees whose salary not in the range of 5000 & 10000.
SQL> select Ename,Salary from employeee where Salary not between 5000 and 10000;
ENAME SALARY
-------------------- ----------
Nilesh joshi 2800
nikhil gosavi 2000
Nitin kurkarni 50000
Niraj sharma 12000
Sumit patil 25000
18) Find all names & joined date of employees whose names starts with ‘A’.
SQL> select ename ,Joined_dat from employeee where ename like 'A%';
ENAME JOINED_DA
-------------------- ---------
Avinash pawar 20-FEB-96
19) Find all names of employees having ‘i’ as a second letter in their names.
SQL> select ename from employeee where ename like '_i%';
ENAME
--------------------
Nilesh joshi
nikhil gosavi
Nitin kurkarni
Niraj sharma
20) Find employee number, name of employees whose commission is not null.
SQL> select empno,ename from employeee where commission is not null;
EMPNO ENAME
---------- --------------------
1001 Nilesh joshi
1002 Avinash pawar
21) Display all employee information in the descending order of employee number.
SQL> select * from employeee order by empno;
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1001 Nilesh joshi Clerk 1005 17-DEC-95 2800
600 20 Nashik
1002 Avinash pawar Salesman 1003 20-FEB-96 5000
1200 30 Nagpur
1003 nikhil gosavi Manager 1004 02-APR-86 2000
30 Pune
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1004 Nitin kurkarni President 19-APR-86 50000
10 Mumbai
1005 Niraj sharma Analyst 1003 03-DEC-98 12000
20 Satara
1007 Sumit patil Manager 1004 01-MAY-91 25000
20 Mumbai
EMPNO ENAME JOB MGR JOINED_DA SALARY
---------- -------------------- --------------- ---------- --------- ----------
COMMISSION DEPTNO ADDRESS
---------- ---------- --------------------
1008 Ravi sawant Analyst 1007 17-NOV-95 10000
Amaravati
7 rows selected.
22) Display the minimum, maximum, sum & average salary of each job type
SQL> select max(Salary),min(Salary),sum(Salary) job from employeee group by job;
MAX(SALARY) MIN(SALARY) JOB
----------- ----------- ----------
2800 2800 2800
5000 5000 5000
25000 2000 27000
50000 50000 50000
12000 10000 22000
23) Write a query to display the number of employee with the same department.
SQL> select count(empno) Deptno from employeee group by deptno;
DEPTNO
----------
24) Select employee number, ename according to the annual salary in ascending order.
SQL> select count (empno), Deptno from employeee group by deptno;
COUNT(EMPNO) DEPTNO
------------ ----------
3 20
2 30
1 10
SQL> select empno, ename,12*salary "annual salary" from employeee order by'annualsalary';
EMPNO ENAME annual salary
---------- -------------------- -------------
1001 Nilesh joshi 33600
1002 Avinash pawar 60000
1003 nikhil gosavi 24000
1004 Nitin kurkarni 600000
1005 Niraj sharma 144000
1007 Sumit patil 300000
1008 Ravi sawant 120000
7 rows selected.
25) Find the department number, maximum salary where the maximum salary is greater than 5000.
SQL> select deptno,max(salary) from employeee group by deptno having max(salary)>5000;
DEPTNO MAX(SALARY)
---------- -----------
20 25000
10 50000
10000
26) Find all distinct column values from employee & department table.
SQL> select deptno from employeee union select deptno from department1;
DEPTNO
----------
20
30
10
40
27) Find all column values with duplicate from employee & department table.
28 Find all column values which are common in both employee & department table.
SQL> select deptno from employeee union select deptno from department1;
DEPTNO
----------
20
30
30
10
20
20
28 Find all column values which are common in both employee & department table.
SQL> select deptno from employeee union all select deptno from department1;
DEPTNO
----------
20
30
30
10
20
20
10
20
30
40
11 rows selected.
29 Find all distinct column values present in employee but not in department table.
SQL> select deptno from employeee minus select deptno from department1;
DEPTNO
----------
30 Display the number of employees in the department 30 who can earn a commission.
SQL> select count (empno) from employeee where deptno = 30 and commission is not null;
COUNT(EMPNO)
------------
