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