1. Give Syntax Example for each of the following Group Functions:
AVG
SUM
MIN
MAX
COUNT
DISTINCT
STDDEV
VARIANCE
2. Provide 2 Syntax examples of using the GROUP BY Clause on Multiple Columns
3. Provide 2 Syntax examples of creating joins with the USING Clause
4. Provide 2 Syntax examples of the HAVING Clause with Subqueries
5. Using the Employees and Departments tables below, provide the answer for Select Command using these tables. The goal is you walk though the Select Command on these tables manually and determine the answer (expected result) based on your reading of from text book: - Just show the expected output when you run the syntax.
EMPLOYEES Table
End of DEPARTMENTS Table
What is the output when the following script is executed?
SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
What is the output when the following script is executed?
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%' ;
What is the output when the following script is executed?
SELECT employees.employee_id, employees.last_name,
departments.location_id, department_id
FROM employees JOIN departments
USING (department_id) ;
What is the output when the following script is executed?
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;