Section 6 Quiz Oracle Database Programming with SQL
Section 6 Quiz(SOLUTION)
(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);
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
(1) Points
Equijoin (*)
Outer Join
Nonequijoin
Self Join
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
(1) Points
An inner join
A right outer join (*)
A cross join
A left outer 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
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);
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
(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
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
(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.
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
(1) Points
Arc
Recursive Relationship (*)
Non-Transferability
Supertype
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
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 (*)
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
(1) Points
True
False (*)
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
(1) Points
(Choose all correct answers)
Full outer join
Equijoin (*)
Simple join (*)
Self join (*)
Nonequijoin
Equijoin (*)
Simple join (*)
Self join (*)
Nonequijoin
11. A NATURAL JOIN is based on: Mark for Review
(1) Points
(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
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
(1) Points
Self-join
Cross join
Outer join
Natural 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
(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.
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
(1) Points
True
False (*)
False (*)
15. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes. Mark for Review
(1) Points
(1) Points
NATURAL ON
USING (*)
ON
WHEN
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
(1) Points
Natural join (*)
Self-join
Cross join
Outer 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
(1) Points
(Choose all correct answers)
Self join (*)
Full outer join
Nonequijoin
Simple 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
(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.
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
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;
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
(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
(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;
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
(1) Points
Equijoin
Self join
Outer join (*)
Natural join
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)
(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.
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
(1) Points
True
False (*)
False (*)
10. The primary advantage of using JOIN ON is: Mark for Review
(1) Points
(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.
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
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
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
(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
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
(1) Points
Arc
Supertype
Recursive Relationship (*)
Non-Transferability
Supertype
Recursive Relationship (*)
Non-Transferability
14. Which statement about a self join is true? Mark for Review
(1) Points
(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.
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
(1) Points
True
False (*)
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
(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
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
(1) Points
Supertype
Arc
Non-Transferability
Recursive Relationship (*)
Arc
Non-Transferability
Recursive Relationship (*)
3. Which statement about a self join is true? Mark for Review
(1) Points
(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. (*)
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
(1) Points
True (*)
False
False
5. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review
(1) Points
(1) Points
Supertype
Non-Transferability
Recursive Relationship (*)
Arc
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)
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
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);
(*)
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
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);
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
(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;
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
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 (*)
Optimal Join
Equijoin
Outer Join (*)
10. Which of the following conditions will cause an error on a NATURAL JOIN? Mark for Review
(1) Points
(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. (*)
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
(1) Points
Natural join (*)
Self-join
Cross join
Outer 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)
(1) Points
(Choose all correct answers)
Full outer join
Nonequijoin
Equijoin (*)
Self join (*)
Simple 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
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
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
(1) Points
The FROM clause
A USING clause (*)
A HAVING clause
The SELECT 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)
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;
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
(1) Points
(Choose all correct answers)
Equijoin (*)
Full outer join
Self join (*)
Nonequijoin
Simple join (*)
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
(1) Points
An inner join
A full outer join
A cross join (*)
An equijoin
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
(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. (*)
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)
(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.
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
(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.
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
(1) Points
USING (*)
WHEN
ON
NATURAL ON
WHEN
ON
NATURAL ON
7. Which statement about a self join is true? Mark for Review
(1) Points
(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.
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
(1) Points
Recursive Relationship (*)
Supertype
Non-Transferability
Arc
Supertype
Non-Transferability
Arc
9. Hierarchical queries can walk both Top-Down and Bottom-Up. True or False? Mark for Review
(1) Points
(1) Points
True (*)
False
False
10. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review
(1) Points
(1) Points
True
False (*)
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
(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
(*)
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
(1) Points
Natural join
Outer join (*)
Equijoin
Self 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
(1) Points
Full outer joins (*)
Left outer joins
Natural joins
Right 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
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);
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
(1) Points
Equijoin (*)
Self Join
Nonequijoin
Outer Join
Self Join
Nonequijoin
Outer Join
No comments:
Post a Comment