OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
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;
----
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.
Ans: C.
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 customer 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
----
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 corresponding orders from a customer, the result set will have the customer ID and name.
Ans: A.
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 (+);
A. NULL, 01-JAN-01
B. NULL, NULL
C. 1001, 02-FEB-00
D. The query will not return customer Abraham Taylor Jr.
----
When an outer join returns values from a table that does not have corresponding records, a NULL is returned.
Ans: B
4. When using ANSI join syntax, which clause is used to specify a join condition?
A. JOIN
B. USING
C. ON
D. WHERE
----
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.
Ans: C
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?
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);
----
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 specifies the join condition explicitly in the ON clause.
Ans: B, D
6. Which two operators are not allowed when using an outer-join operator in the query?
A. OR
B. AND
C. IN
D. =
-----
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.
Ans: A, C.
7. Which two operators are used to add more joining conditions in a multiple-table query?
A. NOT
B. OR
C. AND
D. Comma (,)
----
The operators OR and AND are used to add more joining conditions to the query. NOT is a negation operator, and a comma is used to separate column names and table names.
Ans: B, C
8. The columns of the EMPLOYEES, DEPARTMENTS, and JOBS tables are shown below.
-------------------------------------------
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 VARCHAR2 (30)
---------------------------------------------
Which assertion about the following query is correct?
SELECT e.last_name, d.department_name, j.job_title
FROM jobs j
INNER JOIN employees e
ON (e.department_id = d.department_id)
JOIN departments d
ON (j.job_id = e.job_id);
A. The query returns all the rows from EMPLOYEE table, where there is a corresponding record in the JOBS table and 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.
---------
The query fails because the d.department_id column is referenced before the DEPARTMENTS table is specified in the JOIN clause. A column can be referenced only after its table is specified.
Ans: B
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.
------
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.
Ans: D
10. The data in the STATE table is as shown:
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
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.
--------
There are two records in the STATE table with the ST_CODE value as 'TN'. Since we 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.
Ans: B
11. The data in the STATE table is shown in question 10. The data in the CITY table is as shown below.
SQL> SELECT * FROM city;
CNT_CODE ST 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
-----
The query will succeed, because there is only one row in the city table with the CTY_NAME value 'DALLAS'.
Ans A.
12. Which line of the code below 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);
A. Line 3
B. Line 4
C. Line 5
D. Line 7
E. No error
-----
Ans: E. There is no error in the statement. The query will return the department number where the most employees are working.
Notes: See following test under HR schema
SQL> select count(department_id) from employees;
COUNT(DEPARTMENT_ID)
--------------------
106
SQL> select count(department_id) from employees group by department_id;
COUNT(DEPARTMENT_ID)
--------------------
1
2
6
1
45
5
1
34
3
6
2
0
12 rows selected.
SQL> select max(count(department_id)) from employees group by department_id;
MAX(COUNT(DEPARTMENT_ID))
-------------------------
45
13. Which query is a correlated subquery?
A.
select cty_name from city
where st_code in (select st_code from state
where st_name = 'TENNESSE'
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 = 'TENNESSE');
C.
select cty_name
from city, state
where city.st_code = state.st_code
and city.cnt_code = state.cnt_code
and st_name = 'TENNESSE';
D.
select cty_name
from city, state
where city.st_code = state.st_code (+)
and city.cnt_code = state.cnt_code (+)
and st_name = 'TENNESSE';
-----
A subquery is correlated when a reference is made to a column from a table in the parent statement.
Ans: A.
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);
A. INDIA
B. 65
C. 91
D. SINGAPORE
-----
The subquery returns 91 to the main query.
Ans: C
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
----
You cannot have an ORDER BY clause in the subquery used in a WHERE clause.
Ans: D.
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 inside 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.
----
The query will work fine, producing the difference between 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.
Ans: C
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
What will be the 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 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.
----
Because only one column is selected in the subquery to which we 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).
Ans: C
18. In ANSI SQL, a self-join can be represented by using which of the following? (Choose the best answer.)
A. NATURAL JOIN clause
B. CROSS JOIN clause
C. JOIN .. USING clause
D. JOIN ON clause
E. All of the above
-----
D. NATURAL JOIN and JOIN ... USING clauses will not allow alias names to be used. Since a self-join is getting data from the same table, you must include alias names and qualify column names.
Ans D.
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;
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.
-------
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 of the queries display the last name, salary, and difference of salary from the highest salary in the department for all employees in department 20.
Ans: B
20. The columns of the EMPLOYEES and DEPARTMENTS tables are shown in question 8. Which query will show us the top-five highly 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 )
WHERE ROWNUM <= 5;
C.
SELECT * FROM
(SELECT last_name, salary
FROM employees
ORDER BY salary)
WHERE ROWNUM <= 5;
D.
SELECT * FROM
(SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;
-----
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.
Ans: D
No comments:
Post a Comment