DB2 SF13 学习日志

接着昨天的练习，接着总结。

1，right outer join:
     select empno, lastname, deptno, deptname \
        from employee \
             right outer join department \
             on workdept = deptno
  mark:An Outer Join gives you all the same rows as an inner join, plus          the “orphan” rows that do not have matching values in the other tables.

2，joins of more than two tables:
       select p.projno, p.projname, d.deptno, d.mgrno, \
              e.lastname as mgrname \
       from project p \
            inner join department d on p.deptno = d.deptno \
            left outer join employee e on d.mgrno = e.empno
    mark:You can formulate the appropriate SELECT statement as follows. It provides the same result as the SELECT statement on the visual:
            SELECT PROJNO, PROJNAME, P.DEPTNO, MGRNO, \
                   LASTNAME AS MGRNAME \
            FROM PROJECT P \
                 INNER JOIN \
                       (DEPARTMENT D \
                        LEFT OUTER JOIN EMPLOYEE \
                        ON MGRNO = EMPNO) \
                 ON P.DEPTNO = D.DEPTNO

3，join and local predicates:
         select empno, lastname, salary, \
                deptno, deptname \
                from employee \
                      full outer join department \
                         on workdept = deptno \
                where salary > 3000 and deptname like '%center%'
    mark:The query for “Full Outer Join" of all employees with a salary higher than 30000  to all departments whose names contain the character string 'CENTER' can be formulated as follows:
            SELECT EMPNO, LASTNAME, SALARY, DEPTNO, DEPTNAME \
                   FROM (SELECT * FROM EMPLOYEE WHERE SALARY > 30000) AS E 
            FULL OUTER JOIN \
                      (SELECT * FROM DEPARTMENT \
                       WHERE DEPTNAME LIKE '%CENTER%') AS D \
            ON E.WORKDEPT = D.DEPTNO

4，case expressions in select:
         select empno, lastname, \
                case \
                   when salary < 25000 then 'low' \
                   when salary >=25000 and salary <4000 then 'averge' \
                   else 'high' \
                end as salary_class, \
                case substr(workdept,1,1) \
                   when 'a' then 'administration' \
                   when 'c' then 'customer service' \
                   when 'd' then 'development' else null \
                end as area_type \
         from employee
      mark: substr is not valid in this command and why?

5，cast specifications:
        select empno, comm/salary as col2, \
               cast(comm/salary as dec (9,2)) \
               as col3 \
        from employee \
       where empno= '000140'
   mark:Casting is often used in programming languages to refer to the process of changing a value from one data type to another. Casting in SQL has the same meaning.
        CAST is also useful when a value of a particular data type is needed as the parameter  of a function.

6，summary table-example:
        create table dept_group \
        as ( select workdept, sum(salary) as salary, \
                    sum(bonus) as bonus \
               from employee \
               group by workdept) \
        data initially deferred \
        refresh deferred
   mark:REFRESH - Indicates how the data in the table is maintained.
              DEFERRED - The data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query at the time of the REFRESH TABLE statement is processed (a snapshot).

7，not in predicate for nullable column:
     which departments have no employees?
         select deptno, deptname \
                from department \
                where deptno \
                      not in (select workdept \
                              from employee  \
                              where workdept is not null)
   mark:if the workdept is null, it will reture the wrong result! so you must avoid the null value!
    As an alternative, you can use an EXISTS subquery:
           SELECT DEPTNO, DEPTNAME \
                FROM DEPARTMENT D \
                    WHERE NOT EXISTS \
                      (SELECT * FROM EMPLOYEE \
                       WHERE D.DEPTNO = WORKDEPT)

8， correlated subquery:
       which employees have a salary that is higher than the averge of their department?
          select empno, lastname, salary \
                 from employee e \
                 where salary> \
                     (select avg (salary ) \
                             from employee \
                             where workdept = \
                                   e.workdept)
      mark:Noncorrelated subqueries execute the subquery once at the beginning and use the  result to control the rows returned by the outer query. A correlated query works  differently: a row is read by the outer query, then a value from that row is passed to the subquery and is used to control which rows are returned by the subquery. Then, the  predicate in the outer query is used to determine if the outer table row will appear in the result set. This process is repeated for each row of the outer table until each one has been examined and either written to the result set or omitted from it.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22570045/viewspace-615809/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22570045/viewspace-615809/