1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Overview of SQL queries and using them in technical aspect

Discussion in 'Documents Section Discussions' started by santhoshmarch4, Jan 21, 2015.

  1. santhoshmarch4

    santhoshmarch4 Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Overview of SQL queries and using them in technical aspect
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    These queries are old and some are simply wrong; such a 'document' only adds to any confusion someone might have regarding how to return the desired information. Let's look at one of the queries that is simply wrong and see why:


    List the Deptno where there are no emps.


    A) select deptno ,count(*) from emp
    group by deptno
    having count(*) = 0;


    Now let's look at WHY that query is wrong. The data in the EMP table is as follows:


    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 09-DEC-82 3000 20
    7839 KING PRESIDENT 17-NOV-81 5000 10
    7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
    7876 ADAMS CLERK 7788 12-JAN-83 1100 20


    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
    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>

    Notice there are NO rows without a deptno assigned. How, then, would anyone find departments having no employees using the EMP table alone? It cannot be done. The correct query would be:


    SQL> select dname, deptno, loc
    2 from dept
    3 where deptno not in (select deptno from emp)
    4 /


    DNAME DEPTNO LOC
    -------------- ---------- -------------
    OPERATIONS 40 BOSTON


    SQL>


    This is a completely different query than the one provided in this 'technical' document. There are other examples as well, and simply finding such a document then posting it here as 'helpful' without investigating HOW helpful or harmful it might be does no one any good.

    I won't go into all of the other issues I found with this 'offering'. Be advised that this PDF may be more confusing than helpful.