NEW
Font size
WorksheetsITPC 116 Section 6
Total questions: 10
Worksheet time: 11mins
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
