(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
No comments:
Post a Comment