Subqueries

231
If the scalar subquery returns more than one row, the query will fail. If the scalar sub-
query returns no rows, the value is
NULL
.
Finding Total Space and Free Space Using Dictionary Views
The following dictionary views are best friends of a DBA. They show the most critical
aspect of the database from the user perspective—the space allocated and free. If the
DBA is not monitoring the growth and free space available in the database, it is likely that
they might get calls from the user community that they ran out of space in the tablespace.
Let’s build a query using four dictionary views (you may need the
SELECT_CATALOG_ROLE

privilege to query these views).
DBA_TABLESPACES
ÛN
: Shows the tablespace name, type, and so on.
DBA_DATA_FILES
ÛN
: Shows the data files associated with a permanent or undo
tablespace and the size of the data file. The total size of all data files associated with a
tablespace gives the total size of the tablespace.
DBA_TEMP_FILES
ÛN
: Shows the temporary files associated with a temporary tablespace
and their size.
DBA_FREE_SPACE
ÛN
: Shows the unallocated space (free space) in each tablespace.


Chapter 4
N
Using Joins and Subqueries
GROUP BY tablespace_name;
TABLESPACE_NAME MBYTES
------------------ ----------
UNDOTBS1 730
SYSAUX 800.1875
USERS 201.75
SYSTEM 710
EXAMPLE 100
SELECT tablespace_name, SUM(bytes)/1048576 MBytes
FROM dba_temp_files
GROUP BY tablespace_name;
TABLESPACE_NAME MBYTES
------------------ ----------
TEMP 50.0625
You can find the total free space in each tablespace using the
DBA_FREE_SPACE
view.
Notice that the free space from temporary tablespace is not shown in this query.
SELECT tablespace_name, SUM(bytes)/1048576 MBytesFree
FROM dba_free_space
GROUP BY tablespace_name;
TABLESPACE_NAME MBYTESFREE
------------------ ----------
SYSAUX 85.25
UNDOTBS1 718.6875
USERS 180.4375

SYSAUX 800.1875 0
USERS 201.75 0
SYSTEM 710 0
EXAMPLE 100 0
TEMP 50.0625 0
SYSAUX 0 85.25
UNDOTBS1 0 718.6875
USERS 0 180.4375
SYSTEM 0 8.3125
EXAMPLE 0 22.625
You got the result, but it’s not exactly as you expected. You want to see the free-space
information beside each tablespace. Let’s join the results of the total space with the free
space and see what happens. Here you are creating two subqueries (inline views
total-
space
and
freespace
) and joining them together using the
tablespace_name
column.
SELECT tablespace_name, MBytes, MBytesFree
FROM
(SELECT tablespace_name, SUM(bytes)/1048576 MBytes
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(bytes)/1048576 MBytes
FROM dba_temp_files
GROUP BY tablespace_name) totalspace
JOIN

result:
SELECT tablespace_name, MBytes, MBytesFree
FROM
(SELECT tablespace_name, SUM(bytes)/1048576 MBytes
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name, SUM(bytes)/1048576 MBytes
FROM dba_temp_files
GROUP BY tablespace_name) totalspace
LEFT OUTER JOIN
(SELECT tablespace_name, 0, SUM(bytes)/1048576 MBytesFree
FROM dba_free_space
GROUP BY tablespace_name) freespace
USING (tablespace_name)
ORDER BY 1;
TABLESPACE_NAME MBYTES MBYTESFREE
------------------ ---------- ----------
EXAMPLE 100 22.625
SYSAUX 800.1875 85.0625
SYSTEM 710 8.3125
TEMP 50.0625
UNDOTBS1 730 718.6875
USERS 201.75 180.4375
95127c04.indd 234 2/18/09 9:43:39 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Subqueries

235
Another method to write the same query would be to use the query you built earlier and

UPDATE
statement. Let’s consider a simple example using the
STATE
and
CITY

tables shown here:
SQL> SELECT * FROM state;
CNT_CODE ST_CODE ST_NAME
---------- ------- ------------
1 TX TEXAS
1 CA CALIFORNIA
95127c04.indd 235 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
236

Chapter 4
N
Using Joins and Subqueries
91 TN TAMIL NADU
1 TN TENNESSE
91 KL KERALA
SQL> SELECT * FROM city;
CNT_CODE ST_CODE CTY_CODE CTY_NAME
---------- ------- -------- --------------
1 TX 1001 DALLAS
91 TN 2243 MADRAS
1 CA 8099 LOS ANGELES
List the cities in Texas using a subquery on the
STATE

ÛN
department (using a correlated subquery), use this:
DELETE FROM employees e
WHERE salary < (SELECT AVG(salary) FROM employees
WHERE department_id = e.department_id);
95127c04.indd 236 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Subqueries

237
To insert records to a table using a subquery, use this:
ÛN
INSERT INTO employee_archive
SELECT * FROM employees;
To specify a subquery in the
ÛN
VALUES
clause of the
INSERT
statement, use this:
INSERT INTO departments
(department_id, department_name)
VALUES ((SELECT MAX(department_id)
+10 FROM departments), ‘EDP’);
You can also have a subquery in the
INSERT
,
UPDATE
, and
DELETE

VALUES (45, ‘EDP’)
SQL> /
FROM departments
*
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL>
95127c04.indd 237 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
238

Chapter 4
N
Using Joins and Subqueries
Summary
In this chapter, you learned to retrieve data from multiple tables. I started off discussing
table joins. You also learned how to use subqueries and set operators.
Joins are used to relate two or more tables (or views). In a relational database, it is com-
mon to have a requirement to join data. The tables are joined by using a common column
in the tables in the
WHERE
clause of the query. Oracle supports ISO/ANSI SQL1999 syntax
for joins. Using this syntax, the tables are joined using the
JOIN
keyword, and a condition
can be specified using the
ON
clause.
If the join condition uses the equality operator (
=

and
EXISTS
are the most commonly used subquery operators. Subqueries can appear in the
WHERE
clause or in the
FROM
clause. They can also replace table names in
SELECT
,
DELETE
,
INSERT
, and
UPDATE
statements. Subqueries that return one row and one column result are
known as scalar subqueries. Scalar subqueries can be used in most places where you would
use an expression.
Set operators are used to combine the results of more than one query into one. Each
query is separate and will work on its own. Four set operators are available in Oracle:
UNION
,
UNION ALL
,
MINUS
, and
INTERSECT
.
Exam Essentials
Understand joins. Make sure you know the different types of joins. Understand the differ-
ence between natural, cross, simple, complex, and outer joins.

Know the set operators. Understand the set operators that can be used in compound
queries. Know the difference between the
UNION
and
UNION ALL
operators.
Understand where you can specify the
ORDER BY
clause when using set operators. When
using set operators to join two or more queries, the
ORDER BY
clause can appear only at the
very end of the query. You can specify the column names as they appear in the top query or
use positional notation.
95127c04.indd 239 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
240

Review Questions
Review Questions
1. Which line of code has an error?
A. SELECT dname, ename
B. FROM emp e, dept d
C. WHERE emp.deptno = dept.deptno
D. ORDER BY 1, 2;
2. What will be the result of the following query?
SELECT c.cust_id, c.cust_name, o.ord_date, o.prod_id
FROM customers c, orders o
WHERE c.cust_id = o.cust_id (+);
A. List all the customer names in the CUSTOMERS table and the orders they made from the

B. NULL, NULL
C. 1001, 02-FEB-00
D. The query will not return customer Abraham Taylor Jr.
4. When using ANSI join syntax, which clause is used to specify a join condition?
A. JOIN
B. USING
C. ON
D. WHERE
5. The EMPLOYEES table has EMPLOYEE_ID, DEPARTMENT_ID, and FULL_NAME columns. The
DEPARTMENTS table has DEPARTMENT_ID and DEPARTMENT_NAME columns. Which two of the
following queries return the department ID, name, and employee name, listing department
names even if there is no employee assigned to that department? (Choose two.)
A. SELECT d.department_id, d.department_name, e.full_name
FROM departments d
NATURAL LEFT OUTER JOIN employees e;
B. SELECT department_id, department_name, full_name
FROM departments
NATURAL LEFT JOIN employees;
C. SELECT d.department_id, d.department_name, e.full_name
FROM departments d
LEFT OUTER JOIN employees e
USING (d.department_id);
D. SELECT d.department_id, d.department_name, e.full_name
FROM departments d
LEFT OUTER JOIN employees e
ON (d.department_id = e.department_id);
6. Which two operators are not allowed when using an outer join operator in the query?
(Choose two.)
A. OR
B. AND

EMPLOYEES EMPLOYEE_ID
NUMBER (6)
FIRST_NAME
VARCHAR2 (25)
LAST_NAME
VARCHAR2 (25)
SALARY
NUMBER (8,2)
JOB_ID
VARCHAR2 (10)
MANAGER_ID
NUMBER (6)
DEPARTMENT_ID
NUMBER (2)
DEPARTMENTS DEPARTMENT_ID
NUMBER (2)
DEPARTMENT_NAME
VARCHAR2 (30)
MANAGER_ID
NUMBER (6)
LOCATION_ID
NUMBER (4)
JOBS JOB_ID
VARCHAR2 (10)
JOB_TITLE
VARCAHR2 (30)
Which assertion about the following query is correct?
95127c04.indd 242 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Review Questions

SQL> SELECT * FROM state;
CNT_CODE ST_CODE ST_NAME
---------- ------- ------------
1 TX TEXAS
1 CA CALIFORNIA
91 TN TAMIL NADU
1 TN TENNESSE
91 KL KERALA
95127c04.indd 243 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
244

Review Questions
Consider the following query.
SELECT cnt_code
FROM state
WHERE st_name = (SELECT st_name FROM state
WHERE st_code = ‘TN’);
Which of the following assertions best describes the results?
A. The query will return the CNT_CODE for the ST_CODE value ‘TN’.
B. The query will fail and will not return any rows.
C. The query will display 1 and 91 as CNT_CODE values.
D. The query will fail because an alias name is not used.
11. The data in the STATE table is shown in question 10. The data in the CITY table is as
shown here:
SQL> SELECT * FROM city;
CNT_CODE ST_CODE CTY_CODE CTY_NAME
---------- ------- ---------- -------------
1 TX 1001 DALLAS
91 TN 2243 MADRAS

E. No error
13. Which of the following is a correlated subquery?
A. select cty_name from city
where st_code in (select st_code from state
where st_name = ‘TENNESSEE’
and city.cnt_code = state.cnt_code);
B. select cty_name
from city
where st_code in (select st_code from state
where st_name = ‘TENNESSEE’);
C. select cty_name
from city, state
where city.st_code = state.st_code
and city.cnt_code = state.cnt_code
and st_name = ‘TENNESSEE’;
D. select cty_name
from city, state
where city.st_code = state.st_code (+)
and city.cnt_code = state.cnt_code (+)
and st_name = ‘TENNESSEE’;
14. The COUNTRY table has the following data:
SQL> SELECT * FROM country;
CNT_CODE CNT_NAME CONTINENT
---------- ----------------- ----------
1 UNITED STATES N.AMERICA
91 INDIA ASIA
65 SINGAPORE ASIA
What value is returned from the subquery when you execute the following?
SELECT CNT_NAME
FROM country

A. The query will fail because no alias name is provided for the subquery.
B. The query will fail because a column selected in the subquery is referenced outside the
scope of the subquery.
C. The query will work without errors.
D. GROUP BY cannot be used inside a subquery.
17. The COUNTRY table has the following data:
SQL> SELECT * FROM country;
CNT_CODE CNT_NAME CONTINENT
---------- -------------------- ----------
1 UNITED STATES N.AMERICA
91 INDIA ASIA
65 SINGAPORE ASIA
95127c04.indd 246 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Review Questions

247
What will be result of the following query?
INSERT INTO (SELECT cnt_code FROM country
WHERE continent = ‘ASIA’)
VALUES (971, ‘SAUDI ARABIA’, ‘ASIA’);
A. One row will be inserted into the COUNTRY table.
B. WITH CHECK OPTION is missing in the subquery.
C. The query will fail because the VALUES clause is invalid.
D. The WHERE clause cannot appear in the subqueries used in INSERT statements.
18. Review the SQL code, and choose the line number that has an error.
1 SELECT DISTINCT department_id
2 FROM employees
3 ORDER BY department_id
4 UNION ALL

FROM employees
GROUP BY department_id) sq
ON e.department_id = sq.department_id
WHERE e.department_id = 20;
4. SELECT last_name, salary, msalary - salary dsal
FROM employees INNER JOIN
(SELECT department_id, MAX(salary) msalary
FROM employees
GROUP BY department_id) sq
USING (department_id)
WHERE department_id = 20;
Which of the following assertions best describes the results?
A. Queries 1 and 2 produce identical results, and queries 3 and 4 produce identical results,
but queries 1 and 3 produce different results.
B. Queries 1, 2, 3, and 4 produce identical results.
C. Queries 1, 2, and 3 produce identical results; query 4 will produce errors.
D. Queries 1 and 3 produce identical results; queries 2 and 4 will produce errors.
E. Queries 1, 2, 3, and 4 produce different results.
F. Queries 1 and 2 are valid SQL; queries 3 and 4 are not valid.
20. The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Which
query will show you the top five highest-paid employees in the company?
A. SELECT last_name, salary
FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC;
B. SELECT last_name, salary
FROM (SELECT *
FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC )

natural join. Option B works because the only common column between these two tables is
DEPARTMENT_ID. The keyword OUTER is optional. Option C does not work, again because
you cannot qualify column names when specifying the USING clause. Option D works
because it specifies the join condition explicitly in the ON clause.
6. A, C. OR and IN are not allowed in the WHERE clause on the columns where an outer join
operator is specified. You can use AND and = in the outer join.
7. A, C. Options A and B have an ORDER BY clause used in the subquery. An ORDER BY clause
can be used in the subquery appearing in the FROM clause, but not in the WHERE clause.
Options C and D use the GROUP BY clause in the subquery, and its use is allowed in FROM
as well as WHERE clauses. Option D will give an error because the DEPARTMENT_ID in the
SELECT clause is ambiguous and hence doesn’t need to be qualified as e.DEPARTMENT_ID.
Another issue with option D is that since you used the USING clause to join, the column
used in the USING clause cannot be qualified; e.hire_date in the SELECT clause should be
hire_date.
8. B. The query fails because the d.DEPARTMENT_ID column is referenced before the DEPART-
MENTS table is specified in the JOIN clause. A column can be referenced only after its table
is specified.
9. D. Since DEPARTMENT_ID and MANAGER_ID are common columns in the EMPLOYEES and
DEPARTMENTS tables, a natural join will relate these two tables using the two common
columns.
10. B. There are two records in the STATE table with the ST_CODE value as ‘TN’. Since you
are using a single-row operator for the subquery, it will fail. Option C would be correct if it
used the IN operator instead of = for the subquery.
95127c04.indd 249 2/18/09 9:43:40 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
250

Answers to Review Questions
11. A. The query will succeed, because there is only one row in the CITY table with the CTY_
NAME value ‘DALLAS’.