=============================== SQL QUERIES========================================
PRACTICE ON THE FOLLOWING TABLES
EMP -->employee details table
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> DESC EMP;
Name
--------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
1 QUERY
DISPLAY ALL THE DETAILS OF ALL EMPLOYEES
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
2nd query
Display the department information from the department table ?
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> desc dept;
Name
---------------
DEPTNO
DNAME
LOC
3rd query
Display the name and job of all the employees ?
1* select ename,job from emp
SQL> /
ENAME JOB
---------- ---------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected.
4th query
Display the name and salary for all the employees ?
SQL> select ename,sal from emp;
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
5th query
display the employee name and annual salary for all the employees ?
SQL> select ename,12*(sal+nvl(comm,0)) as "annual sal" from emp
/
ENAME annual sal
---------- ----------
SMITH 9600
ALLEN 22800
WARD 21000
JONES 35700
MARTIN 31800
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
14 rows selected.
6th query
display the employee no and total salary for all the aemployees
SQL> select empno,ename,sal,comm,sal+nvl(comm,0) as "total sal" from emp;
EMPNO ENAME SAL COMM total sal
---------- ---------- ---------- ---------- ----------
7369 SMITH 800 800
7499 ALLEN 1600 300 1900
7521 WARD 1250 500 1750
7566 JONES 2975 2975
7654 MARTIN 1250 1400 2650
7698 BLAKE 2850 2850
7782 CLARK 2450 2450
7788 SCOTT 3000 3000
7839 KING 5000 5000
7844 TURNER 1500 0 1500
7876 ADAMS 1100 1100
7900 JAMES 950 950
7902 FORD 3000 3000
7934 MILLER 1300 1300
14 rows selected.
7th query
display the names of all the employees who are working in dept no 10 ?
SQL> select empno,ename,sal,comm,deptno from emp where deptno='10';
EMPNO ENAME SAL COMM DEPTNO
---------- ---------- ---------- ---------- ----------
7782 CLARK 2450 10
7839 KING 5000 10
7934 MILLER 1300 10
8th query
display the all the names of employees who are working as clerks and drawing the
sal more than 3000 ?
SQL>select empno,ename,sal,comm,deptno from emp where job='CLERK' and sal > 3000
no rows selected
9th query
display the names of all the employee name and name who are earning comm ?
SQL> select empno,ename from emp where comm is not null;
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
10th query
display the employee no and name who do not earn any comm ?
SQL> select empno,ename from emp where comm is null;
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
10 rows selected.
11th query
display the names of employees who are working as clerks ,salesman or analyst and
drawing a salary more than 3000 ?
SQL> select ename,empno from emp where job='CLERK' OR JOB='SALESMAN' OR JOB='ANALYST'
AND SAL>3000
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
MARTIN 7654
TURNER 7844
ADAMS 7876
JAMES 7900
MILLER 7934
8 rows selected.
12TH QUERY
DISPLAY THE NAMES OF THE EMPLOYEES WHO ARE WORKING IN THE COMPANY FOR THE PAST 5 YEARS
SQL> SELECT ENAME,EMPNO FROM EMP WHERE TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(HIREDATE,'YYYY')>=5;
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566
MARTIN 7654
BLAKE 7698
CLARK 7782
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
14 rows selected.
13TH QUERY
display all the employees who have joined the company befor 30-JUN-90 OR AFTER 31-DEC-90
SQL> SELECT ENAME,EMPNO FROM EMP WHERE HIREDATE BETWEEN '30-JUN-90' AND '31-DEC-90';
no rows selected
14th display the current date
SQL> select sysdate from dual;
SYSDATE
---------
14-JUL-12
15th to get the time stamp
SQL> select systimestamp from dual;
SYSTIMESTAMP
-------------------------------------------------
14-JUL-12 06.08.57.881000 PM +05:30
16th query
display the list of users in your database
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SCOTT 54 30-AUG-05
MGMT_VIEW 53 30-AUG-05
MDDATA 50 30-AUG-05
SYSMAN 51 30-AUG-05
MDSYS 46 30-AUG-05
SI_INFORMTN_SCHEMA 45 30-AUG-05
ORDPLUGINS 44 30-AUG-05
ORDSYS 43 30-AUG-05
OLAPSYS 47 30-AUG-05
ANONYMOUS 39 30-AUG-05
XDB 38 30-AUG-05
CTXSYS 36 30-AUG-05
EXFSYS 34 30-AUG-05
WMSYS 25 30-AUG-05
DBSNMP 24 30-AUG-05
TSMSYS 21 30-AUG-05
DMSYS 35 30-AUG-05
DIP 19 30-AUG-05
OUTLN 11 30-AUG-05
SYSTEM 5 30-AUG-05
SYS 0 30-AUG-05
17th query
display the names of all the tables present in current_user ?
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TBL_NEST TABLE
PNUM TABLE
ORDER_MASTER TABLE
ORD_NT_TAB TABLE
BIN$/uITtvy1RYqYBEy/D6A4Gw==$0 TABLE ---->droped tables present in recycle,
we can flash back the query
BIN$yvG6FJmfS2WY7rIVVq2fHQ==$0 TABLE
BIN$NZ+wxPeUQ72R87M92KErqA==$0 TABLE
11 rows selected.
18th query
display the name of the current user
SQL> show user;
USER is "SCOTT"
19th query
display the names of the employees working in the department
numbers 10 or 20 or 40 or employees working as CLERKS,SALESMAN,ANALYST.
SQL> SELECT ENAME,EMPNO FROM EMP WHERE DEPTNO IN (10,20,40) OR JOB
IN ('CLERKS','SALESMAN','ANALYST'
);
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566
MARTIN 7654
CLARK 7782
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
FORD 7902
MILLER 7934
12 rows selected.
20TH QUERY
Display names of the employees whose name starting with alphabet S ?
SQL> select ename,empno from emp where ename like 'S%'
ENAME EMPNO
---------- ----------
SMITH 7369
SCOTT 7788
21st query
Display names of the employees whose name ending with alphabet S ?
SQL> select ename from emp where ename like '%K';
ENAME
----------
CLARK
22nd query
Display the names of the employees whoes name is exactly five charecters in length
SQL> select ename from emp where ename like '_____'; --5 under scores
or
select ename from emp where length(ename)=5 ;
ENAME
----------
SMITH
ALLEN
JONES
BLAKE
CLARK
SCOTT
ADAMS
JAMES
8 rows selected.
23rd query
Disply the employees whose employee names have second alphabet A in their names
SQL> select ename from emp where ename like '_A%';
ENAME
----------
WARD
MARTIN
JAMES
24th query
Display the names of the employees who are not working as MANAGERS
SQL> select ename,empno from emp where job<>'MANAGER';
or
select ename,empno from emp where job NOT IN('MANAGER');
ENAME EMPNO
---------- ----------
SMITH 7369
ALLEN 7499
WARD 7521
MARTIN 7654
SCOTT 7788
KING 7839
TURNER 7844
ADAMS 7876
JAMES 7900
FORD 7902
MILLER 7934
11 rows selected.
25th query
Display the names of employees who are not working as SALESMAN or
CLERK or ANALYST
SQL> select ename,empno from emp where job not in ('SALESMAN','CLERK','ANALYST');
ENAME EMPNO
---------- ----------
JONES 7566
BLAKE 7698
CLARK 7782
KING 7839
26th query
display all the rows from emp table. the system should wait after
every screen full of information
SQL>set pause on
27th query
Disply the total number of employees working in the company
SQL> select count(*) from emp;
COUNT(*)
----------
14
28th query
Display the total sal being paid for all the employees
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
29th query
disply the maximum salary from the emp table ?
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
30th query
Display the minimum salary from the emp table ?
SQL> select min(sal) from emp;
MIN(SAL)
----------
800
31st query
Disply the average sal from the emp table ?
SQL> select avg(sal) from emp;
AVG(SAL)
----------
2073.21429
32nd query
Disply the maximum sal paid being paid to the CLERK
SQL> select max(sal) from emp where job='CLERK';
MAX(SAL)
----------
1300
33RD QUERY
Display the maximum sal being paid to the department number 20 ?
SQL> select max(sal) from emp where deptno=20;
MAX(SAL)
----------
3000
34th query
Display the minimum salary being paid to the SALESMAN ?
SQL> select min(sal) from emp where job='SALESMAN';
MIN(SAL)
----------
1250
35TH QUERY
Display the average salary drawn by the MANAGERS ?
SQL> select max(sal) from emp where job='MANAGER';
MAX(SAL)
----------
2975
36TH QUERY
Display the total sal drawn by the ANALYST working in the
department number 40 ?
SQL> select sum(sal) from emp where job='ANALYST' and deptno=40;
SUM(SAL)
----------
37th query
Display the average sal drawn by MANAGERS
SQL> select avg(sal) from emp where job='MANAGER';
AVG(SAL)
----------
2758.33333
38TH QUERY
Display the names of employees in order of salary i.e the name of
the employee earning lowest salary should appear first ?
SQL> select ename,empno,sal from emp order by sal;
ENAME EMPNO SAL
---------- ---------- ----------
SMITH 7369 800
JAMES 7900 950
ADAMS 7876 1100
WARD 7521 1250
MARTIN 7654 1250
MILLER 7934 1300
TURNER 7844 1500
ALLEN 7499 1600
CLARK 7782 2450
BLAKE 7698 2850
JONES 7566 2975
SCOTT 7788 3000
FORD 7902 3000
KING 7839 5000
14 rows selected.
39th query
Display the names of the employees in desendind order of slary ?
SQL> select ename,empno from emp order by ename;
ENAME EMPNO
---------- ----------
ADAMS 7876
ALLEN 7499
BLAKE 7698
CLARK 7782
FORD 7902
JAMES 7900
JONES 7566
KING 7839
MARTIN 7654
MILLER 7934
SCOTT 7788
SMITH 7369
TURNER 7844
WARD 7521
14 rows selected.
40th query
display the empno,ename,deptno,sal sort the output first base on name
and within name by deptno and within deptno by sal ?
SQL> select empno,ename,deptno,sal from emp order by ename,deptno,sal;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
7876 ADAMS 20 1100
7499 ALLEN 30 1600
7698 BLAKE 30 2850
7782 CLARK 10 2450
7902 FORD 20 3000
7900 JAMES 30 950
7566 JONES 20 2975
7839 KING 10 5000
7654 MARTIN 30 1250
7934 MILLER 10 1300
7788 SCOTT 20 3000
7369 SMITH 20 800
7844 TURNER 30 1500
7521 WARD 30 1250
14 rows selected.
41st query
Display the name of the employee along with their annual salary (sal*12).
the name of the employee earning highest annual sal should appear first ?
SQL> select ename,sal*12 as "ann sal" from emp order by sal desc;
ENAME ann sal
---------- ----------
KING 60000
FORD 36000
SCOTT 36000
JONES 35700
BLAKE 34200
CLARK 29400
ALLEN 19200
TURNER 18000
MILLER 15600
WARD 15000
MARTIN 15000
ADAMS 13200
JAMES 11400
SMITH 9600
14 rows selected.
42nd query
Display name,salary,hra,pf,da,total salary for each employee.
the output should be in the order of total salary,hra 15% of salary,
da 10% of salary,pf 5% salary,total salary will be (salary+hra+da)-pf ?
SQL> select ename,sal,sal/100*15 as hra,sal/100*5 as pf,sal/100*10
as da,sal+sal/100*5+sal/100*10-sa
l/100*5 as total from emp;
ENAME SAL HRA PF DA TOTAL
---------- ---------- ---------- ---------- ---------- ----------
SMITH 800 120 40 80 880
ALLEN 1600 240 80 160 1760
WARD 1250 187.5 62.5 125 1375
JONES 2975 446.25 148.75 297.5 3272.5
MARTIN 1250 187.5 62.5 125 1375
BLAKE 2850 427.5 142.5 285 3135
CLARK 2450 367.5 122.5 245 2695
SCOTT 3000 450 150 300 3300
KING 5000 750 250 500 5500
TURNER 1500 225 75 150 1650
ADAMS 1100 165 55 110 1210
JAMES 950 142.5 47.5 95 1045
FORD 3000 450 150 300 3300
MILLER 1300 195 65 130 1430
14 rows selected.
43rd query
Display the department numbers and total number of employees working
in each department ?
SQL> select deptno,count(deptno) from dept group by deptno;
DEPTNO COUNT(DEPTNO)
---------- -------------
10 1
20 1
30 1
40 1
44th query
Display the various jobs and total number of employees within each job ?
SQL> select job,count(job) from emp group by job;
JOB COUNT(JOB)
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
45th query
Display the department numbers and total number of employees working
in each department ?
SQL> select deptno,count(empno) from emp group by deptno;
DEPTNO COUNT(EMPNO)
---------- ------------
30 6
20 5
10 3
46th query
Display the depart numbers and total sal for each department ?
SQL> select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
47th query
Display the department numbers and max salary for each department ?
SQL> select deptno,max(sal) from emp group by deptno ;
DEPTNO MAX(SAL)
---------- ----------
30 2850
20 3000
10 5000
48th query
Display the various jobs and total salary for each job ?
SQL>select job,sum(sal) from emp group by job;
JOB SUM(SAL)
--------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
5 rows selected.
49th query
Display the department numbers with more than three employees in each department ?
SQL> select deptno,count(deptno) from emp group by deptno having count(deptno)>3 ;
DEPTNO COUNT(DEPTNO)
---------- -------------
30 6
20 5
2 rows selected.
50th query
Display the various jobs along with total salary for each of the jobs
where total salary greater than 40000 ?
SQL> select job ,sum(sal) from emp group by job having sum(sal)>40000;
no rows selected
51st query
Display the various jobs along with total number of employees each job.
the output shoud contain only those jobs with more than three employees ?
SQL> select job,count(empno) from emp group by job having count(job)>3;
JOB COUNT(EMPNO)
--------- ------------
CLERK 4
SALESMAN 4
2 rows selected.
52nd query
Display the name of the employee who earn highest sal ?
SQL> select ename from emp where sal=(select max(sal) from emp )
ENAME
----------
KING
1 row selected.
No comments:
Post a Comment