Section
7 Quiz
(Answer all questions in this section)
1.
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?
Mark
for Review
(1)
Points
FROM employees e, departments d
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
ORDER BY 4;
WHERE e.department_id = d.department_id
AND employees.department_id > 5000 (*)
2.
When must column names be prefixed
by table names in join
syntax? Mark for
Review
(1)
Points
When the more than two tables participate in the join
Never
When the same column name appears in more than one table of the query (*)
Only when query speed and database performance is a concern
3.
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? Mark for Review
(1)
Points
5
15
10
50 (*)
4.
You need to provide a list of the
first and last names of all employees who work in the Sales department who
earned a bonus and had sales over $50,000. The company president would like the
sales listed starting with the highest amount first. The EMPLOYEES table and
the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID
NUMBER(10) PRIMARY KEY
LAST_NAME
VARCHAR2(20)
FIRST_NAME
VARCHAR2(20)
DEPTARTMENT_ID
VARCHAR2(20)
HIRE_DATE
DATE
SALARY
NUMBER(10)
SALES_DEPT
SALES_ID
NUMBER(10) PRIMARY KEY
SALES
NUMBER(20)
QUOTA
NUMBER(20)
MANAGER
VARCHAR2(30)
BONUS
NUMBER(10)
EMPLOYEE_ID
NUMBER(10) FOREIGN KEY
Which
SELECT statement will accomplish this task?
Mark
for Review
(1)
Points
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.
sales
WHERE
e.employee_id = s.employee_id
FROM
employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY
sales DESC;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus,
s.sales
FROM
employees e, sales_dept s
ORDER BY
sales DESC
WHERE
e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.
sales
FROM
employees e, sales_dept s
WHERE
e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY
sales DESC;
(*)
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.
sales
ORDER BY
sales DESC
FROM
employees e, sales_dept s
WHERE
e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
5.
When joining 3 tables in a SELECT
statement, how many join conditions are needed in the WHERE clause? Mark for
Review
(1)
Points
3
0
2 (*)
1
6.
What is produced when a join
condition is not specified in a multiple-table query using Oracle proprietary
Join syntax? Mark for
Review
(1)
Points
An equijoin
A Cartesian product (*)
A self-join
An outer join
7.
The CUSTOMERS and SALES tables
contain these columns:
CUSTOMERS
CUST_ID
NUMBER(10) PRIMARY KEY
COMPANY
VARCHAR2(30)
LOCATION
VARCHAR2(20)
SALES
SALES_ID
NUMBER(5) PRIMARY KEY
CUST_ID
NUMBER(10) FOREIGN KEY
TOTAL_SALES
NUMBER(30)
Which
SELECT statement will return the customer ID, the company and the total sales?
Mark
for Review
(1)
Points
SELECT c.cust_id, c.company, s.total_sales
FROM
customers c, sales s
WHERE
c.cust_id = s.cust_id;
(*)
SELECT cust_id, company, total_sales
FROM
customers, sales
WHERE
cust_id = cust_id;
SELECT cust_id, company, total_sales
FROM
customers c, sales s
WHERE
c.cust_id = s.cust_id;
SELECT c.cust_id, c.company, s.total_sales
FROM
customers c, sales s
WHERE
c.cust_id = s.cust_id (+);
8.
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?
Mark
for Review
(1)
Points
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 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;
9.
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?
Mark for Review
(1)
Points
Self join
Nonequi-Join
Outer join (*)
Equijoin
10.
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?
Mark
for Review
(1)
Points
The join between the player table and the team table on MANAGER_ID
The self-join of the player table (*)
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
11.
Using Oracle Proprietary join syntax, which two
operators can be used in an outer join condition using the outer join operator
(+)? Mark for Review
(1)
Points
OR and =
AND and = (*)
IN and =
BETWEEN...AND... and IN
12.
You need to join the EMPLOYEES table and the SCHEDULES
table, but the two tables do not have any corresponding columns. Which type of
join will you create? Mark for Review
(1)
Points
A full outer join
It is not possible to join these two tables.
A non-equijoin (*)
An equijoin
13.
Using Oracle Proprietary join syntax, which operator
would you use after one of the column names in the WHERE clause when creating
an outer join? Mark for Review
(1)
Points
*
(+) (*)
=
+
14.
Which statement about outer joins is
true? Mark for Review
(1)
Points
The OR operator cannot be used to link outer join conditions. (*)
The tables must be aliased.
Outer joins are always evaluated before other types of joins in the query.
The FULL, RIGHT, or LEFT keyword must be included.
15.
The following is a valid outer join statement:
SELECT
c.country_name, d.department_name
FROM
countries c, departments d
WHERE
c.country_id (+) = d.country_id (+)
True or
False?
Mark
for Review
(1)
Points
True
False (*)
1.
When joining 3 tables in a SELECT
statement, how many join conditions are needed in the WHERE clause? Mark for
Review
(1)
Points
0
3
1
2 (*)
2.
What is the result of a query that
selects from two tables but includes no join
condition? Mark for Review
(1)
Points
A syntax error
A Cartesian product (*)
A selection of matched rows from both tables
A selection of rows from the first table only
3.
Which statement about the join
syntax of an Oracle Proprietary join syntax SELECT statement is
true? Mark
for Review
(1)
Points
The FROM clause represents the join criteria.
The JOIN keyword must be included.
The WHERE clause represents the join criteria. (*)
The ON keyword must be included.
4.
You have two tables named EMPLOYEES
and SALES. You want to identify the sales representatives who have generated at
least $100,000 in revenue.
Which
query should you
issue? Mark for
Review
(1)
Points
SELECT first_name, last_name, sales
FROM
employees e, sales s
WHERE
e.employee_id = s.employee_id AND revenue > 100000;
SELECT e.first_name, e.last_name, s.sales
FROM
employees, sales
WHERE
e.employee_id = s.employee_id AND revenue >= 100000;
SELECT e.first_name, e.last_name, s.sales
FROM
employees e, sales s
WHERE
e.employee_id = s.employee_id AND revenue > 100000;
SELECT e.first_name, e.last_name, s.sales
FROM
employees e, sales s
WHERE
e.employee_id = s.employee_id AND revenue >= 100000;
(*)
5.
If table A has 10 rows and table B
has 5 rows, how many rows will be returned if you perform a equi-join on those
two tables? Mark for Review
(1)
Points
It depends on how many rows have matching data in each of the two tables. (*)
5
50
10
6.
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?
Mark
for Review
(1)
Points
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;
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;
(*)
7.
The PATIENTS and DOCTORS tables
contain these columns:
PATIENTS
PATIENT_ID
NUMBER(9)
LAST_NAME
VARCHAR2 (20)
FIRST_NAME
VARCHAR2 (20)
DOCTORS
DOCTOR_ID
NUMBER(9)
LAST_NAME
VARCHAR2 (20)
FIRST_NAME
VARCHAR2 (20)
You
issue this statement:
SELECT
patient_id, doctor_id
FROM
patients, doctors;
Which
result will this statement provide?
Mark
for Review
(1)
Points
A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID
values (*)
A report containing each patient's id value and his doctor's id value
A report with NO duplicate PATIENT_ID or DOCTOR_ID values
A syntax error
8.
You need to provide a list of the
first and last names of all employees who work in the Sales department who
earned a bonus and had sales over $50,000. The company president would like the
sales listed starting with the highest amount first. The EMPLOYEES table and
the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID
NUMBER(10) PRIMARY KEY
LAST_NAME
VARCHAR2(20)
FIRST_NAME
VARCHAR2(20)
DEPTARTMENT_ID
VARCHAR2(20)
HIRE_DATE
DATE
SALARY
NUMBER(10)
SALES_DEPT
SALES_ID
NUMBER(10) PRIMARY KEY
SALES
NUMBER(20)
QUOTA
NUMBER(20)
MANAGER
VARCHAR2(30)
BONUS
NUMBER(10)
EMPLOYEE_ID
NUMBER(10) FOREIGN KEY
Which
SELECT statement will accomplish this task?
Mark
for Review
(1)
Points
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.
sales
WHERE
e.employee_id = s.employee_id
FROM
employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY
sales DESC;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.
sales
ORDER BY
sales DESC
FROM
employees e, sales_dept s
WHERE
e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus,
s.sales
FROM
employees e, sales_dept s
ORDER BY
sales DESC
WHERE
e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;
SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.
sales
FROM
employees e, sales_dept s
WHERE
e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY
sales DESC;
(*)
9.
Which statement about joining tables
with a non-equijoin is false? Mark for Review
(1)
Points
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
10.
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? Mark
for Review
(1)
Points
Self join
Nonequi-Join
Equijoin
Outer join (*)
11.
Using Oracle Proprietary join syntax, which two
operators can be used in an outer join condition using the outer join operator
(+)? Mark for Review
(1)
Points
IN and =
BETWEEN...AND... and IN
AND and = (*)
OR and =
12.
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
Mark
for Review
(1)
Points
e.department_id = d.department_id
e.department_id(+) = d.department_id(+)
e.department_id = d.department_id(+)
e.department_id(+) = d.department_id (*)
13.
The EMPLOYEE_ID column in the EMPLOYEES table
corresponds to the EMPLOYEE_ID column of the ORDERS table.
The
EMPLOYEE_ID column in the ORDERS table contains null values for rows that you
need to display.
Which
type of join should you use to display the
data? Mark for
Review
(1)
Points
Natural join
Self-join
Equijoin
Outer join (*)
14.
Which operator is typically used in a
nonequijoin? Mark for Review
(1)
Points
>=, <=, or BETWEEN ...AND (*)
NOT
*
OR
IN
15.
Using Oracle Proprietary join syntax, which operator
would you use after one of the column names in the WHERE clause when creating
an outer join? Mark for Review
(1)
Points
(+) (*)
=
+
*
1.
Will the following statement work?
SELECT
department_name, last_name
FROM
employees, departments
WHERE
department_id = department_id;
Mark
for Review
(1)
Points
No, Oracle will return a Column Ambiguously Defined error. (*)
Yes, Oracle will resolve which department_id colum comes from which table.
Yes, there are no syntax errors in that statement
No, Oracle will not allow joins in the WHERE clause
2.
When must column names be prefixed
by table names in join
syntax? Mark for
Review
(1)
Points
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
3.
Oracle proprietary JOINS can use the
WHERE clause for conditions other than the join-condition. True or
False? Mark for Review
(1)
Points
True (*)
False
4.
What is the minimum number of join
conditions required to join 5 tables together? Mark for
Review
(1)
Points
4 (*)
3
5
One more than the number of tables
5.
What happens when you create a
Cartesian product? Mark for Review
(1)
Points
All rows from one table are joined to all rows of another table (*)
The table is joined to another equal table
All rows that do not match in the WHERE clause are displayed
The table is joined to itself, one column to the next column, exhausting all
possibilities
6.
Which statement about the join
syntax of an Oracle Proprietary join syntax SELECT statement is true?
Mark for Review
(1)
Points
The FROM clause represents the join criteria.
The WHERE clause represents the join criteria. (*)
The ON keyword must be included.
The JOIN keyword must be included.
7.
What is the result of a query that
selects from two tables but includes no join
condition? Mark for Review
(1)
Points
A selection of matched rows from both tables
A selection of rows from the first table only
A Cartesian product (*)
A syntax error
8.
The CUSTOMERS and SALES tables
contain these columns:
CUSTOMERS
CUST_ID
NUMBER(10) PRIMARY KEY
COMPANY
VARCHAR2(30)
LOCATION
VARCHAR2(20)
SALES
SALES_ID
NUMBER(5) PRIMARY KEY
CUST_ID
NUMBER(10) FOREIGN KEY
TOTAL_SALES
NUMBER(30)
Which
SELECT statement will return the customer ID, the company and the total sales?
Mark
for Review
(1)
Points
SELECT cust_id, company, total_sales
FROM
customers c, sales s
WHERE c.cust_id
= s.cust_id;
SELECT c.cust_id, c.company, s.total_sales
FROM
customers c, sales s
WHERE
c.cust_id = s.cust_id;
(*)
SELECT c.cust_id, c.company, s.total_sales
FROM
customers c, sales s
WHERE
c.cust_id = s.cust_id (+);
SELECT cust_id, company, total_sales
FROM
customers, sales
WHERE
cust_id = cust_id;
9.
You need to join the EMPLOYEES table
and the SCHEDULES table, but the two tables do not have any corresponding columns.
Which type of join will you create? Mark for Review
(1)
Points
A non-equijoin (*)
It is not possible to join these two tables.
An equijoin
A full outer join
10.
Using Oracle Proprietary join syntax, which operator
would you use after one of the column names in the WHERE clause when creating
an outer join? Mark for Review
(1)
Points
(+) (*)
+
*
=
11.
The EMPLOYEE_ID column in the EMPLOYEES table
corresponds to the EMPLOYEE_ID column of the ORDERS table.
The
EMPLOYEE_ID column in the ORDERS table contains null values for rows that you
need to display.
Which
type of join should you use to display the
data? Mark for
Review
(1)
Points
Natural join
Self-join
Equijoin
Outer join (*)
12.
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
Mark
for Review
(1)
Points
e.department_id = d.department_id(+)
e.department_id(+) = d.department_id(+)
e.department_id = d.department_id
e.department_id(+) = d.department_id (*)
13.
Nonequijoins are normally used with which of the
following? (Choose two) Mark for Review
(1)
Points
(Choose all correct answers)
Ranges of numbers (*)
Ranges of rowids
Ranges of text
ranges of columns
Ranges of dates (*)
14.
Using Oracle Proprietary join syntax, which two
operators can be used in an outer join condition using the outer join operator
(+)? Mark for Review
(1)
Points
BETWEEN...AND... and IN
IN and =
AND and = (*)
OR and =
15.
Which statement about joining tables with a
non-equijoin is false? Mark for Review
(1)
Points
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
No comments:
Post a Comment