Database Programming Section 3

The following statement represents a multi-row function. True or False? SELECT UPPER(last_name) FROM employees;

False

The function COUNT is a single row function. True or False?

False

The following statement represents a multi-row function. True or False? SELECT MAX(salary) FROM employees

True

What will be the results of the following selection? SELECT * FROM employees WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%' a) All last names that do not begin with A or B b) No rows will be returned. There is a syntax error c) All rows will be returned d) All last names that begin with A or B

a) All last names that do not begin with A or B

Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column? a) ORDER BY b) WHERE c) FROM d) HAVING

a) ORDER BY

Evaluate this SELECT statement: SELECT last_name, first_name, salary FROM employees; How will the results of this query be sorted? a) The database will display the rows in whatever order it finds it in the database, so no particular order. b) The results will be sorted ascending by LAST_NAME and FIRST_NAME only. c) The results will be sorted ascending by LAST_NAME, FIRST_NAME, and SALARY. d) The results will be sorted ascending by the LAST_NAME column only.

a) The database will display the rows in whatever order it finds it in the database, so no particular order.

Evaluate this SQL statement: SELECT product_id, product_name, price FROM products ORDER BY product_name, price; What occurs when the statement is executed? a) The results are sorted alphabetically and then numerically. b) The results are sorted alphabetically only. c) The results are sorted numerically only. d) The results are sorted numerically and then alphabetically.

a) The results are sorted alphabetically and then numerically.

From left to right, what is the correct order of Precedence? a) Arithmetic, NOT, Logical, Comparison b) Arithmetic, Concatenation, Comparison, OR c) NOT, AND, OR, Arithmetic d) Arithmetic, NOT, Concatenation, Logical

b) Arithmetic, Concatenation, Comparison, OR

Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? a) SELECT product_id, product_name FROM products GROUP BY price < 50; b) SELECT product_id, product_name FROM products WHERE price < 50; c) SELECT product_id, product_name FROM products WHERE price < 50.00 GROUP BY price; d) SELECT product_id, product_name FROM products HAVING price < 50; e) SELECT product_id, product_name FROM products WHERE price <= 50;

b) SELECT product_id, product_name FROM products WHERE price < 50;

Which symbol in the WHERE clause means "Not Equal To"? (Choose Two) a) =+ b) >< c) <> d) NOT IN (..)

c and d c) <> d) NOT IN (..)

Evaluate this SQL statement: SELECT e.employee_id, e.last_name, e.first_name, m.manager_id FROM employees e, employees m ORDER BY e.last_name, e.first_name WHERE e.employee_id = m.manager_id; This statement fails when executed. Which change will correct the problem? a) Remove the table aliases in the ORDER BY clause. b) Include a HAVING clause. c) Reorder the clauses in the query. d) Remove the table aliases in the WHERE clause.

c) Reorder the clauses in the query.

Which columns can be added to the ORDER BY clause in the following SELECT statement? (Choose Three) SELECT first_name, last_name, salary, hire_date FROM employees WHERE department_id = 50 ORDER BY ?????; a) All the columns in the database b) The table name, EMPLOYEES, which would then automatically sort by all columns in the table c) All columns in the EMPLOYEES table d) last_name, first_name e) Any column in the EMPLOYEES table, any expression in the SELECT list or any ALIAS in the SELECT list

c, d, and e c) All columns in the EMPLOYEES table d) last_name, first_name e) Any column in the EMPLOYEES table, any expression in the SELECT list or any ALIAS in the SELECT list

Evaluate this SELECT statement: SELECT first_name, last_name, email FROM employees ORDER BY last_name; Which statement is true? a) The rows will be sorted in reverse alphabetical order by the LAST_NAME values. b) The rows will be sorted alphabetically by the FIRST_NAME and then the LAST_NAME values c) The rows will not be sorted. d) The rows will be sorted alphabetically by the LAST_NAME values.

d) The rows will be sorted alphabetically by the LAST_NAME values.

The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(9) PK LAST_NAME VARCHAR2(25) FIRST_NAME VARCHAR2(25) DEPARTMENT_ID NUMBER(9) Compare these two SQL statements: 1. SELECT DISTINCT department_id DEPT, last_name, first_name FROM employees ORDER BY department_id; 2. SELECT department_id DEPT, last_name, first_name FROM employees ORDER BY DEPT; How will the results differ? a) There is no difference in the result between the two statements. b) One of the statements will eliminate all duplicate DEPARTMENT_ID values. c) One of the statements will return a syntax error. d) The statements will sort on different column values.

d) The statements will sort on different column values.

Which of the following would be returned by this SQL statement: SELECT First_name, last_name, department_id FROM employees WHERE department_id IN(50,80) AND first_name LIKE ' C% ' OR last_name LIKE ' %s% ' a) FIRST_NAME LAST_NAME DEPARTMENT_ID Shelly Higgins 110 b) FIRST_NAME LAST_NAME DEPARTMENT_ID Curtis Davies 50 c) FIRST_NAME LAST_NAME DEPARTMENT_ID Randall Matos 50 d) FIRST_NAME LAST_NAME DEPARTMENT_ID Michael Hartstein 20 e) All of the above

e) All of the above