🥇SQL ntawv. Lom zem ua si

Nyob zoo Habr!

Tau ntau tshaj 3 xyoos kuv tau qhia SQL hauv ntau lub chaw cob qhia, thiab ib qho ntawm kuv qhov kev soj ntsuam yog tias cov tub ntxhais kawm paub thiab nkag siab SQL zoo dua yog tias lawv tau txais ib txoj haujlwm, thiab tsis yog tham txog qhov muaj peev xwm thiab cov hauv paus ntsiab lus.

Hauv tsab xov xwm no, kuv yuav qhia rau koj txog kuv cov npe ntawm cov haujlwm uas kuv muab rau cov tub ntxhais kawm ua homework thiab uas peb ua ntau hom kev tawm tswv yim, uas ua rau kom nkag siab tob thiab meej ntawm SQL.

SQL (ˈɛsˈkjuˈɛl; eng. structured query language) yog ib hom lus tshaj tawm uas siv los tsim, hloov kho thiab tswj cov ntaub ntawv hauv kev sib raug zoo database tswj los ntawm qhov tsim nyog database tswj system. Nyeem ntxiv…

Koj tuaj yeem nyeem txog SQL los ntawm qhov sib txawv qhov chaw.
Kab lus no tsis yog los qhia koj SQL los ntawm kos.

Yog li cia peb mus.

Peb yuav siv qhov paub zoo HR tswvyim hauv Oracle nrog nws cov ntxhuav (Nyeem ntxiv):

Kuv nco ntsoov tias peb yuav txiav txim siab tsuas yog cov haujlwm ntawm SELECT. Tsis muaj cov haujlwm ntawm DML thiab DDL.

paub tab

Kev txwv thiab cais cov ntaub ntawv

Cov neeg ua haujlwm rooj. Tau txais cov npe nrog cov ntaub ntawv hais txog txhua tus neeg ua haujlwm
kev txiav txim siab

SELECT * FROM employees

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm hu ua 'David'
kev txiav txim siab

SELECT *
  FROM employees
 WHERE first_name = 'David';

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm nrog job_id sib npaug rau 'IT_PROG'
kev txiav txim siab

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm los ntawm 50 lub tuam tsev (department_id) nrog cov nyiaj hli (nyiaj hli) ntau dua 4000
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm los ntawm 20th thiab los ntawm 30th department (department_id)
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws tsab ntawv kawg hauv lawv lub npe yog 'a'
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm los ntawm 50th thiab los ntawm 80th department (department_id) uas muaj nyiaj ntxiv (tus nqi hauv cov commission_pct kem tsis yog khoob)
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws lub npe muaj tsawg kawg 2 tsab ntawv 'n'
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws lub npe ntev dua 4 tsab ntawv
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws cov nyiaj hli nyob nruab nrab ntawm 8000 thiab 9000 (nrog)
kev txiav txim siab

SELECT *
  FROM employees
 WHERE salary BETWEEN 8000 AND 9000;

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws lub npe muaj cov cim '%'
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe ntawm txhua tus thawj tswj ID
kev txiav txim siab

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm nrog lawv txoj haujlwm hauv hom: Donald(sh_clerk)
kev txiav txim siab

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

Siv Tib-Law Functions los Customize Output

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws lub npe ntev dua 10 tsab ntawv
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas muaj tsab ntawv 'b' hauv lawv lub npe (cov ntaub ntawv tsis txaus ntseeg)
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws lub npe muaj tsawg kawg 2 tsab ntawv 'a'
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws cov nyiaj hli yog ntau ntawm 1000
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais thawj 3 tus lej ntawm tus neeg ua haujlwm tus lej xov tooj yog tias nws tus lej nyob hauv hom ХХХ.ХХХ.ХХХХ
kev txiav txim siab

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

Departments rooj. Tau txais thawj lo lus los ntawm lub tuam tsev lub npe rau cov uas muaj ntau tshaj ib lo lus hauv lub npe
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe neeg ua haujlwm yam tsis muaj thawj thiab tsab ntawv kawg hauv lub npe
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws tsab ntawv kawg hauv lub npe sib npaug rau 'm' thiab qhov ntev ntawm lub npe yog ntau dua 5
kev txiav txim siab

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

Ob lub rooj. Tau txais hnub Friday tom ntej
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas tau nrog lub tuam txhab rau ntau dua 17 xyoo
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas nws tus lej kawg ntawm tus lej xov tooj yog khib thiab muaj 3 tus lej sib cais los ntawm qhov chaw
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe ntawm txhua tus neeg ua haujlwm uas nws tus nqi job_id tom qab kos npe '_' muaj tsawg kawg yog 3 tus cim, tab sis tus nqi no tom qab '_' tsis sib npaug rau 'CLERK'
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm los ntawm kev hloov tag nrho '.' hauv PHONE_NUMBER tus nqi ntawm '-'
kev txiav txim siab

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

Siv cov kev hloov pauv hloov pauv thiab cov ntsiab lus nthuav dav

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas tuaj ua haujlwm thawj hnub ntawm lub hli (ib qho twg)
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas tuaj ua haujlwm xyoo 2008
kev txiav txim siab

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

DUAL rooj. Qhia tag kis hnub nyob rau hauv hom: Tag kis yog Hnub Ob ntawm Lub Ib Hlis
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe ntawm txhua tus neeg ua haujlwm thiab lawv hnub pib ua haujlwm hauv hom: Lub Rau Hli 21, 2007
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm nrog cov nyiaj hli nce ntxiv los ntawm 20%. Qhia cov nyiaj hli nrog nyiaj daus las kos npe
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm uas tuaj ua haujlwm thaum Lub Ob Hlis 2007.
kev txiav txim siab

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 rooj. Export hnub tim tam sim no, + thib ob, + feeb, + teev, + hnub, + hli, + xyoo
kev txiav txim siab

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;

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm txhua tus neeg ua haujlwm nrog cov nyiaj hli tag nrho (nyiaj hli + commission_pct(%)) hauv hom: $ 24,000.00
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe ntawm txhua tus neeg ua haujlwm thiab cov ntaub ntawv hais txog qhov muaj cov nyiaj tau los (Yog / Tsis yog)
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov nyiaj hli ntawm txhua tus neeg ua haujlwm: Tsawg dua 5000 suav tias yog qib qis, Ntau dua lossis sib npaug rau 5000 thiab tsawg dua 10000 suav tias yog qib Ib txwm, Ntau dua thiab sib npaug rau 10000 suav tias yog qib siab
kev txiav txim siab

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;

Lub teb chaws rooj. Rau txhua lub tebchaws, qhia thaj chaw uas nws nyob: 1-Europe, 2-America, 3-Asia, 4-Africa (tsis koom nrog)
kev txiav txim siab

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;

Qhia txog cov ntaub ntawv sib sau ua ke siv pab pawg ua haujlwm

Cov neeg ua haujlwm rooj. Tau txais daim ntawv tshaj tawm los ntawm department_id nrog cov nyiaj hli tsawg kawg thiab siab tshaj plaws, hnub tuaj txog ntxov thiab lig thiab cov neeg ua haujlwm. Xaiv los ntawm tus lej ntawm cov neeg ua haujlwm (desc)
kev txiav txim siab

  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;

Cov neeg ua haujlwm rooj. Muaj pes tsawg tus neeg ua haujlwm uas nws lub npe pib nrog tib tsab ntawv? Cais los ntawm qhov ntau. Qhia tsuas yog cov uas tus lej ntau dua 1
kev txiav txim siab

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;

Cov neeg ua haujlwm rooj. Muaj pes tsawg tus neeg ua haujlwm ua haujlwm hauv tib lub tuam tsev thiab tau txais cov nyiaj hli qub?
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv qhia txog pes tsawg tus neeg ua haujlwm raug ntiav nyob rau txhua hnub ntawm lub lim tiam. Cais los ntawm qhov ntau
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv qhia txog pes tsawg tus neeg ua haujlwm raug ntiav los ntawm lub xyoo. Cais los ntawm qhov ntau
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov naj npawb ntawm cov chaw haujlwm uas muaj cov neeg ua haujlwm
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe department_id nrog ntau dua 30 tus neeg ua haujlwm
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe department_ids thiab cov nyiaj hli nruab nrab ntawm cov neeg ua haujlwm hauv txhua lub tuam tsev.
kev txiav txim siab

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

Lub teb chaws rooj. Tau txais ib daim ntawv teev npe ntawm region_id sum ntawm tag nrho cov tsiaj ntawv ntawm tag nrho cov country_names uas ntau tshaj 60
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe department_id uas cov neeg ua haujlwm ntawm ob peb (> 1) job_id ua haujlwm
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe manager_id uas nws tus lej ntawm cov subordinates ntau dua 5 thiab tag nrho cov nyiaj hli ntawm nws cov subordinates ntau dua 50000
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais ib daim ntawv teev npe manager_id uas nws cov nyiaj hli nruab nrab ntawm tag nrho nws cov subordinates nyob nruab nrab ntawm 6000 thiab 9000 uas tsis tau txais nyiaj tshwj xeeb (commission_pct yog khoob)
kev txiav txim siab

  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;

Cov neeg ua haujlwm rooj. Tau txais cov nyiaj hli siab tshaj plaws los ntawm txhua tus neeg ua haujlwm job_id uas xaus nrog lo lus 'CLERK'
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov nyiaj hli siab tshaj plaws ntawm tag nrho cov nyiaj hli nruab nrab rau chav haujlwm
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais tus lej ntawm cov neeg ua haujlwm nrog tib tus lej ntawm cov ntawv hauv lawv lub npe. Nyob rau tib lub sijhawm, tsuas yog qhia cov neeg uas nws lub npe ntev dua 5 thiab cov neeg ua haujlwm nrog tib lub npe yog ntau dua 20. Ntaus los ntawm lub npe ntev
kev txiav txim siab

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

Tso cov ntaub ntawv los ntawm ntau lub rooj siv koom nrog

Table neeg ua hauj lwm, departments, qhov chaw, lub teb chaws, cheeb tsam. Tau txais cov npe ntawm cheeb tsam thiab tus naj npawb ntawm cov neeg ua haujlwm hauv txhua cheeb tsam
kev txiav txim siab

  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;

Table neeg ua hauj lwm, departments, qhov chaw, lub teb chaws, cheeb tsam. Tau txais cov ncauj lus kom ntxaws txog txhua tus neeg ua haujlwm:
Thawj_name, Last_name, Department, Txoj Haujlwm, Txoj Kev, Lub Tebchaws, Thaj Chaw
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Qhia tag nrho cov thawj coj uas muaj ntau tshaj 6 tus neeg ua haujlwm
kev txiav txim siab

  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;

Cov neeg ua haujlwm rooj. Qhia tag nrho cov neeg ua haujlwm uas tsis qhia rau leej twg
kev txiav txim siab

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;

Cov neeg ua haujlwm rooj, Job_history. Lub Rooj Ua Haujlwm khaws txhua tus neeg ua haujlwm. Lub rooj Job_history khaws cov neeg ua haujlwm uas tawm hauv lub tuam txhab. Tau txais daim ntawv qhia txog txhua tus neeg ua haujlwm thiab lawv cov xwm txheej hauv lub tuam txhab (ua haujlwm lossis tawm hauv lub tuam txhab nrog hnub tawm mus)
Piv Txwv:
thawj_name | xwm txheej
jennifer | Tawm ntawm lub tuam txhab thaum lub Kaum Ob Hlis 31, 2006
Clara | Ua haujlwm tam sim no
kev txiav txim siab

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

Table neeg ua hauj lwm, departments, qhov chaw, lub teb chaws, cheeb tsam. Tau txais cov npe ntawm cov neeg ua haujlwm nyob hauv Europe (region_name)
kev txiav txim siab

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

Cov neeg ua haujlwm rooj, Departments. Qhia tag nrho cov chaw haujlwm nrog ntau tshaj 30 tus neeg ua haujlwm
kev txiav txim siab

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

Cov neeg ua hauj lwm rooj, Departments. Qhia tag nrho cov neeg ua haujlwm uas tsis nyob hauv ib chav haujlwm
kev txiav txim siab

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;

Cov neeg ua hauj lwm rooj, Departments. Qhia tag nrho cov chaw haujlwm uas tsis muaj neeg ua haujlwm
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Qhia tag nrho cov neeg ua haujlwm uas tsis muaj subordinates
kev txiav txim siab

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

Cov neeg ua hauj lwm rooj, hauj lwm, departments. Qhia cov neeg ua haujlwm hauv hom: First_name, Job_title, Department_name.
Piv Txwv:
thawj_name | txoj hauj lwm npe | Department_name
Donald | shipping | Clerk Shipping
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm uas cov thawj coj tau txais txoj haujlwm xyoo 2005, tab sis tib lub sijhawm, cov neeg ua haujlwm no lawv tus kheej tau txais haujlwm ua ntej xyoo 2005.
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm uas nws cov thawj tswj hwm tau txais txoj haujlwm nyob rau lub Ib Hlis Lub Ib Hlis ntawm ib xyoos twg thiab qhov ntev ntawm txoj haujlwm_title ntawm cov neeg ua haujlwm no muaj ntau dua 15 tus cim
kev txiav txim siab

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;

Siv Subqueries los daws cov lus nug

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm nrog lub npe ntev tshaj plaws.
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm nrog cov nyiaj hli ntau dua li cov nyiaj hli nruab nrab ntawm txhua tus neeg ua haujlwm.
kev txiav txim siab

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

Table neeg ua hauj lwm, departments, qhov chaw. Tau lub nroog uas cov neeg ua haujlwm khwv tau tsawg kawg nkaus.
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Tau txais cov npe ntawm cov neeg ua haujlwm uas tus thawj tswj hwm tau txais nyiaj hli ntau dua 15000.
kev txiav txim siab

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

Cov neeg ua hauj lwm rooj, Departments. Qhia tag nrho cov chaw haujlwm uas tsis muaj neeg ua haujlwm
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Qhia txhua tus neeg ua haujlwm uas tsis yog tus thawj coj
kev txiav txim siab

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

Cov neeg ua haujlwm rooj. Qhia tag nrho cov thawj coj uas muaj ntau tshaj 6 tus neeg ua haujlwm
kev txiav txim siab

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

Cov neeg ua hauj lwm rooj, Departments. Qhia cov neeg ua haujlwm uas ua haujlwm hauv IT department
kev txiav txim siab

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;

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;

Qhov ntawd yog tag nrho rau tam sim no.

Kuv vam tias cov dej num tau nthuav thiab zoo siab.
Kuv yuav ntxiv rau daim ntawv no kom ntau li ntau tau.
Kuv kuj yuav zoo siab rau cov lus thiab cov lus qhia.

PS: Yog hais tias ib tug neeg tuaj nrog ib tug nthuav txoj hauj lwm ntawm SELECT, sau nyob rau hauv cov lus, kuv yuav ntxiv rau hauv daim ntawv.

Ua tsaug.

Tau qhov twg los: www.hab.com

SQL ntawv. Kev lom zem puzzles

paub tab

Ntxiv ib saib Отменить ответ