ITPC 116 Section 6

The join column must be included in the select statement when you use the NATURAL JOIN clause. 

• True

• False

Which of the following conditions will cause an error on a NATURAL JOIN?

• When you attempt to use two tables that have a common field.

• 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.

• If the columns having the same names have different data types.

A NATURAL JOIN is based on:

• Columns with the same name and datatype

• Columns with the same name

• Columns with the same datatype and width

• Tables with the same structure

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?

• SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary 

  FROM employees 

  NATURAL JOIN jobs 

  USING (job_id);

• 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;

• 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 ON (employees.job_title = jobs.job_title);

Which query represents the correct syntax for a left outer join?

• SELECT companyname, orderdate, total 

  FROM customers 

  LEFT JOIN orders 

  ON customers.cust_id = orders.cust_id;

• SELECT companyname, orderdate, total 

  FROM customers 

  OUTER JOIN orders 

  ON customers.cust_id = orders.cust_id;

• SELECT companyname, orderdate, total 

  FROM customers 

  LEFT OUTER JOIN orders 

  ON customers.cust_id = orders.cust_id;

• SELECT companyname, orderdate, total 

  FROM customers 

  LEFT OUTER orders 

  ON customers.cust_id = orders.cust_id;

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?

• SELECT departments.department_id, employees.first_name, employees.last_name 

  FROM employees 

  RIGHT OUTER JOIN departments 

  ON (employees.employee_id = departments.manager_id);

• SELECT departments.department_id, employees.first_name, employees.last_name 

  FROM employees 

  LEFT OUTER JOIN departments 

  WHERE (employees.department_id = departments.department_id);

• SELECT departments.department_id, employees.first_name, employees.last_name 

  FROM employees 

  FULL OUTER JOIN departments 

  ON (employees.employee_id = departments.manager_id);

• SELECT departments.department_id, employees.first_name, employees.last_name 

  FROM employees , departments 

  WHERE employees.employee_id 

  RIGHT OUTER JOIN departments.manager_id;

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) ;

• Inner Join

• Outer Join

• Equijoin

• Optimal Join

Which statement about a self join is true?

• The NATURAL JOIN clause must be used.

• Table aliases must be used to qualify table names.

• Table aliases cannot be used to qualify table names.

• A self join must be implemented by defining a view.

Evaluate 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 self join

• a cross join

• a left outer join

• a full outer join

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?

• SELECT worker.last_name, worker.hire_date, manager.last_name, m.hire_date 

  FROM employees worker JOIN employees manager 

  ON worker.manager_id = manager.employee_id 

  WHERE worker.hire_date < manager.hire_date 

• SELECT worker.last_name, worker.hire_date, manager.last_name, manager.hire_date 

  FROM employees worker JOIN employees manager 

  ON worker.manager_id = manager.employee_id 

  WHERE worker.hire_date > manager.hire_date

• SELECT worker.last_name, worker.hire_date, manager.last_name, manager.hire_date 

  FROM employees worker JOIN employees worker 

  ON worker.manager_id = worker.employee_id 

  WHERE worker.hire_date < worker.hire_date

• SELECT worker.last_name, worker.hire_date, manager.last_name, manager.hire_date 

  FROM employees worker JOIN employees manager 

  ON worker.manager_id != manager.employee_id 

  WHERE worker.hire_date < manager.hire_date