{"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\/da\/blog\/news\/sql-zanimatelnye-zadachki","title":{"rendered":"SQL. Underholdende opgaver","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hej, Habr!<\/p>\n

I over 3 \u00e5r har jeg undervist i SQL p\u00e5 forskellige tr\u00e6ningscentre, og en af mine observationer er, at studerende tilegner sig og forst\u00e5r SQL bedre, n\u00e5r de f\u00e5r stillet opgaver, i stedet for blot at h\u00f8re om mulighederne og de teoretiske grundlag.<\/p>\n

I denne artikel vil jeg dele min liste over opgaver, som jeg giver til studerende som hjemmearbejde, og som vi laver forskellige former for brainstorming ud fra, hvilket f\u00f8rer til en dyb og klar forst\u00e5else af SQL.<\/p>\n

\"SQL.
\n
\nSQL (\u02c8\u025bs\u02c8kju\u02c8\u025bl; eng. structured query language \u2013 'strukturert foresp\u00f8rgselssprog') \u2013 er et deklarativt programmeringssprog, der anvendes til at oprette, \u00e6ndre og administrere data i en relationsdatabase, der styres af et passende databaseh\u00e5ndteringssystem. L\u00e6s mere\u2026 <\/a><\/noindex><\/p>\n

Du kan finde information om SQL fra forskellige kilder<\/a><\/noindex>.
\nDenne artikel har ikke til hensigt at l\u00e6re dig SQL fra bunden.
\n<\/a><\/noindex>
\nS\u00e5 lad os komme i gang.<\/p>\n

Vi vil benytte den velkendte HR-skema<\/a><\/noindex> i Oracle med dens tabeller (Mere information<\/a><\/noindex>):<\/p>\n

\"SQL.
\nJeg vil bem\u00e6rke, at vi kun vil se p\u00e5 opgaver om SELECT. Her er der ikke opgaver om DML og DDL.<\/p>\n

Opgaver<\/h2>\n

\nBegr\u00e6nsning og sortering af data<\/b><\/p>\n

Tabel Employees. F\u00e5 en liste med information om alle ansatte
\nL\u00f8sning<\/b><\/p>\n

SELECT * FROM employees\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere med navnet 'David'
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name = 'David';\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere med job_id lig med 'IT_PROG'
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE job_id = 'IT_PROG'\n<\/code><\/pre>\n
Tabel Employees. F\u00e5 en liste over alle ansatte fra afdeling 50 (department_id) med l\u00f8n (salary) over 4000
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = 50 AND salary > 4000;\n<\/code><\/pre>\n
Tabel Employees. F\u00e5 en liste over alle ansatte fra 20. og 30. afdeling (department_id)
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE department_id = 20 OR department_id = 30;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere, hvis navn ender med bogstavet 'a'
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%a';\n<\/code><\/pre>\n
Tabel Employees. F\u00e5 en liste over alle ansatte fra afdeling 50 og afdeling 80 (department_id), som har en bonus (v\u00e6rdien i kolonnen commission_pct er ikke tom)
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over alle medarbejdere, der har mindst 2 bogstaver 'n' i deres navn
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%n%n%';\n<\/code><\/pre>\n
Tabel Employees. F\u00e5 en liste over alle ansatte, hvis navn er l\u00e6ngere end 4 bogstaver
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%_____%';\n<\/code><\/pre>\n
Tabel over ansatte. Hent en liste over alle ansatte, hvis l\u00f8n ligger mellem 8000 og 9000 (inklusive)
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE salary BETWEEN 8000 AND 9000;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere, der har tegnet '%' i deres navn
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE first_name LIKE '%%%' ESCAPE '';\n<\/code><\/pre>\n
Tabel over ansatte. Hent en liste over alle ID'er for ledere
\nL\u00f8sning<\/b><\/p>\n
SELECT DISTINCT manager_id\n  FROM employees\n WHERE manager_id IS NOT NULL;\n<\/code><\/pre>\n
Tabel over ansatte. Hent en liste over medarbejdere med deres stillinger i formatet: Donald(sh_clerk)
\nL\u00f8sning<\/b><\/p>\n
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;\n<\/code><\/pre>\n
\nBrug af enkelt-r\u00e6kke funktioner til at tilpasse output<\/b><\/p>\n
Tabel over ansatte. Hent en liste over alle ansatte, hvis navn er l\u00e6ngere end 10 bogstaver
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE LENGTH (first_name) > 10;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere, der har bogstavet 'b' i deres navn (uanset case)
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name), 'b') > 0;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere, der har mindst 2 bogstaver 'a' i deres navn
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE INSTR (LOWER (first_name), 'a', 1, 2) > 0;\n<\/code><\/pre>\n
Tabel over ansatte. Hent en liste over alle ansatte, hvis l\u00f8n er delelig med 1000
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MOD (salary, 1000) = 0;\n<\/code><\/pre>\n
Tabel over ansatte. Hent det f\u00f8rste 3-cifrede nummer fra medarbejderens telefonnummer, hvis nummeret er i formatet XXX.XXX.XXXX
\nL\u00f8sning<\/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
Tabel over afdelinger. Hent det f\u00f8rste ord fra afdelingens navn for dem, der har mere end \u00e9t ord i navnet
\nL\u00f8sning<\/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
Tabel over ansatte. Hent medarbejdernes navne uden den f\u00f8rste og sidste bogstav i navnet
\nL\u00f8sning<\/b><\/p>\n
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name\n  FROM employees;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere, hvis navn ender med bogstavet 'm' og er l\u00e6ngere end 5 tegn
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name) > 5;\n<\/code><\/pre>\n
Tabel over Dual. Hent datoen for n\u00e6ste fredag
\nL\u00f8sning<\/b><\/p>\n
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;\n<\/code><\/pre>\n
Tabel over ansatte. Hent en liste over alle ansatte, der har arbejdet i virksomheden i mere end 17 \u00e5r
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE MONTHS_BETWEEN (SYSDATE, hire_date) \/ 12 > 17;\n<\/code><\/pre>\n
Tabel over ansatte. Hent en liste over alle ansatte, hvis sidste cifre i telefonnummeret er ulige og best\u00e5r af 3 cifre adskilt af prikker
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over alle medarbejdere, hvis job_id v\u00e6rdi efter tegnet '_' har mindst 3 tegn, men denne v\u00e6rdi efter '_' er ikke lig med 'CLERK'
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over alle medarbejdere ved at erstatte alle '.' i PHONE_NUMBER v\u00e6rdien med '-'
\nL\u00f8sning<\/b><\/p>\n
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number\n  FROM employees;\n<\/code><\/pre>\n
\nBrug af konverteringsfunktioner og betingede udtryk<\/b><\/p>\n
Tabel Employees. Hent listen over alle medarbejdere, der startede den f\u00f8rste dag i en m\u00e5ned (enhver m\u00e5ned)
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE TO_CHAR (hire_date, 'DD') = '01';\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere, der startede i 2008
\nL\u00f8sning<\/b><\/p>\n
SELECT *\n  FROM employees\n WHERE TO_CHAR (hire_date, 'YYYY') = '2008';\n<\/code><\/pre>\n
Tabel DUAL. Vis i morgen dato i format: Tomorrow is Second day of January
\nL\u00f8sning<\/b><\/p>\n
SELECT TO_CHAR (SYSDATE, 'fm\"\"Tomorrow is \"\"Ddspth \"\"day of\"\" Month') info\n  FROM DUAL;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over alle medarbejdere og datoen for hver ans\u00e6ttelse i formatet: 21st of June, 2007
\nL\u00f8sning<\/b><\/p>\n
SELECT first_name, TO_CHAR (hire_date, 'fmddth \"\"of\"\" Month, YYYY') hire_date\n  FROM employees;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over medarbejdere med l\u00f8nstigninger p\u00e5 20%. Vis l\u00f8nnen med dollar tegn
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over alle medarbejdere, der startede i februar 2007.
\nL\u00f8sning<\/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
Tabel DUAL. Vis den aktuelle dato, + sekund, + minut, + time, + dag, + m\u00e5ned, + \u00e5r
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over alle medarbejdere med totale l\u00f8nninger (salary + commission_pct(%)) i formatet: $24,000.00
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over alle medarbejdere og information om tilstedev\u00e6relsen af l\u00f8nbonusser (Ja\/Nej)
\nL\u00f8sning<\/b><\/p>\n
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus\n  FROM employees;\n<\/code><\/pre>\n
Tabel Employees. Hent niveauet for hver medarbejders l\u00f8n: Under 5000 betragtes som lavt niveau, Lige med eller over 5000 og under 10000 betragtes som normalt niveau, Lige med eller over 10000 betragtes som h\u00f8jt niveau
\nL\u00f8sning<\/b><\/p>\n
SELECT first_name,\n       salary,\n       CASE\n           WHEN salary = 5000 AND salary < 10000 THEN 'Normal'\n           ELSE 'H\u00f8j'\n       END salary_level\n  FROM employees;\n<\/code><\/pre>\n
Tabel Countries. Vis regionen for hvert land: 1-Europe, 2-America, 3-Asia, 4-Africa (uden Join)
\nL\u00f8sning<\/b><\/p>\n
SELECT country_name country,\n       DECODE (region_id,\n               1, 'Europa',\n               2, 'Amerika',\n               3, 'Asien',\n               4, 'Afrika',\n               'Ukendt')\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 'Asien'\n           WHEN 4 THEN 'Afrika'\n           ELSE 'Ukendt'\n       END\n           region\n  FROM countries;\n<\/code><\/pre>\n
\nRapportering af aggregerede data ved brug af gruppefunktioner<\/b><\/p>\n
Tabel Employees. F\u00e5 en rapport over department_id med minimum og maksimum l\u00f8n, tidlig og sen ans\u00e6ttelsesdato samt antallet af medarbejdere. Sorter efter antallet af medarbejdere (i faldende r\u00e6kkef\u00f8lge)
\nL\u00f8sning<\/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
Tabel Employees. Hvor mange medarbejdere har navne der starter med samme bogstav? Sorter efter antal. Vis kun dem hvor antallet er st\u00f8rre end 1
\nL\u00f8sning<\/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
Tabel Employees. Hvor mange medarbejdere arbejder i samme afdeling og f\u00e5r samme l\u00f8n?
\nL\u00f8sning<\/b><\/p>\n
SELECT department_id, salary, COUNT (*)\n    FROM employees\nGROUP BY department_id, salary\n  HAVING COUNT (*) > 1;\n<\/code><\/pre>\n
Tabel Employees. F\u00e5 en rapport om hvor mange medarbejdere der blev ansat p\u00e5 hver ugedag. Sorter efter antal
\nL\u00f8sning<\/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
Tabel Employees. F\u00e5 en rapport om hvor mange medarbejdere der blev ansat hvert \u00e5r. Sorter efter antal
\nL\u00f8sning<\/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
Tabel Employees. F\u00e5 antallet af afdelinger med medarbejdere
\nL\u00f8sning<\/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
Tabel Employees. F\u00e5 en liste over department_id med mere end 30 medarbejdere
\nL\u00f8sning<\/b><\/p>\n
  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (*) > 30;\n<\/code><\/pre>\n
Tabel Employees. F\u00e5 en liste over department_id og den afrundede gennemsnitlige l\u00f8n for medarbejdere i hver afdeling. 
\nL\u00f8sning<\/b><\/p>\n
  SELECT department_id, ROUND (AVG (salary)) avg_salary\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n
Tabel Countries. F\u00e5 en liste over region_id med summen af alle tegn i country_name hvor der er mere end 60
\nL\u00f8sning<\/b><\/p>\n
  SELECT region_id\n    FROM countries\nGROUP BY region_id\n  HAVING SUM (LENGTH (country_name)) > 60;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over department_id, hvor medarbejdere arbejder med flere (>1) job_id
\nL\u00f8sning<\/b><\/p>\n
  SELECT department_id\n    FROM employees\nGROUP BY department_id\n  HAVING COUNT (DISTINCT job_id) > 1;\n<\/code><\/pre>\n
Tabel Employees. Hent listen over manager_id, der har flere end 5 underordnede, og summen af alle l\u00f8nninger for hans underordnede er over 50000
\nL\u00f8sning<\/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
Tabel Employees. Hent listen over manager_id, hvor den gennemsnitlige l\u00f8n for alle hans underordnede ligger mellem 6000 og 9000, og som ikke modtager bonusser (commission_pct er tom)
\nL\u00f8sning<\/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
Tabel Employees. Hent den maksimale l\u00f8n blandt alle medarbejdere, hvis job_id slutter p\u00e5 ordet 'CLERK'
\nL\u00f8sning<\/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
Tabel Employees. Hent den h\u00f8jeste l\u00f8n blandt alle gennemsnitlige l\u00f8nninger pr. afdeling
\nL\u00f8sning<\/b><\/p>\n
  SELECT MAX (AVG (salary))\n    FROM employees\nGROUP BY department_id;\n<\/code><\/pre>\n
Tabel Employees. Hent antallet af medarbejdere med samme antal bogstaver i navnet. Vis kun dem, hvis l\u00e6ngde af navnet er over 5 og antallet af medarbejdere med dette navn er over 20. Sorter efter l\u00e6ngden af navnet
\nL\u00f8sning<\/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 af data fra flere tabeller ved hj\u00e6lp af joins<\/b><\/p>\n
Tabel Employees, Departaments, Locations, Countries, Regions. Hent listen over regioner og antallet af medarbejdere i hver region
\nL\u00f8sning<\/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
Tabel Employees, Departaments, Locations, Countries, Regions. Hent detaljerede oplysninger om hver medarbejder:
\nFirst_name, Last_name, Departament, Job, Street, Country, Region
\nL\u00f8sning<\/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
Tabel Employees. Vis alle ledere, der har flere end 6 medarbejdere under dem
\nL\u00f8sning<\/b><\/p>\n
  V\u00c6LG man.first_name, COUNT (*)\n    FRA ansatte emp JOIN ansatte man ON (emp.manager_id = man.employee_id)\nGRUPPER EFTER man.first_name\n  HAVING COUNT (*) > 6;\n<\/code><\/pre>\n
Tabel Ansatte. Vis alle medarbejdere, der ikke rapporterer til nogen.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG emp.first_name\n  FRA ansatte emp\n       VENSTRE JOIN ansatte man ON (emp.manager_id = man.employee_id)\n HVOR man.FIRST_NAME ER NULL;\n\nV\u00c6LG first_name\n  FRA ansatte\n HVOR manager_id ER NULL;\n<\/code><\/pre>\n
Tabel Ansatte, Jobhistorik. I tabellen Ansatte gemmes alle medarbejdere. I tabellen Jobhistorik opbevares medarbejdere, der har forladt virksomheden. F\u00e5 en rapport om alle medarbejdere og deres status i virksomheden (Ansat eller forladt med fratr\u00e6delsesdato).
\nEksempel:
\nfirst_name | status
\nJennifer | Forlod virksomheden den 31. december 2006
\nClara | Arbejder for tiden
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG first_name,\n       NVL2 (\n           end_date,\n           TO_CHAR (end_date, 'fm\"\"Forlod virksomheden den\"\" DD \"\"i\"\" M\u00e5ned, YYYY'),\n           'Arbejde i \u00f8jeblikket')\n           status\n  FRA ansatte e VENSTRE JOIN job_history j ON (e.employee_id = j.employee_id);\n<\/code><\/pre>\n
Tabel Ansatte, Afdelinger, Lokationer, Lande, Regioner. F\u00e5 en liste over medarbejdere, der bor i Europa (region_name).
\nL\u00f8sning<\/b><\/p>\n
 V\u00c6LG first_name\n  FRA ansatte\n       JOIN afdelinger USING (department_id)\n       JOIN lokationer USING (location_id)\n       JOIN lande USING (country_id)\n       JOIN regioner USING (region_id)\n HVOR region_name = 'Europa';\n \n V\u00c6LG first_name\n  FRA ansatte e\n       JOIN afdelinger d ON (e.department_id = d.department_id)\n       JOIN lokationer l ON (d.location_id = l.location_id)\n       JOIN lande c ON (l.country_id = c.country_id)\n       JOIN regioner r ON (c.region_id = r.region_id)\n HVOR region_name = 'Europa';\n<\/code><\/pre>\n
Tabel Ansatte, Afdelinger. Vis alle afdelinger, hvor der arbejder mere end 30 medarbejdere.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG department_name, COUNT (*)\n    FRA ansatte e JOIN afdelinger d ON (e.department_id = d.department_id)\nGRUPPER EFTER department_name\n  HAVING COUNT (*) > 30;\n<\/code><\/pre>\n
Tabel Ansatte, Afdelinger. Vis alle medarbejdere, der ikke er tilknyttet nogen afdeling.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG first_name\n  FRA ansatte e\n       VENSTRE JOIN afdelinger d ON (e.department_id = d.department_id)\n HVOR d.department_name ER NULL;\n\nV\u00c6LG first_name\n  FRA ansatte\n HVOR department_id ER NULL;\n<\/code><\/pre>\n
Tabel Ansatte, Afdelinger. Vis alle afdelinger, hvor der ikke er nogen medarbejdere.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG department_name\n  FRA ansatte e\n       H\u00d8JRE JOIN afdelinger d ON (e.department_id = d.department_id)\n HVOR first_name ER NULL;\n<\/code><\/pre>\n
Tabel Ansatte. Vis alle medarbejdere, der ikke har nogen underordnede.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG man.first_name\n  FRA ansatte emp\n       H\u00d8JRE JOIN ansatte man ON (emp.manager_id = man.employee_id)\n HVOR emp.FIRST_NAME ER NULL;\n<\/code><\/pre>\n
Tabel Ansatte, Jobs, Afdelinger. Vis medarbejdere i formatet: First_name, Job_title, Department_name.
\nEksempel:
\nFirst_name | Job_title | Department_name
\nDonald | Shipping | Clerk Shipping
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG first_name, job_title, department_name\n  FRA ansatte e\n       JOIN jobs j ON (e.job_id = j.job_id)\n       JOIN afdelinger d ON (d.department_id = e.department_id);\n<\/code><\/pre>\n
Tabel Ansatte. F\u00e5 en liste over medarbejdere, hvis ledere blev ansat i 2005, men som selv blev ansat f\u00f8r 2005.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG emp.*\n  FRA ansatte emp JOIN ansatte man ON (emp.manager_id = man.employee_id)\n HVOR TO_CHAR (man.hire_date, 'YYYY') = '2005'\n       OG emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY');\n<\/code><\/pre>\n
Tabel for Ansatte. F\u00e5 en liste over ansatte, hvis ledere blev ansat i januar m\u00e5ned i et hvilket som helst \u00e5r, og l\u00e6ngden af jobtitlen for disse ansatte er over 15 tegn.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG emp.*\n  FRA ansatte emp\n       JOIN ansatte man ON (emp.manager_id = man.employee_id)\n       JOIN jobs j ON (emp.job_id = j.job_id)\n HVOR TO_CHAR (man.hire_date, 'MM') = '01' OG LENGTH (j.job_title) > 15;\n<\/code><\/pre>\n
\nBrug af underforesp\u00f8rgsler til at l\u00f8se foresp\u00f8rgsler<\/b><\/p>\n
Tabel for Ansatte. F\u00e5 en liste over ansatte med det l\u00e6ngste navn.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte\n HVOR LENGTH (first_name) =\n       (V\u00c6LG MAX (LENGTH (first_name)) FRA ansatte);\n<\/code><\/pre>\n
Tabel for Ansatte. F\u00e5 en liste over ansatte med en l\u00f8n h\u00f8jere end den gennemsnitlige l\u00f8n for alle ansatte.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte\n HVOR salary > (V\u00c6LG AVG (salary) FRA ansatte);\n<\/code><\/pre>\n
Tabel for Ansatte, Afdelinger, Lokationer. F\u00e5 byen, hvor de ansatte samlet set tjener mindst.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG city\n    FRA ansatte e\n         JOIN afdelinger d ON (e.department_id = d.department_id)\n         JOIN lokationer l ON (d.location_id = l.location_id)\nGRUPPER EFTER city\n  HAVING SUM (salary) =\n         (  V\u00c6LG MIN (SUM (salary))\n              FRA ansatte e\n                   JOIN afdelinger d ON (e.department_id = d.department_id)\n                   JOIN lokationer l ON (d.location_id = l.location_id)\n          GRUPPER EFTER city);\n<\/code><\/pre>\n
Tabel for Ansatte. F\u00e5 en liste over ansatte, hvis leder tjener mere end 15000.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte\n HVOR manager_id IN (V\u00c6LG employee_id\n                        FRA ansatte\n                       HVOR salary > 15000)\n<\/code><\/pre>\n
Tabel Ansatte, Afdelinger. Vis alle afdelinger, hvor der ikke er nogen medarbejdere.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA afdelinger\n HVOR department_id NOT IN (V\u00c6LG department_id\n                               FRA ansatte\n                              HVOR department_id IS NOT NULL);\n<\/code><\/pre>\n
Tabel for Ansatte. Vis alle ansatte, som ikke er ledere.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte\n HVOR employee_id NOT IN (V\u00c6LG manager_id\n                             FRA ansatte\n                            HVOR manager_id IS NOT NULL)\n<\/code><\/pre>\n
Tabel Employees. Vis alle ledere, der har flere end 6 medarbejdere under dem
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte e\n HVOR (V\u00c6LG COUNT (*)\n          FRA ansatte\n         HVOR manager_id = e.employee_id) > 6;\n<\/code><\/pre>\n
Tabel for Ansatte, Afdelinger. Vis ansatte, der arbejder i IT-afdelingen.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte\n HVOR department_id = (V\u00c6LG department_id\n                          FRA afdelinger\n                         HVOR department_name = 'IT');\n<\/code><\/pre>\n
Tabel Ansatte, Jobs, Afdelinger. Vis medarbejdere i formatet: First_name, Job_title, Department_name.
\nEksempel:
\nFirst_name | Job_title | Department_name
\nDonald | Shipping | Clerk Shipping
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG first_name,\n       (V\u00c6LG job_title\n          FRA jobs\n         HVOR job_id = e.job_id)\n           job_title,\n       (V\u00c6LG department_name\n          FRA afdelinger\n         HVOR department_id = e.department_id)\n           department_name\n  FRA ansatte e;\n<\/code><\/pre>\n
Tabel Ansatte. F\u00e5 en liste over medarbejdere, hvis ledere blev ansat i 2005, men som selv blev ansat f\u00f8r 2005.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte\n HVOR     manager_id IN (V\u00c6LG employee_id\n                            FRA ansatte\n                           HVOR TO_CHAR (hire_date, 'YYYY') = '2005')\n       OG hire_date < TO_DATE ('01012005', 'DDMMYYYY');\n<\/code><\/pre>\n
Tabel for Ansatte. F\u00e5 en liste over ansatte, hvis ledere blev ansat i januar m\u00e5ned i et hvilket som helst \u00e5r, og l\u00e6ngden af jobtitlen for disse ansatte er over 15 tegn.
\nL\u00f8sning<\/b><\/p>\n
V\u00c6LG *\n  FRA ansatte e\n HVOR     manager_id IN (V\u00c6LG employee_id\n                            FRA ansatte\n                           HVOR TO_CHAR (hire_date, 'MM') = '01')\n       OG (V\u00c6LG LENGTH (job_title)\n              FRA jobs\n             HVOR job_id = e.job_id) > 15;\n<\/code><\/pre>\n
\nDet er alt for nu.<\/b><\/p>\n
Jeg h\u00e5ber, opgaverne var interessante og underholdende. 
\nJeg vil s\u00e5 vidt muligt opdatere denne liste med opgaver.
\nJeg er ogs\u00e5 \u00e5ben for eventuelle kommentarer og forslag.<\/p>\n
P.S.: Hvis nogen f\u00e5r en interessant opgave til SELECT, s\u00e5 skriv i kommentarer, s\u00e5 tilf\u00f8jer jeg den til listen.<\/p>\n
Tak.
\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. Sp\u00e6ndende opgaver | ProHoster","description":"Hej, Habr! I mere end 3 \u00e5r har jeg undervist i SQL i forskellige tr\u00e6ningscentre, og en af mine observationer er, at studerende tilegner sig og forst\u00e5r SQL bedre, hvis de pr\u00e6senteres for en opgave i stedet for bare.","canonical_url":"https:\/\/prohoster.info\/da\/blog\/news\/sql-zanimatelnye-zadachki","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"da_DK","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\/da\/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\/da\/wp-json\/wp\/v2\/posts\/36671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/comments?post=36671"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts\/36671\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media\/27465"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media?parent=36671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/categories?post=36671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/tags?post=36671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}