7 0 58 KB
Database Programming Section 6 Quiz Section 6 Quiz (Answer all questions in this section)
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)
Nonequijoin
Equijoin (*)
Self join (*)
Full outer join
Simple join (*)
[Correct]
2. Review
Correct
Which statement about a natural join is true?
Mark for
(1) Points
Columns with the same names must have identical data types.
Columns with the same names must have compatible data types.
Columns with the same names must have the same precision and datatype. (*)
Columns with the same names cannot be included in the SELECT list of the query.
[Correct]
Correct
3. 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
[Correct]
Correct
4. Which of the following statements is the simplest description of a nonequijoin? Mark for Review (1) Points
A join condition that is not equal to other joins
A join condition that includes the (+) on the left hand side
A join condition containing something other than an equality operator (*)
A join that joins a table to itself
[Correct]
Correct
5. The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes. Mark for Review (1) Points
ON
NATURAL ON
USING (*)
WHEN
[Correct] 6.
Correct
Evaluate this SELECT statement:
SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission FROM patient a
JOIN physician b ON (b.physician_id = c.physician_id) JOIN admission c ON (a.patient_id = c.patient_id);
Which clause generates an error? Mark for Review (1) Points
JOIN admission c
ON (b.physician_id = c.physician_id); (*)
JOIN physician b
ON (a.patient_id = c.patient_id)
[Correct]
Correct
7. Which of the following database design concepts do you need in your tables to write Hierarchical queries? Mark for Review (1) Points
Supertype
Recursive Relationship (*)
Non-Transferability
Arc
[Correct]
8.
Correct
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 cross join
a full outer join
a left outer join
a self join (*)
[Correct]
Correct
9. 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
[Correct]
Correct
10. Which SELECT statement implements a self join? for Review (1) Points
SELECT p.part_id, t.product_id FROM part p, part t WHERE p.part_id = t.product_id;
(*)
Mark
SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id =! t.product_id;
SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id = t.product_id;
SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id = t.product_id (+);
[Correct]
Correct
11. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review (1) Points
True
False (*)
[Correct]
Correct
12. If you select rows from two tables (employees and departments) using the outer join specified in the example, what will you get?
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); 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
[Correct]
Correct
13. What types of joins will return the unmatched values from both tables in the join? Mark for Review (1) Points
Right outer joins
Left outer joins
Full outer joins (*)
Natural joins
[Correct]
Correct
14. You need to display all the rows (both matching and nonmatching) from both the EMPLOYEE and EMPLOYEE_HIST tables. Which type of join would you use? Mark for Review (1) Points
A full outer join (*)
A right outer join
An inner join
A left outer join
[Correct]
Correct
15. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match? Mark for Review (1) Points
FULL INNER JOIN
FULL OUTER JOIN (*)
LEFT OUTER JOIN AND RIGHT OUTER JOIN
Use any equijoin syntax