Learning Visually with Examples [Electronic resources] نسخه متنی

9.7. Case Study

Let's review some of the SQL statements you have learned in this chapter. This section gives scenarios and then shows the commands and resulting output.

1. Return a result of

   deptno, admrdept , and a derived

   comment column from the

   department table where

   deptname contains

   CENTER . Order the result with the first column of the result set.

   
   SELECT deptno 
   , admrdept 
   , 'it is a center' AS comment 
   FROM department 
   WHERE deptname 
   LIKE '%CENTER%' 
   ORDER BY 1 
   DEPTNO ADMRDEPT COMMENT
   ------ -------- --------------
   C01    A00      it is a center
   D01    A00      it is a center
   2 record(s) selected.
   

2. Return the

   name and

   id of staffs whose year of service is NOT NULL. Order the result by

   years and

   id . Fetch only the first five rows of the result.

   
   SELECT years 
   , name 
   , id 
   FROM staff 
   WHERE years IS NOT NULL 
   ORDER BY years DESC, id DESC 
   FETCH FIRST 5 ROWS ONLY 
   YEARS  NAME      ID
   ------ --------- ------
   13 Graham       310
   12 Jones        260
   10 Quill        290
   10 Lu           210
   10 Hanes         50
   5 record(s) selected.
   

3. Return a list of employees who do not work as a SALESREP in the OPERATIONS department.

   
   SELECT a.empno, a.lastname, b.deptno AS dept 
   FROM employee a, department b 
   WHERE a.workdept = b.deptno 
   AND a.job     <> 'SALESREP' 
   AND b.deptname = 'OPERATIONS' 
   EMPNO  LASTNAME        DEPT
   ------ --------------- ----
   000090 HENDERSON       E11
   000280 SCHNEIDER       E11
   000290 PARKER          E11
   000300 SMITH           E11
   000310 SETRIGHT        E11
   5 record(s) selected.
   

4. Insert multiple rows into the

   emp_act table.

   
   INSERT INTO emp_act VALUES 
   ('200000' ,'ABC' ,10 ,NULL ,'2003-10-22',CURRENT DATE) 
   ,('200000' ,'DEF' ,10 ,1.4  ,NULL        ,DATE (CURRENT TIMESTAMP)) 
   ,('200000' ,'IJK' ,10 ,1.4  ,'2003-10-22', DEFAULT) 
   DB20000I  The SQL command completed successfully.
   

5. Insert the result of a query into the

   emp_act table.

   
   INSERT INTO emp_act 
   SELECT LTRIM(CHAR(id + 600000)) 
   , SUBSTR(UCASE(name),1,6) 
   , 180 
   , 100 
   , CURRENT DATE 
   , CURRENT DATE + 100 DAYS 
   FROM staff 
   DB20000I  The SQL command completed successfully.
   

6. Update multiple rows in the

   emp_act table using a result of a query.

   
   UPDATE emp_act 
   SET ( actno 
   , emstdate 
   , projno ) = ( SELECT MAX(salary) 
   , CURRENT DATE + 2 DAYS 
   , MIN(CHAR(id)) 
   FROM staff 
   WHERE id <> 33 ) 
   WHERE empno LIKE '600%'; 
   DB20000I  The SQL command completed successfully.
   

7. Delete records from the

   emp_act table where

   emstdate is greater than 01/01/2004.

   
   DELETE FROM emp_act WHERE emstdate > '01/01/2004' 
   DB20000I  The SQL command completed successfully.
   

8. Query records just inserted.

   
   SELECT * FROM NEW TABLE ( 
   INSERT INTO emp_act VALUES 
   ('200000' ,'ABC' ,10 ,NULL ,'2003-10-22',CURRENT DATE) 
   ,('200000' ,'DEF' ,10 ,1.4  ,NULL, DATE (CURRENT TIMESTAMP)) 
   ,('200000' ,'IJK' ,10 ,1.4  ,'2003-10-22', DEFAULT) 
   ) 
   EMPNO  PROJNO ACTNO  EMPTIME EMSTDATE   EMENDATE
   ------ ------ ------ ------- ---------- ----------
   200000 abc        10       - 10/22/2003 04/23/2004
   200000 DEF        10    1.40 -          04/23/2004
   200000 IJK        10    1.40 10/22/2003 -
   3 record(s) selected.
   

9. Query records just deleted.

   
   SELECT * FROM OLD TABLE ( 
   DELETE FROM emp_act WHERE emstdate > '01/01/2003' ) 
   EMPNO  PROJNO ACTNO  EMPTIME EMSTDATE   EMENDATE
   ------ ------ ------ ------- ---------- ----------
   200000 abc        10       - 10/22/2003 04/23/2004
   200000 abc        10       - 10/22/2003 04/23/2004
   20000  IJK        10    1.40 10/22/2003 -
   3 record(s) selected.
   

10. Query records just inserted in the order they were inserted.

    
    SELECT empno 
    , projno 
    , actno 
    , row# 
    FROM FINAL TABLE 
    ( INSERT INTO emp_act (empno, projno, actno) 
    INCLUDE ( row# SMALLINT ) 
    VALUES ('300000', 'XXX', 999, 1) 
    , ('300000', 'YYY', 999, 2) ) 
    ORDER BY row# 
    EMPNO  PROJNO ACTNO  ROW#
    ------ ------ ------ ------
    300000 XXX       999      1
    300000 YYY       999      2
    2 record(s) selected.