Section 7
The ID column in the CLIENT table that corresponds to the
CLIENT_ID column of the ORDER table contains null values for rows that need to
be displayed. Which type of join should you use to display the data?
Nonequi-Join
Self
join
Equijoin
Outer
join (*)
Evaluate this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);
Which join is evaluated first?
The
self-join of the player table (*)
The
join between the player table and the team table on MANAGER_ID
The
join between the player table and the team table on TEAM_ID
The
join between the player table and the team table on PLAYER_ID
Which statement about joining
tables with a non-equijoin is false?
A WHERE
clause must specify a column in one table that is compared to a column in the
second table (*)
The
number of join conditions required is always one less than the number of tables
being joined
The
columns being joined must have compatible data types
None of
the above
The following statement is an
example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN
j.lowest_sal AND j.highest_sal;
True or False?
True
(*)
False
Which operator is typically
used in a nonequijoin?
IN
NOT
*
OR
>=,
<=, or BETWEEN ...AND (*)
Which
symbol is used to perform an outer join?
*
(+) (*)
#
||
To perform a valid outer join
between DEPARMENTS and EMPLOYEES to list departments without employees, select
the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
e.department_id
= d.department_id
e.department_id(+)
= d.department_id(+)
e.department_id(+)
= d.department_id (*)
e.department_id
= d.department_id(+)
You need to create a report
that lists all employees in department 10 (Sales) whose salary is not equal to
$25,000 per year. Which query should you issue to accomplish this task?
SELECT
last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND dept_id = 10;
(*)
SELECT
last_name, first_name, salary
FROM employees
WHERE salary = 25000 AND dept_id = 10;
SELECT
last_name, first_name, salary
FROM employees
WHERE salary <= 25000 AND dept_id = 10;
SELECT
last_name, first_name, salary
FROM employees
WHERE salary > 25000 AND dept_id = 10;
What is the result of a query
that selects from two tables but includes no join condition?
A
Cartesian product (*)
A
selection of rows from the first table only
A
syntax error
A
selection of matched rows from both tables
What is produced when a join
condition is not specified in a multiple-table query using Oracle proprietary
Join syntax?
A self-join
An
outer join
A
Cartesian product (*)
An
equijoin
When must column names be prefixed by table names in join
syntax?
Only
when query speed and database performance is a concern
When
the more than two tables participate in the join
When
the same column name appears in more than one table of the query (*)
Never
Will the following statement
work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
No,
Oracle will return a Column Ambiguously Defined error. (*)
Yes,
there are no syntax errors in that statement
Yes,
Oracle will resolve which department_id colum comes from which table.
No,
Oracle will not allow joins in the WHERE clause
If table A has 10 rows and table
B has 5 rows, how many rows will be returned if you perform a cartesian join on
those two tables?
10
15
50 (*)
5
You have the following EMPLOYEES
table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
The BONUS table includes the following columns:
BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee's bonus as
a calculation of salary times bonus. Which of the following queries should you
issue?
SELECT
e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)
SELECT
first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;
SELECT
e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;
SELECT
e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND
employees.department_id > 5000
ORDER BY 4;
Which clause contains a syntax error?
AND
employees.department_id > 5000 (*)
FROM
employees e, departments d
WHERE
e.department_id = d.department_id
ORDER
BY 4;
SELECT
e.employee_id, e.last_name, e.first_name, d.department_name
Que pinta Kanna Kobayashi en esta página? ...
ReplyDeleteThis comment has been removed by the author.
Delete