Database Programming Section 6 Quiz [PDF]

Citation preview

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