Hello Habr!
Pikeun leuwih ti 3 taun Kuring geus ngajarkeun SQL di sagala rupa puseur latihan, sarta salah sahiji observasi abdi yen siswa ngawasaan tur ngartos SQL hadé mun maranéhna dibéré tugas, sarta teu ngan ngobrol ngeunaan kamungkinan sarta pondasi teoritis.
Dina tulisan ieu, kuring bakal ngabagikeun daptar tugas anu kuring pasihan ka mahasiswa salaku PR sareng dimana urang ngalaksanakeun rupa-rupa brainstorms, anu nyababkeun pamahaman anu jero sareng jelas ngeunaan SQL.
SQL (ˈɛsˈkjuˈɛl; eng. structured query language) nyaéta basa pamrograman déklaratif anu dipaké pikeun nyieun, ngaropea jeung ngatur data dina basis data relasional anu dikokolakeun ku sistem manajemen basis data anu luyu.
Anjeun tiasa maca ngeunaan SQL ti béda .
Artikel ieu teu dimaksudkeun pikeun ngajarkeun anjeun SQL ti scratch.
Ku kituna hayu urang angkat.
Urang bakal ngagunakeun well-dipikawanoh dina Oracle sareng tabelna ():
Kuring dicatet yén urang bakal mertimbangkeun ngan tugas on SELECT. Henteu aya tugas dina DML sareng DDL.
pancén
Watesan sareng Asihan Data
méja pagawé. Kéngingkeun daptar inpormasi ngeunaan sadaya karyawan
kaputusan
SELECT * FROM employees
méja pagawé. Kéngingkeun daptar sadaya pagawé anu namina 'David'
kaputusan
SELECT *
FROM employees
WHERE first_name = 'David';
méja pagawé. Meunang daptar sadaya pagawé kalawan job_id sarua jeung 'IT_PROG'
kaputusan
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
méja pagawé. Kéngingkeun daptar sadaya karyawan ti departemen ka-50 (departemén_id) kalayan gaji (gaji) langkung ageung ti 4000
kaputusan
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
méja pagawé. Kéngingkeun daptar sadaya karyawan ti tanggal 20 sareng ti departemén ka-30 (department_id)
kaputusan
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
méja pagawé. Kéngingkeun daptar sadaya karyawan anu hurup panungtung dina namina nyaéta 'a'
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%a';
méja pagawé. Kéngingkeun daptar sadaya karyawan ti 50 sareng ti departemén 80 (department_id) anu gaduh bonus (nilai dina kolom komisi_pct henteu kosong)
kaputusan
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
méja pagawé. Kéngingkeun daptar sadaya karyawan anu namina sahenteuna sahenteuna 2 hurup 'n'
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
méja pagawé. Kéngingkeun daptar sadaya karyawan anu namina langkung panjang ti 4 hurup
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
méja pagawé. Kéngingkeun daptar sadaya karyawan anu gajina antara 8000 sareng 9000 (inklusif)
kaputusan
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
méja pagawé. Meunang daptar sadaya pagawé anu ngaranna ngandung simbol '%'
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
méja pagawé. Meunang daptar sadaya ID manajer
kaputusan
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
méja pagawé. Kéngingkeun daptar karyawan kalayan posisina dina format: Donald(sh_clerk)
kaputusan
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Ngagunakeun Fungsi Baris Tunggal pikeun Ngaropea Kaluaran
méja pagawé. Kéngingkeun daptar sadaya karyawan anu namina langkung panjang ti 10 hurup
kaputusan
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
méja pagawé. Kéngingkeun daptar sadaya karyawan anu ngagaduhan hurup 'b' dina namina (henteu sensitip)
kaputusan
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
méja pagawé. Kéngingkeun daptar sadaya karyawan anu namina sahenteuna sahenteuna 2 hurup 'a'
kaputusan
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
méja pagawé. Kéngingkeun daptar sadaya karyawan anu gajina dikali 1000
kaputusan
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
méja pagawé. Kéngingkeun nomer 3-angka kahiji tina nomer telepon karyawan upami nomerna dina format ХХХ.ХХХ.ХХХХ
kaputusan
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Méja departemén. Kéngingkeun kecap munggaran tina nami jabatan pikeun anu gaduh langkung ti hiji kecap dina nami
kaputusan
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
méja pagawé. Meunangkeun ngaran pagawe tanpa hurup kahiji jeung panungtung dina ngaran
kaputusan
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
méja pagawé. Kéngingkeun daptar sadaya pagawé anu hurup terakhir dina nami sami sareng 'm' sareng panjang nami langkung ageung ti 5
kaputusan
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
méja ganda. Meunang tanggal Jumaah hareup
kaputusan
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
méja pagawé. Kéngingkeun daptar sadaya karyawan anu parantos sareng perusahaan langkung ti 17 taun
kaputusan
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
méja pagawé. Kéngingkeun daptar sadaya pagawé anu digit terakhir tina nomer teleponna ganjil sareng diwangun ku 3 nomer anu dipisahkeun ku titik
kaputusan
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
méja pagawé. Kéngingkeun daptar sadaya pagawé anu nilai job_id saatos tanda '_' sahenteuna sahenteuna 3 karakter, tapi nilai ieu saatos '_' henteu sami sareng 'CLERK'
kaputusan
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
méja pagawé. Kéngingkeun daptar sadaya pagawé ku ngagentos sadayana '.' dina nilai PHONE_NUMBER dina '-'
kaputusan
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Ngagunakeun Fungsi Konversi sareng Ekspresi Kondisional
méja pagawé. Kéngingkeun daptar sadaya pagawé anu sumping damel dina dinten kahiji dina sasih (naon waé)
kaputusan
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
méja pagawé. Kéngingkeun daptar sadaya karyawan anu sumping damel di 2008
kaputusan
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
méja DUAL. Témbongkeun tanggal isukan dina format: Isukan nyaéta poé Kadua Januari
kaputusan
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
méja pagawé. Kéngingkeun daptar sadaya karyawan sareng tanggal mimitina dina format: 21 Juni 2007
kaputusan
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
méja pagawé. Kéngingkeun daptar karyawan kalayan gaji ningkat ku 20%. Témbongkeun gaji kalawan tanda dollar
kaputusan
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
méja pagawé. Kéngingkeun daptar sadaya pagawé anu sumping damel dina bulan Pebruari 2007.
kaputusan
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';
méja DUAL. Ékspor tanggal ayeuna, + detik, + menit, + jam, + dinten, + bulan, + taun
kaputusan
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;
méja pagawé. Kéngingkeun daptar sadaya karyawan kalayan gaji pinuh (gaji + komisi_pct(%)) dina format: $24,000.00
kaputusan
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
méja pagawé. Kéngingkeun daptar sadaya karyawan sareng inpormasi ngeunaan kasadiaan bonus gaji (Leres / Henteu)
kaputusan
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
méja pagawé. Kéngingkeun tingkat gaji unggal karyawan: Kirang ti 5000 dianggap tingkat rendah, langkung ageung atanapi sami sareng 5000 sareng kirang ti 10000 dianggap tingkat normal, langkung ageung atanapi sami sareng 10000 dianggap tingkat luhur
kaputusan
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;
Méja nagara. Pikeun unggal nagara, tunjukkeun daérah dimana éta: 1-Eropa, 2-Amerika, 3-Asia, 4-Afrika (tanpa Gabung)
kaputusan
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;
Ngalaporkeun Data Gabungan Ngagunakeun Fungsi Grup
méja pagawé. Kéngingkeun laporan ku department_id kalayan gaji minimum sareng maksimal, tanggal kadatangan awal sareng telat sareng jumlah karyawan. Susun dumasar jumlah karyawan (desc)
kaputusan
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;
méja pagawé. Sabaraha karyawan anu namina dimimitian ku hurup anu sami? Susun dumasar kuantitas. Tampilkeun ngan ukur anu jumlahna langkung ageung tibatan 1
kaputusan
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;
méja pagawé. Sabaraha karyawan damel di jabatan anu sami sareng nampi gaji anu sami?
kaputusan
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
méja pagawé. Meunang laporan ngeunaan sabaraha karyawan anu hired on unggal poe dina saminggu. Susun dumasar kuantitas
kaputusan
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
méja pagawé. Meunang laporan ngeunaan sabaraha karyawan anu hired dumasar taun. Susun dumasar kuantitas
kaputusan
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
méja pagawé. Kéngingkeun jumlah departemén anu ngagaduhan karyawan
kaputusan
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
méja pagawé. Kéngingkeun daptar department_id sareng langkung ti 30 pagawé
kaputusan
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
méja pagawé. Kéngingkeun daptar jabatan_id sareng rata-rata gaji karyawan di unggal departemen.
kaputusan
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Méja nagara. Kéngingkeun daptar région_id jumlah sadaya hurup sadaya ngaran_nagara anu aya langkung ti 60
kaputusan
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
méja pagawé. Kéngingkeun daptar jabatan_id dimana karyawan sababaraha (>1) job_id dianggo
kaputusan
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
méja pagawé. Kéngingkeun daptar manager_id anu jumlah bawahan langkung ageung tibatan 5 sareng jumlah sadaya gaji bawahan langkung ageung tibatan 50000
kaputusan
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
méja pagawé. Kéngingkeun daptar manager_id anu rata-rata gaji sadaya bawahanna antara 6000 sareng 9000 anu henteu nampi bonus (commission_pct kosong)
kaputusan
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;
méja pagawé. Kéngingkeun gaji maksimal ti sadaya karyawan job_id anu ditungtungan ku kecap 'CLERK'
kaputusan
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';
méja pagawé. Kéngingkeun gaji maksimal diantara sadaya gaji rata-rata pikeun jabatan
kaputusan
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
méja pagawé. Kéngingkeun jumlah karyawan kalayan jumlah hurup anu sami dina nami na. Dina waktos anu sami, tunjukkeun ngan ukur anu namina langkung panjang tibatan 5 sareng jumlah karyawan anu nami sami langkung ti 20. Susun dumasar panjang nami.
kaputusan
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);
Mintonkeun Data ti Sababaraha Tables Ngagunakeun Gabung
Pagawé méja, Departemén, Lokasi, Nagara, Wewengkon. Kéngingkeun daptar daérah sareng jumlah pagawé di unggal daérah
kaputusan
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;
Pagawé méja, Departemén, Lokasi, Nagara, Wewengkon. Meunangkeun inpormasi lengkep ngeunaan unggal karyawan:
First_name, Last_name, Department, Job, Street, Country, Region
kaputusan
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);
méja pagawé. Tampilkeun sadayana manajer anu ngagaduhan langkung ti 6 karyawan
kaputusan
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;
méja pagawé. Témbongkeun sakabéh pagawé anu teu ngalaporkeun ka saha
kaputusan
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;
méja karyawan, Job_history. Méja karyawan nyimpen sadaya karyawan. Méja Job_history nyimpen karyawan anu ninggalkeun perusahaan. Kéngingkeun laporan sadaya karyawan sareng statusna di perusahaan (Padamelan atanapi tinggalkeun perusahaan sareng tanggal angkat)
contona:
first_name | status
jennifer | Ninggalkeun perusahaan di 31 Désémber 2006
Clara | Ayeuna Gawé
kaputusan
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);
Pagawé méja, Departemén, Lokasi, Nagara, Wewengkon. Meunang daptar pagawé anu cicing di Éropa (region_name)
kaputusan
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';
Pagawe méja, Departemén. Témbongkeun sakabéh departemén kalawan leuwih ti 30 pagawé
kaputusan
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Pagawé méja, Departemén. Témbongkeun sakabéh pagawé anu teu di departemen mana wae
kaputusan
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;
Pagawé méja, Departemén. Témbongkeun sakabéh departemén kalawan euweuh pagawé
kaputusan
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
méja pagawé. Témbongkeun sakabéh pagawé anu teu boga bawahan
kaputusan
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Pagawe méja, Proyék, Departemén. Témbongkeun pagawé dina format: First_name, Job_title, Department_name.
contona:
first_name | judul padamelan | Ngaran_departemén
Donald | pengiriman barang | Petugas Pangiriman
kaputusan
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);
méja pagawé. Kéngingkeun daptar karyawan anu manajerna ngagaduhan padamelan di 2005, tapi dina waktos anu sami, para karyawan ieu nyalira ngagaduhan padamelan sateuacan 2005.
kaputusan
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');
méja pagawé. Kéngingkeun daptar karyawan anu manajerna ngagaduhan padamelan dina sasih Januari unggal taun sareng panjang job_title karyawan ieu langkung ti 15 karakter.
kaputusan
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;
Ngagunakeun Subqueries pikeun ngajawab Query
méja pagawé. Kéngingkeun daptar karyawan kalayan nami pangpanjangna.
kaputusan
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
méja pagawé. Kéngingkeun daptar karyawan anu gajina langkung ageung tibatan gaji rata-rata sadaya karyawan.
kaputusan
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Pagawé méja, Departemén, Lokasi. Kéngingkeun kota dimana karyawan nampi pangsaeutikna dina total.
kaputusan
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);
méja pagawé. Kéngingkeun daptar karyawan anu manajerna nampi gaji langkung ti 15000.
kaputusan
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Pagawé méja, Departemén. Témbongkeun sakabéh departemén kalawan euweuh pagawé
kaputusan
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
méja pagawé. Témbongkeun sakabéh pagawé anu lain manajer
kaputusan
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
méja pagawé. Tampilkeun sadayana manajer anu ngagaduhan langkung ti 6 karyawan
kaputusan
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Pagawé méja, Departemén. Témbongkeun pagawé anu digawé di jurusan IT
kaputusan
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Pagawe méja, Proyék, Departemén. Témbongkeun pagawé dina format: First_name, Job_title, Department_name.
contona:
first_name | judul padamelan | Ngaran_departemén
Donald | pengiriman barang | Petugas Pangiriman
kaputusan
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;
méja pagawé. Kéngingkeun daptar karyawan anu manajerna ngagaduhan padamelan di 2005, tapi dina waktos anu sami, para karyawan ieu nyalira ngagaduhan padamelan sateuacan 2005.
kaputusan
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');
méja pagawé. Kéngingkeun daptar karyawan anu manajerna ngagaduhan padamelan dina sasih Januari unggal taun sareng panjang job_title karyawan ieu langkung ti 15 karakter.
kaputusan
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;
Sakitu wae kanggo ayeuna.
Kuring miharep éta tugas éta metot sarta seru.
Kuring bakal nambahan kana daptar ieu saloba mungkin.
Kuring ogé bakal bungah mun sagala koméntar sarta bongbolongan.
PS: Lamun batur datang nepi ka hiji tugas metot dina PILIH, nulis dina komentar, Kuring bakal nambahan ka daptar.
Hatur nuhun.
sumber: www.habr.com