Hola Habr!
Fa més de 3 anys que estic ensenyant SQL en diversos centres de formació, i una de les meves observacions és que els alumnes dominen i entenen millor l'SQL si se'ls encarreguen una tasca, i no només parlar de les possibilitats i fonaments teòrics.
En aquest article, compartiré amb vosaltres la meva llista de tasques que dono com a deures als estudiants i sobre les quals realitzem diferents tipus de pluja d'idees, la qual cosa condueix a una comprensió profunda i clara de l'SQL.
SQL (ˈɛsˈkjuˈɛl; eng. llenguatge de consulta estructurat - "llenguatge de consulta estructurat") és un llenguatge de programació declaratiu utilitzat per crear, modificar i gestionar dades en una base de dades relacional gestionada per un sistema de gestió de bases de dades adequat.
Podeu llegir sobre SQL des de diferents .
Aquest article no pretén ensenyar-vos SQL des de zero.
Així que anem.
Farem servir el conegut a Oracle amb les seves taules ():
Tinc en compte que només tindrem en compte les tasques a SELECT. No hi ha tasques en DML i DDL.
tasques
Restringir i ordenar dades
Taula d'empleats. Obteniu una llista amb informació sobre tots els empleats
decisió
SELECT * FROM employees
Taula d'empleats. Obteniu una llista de tots els empleats anomenats "David"
decisió
SELECT *
FROM employees
WHERE first_name = 'David';
Taula d'empleats. Obteniu una llista de tots els empleats amb job_id igual a "IT_PROG"
decisió
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Taula d'empleats. Obteniu una llista de tots els empleats del departament 50 (department_id) amb un sou (sou) superior a 4000
decisió
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Taula d'empleats. Obteniu una llista de tots els empleats del departament 20 i del 30 (department_id)
decisió
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Taula d'empleats. Obteniu una llista de tots els empleats l'última lletra del seu nom és "a"
decisió
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Taula d'empleats. Obteniu una llista de tots els empleats del departament 50 i del 80 (department_id) que tenen una bonificació (el valor de la columna commission_pct no està buit)
decisió
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Taula d'empleats. Obteniu una llista de tots els empleats el nom dels quals conté almenys 2 lletres "n"
decisió
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Taula d'empleats. Obteniu una llista de tots els empleats el nom dels quals tingui més de 4 lletres
decisió
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Taula d'empleats. Obteniu una llista de tots els empleats que tinguin un sou entre 8000 i 9000 (inclosos)
decisió
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Taula d'empleats. Obteniu una llista de tots els empleats el nom dels quals conté el símbol '%'
decisió
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Taula d'empleats. Obteniu una llista de tots els identificadors de gestor
decisió
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Taula d'empleats. Obteniu una llista d'empleats amb les seves posicions en el format: Donald(sh_clerk)
decisió
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Ús de funcions d'una sola fila per personalitzar la sortida
Taula d'empleats. Obteniu una llista de tots els empleats el nom dels quals tingui més de 10 lletres
decisió
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Taula d'empleats. Obteniu una llista de tots els empleats que tenen la lletra 'b' al seu nom (no distingeix entre majúscules i minúscules)
decisió
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Taula d'empleats. Obteniu una llista de tots els empleats el nom dels quals conté almenys 2 lletres "a"
decisió
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Taula d'empleats. Obteniu una llista de tots els empleats el sou dels quals és múltiple de 1000
decisió
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Taula d'empleats. Obteniu el primer número de 3 dígits del número de telèfon de l'empleat si el seu número té el format ХХХ.ХХХ.ХХХХ
decisió
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Taula de departaments. Obteniu la primera paraula del nom del departament per a aquells que tinguin més d'una paraula al nom
decisió
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Taula d'empleats. Obteniu els noms dels empleats sense la primera i l'última lletra del nom
decisió
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Taula d'empleats. Obteniu una llista de tots els empleats l'última lletra del nom és igual a 'm' i la longitud del nom és superior a 5
decisió
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Taula doble. Obteniu la data del proper divendres
decisió
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Taula d'empleats. Obteniu una llista de tots els empleats que porten més de 17 anys a l'empresa
decisió
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Taula d'empleats. Obteniu una llista de tots els empleats l'últim dígit del número de telèfon dels quals és senar i consta de 3 números separats per un punt
decisió
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Taula d'empleats. Obteniu una llista de tots els empleats el valor job_id després del signe '_' té almenys 3 caràcters, però aquest valor després del '_' no és igual a 'CLERK'
decisió
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Taula d'empleats. Obteniu una llista de tots els empleats substituint tots els "." al valor PHONE_NUMBER el '-'
decisió
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Ús de funcions de conversió i expressions condicionals
Taula d'empleats. Obteniu una llista de tots els empleats que van venir a treballar el primer dia del mes (qualsevol)
decisió
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Taula d'empleats. Obteniu una llista de tots els empleats que van venir a treballar l'any 2008
decisió
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Taula DUAL. Mostra la data de demà en el format: Demà és el segon dia de gener
decisió
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Taula d'empleats. Obteniu una llista de tots els empleats i la seva data d'inici en el format: 21 de juny de 2007
decisió
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Taula d'empleats. Obteniu una llista d'empleats amb salaris augmentats un 20%. Mostra el salari amb el signe del dòlar
decisió
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Taula d'empleats. Obteniu una llista de tots els empleats que van venir a treballar el febrer de 2007.
decisió
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';
Taula DUAL. Exporta la data actual, + segon, + minut, + hora, + dia, + mes, + any
decisió
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;
Taula d'empleats. Obteniu una llista de tots els empleats amb sous complets (salari + commission_pct(%)) en el format: 24,000.00 $
decisió
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Taula d'empleats. Obteniu una llista de tots els empleats i informació sobre la disponibilitat de bonificacions salarials (Sí/No)
decisió
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Taula d'empleats. Obtenir el nivell salarial de cada empleat: Menys de 5000 es considera Nivell baix, Major o igual a 5000 i menys de 10000 es considera Nivell normal, Major o igual a 10000 es considera Nivell alt
decisió
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;
Taula de països. Per a cada país, mostra la regió on es troba: 1-Europa, 2-Amèrica, 3-Àsia, 4-Àfrica (sense unir-se)
decisió
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 dades agregades mitjançant les funcions de grup
Taula d'empleats. Obteniu un informe per departament_id amb el salari mínim i màxim, les dates d'arribada anticipades i tardanes i el nombre d'empleats. Ordenar per nombre d'empleats (desc)
decisió
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;
Taula d'empleats. Quants empleats els noms dels quals comencen per la mateixa lletra? Ordena per quantitat. Mostra només aquells on el nombre és superior a 1
decisió
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;
Taula d'empleats. Quants treballadors treballen al mateix departament i reben el mateix sou?
decisió
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Taula d'empleats. Obteniu un informe de quants empleats es van contractar cada dia de la setmana. Ordena per quantitat
decisió
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Taula d'empleats. Obteniu un informe sobre quants empleats es van contractar per any. Ordena per quantitat
decisió
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Taula d'empleats. Obteniu el nombre de departaments que tenen empleats
decisió
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Taula d'empleats. Obteniu la llista de departament_id amb més de 30 empleats
decisió
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Taula d'empleats. Obteniu una llista de departaments_ids i el sou mitjà arrodonit dels empleats de cada departament.
decisió
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Taula de països. Obteniu una llista de region_id suma de totes les lletres de tots els country_names en què més de 60
decisió
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Taula d'empleats. Obteniu una llista de departament_id en què treballen els empleats de diversos (>1) job_id
decisió
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Taula d'empleats. Obteniu una llista de manager_id el nombre de subordinats és superior a 5 i la suma de tots els sous dels seus subordinats és superior a 50000
decisió
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Taula d'empleats. Obteniu una llista de manager_id el sou mitjà dels quals de tots els seus subordinats està entre 6000 i 9000 que no reben bonificacions (commission_pct està buit)
decisió
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;
Taula d'empleats. Obteniu el sou màxim de tots els empleats job_id que acabi amb la paraula "CLERK"
decisió
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';
Taula d'empleats. Obteniu el sou màxim entre tots els salaris mitjans del departament
decisió
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Taula d'empleats. Obteniu el nombre d'empleats amb el mateix nombre de lletres al seu nom. Al mateix temps, mostra només aquells que tinguin un nom superior a 5 i que el nombre d'empleats amb el mateix nom sigui superior a 20. Ordena per longitud del nom
decisió
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);
Visualització de dades de diverses taules mitjançant les unions
Taula Empleats, departaments, ubicacions, països, regions. Obteniu una llista de regions i el nombre d'empleats de cada regió
decisió
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;
Taula Empleats, departaments, ubicacions, països, regions. Obteniu informació detallada sobre cada empleat:
Nom, Cognoms, Departament, Treball, Carrer, País, Regió
decisió
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);
Taula d'empleats. Mostra tots els directius que tenen més de 6 empleats
decisió
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;
Taula d'empleats. Mostra tots els empleats que no denuncien a ningú
decisió
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;
Taula d'empleats, Job_history. La taula d'empleats emmagatzema tots els empleats. La taula Job_history emmagatzema els empleats que van abandonar l'empresa. Aconsegueix un informe de tots els empleats i la seva situació a l'empresa (Ocupats o abandonats amb la data de sortida)
Exemple:
nom_de_nom | estat
jennifer | Va abandonar l'empresa el 31 de desembre de 2006
Clara | Actualment Treballant
decisió
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);
Taula Empleats, departaments, ubicacions, països, regions. Obteniu una llista dels empleats que viuen a Europa (region_name)
decisió
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';
Taula Empleats, Departaments. Mostra tots els departaments amb més de 30 empleats
decisió
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Taula Empleats, Departaments. Mostra tots els empleats que no estan en cap departament
decisió
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;
Taula Empleats, Departaments. Mostra tots els departaments sense empleats
decisió
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Taula d'empleats. Mostra tots els empleats que no tenen subordinats
decisió
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Taula Empleats, Feines, Departaments. Mostra els empleats en el format: Nom_Nom, Títol_Treball, Nom_departament.
Exemple:
nom_de_nom | títol del treball | Nom_departament
Donald | enviament | Empleat d'enviament
decisió
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);
Taula d'empleats. Obteniu una llista d'empleats els directius dels quals van obtenir feina l'any 2005, però al mateix temps, aquests mateixos treballadors van aconseguir una feina abans de 2005
decisió
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');
Taula d'empleats. Obteniu una llista d'empleats els directius dels quals van obtenir feina el mes de gener de qualsevol any i la longitud del job_title d'aquests empleats és de més de 15 caràcters
decisió
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;
Ús de subconsultes per resoldre consultes
Taula d'empleats. Obteniu una llista dels empleats amb el nom més llarg.
decisió
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Taula d'empleats. Obteniu una llista d'empleats amb un sou superior al salari mitjà de tots els empleats.
decisió
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Taula Empleats, Departaments, Ubicacions. Obteniu la ciutat en què els empleats guanyen menys en total.
decisió
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);
Taula d'empleats. Obteniu una llista dels empleats el gerent dels quals rep un sou superior a 15000.
decisió
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Taula Empleats, Departaments. Mostra tots els departaments sense empleats
decisió
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Taula d'empleats. Mostra tots els empleats que no són directius
decisió
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Taula d'empleats. Mostra tots els directius que tenen més de 6 empleats
decisió
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Taula Empleats, Departaments. Mostra els empleats que treballen al departament de TI
decisió
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Taula Empleats, Feines, Departaments. Mostra els empleats en el format: Nom_Nom, Títol_Treball, Nom_departament.
Exemple:
nom_de_nom | títol del treball | Nom_departament
Donald | enviament | Empleat d'enviament
decisió
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;
Taula d'empleats. Obteniu una llista d'empleats els directius dels quals van obtenir feina l'any 2005, però al mateix temps, aquests mateixos treballadors van aconseguir una feina abans de 2005
decisió
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');
Taula d'empleats. Obteniu una llista d'empleats els directius dels quals van obtenir feina el mes de gener de qualsevol any i la longitud del job_title d'aquests empleats és de més de 15 caràcters
decisió
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;
Això és tot per ara.
Espero que les tasques hagin estat interessants i emocionants.
Jo afegiré a aquesta llista tant com sigui possible.
També estaré encantat amb qualsevol comentari i suggeriment.
PD: Si a algú se li presenta una tasca interessant a SELECT, escriu-hi als comentaris, l'afegiré a la llista.
Gràcies.
Font: www.habr.com