🥇SQL. Tòimhseachain aoigheachd

Halo Habr!

Airson còrr air 3 bliadhna tha mi air a bhith a’ teagasg SQL ann an diofar ionadan trèanaidh, agus is e aon de na beachdan a th’ agam gum bi oileanaich a’ maighstireachd agus a’ tuigsinn SQL nas fheàrr ma gheibh iad gnìomh, agus chan ann dìreach a’ bruidhinn mu na cothroman agus na bunaitean teòiridheach.

San artaigil seo, roinnidh mi riut mo liosta de ghnìomhan a bheir mi do dh’ oileanaich mar obair-dachaigh agus air am bi sinn a’ dèanamh diofar sheòrsan cnuasachadh-eanchainn, a tha a’ leantainn gu tuigse dhomhainn is shoilleir air SQL.

Tha SQL (ˈɛsˈkjuˈɛl; eng. cànan ceist structarail) na chànan prògramadh dearbhach a thathas a’ cleachdadh gus dàta a chruthachadh, atharrachadh agus a riaghladh ann an stòr-dàta dàimheach air a riaghladh le siostam riaghlaidh stòr-dàta iomchaidh. Ionnsaich tuilleadh

Faodaidh tu leughadh mu SQL bho dhiofar stòran.
Chan eil an artaigil seo an dùil SQL a theagasg dhut bhon fhìor thoiseach.

Mar sin rachamaid.

Cleachdaidh sinn an fheadhainn ainmeil Sgeama HR ann an Oracle le a bhùird (Tuilleadh):

Tha mi a’ toirt fa-near nach beachdaich sinn ach air gnìomhan air SELECT. Chan eil gnìomhan sam bith air DML agus DDL.

gnìomhan

A 'cuingealachadh agus a' rèiteachadh dàta

Clàr luchd-obrach. Faigh liosta le fiosrachadh mun luchd-obrach gu lèir
co-dhùnadh

SELECT * FROM employees

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad leis an ainm ‘David’
co-dhùnadh

SELECT *
  FROM employees
 WHERE first_name = 'David';

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad le job_id co-ionann ri 'IT_PROG'
co-dhùnadh

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad bhon 50mh roinn (department_id) le tuarastal (tuarastal) nas àirde na 4000
co-dhùnadh

SELECT *
  FROM employees
 WHERE department_id = 50 AND salary > 4000;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad bhon 20mh agus bhon 30mh roinn (department_id)
co-dhùnadh

SELECT *
  FROM employees
 WHERE department_id = 20 OR department_id = 30;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil an litir mu dheireadh nan ainm ‘a’
co-dhùnadh

SELECT *
  FROM employees
 WHERE first_name LIKE '%a';

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad bhon 50mh agus bhon 80mh roinn (department_id) aig a bheil bònas (chan eil an luach sa cholbh commission_pct falamh)
co-dhùnadh

SELECT *
  FROM employees
 WHERE     (department_id = 50 OR department_id = 80)
       AND commission_pct IS NOT NULL;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil co-dhiù dà litir 'n' air an ainm
co-dhùnadh

SELECT *
  FROM employees
 WHERE first_name LIKE '%n%n%';

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil ainm nas fhaide na 4 litrichean
co-dhùnadh

SELECT *
  FROM employees
 WHERE first_name LIKE '%_____%';

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil tuarastal eadar 8000 agus 9000 (in-ghabhalach)
co-dhùnadh

SELECT *
  FROM employees
 WHERE salary BETWEEN 8000 AND 9000;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil an samhla '%' air an ainm
co-dhùnadh

SELECT *
  FROM employees
 WHERE first_name LIKE '%%%' ESCAPE '';

Clàr luchd-obrach. Faigh liosta de na IDan manaidsear uile
co-dhùnadh

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

Clàr luchd-obrach. Faigh liosta den luchd-obrach le na dreuchdan aca ann an cruth: Donald(sh_clerk)
co-dhùnadh

SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;

A’ cleachdadh gnìomhan aon-sreath gus toradh a ghnàthachadh

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil ainm nas fhaide na 10 litrichean
co-dhùnadh

SELECT *
  FROM employees
 WHERE LENGTH (first_name) > 10;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil an litir ‘b’ nan ainm (cùis neo-mhothachail)
co-dhùnadh

SELECT *
  FROM employees
 WHERE INSTR (LOWER (first_name), 'b') > 0;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil co-dhiù dà litir ‘a’ air an ainm
co-dhùnadh

SELECT *
  FROM employees
 WHERE INSTR (LOWER (first_name),'a',1,2) > 0;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil tuarastal iomadaidh de 1000
co-dhùnadh

SELECT *
  FROM employees
 WHERE MOD (salary, 1000) = 0;

Clàr luchd-obrach. Faigh a’ chiad àireamh 3-fhigearach de dh’ àireamh fòn an neach-obrach ma tha an àireamh aige san fhòrmat ХХХ.ХХХ.ХХХХ
co-dhùnadh

SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
  FROM employees
 WHERE phone_number LIKE '___.___.____';

Clàr roinnean. Faigh a’ chiad fhacal bho ainm na roinne dhaibhsan aig a bheil barrachd air aon fhacal san ainm
co-dhùnadh

SELECT department_name,
       SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
           first_word
  FROM departments
 WHERE INSTR (department_name, ' ') > 0;

Clàr luchd-obrach. Faigh ainmean luchd-obrach às aonais a’ chiad agus an litir mu dheireadh san ainm
co-dhùnadh

SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
  FROM employees;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil an litir mu dheireadh san ainm co-ionann ri ‘m’ agus fad an ainm nas motha na 5
co-dhùnadh

SELECT *
  FROM employees
 WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;

Clàr dùbailte. Faigh an ceann-latha Dihaoine sa tighinn
co-dhùnadh

SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad a tha air a bhith aig a’ chompanaidh airson còrr air 17 bliadhna
co-dhùnadh

SELECT *
  FROM employees
 WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil figear mu dheireadh den àireamh fòn neònach agus air a dhèanamh suas de 3 àireamhan air an sgaradh le dot
co-dhùnadh

SELECT *
  FROM employees
 WHERE     MOD (SUBSTR (phone_number, -1), 2) != 0
       AND INSTR (phone_number,'.',1,3) = 0;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad aig a bheil luach job_id an dèidh an t-soidhne '_' aig a bheil co-dhiù 3 caractaran, ach chan eil an luach seo às dèidh an '_' co-ionnan ri 'CLERK'
co-dhùnadh

SELECT *
  FROM employees
 WHERE     LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
       AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad le bhith a' cur a h-uile duine an àite '.' san luach PHONE_NUMBER air '-'
co-dhùnadh

SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
  FROM employees;

A’ cleachdadh gnìomhan tionndaidh agus abairtean cumhach

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad a thàinig a dh’obair air a’ chiad latha den mhìos (sam bith)
co-dhùnadh

SELECT *
  FROM employees
 WHERE TO_CHAR (hire_date, 'DD') = '01';

Clàr luchd-obrach. Faigh liosta den luchd-obrach uile a thàinig a dh'obair ann an 2008
co-dhùnadh

SELECT *
  FROM employees
 WHERE TO_CHAR (hire_date, 'YYYY') = '2008';

DUAL clàr. Seall an ceann-latha a-màireach ann an cruth: Is e amàireach an dàrna latha den Fhaoilleach
co-dhùnadh

SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month')     info
  FROM DUAL;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad agus an ceann-latha tòiseachaidh ann an cruth: 21 Ògmhios, 2007
co-dhùnadh

SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
  FROM employees;

Clàr luchd-obrach. Faigh liosta de luchd-obrach le àrdachadh tuarastail 20%. Seall tuarastal le soidhne dolar
co-dhùnadh

SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
  FROM employees;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad a thàinig a dh'obair sa Ghearran 2007.
co-dhùnadh

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';

DUAL clàr. Às-mhalairt ceann-latha làithreach, + diog, + mionaid, + uair, + latha, + mìos, + bliadhna
co-dhùnadh

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;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad le làn thuarastal (tuarastal + commission_pct(%)) ann an cruth: $24,000.00
co-dhùnadh

SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
  FROM employees;

Clàr luchd-obrach. Faigh liosta den luchd-obrach air fad agus fiosrachadh mu na tha ri fhaighinn de bhònasan tuarastail (Tha / Chan eil)
co-dhùnadh

SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
  FROM employees;

Clàr luchd-obrach. Faigh ìre tuarastail gach neach-obrach: Thathas den bheachd gu bheil nas lugha na 5000 aig ìre ìosal, nas motha na no co-ionann ri 5000 agus nas lugha na 10000 air a mheas mar ìre àbhaisteach, Thathas den bheachd gu bheil nas àirde na no co-ionann ri 10000 aig ìre àrd
co-dhùnadh

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;

Clàr nan dùthchannan. Airson gach dùthaich, seall an roinn anns a bheil e suidhichte: 1-Europe, 2-America, 3-Asia, 4-Africa (gun Join)
co-dhùnadh

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;

Ag aithris air dàta cruinnichte a’ cleachdadh gnìomhan na buidhne

Clàr luchd-obrach. Faigh aithisg le roinn_id leis an tuarastal as ìsle agus as àirde, cinn-latha ruighinn tràth is fadalach agus an àireamh de luchd-obrach. Deasaich a rèir àireamh luchd-obrach (Desc)
co-dhùnadh

  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;

Clàr luchd-obrach. Cia mheud neach-obrach aig a bheil ainmean a’ tòiseachadh leis an aon litir? Deasaich a rèir meud. Na seall ach an fheadhainn far a bheil an àireamh nas motha na 1
co-dhùnadh

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;

Clàr luchd-obrach. Cia mheud neach-obrach a tha ag obair san aon roinn agus a gheibh an aon tuarastal?
co-dhùnadh

SELECT department_id, salary, COUNT (*)
    FROM employees
GROUP BY department_id, salary
  HAVING COUNT (*) > 1;

Clàr luchd-obrach. Faigh cunntas air cia mheud neach-obrach a chaidh fhastadh air gach latha den t-seachdain. Deasaich a rèir meud
co-dhùnadh

SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
    FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;

Clàr luchd-obrach. Luchdaich a-nuas an aithisg air an àireamh de luchd-obrach ann am bliadhna. Deasaich a rèir meud
co-dhùnadh

SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
    FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');

Clàr luchd-obrach. Faigh an àireamh de roinnean aig a bheil luchd-obrach
co-dhùnadh

SELECT COUNT (COUNT (*))     department_count
    FROM employees
   WHERE department_id IS NOT NULL
GROUP BY department_id;

Clàr luchd-obrach. Faigh liosta de department_id le còrr air 30 neach-obrach
co-dhùnadh

  SELECT department_id
    FROM employees
GROUP BY department_id
  HAVING COUNT (*) > 30;

Clàr luchd-obrach. Faigh liosta de roinnean_ids agus tuarastal cuibheasach cruinn luchd-obrach anns gach roinn.
co-dhùnadh

  SELECT department_id, ROUND (AVG (salary)) avg_salary
    FROM employees
GROUP BY department_id;

Clàr nan dùthchannan. Faigh liosta de region_id suim litrichean uile na dùthcha_names anns a bheil còrr is 60
co-dhùnadh

  SELECT region_id
    FROM countries
GROUP BY region_id
  HAVING SUM (LENGTH (country_name)) > 60;

Clàr luchd-obrach. Faigh liosta roinn_id anns a bheil luchd-obrach grunn (> 1) job_id ag obair
co-dhùnadh

  SELECT department_id
    FROM employees
GROUP BY department_id
  HAVING COUNT (DISTINCT job_id) > 1;

Clàr luchd-obrach. Faigh liosta de manager_id aig a bheil an àireamh de fo-roinnean nas motha na 5 agus tha an t-suim de thuarastal gu lèir aig na fo-roinnean aige nas àirde na 50000
co-dhùnadh

  SELECT manager_id
    FROM employees
GROUP BY manager_id
  HAVING COUNT (*) > 5 AND SUM (salary) > 50000;

Clàr luchd-obrach. Faigh liosta de manager_id aig a bheil tuarastal cuibheasach de na fo-oifigearan aige eadar 6000 agus 9000 nach eil a’ faighinn bònasan (tha commission_pct falamh)
co-dhùnadh

  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;

Clàr luchd-obrach. Faigh an tuarastal as àirde bhon a h-uile neach-obrach job_id a tha a’ crìochnachadh leis an fhacal ‘CLERK’
co-dhùnadh

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';

Clàr luchd-obrach. Faigh an tuarastal as àirde am measg tuarastalan cuibheasach na roinne
co-dhùnadh

  SELECT MAX (AVG (salary))
    FROM employees
GROUP BY department_id;

Clàr luchd-obrach. Faigh an àireamh de luchd-obrach leis an aon àireamh de litrichean nan ainm. Aig an aon àm, seall a-mhàin an fheadhainn aig a bheil ainm nas fhaide na 5 agus tha an àireamh de luchd-obrach leis an aon ainm nas àirde na 20. Deasaich a rèir faid ainm
co-dhùnadh

  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);

A’ taisbeanadh dàta bho ioma-chlàran a’ cleachdadh aonadh

Luchd-obrach Clàr, Roinnean, Àiteachan, Dùthchannan, Roinnean. Faigh liosta de roinnean agus an àireamh de luchd-obrach anns gach sgìre
co-dhùnadh

  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;

Luchd-obrach Clàr, Roinnean, Àiteachan, Dùthchannan, Roinnean. Faigh fiosrachadh mionaideach mu gach neach-obrach:
Ciad_ainm, Last_name, Roinn, Iob, Sràid, Dùthaich, Roinn
co-dhùnadh

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);

Clàr luchd-obrach. Seall a h-uile manaidsear aig a bheil barrachd air 6 luchd-obrach
co-dhùnadh

  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;

Clàr luchd-obrach. Seall a h-uile neach-obrach nach bi ag aithris do dhuine sam bith
co-dhùnadh

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;

Clàr luchd-obrach, Job_history. Bidh bòrd an Luchd-obrach a’ stòradh a h-uile neach-obrach. Bidh an clàr Job_history a’ stòradh luchd-obrach a dh’ fhàg a’ chompanaidh. Faigh aithisg air a h-uile neach-obrach agus an inbhe sa chompanaidh (Fastaichte no dh’ fhàg a’ chompanaidh leis a’ cheann-latha fàgail)
Eisimpleir:
ciad_ainm | inbhe
seudan | Dh’ fhàg iad a’ chompanaidh air 31 Dùbhlachd, 2006
Clara | Ag obair an-dràsta
co-dhùnadh

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);

Luchd-obrach Clàr, Roinnean, Àiteachan, Dùthchannan, Roinnean. Faigh liosta den luchd-obrach a tha a’ fuireach san Roinn Eòrpa (region_name)
co-dhùnadh

 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';

Luchd-obrach Clàr, Roinnean. Seall a h-uile roinn le barrachd air luchd-obrach 30
co-dhùnadh

SELECT department_name, COUNT (*)
    FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
  HAVING COUNT (*) > 30;

Luchd-obrach Clàr, Roinnean. Seall a h-uile neach-obrach nach eil ann an roinn sam bith
co-dhùnadh

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;

Luchd-obrach Clàr, Roinnean. Seall a h-uile roinn gun luchd-obrach
co-dhùnadh

SELECT department_name
  FROM employees  e
       RIGHT JOIN departments d ON (e.department_id = d.department_id)
 WHERE first_name IS NULL;

Clàr luchd-obrach. Seall a h-uile neach-obrach aig nach eil fo-oifigearan
co-dhùnadh

SELECT man.first_name
  FROM employees  emp
       RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
 WHERE emp.FIRST_NAME IS NULL;

Luchd-obrach Clàr, Obraichean, Roinnean. Seall luchd-obrach ann an cruth: First_name, Job_title, Department_name.
Eisimpleir:
ciad_ainm | tiotal obrach | Roinn_ainm
Dòmhnall | luingearachd | Clèireach Shipping
co-dhùnadh

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);

Clàr luchd-obrach. Faigh liosta den luchd-obrach aig an d’ fhuair manaidsearan obair ann an 2005, ach aig an aon àm, fhuair an luchd-obrach sin iad fhèin obair ro 2005
co-dhùnadh

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');

Clàr luchd-obrach. Faigh liosta den luchd-obrach aig an d’ fhuair na manaidsearan obair ann am mìos an Fhaoillich de bhliadhna sam bith agus tha fad tiotal obrach an luchd-obrach sin nas motha na 15 caractar
co-dhùnadh

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;

A’ cleachdadh subqueries gus ceistean fhuasgladh

Clàr luchd-obrach. Faigh liosta de luchd-obrach leis an ainm as fhaide.
co-dhùnadh

SELECT *
  FROM employees
 WHERE LENGTH (first_name) =
       (SELECT MAX (LENGTH (first_name)) FROM employees);

Clàr luchd-obrach. Faigh liosta de luchd-obrach le tuarastal nas àirde na tuarastal cuibheasach gach neach-obrach.
co-dhùnadh

SELECT *
  FROM employees
 WHERE salary > (SELECT AVG (salary) FROM employees);

Luchd-obrach Clàr, Roinnean, Àiteachan. Faigh am baile-mòr anns a bheil luchd-obrach a’ cosnadh an ìre as lugha gu h-iomlan.
co-dhùnadh

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);

Clàr luchd-obrach. Faigh liosta den luchd-obrach aig a bheil manaidsear a’ faighinn tuarastal nas motha na 15000.
co-dhùnadh

SELECT *
  FROM employees
 WHERE manager_id IN (SELECT employee_id
                        FROM employees
                       WHERE salary > 15000)

Luchd-obrach Clàr, Roinnean. Seall a h-uile roinn gun luchd-obrach
co-dhùnadh

SELECT *
  FROM departments
 WHERE department_id NOT IN (SELECT department_id
                               FROM employees
                              WHERE department_id IS NOT NULL);

Clàr luchd-obrach. Seall a h-uile neach-obrach nach eil nan manaidsearan
co-dhùnadh

SELECT *
  FROM employees
 WHERE employee_id NOT IN (SELECT manager_id
                             FROM employees
                            WHERE manager_id IS NOT NULL)

Clàr luchd-obrach. Seall a h-uile manaidsear aig a bheil barrachd air 6 luchd-obrach
co-dhùnadh

SELECT *
  FROM employees e
 WHERE (SELECT COUNT (*)
          FROM employees
         WHERE manager_id = e.employee_id) > 6;

Luchd-obrach Clàr, Roinnean. Seall luchd-obrach a tha ag obair anns an roinn IT
co-dhùnadh

SELECT *
  FROM employees
 WHERE department_id = (SELECT department_id
                          FROM departments
                         WHERE department_name = 'IT');

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;

Clàr luchd-obrach. Faigh liosta den luchd-obrach aig an d’ fhuair manaidsearan obair ann an 2005, ach aig an aon àm, fhuair an luchd-obrach sin iad fhèin obair ro 2005
co-dhùnadh

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');

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;

Tha sin uile airson a-nis.

Tha mi an dòchas gun robh na gnìomhan inntinneach agus brosnachail.
Cuiridh mi ris an liosta seo cho mòr 'sa ghabhas.
Bidh mi cuideachd toilichte le beachdan agus molaidhean sam bith.

PS: Ma thig cuideigin suas le gnìomh inntinneach air SELECT, sgrìobh na beachdan, cuiridh mi ris an liosta e.

Tapadh leibh.

Source: www.habr.com

SQL. Tòimhseachain spòrsail

gnìomhan

Cuir beachd ann freagairt a chur dheth