¡Hola Habr!
Durante más de 3 años he estado enseñando SQL en varios centros de formación, y una de mis observaciones es que los estudiantes dominan y entienden mejor SQL si se les asigna una tarea, y no solo hablan de las posibilidades y los fundamentos teóricos.
En este artículo, compartiré con ustedes mi lista de tareas que doy a los estudiantes como deberes y en las que llevamos a cabo varios tipos de tormentas de ideas, lo que conduce a una comprensión profunda y clara de SQL.
SQL (ˈɛsˈkjuˈɛl; lenguaje de consulta estructurado en inglés - "lenguaje de consulta estructurado") es un lenguaje de programación declarativo que se utiliza para crear, modificar y administrar datos en una base de datos relacional administrada por un sistema de administración de base de datos apropiado.
Puede leer sobre SQL de diferentes .
Este artículo no pretende enseñarle SQL desde cero.
Así que, vamos.
Usaremos el conocido en Oracle con sus tablas ():
Observo que solo consideraremos tareas en SELECCIONAR. No hay tareas en DML y DDL.
Tareas
Restricción y clasificación de datos
Mesa de empleados. Obtenga una lista con información sobre todos los empleados
Solución
SELECT * FROM employees
Mesa de empleados. Obtener una lista de todos los empleados llamados 'David'
Solución
SELECT *
FROM employees
WHERE first_name = 'David';
Mesa de empleados. Obtenga una lista de todos los empleados con job_id igual a 'IT_PROG'
Solución
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Mesa de empleados. Obtenga una lista de todos los empleados del departamento 50 (department_id) con un salario (salario) superior a 4000
Solución
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Mesa de empleados. Obtenga una lista de todos los empleados de los departamentos 20 y 30 (department_id)
Solución
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Mesa de empleados. Obtenga una lista de todos los empleados cuya última letra en su nombre es 'a'
Solución
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Mesa de empleados. Obtenga una lista de todos los empleados de los departamentos 50 y 80 (department_id) que tienen una bonificación (el valor en la columna comisión_pct no está vacío)
Solución
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Mesa de empleados. Obtenga una lista de todos los empleados cuyo nombre contenga al menos 2 letras 'n'
Solución
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Mesa de empleados. Obtenga una lista de todos los empleados cuyo nombre tiene más de 4 letras
Solución
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Mesa de empleados. Obtenga una lista de todos los empleados cuyo salario está entre 8000 y 9000 (ambos inclusive)
Solución
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Mesa de empleados. Obtenga una lista de todos los empleados cuyo nombre contiene el símbolo '%'
Solución
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Mesa de empleados. Obtener una lista de todos los ID de administrador
Solución
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Mesa de empleados. Obtenga una lista de empleados con sus puestos en el formato: Donald(sh_clerk)
Solución
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Uso de funciones de una sola fila para personalizar la salida
Mesa de empleados. Obtenga una lista de todos los empleados cuyo nombre tiene más de 10 letras
Solución
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Mesa de empleados. Obtenga una lista de todos los empleados que tienen la letra 'b' en su nombre (sin distinción entre mayúsculas y minúsculas)
Solución
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Mesa de empleados. Obtenga una lista de todos los empleados cuyo nombre contenga al menos 2 letras 'a'
Solución
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Mesa de empleados. Obtenga una lista de todos los empleados cuyo salario es un múltiplo de 1000
Solución
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Mesa de empleados. Obtenga el primer número de 3 dígitos del número de teléfono del empleado si su número tiene el formato ХХХ.ХХХ.ХХХХ
Solución
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Tabla de departamentos. Obtenga la primera palabra del nombre del departamento para aquellos con más de una palabra en el nombre
Solución
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Mesa de empleados. Obtener nombres de empleados sin la primera y última letra en el nombre
Solución
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Mesa de empleados. Obtenga una lista de todos los empleados cuya última letra en el nombre sea igual a 'm' y la longitud del nombre sea mayor a 5
Solución
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Mesa doble. Obtener la fecha del próximo viernes
Solución
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Mesa de empleados. Obtenga una lista de todos los empleados que han estado en la empresa por más de 17 años
Solución
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Mesa de empleados. Obtenga una lista de todos los empleados cuyo último dígito del número de teléfono es impar y consta de 3 números separados por un punto
Solución
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Mesa de empleados. Obtenga una lista de todos los empleados cuyo valor job_id después del signo '_' tiene al menos 3 caracteres, pero este valor después de '_' no es igual a 'CLERK'
Solución
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Mesa de empleados. Obtenga una lista de todos los empleados reemplazando todo '.' en el valor PHONE_NUMBER en '-'
Solución
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Uso de funciones de conversión y expresiones condicionales
Mesa de empleados. Obtenga una lista de todos los empleados que vinieron a trabajar el primer día del mes (cualquiera)
Solución
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Mesa de empleados. Obtenga una lista de todos los empleados que vinieron a trabajar en 2008
Solución
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Mesa DOBLE. Mostrar la fecha de mañana en el formato: Mañana es el segundo día de enero
Solución
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Mesa de empleados. Obtenga una lista de todos los empleados y su fecha de inicio en el formato: 21 de junio de 2007
Solución
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Mesa de empleados. Obtenga una lista de empleados con salarios aumentados en un 20%. Mostrar salario con signo de dólar
Solución
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Mesa de empleados. Obtenga una lista de todos los empleados que vinieron a trabajar en febrero de 2007.
Solución
SELECT *
FROM employees
WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')
AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));
SELECT *
FROM employees
WHERE to_char(hire_date,'MM.YYYY') = '02.2007';
Mesa DOBLE. Exportar fecha actual, + segundo, + minuto, + hora, + día, + mes, + año
Solución
SELECT SYSDATE now,
SYSDATE + 1 / (24 * 60 * 60) plus_second,
SYSDATE + 1 / (24 * 60) plus_minute,
SYSDATE + 1 / 24 plus_hour,
SYSDATE + 1 plus_day,
ADD_MONTHS (SYSDATE, 1) plus_month,
ADD_MONTHS (SYSDATE, 12) plus_year
FROM DUAL;
Mesa de empleados. Obtenga una lista de todos los empleados con salarios completos (salario + comisión_pct(%)) en el formato: $24,000.00
Solución
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Mesa de empleados. Obtenga una lista de todos los empleados e información sobre la disponibilidad de bonos salariales (Sí/No)
Solución
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Mesa de empleados. Obtenga el nivel de salario de cada empleado: Menos de 5000 se considera nivel bajo, mayor o igual a 5000 y menos de 10000 se considera nivel normal, mayor o igual a 10000 se considera nivel alto
Solución
SELECT first_name,
salary,
CASE
WHEN salary < 5000 THEN 'Low'
WHEN salary >= 5000 AND salary < 10000 THEN 'Normal'
ELSE 'High'
END salary_level
FROM employees;
Tabla de países. Para cada país, muestra la región en la que se encuentra: 1-Europa, 2-América, 3-Asia, 4-África (sin Unión)
Solución
SELECT country_name country,
DECODE (region_id,
1, 'Europe',
2, 'America',
3, 'Asia',
4, 'Africa',
'Unknown')
region
FROM countries;
SELECT country_name
country,
CASE region_id
WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
WHEN 4 THEN 'Africa'
ELSE 'Unknown'
END
region
FROM countries;
Informes de datos agregados usando las funciones de grupo
Mesa de empleados. Obtenga un informe por departamento_id con salario mínimo y máximo, fechas de llegada temprana y tardía y número de empleados. Ordenar por número de empleados (desc)
Solución
SELECT department_id,
MIN (salary) min_salary,
MAX (salary) max_salary,
MIN (hire_date) min_hire_date,
MAX (hire_date) max_hire_Date,
COUNT (*) count
FROM employees
GROUP BY department_id
order by count(*) desc;
Mesa de empleados. ¿Cuántos empleados cuyos nombres comienzan con la misma letra? Ordenar por cantidad. Mostrar solo aquellos en los que el número es mayor que 1
Solución
SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)
FROM employees
GROUP BY SUBSTR (first_name, 1, 1)
HAVING COUNT (*) > 1
ORDER BY 2 DESC;
Mesa de empleados. ¿Cuántos empleados trabajan en el mismo departamento y reciben el mismo salario?
Solución
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Mesa de empleados. Obtenga un informe de cuántos empleados fueron contratados cada día de la semana. Ordenar por cantidad
Solución
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Mesa de empleados. Obtenga un informe sobre cuántos empleados fueron contratados por año. Ordenar por cantidad
Solución
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Mesa de empleados. Obtener el número de departamentos que tienen empleados
Solución
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Mesa de empleados. Obtenga una lista de department_id con más de 30 empleados
Solución
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Mesa de empleados. Obtenga una lista de department_ids y el salario promedio redondeado de los empleados en cada departamento.
Solución
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Tabla de países. Obtenga una lista de region_id suma de todas las letras de todos los nombres de países en los que hay más de 60
Solución
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Mesa de empleados. Obtenga una lista de department_id en la que trabajan empleados de varios (> 1) job_id
Solución
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Mesa de empleados. Obtenga una lista de manager_id cuyo número de subordinados sea mayor a 5 y la suma de todos los salarios de sus subordinados sea mayor a 50000
Solución
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Mesa de empleados. Obtenga una lista de manager_id cuyo salario promedio de todos sus subordinados está entre 6000 y 9000 que no reciben bonos (commission_pct está vacío)
Solución
SELECT manager_id, AVG (salary) avg_salary
FROM employees
WHERE commission_pct IS NULL
GROUP BY manager_id
HAVING AVG (salary) BETWEEN 6000 AND 9000;
Mesa de empleados. Obtenga el salario máximo de todos los empleados job_id que termina con la palabra 'CLERK'
Solución
SELECT MAX (salary) max_salary
FROM employees
WHERE job_id LIKE '%CLERK';
SELECT MAX (salary) max_salary
FROM employees
WHERE SUBSTR (job_id, -5) = 'CLERK';
Mesa de empleados. Consigue el salario máximo entre todos los salarios medios del departamento
Solución
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Mesa de empleados. Obtenga el número de empleados con el mismo número de letras en su nombre. Al mismo tiempo, muestre solo aquellos cuyo nombre sea mayor a 5 y el número de empleados con el mismo nombre sea mayor a 20. Ordenar por longitud de nombre
Solución
SELECT LENGTH (first_name), COUNT (*)
FROM employees
GROUP BY LENGTH (first_name)
HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20
ORDER BY LENGTH (first_name);
SELECT LENGTH (first_name), COUNT (*)
FROM employees
WHERE LENGTH (first_name) > 5
GROUP BY LENGTH (first_name)
HAVING COUNT (*) > 20
ORDER BY LENGTH (first_name);
Visualización de datos de varias tablas mediante combinaciones
Tabla Empleados, Departamentos, Ubicaciones, Países, Regiones. Obtenga una lista de regiones y el número de empleados en cada región
Solución
SELECT region_name, COUNT (*)
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
GROUP BY region_name;
Tabla Empleados, Departamentos, Ubicaciones, Países, Regiones. Obtenga información detallada de cada empleado:
Nombre, Apellido, Departamento, Trabajo, Calle, País, Región
Solución
SELECT First_name,
Last_name,
Department_name,
Job_id,
street_address,
Country_name,
Region_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id);
Mesa de empleados. Mostrar todos los gerentes que tienen más de 6 empleados
Solución
SELECT man.first_name, COUNT (*)
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
GROUP BY man.first_name
HAVING COUNT (*) > 6;
Mesa de empleados. Mostrar todos los empleados que no reportan a nadie
Solución
SELECT emp.first_name
FROM employees emp
LEFT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE man.FIRST_NAME IS NULL;
SELECT first_name
FROM employees
WHERE manager_id IS NULL;
Tabla de empleados, Job_history. La tabla de empleados almacena todos los empleados. La tabla Job_history almacena los empleados que abandonaron la empresa. Obtenga un informe sobre todos los empleados y su estado en la empresa (Empleados o dejaron la empresa con la fecha de salida)
Ejemplo:
nombre | estado
jennifer | Dejó la empresa el 31 de diciembre de 2006
Clara | Actualmente trabajando
Solución
SELECT first_name,
NVL2 (
end_date,
TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'),
'Currently Working')
status
FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);
Tabla Empleados, Departamentos, Ubicaciones, Países, Regiones. Obtenga una lista de los empleados que viven en Europa (nombre_región)
Solución
SELECT first_name
FROM employees
JOIN departments USING (department_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
JOIN regions USING (region_id)
WHERE region_name = 'Europe';
SELECT first_name
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
JOIN countries c ON (l.country_id = c.country_id)
JOIN regions r ON (c.region_id = r.region_id)
WHERE region_name = 'Europe';
Tabla Empleados, Departamentos. Mostrar todos los departamentos con más de 30 empleados
Solución
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Tabla Empleados, Departamentos. Mostrar todos los empleados que no están en ningún departamento
Solución
SELECT first_name
FROM employees e
LEFT JOIN departments d ON (e.department_id = d.department_id)
WHERE d.department_name IS NULL;
SELECT first_name
FROM employees
WHERE department_id IS NULL;
Tabla Empleados, Departamentos. Mostrar todos los departamentos sin empleados
Solución
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Mesa de empleados. Mostrar todos los empleados que no tienen subordinados
Solución
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Tabla Empleados, Trabajos, Departamentos. Muestre a los empleados en el formato: First_name, Job_title, Department_name.
Ejemplo:
nombre | título del trabajo | Nombre de Departamento
donald | envío | Empleado de envío
Solución
SELECT first_name, job_title, department_name
FROM employees e
JOIN jobs j ON (e.job_id = j.job_id)
JOIN departments d ON (d.department_id = e.department_id);
Mesa de empleados. Obtenga una lista de empleados cuyos gerentes consiguieron un trabajo en 2005, pero al mismo tiempo, estos trabajadores consiguieron un trabajo antes de 2005
Solución
SELECT emp.*
FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE TO_CHAR (man.hire_date, 'YYYY') = '2005'
AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');
Mesa de empleados. Obtenga una lista de empleados cuyos gerentes obtuvieron un trabajo en el mes de enero de cualquier año y la longitud del job_title de estos empleados tiene más de 15 caracteres
Solución
SELECT emp.*
FROM employees emp
JOIN employees man ON (emp.manager_id = man.employee_id)
JOIN jobs j ON (emp.job_id = j.job_id)
WHERE TO_CHAR (man.hire_date, 'MM') = '01' AND LENGTH (j.job_title) > 15;
Uso de subconsultas para resolver consultas
Mesa de empleados. Obtenga una lista de los empleados con el nombre más largo.
Solución
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Mesa de empleados. Obtenga una lista de empleados con un salario superior al salario promedio de todos los empleados.
Solución
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Tabla Empleados, Departamentos, Ubicaciones. Obtenga la ciudad en la que los empleados ganan menos en total.
Solución
SELECT city
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city
HAVING SUM (salary) =
( SELECT MIN (SUM (salary))
FROM employees e
JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id)
GROUP BY city);
Mesa de empleados. Obtenga una lista de empleados cuyo gerente recibe un salario de más de 15000.
Solución
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Tabla Empleados, Departamentos. Mostrar todos los departamentos sin empleados
Solución
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Mesa de empleados. Mostrar todos los empleados que no son gerentes
Solución
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Mesa de empleados. Mostrar todos los gerentes que tienen más de 6 empleados
Solución
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Tabla Empleados, Departamentos. Mostrar empleados que trabajan en el departamento de TI
Solución
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tabla Empleados, Trabajos, Departamentos. Muestre a los empleados en el formato: First_name, Job_title, Department_name.
Ejemplo:
nombre | título del trabajo | Nombre de Departamento
donald | envío | Empleado de envío
Solución
SELECT first_name,
(SELECT job_title
FROM jobs
WHERE job_id = e.job_id)
job_title,
(SELECT department_name
FROM departments
WHERE department_id = e.department_id)
department_name
FROM employees e;
Mesa de empleados. Obtenga una lista de empleados cuyos gerentes consiguieron un trabajo en 2005, pero al mismo tiempo, estos trabajadores consiguieron un trabajo antes de 2005
Solución
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2005')
AND hire_date < TO_DATE ('01012005', 'DDMMYYYY');
Mesa de empleados. Obtenga una lista de empleados cuyos gerentes obtuvieron un trabajo en el mes de enero de cualquier año y la longitud del job_title de estos empleados tiene más de 15 caracteres
Solución
SELECT *
FROM employees e
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE TO_CHAR (hire_date, 'MM') = '01')
AND (SELECT LENGTH (job_title)
FROM jobs
WHERE job_id = e.job_id) > 15;
Eso es todo por ahora.
Espero que las tareas hayan sido interesantes y emocionantes.
Agregaré a esta lista tanto como sea posible.
También estaré encantado de cualquier comentario y sugerencia.
PD: Si a alguien se le ocurre una tarea interesante en SELECCIONAR, escriba en los comentarios, la agregaré a la lista.
Gracias.
Fuente: habr.com