Ola Habr!
Levo máis de 3 anos impartindo clases de SQL en diversos centros de formación, e unha das miñas observacións é que os alumnos dominan e comprenden mellor SQL se se lles encarga unha tarefa, e non só falar das posibilidades e dos fundamentos teóricos.
Neste artigo, compartirei con vós a miña lista de tarefas que lles dou aos estudantes como deberes e sobre as que realizamos varios tipos de chuvias de ideas, o que leva a unha comprensión profunda e clara de SQL.
SQL (ˈɛsˈkjuˈɛl; eng. linguaxe de consulta estruturada) é unha linguaxe de programación declarativa utilizada para crear, modificar e xestionar datos nunha base de datos relacional xestionada por un sistema de xestión de bases de datos axeitado.
Podes ler sobre SQL desde diferentes .
Este artigo non pretende ensinarche SQL desde cero.
Entón, imos.
Usaremos o coñecido en Oracle coas súas táboas ():
Teño en conta que só consideraremos tarefas en SELECT. Non hai tarefas en DML e DDL.
tarefas
Restricción e clasificación de datos
Mesa de empregados. Obtén unha lista con información sobre todos os empregados
decisión
SELECT * FROM employees
Mesa de empregados. Obter unha lista de todos os empregados chamados 'David'
decisión
SELECT *
FROM employees
WHERE first_name = 'David';
Mesa de empregados. Obter unha lista de todos os empregados con job_id igual a "IT_PROG"
decisión
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Mesa de empregados. Obter unha lista de todos os empregados do departamento 50 (department_id) cun salario (salario) superior a 4000
decisión
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Mesa de empregados. Obter unha lista de todos os empregados do departamento 20 e 30 (department_id)
decisión
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Mesa de empregados. Obter unha lista de todos os empregados cuxa última letra do seu nome é "a"
decisión
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Mesa de empregados. Obtén unha lista de todos os empregados do departamento 50 e do 80 (department_id) que teñen unha bonificación (o valor da columna commission_pct non está baleiro)
decisión
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Mesa de empregados. Obter unha lista de todos os empregados cuxo nome conteña polo menos 2 letras "n"
decisión
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Mesa de empregados. Obtén unha lista de todos os empregados cuxo nome supere 4 letras
decisión
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Mesa de empregados. Obter unha lista de todos os empregados cuxo salario estea entre 8000 e 9000 (inclusive)
decisión
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Mesa de empregados. Obter unha lista de todos os empregados cuxo nome contén o símbolo '%'
decisión
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Mesa de empregados. Obtén unha lista de todos os ID de xestor
decisión
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Mesa de empregados. Obter unha lista de empregados cos seus postos no formato: Donald(sh_clerk)
decisión
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Usando funcións dunha soa fila para personalizar a saída
Mesa de empregados. Obtén unha lista de todos os empregados cuxo nome supere 10 letras
decisión
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Mesa de empregados. Obter unha lista de todos os empregados que teñen a letra "b" no seu nome (sen distingo entre maiúsculas e minúsculas)
decisión
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Mesa de empregados. Obter unha lista de todos os empregados cuxo nome conteña polo menos 2 letras "a"
decisión
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Mesa de empregados. Obter unha lista de todos os empregados cuxo salario é múltiplo de 1000
decisión
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Mesa de empregados. Obter o primeiro número de 3 díxitos do número de teléfono do empregado se o seu número está no formato ХХХ.ХХХ.ХХХХ
decisión
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Táboa de departamentos. Obter a primeira palabra do nome do departamento para aqueles con máis dunha palabra no nome
decisión
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Mesa de empregados. Obtén os nomes dos empregados sen a primeira e a última letra do nome
decisión
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Mesa de empregados. Obter unha lista de todos os empregados cuxa última letra do nome sexa igual a "m" e a lonxitude do nome sexa superior a 5
decisión
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Mesa dual. Consulta a data do vindeiro venres
decisión
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Mesa de empregados. Obtén unha lista de todos os empregados que levan máis de 17 anos na empresa
decisión
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Mesa de empregados. Obter unha lista de todos os empregados cuxo último díxito do número de teléfono é impar e consta de 3 números separados por un punto
decisión
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Mesa de empregados. Obter unha lista de todos os empregados cuxo valor job_id despois do signo '_' teña polo menos 3 caracteres, pero este valor despois do '_' non é igual a 'CLERK'
decisió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 empregados. Obtén unha lista de todos os empregados substituíndo todos "." no valor PHONE_NUMBER en '-'
decisión
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Usando funcións de conversión e expresións condicionais
Mesa de empregados. Obter unha lista de todos os empregados que viñeron a traballar o primeiro día do mes (calquera)
decisión
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Mesa de empregados. Obtén unha lista de todos os empregados que chegaron a traballar en 2008
decisión
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Mesa DUAL. Mostra a data de mañá no formato: Mañá é o segundo día de xaneiro
decisión
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Mesa de empregados. Obtén unha lista de todos os empregados e a súa data de inicio no formato: 21 de xuño de 2007
decisión
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Mesa de empregados. Obter unha lista de empregados con salarios incrementados nun 20%. Mostrar o salario con signo de dólar
decisión
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Mesa de empregados. Obtén unha lista de todos os empregados que chegaron a traballar en febreiro de 2007.
decisió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. Exportar a data actual, + segundo, + minuto, + hora, + día, + mes, + ano
decisió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 empregados. Obter unha lista de todos os empregados con soldos completos (salario + commission_pct(%)) no formato: $24,000.00
decisión
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Mesa de empregados. Obter unha lista de todos os empregados e información sobre a dispoñibilidade de bonificacións salariais (Si/Non)
decisión
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Mesa de empregados. Obter o nivel salarial de cada empregado: Menos de 5000 considérase Nivel baixo, Maior ou igual a 5000 e inferior a 10000 considérase Nivel normal, Maior ou igual a 10000 considérase Nivel alto
decisió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;
Táboa de países. Para cada país, mostra a rexión na que se atopa: 1-Europa, 2-América, 3-Asia, 4-África (sen Unirse)
decisió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 mediante as funcións de grupo
Mesa de empregados. Obtén un informe por departamento_id con salario mínimo e máximo, datas de chegada anticipada e tardía e número de empregados. Ordenar por número de empregados (desc)
decisió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 empregados. Cantos empregados cuxos nomes comezan pola mesma letra? Ordenar por cantidade. Mostra só aqueles onde o número é maior que 1
decisió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 empregados. Cantos empregados traballan no mesmo departamento e reciben o mesmo salario?
decisión
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Mesa de empregados. Obtén un informe de cantos empregados foron contratados en cada día da semana. Ordenar por cantidade
decisión
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Mesa de empregados. Obtén un informe sobre cantos empregados foron contratados por ano. Ordenar por cantidade
decisión
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Mesa de empregados. Obtén o número de departamentos que teñen empregados
decisión
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Mesa de empregados. Obtén a lista de Department_id con máis de 30 empregados
decisión
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Mesa de empregados. Obtén unha lista de Department_ids e o salario medio redondeado dos empregados de cada departamento.
decisión
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Táboa de países. Obtén unha lista de region_id suma de todas as letras de todos os nomes_país nas que hai máis de 60
decisión
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Mesa de empregados. Obter unha lista de department_id na que traballan os empregados de varios (>1) job_id
decisión
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Mesa de empregados. Obter unha lista de manager_id cuxo número de subordinados sexa superior a 5 e a suma de todos os salarios dos seus subordinados sexa superior a 50000
decisión
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Mesa de empregados. Obtén unha lista de manager_id cuxo salario medio de todos os seus subordinados está entre 6000 e 9000 que non reciben bonificacións (commission_pct está baleiro)
decisió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 empregados. Obtén o salario máximo de todos os empregados job_id que remata coa palabra "CLERK"
decisió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 empregados. Obter o salario máximo entre todos os salarios medios do departamento
decisión
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Mesa de empregados. Obter o número de empregados co mesmo número de letras no seu nome. Ao mesmo tempo, amosa só aqueles cuxo nome sexa superior a 5 e o número de empregados co mesmo nome sexa superior a 20. Ordenar pola lonxitude do nome
decisió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);
Mostrar datos de varias táboas mediante unións
Táboa Empregados, Departamentos, Localizacións, Países, Rexións. Obtén unha lista de rexións e o número de empregados en cada rexión
decisió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;
Táboa Empregados, Departamentos, Localizacións, Países, Rexións. Obtén información detallada sobre cada empregado:
Nome, Apelidos, Departamento, Traballo, Rúa, País, Rexión
decisió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 empregados. Mostrar todos os xestores que teñan máis de 6 empregados
decisió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 empregados. Mostra a todos os empregados que non reportan a ninguén
decisió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;
Táboa de empregados, Job_history. A táboa Empregado almacena todos os empregados. A táboa Job_history almacena os empregados que abandonaron a empresa. Obter un informe de todos os empregados e a súa situación na empresa (Traballador ou deixou a empresa coa data de saída)
Exemplo:
nome_| estado
jennifer | Saíu da empresa o 31 de decembro de 2006
Clara | Actualmente traballando
decisió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);
Táboa Empregados, Departamentos, Localizacións, Países, Rexións. Obter unha lista de empregados que viven en Europa (nome_rexión)
decisió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 Empregados, Departamentos. Mostra todos os departamentos con máis de 30 empregados
decisió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 Empregados, Departamentos. Mostrar todos os empregados que non estean en ningún departamento
decisió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 Empregados, Departamentos. Mostra todos os departamentos sen empregados
decisió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 empregados. Mostrar todos os empregados que non teñen subordinados
decisió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;
Táboa Empregados, Postos de Traballo, Departamentos. Mostrar aos empregados no formato: Nome, Título do traballo, Nome do departamento.
Exemplo:
nome_| título do traballo | Nome_departamento
Donald | envío | Empleado de envío
decisió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 empregados. Obter unha lista de empregados cuxos xestores conseguiron un emprego en 2005, pero ao mesmo tempo, estes traballadores conseguiron un emprego antes de 2005
decisió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 empregados. Obtén unha lista de empregados cuxos xestores conseguiron un emprego no mes de xaneiro de calquera ano e a lonxitude do job_title destes empregados supera os 15 caracteres
decisió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 empregados. Obtén unha lista de empregados co nome máis longo.
decisión
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Mesa de empregados. Obter unha lista de empregados cun salario superior ao salario medio de todos os empregados.
decisión
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Táboa Empregados, Departamentos, Localizacións. Obtén a cidade na que os empregados gañan menos en total.
decisió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 empregados. Obter unha lista de empregados cuxo xestor perciba un salario superior a 15000.
decisión
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Mesa Empregados, Departamentos. Mostra todos os departamentos sen empregados
decisión
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Mesa de empregados. Mostrar todos os empregados que non son xestores
decisión
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Mesa de empregados. Mostrar todos os xestores que teñan máis de 6 empregados
decisión
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Mesa Empregados, Departamentos. Mostrar aos empregados que traballan no departamento de TI
decisión
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Táboa Empregados, Postos de Traballo, Departamentos. Mostrar aos empregados no formato: Nome, Título do traballo, Nome do departamento.
Exemplo:
nome_| título do traballo | Nome_departamento
Donald | envío | Empleado de envío
decisió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 empregados. Obter unha lista de empregados cuxos xestores conseguiron un emprego en 2005, pero ao mesmo tempo, estes traballadores conseguiron un emprego antes de 2005
decisió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 empregados. Obtén unha lista de empregados cuxos xestores conseguiron un emprego no mes de xaneiro de calquera ano e a lonxitude do job_title destes empregados supera os 15 caracteres
decisió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;
Iso é todo por agora.
Espero que as tarefas fosen interesantes e emocionantes.
Vou engadir a esta lista na medida do posible.
Tamén estarei encantado de recibir calquera comentario e suxestión.
PD: Se a alguén se lle ocorre unha tarefa interesante en SELECT, escribe nos comentarios, engadireina á lista.
Grazas.
Fonte: www.habr.com