- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
81.
The columns of the EMPLOYEES , DEPARTMENTS , and JOBS tables are shown here:
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);
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.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| 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. |
82.
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?
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.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| Because DEPARTMENT_ID an d MANAGER_ID are common columns in the EMPLOYEES and DEPARTMENTS tables, a natural join will relate these two tables using the two common columns. |
83.
The data in the STATE table is shown here:
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?
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.
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| There are two records in the STATE table with the ST_CODE value as 'TN' . Because 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. |
84.
The data in the STATE table is shown in question 10. The data in the CITY table is as
shown here:
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');
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
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| The query will succeed, because there is only one row in the CITY table with the CTY_NAME value 'DALLAS'. |
85.
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 4
- B.Line 5
- C.Line 6
- D.Line 7
- D.No Error
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| 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. |
|
|
||||||||||||||||||||||||||||
