Database Programming Section 7

Evaluate this SQL statement: SELECT e.employee_id, e.last_name, e.first_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id AND employees.department_id > 5000 ORDER BY 4; Which clause contains a syntax error? a) AND employees.department_id > 5000 b) WHERE e.department_id = d.department_id c) FROM employees e, departments d d) ORDER BY 4; e) SELECT e.employee_id, e.last_name, e.first_name, d.department_name

a) AND employees.department_id > 5000

To perform a valid outer join between DEPARTMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement: SELECT d.department_name, e.last_name FROM employees e, departments d WHERE a) e.department_id(+) = d.department_id b) e.department_id = d.department_id c) e.department_id(+) = d.department_id(+) d) e.department_id = d.department_id(+)

a) e.department_id(+) = d.department_id

Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?

True

The following statement is an example of a nonequi-join? SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; True or False?

True

Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)? a) BETWEEN...AND... and IN b) OR and = c) IN and = d) AND and =

d) AND and =

The following is a valid outer join statement: SELECT c.country_name, d.department_name FROM countries c, departments d WHERE c.country_id (+) = d.country_id (+) True or False?

False

Which symbol is used to perform an outer join? a) (+) b) || c) # d) *

a) (+)

If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a cartesian join on those two tables? a) 50 b) 5 c) 10 d) 15

a) 50

Which statement about joining tables with a non-equijoin is false? a) A WHERE clause must specify a column in one table that is compared to a column in the second table b) The number of join conditions required is always one less than the number of tables being joined c) The columns being joined must have compatible data types d) None of the above

a) A WHERE clause must specify a column in one table that is compared to a column in the second table

What is the result of a query that selects from two tables but includes no join condition? a) A selection of rows from the first table only b) A selection of matched rows from both tables c) A Cartesian product d) A syntax error

c) A Cartesian product

Will the following statement work? SELECT department_name, last_name FROM employees, departments WHERE department_id = department_id; a) Yes, Oracle will resolve which department_id column comes from which table. b) No, Oracle will not allow joins in the WHERE clause c) No, Oracle will return a Column Ambiguously Defined error. d) Yes, there are no syntax errors in that statement

c) No, Oracle will return a Column Ambiguously Defined error.

When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause? a) 1 b) 0 c) 3 d) 2

d) 2

You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue. Which query should you issue? a) SELECT e.first_name, e.last_name, s.sales FROM employees, sales WHERE e.employee_id = s.employee_id AND revenue >= 100000; b)SELECT first_name, last_name, sales FROM employees e, sales s WHERE e.employee_id = s.employee_id AND revenue > 100000; c) SELECT e.first_name, e.last_name, s.sales FROM employees e, sales s WHERE e.employee_id = s.employee_id AND revenue > 100000; d) SELECT e.first_name, e.last_name, s.sales FROM employees e, sales s WHERE e.employee_id = s.employee_id AND revenue >= 100000;

d) SELECT e.first_name, e.last_name, s.sales FROM employees e, sales s WHERE e.employee_id = s.employee_id AND revenue >= 100000;

You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task? a) SELECT last_name, first_name, salary FROM employees WHERE salary > 25000 AND dept_id = 10; b) SELECT last_name, first_name, salary FROM employees WHERE salary = 25000 AND dept_id = 10; c)SELECT last_name, first_name, salary FROM employees WHERE salary <= 25000 AND dept_id = 10; d) SELECT last_name, first_name, salary FROM employees WHERE salary != 25000 AND dept_id = 10;

d) SELECT last_name, first_name, salary FROM employees WHERE salary != 25000 AND dept_id = 10;

Which statement about outer joins is true? a) The FULL, RIGHT, or LEFT keyword must be included. b) Outer joins are always evaluated before other types of joins in the query. c) The tables must be aliased. d) The OR operator cannot be used to link outer join conditions.

d) The OR operator cannot be used to link outer join conditions.