{"id":36671,"date":"2019-10-31T22:13:01","date_gmt":"2019-10-31T19:13:01","guid":{"rendered":"https:\/\/prohoster.info\/blog\/sql-zanimatelnye-zadachki\/"},"modified":"2019-10-31T22:13:01","modified_gmt":"2019-10-31T19:13:01","slug":"sql-zanimatelnye-zadachki","status":"publish","type":"post","link":"https:\/\/prohoster.info\/no\/blog\/news\/sql-zanimatelnye-zadachki","title":{"rendered":"SQL. Interessante oppg\u00e5ver","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hallo, Habr!<\/p>\n

Eg har undervist i SQL i over 3 \u00e5r i ulike treningssenter, og ei av mine observasjonar er at studentar meislar og forst\u00e5r SQL betre n\u00e5r dei blir sett oppg\u00e5ver, snarare enn berre \u00e5 bli fortalt om moglegheiter og teoretiske grunnlag.<\/p>\n

I denne artikkelen vil eg dele med deg mi liste over oppg\u00e5ver som eg gir studentar som heimlek og som vi har diverse brainstorming p\u00e5, noko som f\u00f8rer til djup og klar forst\u00e5ing av SQL.<\/p>\n

\"SQL.
\n
\nSQL (\u02c8\u025bs\u02c8kju\u02c8\u025bl; eng. structured query language \u2014 \u00abspr\u00e5k for strukturerte sp\u00f8rringar\u00bb) \u2014 er eit deklarativt programmeringsspr\u00e5k som blir brukt for \u00e5 lage, endre og administrere data i relasjonsdatabasar, styrt av den aktuelle databasedefinisjonen. Les meir\u2026 <\/a><\/noindex><\/p>\n

Du kan lese om SQL fr\u00e5 fleire kjelder<\/a><\/noindex>.
\nDenne artikkelen har ikkje som m\u00e5l \u00e5 l\u00e6re deg SQL fr\u00e5 grunnen av.
\n<\/a><\/noindex>
\nS\u00e5 la oss starte.<\/p>\n

Vi skal bruke den velkjente HR-skjemaet<\/a><\/noindex> i Oracle med dens tabeller (Les mer<\/a><\/noindex>):<\/p>\n

\"SQL.
\nEg vil merke at vi berre vil sj\u00e5 p\u00e5 oppg\u00e5ver knytt til SELECT. Det finnes ingen DML og DDL oppg\u00e5ver.<\/p>\n

Oppgaver<\/h2>\n

\nAvgrense og sortere data<\/b><\/p>\n

Tabell Employees. Hent ei liste med informasjon om alle tilsette
\nSolution<\/b><\/p>\n

SELECT * FROM employees\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte med navnet 'David'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name = 'David';\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte med job_id lik 'IT_PROG'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE job_id = 'IT_PROG'\n<\/code><\/pre>\n
Tabell Employees. Hent ei liste over alle tilsette fr\u00e5 avdeling 50 (department_id) med l\u00f8nn (salary) over 4000
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = 50 AND salary > 4000;\n<\/code><\/pre>\n
Tabell Employees. Hent ei liste over alle tilsette fr\u00e5 avdeling 20 og avdeling 30 (department_id)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = 20 OR department_id = 30;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der den siste bokstaven i navnet er 'a'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%a';\n<\/code><\/pre>\n
Tabell Employees. Hent ei liste over alle tilsette fr\u00e5 avdeling 50 og avdeling 80 (department_id) som har ein bonus (verdi i kolonnen commission_pct er ikkje tom)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE     (department_id = 50 OR department_id = 80)\n       AND commission_pct IS NOT NULL;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der navnet inneholder minst 2 bokstaver 'n'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%n%n%';\n<\/code><\/pre>\n
Tabell Employees. Hent ei liste over alle tilsette der lengda p\u00e5 namnet er meir enn 4 bokstavar
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%_____%';\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte som har en l\u00f8nn mellom 8000 og 9000 (inkludert)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE salary BETWEEN 8000 AND 9000;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der navnet inneholder symbolet '%'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%%%' ESCAPE '';\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ID-er til ledere
\nSolution<\/b><\/p>\n
SELECT DISTINCT manager_id\n  FROM employees\n WHERE manager_id IS NOT NULL;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over ansatte med deres posisjoner i formatet: Donald(sh_clerk)
\nSolution<\/b><\/p>\n
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;\n<\/code><\/pre>\n
\nBruke enkeltlinjefunksjoner for \u00e5 tilpasse utdata<\/b><\/p>\n
Tabell over ansatte. Hent en liste over alle ansatte hvor lengden p\u00e5 navnet er mer enn 10 bokstaver
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE LENGTH (first_name) > 10;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der navnet har bokstaven 'b' (uten hensyn til store og sm\u00e5 bokstaver)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name), 'b') > 0;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der navnet inneholder minst 2 bokstaver 'a'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name),'a',1,2) > 0;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte hvis l\u00f8nn er delelig med 1000
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MOD (salary, 1000) = 0;\n<\/code><\/pre>\n
Tabell over ansatte. Hent de f\u00f8rste 3 sifferne fra ansattes telefonnummer hvis nummeret er i formatet XXX.XXX.XXXX
\nSolution<\/b><\/p>\n
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number\n  FROM employees\n WHERE phone_number LIKE '___.___.____';\n<\/code><\/pre>\n
Tabell over avdelinger. Hent det f\u00f8rste ordet fra avdelingsnavnet for de som har mer enn ett ord i navnet
\nSolution<\/b><\/p>\n
SELECT department_name,\n       SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)\n           first_word\n  FROM departments\n WHERE INSTR (department_name, ' ') > 0;\n<\/code><\/pre>\n
Tabell over ansatte. Hent ansattes navn uten f\u00f8rste og siste bokstav i navnet
\nSolution<\/b><\/p>\n
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name\n  FROM employees;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der den siste bokstaven i navnet er 'm' og navnet er lengre enn 5 tegn
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;\n<\/code><\/pre>\n
Tabell over Dual. Hent datoen for neste fredag
\nSolution<\/b><\/p>\n
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte som har v\u00e6rt i selskapet i mer enn 17 \u00e5r
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MONTHS_BETWEEN (SYSDATE, hire_date) \/ 12 > 17;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte hvor den siste sifferen i telefonnummeret er oddetall og best\u00e5r av 3 siffer delt med punktum
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MOD (SUBSTR (phone_number, -1), 2) != 0\n       AND INSTR (phone_number,'.',1,3) = 0;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte der verdien job_id etter symbolet '_' har minst 3 tegn, men denne verdien etter '_' er ikke lik 'CLERK'
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE     LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3\n       AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over alle ansatte ved \u00e5 erstatte i verdien PHONE_NUMBER alle '.' med '-'
\nSolution<\/b><\/p>\n
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number\n  FROM employees;\n<\/code><\/pre>\n
\nBruke konverteringsfunksjoner og betingede uttrykk<\/b><\/p>\n
Ansatt-tabellen. Hente listen over alle ansatte som begynte i f\u00f8rste dag i en m\u00e5ned (hvilken som helst)
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE TO_CHAR (hire_date, 'DD') = '01';\n<\/code><\/pre>\n
Ansatt-tabellen. Hente listen over alle ansatte som begynte i 2008
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE TO_CHAR (hire_date, 'YYYY') = '2008';\n<\/code><\/pre>\n
DUAL-tabellen. Vise i morgen dato i format: Tomorrow is Second day of January
\nSolution<\/b><\/p>\n
SELECT TO_CHAR (SYSDATE, 'fm\"\"Tomorrow is \"\"Ddspth \"\"day of\"\" Month')     info\n  FROM DUAL;\n<\/code><\/pre>\n
Ansatt-tabellen. Hente listen over alle ansatte og ansettelsesdatoen for hver i formatet: 21st of June, 2007
\nSolution<\/b><\/p>\n
SELECT first_name, TO_CHAR (hire_date, 'fmddth \"\"of\"\" Month, YYYY') hire_date\n  FROM employees;\n<\/code><\/pre>\n
Ansatt-tabellen. Hente listen over ansatte med 20 % \u00f8kning i l\u00f8nn. Vise l\u00f8nnen med dollartegn
\nSolution<\/b><\/p>\n
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary\n  FROM employees;\n<\/code><\/pre>\n
Ansatt-tabellen. Hente listen over alle ansatte som begynte i februar 2007.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY')\n                     AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY'));\n\nSELECT *\n  FROM employees\n WHERE to_char(hire_date,'MM.YYYY') = '02.2007'; \n<\/code><\/pre>\n
DUAL-tabellen. Vise den aktuelle datoen, + sekund, + minutt, + time, + dag, + m\u00e5ned, + \u00e5r
\nSolution<\/b><\/p>\n
SELECT SYSDATE                          now,\n       SYSDATE + 1 \/ (24 * 60 * 60)     plus_second,\n       SYSDATE + 1 \/ (24 * 60)          plus_minute,\n       SYSDATE + 1 \/ 24                 plus_hour,\n       SYSDATE + 1                      plus_day,\n       ADD_MONTHS (SYSDATE, 1)          plus_month,\n       ADD_MONTHS (SYSDATE, 12)         plus_year\n  FROM DUAL;\n<\/code><\/pre>\n
Ansatt-tabellen. Hente listen over alle ansatte med full l\u00f8nn (salary + commission_pct(%)) i formatet: $24,000.00
\nSolution<\/b><\/p>\n
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary\n  FROM employees;\n<\/code><\/pre>\n
Ansatt-tabellen. Hente listen over alle ansatte og informasjon om bonusene til l\u00f8nnen (Ja\/Nei)
\nSolution<\/b><\/p>\n
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus\n  FROM employees;\n<\/code><\/pre>\n
Ansatt-tabellen. Hente l\u00f8nnsniv\u00e5et for hver ansatt: Mindre enn 5000 regnes som Lavt niv\u00e5, St\u00f8rre eller lik 5000 og mindre enn 10000 regnes som Normalt niv\u00e5, St\u00f8rre eller lik 10000 regnes som H\u00f8yt niv\u00e5
\nSolution<\/b><\/p>\n
SELECT first_name,\n       salary,\n       CASE\n           WHEN salary = 5000 AND salary < 10000 THEN 'Normal'\n           ELSE 'H\u00f8g'\n       END salary_level\n  FROM employees;\n<\/code><\/pre>\n
Tabell Countries. Vis regionen for hvert land: 1-Europa, 2-Amerika, 3-Asia, 4-Afrika (uten Join)
\nSolution<\/b><\/p>\n
SELECT country_name country,\n       DECODE (region_id,\n               1, 'Europa',\n               2, 'Amerika',\n               3, 'Asia',\n               4, 'Afrika',\n               'Ukjent')\n           region\n  FROM countries;\n\nSELECT country_name\n           country,\n       CASE region_id\n           WHEN 1 THEN 'Europa'\n           WHEN 2 THEN 'Amerika'\n           WHEN 3 THEN 'Asia'\n           WHEN 4 THEN 'Afrika'\n           ELSE 'Ukjent'\n       END\n           region\n  FROM countries;\n<\/code><\/pre>\n
\nRapportere aggregert data ved hjelp av gruppefunksjoner<\/b><\/p>\n
Tabell Employees. F\u00e5 en rapport for department_id med minimum og maksimum l\u00f8nn, tidlig og sent ansettelsesdato og antall ansatte. Sorter etter antall ansatte (synkende rekkef\u00f8lge)
\nSolution<\/b><\/p>\n
  SELECT department_id,\n         MIN (salary) min_salary,\n         MAX (salary) max_salary,\n         MIN (hire_date) min_hire_date,\n         MAX (hire_date) max_hire_Date,\n         COUNT (*) count\n    FROM employees\nGROUP BY department_id\norder by count(*) desc;\n<\/code><\/pre>\n
Tabell Employees. Hvor mange ansatte har navn som begynner med samme bokstav? Sorter etter antall. Vis kun de med antall st\u00f8rre enn 1
\nSolution<\/b><\/p>\n
SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*)\n    FROM employees\nGROUP BY SUBSTR (first_name, 1, 1)\n  HAVING COUNT (*) > 1\nORDER BY 2 DESC;\n<\/code><\/pre>\n
Tabell Employees. Hvor mange ansatte arbeider i samme avdeling og har samme l\u00f8nn?
\nSolution<\/b><\/p>\n
SELECT department_id, salary, COUNT (*)\n    FROM employees\nGROUP BY department_id, salary\n  HAVING COUNT (*) > 1;\n<\/code><\/pre>\n
Tabell Employees. F\u00e5 en rapport om hvor mange ansatte som ble ansatt hver ukedag. Sorter etter antall
\nSolution<\/b><\/p>\n
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)\n    FROM employees\nGROUP BY TO_CHAR (hire_Date, 'Day')\nORDER BY 2 DESC;\n<\/code><\/pre>\n
Tabell Employees. F\u00e5 en rapport om hvor mange ansatte som ble ansatt hvert \u00e5r. Sorter etter antall
\nSolution<\/b><\/p>\n
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)\n    FROM employees\nGROUP BY TO_CHAR (hire_date, 'YYYY');\n<\/code><\/pre>\n
Tabell Employees. F\u00e5 antall avdelinger som har ansatte
\nSolution<\/b><\/p>\n
SELECT COUNT (COUNT (*)) department_count\n    FROM employees\n   WHERE department_id IS NOT NULL\nGROUP BY department_id;\n<\/code><\/pre>\n
Tabell Employees. F\u00e5 en liste over department_id der mer enn 30 ansatte jobber
\nSolution<\/b><\/p>\n
  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (*) > 30;\n<\/code><\/pre>\n
Tabell Employees. F\u00e5 en liste over department_id og avrundet gjennomsnittlig l\u00f8nn for ansatte i hver avdeling. 
\nSolution<\/b><\/p>\n
  SELECT department_id, ROUND (AVG (salary)) avg_salary\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n
Tabell Countries. F\u00e5 en liste over region_id med summen av alle bokstaver i country_name der det er mer enn 60
\nSolution<\/b><\/p>\n
  SELECT region_id\n    FROM countries\nGROUP BY region_id\n  HAVING SUM (LENGTH (country_name)) > 60;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over department_id der arbeidere jobber med flere (>1) job_id
\nSolution<\/b><\/p>\n
  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (DISTINCT job_id) > 1;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over manager_id der antallet av underordnede er mer enn 5 og summen av alle l\u00f8nningene til underordnede er mer enn 50000
\nSolution<\/b><\/p>\n
  SELECT manager_id\n    FROM employees\nGROUP BY manager_id\n  HAVING COUNT (*) > 5 AND SUM (salary) > 50000;\n<\/code><\/pre>\n
Tabell over ansatte. Hent en liste over manager_id der gjennomsnittlig l\u00f8nn for alle underordnede ligger mellom 6000 og 9000 som ikke f\u00e5r bonuser (commission_pct er tomt)
\nSolution<\/b><\/p>\n
  SELECT manager_id, AVG (salary) avg_salary\n    FROM employees\n   WHERE commission_pct IS NULL\nGROUP BY manager_id\n  HAVING AVG (salary) BETWEEN 6000 AND 9000;\n<\/code><\/pre>\n
Tabell over ansatte. Hent den h\u00f8yeste l\u00f8nnen blant alle ansatte med job_id som slutter p\u00e5 ordet 'CLERK'
\nSolution<\/b><\/p>\n
SELECT MAX (salary) max_salary\n  FROM employees\n WHERE job_id LIKE '%CLERK';\n\nSELECT MAX (salary) max_salary\n  FROM employees\n WHERE SUBSTR (job_id, -5) = 'CLERK';\n<\/code><\/pre>\n
Tabell over ansatte. Hent den h\u00f8yeste l\u00f8nnen blant alle gjennomsnittlige l\u00f8nninger per avdeling
\nSolution<\/b><\/p>\n
  SELECT MAX (AVG (salary))\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n
Tabell over ansatte. Hent antall ansatte med samme antall bokstaver i navnet. Vise kun de med navn lengre enn 5 og antall ansatte med det navnet st\u00f8rre enn 20. Sorter etter lengden p\u00e5 navnet
\nSolution<\/b><\/p>\n
  SELECT LENGTH (first_name), COUNT (*)\n    FROM employees\nGROUP BY LENGTH (first_name)\n  HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20\nORDER BY LENGTH (first_name);\n\n  SELECT LENGTH (first_name), COUNT (*)\n    FROM employees\n   WHERE LENGTH (first_name) > 5\nGROUP BY LENGTH (first_name)\n  HAVING COUNT (*) > 20\nORDER BY LENGTH (first_name);\n<\/code><\/pre>\n
\nVisning av data fra flere tabeller ved hjelp av joins<\/b><\/p>\n
Tabell over ansatte, avdelinger, lokasjoner, land, regioner. Hent en liste over regioner og antall ansatte i hver region
\nSolution<\/b><\/p>\n
  SELECT region_name, COUNT (*)\n    FROM employees e\n         JOIN departments d ON (e.department_id = d.department_id)\n         JOIN locations l ON (d.location_id = l.location_id)\n         JOIN countries c ON (l.country_id = c.country_id)\n         JOIN regions r ON (c.region_id = r.region_id)\nGROUP BY region_name;\n<\/code><\/pre>\n
Tabell over ansatte, avdelinger, lokasjoner, land, regioner. Hent detaljert informasjon om hver ansatt:
\nFornavn, Etternavn, Avdeling, Jobb, Gate, Land, Region
\nSolution<\/b><\/p>\n
SELECT First_name,\n       Last_name,\n       Department_name,\n       Job_id,\n       street_address,\n       Country_name,\n       Region_name\n  FROM employees e\n       JOIN departments d ON (e.department_id = d.department_id)\n       JOIN locations l ON (d.location_id = l.location_id)\n       JOIN countries c ON (l.country_id = c.country_id)\n       JOIN regions r ON (c.region_id = r.region_id);\n<\/code><\/pre>\n
Tabell over ansatte. Vis alle ledere som har mer enn 6 ansatte under seg
\nSolution<\/b><\/p>\n
  SELECT man.first_name, COUNT (*)\n    FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)\nGROUP BY man.first_name\n  HAVING COUNT (*) > 6;\n<\/code><\/pre>\n
Tabell over ansatte. Vise alle ansatte som ikke har noen underordnede.
\nSolution<\/b><\/p>\n
SELECT emp.first_name\n  FROM employees emp\n       LEFT JOIN employees man ON (emp.manager_id = man.employee_id)\n WHERE man.FIRST_NAME IS NULL;\n\nSELECT first_name\n  FROM employees\n WHERE manager_id IS NULL;\n<\/code><\/pre>\n
Tabell over ansatte, arbeidshistorikk. I tabellen Ansatte lagres alle ansatte. I tabellen Arbeidshistorikk lagres ansatte som har forlatt selskapet. Hent rapport om alle ansatte og deres status i selskapet (Jobber eller har forlatt selskapet med dato for avgang).
\nEksempel:
\nfirst_name | status
\nJennifer | Forlot selskapet 31. desember 2006
\nClara | Jobber for tiden
\nSolution<\/b><\/p>\n
SELECT first_name,\n       NVL2 (\n           end_date,\n           TO_CHAR (end_date, 'fm\"\"Forlot selskapet p\u00e5\"\" DD \"\"i\"\" M\u00e5ned, YYYY'),\n           'Jobber for tiden')\n           status\n  FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id);\n<\/code><\/pre>\n
Tabell over ansatte, avdelinger, beliggenheter, land, regioner. Hent liste over ansatte som bor i Europa (region_name).
\nSolution<\/b><\/p>\n
 SELECT first_name\n  FROM employees\n       JOIN departments USING (department_id)\n       JOIN locations USING (location_id)\n       JOIN countries USING (country_id)\n       JOIN regions USING (region_id)\n WHERE region_name = 'Europe';\n \n SELECT first_name\n  FROM employees e\n       JOIN departments d ON (e.department_id = d.department_id)\n       JOIN locations l ON (d.location_id = l.location_id)\n       JOIN countries c ON (l.country_id = c.country_id)\n       JOIN regions r ON (c.region_id = r.region_id)\n WHERE region_name = 'Europe';\n<\/code><\/pre>\n
Tabell over ansatte, avdelinger. Vise alle avdelinger der mer enn 30 ansatte jobber.
\nSolution<\/b><\/p>\n
SELECT department_name, COUNT (*)\n    FROM employees e JOIN departments d ON (e.department_id = d.department_id)\nGROUP BY department_name\n  HAVING COUNT (*) > 30;\n<\/code><\/pre>\n
Tabell over ansatte, avdelinger. Vise alle ansatte som ikke tilh\u00f8rer noen avdeling.
\nSolution<\/b><\/p>\n
SELECT first_name\n  FROM employees e\n       LEFT JOIN departments d ON (e.department_id = d.department_id)\n WHERE d.department_name IS NULL;\n\nSELECT first_name\n  FROM employees\n WHERE department_id IS NULL;\n<\/code><\/pre>\n
Tabell over ansatte, avdelinger. Vise alle avdelinger som ikke har noen ansatte.
\nSolution<\/b><\/p>\n
SELECT department_name\n  FROM employees e\n       RIGHT JOIN departments d ON (e.department_id = d.department_id)\n WHERE first_name IS NULL;\n<\/code><\/pre>\n
Tabell over ansatte. Vise alle ansatte som ikke har noen som rapporterer til dem.
\nSolution<\/b><\/p>\n
SELECT man.first_name\n  FROM employees emp\n       RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)\n WHERE emp.FIRST_NAME IS NULL;\n<\/code><\/pre>\n
Tabell over ansatte, jobber, avdelinger. Vise ansatte i formatet: First_name, Job_title, Department_name.
\nEksempel:
\nFirst_name | Job_title | Department_name
\nDonald | Shipping | Clerk Shipping
\nSolution<\/b><\/p>\n
SELECT first_name, job_title, department_name\n  FROM employees e\n       JOIN jobs j ON (e.job_id = j.job_id)\n       JOIN departments d ON (d.department_id = e.department_id);\n<\/code><\/pre>\n
Tabell over ansatte. Hent liste over ansatte hvis ledere begynte i jobben i 2005, men som selv begynte \u00e5 jobbe f\u00f8r 2005.
\nSolution<\/b><\/p>\n
SELECT emp.*\n  FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id)\n WHERE     TO_CHAR (man.hire_date, 'YYYY') = '2005'\n       AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');\n<\/code><\/pre>\n
Tabell for ansatt. Hent liste over ansatte hvis ledere startet i januar m\u00e5ned i et hvilket som helst \u00e5r, og lengden p\u00e5 stillingstittelen til disse ansatte er mer enn 15 tegn.
\nSolution<\/b><\/p>\n
SELECT emp.*\n  FROM employees emp\n       JOIN employees man ON (emp.manager_id = man.employee_id)\n       JOIN jobs j ON (emp.job_id = j.job_id)\n WHERE TO_CHAR (man.hire_date, 'MM') = '01' AND LENGTH (j.job_title) > 15;\n<\/code><\/pre>\n
\nBruke subsp\u00f8rringer for \u00e5 l\u00f8se sp\u00f8rringer<\/b><\/p>\n
Tabell for ansatt. Hent liste over ansatte med det lengste navnet.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE LENGTH (first_name) =\n       (SELECT MAX (LENGTH (first_name)) FROM employees);\n<\/code><\/pre>\n
Tabell for ansatt. Hent liste over ansatte med l\u00f8nn over gjennomsnittlig l\u00f8nn for alle ansatte.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE salary > (SELECT AVG (salary) FROM employees);\n<\/code><\/pre>\n
Tabell for ansatte, avdelinger, steder. Hent byen der ansatte totalt tjener minst.
\nSolution<\/b><\/p>\n
SELECT city\n    FROM employees e\n         JOIN departments d ON (e.department_id = d.department_id)\n         JOIN locations l ON (d.location_id = l.location_id)\nGROUP BY city\n  HAVING SUM (salary) =\n         (  SELECT MIN (SUM (salary))\n              FROM employees e\n                   JOIN departments d ON (e.department_id = d.department_id)\n                   JOIN locations l ON (d.location_id = l.location_id)\n          GROUP BY city);\n<\/code><\/pre>\n
Tabell for ansatt. Hent liste over ansatte hvis leder tjener mer enn 15000.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE manager_id IN (SELECT employee_id\n                        FROM employees\n                       WHERE salary > 15000)\n<\/code><\/pre>\n
Tabell over ansatte, avdelinger. Vise alle avdelinger som ikke har noen ansatte.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM departments\n WHERE department_id NOT IN (SELECT department_id\n                               FROM employees\n                              WHERE department_id IS NOT NULL);\n<\/code><\/pre>\n
Tabell for ansatt. Vis alle ansatte som ikke er ledere.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE employee_id NOT IN (SELECT manager_id\n                             FROM employees\n                            WHERE manager_id IS NOT NULL)\n<\/code><\/pre>\n
Tabell over ansatte. Vis alle ledere som har mer enn 6 ansatte under seg
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees e\n WHERE (SELECT COUNT (*)\n          FROM employees\n         WHERE manager_id = e.employee_id) > 6;\n<\/code><\/pre>\n
Tabell for ansatte, avdelinger. Vis ansatte som jobber i IT-avdelingen.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = (SELECT department_id\n                          FROM departments\n                         WHERE department_name = 'IT');\n<\/code><\/pre>\n
Tabell over ansatte, jobber, avdelinger. Vise ansatte i formatet: First_name, Job_title, Department_name.
\nEksempel:
\nFirst_name | Job_title | Department_name
\nDonald | Shipping | Clerk Shipping
\nSolution<\/b><\/p>\n
SELECT first_name,\n       (SELECT job_title\n          FROM jobs\n         WHERE job_id = e.job_id)\n           job_title,\n       (SELECT department_name\n          FROM departments\n         WHERE department_id = e.department_id)\n           department_name\n  FROM employees e;\n<\/code><\/pre>\n
Tabell over ansatte. Hent liste over ansatte hvis ledere begynte i jobben i 2005, men som selv begynte \u00e5 jobbe f\u00f8r 2005.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE     manager_id IN (SELECT employee_id\n                            FROM employees\n                           WHERE TO_CHAR (hire_date, 'YYYY') = '2005')\n       AND hire_date < TO_DATE ('01012005', 'DDMMYYYY');\n<\/code><\/pre>\n
Tabell for ansatt. Hent liste over ansatte hvis ledere startet i januar m\u00e5ned i et hvilket som helst \u00e5r, og lengden p\u00e5 stillingstittelen til disse ansatte er mer enn 15 tegn.
\nSolution<\/b><\/p>\n
SELECT *\n  FROM employees e\n WHERE     manager_id IN (SELECT employee_id\n                            FROM employees\n                           WHERE TO_CHAR (hire_date, 'MM') = '01')\n       AND (SELECT LENGTH (job_title)\n              FROM jobs\n             WHERE job_id = e.job_id) > 15;\n<\/code><\/pre>\n
\nDet er alt for n\u00e5.<\/b><\/p>\n
Eg h\u00e5par oppg\u00e5vene var interessante og spennande. 
\nEg vil s\u00e5 langt som mogleg oppdatere denne oppgavelista.
\nEg set ogs\u00e5 pris p\u00e5 eventuelle merknader og forslag.<\/p>\n
P.S.: Om nokon kjem p\u00e5 ei interessant oppg\u00e5ve p\u00e5 SELECT, skriv i kommentarfeltet, s\u00e5 legg eg den til lista.<\/p>\n
Takk.
\n
Kilde: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u044b\u0432\u0430\u0442\u044c \u043e \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044f\u0445 \u0438 \u0442\u0435\u043e\u0440\u0435\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u043e\u0441\u043d\u043e\u0432\u0430\u0445. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u044f \u043f\u043e\u0434\u0435\u043b\u044e\u0441\u044c \u0441 \u0432\u0430\u043c\u0438 \u0441\u0432\u043e\u0438\u043c \u0441\u043f\u0438\u0441\u043a\u043e\u043c \u0437\u0430\u0434\u0430\u0447, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u044f \u0434\u0430\u044e […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27465,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[702],"tags":[],"class_list":["post-36671","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-news"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47SQL. Morosame oppg\u00e5ver | ProHoster","description":"Hei, Habr! Eg har allereie i over 3 \u00e5r undervist i SQL p\u00e5 ulike treningsterminalar, og eit av mine observationar er at studentar l\u00e6rar og forst\u00e5r SQL betre n\u00e5r dei f\u00e5r ei oppg\u00e5ve, i staden for berre \u00e5 l\u00e6re det.","canonical_url":"https:\/\/prohoster.info\/no\/blog\/news\/sql-zanimatelnye-zadachki","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"nn_NO","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47SQL. \u0417\u0430\u043d\u0438\u043c\u0430\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0437\u0430\u0434\u0430\u0447\u043a\u0438 | ProHoster","og:description":"\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439, \u0425\u0430\u0431\u0440! \u0412\u043e\u0442 \u0443\u0436\u0435 \u0431\u043e\u043b\u0435\u0435 3-\u0445 \u043b\u0435\u0442 \u044f \u043f\u0440\u0435\u043f\u043e\u0434\u0430\u044e SQL \u0432 \u0440\u0430\u0437\u043d\u044b\u0445 \u0442\u0440\u0435\u043d\u0438\u043d\u0433 \u0446\u0435\u043d\u0442\u0440\u0430\u0445, \u0438 \u043e\u0434\u043d\u0438\u043c \u0438\u0437 \u043c\u043e\u0438\u0445 \u043d\u0430\u0431\u043b\u044e\u0434\u0435\u043d\u0438\u0439 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0442\u043e, \u0447\u0442\u043e \u0441\u0442\u0443\u0434\u0435\u043d\u0442\u044b \u043e\u0441\u0432\u0430\u0438\u0432\u0430\u044e\u0442 \u0438 \u043f\u043e\u043d\u0438\u043c\u0430\u044e\u0442 SQL \u043b\u0443\u0447\u0448\u0435, \u0435\u0441\u043b\u0438 \u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043f\u0435\u0440\u0435\u0434 \u043d\u0438\u043c\u0438 \u0437\u0430\u0434\u0430\u0447\u0443, \u0430 \u043d\u0435 \u043f\u0440\u043e\u0441\u0442\u043e.","og:url":"https:\/\/prohoster.info\/no\/blog\/news\/sql-zanimatelnye-zadachki","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:13:01+00:00","article:modified_time":"2019-10-31T19:13:01+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36671","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-22 04:21:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:40:23","updated":"2026-01-22 04:21:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/posts\/36671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/comments?post=36671"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/posts\/36671\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/media\/27465"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/media?parent=36671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/categories?post=36671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/no\/wp-json\/wp\/v2\/tags?post=36671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}