Geekly articles weekly

SQL Rompecabezas entretenidos

Hola Habr!

Durante más de 3 años he enseñado SQL en varios centros de capacitación, y una de mis observaciones es que los estudiantes dominan y comprenden SQL mejor si establecen una tarea, y no solo hablan sobre las posibilidades y los fundamentos teóricos.

En este artículo compartiré con ustedes mi lista de tareas que les doy a los estudiantes como tarea y sobre las cuales realizamos 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) es un lenguaje de programación declarativo utilizado para crear, modificar y administrar datos en una base de datos relacional administrada por un sistema de administración de bases de datos apropiado. Aprende más

Puede leer sobre SQL de varias fuentes .
Este artículo no tiene la intención de enseñarle SQL desde cero.

Entonces vamos.

Utilizaremos el conocido esquema de recursos humanos en Oracle con sus tablas ( más ):


Observo que solo consideraremos las tareas SELECT. No hay problemas con DML y DDL.

Las 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. Obtenga 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 (departamento_id) con salario (salario), más de 4000
Solución
 SELECT * FROM employees WHERE department_id = 50 AND salary > 4000;


Mesa de empleados. Obtenga una lista de todos los empleados del departamento 20 y 30 (departamento_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 el nombre es 'a'
Solución
 SELECT * FROM employees WHERE first_name LIKE '%a';


Mesa de empleados. Obtenga una lista de todos los empleados del departamento 50 y 80 (departamento_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 contiene 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á en el rango de 8000 a 9000 (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 carácter '%'
Solución
 SELECT * FROM employees WHERE first_name LIKE '%\%%' ESCAPE '\';


Mesa de empleados. Obtenga una lista de todos los administradores de ID
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 contiene 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 XXXX.XXX.XXXX
Solución
 SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number FROM employees WHERE phone_number LIKE '___.___.____';


Mesa 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. Obtenga los nombres de los empleados sin la primera y la última letra del 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 es 'm' y un nombre largo mayor que 5
Solución
 SELECT * FROM employees WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;


Mesa doble Obtener el 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 trabajado en la empresa durante 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 AND INSTR (phone_number,'.',1,2) > 0;


Mesa de empleados. Obtenga una lista de todos los empleados que tienen al menos 3 caracteres en el valor de job_id después del signo '_', 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 todos los '.' 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 DUAL. Muestra 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 la fecha en que todos vinieron a trabajar 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 mayores salarios 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 vienen 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 DUAL. Saca la 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' WHEN salary >= 10000 THEN 'High' ELSE 'Unknown' END salary_level FROM employees;


Tabla de países Para cada país, muestre la región en la que se encuentra: 1-Europa, 2-América, 3-Asia, 4-África (sin unirse)
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;



Informe de datos agregados utilizando las funciones de grupo

Mesa de empleados. Obtenga un informe sobre department_id con un salario mínimo y máximo, con una fecha de llegada temprana y tardía al trabajo y con el número de empleados. Ordenar por número de empleados (descendente)
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 donde la cantidad 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 que 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 de 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. Obtenga la cantidad de departamentos en los que hay 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 departamento_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_id y un salario promedio redondeado para los empleados de 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 la suma de todas las letras de todos los country_name en los que 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 departamento_id en el que trabajan los empleados de varios (> 1) trabajo_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 es más de 5 y la suma de todos los salarios de sus subordinados es más de 50,000
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 para el cual el salario promedio de todos sus subordinados está en el rango de 6000 a 9000 que no reciben bonificaciones (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 terminen en 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. Obtenga el salario máximo entre todos los salarios promedio en el departamento
Solución
  SELECT MAX (AVG (salary)) FROM employees GROUP BY department_id;


Mesa de empleados. Obtenga la cantidad de empleados con la misma cantidad de letras en el nombre. Al mismo tiempo, muestre solo aquellos con una longitud de nombre de más de 5 y la cantidad de empleados con ese nombre es mayor de 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 de empleados, departamentos, ubicaciones, países, regiones. Obtenga una lista de regiones y la cantidad 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 de empleados, departamentos, ubicaciones, países, regiones. Obtenga información detallada sobre 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 a 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 a todos los empleados que no informan 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 (trabaja o dejó la empresa con la fecha de salida)
Un ejemplo:
nombre_nombre | estado
Jennifer | Abandonó la empresa al 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 de empleados, departamentos, ubicaciones, países, regiones. Obtenga una lista de empleados que viven en Europa (region_name)
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';


Mesa de 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;


Mesa de empleados, Departamentos. Mostrar a 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;


Mesa de empleados, Departamentos. Mostrar todos los departamentos sin un solo empleado
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 a 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. Mostrar a los empleados en el formato: Nombre, Título del trabajo, Nombre del departamento.
Un ejemplo:
Primer nombre | Job_title | Nombre_departamental
Donald | Envío | Secretario 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 los empleados cuyos gerentes obtuvieron un trabajo en 2005, pero al mismo tiempo, estos mismos empleados obtuvieron 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 los empleados cuyos gerentes obtuvieron un trabajo en el mes de enero de cualquier año y la duración del título de trabajo de estos empleados es de 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 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 mayor que el salario promedio de todos los empleados.
Solución
 SELECT * FROM employees WHERE salary > (SELECT AVG (salary) FROM employees);


Tabla de empleados, departamentos, ubicaciones. Obtenga una ciudad en la que los empleados ganen 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 15,000.
Solución
 SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE salary > 15000)


Mesa de empleados, Departamentos. Mostrar todos los departamentos sin un solo empleado
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 a 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 a 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;


Mesa de empleados, Departamentos. Mostrar a los 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. Mostrar a los empleados en el formato: Nombre, Título del trabajo, Nombre del departamento.
Un ejemplo:
Primer nombre | Job_title | Nombre_departamental
Donald | Envío | Secretario 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 los empleados cuyos gerentes obtuvieron un trabajo en 2005, pero al mismo tiempo, estos mismos empleados obtuvieron 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 los empleados cuyos gerentes obtuvieron un trabajo en el mes de enero de cualquier año y la duración del título de trabajo de estos empleados es de 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 fascinantes.
Si es posible, completaré esta lista de tareas.
También me complacerá cualquier comentario y sugerencia.

PD: Si a alguien se le ocurre una tarea SELECT interesante, escriba los comentarios y agréguelos a la lista.

Gracias

Source: https://habr.com/ru/post/461567/

More articles:


All Articles