 |
|
Advanced Oracle SQL: Exercises and Solutions
Oracle Tips by Laurent Schneider
|
Laurent
Schneider is considered one of the top Oracle SQL experts, and
he is the author of the book "Advanced
Oracle SQL Programming" by Rampant TechPress. The following
is an excerpt from the book.
Exercises
1.
Point out the offending line of code in the following query.
SELECT
DEPTNO,
ENAME
FROM
EMP
GROUP BY
DEPTNO
2.
Which two of the following expressions are not valid?
SELECT
ROWID,
DATE '01-JAN-08',
DEPTNO,
1/2,
2^10,
(
WITH
T
AS
(
SELECT
13
FROM
DUAL
)
SELECT
T.*
FROM
T
)
FROM
DEPT
3.
Table A has 5 rows and table B has 3 rows:
SELECT
X
FROM
A
UNION
SELECT
X
FROM
B
How many rows will be returned? Specify a
range.
The query will return from … to … rows.
4.
Describe the purpose of the following query.
SELECT
d.DNAME,
e.ENAME,
e.JOB
FROM
EMP e
RIGHT JOIN
DEPT d
ON
(
e.DEPTNO=d.DEPTNO
AND
e.JOB IN ('MANAGER','PRESIDENT')
)
Try to rewrite the query using the (+) syntax.
5.
King earns $5000 a month. What does the following query
return?
SELECT
CASE
WHEN
SAL>2000
THEN
1
WHEN
SAL>4000
THEN
2
WHEN
SAL>6000
THEN
3
ELSE
4
END "N"
FROM
EMP
WHERE
ENAME='KING'
6.
How many rows will the following query return (10g/11g)?
SELECT
COLUMN_VALUE
FROM
TABLE
(
SYS.ODCIDATELIST(DATE '2000-01-01',DATE '2000-01-31')
)
Solutions
1.
Line 3: ENAME is not a group by expression.
To order the rows, use order by!
SELECT
DEPTNO,
ENAME
FROM
EMP
GROUP BY
DEPTNO
ENAME
*
ERROR at line 3:
ORA-00979: not a GROUP BY expression
2.
DATE '01-JAN-08' is not a proper date literal. 2^10 is not
valid.
A date literal is always in the format DATE 'YYYY-MM-SS',
regardless of the NLS settings. 2^10 must be replaced by
POWER(2,10). ROWID is a pseudo column, DEPTNO is a column, 1/2 is a
compounded expression and the last column is a scalar subquery
returning 13.
SELECT
ROWID,
DATE '01-JAN-08',
DEPTNO,
1/2,
2^10,
(
WITH
T
AS
(
SELECT
13
FROM
DUAL
)
SELECT
T.*
FROM
T
)
FROM
DEPT
3.
Table A has 5 rows and table B has 3 rows. The union contains
1 to 8 rows.
SELECT
*
FROM
TABLE(SYS.ODCINUMBERLIST(1,2,3,4,5))
UNION
SELECT
*
FROM
TABLE(SYS.ODCINUMBERLIST(6,7,8))
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
8 rows selected.
SELECT
*
FROM
TABLE(SYS.ODCINUMBERLIST(1,1,1,1,1))
UNION
SELECT
*
FROM
TABLE(SYS.ODCINUMBERLIST(1,1,1));
COLUMN_VALUE
------------
1
1 row selected.
4.
The following query returns the managers and president of
each department. If there is no manager and no president, the name
and jobs are set to NULL.
SELECT
d.DNAME,
e.ENAME,
e.JOB
FROM
EMP e
RIGHT JOIN
DEPT d
ON
(
e.DEPTNO=d.DEPTNO
AND
e.JOB IN ('MANAGER','PRESIDENT')
)
DNAME ENAME JOB
-------------- ---------- ---------
ACCOUNTING CLARK MANAGER
ACCOUNTING KING PRESIDENT
RESEARCH JONES MANAGER
SALES BLAKE MANAGER
OPERATIONS
It is not possible to use the (+) syntax with an
IN predicate, therefore a subquery is required. For example:
SELECT
d.DNAME,
e.ENAME,
e.JOB
FROM
(
SELECT
e1.ENAME,
e1.DEPTNO,
e1.JOB
FROM
EMP e1
WHERE
e1.JOB IN ('MANAGER','PRESIDENT')
) e,
DEPT d
WHERE
e.DEPTNO (+)=d.DEPTNO
5.
King earns $5000 a month. Case returns the first expression that
fulfills the condition, and that is "1".
SELECT
CASE
WHEN
SAL>2000
THEN
1
WHEN
SAL>4000
THEN
2
WHEN
SAL>6000
THEN
3
ELSE
4
END as "N"
FROM
EMP
WHERE
ENAME='KING'
N
----------
1
6.
TABLE transforms the collection into a table. Two rows are
returned.
SELECT
COLUMN_VALUE
FROM
TABLE
(
SYS.ODCIDATELIST(DATE '2000-01-01',DATE '2000-01-31')
)
COLUMN_VALUE
-----------
01-JAN-2000
31-JAN-2000
