OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P7

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

143
lượt xem 31
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P7: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P7

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). NÛ DBA_TABLESPACES: Shows the tablespace name, type, and so on. NÛ DBA_DATA_FILES: 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. NÛ DBA_TEMP_FILES: Shows the temporary files associated with a temporary tablespace and their size. NÛ DBA_FREE_SPACE: Shows the unallocated space (free space) in each tablespace. The query to get the tablespace names and type of tablespace would be as follows: column tablespace_name format a18 SELECT tablespace_name, contents FROM dba_tablespaces; TABLESPACE_NAME CONTENTS ------------------ --------- SYSTEM PERMANENT SYSAUX PERMANENT UNDOTBS1 UNDO TEMP TEMPORARY USERS PERMANENT EXAMPLE PERMANENT To find the total space allocated to each tablespace, you need to query DBA_DATA_FILES and DBA_TEMP_FILES. Since you are using a group function (SUM) along with a nonaggregated column (tablespace_name), the GROUP BY clause is a must. Notice the use of an arithmetic operation on the aggregated result to display the bytes in megabytes. SELECT tablespace_name, SUM(bytes)/1048576 MBytes FROM dba_data_files
232 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 SYSTEM 8.3125 EXAMPLE 22.625 Let’s now try to display the total size of the tablespaces and their free space side-by-side using a UNION ALL query. UNION ALL is used to avoid sorting. UNION will produce the same result. SELECT tablespace_name, SUM(bytes)/1048576 MBytes, 0 MBytesFree FROM dba_data_files GROUP BY tablespace_name UNION ALL SELECT tablespace_name, SUM(bytes)/1048576 MBytes, 0 FROM dba_temp_files
Subqueries 233 GROUP BY tablespace_name UNION ALL SELECT tablespace_name, 0, SUM(bytes)/1048576 FROM dba_free_space GROUP BY tablespace_name; TABLESPACE_NAME MBYTES MBYTESFREE ------------------ ---------- ---------- UNDOTBS1 730 0 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 (SELECT tablespace_name, 0, SUM(bytes)/1048576 MBytesFree FROM dba_free_space GROUP BY tablespace_name) freespace USING (tablespace_name);
234 Chapter 4 N Using Joins and Subqueries TABLESPACE_NAME MBYTES MBYTESFREE ------------------ ---------- ---------- SYSAUX 800.1875 85.25 UNDOTBS1 730 718.6875 USERS 201.75 180.4375 SYSTEM 710 8.3125 EXAMPLE 100 22.625 You are almost there; the only item missing is information about the temporary tablespace. Since the temporary-tablespace free-space information is not included in the freespace subquery and you used an INNER join condition, the result set did not include temporary tablespaces. Now if you change the INNER JOIN to an OUTER JOIN, you get the desired 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
Subqueries 235 Another method to write the same query would be to use the query you built earlier and aggregate its result using an outer query, as shown here: SELECT tsname, sum(MBytes) MBytes, sum(MBytesFree) MBytesFree FROM ( SELECT tablespace_name tsname, SUM(bytes)/1048576 MBytes, 0 MBytesFree FROM dba_data_files GROUP BY tablespace_name UNION ALL SELECT tablespace_name, SUM(bytes)/1048576 MBytes, 0 FROM dba_temp_files GROUP BY tablespace_name UNION ALL SELECT tablespace_name, 0, SUM(bytes)/1048576 FROM dba_free_space GROUP BY tablespace_name) GROUP BY tsname ORDER BY 1; TSNAME MBYTES MBYTESFREE ------------------------------ ---------- ---------- EXAMPLE 100 22.625 SYSAUX 800.1875 85.0625 SYSTEM 710 8.3125 TEMP 50.0625 0 UNDOTBS1 730 718.6875 USERS 201.75 180.4375 Multiple-Column Subqueries A subquery is multiple-column when you have more than one column in the SELECT clause of the subquery. Multiple-column subqueries are generally used to compare column condi- tions or in an 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
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 table: SELECT cty_name FROM city WHERE (cnt_code, st_code) IN (SELECT cnt_code, st_code FROM state WHERE st_name = ‘TEXAS’); CTY_NAME ---------- DALLAS Subqueries in Other DML Statements You can use subqueries in DML statements such as INSERT, UPDATE, DELETE, and MERGE. DML statements and their syntax are discussed in Chapter 5, “Manipulating Data.” The following are some examples of subqueries in DML statements: NÛ To update the salary of all employees to the maximum salary in the corresponding department (correlated subquery), use this: UPDATE employees e1 SET salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department_id = e2.department_id); NÛ To delete the records of employees whose salary is less than the average salary in the department (using a correlated subquery), use this: DELETE FROM employees e WHERE salary < (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);
Subqueries 237 NÛ To insert records to a table using a subquery, use this: INSERT INTO employee_archive SELECT * FROM employees; NÛ To specify a subquery in the 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 statements in place of the table name. Here is an example: DELETE FROM (SELECT * FROM departments WHERE department_id < 20) WHERE department_id = 10; The subquery can have an optional WITH clause. WITH READ ONLY specifies that the subquery cannot be updated. WITH CHECK OPTION specifies that if the subquery is used in place of a table in an INSERT, UPDATE, or DELETE statement, Oracle will not allow any changes to the table that would produce rows that are not included in the subquery. Let’s look at an example: INSERT INTO (SELECT department_id, department_name FROM departments WHERE department_id < 20) VALUES (35, ‘MARKETING’); 1 row created. INSERT INTO (SELECT department_id, department_name FROM departments WHERE department_id < 20 WITH CHECK OPTION) VALUES (45, ‘EDP’) SQL> / FROM departments * ERROR at line 2: ORA-01402: view WITH CHECK OPTION where-clause violation SQL>
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 (= or IN), it is known as an equality join. If any other operator is used to join the tables, it is a nonequality join. If you do not specify any join condition between the tables, the result will be a Cartesian product: each row from the first table joined to every row in the second table. To avoid Cartesian joins, there should be at least n-1 join conditions in the WHERE clause when there are n tables in the FROM clause. A table can be joined to itself. If you want to select the results from a table, even if there are no corresponding rows in the joined table, you can use the outer join operator: (+). In the ANSI syntax, you can use the NATURAL JOIN, CROSS JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN keywords to specify the type of join. A subquery is a query within a query. Writing subqueries is a powerful way to manipu- late data. You can write single-row and multiple-row subqueries. Single-row subqueries must return zero or one row; multiple-row subqueries return zero or more rows. IN 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 different outer join clauses. You can specify outer joins using LEFT, RIGHT, or FULL. Know the syntax of each type of join. Be sure of the join syntax. Spend time practicing each type of join using the ANSI syntax. Understand the restrictions of using each ANSI keyword in the JOIN and their implied column-naming conventions.
Exam Essentials 239 Know how to write subqueries. Understand the use and flexibility of subqueries. Practice using scalar subqueries and correlated subqueries. Understand the use of the ORDER BY clause in the subqueries. You can use the ORDER BY clause in all subqueries, except the subqueries appearing in the WHERE clause of the query. You can use the GROUP BY clause in the subqueries. 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.
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 ORDERS table, even if the customer has not placed an order. B. List only the names of customers from the CUSTOMERS table who have placed an order in the ORDERS table. C. List all orders from the ORDERS table, even if there is no valid customer record in the CUSTOMERS table. D. For each record in the CUSTOMERS table, list the information from the ORDERS table. 3. The CUSTOMERS and ORDERS tables have the following data: SQL> SELECT * FROM customers; CUST_ CUST_NAME PHONE CITY ----- -------------------- --------------- ----------- A0101 Abraham Taylor Jr. Fort Worth B0134 Betty Baylor 972-555-5555 Dallas B0135 Brian King Chicago SQL> SELECT * FROM orders; ORD_DATE PROD_ID CUST_ID QUANTITY PRICE --------- ---------- ------- ---------- ---------- 20-FEB-00 1741 B0134 5 65.5 02-FEB-00 1001 B0134 25 2065.85 02-FEB-00 1001 B0135 3 247.9 When the following query is executed, what will be the value of PROD_ID and ORD_DATE for the customer Abraham Taylor Jr.? 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 (+);
Review Questions 241 A. NULL, 01-JAN-01 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 C. IN D. = 7. Which SQL statements do not give an error? (Choose all that apply.) A. SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) me ON (e.hire_date = me.max_hire_date)
242 Review Questions B. SELECT last_name, e.hire_date, department_id FROM employees e WHERE hire_date = (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) C. SELECT last_name, e.hire_date, department_id FROM employees e WHERE (department_id, hire_date) IN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id) D. SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id) me USING (hire_date) 8. The columns of the EMPLOYEES, DEPARTMENTS, and JOBS tables are shown here: Table Column Names Datatype 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?
Review Questions 243 1 SELECT e.last_name, d.department_name, j.job_title 2 FROM jobs j 3 INNER JOIN employees e 4 ON (e.department_id = d.department_id) 5 JOIN departments d 6 ON (j.job_id = e.job_id); A. The query returns all the rows from the EMPLOYEE table, where there is a corresponding record in the JOBS table and the DEPARTMENTS table. B. The query fails with an invalid column name error. C. The query fails because line 3 specifies INNER JOIN, which is not a valid syntax. D. The query fails because line 5 does not specify the keyword INNER. E. The query fails because the column names are qualified with the table alias. 9. The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Consider the following three queries using those tables. 1. SELECT last_name, department_name FROM employees e, departments d WHERE e.department_id = d.department_id; 2. SELECT last_name, department_name FROM employees NATURAL JOIN departments; 3. SELECT last_name, department_name FROM employees JOIN departments USING (department_id); Which of the following assertions best describes the results? A. Queries 1, 2, and 3 produce the same results. B. Queries 2 and 3 produce the same result; query 1 produces a different result. C. Queries 1, 2, and 3 produce different results. D. Queries 1 and 3 produce the same result; query 2 produces a different result. 10. The data in the STATE table is as shown here: 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
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 1 CA 8099 LOS ANGELES What is the result of the following query? SELECT st_name “State Name” FROM state WHERE (cnt_code, st_code) = (SELECT cnt_code, st_code FROM city WHERE cty_name = ‘DALLAS’); A. TEXAS B. The query will fail because CNT_CODE and ST_CODE are not in the WHERE clause of the subquery. C. The query will fail because more than one column appears in the WHERE clause. D. TX 12. Which line of the code has an error? 1 SELECT department_id, count(*) 2 FROM employees 3 GROUP BY department_id 4 HAVING COUNT(department_id) = 5 (SELECT max(count(department_id)) 6 FROM employees 7 GROUP BY department_id);
Review Questions 245 A. Line 3 B. Line 4 C. Line 5 D. Line 7 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 WHERE CNT_CODE = (SELECT MAX(cnt_code) FROM country);
246 Review Questions A. INDIA B. 65 C. 91 D. SINGAPORE 15. Which line in the following query contains an error? 1 SELECT deptno, ename, sal 2 FROM emp e1 3 WHERE sal = (SELECT MAX(sal) FROM emp 4 WHERE deptno = e1.deptno 5 ORDER BY deptno); A. Line 2 B. Line 3 C. Line 4 D. Line 5 16. Consider the following query: SELECT deptno, ename, salary salary, average, salary-average difference FROM emp, (SELECT deptno dno, AVG(salary) average FROM emp GROUP BY deptno) WHERE deptno = dno ORDER BY 1, 2; Which of the following statements is correct? 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
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 5 SELECT department_id 6 FROM departments 7 ORDER BY department_id A. 1 B. 3 C. 6 D. 7 E. No error 19. Consider the following queries: 1. SELECT last_name, salary, (SELECT (MAX(sq.salary) - e.salary) FROM employees sq WHERE sq.department_id = e.department_id) DSAL FROM employees e WHERE department_id = 20; 2. SELECT last_name, salary, msalary - salary dsal FROM employees e, (SELECT department_id, MAX(salary) msalary FROM employees GROUP BY department_id) sq WHERE e.department_id = sq.department_id AND e.department_id = 20;
248 Review Questions 3. SELECT last_name, salary, msalary - salary dsal FROM employees e INNER JOIN (SELECT department_id, MAX(salary) msalary 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
Answers to Review Questions 249 Answers to Review Questions 1. C. When table aliases are defined, you should qualify the column names with the table alias only. In this case, the table name cannot be used to qualify column names. The line in option C should read WHERE e.deptno = d.deptno. 2. A. An outer join operator (+) indicates an outer join and is used to display the records, even if there are no corresponding records in the table mentioned on the other side of the operator. Here, the outer join operator is next to the ORDERS table, so even if there are no correspond- ing orders from a customer, the result set will have the customer ID and name. 3. B. When an outer join returns values from a table that does not have corresponding records, a NULL is returned. 4. C. The join condition is specified in the ON clause. The JOIN clause specifies the table to be joined. The USING clause specifies the column names that should be used in the join. The WHERE clause is used to specify additional search criteria to restrict the rows returned. 5. B, D. Option A does not work because you cannot qualify column names when using a 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.
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’. 12. E. There is no error in the statement. The query will return the department number where the most employees are working and the number of employees in that department. 13. A. A subquery is correlated when a reference is made to a column from a table in the parent statement. 14. C. The subquery returns 91 to the main query. 15. D. You cannot have an ORDER BY clause in the subquery used in a WHERE clause. 16. C. The query will work fine, producing the difference between the employee’s salary and average salary in the department. You do not need to use the alias names, because the column names returned from the subquery are different from the column names returned by the parent query. 17. C. Because only one column is selected in the subquery to which you are doing the insert, only one column value should be supplied in the VALUES clause. The VALUES clause can have only CNT_CODE value (971). 18. B. When using set operators, the ORDER BY clause can appear only on the SQL at the very end. You can use the column names (or aliases) appearing in the top query or use positional columns. 19. B. All four queries produce the same result. The first query uses a scalar subquery in the SELECT clause. The rest of queries use an inline view. All the queries display the last name, salary, and difference of salary from the highest salary in the department for all employees in department 20. 20. D. To find the top n rows, you can select the necessary columns in an inline view with an ORDER BY DESC clause. An outer query limiting the rows to n will give the result. ROWNUM returns the row number of the result row.