🥇SQL. Qiziqarli boshqotirmalar

Salom Xabr!

3 yildan ortiq vaqt davomida turli o‘quv markazlarida SQL tilidan dars berib kelaman va bir kuzatishim shundan iboratki, talabalar SQL tilini faqat imkoniyatlar va nazariy asoslar haqida gapirmasdan, ularga topshiriq berilsa yaxshi o‘zlashtiradilar va tushunadilar.

Ushbu maqolada men talabalarga uy vazifasi sifatida beradigan va biz SQLni chuqur va aniq tushunishga olib keladigan turli xil aqliy hujumlarni o'tkazadigan vazifalarim ro'yxatini siz bilan baham ko'raman.

SQL (ˈɛsˈkjuˈɛl; inglizcha tuzilgan so'rovlar tili) - tegishli ma'lumotlar bazasini boshqarish tizimi tomonidan boshqariladigan relyatsion ma'lumotlar bazasidagi ma'lumotlarni yaratish, o'zgartirish va boshqarish uchun ishlatiladigan deklarativ dasturlash tili. Ko'proq ...

SQL haqida turli xil ma'lumotlardan o'qishingiz mumkin manbalar.
Ushbu maqola sizga SQLni noldan o'rgatish uchun mo'ljallanmagan.

Keling, boraylik.

Biz taniqlilardan foydalanamiz HR sxemasi jadvallari bilan Oracle da (Nizom):

Shuni ta'kidlash kerakki, biz faqat SELECTdagi vazifalarni ko'rib chiqamiz. DML va DDL da hech qanday vazifa yo'q.

vazifalar

Ma'lumotlarni cheklash va saralash

Xodimlar jadvali. Barcha xodimlar to'g'risidagi ma'lumotlar ro'yxatini oling
qaror

SELECT * FROM employees

Xodimlar jadvali. "David" ismli barcha xodimlar ro'yxatini oling
qaror

SELECT *
  FROM employees
 WHERE first_name = 'David';

Xodimlar jadvali. Ish_identifikatori “IT_PROG”ga teng boʻlgan barcha xodimlar roʻyxatini oling
qaror

SELECT *
  FROM employees
 WHERE job_id = 'IT_PROG'

Xodimlar jadvali. Ish haqi (ish haqi) 50 dan yuqori bo'lgan 4000-bo'limdan (bo'lim_id) barcha xodimlar ro'yxatini oling
qaror

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

Xodimlar jadvali. 20 va 30-bo'limdan barcha xodimlar ro'yxatini oling (department_id)
qaror

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

Xodimlar jadvali. Ismidagi oxirgi harfi "a" bo'lgan barcha xodimlar ro'yxatini oling
qaror

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

Xodimlar jadvali. 50-chi va 80-bo'limdan (department_id) bonusga ega bo'lgan barcha xodimlarning ro'yxatini oling (komissiya_pct ustunidagi qiymat bo'sh emas)
qaror

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

Xodimlar jadvali. Nomi kamida 2 ta "n" harfidan iborat bo'lgan barcha xodimlar ro'yxatini oling.
qaror

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

Xodimlar jadvali. Ismi 4 harfdan ortiq bo'lgan barcha xodimlar ro'yxatini oling
qaror

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

Xodimlar jadvali. Ish haqi 8000 dan 9000 gacha (shu jumladan) bo'lgan barcha xodimlarning ro'yxatini oling
qaror

SELECT *
  FROM employees
 WHERE salary BETWEEN 8000 AND 9000;

Xodimlar jadvali. Nomida "%" belgisi bo'lgan barcha xodimlar ro'yxatini oling
qaror

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

Xodimlar jadvali. Barcha menejer identifikatorlari ro'yxatini oling
qaror

SELECT DISTINCT manager_id
  FROM employees
 WHERE manager_id IS NOT NULL;

Xodimlar jadvali. Xodimlar ro'yxatini ularning lavozimlari bilan quyidagi formatda oling: Donald(sh_clerk)
qaror

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

Chiqishni moslashtirish uchun bir qatorli funksiyalardan foydalanish

Xodimlar jadvali. Ismi 10 harfdan ortiq bo'lgan barcha xodimlar ro'yxatini oling
qaror

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

Xodimlar jadvali. Nomida "b" harfi bo'lgan barcha xodimlarning ro'yxatini oling (katta harflar hisobga olinmaydi)
qaror

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

Xodimlar jadvali. Nomi kamida 2 ta "a" harfidan iborat bo'lgan barcha xodimlar ro'yxatini oling.
qaror

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

Xodimlar jadvali. Ish haqi 1000 ga karrali barcha xodimlarning ro'yxatini oling
qaror

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

Xodimlar jadvali. Xodimning telefon raqamining birinchi 3 xonali raqamini oling, agar uning raqami XXX.XX.XX.XXXXX formatida bo'lsa.
qaror

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

Bo'limlar jadvali. Nomda bir nechta so'z bo'lganlar uchun bo'lim nomidan birinchi so'zni oling
qaror

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

Xodimlar jadvali. Ismning birinchi va oxirgi harfisiz xodimlarning ismlarini oling
qaror

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

Xodimlar jadvali. Nomidagi oxirgi harfi "m" ga teng va ism uzunligi 5 dan katta bo'lgan barcha xodimlar ro'yxatini oling.
qaror

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

Ikkilik stol. Keyingi juma sanasini oling
qaror

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

Xodimlar jadvali. Kompaniyada 17 yildan ortiq ishlagan barcha xodimlar ro'yxatini oling
qaror

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

Xodimlar jadvali. Telefon raqamining oxirgi raqami toq boʻlgan va nuqta bilan ajratilgan 3 ta raqamdan iborat boʻlgan barcha xodimlar roʻyxatini oling.
qaror

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

Xodimlar jadvali. '_' belgisidan keyin job_id qiymati kamida 3 ta belgidan iborat bo'lgan barcha xodimlar ro'yxatini oling, lekin '_' belgisidan keyin bu qiymat 'XOTOBAT' bilan teng emas
qaror

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

Xodimlar jadvali. PHONE_NUMBER qiymatidagi barcha "." ni almashtirish orqali barcha xodimlar ro'yxatini oling '-' da
qaror

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

Konvertatsiya funksiyalari va shartli ifodalardan foydalanish

Xodimlar jadvali. Oyning birinchi kunida ishga kelgan barcha xodimlarning ro'yxatini oling (har qanday)
qaror

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

Xodimlar jadvali. 2008 yilda ishga kelgan barcha xodimlarning ro'yxatini oling
qaror

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

DUAL stol. Ertangi sanani formatda ko'rsating: Ertaga yanvarning ikkinchi kuni
qaror

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

Xodimlar jadvali. Barcha xodimlar ro'yxatini va ularning boshlanish sanasini quyidagi formatda oling: 21 yil 2007 iyun
qaror

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

Xodimlar jadvali. Ish haqi 20% ga oshirilgan xodimlar ro'yxatini oling. Dollar belgisi bilan ish haqini ko'rsatish
qaror

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

Xodimlar jadvali. 2007 yil fevral oyida ishga kelgan barcha xodimlar ro'yxatini oling.
qaror

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 stol. Joriy sana, + soniya, + daqiqa, + soat, + kun, + oy, + yilni eksport qiling
qaror

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;

Xodimlar jadvali. To'liq ish haqi (ish haqi + komissiya_pct(%)) bo'lgan barcha xodimlar ro'yxatini quyidagi formatda oling: $24,000.00
qaror

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

Xodimlar jadvali. Barcha xodimlar ro'yxatini va ish haqi bo'yicha bonuslar mavjudligi to'g'risida ma'lumot oling (Ha / Yo'q)
qaror

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

Xodimlar jadvali. Har bir xodimning ish haqi darajasini oling: 5000 dan kam past daraja deb hisoblanadi, 5000 dan katta yoki unga teng va 10000 dan kam normal daraja, 10000 dan katta yoki unga teng yuqori daraja deb hisoblanadi
qaror

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;

Mamlakatlar jadvali. Har bir mamlakat uchun u joylashgan mintaqani ko'rsating: 1-Evropa, 2-Amerika, 3-Osiyo, 4-Afrika (qo'shilishsiz)
qaror

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;

Guruh funktsiyalaridan foydalangan holda yig'ilgan ma'lumotlar haqida hisobot berish

Xodimlar jadvali. Minimal va maksimal ish haqi, erta va kech kelish sanalari va xodimlar soni bilan departament_id tomonidan hisobot oling. Xodimlar soni bo'yicha saralash (kamaytirish)
qaror

  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;

Xodimlar jadvali. Ismlari bir harf bilan boshlangan nechta xodim bor? Miqdori bo'yicha saralash. Faqat raqam 1 dan katta bo'lganlarni ko'rsating
qaror

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;

Xodimlar jadvali. Bir bo'limda qancha xodim ishlaydi va bir xil maosh oladi?
qaror

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

Xodimlar jadvali. Haftaning har kuni qancha xodim yollanganligi haqida hisobot oling. Miqdori bo'yicha saralash
qaror

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

Xodimlar jadvali. Yil bo'yicha qancha xodim ishga olinganligi haqida hisobot oling. Miqdori bo'yicha saralash
qaror

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

Xodimlar jadvali. Xodimlari bo'lgan bo'limlar sonini oling
qaror

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

Xodimlar jadvali. 30 dan ortiq xodimlarga ega bo'lim_id ro'yxatini oling
qaror

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

Xodimlar jadvali. Bo'lim_idlari ro'yxatini va har bir bo'limdagi xodimlarning yaxlitlangan o'rtacha ish haqini oling.
qaror

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

Mamlakatlar jadvali. 60 dan ortiq bo'lgan barcha mamlakat_nomlarining barcha harflarining region_id yig'indisi ro'yxatini oling
qaror

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

Xodimlar jadvali. Bir nechta (>1) job_id xodimlari ishlaydigan bo'lim_id ro'yxatini oling
qaror

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

Xodimlar jadvali. Qo'l ostidagilar soni 5 dan ortiq va unga bo'ysunuvchilarning barcha maoshlari yig'indisi 50000 dan ortiq bo'lgan menejer_id ro'yxatini oling
qaror

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

Xodimlar jadvali. Barcha qo'l ostidagi xodimlarining o'rtacha maoshi 6000 dan 9000 gacha bo'lgan va bonuslar olmaydigan menejer_id ro'yxatini oling (komissiya_pct bo'sh)
qaror

  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;

Xodimlar jadvali. Barcha xodimlardan maksimal ish haqini oling job_id “KLERK” so‘zi bilan tugaydi
qaror

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

Xodimlar jadvali. Bo'lim uchun barcha o'rtacha ish haqi orasida maksimal ish haqini oling
qaror

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

Xodimlar jadvali. Ularning nomidagi harflar soni bir xil bo'lgan xodimlar sonini oling. Shu bilan birga, faqat nomi 5 dan uzun va bir xil nomdagi xodimlar soni 20 dan ortiq bo'lganlarni ko'rsating. Ism uzunligi bo'yicha tartiblang
qaror

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

Birlashmalar yordamida bir nechta jadvallardan ma'lumotlarni ko'rsatish

Jadval xodimlari, bo'limlar, joylar, mamlakatlar, hududlar. Mintaqalar ro'yxatini va har bir mintaqadagi xodimlar sonini oling
qaror

  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;

Jadval xodimlari, bo'limlar, joylar, mamlakatlar, hududlar. Har bir xodim haqida batafsil ma'lumot oling:
Ism, familiya, bo'lim, ish, ko'cha, mamlakat, mintaqa
qaror

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

Xodimlar jadvali. 6 dan ortiq xodimi bo'lgan barcha menejerlarni ko'rsating
qaror

  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;

Xodimlar jadvali. Hech kimga hisobot bermaydigan barcha xodimlarni ko'rsating
qaror

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;

Xodimlar jadvali, Job_history. Xodimlar jadvali barcha xodimlarni saqlaydi. Job_history jadvali kompaniyani tark etgan xodimlarni saqlaydi. Barcha xodimlar va ularning kompaniyadagi holati to'g'risida hisobot oling (ishlagan yoki kompaniyadan ketish sanasi bilan ketgan)
Misol:
birinchi_ism | holat
jennifer | 31 yil 2006 dekabrda kompaniyani tark etdi
Clara | Hozirda ishlamoqda
qaror

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

Jadval xodimlari, bo'limlar, joylar, mamlakatlar, hududlar. Evropada yashovchi xodimlar ro'yxatini oling (mintaqa_nomi)
qaror

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

Stol xodimlari, bo'limlar. 30 dan ortiq xodimi bo'lgan barcha bo'limlarni ko'rsating
qaror

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

Stol xodimlari, bo'limlar. Hech qanday bo'limda bo'lmagan barcha xodimlarni ko'rsating
qaror

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;

Stol xodimlari, bo'limlar. Xodimlarsiz barcha bo'limlarni ko'rsating
qaror

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

Xodimlar jadvali. Bo'ysunuvchilari bo'lmagan barcha xodimlarni ko'rsating
qaror

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

Stol xodimlari, ish o'rinlari, bo'limlar. Xodimlarni formatda ko'rsating: Ism_ismi, Lavozim_nomi, Bo'lim_nomi.
Misol:
birinchi_ism | lavozim nomi | Bo'lim_nomi
Donald | yuk tashish | Kotib yetkazib berish
qaror

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

Xodimlar jadvali. Menejerlari 2005 yilda ishga kelgan xodimlar ro'yxatini oling, lekin shu bilan birga, bu ishchilarning o'zlari 2005 yilgacha ishga joylashdilar.
qaror

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

Xodimlar jadvali. Menejerlari istalgan yilning yanvar oyida ishga joylashgan va ushbu xodimlarning lavozimi 15 belgidan ortiq bo'lgan xodimlar ro'yxatini oling.
qaror

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;

So'rovlarni hal qilish uchun pastki so'rovlardan foydalanish

Xodimlar jadvali. Eng uzun ismli xodimlar ro'yxatini oling.
qaror

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

Xodimlar jadvali. Barcha xodimlarning o'rtacha maoshidan yuqori ish haqi bo'lgan xodimlar ro'yxatini oling.
qaror

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

Stol xodimlari, bo'limlar, joylar. Xodimlari jami eng kam maosh oladigan shaharni oling.
qaror

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

Xodimlar jadvali. Rahbari 15000 XNUMX dan ortiq maosh oladigan xodimlar ro'yxatini oling.
qaror

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

Stol xodimlari, bo'limlar. Xodimlarsiz barcha bo'limlarni ko'rsating
qaror

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

Xodimlar jadvali. Menejer bo'lmagan barcha xodimlarni ko'rsating
qaror

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

Xodimlar jadvali. 6 dan ortiq xodimi bo'lgan barcha menejerlarni ko'rsating
qaror

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

Stol xodimlari, bo'limlar. IT bo'limida ishlaydigan xodimlarni ko'rsating
qaror

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;

Xodimlar jadvali. Menejerlari 2005 yilda ishga kelgan xodimlar ro'yxatini oling, lekin shu bilan birga, bu ishchilarning o'zlari 2005 yilgacha ishga joylashdilar.
qaror

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

Xodimlar jadvali. Menejerlari istalgan yilning yanvar oyida ishga joylashgan va ushbu xodimlarning lavozimi 15 belgidan ortiq bo'lgan xodimlar ro'yxatini oling.
qaror

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;

Hozircha hammasi shu.

Umid qilamanki, topshiriqlar qiziqarli va hayajonli bo'ldi.
Men bu ro'yxatga iloji boricha qo'shaman.
Men ham har qanday izoh va takliflardan xursand bo'laman.

PS: Agar kimdir SELECTda qiziqarli topshiriq bilan chiqsa, izohlarda yozing, men uni ro'yxatga qo'shaman.

Rahmat.

Manba: www.habr.com

SQL. Qiziqarli jumboqlar

vazifalar

a Izoh qo'shish Bu savolga javob bekor