Database programming section 3

1. A column alias can be specified in an ORDER BY Clause. True or False?
True (*)

2. Evaluate this SELECT statement:
SELECT last_name, first_name, salary
FROM employees;
How will the results of this query be sorted?
The database will display the rows in whatever order it finds it in the database, so no particular order. (*)

3. Evaluate this SELECT statement:
SELECT last_name, first_name, department_id, manager_id
FROM employees;
You need to sort data by manager id values and then alphabetically by employee last name and first name values. Which ORDER BY clause could you use?
ORDER BY manager_id, last_name, first_name (*)

4. 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 (*)

5. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50?
SELECT product_id, product_name
FROM products
WHERE price < 50;
(*)

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

7. 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;
(*)

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

9. 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?
Reorder the clauses in the query. (*)

10. Will the following statement return one row?
SELECT MAX(salary), MIN(Salary), AVG(SALARY)
FROM employees;

Yes, it will return the highest salary, the lowest salary, and the average salary from all employees. (*)

11. Which of the following is earliest in the rules of precedence?
Arithmetic operator (*)

12. Which statement about the logical operators is true?
The order of operator precedence is NOT, AND, and OR. (*)

13. Which comparison condition means "Less Than or Equal To"?

"<=" (*)

14. What will be the results of the following selection?
SELECT *
FROM employees
WHERE last_name NOT LIKE 'A%' AND last_name NOT LIKE 'B%'
All last names that do not begin with A or B (*)

15. Which of the following statements best describes the rules of precedence when using SQL?
The order in which the expressions are evaluated and calculated (*)