Section
6 Quiz
(Answer all questions in this
section)
1. Given
the following descriptions of the employees and jobs tables, which of the
following scripts will display each employeeï¾’s possible minimum and maximum
salaries based on their job title?
EMPLOYEES
Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2
(25)
EMAIL NOT NULL VARCHAR2
(25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS
Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2
(35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1)
Points
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees
NATURAL
JOIN jobs;
(*)
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j
USING
(job_id);
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j;
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees e
FULL
JOIN jobs j (job_id);
SELECT e.first_name, e.last_name, e.job_id,
j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j ON (e.job_title = j.job_title);
2. What
is another name for a simple join or an inner join? Mark for Review
(1)
Points
Equijoin (*)
Outer Join
Nonequijoin
Self Join
3. You
need to join the EMPLOYEE_HIST and EMPLOYEES tables. The EMPLOYEE_HIST table
will be the first table in the FROM clause. All the matched and unmatched rows
in the EMPLOYEES table need to be displayed. Which type of join will you use? Mark for Review
(1)
Points
An inner join
A right outer join (*)
A cross join
A left outer join
4. EMPLOYEES
Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS
Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER
4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A
query is needed to display each department and its manager name from the above
tables. However, not all departments have a manager but we want departments
returned in all cases. Which of the following SQL: 1999 syntax scripts will
accomplish the task?
Mark for Review
(1)
Points
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e
RIGHT
OUTER JOIN departments d
ON
(e.employee_id = d.manager_id);
(*)
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e, departments d
WHERE
e.employee_id
RIGHT
OUTER JOIN d.manager_id;
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e
FULL
OUTER JOIN departments d
ON
(e.employee_id = d.manager_id);
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e
LEFT
OUTER JOIN departments d
WHERE
(e.department_id = d.department_id);
5. Which
select statement will return the last name and hire date of an employee and
his/ her manager for employees that started in the company before their
managers? Mark for Review
(1)
Points
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees w
WHERE
w.manager_id = w.employee_id
AND
w.hire_date < w.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date < m.hire_date
(*)
SELECT w.last_name, w.hire_date, m.last_name,
m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date > m.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id != m.employee_id
AND
w.hire_date < m.hire_date
6. Which statement about a self join is
true? Mark for Review
(1)
Points
A self join must be implemented by
defining a view.
Table aliases must be used to
qualify table names. (*)
The NATURAL JOIN clause must be
used.
Table aliases cannot be used to
qualify table names.
7. Which of the following database design
concepts do you need in your tables to write Hierarchical queries? Mark for Review
(1)
Points
Arc
Recursive Relationship (*)
Non-Transferability
Supertype
8. Evaluate this SELECT statement:
SELECT
*
FROM
employee e, employee m
WHERE
e.mgr_id = m.emp_id;
Which
type of join is created by this SELECT statement?
Mark for Review
(1)
Points
a full outer join
a left outer join
a cross join
a self join (*)
9. Hierarchical queries MUST use the LEVEL
pseudo column. True or False? Mark for
Review
(1)
Points
True
False (*)
10. A
join between tables where the result set includes matching values from both
tables but does NOT return any unmatched rows could be called which of the
following? (Choose three) Mark for Review
(1)
Points
(Choose all
correct answers)
Full outer join
Equijoin (*)
Simple join (*)
Self join (*)
Nonequijoin
11. A
NATURAL JOIN is based on: Mark
for Review
(1)
Points
Columns with the same name
Columns with the same datatype and
width
Columns with the same name and
datatype (*)
Tables with the same structure
12. You
need to join two tables that have two columns with the same name, datatype, and
precision. Which type of join would you create to join the tables on both of
the columns? Mark for Review
(1)
Points
Self-join
Cross join
Outer join
Natural join (*)
13. For
which condition would you use an equijoin query with the USING keyword? Mark for Review
(1)
Points
The CUSTOMER and ORDER tables have
no columns with identical names.
You need to perform a join of the
CUSTOMER and ORDER tables but limit the number of columns in the join
condition. (*)
The ORDER table contains a column that
has a referential constraint to a column in the PRODUCT table.
The CUSTOMER and ORDER tables have a
corresponding column, CUST_ID. The CUST_ID column in the ORDER table contains
null values that need to be displayed.
14. Table
aliases MUST be used with columns referenced in the JOIN USING clause. True or
False? Mark for Review
(1)
Points
True
False (*)
15. The
keywords JOIN _____________ should be used to join tables with the same column
names but different datatypes. Mark
for Review
(1)
Points
NATURAL ON
USING (*)
ON
WHEN
1. You need to join two tables that have
two columns with the same name, datatype, and precision. Which type of join
would you create to join the tables on both of the columns? Mark for Review
(1)
Points
Natural join (*)
Self-join
Cross join
Outer join
2. A join between tables where the result
set includes matching values from both tables but does NOT return any unmatched
rows could be called which of the following? (Choose three) Mark for Review
(1)
Points
(Choose all
correct answers)
Self join (*)
Full outer join
Nonequijoin
Simple join (*)
Equijoin (*)
3. Which of the following conditions will
cause an error on a NATURAL JOIN?
Mark for Review
(1)
Points
If the columns having the same names
have different data types, then an error is returned. (*)
When you attempt to write it as an
equijoin.
When the NATURAL JOIN clause is
based on all columns in the two tables that have the same name.
If it selects rows from the two
tables that have equal values in all matched columns.
4. Given the following descriptions of the
employees and jobs tables, which of the following scripts will display each
employeeï¾’s
possible minimum and maximum salaries based on their job title?
EMPLOYEES
Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2
(25)
EMAIL NOT NULL VARCHAR2
(25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS
Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2
(35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1)
Points
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees
NATURAL
JOIN jobs;
(*)
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j
USING
(job_id);
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j ON (e.job_title = j.job_title);
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees e
FULL
JOIN jobs j (job_id);
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j;
5. Which query represents the correct
syntax for a left outer join?
Mark for Review
(1)
Points
SELECT companyname, orderdate, total
FROM
customers c
LEFT
JOIN orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM
customers c
LEFT
OUTER orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM
customers c
OUTER
JOIN orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM
customers c
LEFT
OUTER JOIN orders o
ON
c.cust_id = o.cust_id;
(*)
6. Which query will retrieve all the rows
in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table? Mark for Review
(1)
Points
SELECT e.last_name, e.department_id,
d.department_name
FROM
employees e
RIGHT
OUTER JOIN departments d ON (e.department_id = d.department_id);
SELECT e.last_name, e.department_id,
d.department_name
FROM
employees e
JOIN
departments d USING (e.department_id = d.department_id);
SELECT e.last_name, e.department_id,
d.department_name
FROM
employees e
LEFT
OUTER JOIN departments d ON (e.department_id = d.department_id);
(*)
SELECT e.last_name, e.department_id,
d.department_name
FROM
employees e
NATURAL
JOIN departments d;
7. Which type of join returns rows from one
table that have NO direct match in the other table? Mark for Review
(1)
Points
Equijoin
Self join
Outer join (*)
Natural join
8. The primary advantages of using JOIN ON
is: (Select two) Mark for Review
(1)
Points
(Choose all
correct answers)
The join happens automatically based
on matching column names and data types.
It permits columns that donï¾’t have matching data types to be
joined. (*)
It permits columns with different
names to be joined. (*)
It will display rows that do not
meet the join condition.
9. Table aliases MUST be used with columns
referenced in the JOIN USING clause. True or False? Mark for Review
(1)
Points
True
False (*)
10. The
primary advantage of using JOIN ON is:
Mark for Review
(1)
Points
It easily produces a Cartesian
product between the tables in the statement.
The join happens automatically based
on matching column names and data types.
It permits columns that donï¾’t have matching data types to be
joined.
It permits columns with different
names to be joined. (*)
It will display rows that do not
meet the join condition.
11. Evaluate
this SELECT statement:
SELECT
*
FROM
employee e, employee m
WHERE
e.mgr_id = m.emp_id;
Which
type of join is created by this SELECT statement?
Mark for Review
(1)
Points
a self join (*)
a full outer join
a left outer join
a cross join
12. Which
select statement will return the last name and hire date of an employee and
his/ her manager for employees that started in the company before their
managers? Mark for Review
(1)
Points
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id != m.employee_id
AND w.hire_date
< m.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date > m.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date < m.hire_date
(*)
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees w
WHERE
w.manager_id = w.employee_id
AND
w.hire_date < w.hire_date
13. Which
of the following database design concepts do you need in your tables to write
Hierarchical queries? Mark for
Review
(1)
Points
Arc
Supertype
Recursive Relationship (*)
Non-Transferability
14. Which
statement about a self join is true?
Mark for Review
(1)
Points
Table aliases must be used to
qualify table names. (*)
Table aliases cannot be used to
qualify table names.
The NATURAL JOIN clause must be
used.
A self join must be implemented by
defining a view.
15. Hierarchical
queries MUST use the LEVEL pseudo column. True or False? Mark for Review
(1)
Points
True
False (*)
1. Which select statement will return the
last name and hire date of an employee and his/ her manager for employees that
started in the company before their managers?
Mark for Review
(1)
Points
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id != m.employee_id
AND
w.hire_date < m.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees w
WHERE
w.manager_id = w.employee_id
AND
w.hire_date < w.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date < m.hire_date
(*)
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date > m.hire_date
2. Which of the following database design
concepts is implemented with a self join?
Mark for Review
(1)
Points
Supertype
Arc
Non-Transferability
Recursive Relationship (*)
3. Which statement about a self join is
true? Mark for Review
(1)
Points
Table aliases cannot be used to
qualify table names.
A self join must be implemented by
defining a view.
The NATURAL JOIN clause must be
used.
Table aliases must be used to
qualify table names. (*)
4. Hierarchical queries can walk both
Top-Down and Bottom-Up. True or False?
Mark for Review
(1)
Points
True (*)
False
5. Which of the following database design
concepts do you need in your tables to write Hierarchical queries? Mark for Review
(1)
Points
Supertype
Non-Transferability
Recursive Relationship (*)
Arc
6. EMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
DEPARTMENT_ID NUMBER (4)
DEPARTMENTS
Table:
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER
4
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER (6)
A
query is needed to display each department and its manager name from the above
tables. However, not all departments have a manager but we want departments
returned in all cases. Which of the following SQL: 1999 syntax scripts will
accomplish the task?
Mark for Review
(1)
Points
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e, departments d
WHERE
e.employee_id
RIGHT
OUTER JOIN d.manager_id;
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e
LEFT
OUTER JOIN departments d
WHERE
(e.department_id = d.department_id);
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e
FULL
OUTER JOIN departments d
ON
(e.employee_id = d.manager_id);
SELECT d.department_id,
e.first_name, e.last_name
FROM
employees e
RIGHT
OUTER JOIN departments d
ON
(e.employee_id = d.manager_id);
(*)
7. Given the following descriptions of the
employees and jobs tables, which of the following scripts will display each
employeeï¾’s
possible minimum and maximum salaries based on their job title?
EMPLOYEES
Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2
(25)
EMAIL NOT NULL VARCHAR2
(25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS
Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2
(35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1)
Points
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j;
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees
NATURAL
JOIN jobs;
(*)
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j ON (e.job_title = j.job_title);
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j
USING
(job_id);
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees e
FULL
JOIN jobs j (job_id);
8. Which query represents the correct
syntax for a left outer join?
Mark for Review
(1)
Points
SELECT companyname, orderdate, total
FROM
customers c
LEFT
OUTER orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM
customers c
LEFT
JOIN orders o
ON
c.cust_id = o.cust_id;
SELECT companyname, orderdate, total
FROM
customers c
LEFT
OUTER JOIN orders o
ON
c.cust_id = o.cust_id;
(*)
SELECT companyname, orderdate, total
FROM
customers c
OUTER
JOIN orders o
ON
c.cust_id = o.cust_id;
9. The following statement is an example of
what kind of join?
SELECT
car.vehicle_id, driver.name
FROM
car
LEFT
OUTER JOIN driver ON (driver_id) ;
Mark for Review
(1)
Points
Inner Join
Optimal Join
Equijoin
Outer Join (*)
10. Which
of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
(1)
Points
When the NATURAL JOIN clause is
based on all columns in the two tables that have the same name.
If it selects rows from the two
tables that have equal values in all matched columns.
When you attempt to write it as an
equijoin.
If the columns having the same names
have different data types, then an error is returned. (*)
11. You
need to join two tables that have two columns with the same name, datatype, and
precision. Which type of join would you create to join the tables on both of
the columns? Mark for Review
(1)
Points
Natural join (*)
Self-join
Cross join
Outer join
12. A
join between tables where the result set includes matching values from both
tables but does NOT return any unmatched rows could be called which of the
following? (Choose three) Mark for Review
(1)
Points
(Choose all
correct answers)
Full outer join
Nonequijoin
Equijoin (*)
Self join (*)
Simple join (*)
13. The
following is a valid SQL statement.
SELECT
e.employee_id, e.last_name,
d.location_id, department_id
FROM
employees e JOIN departments d
USING
(department_id) ;
True
or False?
Mark for Review
(1)
Points
True (*)
False
14. Which
keyword in a SELECT statement creates an equijoin by specifying a column name
common to both tables? Mark for
Review
(1)
Points
The FROM clause
A USING clause (*)
A HAVING clause
The SELECT clause
15. Below
find the structures of the PRODUCTS and VENDORS tables:
PRODUCTS
PRODUCT_ID
NUMBER
PRODUCT_NAME
VARCHAR2 (25)
VENDOR_ID
NUMBER
CATEGORY_ID
NUMBER
VENDORS
VENDOR_ID
NUMBER
VENDOR_NAME
VARCHAR2 (25)
ADDRESS
VARCHAR2 (30)
CITY
VARCHAR2 (25)
REGION
VARCHAR2 (10)
POSTAL_CODE
VARCHAR2 (11)
You
want to create a query that will return an alphabetical list of products,
including the product name and associated vendor name, for all products that
have a vendor assigned.
Which
two queries could you use?
Mark for Review
(1)
Points
(Choose all
correct answers)
SELECT p.product_name, v.vendor_name
FROM
products p
JOIN
vendors v
ON
(vendor_id)
ORDER
BY p.product_name;
SELECT p.product_name, v.vendor_name
FROM
products p
NATURAL
JOIN vendors v
ORDER
BY p.product_name;
(*)
SELECT p.product_name, v.vendor_name
FROM
products p
JOIN
vendors v
USING
(p.vendor_id)
ORDER
BY p.product_name;
SELECT p.product_name, v.vendor_name
FROM
products p
JOIN
vendors v
USING
(vendor_id)
ORDER
BY p.product_name;
(*)
SELECT p.product_name, v.vendor_name
FROM
products p
LEFT
OUTER JOIN vendors v
ON
p.vendor_id = v.vendor_id
ORDER
BY p.product_name;
1. A join between tables where the result
set includes matching values from both tables but does NOT return any unmatched
rows could be called which of the following? (Choose three) Mark for Review
(1)
Points
(Choose all
correct answers)
Equijoin (*)
Full outer join
Self join (*)
Nonequijoin
Simple join (*)
2. You need to join all the rows in the
EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join
should you create? Mark for
Review
(1)
Points
An inner join
A full outer join
A cross join (*)
An equijoin
3. Which of the following conditions will
cause an error on a NATURAL JOIN?
Mark for Review
(1)
Points
When the NATURAL JOIN clause is
based on all columns in the two tables that have the same name.
If it selects rows from the two
tables that have equal values in all matched columns.
When you attempt to write it as an
equijoin.
If the columns having the same names
have different data types, then an error is returned. (*)
4. The primary advantages of using JOIN ON
is: (Select two) Mark for Review
(1)
Points
(Choose all
correct answers)
It permits columns that donï¾’t have matching data types to be
joined. (*)
The join happens automatically based
on matching column names and data types.
It permits columns with different names
to be joined. (*)
It will display rows that do not
meet the join condition.
5. For which condition would you use an
equijoin query with the USING keyword?
Mark for Review
(1)
Points
You need to perform a join of the
CUSTOMER and ORDER tables but limit the number of columns in the join
condition. (*)
The CUSTOMER and ORDER tables have a
corresponding column, CUST_ID. The CUST_ID column in the ORDER table contains
null values that need to be displayed.
The ORDER table contains a column
that has a referential constraint to a column in the PRODUCT table.
The CUSTOMER and ORDER tables have
no columns with identical names.
6. The keywords JOIN _____________ should
be used to join tables with the same column names but different datatypes. Mark for Review
(1)
Points
USING (*)
WHEN
ON
NATURAL ON
7. Which statement about a self join is
true? Mark for Review
(1)
Points
The NATURAL JOIN clause must be
used.
Table aliases cannot be used to
qualify table names.
Table aliases must be used to
qualify table names. (*)
A self join must be implemented by
defining a view.
8. Which of the following database design
concepts do you need in your tables to write Hierarchical queries? Mark for Review
(1)
Points
Recursive Relationship (*)
Supertype
Non-Transferability
Arc
9. Hierarchical queries can walk both
Top-Down and Bottom-Up. True or False?
Mark for Review
(1)
Points
True (*)
False
10. Hierarchical
queries MUST use the LEVEL pseudo column. True or False? Mark for Review
(1)
Points
True
False (*)
11. Which
select statement will return the last name and hire date of an employee and
his/ her manager for employees that started in the company before their
managers? Mark for Review
(1)
Points
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id != m.employee_id
AND
w.hire_date < m.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees w
WHERE
w.manager_id = w.employee_id
AND
w.hire_date < w.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date > m.hire_date
SELECT w.last_name, w.hire_date,
m.last_name, m.hire_date
FROM
employees w , employees m
WHERE
w.manager_id = m.employee_id
AND
w.hire_date < m.hire_date
(*)
12. Which
type of join returns rows from one table that have NO direct match in the other
table? Mark for Review
(1)
Points
Natural join
Outer join (*)
Equijoin
Self join
13. What
types of joins will return the unmatched values from both tables in the join? Mark for Review
(1)
Points
Full outer joins (*)
Left outer joins
Natural joins
Right outer joins
14. Given
the following descriptions of the employees and jobs tables, which of the
following scripts will display each employeeï¾’s possible minimum and maximum
salaries based on their job title?
EMPLOYEES
Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2
(25)
EMAIL NOT NULL VARCHAR2
(25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS
Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2
(35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
Mark for Review
(1)
Points
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j
USING
(job_id);
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees
NATURAL
JOIN jobs;
(*)
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j ON (e.job_title = j.job_title);
SELECT e.first_name, e.last_name,
e.job_id, j.min_salary, j.max_salary
FROM
employees e
NATURAL
JOIN jobs j;
SELECT first_name, last_name,
job_id, min_salary, max_salary
FROM
employees e
FULL
JOIN jobs j (job_id);
15. What
is another name for a simple join or an inner join? Mark for Review
(1)
Points
Equijoin (*)
Self Join
Nonequijoin
Outer Join
For which of the following tables will all the values be retrieved even if there is no match in the other?
ReplyDeleteSELECT employees.last_name, employees.department_id, departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON (employees.department_id = departments.department_id);
Mark for Review
(1) Points
Both
Neither. The LEFT OUTER JOIN limits the value to the matching department ids.
employees (*)
department
You can do nonequi-joins with ANSI-Syntax. True or False?
ReplyDeleteTrue (*)
False
Evaluate this SELECT statement:
ReplyDeleteSELECT patient.lname || ', ' || patient.fname as "Patient", physician.lname || ', ' || physician.fname as "Physician", admission.admission
FROM patient
JOIN physician
ON (physician.physician_id = admission.physician_id)
JOIN admission
ON (patient.patient_id = admission.patient_id);
Which clause generates an error?
JOIN admission
ON (physician.physician_id = admission.physician_id); (*)
ON (patient.patient_id = admission.patient_id)
JOIN physician
Artikel terkait bisnis online :
ReplyDelete1. Bisnis dan Pekerjaan Online Terbaik
2. Pemahaman Bisnis Online bersama Facebook
3. Pemahaman Bisnis Online bersama Google
4. Panduan Bisnis Bitcoin & Cryptocurrency
5. Panduan Memulai Trading Forex
6. Panduan Memulai Trading Saham
This comment has been removed by the author.
ReplyDelete4. If you select rows from two tables (employees and departments) using the outer join specified in the example, what will you get?
ReplyDeleteSELECT employees.last_name, employees.department_id, departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON (employees.department_id = departments.department_id);
Mark for Review
(1) Points
All employees that do not have a department_id assigned to them
All employees including those that do not have a departement_id assigned to them (*)
No employees as the statement will fail
None of the above
12. Which SELECT statement implements a self join?
ReplyDeleteMark for Review
(1) Points
SELECT item.part_id, type.product_id
FROM part item JOIN product type
ON item.part_id = type.product_id;
SELECT item.part_id, type.product_id
FROM part item JOIN product type
ON item.part_id = type.product_id (+);
SELECT item.part_id, type.product_id
FROM part item JOIN part type
ON item.part_id = type.product_id; (*)
SELECT item.part_id, type.product_id
FROM part item JOIN product type
ON item.part_id =! type.product_id;
The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
ReplyDeleteMark for Review
(1) Points
True
False (*)
Nice Work Thanks For Sharing This Post
ReplyDeleteAdmission Manager
one year ago was really a tough year for me,my wife and my family . I have never been the type to believe in online platforms to make money, I have always believed in rendering services and adding apparent values to anything and then making money from it. But when COVID struck,my business had to shut down,then I was left with no choice but to think of other legit ways to make money . I was presented a lucrative offer in ‘FX INVEST OPTION’ since it was the only thing that was making sense as at the time I wanted to invest. All thanks to = GLOBALFXINVESTMENT2@GMAIL.COM, I cannot thank them enough,one of my business associates referred me to them when he heard of my situation after reaching out to loan some money from him ,I contacted them via their email,and related necessary information and requirements as requested by the specialist who asked for all the necessary details and in less than a week ,they were able to retrieve all I lost to this fake investors,It seemed like a dream to me because I had lost all hope. I told them I will tell the whole world about what they did for me,they literally restored me to life. If you’re seeking funds/Cryptocurrency from online scammers , contact them rightly away;- at GLOBALFXINVESTMENT2@GMAIL.COM and also his Telegram @FX_INVEST_OPTION
ReplyDeleteAlso contact him +1 (505) 317-3340…
10. The JOIN _____________ keywords should be used to join tables with the same column names, but different types of data.
ReplyDeleteNATURAL ON
WHEN
OVER
USING (*)
All thanks to Mr Anderson for helping with my profits and making my fifth withdrawal possible. I'm here to share an amazing life changing opportunity with you. its called Bitcoin / Forex trading options. it is a highly lucrative business which can earn you as much as $2,570 in a week from an initial investment of just $200. I am living proof of this great business opportunity. If anyone is interested in trading on bitcoin or any cryptocurrency and want a successful trade without losing notify Mr Anderson now.Whatsapp: (+447883246472)
ReplyDeleteEmail: tdameritrade077@gmail.com
The following is a valid SQL statement.
ReplyDeleteSELECT employees.employee_id, employees.last_name, departments.location_id, department_id
FROM employees JOIN departments
USING (department_id) ;
True or False?
True (*)
False
Deni Ace: Section 6 Quiz Oracle Database Programming With Sql >>>>> Download Now
ReplyDelete>>>>> Download Full
Deni Ace: Section 6 Quiz Oracle Database Programming With Sql >>>>> Download LINK
>>>>> Download Now
Deni Ace: Section 6 Quiz Oracle Database Programming With Sql >>>>> Download Full
>>>>> Download LINK 7J
To endure in such a competitive world, each of business group is attempting much more difficult in order to make their strong online presence. All of them require an excellent and enticing internet site for their business. Hire SEO VA To Promote Your Website
ReplyDeleteBest Data science Training provided by Vepsun in Bangalore for the last 12 years. Our Trainer has more than 20+ Years
ReplyDeleteof IT Experience in teaching Virtualization and bootcamp topics.. we are very delighted to say that Vepsun is
the Top data science training Provider in Bangalore. We provide the best atmosphere for our students to learn.
Our Trainers have great experience and are highly skilled in IT Professionals. It includes a mixture of
infrastructure as service and packaged software as service offerings and also automation. We have trained
more than 10000 students in data science and our trainer has been awarded as the best Citrix and programming
trainer in India.
www.vepsun.in
They also argue that business owners have an obligation to their employees, and that employees have the right to a labour law consultant , even if they may not enjoy it.
ReplyDeleteThanks for sharing a useful information..
ReplyDeleteBest Mulesoft Training
Best
Mulesoft Online Training
Thanks for sharing the valuable information for Web designers. Keep Blogging!!!
ReplyDeleteVery good article, thanks for the information
ReplyDeletePrint Online Murah
Tempat Print Jakarta Timur
Jasa Print 24 Jam
Tempat Print Buka 24 Jam
Jasa Print Murah Jakarta Timur
kombi
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks for sharing the nice article.
ReplyDeleteTrademark Registration In Mumbai
Thanks for sharing this informative article in detail on Oracle Database Programming with SQL. If you have any requirement to Hire Database Managers for your project. Please visit us.
ReplyDeleteGiven the following descriptions of the employees and jobs tables, which of the following scripts will display each employees' possible minimum and maximum salaries based on their job title?
ReplyDeleteEMPLOYEES Table:
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER (6)
FIRST_NAME VARCHAR2 (20)
LAST_NAME NOT NULL VARCHAR2 (25)
EMAIL NOT NULL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2 (10)
SALARY NUMBER (8,2)
COMMISSION_PCT NUMBER (2,2)
MANAGER_ID NUMBER (6)
DEPARTMENT_ID NUMBER (4)
JOBS Table:
Name Null? Type
JOB_ID NOT NULL VARCHAR2 (10)
JOB_TITLE NOT NULL VARCHAR2 (35)
MIN_SALARY NUMBER (6)
MAX_SALARY NUMBER (6)
SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary
FROM employees
NATURAL JOIN jobs ON (employees.job_title = jobs.job_title);
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
FULL JOIN jobs (job_id);
SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary
FROM employees
NATURAL JOIN jobs;
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs; (*)
SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary
FROM employees
NATURAL JOIN jobs
USING (job_id);
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:
ReplyDeleteEMPLOYEES
EMPLOYEE_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?
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
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
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
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;
Great Article. Thanks for providing the such a wonderful blog which helps to clarify my doubts. Keep Blogging!!! Pool Tiles Sydney
ReplyDeleteGreat blog!!! Thannks for sharing the wonderful blog which clarify my entire doubts. Keep blogging!!! Stone Tiles Brisbane
ReplyDeleteGreat blog, Thanks for sharing Company Registration in Singapore
ReplyDeleteWhich two sets of join keywords create a join that will include unmatched rows from the first table specified in the SELECT statement?
ReplyDeleteOUTER JOIN and USING
RIGHT OUTER JOIN and LEFT OUTER JOIN
LEFT OUTER JOIN and FULL OUTER JOIN (*)
USING and HAVING
The primary advantages of using JOIN ON is: (Choose two)
ReplyDelete(Choose all correct answers)
It permits columns that donï¾’t have matching data types to be joined. (*)
The join happens automatically based on matching column names and data types.
It permits columns with different names to be joined. (*)
It will display rows that do not meet the join condition.
Which statement about a natural join is true?
ReplyDeleteColumns with the same names cannot be included in the SELECT list of the query.
Columns with the same names must have compatible data types.
Columns with the same names must not have identical data types.
Columns with the same names must have the same datatype. (*)
Evaluate this SELECT statement:
ReplyDeleteSELECT *
FROM employee worker JOIN employee manager
ON worker.mgr_id = manager.emp_id;
Which type of join is created by this SELECT statement?
a cross join
a self join (*)
a full outer join
a left outer joinEvaluate this SELECT statement:
SELECT *
FROM employee worker JOIN employee manager
ON worker.mgr_id = manager.emp_id;
Which type of join is created by this SELECT statement?
a cross join
a self join (*)
a full outer join
a left outer join
Which SELECT statement implements a self join?
ReplyDeleteSELECT worker.employee_id, manager.manager_id
FROM employees worker JOIN departments manager
ON worker.employee_id = manager.manager_id;
SELECT worker.employee_id, manager.manager_id
FROM employees worker JOIN employees manager
ON manager.employee_id = worker.manager_id; (*)
SELECT worker.employee_id, manager.manager_id
FROM employees worker JOIN managers manager
ON worker.employee_id = manager.manager_id;
SELECT worker.employee_id, manager.manager_id
FROM employees worker
NATURAL JOIN employees manager;
he following statement is an example of what kind of join?
ReplyDeleteSELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver USING(driver_id) ;
Equijoin
Inner Join
Outer Join (*)
Optimal Join
Which of the following conditions will cause an error on a NATURAL JOIN?
ReplyDeleteIf the columns having the same names have different data types. (*)
When the NATURAL JOIN clause is based on all columns in the two tables that have the same name.
If it selects rows from the two tables that have equal values in all matched columns.
When you attempt to use two tables that have a common field.
Which of the following statements is the simplest description of a nonequijoin?
ReplyDeleteA join condition containing something other than an equality operator (*)
A join condition that is not equal to other joins
A join that joins a table to itself
A join condition that includes the (+) on the left hand side
Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?
ReplyDeleteFULL INNER JOIN
FULL OUTER JOIN (*)
Use any equijoin syntax
LEFT OUTER JOIN AND RIGHT OUTER JOIN
You created the CUSTOMERS and ORDERS tables by issuing these CREATE TABLE statements in sequence:
ReplyDeleteCREATE TABLE customers
(custid varchar2(5),
companyname varchar2(30),
contactname varchar2(30),
address varchar2(30),
city varchar2(20),
state varchar2(30),
phone varchar2(20),
constraint pk_customers_01 primary key (custid));
CREATE TABLE orders
(orderid varchar2(5) constraint pk_orders_01 primary key,
orderdate date,
total number(15),
custid varchar2(5) references customers (custid));
You have been instructed to compile a report to present the information about orders placed by customers who reside in Nashville. Which query should you issue to achieve the desired results?
SELECT orderid, orderdate, total
FROM orders
NATURAL JOIN customers ON orders.custid = customers.custid
WHERE city = 'Nashville';
SELECT custid, companyname
FROM customers
WHERE city = 'Nashville';
SELECT orderid, orderdate, total
FROM orders
WHERE city = 'Nashville';
SELECT orderid, orderdate, total
FROM orders
JOIN customers ON orders.custid = customers.custid
WHERE city = 'Nashville'; (*)