👩🏾‍🏫 🕣 🍭 SQL Quebra-cabeças divertidos 🔓 🚵 👨🏻‍🍳

Olá Habr!

Há mais de três anos, ensino SQL em vários centros de treinamento, e uma das minhas observações é que os alunos dominam e entendem melhor o SQL se definem uma tarefa, e não apenas falam sobre as possibilidades e fundamentos teóricos.

Neste artigo, compartilharei com você minha lista de tarefas que dou aos alunos como lição de casa e sobre as quais realizamos vários tipos de brainstorms, o que leva a um entendimento profundo e claro do SQL.

SQL (structuredsˈkjuˈɛl; idioma de consulta estruturada em inglês) é uma linguagem de programação declarativa usada para criar, modificar e gerenciar dados em um banco de dados relacional gerenciado por um sistema de gerenciamento de banco de dados apropriado. Aprenda mais

Você pode ler sobre SQL de várias fontes .
Este artigo não pretende ensinar SQL a partir do zero.

Então vamos lá.

Usaremos o conhecido esquema de RH no Oracle com suas tabelas ( mais ):

Observo que consideraremos apenas tarefas SELECT. Não há problemas com DML e DDL.

As tarefas

Restringindo e classificando dados

Tabela de funcionários. Obtenha uma lista com informações sobre todos os funcionários

Solução

SELECT * FROM employees

Tabela de funcionários. Obtenha uma lista de todos os funcionários chamados 'David'

Solução

 SELECT * FROM employees WHERE first_name = 'David';

Tabela de funcionários. Obtenha uma lista de todos os funcionários com job_id igual a 'IT_PROG'

Solução

 SELECT * FROM employees WHERE job_id = 'IT_PROG'

Tabela de funcionários. Obtenha uma lista de todos os funcionários do 50º departamento (departamento_id) com salário (salário), mais de 4000

Solução

 SELECT * FROM employees WHERE department_id = 50 AND salary > 4000;

Tabela de funcionários. Obtenha uma lista de todos os funcionários do 20º e do 30º departamento (department_id)

Solução

 SELECT * FROM employees WHERE department_id = 20 OR department_id = 30;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cuja última letra no nome é 'a'

Solução

 SELECT * FROM employees WHERE first_name LIKE '%a';

Tabela de funcionários. Obtenha uma lista de todos os funcionários do 50º e do 80º departamento (departamento_id) que possuem um bônus (o valor na coluna commission_pct não está vazio)

Solução

 SELECT * FROM employees WHERE (department_id = 50 OR department_id = 80) AND commission_pct IS NOT NULL;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo nome contém pelo menos 2 letras 'n'

Solução

 SELECT * FROM employees WHERE first_name LIKE '%n%n%';

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo nome tenha mais de 4 letras

Solução

 SELECT * FROM employees WHERE first_name LIKE '%_____%';

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo salário varia de 8000 a 9000 (inclusive)

Solução

 SELECT * FROM employees WHERE salary BETWEEN 8000 AND 9000;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo nome contém o caractere '%'

Solução

 SELECT * FROM employees WHERE first_name LIKE '%\%%' ESCAPE '\';

Tabela de funcionários. Obtenha uma lista de todos os gerenciadores de ID

Solução

 SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL;

Tabela de funcionários. Obtenha uma lista de funcionários com suas posições no formato: Donald (sh_clerk)

Solução

 SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;

Usando funções de linha única para personalizar a saída

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo nome tenha mais de 10 letras

Solução

 SELECT * FROM employees WHERE LENGTH (first_name) > 10;

Tabela de funcionários. Obtenha uma lista de todos os funcionários que tenham a letra 'b' em seu nome (sem distinção entre maiúsculas e minúsculas)

Solução

 SELECT * FROM employees WHERE INSTR (LOWER (first_name), 'b') > 0;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo nome contém pelo menos 2 letras 'a'

Solução

 SELECT * FROM employees WHERE INSTR (LOWER (first_name),'a',1,2) > 0;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo salário é múltiplo de 1000

Solução

 SELECT * FROM employees WHERE MOD (salary, 1000) = 0;

Tabela de funcionários. Obtenha o primeiro número de três dígitos do número de telefone do funcionário, se o número estiver no formato XXXX.XXX.XXXX

Solução

 SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number FROM employees WHERE phone_number LIKE '___.___.____';

Tabela de departamentos. Obtenha a primeira palavra do nome do departamento para aqueles com mais de uma palavra no nome

Solução

 SELECT department_name, SUBSTR (department_name, 1, INSTR (department_name, ' ')-1) first_word FROM departments WHERE INSTR (department_name, ' ') > 0;

Tabela de funcionários. Obter os nomes dos funcionários sem a primeira e a última letras do nome

Solução

 SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name FROM employees;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cuja última letra no nome é 'm' e um nome longo maior que 5

Solução

 SELECT * FROM employees WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;

Mesa dupla. Obter a próxima data de sexta-feira

Solução

 SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;

Tabela de funcionários. Obtenha uma lista de todos os funcionários que trabalham na empresa há mais de 17 anos

Solução

 SELECT * FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;

Tabela de funcionários. Obtenha uma lista de todos os funcionários cujo último dígito do número de telefone é ímpar e consiste em 3 números separados por um ponto

Solução

 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;

Tabela de funcionários. Obtenha uma lista de todos os funcionários que têm pelo menos três caracteres no valor job_id após o sinal '_', mas esse valor após '_' não é igual a 'CLERK'

Solução

 SELECT * FROM employees WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3 AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';

Tabela de funcionários. Obtenha uma lista de todos os funcionários substituindo todos os '.' No valor PHONE_NUMBER. em '-'

Solução

 SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number FROM employees;

Usando funções de conversão e expressões condicionais

Tabela de funcionários. Obtenha uma lista de todos os funcionários que vieram trabalhar no primeiro dia do mês (qualquer um)

Solução

 SELECT * FROM employees WHERE TO_CHAR (hire_date, 'DD') = '01';

Tabela de funcionários. Obtenha uma lista de todos os funcionários que vieram trabalhar em 2008

Solução

 SELECT * FROM employees WHERE TO_CHAR (hire_date, 'YYYY') = '2008';

Tabela DUAL. Exibir a data de amanhã no formato: Amanhã é segundo dia de janeiro

Solução

 SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info FROM DUAL;

Tabela de funcionários. Obtenha uma lista de todos os funcionários e a data em que todos vieram trabalhar no formato: 21 de junho de 2007

Solução

 SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date FROM employees;

Tabela de funcionários. Obtenha uma lista de funcionários com salários aumentados em 20%. Mostrar salário com cifrão

Solução

 SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary FROM employees;

Tabela de funcionários. Obtenha uma lista de todos os funcionários que vêm trabalhar em fevereiro de 2007.

Solução

 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';

Tabela DUAL. Retire a data atual, + segundo, + minuto, + hora, + dia, + mês, + ano

Solução

 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;

Tabela de funcionários. Obtenha uma lista de todos os funcionários com salários completos (salário + comissão_pct (%)) no formato: US $ 24.000,00

Solução

 SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary FROM employees;

Tabela de funcionários. Obtenha uma lista de todos os funcionários e informações sobre a disponibilidade de bônus salariais (Sim / Não)

Solução

 SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus FROM employees;

Tabela de funcionários. Obter o nível salarial de cada funcionário: Menor que 5000 é considerado Nível baixo, Maior que ou igual a 5000 e menor que 10000 é considerado Nível normal, Maior que ou igual a 10000 é considerado Nível alto

Solução

 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;

Tabela de países. Para cada país, mostre a região em que está localizado: 1-Europa, 2-América, 3-Ásia, 4-África (sem Participação)

Solução

 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;

Relatando dados agregados usando as funções de grupo

Tabela de funcionários. Obtenha um relatório sobre departamento_id com um salário mínimo e máximo, com data de chegada antecipada e tardia ao trabalho e com o número de funcionários. Classificar por número de funcionários (decrescente)

Solução

  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;

Tabela de funcionários. Quantos funcionários cujos nomes começam com a mesma letra? Classifique por quantidade. Mostrar apenas onde a quantidade é maior que 1

Solução

 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;

Tabela de funcionários. Quantos funcionários trabalham no mesmo departamento e recebem o mesmo salário?

Solução

 SELECT department_id, salary, COUNT (*) FROM employees GROUP BY department_id, salary HAVING COUNT (*) > 1;

Tabela de funcionários. Obtenha um relatório de quantos funcionários foram contratados em cada dia da semana. Classificar por quantidade

Solução

 SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_Date, 'Day') ORDER BY 2 DESC;

Tabela de funcionários. Obtenha um relatório de quantos funcionários foram contratados por ano. Classificar por quantidade

Solução

 SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_date, 'YYYY');

Tabela de funcionários. Obter o número de departamentos em que existem funcionários

Solução

 SELECT COUNT (COUNT (*)) department_count FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;

Tabela de funcionários. Obtenha uma lista de departamento_id com mais de 30 funcionários

Solução

  SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (*) > 30;

Tabela de funcionários. Obtenha uma lista de departamento_id e um salário médio arredondado para funcionários em cada departamento.

Solução

  SELECT department_id, ROUND (AVG (salary)) avg_salary FROM employees GROUP BY department_id;

Tabela de países. Obtenha uma lista de region_id a soma de todas as letras de todo country_name nas quais mais de 60

Solução

  SELECT region_id FROM countries GROUP BY region_id HAVING SUM (LENGTH (country_name)) > 60;

Tabela de funcionários. Obtenha uma lista de departamento_id na qual funcionários de vários (> 1) job_id trabalham

Solução

  SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (DISTINCT job_id) > 1;

Tabela de funcionários. Obtenha uma lista de manager_id cujo número de subordinados é superior a 5 e a soma de todos os salários de seus subordinados é superior a 50.000

Solução

  SELECT manager_id FROM employees GROUP BY manager_id HAVING COUNT (*) > 5 AND SUM (salary) > 50000;

Tabela de funcionários. Obtenha uma lista de manager_id para a qual o salário médio de todos os seus subordinados está entre 6.000 e 9.000 que não recebem bônus (commission_pct está vazio)

Solução

  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;

Tabela de funcionários. Obter o salário máximo de todos os funcionários job_id que terminam com a palavra 'CLERK'

Solução

 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';

Tabela de funcionários. Obter o salário máximo entre todos os salários médios do departamento

Solução

  SELECT MAX (AVG (salary)) FROM employees GROUP BY department_id;

Tabela de funcionários. Obtenha o número de funcionários com o mesmo número de letras no nome. Ao mesmo tempo, mostre apenas aqueles com um comprimento de nome superior a 5 e o número de funcionários com esse nome seja superior a 20. Classificar por comprimento de nome

Solução

  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);

Exibindo dados de várias tabelas usando junções

Tabela Empregados, Departamentos, Localizações, Países, Regiões. Obtenha uma lista de regiões e o número de funcionários em cada região

Solução

  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;

Tabela Empregados, Departamentos, Localizações, Países, Regiões. Obtenha informações detalhadas sobre cada funcionário:
Nome, Sobrenome, Departamento, Trabalho, Rua, País, Região

Solução

 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);

Tabela de funcionários. Mostrar todos os gerentes com mais de 6 funcionários

Solução

  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;

Tabela de funcionários. Mostrar todos os funcionários que não se reportam a ninguém

Solução

 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;

Tabela de funcionários, Job_history. A tabela Employee armazena todos os funcionários. A tabela Job_history armazena os funcionários que deixaram a empresa. Obter um relatório sobre todos os funcionários e seu status na empresa (trabalha ou saiu da empresa com a data de partida)
Um exemplo:
first_name | status
Jennifer Saiu da empresa em 31 de dezembro de 2006
Clara Atualmente trabalhando

Solução

 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);

Tabela Empregados, Departamentos, Localizações, Países, Regiões. Obtenha uma lista de funcionários que vivem na Europa (region_name)

Solução

  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';

Tabela de funcionários, departamentos. Mostrar todos os departamentos com mais de 30 funcionários

Solução

 SELECT department_name, COUNT (*) FROM employees e JOIN departments d ON (e.department_id = d.department_id) GROUP BY department_name HAVING COUNT (*) > 30;

Tabela de funcionários, departamentos. Mostrar todos os funcionários que não estão em nenhum departamento

Solução

 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;

Tabela de funcionários, departamentos. Mostrar todos os departamentos sem um único funcionário

Solução

 SELECT department_name FROM employees e RIGHT JOIN departments d ON (e.department_id = d.department_id) WHERE first_name IS NULL;

Tabela de funcionários. Mostrar todos os funcionários que não têm subordinados

Solução

 SELECT man.first_name FROM employees emp RIGHT JOIN employees man ON (emp.manager_id = man.employee_id) WHERE emp.FIRST_NAME IS NULL;

Tabela Empregados, Empregos, Departamentos. Mostrar funcionários no formato: First_name, Job_title, Department_name.
Um exemplo:
First_name | Job_title | Department_name
Donald Envio | Envio de funcionário

Solução

 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);

Tabela de funcionários. Obtenha uma lista de funcionários cujos gerentes conseguiram um emprego em 2005, mas, ao mesmo tempo, esses funcionários conseguiram um emprego antes de 2005

Solução

 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');

Tabela de funcionários. Obtenha uma lista de funcionários cujos gerentes conseguiram um emprego no mês de janeiro de qualquer ano e a duração do cargo_título desses funcionários tenha mais de 15 caracteres

Solução

 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;

Usando subconsultas para resolver consultas

Tabela de funcionários. Obtenha uma lista de funcionários com o nome mais longo.

Solução

 SELECT * FROM employees WHERE LENGTH (first_name) = (SELECT MAX (LENGTH (first_name)) FROM employees);

Tabela de funcionários. Obtenha uma lista de funcionários com um salário maior que o salário médio de todos os funcionários.

Solução

 SELECT * FROM employees WHERE salary > (SELECT AVG (salary) FROM employees);

Tabela Empregados, departamentos, locais. Obtenha uma cidade na qual os funcionários ganhem menos no total.

Solução

 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);

Tabela de funcionários. Obtenha uma lista de funcionários cujo gerente recebe um salário superior a 15.000.

Solução

 SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE salary > 15000)

Tabela de funcionários, departamentos. Mostrar todos os departamentos sem um único funcionário

Solução

 SELECT * FROM departments WHERE department_id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL);

Tabela de funcionários. Mostrar todos os funcionários que não são gerentes

Solução

 SELECT * FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL)

Tabela de funcionários. Mostrar todos os gerentes com mais de 6 funcionários

Solução

 SELECT * FROM employees e WHERE (SELECT COUNT (*) FROM employees WHERE manager_id = e.employee_id) > 6;

Tabela de funcionários, departamentos. Mostrar funcionários que trabalham no departamento de TI

Solução

 SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

Solução

 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;

Tabela de funcionários. Obtenha uma lista de funcionários cujos gerentes conseguiram um emprego em 2005, mas, ao mesmo tempo, esses funcionários conseguiram um emprego antes de 2005

Solução

 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');

Solução

 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;

Por enquanto é tudo.

Espero que as tarefas sejam interessantes e fascinantes.
Se possível, complementarei esta lista de tarefas.
Também terei prazer em quaisquer comentários e sugestões.

PS: Se alguém criar uma tarefa SELECT interessante, escreva nos comentários e adicione à lista.

Obrigada

SQL Quebra-cabeças divertidos

As tarefas

More articles: