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
None of the above
This is an excellent information I would like to say thanks for providing with us. check it once at msbi online training
BalasHapusYou have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
BalasHapusWhich query should you issue? Mark for Review
(1) Points
SELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
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 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;
Which of the following best describes the function of an outer join?
BalasHapusAn outer join will return only data from the far left column in one table and the far right column in the other table.
An outer join will return data only if both tables contain an identical pair of columns.
An outer join will return only those rows that do not meet the join criteria.
An outer join will return all rows that meet the join criteria and will return NULL values from one table if no rows from the other table satisfy the join criteria. (*)
It has really a good stuff
BalasHapusAnd it has 99% correct answers
You have been asked to create a report that lists all corporate customers and all orders that they have placed. The customers should be listed alphabetically beginning with the letter 'A', and their corresponding order totals should be sorted from the highest amount to the lowest amount.
BalasHapusWhich of the following statements should you issue?
Mark for Review
(1) Points
SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount DESC; (*)
SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname ASC, amount ASC;
SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY amount DESC, companyname;
SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount;
modular lab furniture
BalasHapusworld777 agent
class 9 tuition classes in gurgaon
Ajmer road best Project
cloudkeeda
what is azure
azure free account
azure data factory
supportfly
BalasHapusleasepacket
Elevate your online presence with powerful singapore dedicated server. Experience unmatched performance and reliability for your business needs.
BalasHapusYou 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?
BalasHapusSELECT 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;
The following statement is an example of a nonequi-join?
BalasHapusSELECT 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 symbol is used to perform an outer join?
BalasHapus||
(+) (*)
*
#
The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
BalasHapusThe 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?
Equijoin
Outer join (*)
Natural join
Self-join
Escape to a luxurious best resort in jaipur, where royal elegance meets modern comfort. Enjoy world-class amenities, serene landscapes, and unforgettable experiences.
BalasHapus