Database Section 6

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?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 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

  USING (job_id);

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

What is another name for a simple join or an inner join

• Equijoin

• Outer Join

• Nonequijoin

• Self Join

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?   

• An inner join

• A right outer join

• A cross join

• A left 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 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

Which statement about a self join is true?

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

• Table aliases must be used to qualify table names.

Which of the following database design concepts do you need in your tables to write Hierarchical queries? 

• Arc

• Recursive Relationship

• Non-Transferability

• Supertype

A NATURAL JOIN is based on:         

• Columns with the same name

• Columns with the same datatype and width

• Columns with the same name and datatype

• Tables with the same structure

For which condition would you use an equijoin query with the USING keyword

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

• You need to perform a join of the CUSTOMER and ORDER tables but limit the number of columns in the join condition.

Table aliases MUST be used with columns referenced in the JOIN USING clause.

• True

• False

The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.

• NATURAL ON

• ON

• WHEN

• USING