Infolinks

Saturday 14 July 2012

sql


=============================== 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