Hei Habr!
I mer enn 3 år har jeg undervist i SQL i ulike opplæringssentre, og en av mine observasjoner er at elevene mestrer og forstår SQL bedre hvis de får en oppgave, og ikke bare snakker om mulighetene og teoretiske grunnlaget.
I denne artikkelen vil jeg dele med deg min liste over oppgaver som jeg gir elevene som lekser og som vi gjennomfører ulike typer brainstorming på, som fører til en dyp og klar forståelse av SQL.
SQL (ˈɛsˈkjuˈɛl; eng. structured query language – “structured query language”) er et deklarativt programmeringsspråk som brukes til å lage, endre og administrere data i en relasjonsdatabase administrert av et passende databasestyringssystem.
Du kan lese om SQL fra forskjellige .
Denne artikkelen er ikke ment å lære deg SQL fra bunnen av.
Så la oss gå.
Vi skal bruke det velkjente i Oracle med sine tabeller ():
Jeg legger merke til at vi kun vil vurdere oppgaver på SELECT. Det er ingen oppgaver på DML og DDL.
oppgaver
Begrense og sortere data
Ansattes bord. Få en liste med informasjon om alle ansatte
beslutning
SELECT * FROM employees
Ansattes bord. Få en liste over alle ansatte som heter 'David'
beslutning
SELECT *
FROM employees
WHERE first_name = 'David';
Ansattes bord. Få en liste over alle ansatte med job_id lik 'IT_PROG'
beslutning
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Ansattes bord. Få en liste over alle ansatte fra 50. avdeling (department_id) med lønn (lønn) større enn 4000
beslutning
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Ansattes bord. Få en liste over alle ansatte fra 20. og fra 30. avdeling (department_id)
beslutning
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Ansattes bord. Få en liste over alle ansatte hvis siste bokstav i navnet er 'a'
beslutning
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Ansattes bord. Få en liste over alle ansatte fra 50. og fra 80. avdeling (department_id) som har en bonus (verdien i commission_pct-kolonnen er ikke tom)
beslutning
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Ansattes bord. Få en liste over alle ansatte hvis navn inneholder minst 2 bokstaver 'n'
beslutning
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Ansattes bord. Få en liste over alle ansatte hvis navn er lengre enn 4 bokstaver
beslutning
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Ansattes bord. Få en liste over alle ansatte med lønn mellom 8000 og 9000 (inklusive)
beslutning
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Ansattes bord. Få en liste over alle ansatte hvis navn inneholder symbolet '%'
beslutning
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Ansattes bord. Få en liste over alle manager-ID-er
beslutning
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Ansattes bord. Få en liste over ansatte med sine stillinger i formatet: Donald(sh_clerk)
beslutning
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Bruke enkeltradsfunksjoner for å tilpasse utdata
Ansattes bord. Få en liste over alle ansatte hvis navn er lengre enn 10 bokstaver
beslutning
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Ansattes bord. Få en liste over alle ansatte som har bokstaven 'b' i navnet sitt (uavhengig av store og små bokstaver)
beslutning
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Ansattes bord. Få en liste over alle ansatte hvis navn inneholder minst 2 bokstaver 'a'
beslutning
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Ansattes bord. Få en liste over alle ansatte hvis lønn er et multiplum av 1000
beslutning
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Ansattes bord. Få det første 3-sifrede nummeret til den ansattes telefonnummer hvis nummeret hans er i formatet ХХХ.ХХХ.ХХХХ
beslutning
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Avdelingstabell. Få det første ordet fra avdelingsnavnet for de med mer enn ett ord i navnet
beslutning
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Ansattes bord. Få medarbeidernavn uten første og siste bokstav i navnet
beslutning
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Ansattes bord. Få en liste over alle ansatte hvis siste bokstav i navnet er lik 'm' og lengden på navnet er større enn 5
beslutning
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Dobbelt bord. Få datoen neste fredag
beslutning
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Ansattes bord. Få en liste over alle ansatte som har vært i selskapet i over 17 år
beslutning
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Ansattes bord. Få en liste over alle ansatte hvis siste siffer i telefonnummeret er oddetall og består av 3 tall atskilt med en prikk
beslutning
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Ansattes bord. Få en liste over alle ansatte hvis job_id-verdi etter '_'-tegnet har minst 3 tegn, men denne verdien etter '_' er ikke lik 'CLERK'
beslutning
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Ansattes bord. Få en liste over alle ansatte ved å erstatte alle '.' i PHONE_NUMBER-verdien på '-'
beslutning
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Bruke konverteringsfunksjoner og betingede uttrykk
Ansattes bord. Få en liste over alle ansatte som kom på jobb den første dagen i måneden (alle)
beslutning
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Ansattes bord. Få en liste over alle ansatte som kom på jobb i 2008
beslutning
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
DOBBELT bord. Vis morgendagens dato i formatet: I morgen er det andre dag i januar
beslutning
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Ansattes bord. Få en liste over alle ansatte og deres startdato i formatet: 21. juni 2007
beslutning
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Ansattes bord. Få en liste over ansatte med økt lønn med 20 %. Vis lønn med dollartegn
beslutning
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Ansattes bord. Få en liste over alle ansatte som kom på jobb i februar 2007.
beslutning
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';
DOBBELT bord. Eksporter gjeldende dato, + sekund, + minutt, + time, + dag, + måned, + år
beslutning
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;
Ansattes bord. Få en liste over alle ansatte med full lønn (lønn + commission_pct(%)) i formatet: $24,000.00 XNUMX
beslutning
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Ansattes bord. Få en liste over alle ansatte og informasjon om tilgjengeligheten av lønnsbonuser (Ja/Nei)
beslutning
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Ansattes bord. Få lønnsnivået til hver ansatt: Mindre enn 5000 regnes som lavt nivå, Større enn eller lik 5000 og mindre enn 10000 10000 regnes som normalt nivå, Større enn og lik XNUMX regnes som høyt nivå
beslutning
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;
Tabell over land. For hvert land, vis regionen der det ligger: 1-Europa, 2-Amerika, 3-Asia, 4-Afrika (uten medlemskap)
beslutning
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;
Rapportering av aggregerte data ved hjelp av gruppefunksjonene
Ansattes bord. Få rapport etter avdeling_id med minimums- og maksimumslønn, tidlig og sen ankomstdato og antall ansatte. Sorter etter antall ansatte (desc)
beslutning
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;
Ansattes bord. Hvor mange ansatte hvis navn begynner med samme bokstav? Sorter etter mengde. Vis bare de der tallet er større enn 1
beslutning
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;
Ansattes bord. Hvor mange ansatte jobber i samme avdeling og får samme lønn?
beslutning
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Ansattes bord. Få en rapport over hvor mange ansatte som ble ansatt hver ukedag. Sorter etter mengde
beslutning
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Ansattes bord. Få en rapport om hvor mange ansatte som ble ansatt etter år. Sorter etter mengde
beslutning
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Ansattes bord. Få antall avdelinger som har ansatte
beslutning
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Ansattes bord. Få liste over avdelings-id med mer enn 30 ansatte
beslutning
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Ansattes bord. Få en liste over avdelings-ID og den avrundede gjennomsnittslønnen til ansatte i hver avdeling.
beslutning
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Tabell over land. Få en liste over region_id summen av alle bokstavene i alle country_names der mer enn 60
beslutning
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Ansattes bord. Få en liste over avdelings-id der ansatte i flere (>1) jobb-id jobber
beslutning
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Ansattes bord. Få en liste over manager_id hvis antall underordnede er større enn 5 og summen av alle lønnene til hans underordnede er større enn 50000
beslutning
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Ansattes bord. Få en liste over manager_id hvis gjennomsnittslønn for alle hans underordnede er mellom 6000 og 9000 som ikke mottar bonuser (commission_pct er tom)
beslutning
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;
Ansattes bord. Få maksimal lønn fra alle ansatte job_id som slutter med ordet 'CLERK'
beslutning
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';
Ansattes bord. Få maksimal lønn blant alle gjennomsnittslønningene for avdelingen
beslutning
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Ansattes bord. Få antall ansatte med samme antall bokstaver i navnet. Vis samtidig kun de hvis navn er lengre enn 5 og antall ansatte med samme navn er mer enn 20. Sorter etter navnelengde
beslutning
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);
Vise data fra flere tabeller ved hjelp av sammenføyninger
Tabell Ansatte, avdelinger, lokasjoner, land, regioner. Få en liste over regioner og antall ansatte i hver region
beslutning
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;
Tabell Ansatte, avdelinger, lokasjoner, land, regioner. Få detaljert informasjon om hver ansatt:
Fornavn, Etternavn, Avdeling, Jobb, Gate, Land, Region
beslutning
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);
Ansattes bord. Vis alle ledere som har mer enn 6 ansatte
beslutning
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;
Ansattes bord. Vis alle ansatte som ikke rapporterer til noen
beslutning
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;
Ansattetabell, Job_historie. Medarbeidertabellen lagrer alle ansatte. Job_history-tabellen lagrer ansatte som forlot selskapet. Få rapport om alle ansatte og deres status i selskapet (Ansatt eller sluttet i selskapet med avreisedato)
Eksempel:
fornavn | status
jennifer | Forlot selskapet 31. desember 2006
Clara | Arbeider for tiden
beslutning
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);
Tabell Ansatte, avdelinger, lokasjoner, land, regioner. Få en liste over ansatte som bor i Europa (region_name)
beslutning
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';
Tabellansatte, avdelinger. Vis alle avdelinger med mer enn 30 ansatte
beslutning
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Tabellansatte, avdelinger. Vis alle ansatte som ikke er i noen avdeling
beslutning
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;
Tabellansatte, avdelinger. Vis alle avdelinger uten ansatte
beslutning
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Ansattes bord. Vis alle ansatte som ikke har noen underordnede
beslutning
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Tabell Ansatte, jobber, avdelinger. Vis ansatte i formatet: Fornavn, Stillingstittel, Avdelingsnavn.
Eksempel:
fornavn | stillingstittel | Avdelingsnavn
Donald | frakt | Ekspeditør Shipping
beslutning
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);
Ansattes bord. Få en liste over ansatte hvis ledere fikk jobb i 2005, men samtidig fikk disse arbeiderne selv jobb før 2005
beslutning
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');
Ansattes bord. Få en liste over ansatte hvis ledere fikk jobb i januar måned et hvilket som helst år, og lengden på stillingstittelen til disse ansatte er mer enn 15 tegn
beslutning
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;
Bruke underspørringer for å løse spørsmål
Ansattes bord. Få en liste over ansatte med det lengste navnet.
beslutning
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Ansattes bord. Få en liste over ansatte med høyere lønn enn gjennomsnittslønnen til alle ansatte.
beslutning
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Tabellansatte, avdelinger, lokasjoner. Få byen der ansatte tjener minst totalt.
beslutning
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);
Ansattes bord. Få en liste over ansatte hvis leder mottar en lønn på mer enn 15000 XNUMX.
beslutning
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Tabellansatte, avdelinger. Vis alle avdelinger uten ansatte
beslutning
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Ansattes bord. Vis alle ansatte som ikke er ledere
beslutning
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Ansattes bord. Vis alle ledere som har mer enn 6 ansatte
beslutning
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Tabellansatte, avdelinger. Vis ansatte som jobber i IT-avdelingen
beslutning
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tabell Ansatte, jobber, avdelinger. Vis ansatte i formatet: Fornavn, Stillingstittel, Avdelingsnavn.
Eksempel:
fornavn | stillingstittel | Avdelingsnavn
Donald | frakt | Ekspeditør Shipping
beslutning
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;
Ansattes bord. Få en liste over ansatte hvis ledere fikk jobb i 2005, men samtidig fikk disse arbeiderne selv jobb før 2005
beslutning
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');
Ansattes bord. Få en liste over ansatte hvis ledere fikk jobb i januar måned et hvilket som helst år, og lengden på stillingstittelen til disse ansatte er mer enn 15 tegn
beslutning
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;
Det er alt for nå.
Jeg håper oppgavene var interessante og spennende.
Jeg vil legge til denne listen så mye som mulig.
Jeg vil også være glad for eventuelle kommentarer og forslag.
PS: Hvis noen kommer på en interessant oppgave på SELECT, skriv i kommentarfeltet, jeg legger den til i listen.
Takk.
Kilde: www.habr.com