🥇SQL. Zvinovaraidza puzzles

Mhoro, Habr!

Kweanopfuura makore 3 ikozvino ndanga ndichidzidzisa SQL munzvimbo dzakasiyana-siyana dzekudzidzira, uye chimwe chezvandinoona ndechekuti vadzidzi vatenzi uye vanonzwisisa SQL zviri nani kana vakapihwa basa, uye kwete kungoudzwa nezvemikana uye nheyo dzedzidziso.

Muchikamu chino, ini ndichagoverana newe mazita angu ematambudziko andinopa kuvadzidzi sebasa rechikoro uye patinenge tichiita zvakasiyana-siyana zvepfungwa, izvo zvinotungamirira kunzwisiso yakadzika uye yakajeka yeSQL.

SQL (ˈɛsˈkjuˈɛl; Chirungu chakarongeka query mutauro) mutauro unozivisa wehurongwa unoshandiswa kugadzira, kugadzirisa uye kubata data mudhatabhesi rehukama rinotungamirwa neyakakodzera dhatabhesi manejimendi system. Dzidza zvakawanda

Unogona kuverenga nezve SQL kubva kwakasiyana zvinyorwa.
Ichi chinyorwa hachina kuitirwa kukudzidzisa SQL kubva kutanga.

Saka handei.

Tichashandisa anozivikanwa HR mufananidzo muOracle nematafura aro (More):

Ndinocherechedza kuti tichangofunga nezve SELECT mabasa. Iko hakuna DML kana DDL mabasa pano.

mabasa

Kurambidza uye Kuronga Data

Tafura yevashandi. Wana rondedzero ine ruzivo nezvevashandi vese
chisarudzo

SELECT * FROM employees

Tafura yevashandi. Tora runyoro rwevashandi vese vanonzi 'David'
chisarudzo

SELECT *
  FROM employees
 WHERE first_name = 'David';

Tafura yevashandi. Wana mazita evashandi vese vane job_id yakaenzana ne 'IT_PROG'
chisarudzo

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Tafura yevashandi. Tora runyoro rwevashandi vese kubva kubazi rechimakumi mashanu (department_id) nemuhoro (muhoro) unopfuura zviuru mazana mana.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese kubva ku20th uye 30th department (department_id)
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane tsamba yekupedzisira muzita ravo 'a'
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese kubva kudhipatimendi rechi50 uye rechi80 (department_id) vane bhonasi (ukoshi huri mukomisheni_pct column haina chinhu)
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane mazita ane angangoita mabhii maviri 'n'
chisarudzo

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

Tafura yevashandi. Wana runyoro rwevashandi vese vane mazita akareba kupfuura mavara mana
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane muhoro uri pakati pe8000 kusvika 9000 (inosanganisira)
chisarudzo

SELECT *
  FROM employees
 WHERE salary BETWEEN 8000 AND 9000;

Tafura yevashandi. Tora runyoro rwevashandi vese vane zita rine chiratidzo '%'
chisarudzo

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

Tafura yevashandi. Tora runyoro rweese maneja ID
chisarudzo

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

Tafura yevashandi. Tora runyoro rwevashandi vane zvinzvimbo zvavo mufomati: Donald(sh_clerk)
chisarudzo

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

Kushandisa Single-Row Mabasa Kugadzirisa Kubuda

Tafura yevashandi. Wana runyoro rwevashandi vese vane mazita akareba kupfuura mavara mana
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane tsamba 'b' muzita ravo (nyaya isinganzwisisike)
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane mazita ane angangoita mabhii maviri 'a'
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane muhoro wakawanda we1000
chisarudzo

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

Tafura yevashandi. Wana nhamba yekutanga ine manhamba matatu yenhamba dzerunhare dzemushandi kana nhamba yake iri muchimiro XXX.XXX.XXXX
chisarudzo

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

Tafura yemadhipatimendi. Wana izwi rekutanga kubva kuzita redhipatimendi kune avo vane mazwi anopfuura rimwe muzita
chisarudzo

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

Tafura yevashandi. Wana mazita evashandi asina tsamba yekutanga neyekupedzisira muzita
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane tsamba yekupedzisira muzita ravo 'm' uye vane zita rakareba kupfuura 5
chisarudzo

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

Tafura Dual. Tora zuva reChishanu rinotevera
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vakashandira kambani kwemakore anopfuura 17
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vane nhamba yekupedzisira yenhamba yavo yefoni isinganzwisisike uye ine nhamba nhatu dzakapatsanurwa nedoti.
chisarudzo

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

Tafura yevashandi. Wana mazita evashandi vese vane job_id value mushure mechiratidzo che'_' chine mavara angangoita matatu, asi kukosha uku mushure me'_' hakuenzane ne'CLERK'
chisarudzo

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

Tafura yevashandi. Wana mazita evashandi vese nekutsiva vese '.' mumutengo wePHONE_NUMBER pa'-'
chisarudzo

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

Kushandisa Shanduko Mabasa uye Mamiriro Ekutaura

Tafura yevashandi. Tora runyoro rwevashandi vese vakauya kuzoshanda pazuva rekutanga remwedzi (chero)
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vakauya kuzoshanda muna 2008
chisarudzo

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

Tafura DUAL. Ratidza zuva remangwana mufomati: Mangwana izuva reChipiri raNdira
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese uye zuva iro mushandi wega wega akasvika kubasa mufomati: 21st yaChikumi, 2007.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vane mihoro yakawedzerwa ne20%. Ratidza muhoro nechiratidzo chedhora
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese vakatanga kushanda muna Kukadzi 2007.
chisarudzo

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

Tafura DUAL. Budisa zuva razvino, + rechipiri, + miniti, + awa, + zuva, + mwedzi, + gore
chisarudzo

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;

Tafura yevashandi. Tora runyoro rwevashandi vese vane mihoro izere (muhoro + commission_pct(%)) mufomati: $24,000.00
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vese uye ruzivo nezve kuwanikwa kwemabhonasi emuhoro (Hongu / Kwete)
chisarudzo

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

Tafura yevashandi. Tora mwero wemuhoro wemushandi wega wega: isingasviki 5000 inonzi yakaderera nhanho, Yakakura kupfuura kana yakaenzana ne5000 uye isingasviki 10000 inoonekwa seyakajairika level, Kupfuura kana kuenzana ne10000 kunoonekwa seyakakwirira level.
chisarudzo

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;

Tafura Nyika. Kune imwe neimwe nyika, ratidza nharaunda yairi: 1-Europe, 2-America, 3-Asia, 4-Africa (isina Join)
chisarudzo

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;

Kushuma Aggregated Data Uchishandisa Mabasa Eboka

Tafura yevashandi. Gamuchira mushumo nedhipatimendi_id nemari shoma uye yakawanda, mazuva ekutanga uye ekupedzisira ekusvika kubasa uye nhamba yevashandi. Ronga nenhamba yevashandi (kudzika)
chisarudzo

  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;

Tafura yevashandi. Vangani vashandi vane mazita anotanga nebhii rimwechete? Ronga neuwandu. Ratidza chete ayo ane huwandu hwakakura kupfuura 1
chisarudzo

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;

Tafura yevashandi. Vangani vashandi vanoshanda mudhipatimendi rimwechete uye vanogamuchira muhoro wakafanana?
chisarudzo

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

Tafura yevashandi. Wana mushumo wekuti vangani vashandi vakahaiwa pazuva rega rega revhiki. Ronga neuwandu
chisarudzo

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

Tafura yevashandi. Wana mushumo wekuti vangani vashandi vakapinzwa pagore. Ronga neuwandu
chisarudzo

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

Tafura yevashandi. Wana nhamba yemadhipatimendi ane vashandi
chisarudzo

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

Tafura yevashandi. Tora runyoro rwemadhipatimendi_id ane vashandi vanopfuura makumi matatu
chisarudzo

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

Tafura yevashandi. Tora runyoro rwedhipatimendi_id uye yakatenderedzwa avhareji muhoro wevashandi mudhipatimendi rega rega.
chisarudzo

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

Tafura Nyika. Wana rondedzero yedunhu_id nhamba yemabhii ese enyika_mazita mune anodarika makumi matanhatu
chisarudzo

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

Tafura yevashandi. Tora runyoro rwedhipatimendi_id umo vashandi vakati wandei (>1) job_ids vanoshanda
chisarudzo

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

Tafura yevashandi. Tora runyoro rwemaneja_ids ane nhamba yevari pasi inopfuura 5 uye huwandu hwemihoro yevose vari pasi pake hunodarika zviuru makumi mashanu.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwemaneja_ids ane avhareji muhoro wevose vari pasi pake ari pakati pe6000 kusvika 9000 uye asingagamuchire mabhonasi (commission_pct haina chinhu)
chisarudzo

  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;

Tafura yevashandi. Wana muhoro wepamusoro kubva kune vese vashandi job_id inopera neizwi rekuti 'CLERK'
chisarudzo

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

Tafura yevashandi. Tora iyo yakanyanya muhoro pakati peavhareji mihoro yedhipatimendi
chisarudzo

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

Tafura yevashandi. Wana nhamba yevashandi vane nhamba yakafanana yemavara muzita ravo. Panguva imwecheteyo, ratidza chete avo vane mazita akareba kupfuura 5 uye nhamba yevashandi vane zita rimwe chete inopfuura 20. Ronga nemazita ekureba
chisarudzo

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

Kuratidza Dhata kubva kuMatafura akawanda Uchishandisa Majoini

Tafura Vashandi, Madhipatimendi, Nzvimbo, Nyika, Matunhu. Tora runyoro rwematunhu uye nhamba yevashandi mudunhu rega rega
chisarudzo

  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;

Tafura Vashandi, Madhipatimendi, Nzvimbo, Nyika, Matunhu. Wana ruzivo rwakadzama nezve mushandi wega wega:
First_name, Last_name, Department, Job, Street, Country, Region
chisarudzo

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

Tafura yevashandi. Ratidza maneja ese ane vashandi vanopfuura 6 vari pasi pavo
chisarudzo

  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;

Tafura yevashandi. Ratidza vashandi vese vasingamhan'ara kune chero munhu
chisarudzo

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;

Tafura Vashandi, Job_history. Tafura yeVashandi inochengeta vashandi vese. Tafura yeJobho_history inochengetera vashandi vakasiya kambani. Tora shumo nezvevashandi vese uye chimiro chavo mukambani (Inoshanda kana kusiya kambani ine zuva rekusimuka)
Muenzaniso:
first_name | chimiro
Jennifer | Akasiya kambani pa31 Zvita, 2006
Clara | Parizvino Kushanda
chisarudzo

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

Tafura Vashandi, Madhipatimendi, Nzvimbo, Nyika, Matunhu. Tora runyoro rwevashandi vanogara muEurope (region_name)
chisarudzo

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

Tafura Vashandi, Madhipatimendi. Ratidza madhipatimendi ese ane vashandi vanopfuura makumi matatu
chisarudzo

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

Tafura Vashandi, Madhipatimendi. Ratidza vashandi vese vasiri mune chero dhipatimendi
chisarudzo

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;

Tafura Vashandi, Madhipatimendi. Ratidza madhipatimendi ese asina vashandi
chisarudzo

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

Tafura yevashandi. Ratidza vashandi vese vasina munhu ari pasi pavo
chisarudzo

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

Tafura Vashandi, Mabasa, Madhipatimendi. Ratidza vashandi mufomati: First_name, Job_title, Department_name.
Muenzaniso:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vane mamaneja vakawana basa muna 2005, asi panguva imwe chete ava vashandi pachavo vakawana basa gore ra2005 risati rasvika.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vane maneja vakawana basa muna Ndira wegore ripi zvaro uye hurefu hwebasa revashandi ava hunodarika mavara gumi nemashanu.
chisarudzo

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;

Kushandisa Subqueries Kugadzirisa Mibvunzo

Tafura yevashandi. Tora runyoro rwevashandi vane zita rakareba kwazvo.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vane muhoro wakakura kupfuura wepakati muhoro wevashandi vese.
chisarudzo

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

Vashandi, Madhipatimendi, Nzvimbo tafura. Tora guta iro vashandi vese vanowana zvishoma.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vane maneja anotambira muhoro unodarika zviuru gumi nezvishanu.
chisarudzo

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

Tafura Vashandi, Madhipatimendi. Ratidza madhipatimendi ese asina vashandi
chisarudzo

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

Tafura yevashandi. Ratidza vashandi vese vasiri mamaneja
chisarudzo

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

Tafura yevashandi. Ratidza maneja ese ane vashandi vanopfuura 6 vari pasi pavo
chisarudzo

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

Tafura Vashandi, Madhipatimendi. Ratidza vashandi vanoshanda mudhipatimendi reIT
chisarudzo

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;

Tafura yevashandi. Tora runyoro rwevashandi vane mamaneja vakawana basa muna 2005, asi panguva imwe chete ava vashandi pachavo vakawana basa gore ra2005 risati rasvika.
chisarudzo

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

Tafura yevashandi. Tora runyoro rwevashandi vane maneja vakawana basa muna Ndira wegore ripi zvaro uye hurefu hwebasa revashandi ava hunodarika mavara gumi nemashanu.
chisarudzo

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;

Ndizvo zvose ikozvino.

Ndinovimba kuti mabasa acho ainakidza uye ainakidza.
Ini ndichawedzera kune iyi runyorwa rwemabasa zvakanyanya sezvinobvira.
Iniwo ndichafara kugamuchira chero mhinduro uye mazano.

PS: Kana mumwe munhu akauya nebasa rinonakidza SARUDZA, nyora mumashoko uye ini ndichawedzera kune iyo rondedzero.

Спасибо.

Source: www.habr.com

SQL. Mapikicha anovaraidza

mabasa

Voeg kukanzura mhinduro