Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/8

Click to flip

8 Cards in this Set

  • Front
  • Back
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.
SELECT AVG(salary), STDDEV(salary),
COUNT(commission_pct),MAX(hire_date)
FROM employees
WHERE job_id LIKE 'SA%';
count how many null-values there are in the managers_id row in the employees table.
select COUNT(NVL) FROM employees;

NVL gives all values that are empty in a row. COUNT counts them.
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.
SELECT SUM(salary), COUNT(employee_id) FROM employees GROUP BY department_id, job_id;
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
SELECT manager_id, job_id, sum(salary)
FROM employees WHERE manager_id < 120
GROUP BY ROLLUP (manager_id, job_id)
use the previous flashcard answer and rewrite the sql statement to test if the ROLLUP operation working properly with NULL values
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)
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
SELECT manager_id, job_id, sum(salary)
FROM employees WHERE manager_id < 120
GROUP BY CUBE (manager_id, job_id);
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
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);
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
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));