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