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