Lab 5 Review Quiz - Functions and Subqueries

Which of the following is a single-row SQL function that converts a string to uppercase in Oracle SQL?

• LOWER

• UPPER

• INITCAP

• CONCAT

What is the purpose of the COUNT function in SQL?

• To count the number of columns in a table

• To count the number of rows in a table

• To count the number of distinct values in a column

• To count the number of characters in a string

Which of the following SQL statements correctly uses a subquery to find employees who earn more than the average salary?

• SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• SELECT * FROM employees WHERE salary > AVG(salary);

• SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees);

• SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees);

In a correlated subquery, which of the following is true?

• The subquery is executed once for the entire query

• The subquery is executed once for each row processed by the outer query

• The subquery is executed only if the outer query returns no results

• The subquery is executed only if the outer query returns results

Which of the following is an example of an aggregate function in SQL?

• LENGTH

• SUM

• SUBSTR

• INSTR

What does the following SQL statement do? SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;

• It selects the maximum salary from the employees table

• It selects the department ID and the maximum salary for each department

• It selects the department ID and the total salary for each department

• It selects the department ID and the average salary for each department

Which of the following SQL functions can be used to find the remainder of a division operation?

• MOD

• DIV

• REM

• ROUND

Which of the following SQL statements correctly uses a correlated subquery to find employees who earn more than the average salary in their department?

• SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees);

• SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

• SELECT * FROM employees e WHERE salary > ALL (SELECT AVG(salary) FROM employees);

• SELECT * FROM employees e WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id);

What does the SQL statement 'SELECT COUNT(DISTINCT department_id) FROM employees;' do?

• Counts the total number of employees

• Counts the number of employees in each department

• Counts the number of distinct department IDs

• Counts the total number of department IDs

Which of the following SQL statements correctly uses a subquery to update the salary of employees in the 'Sales' department to the average salary of the 'HR' department?

• UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department = 'HR') WHERE department = 'Sales';

• UPDATE employees SET salary = (SELECT AVG(salary) FROM employees WHERE department = 'Sales') WHERE department = 'HR';

• UPDATE employees SET salary = (SELECT MAX(salary) FROM employees WHERE department = 'HR') WHERE department = 'Sales';

• UPDATE employees SET salary = (SELECT MIN(salary) FROM employees WHERE department = 'HR') WHERE department = 'Sales';