Sql Fundementals Ii (Multiple Table Alters)

Front 1

calculate the average salary, standard deviation on the salary, number of employees earning a commission, and the maximum hire date for those employees whose JOB_ID begins with SA.

Back 1

SELECT AVG(salary), STDDEV(salary),
COUNT(commission_pct),MAX(hire_date)
FROM employees
WHERE job_id LIKE 'SA%';

Front 2

count how many null-values there are in the managers_id row in the employees table.

Back 2

select COUNT(NVL) FROM employees;

NVL gives all values that are empty in a row. COUNT counts them.

Front 3

Give me the sum of all salaries, and how many employees there are with an employee_id. then group them by department_id and job_id.

Back 3

SELECT SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id;

Front 4

Write a query to display the following for those employees whose manager ID is less than 120:
Manager ID
Job ID and total salary for every job ID for employees who report to the same manager
Total salary of those managers
Total salary of those managers, irrespective of the job IDs

Back 4

SELECT manager_id, job_id, sum(salary)
FROM employees WHERE manager_id < 120
GROUP BY ROLLUP (manager_id, job_id)

Front 5

use the previous flashcard answer and rewrite the sql statement to test if the ROLLUP operation working properly with NULL values

Back 5

SELECT manager_id Managers, job_id Jobs, sum(salary) Total Salary, GROUPING( manager_id), manager_id, job_id, GROUPING(job_id)
FROM employees WHERE manager_id < 120
GROUP BY ROLLUP (manager_id, job_id)

Front 6

Write a query to display the following for those employees whose manager ID is less than 120:
Manager ID
Job and total salaries for every job for employees who report to the same manager
Total salary of those managers
Cross-tabulation values to display the total salary for every job, irrespective of the manager
Total salary irrespective of all job titles

Back 6

SELECT manager_id, job_id, sum(salary)
FROM employees WHERE manager_id < 120
GROUP BY CUBE (manager_id, job_id);

Front 7

Observe the output from the previous question. Write a query using the GROUPING function to determine whether the NULL values in the columns corresponding to the GROUP BY expressions are caused by the CUBE operation

Back 7

SELECT manager_id Managers, job_id Jobs, sum(salary) Total Salary, GROUPING( manager_id), manager_id, job_id, GROUPING(job_id)
FROM employees WHERE manager_id < 120
GROUP BY CUBE (manager_id, job_id);

Front 8

Using GROUPING SETS, write a query to display the following groupings:
department_id, manager_id, job_id
department_id, job_id
manager_id, job_id

Back 8

SELECT department_id, manager_id, job_id, SUM(salary) FROM employees GROUP BY
GROUPING SETS ((department_id, manager_id, job_id), (department_id, job_id), (manager_id, job_id));