Free Printable Quizz SQL Joel y Ángel

Worksheets

Quizz SQL Joel y Ángel

Total questions: 10

Worksheet time: 14mins

Name

Class

Date

¿Cuál de estas consultas es correcta para saber qué empleados cobran más que la media por departamento del departamento que le pases por parámetro, y su salario?

SELECT first_name

FROM employees

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = &department_id_parametro)

SELECT first_name, salary

FROM employees

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = &department_id_parametro)

SELECT first_name, salary

FROM employees

HAVING salary < (

SELECT AVG(salary)

FROM employees

WHERE department_id = &department_id_parametro)

SELECT first_name, salary

FROM employees

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE &department_id = &department_id_parametro)

¿Con cuál de estas consultas NO de te cargarías toda la base de datos?

INSERT INTO employees...

SAVEPOINT empleado_creado;

COMMIT;

DELETE FROM employees;

ROLLBACK;

COMMIT;

INSERT INTO employees...

SAVEPOINT empleado_creado;

DELETE FROM employees;

SAVEPOINT empleado_eliminado;

ROLLBACK TO empleado_eliminado;

COMMIT;

INSERT INTO employees...

SAVEPOINT empleado_creado;

DELETE FROM employees;

SAVEPOINT empleado_eliminado;

ROLLBACK TO empleado_creado;

COMMIT;

INSERT INTO employees...

COMMIT;

DELETE FROM employees;

SAVEPOINT empleado_creado

ROLLBACK TO empleado_creado;

COMMIT;

¿Cuáles son los departamentos cuya media salarial es mayor que tu sueldo?

Tu id de empleado es = 200.

SELECT employee_id, AVG(salary) AS salario_promedio_empleado

FROM employees

GROUP BY employee_id

HAVING AVG(salary) > (

SELECT AVG(salary) AS salario_promedio_departamento

FROM departments

WHERE employee_id = 200);

SELECT department_id, AVG(salary) AS salario_promedio_departamento FROM employees

GROUP BY department_id

HAVING AVG(salary) > (

SELECT AVG(salary) AS salario_promedio_empleado

FROM employees

WHERE employee_id = “200” );

SELECT department_id, AVG(salary) AS salario_promedio_departamento

FROM employees

GROUP BY department_id

HAVING AVG(salary) > (

SELECT AVG(salary) AS salario_promedio_empleado

FROM employees

WHERE employee_id = 200);

Ninguna es correcta.

Muestra el nombre y salario de los empleados que cobran menos que Daniel (nombre) sumándole a su salario un valor numérico pasado por teclado, y ordenado por salario.

SELECT last_name, salary

FROM employees WHERE salary < (SELECT salary + &incremento

FROM employees

WHERE first_name LIKE 'Daniel%')

ORDER BY salary;

SELECT first_name, salary

FROM employees WHERE salary < (SELECT salary + &incremento

FROM employees

WHERE first_name LIKE 'Daniel%')

ORDENAR BY salary;

SELECT first_name, salary

FROM employees WHERE salary + &incremento < (SELECT salary

FROM employees

WHERE first_name LIKE 'Daniel%')

ORDER BY salary;

Ninguna es correcta.

Muestra el salario medio (máximo dos decimales) con su departamento de aquellos departamentos cuya media salarial sea mayor que la media salarial de todos los departamentos en conjunto.

SELECT department_id, ROUND(AVG(salary),2)

FROM employees

GROUP BY department_id

HAVING AVG(salary) > (SELECT AVG(AVG(salary))

FROM employees

GROUP BY department_id);

SELECT e.department_id, ROUND(AVG(salary),2)

FROM e.employees FULL JOIN d.departments

WHERE AVG(salary) > (SELECT AVG(AVG(salary))

FROM employees

GROUP BY departments);

SELECT department_id, ROUND(AVG(salary),3)

FROM employees

WHERE AVG(salary) > (SELECT AVG(AVG(salary))

FROM e.employees FULL JOIN d.departments

WHERE e.department_id= e.department_id);

SELECT e.department_id, ROUND(AVG(salary),2)

FROM d.employees FULL JOIN e.departments

WHERE AVG(salary) > (SELECT AVG(AVG(salary))

FROM employees

GROUP BY departments);

Modifica el salario del empleado con id 99.

Su salario debe ser igual a la media de los empleados del departamento al que pertenece Tayler

los cuales cobran más que Tayler.

UPDATE employees
SET salary = (SELECT AVG (salary)
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE first_name = Tayler
AND salary > (SELECT salary
FROM employees
WHERE first_name = Tayler))

WHERE EMPLOYEE_ID = 99;

UPDATE employees

SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id

FROM employees

WHERE first_name = 'Tayler')

OR salary > (SELECT salary

FROM employees

WHERE first_name = 'Tayler'))

WHERE EMPLOYEE_ID = 99;

UPDATE employees
SET salary = (SELECT AVG(salary)
FROM employees
WHERE department_id =
(SELECT department_id
FROM employees
WHERE first_name = 'Tayler'
AND salary > (SELECT salary FROM employees WHERE first_name = 'Tayler')) WHERE EMPLOYEE_ID = 99;

UPDATE employees

SET salary = (SELECT AVG(salary)

FROM employees

WHERE department_id = (SELECT department_id

FROM employees

WHERE first_name = 'Tayler')

AND salary > (SELECT salary

FROM employees

WHERE first_name = 'Tayler'))

WHERE EMPLOYEE_ID = 99;

Muestra los detalles actuales y anteriores del puesto de todos los empleados, sin repetir. Debe ordenarse de manera ascendente por id del empleado.

SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history
ORDER BY employee_id ASC;

SELECT employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history
ORDER BY employee_id ASC;

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

SELECT employee_id, job_id
FROM employees
UNION ALL
SELECT employee_id, job_id
FROM job_history
ORDER BY employee_id ASC;

Muestra el nombre de los departamentos con su ciudad y país, solo de aquellos departamentos que su ciudad pertenezca a US

SELECT d.department_name, l.city, l.country_id
FROM departments d JOIN locations l
ON (d.location_id = l.location_id)
WHERE l.city IN (SELECT city FROM locations WHERE country_id = 'US' );

SELECT d.department_name, l.city, l.country_id
FROM departments d JOIN locations l
ON (d.location_id = l.location_id)
HAVING l.city = ANY (SELECT city FROM locations WHERE country_id = 'US' );

SELECT d.department_name, l.city, l.country_id
FROM departments d LEFT OUTER JOIN locations l
ON (d.location_id = l.location_id)
HAVING l.city = (SELECT city FROM locations WHERE country_id = 'US' );

SELECT d.department_name, l.city, l.country_id
FROM departments d JOIN locations l
ON (d.location_id = l.location_id)
WHERE l.city = (SELECT city FROM locations WHERE country_id = 'US' );

¿Cuál de estas consultas devuelve el departamento o departamentos con el menos número de empleados?

SELECT department_id, COUNT(employee_id)

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) = ( SELECT MIN(COUNT(employee_id)) FROM employees

GROUP BY

department_id );

SELECT department_id, COUNT(department_id)

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) = ( SELECT MIN(COUNT(employee_id)) FROM employees

GROUP BY

department_id );

SELECT department_id, COUNT(employee_id)

FROM employees

GROUP BY department_id

HAVING COUNT(employee_id) = ( SELECT MIN(COUNT(employee_id)) FROM employees

GROUP BY

employee_id );

SELECT department_id, COUNT(employee_id)

FROM employees

GROUP BY department_id

WHERE COUNT(employee_id) = ( SELECT MIN(COUNT(employee_id)) FROM employees

GROUP BY

department_id );

10.

Mostrar los apellidos de los empleados que no han cambiado sus puestos ni una vez.

SELECT employee_id FROM employees MINUS SELECT employee_id FROM job_history;

SELECT last_name FROM employees MINUS SELECT employee_id FROM job_history;

SELECT last_name FROM employees WHERE employee_id IS NOT IN (SELECT employee_id FROM job_history);

SELECT e.last_name FROM employees e JOIN e.employee_id ON (SELECT j.employee_id FROM job_history);

Reset

Quizz SQL Joel y Ángel

Total questions: 10

Worksheet time: 14mins

Similar Resources on Wayground