Section 3 Quiz Database Programming with SQL

• ·         Which of the following is earliest in the rules of precedence? Arithmetic operator (*)
• ·         Which of the following statements best describes the rules of precedence when using SQL?  The order in which the expressions are evaluated and calculated (*)
• ·         Which logical operator returns TRUE if either condition is true? OR (*)
• ·         Which statement about the ORDER BY clause is true? You can use a column alias in the ORDER BY clause. (*)
• ·         Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column? ORDER BY (*)

• ·         You attempt to query the database with this SQL statement:

SELECT product_id "Product Number", category_id "Category", price "Price"
FROM products
WHERE "Category" = 5570
ORDER BY "Product Number";

This statement fails when executed. Which clause contains a syntax error?

WHERE "Category" = 5570 (*)

• ·         Evaluate this SELECT statement:

SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
FROM employees
WHERE salary IS NOT NULL
ORDER BY last_name, 3;

Which clause contains an error?

SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)

• ·         Evaluate this SELECT statement:

SELECT last_name, first_name, email
FROM employees
ORDER BY email;

If the EMAIL column contains null values, which statement is true?

Null email values will be displayed last in the result. (*)

• ·         What clause must you place in a SQL statement to have your results sorted from highest to lowest salary?

·         ORDER BY salary DESC (*)

• ·         Evaluate this SELECT statement:

SELECT *
FROM employees
WHERE salary > 30000
AND department_id = 10
OR email IS NOT NULL;

Which statement is true? 

he AND condition will be evaluated before the OR condition. (*)

• ·         The PLAYERS table contains these columns:

PLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)

You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?

 SELECT last_name, first_name, team_id, salary 
FROM players 
WHERE salary BETWEEN 25000 AND 100000 
AND team_id BETWEEN 1200 AND 1500 
ORDER BY team_id, salary DESC; (*)

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

The statements will sort on different column values. (*)

• ·         The PLAYERS table contains these columns:

PLAYERS TABLE:
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
SALARY NUMBER(8,2)
TEAM_ID NUMBER(4)
MANAGER_ID NUMBER(9)
POSITION_ID NUMBER(4)

You want to display all players' names with position 6900 or greater.
You want the players names to be displayed alphabetically by last name and then by first name.
Which statement should you use to achieve the required results?

SELECT last_name, first_name 
FROM players 
WHERE position_id >= 6900 
ORDER BY last_name, first_name;

(*)

• ·         The following statement represents a multi-row function. True or False?

SELECT MAX(salary)
FROM employees 

True (*)

• ·         The following statement represents a multi-row function. True or False?

SELECT UPPER(last_name)
FROM employees;

False (*)