Aco 4

Front 1

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;

Back 1

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.

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

Back 2

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 corresponding orders
from a customer, the result set will have the customer ID and name.

3.

Front 3

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.

Back 3

3. B. When an outer join returns values from a table that does not have corresponding records, a NULL is returned.

Front 4

4. When using ANSI join syntax, which clause is used to specify a join condition?
A. JOIN
B. USING
C. ON
D. WHERE

Back 4

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.

Front 5

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_nameFROM departments d NATURAL LEFT OUTER JOIN employees e;

B. SELECT department_id, department_name, full_nameFROM departments NATURAL LEFT JOIN employees;

C. SELECT d.department_id, d.department_name, e.full_nameFROM departments d LEFT OUTER JOIN employees eUSING (d.department_id);

D. SELECT d.department_id, d.department_name, e.full_nameFROM departments d LEFT OUTER JOIN employees eON (d.department_id = e.department_id);

Back 5

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.

Front 6

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

Back 6

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.

Front 7

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)

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)

Back 7

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.

Front 8

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

Back 8

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

Front 9

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.

Back 9

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.

Front 10

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


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.

Back 10

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.

Front 11

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

Back 11

11. A. The query will succeed, because there is only one row in the CITY table with the CTY_NAME value 'DALLAS'.

Front 12

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);


A. Line 3

B. Line 4

C. Line 5

D. Line 7

E. No error

Back 12

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.

Front 13

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';

Back 13

13. A. A subquery is correlated when a reference is made to a column from a table in the parent statement.

Front 14

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

Back 14

14. C. The subquery returns 91 to the main query.

Front 15

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

Back 15

15. D. You cannot have an ORDER BY clause in the subquery used in a WHERE clause.

Front 16

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.

Back 16

16. C. The query will work fine, producing the difference between the employee’s salary and average salary in the department. Y ou 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.

Front 17

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

Back 17

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

Front 18

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

Back 18

18. B. When using set operators, the ORDER BY clause can appear only on the SQL at the very end. Y ou can use the column names (or aliases)
appearing in the top query or use positional columns.

Front 19

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.

Back 19

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.

Front 20

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, salaryFROM employeesWHERE ROWNUM <= 5ORDER BY salary DESC;

B. SELECT last_name, salaryFROM (SELECT *FROM employeesWHERE ROWNUM <= 5ORDER BY salary DESC )WHERE ROWNUM <= 5;

C. SELECT * FROM(SELECT last_name, salaryFROM employeesORDER BY salary)WHERE ROWNUM <= 5;

D. SELECT * FROM(SELECT last_name, salaryFROM employeesORDER BY salary DESC)WHERE ROWNUM <= 5;

Back 20

20. D. T o 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.