Section 9.7. Case Study

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.