Sugeng rawuh, Habr!
Kanggo luwih saka 3 taun aku wis mulang SQL ing macem-macem pusat latihan, lan salah siji saka pengamatan iku siswa master lan ngerti SQL luwih apik yen padha diwenehi tugas, lan ora mung marang bab kemungkinan lan dhasar teori.
Ing artikel iki, aku bakal nuduhake karo sampeyan dhaptar masalah sing dakwenehake marang siswa minangka peer lan ing ngendi kita nindakake macem-macem jinis brainstorms, sing ndadΓ©kakΓ© pangerten SQL sing jero lan jelas.
SQL (ΛΙsΛkjuΛΙl; basa pitakon terstruktur Inggris) minangka basa pamrograman deklaratif sing digunakake kanggo nggawe, ngowahi lan ngatur data ing basis data relasional sing dikelola dening sistem manajemen basis data sing cocog.
Sampeyan bisa maca babagan SQL saka macem-macem .
Artikel iki ora dimaksudake kanggo mulang sampeyan SQL saka awal.
Dadi ayo lunga.
Kita bakal nggunakake kondhang ing Oracle karo tabel ():
Aku Wigati sing kita mung bakal nimbang tugas PILIH. Ora ana tugas DML utawa DDL ing kene.
tugas
Watesan lan Ngurutake Data
Meja karyawan. Entuk dhaptar kanthi informasi babagan kabeh karyawan
kaputusan
SELECT * FROM employees
Meja karyawan. Entuk dhaptar kabeh karyawan sing jenenge 'David'
kaputusan
SELECT *
FROM employees
WHERE first_name = 'David';
Meja karyawan. Entuk dhaptar kabeh karyawan sing duwe job_id padha karo 'IT_PROG'
kaputusan
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Meja karyawan. Njaluk dhaptar kabeh karyawan saka departemen 50 (department_id) kanthi gaji (gaji) luwih saka 4000
kaputusan
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Meja karyawan. Entuk dhaptar kabeh karyawan saka departemen 20 lan 30 (department_id)
kaputusan
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Meja karyawan. Entuk dhaptar kabeh karyawan sing huruf pungkasan jenenge 'a'
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Meja karyawan. Njaluk dhaptar kabeh karyawan saka departemen 50 lan 80 (department_id) sing duwe bonus (nilai ing kolom komisi_pct ora kosong)
kaputusan
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Meja karyawan. Entuk dhaptar kabeh karyawan sing jenenge ngemot paling ora 2 huruf 'n'
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Meja karyawan. Entuk dhaptar kabeh karyawan sing jenenge luwih saka 4 huruf
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Meja karyawan. Entuk dhaptar kabeh karyawan sing gajine ana ing kisaran 8000 nganti 9000 (kalebu)
kaputusan
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Meja karyawan. Entuk dhaptar kabeh karyawan sing jenenge ngemot simbol '%'
kaputusan
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Meja karyawan. Entuk dhaptar kabeh ID manajer
kaputusan
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Meja karyawan. Entuk dhaptar karyawan kanthi posisi ing format: Donald(sh_clerk)
kaputusan
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Nggunakake Fungsi Baris Tunggal kanggo Ngatur Output
Meja karyawan. Entuk dhaptar kabeh karyawan sing jenenge luwih saka 10 huruf
kaputusan
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Meja karyawan. Entuk dhaptar kabeh karyawan sing duwe huruf 'b' ing jenenge (ora sensitif huruf cilik)
kaputusan
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Meja karyawan. Entuk dhaptar kabeh karyawan sing jenenge ngemot paling ora 2 huruf 'a'
kaputusan
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Meja karyawan. Entuk dhaptar kabeh karyawan sing gajine kelipatan 1000
kaputusan
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Meja karyawan. Entuk nomer 3 digit pisanan saka nomer telpon karyawan yen nomer kasebut ing format XXX.XXX.XXXX
kaputusan
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Tabel departemen. Entuk tembung pisanan saka jeneng departemen kanggo sing duwe luwih saka siji tembung ing jeneng
kaputusan
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Meja karyawan. Entuk jeneng pegawe tanpa jeneng pisanan lan pungkasan
kaputusan
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Meja karyawan. Entuk dhaptar kabeh karyawan sing huruf pungkasan ing jenenge 'm' lan sing jenenge luwih saka 5
kaputusan
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Tabel Dwi. Njaluk tanggal Jum'at sabanjurΓ©
kaputusan
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Meja karyawan. Entuk dhaptar kabeh karyawan sing wis kerja ing perusahaan luwih saka 17 taun
kaputusan
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Meja karyawan. Entuk dhaptar kabeh karyawan sing nomer pungkasan nomer telpon ganjil lan kasusun saka 3 nomer sing dipisahake karo titik
kaputusan
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Meja karyawan. Entuk dhaptar kabeh karyawan sing nilai job_id sawise tandha '_' paling sethithik 3 karakter, nanging nilai iki sawise '_' ora padha karo 'CLERK'
kaputusan
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Meja karyawan. Entuk dhaptar kabeh karyawan kanthi ngganti kabeh '.' ing nilai PHONE_NUMBER ing '-'
kaputusan
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Nggunakake Fungsi Konversi lan Ekspresi Kondisi
Meja karyawan. Entuk dhaptar kabeh karyawan sing teka kerja ing dina pisanan sasi (apa wae)
kaputusan
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Meja karyawan. Entuk dhaptar kabeh karyawan sing teka ing taun 2008
kaputusan
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Tabel DUAL. Tampilake tanggal sesuk ing format: Sesuk dina kapindho Januari
kaputusan
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Meja karyawan. Entuk dhaptar kabeh karyawan lan tanggal saben karyawan teka ing kantor kanthi format: 21 Juni 2007
kaputusan
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Meja karyawan. Entuk dhaptar karyawan kanthi gaji tambah 20%. Tampilake gaji kanthi tandha dolar
kaputusan
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Meja karyawan. Entuk dhaptar kabeh karyawan sing wiwit kerja ing Februari 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';
Tabel DUAL. Temokake tanggal saiki, + detik, + menit, + jam, + dina, + sasi, + 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;
Meja karyawan. Entuk dhaptar kabeh karyawan kanthi gaji lengkap (gaji + komisi_pct(%)) kanthi 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;
Meja karyawan. Entuk dhaptar kabeh karyawan lan informasi babagan kasedhiyan bonus gaji (Ya / Ora)
kaputusan
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Meja karyawan. Entuk tingkat gaji saben pegawe: Kurang saka 5000 dianggep minangka tingkat sing kurang, luwih saka utawa padha karo 5000 lan kurang saka 10000 dianggep tingkat normal, luwih saka utawa padha karo 10000 dianggep tingkat dhuwur
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;
Negara Tabel. Kanggo saben negara, tuduhake wilayah sing ana: 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;
Nglaporake Data Gabungan Nggunakake Fungsi Grup
Meja karyawan. Nampa laporan dening department_id kanthi gaji minimal lan maksimal, tanggal tekan awal lan pungkasan ing karya lan jumlah karyawan. Urut miturut jumlah karyawan (mudhun)
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;
Meja karyawan. Pira karyawan sing jenenge diwiwiti kanthi huruf sing padha? Urut miturut jumlah. Tampilake mung sing jumlahe luwih saka 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;
Meja karyawan. Pira karyawan sing kerja ing departemen sing padha lan entuk gaji sing padha?
kaputusan
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Meja karyawan. Entuk laporan babagan jumlah karyawan sing direkrut saben dina ing minggu kasebut. Urut miturut jumlah
kaputusan
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Meja karyawan. Entuk laporan babagan jumlah karyawan sing direkrut miturut taun. Urut miturut jumlah
kaputusan
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Meja karyawan. Entuk nomer departemen sing duwe karyawan
kaputusan
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Meja karyawan. Entuk dhaptar department_id sing duwe luwih saka 30 karyawan
kaputusan
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Meja karyawan. Entuk dhaptar departemen_id lan gaji rata-rata karyawan ing saben departemen.
kaputusan
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Negara Tabel. Entuk dhaptar region_id jumlah kabeh huruf saka kabeh jeneng_negara sing ana luwih saka 60
kaputusan
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Meja karyawan. Entuk dhaptar departemen_id ing ngendi karyawan saka sawetara (>1) job_id bisa digunakake
kaputusan
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Meja karyawan. Njaluk dhaptar manager_id sing jumlah bawahan luwih saka 5 lan jumlah kabeh gaji bawahan luwih saka 50000
kaputusan
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Meja karyawan. Njaluk dhaptar manager_id sing gaji rata-rata kabeh bawahan ana ing kisaran saka 6000 nganti 9000 lan sing ora nampa 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;
Meja karyawan. Entuk gaji maksimal saka kabeh karyawan job_id sing diakhiri karo tembung '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';
Meja karyawan. Entuk gaji maksimal ing antarane kabeh gaji rata-rata kanggo departemen
kaputusan
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Meja karyawan. Entuk nomer karyawan kanthi jumlah huruf sing padha ing jenenge. Ing wektu sing padha, nuduhake mung sing jenenge dawane luwih saka 5 lan jumlah karyawan kanthi jeneng sing padha luwih saka 20. Urut miturut dawa jeneng.
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);
Nampilake Data saka Multiple Tables Nggunakake Gabung
Meja Karyawan, Departemen, Lokasi, Negara, Wilayah. Entuk dhaptar wilayah lan jumlah karyawan ing saben wilayah
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;
Meja Karyawan, Departemen, Lokasi, Negara, Wilayah. Entuk informasi rinci babagan saben 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);
Meja karyawan. Tampilake kabeh manajer sing duwe karyawan luwih saka 6 bawahan
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;
Meja karyawan. Tampilake kabeh karyawan sing ora nglaporake marang sapa wae
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;
Tabel Karyawan, Job_history. Tabel Karyawan nyimpen kabeh karyawan. Tabel Job_history nyimpen karyawan sing wis ninggalake perusahaan. Entuk laporan babagan kabeh karyawan lan statuse ing perusahaan (Kanggo utawa ninggalake perusahaan kanthi tanggal budhal)
Conto:
jeneng_pisanan | status
Jennifer | Ninggalake perusahaan ing 31 Desember 2006
Clara | Saiki Kerja
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);
Meja Karyawan, Departemen, Lokasi, Negara, Wilayah. Entuk dhaptar karyawan sing manggon ing Eropa (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';
Meja Karyawan, Departemen. Tampilake kabeh departemen kanthi luwih saka 30 karyawan
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;
Meja Karyawan, Departemen. Tampilake kabeh karyawan sing ora ana ing departemen apa 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;
Meja Karyawan, Departemen. Tampilake kabeh departemen sing ora ana karyawan
kaputusan
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Meja karyawan. Tampilake kabeh karyawan sing ora duwe 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;
Tabel Karyawan, Jobs, Departemen. Tampilake karyawan ing format: First_name, Job_title, Department_name.
Conto:
jeneng_pisanan | Job_title | Jeneng_jabatan
Donald | Pangiriman | Petugas Pengiriman
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);
Meja karyawan. Njaluk dhaptar karyawan sing manajer entuk kerja ing taun 2005, nanging ing wektu sing padha karyawan kasebut entuk kerja sadurunge 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');
Meja karyawan. Entuk dhaptar karyawan sing manajer entuk kerja ing wulan Januari saben taun lan dawa job_title karyawan kasebut luwih saka 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;
Nggunakake Subqueries kanggo Ngatasi Pitakonan
Meja karyawan. Entuk dhaptar karyawan kanthi jeneng paling dawa.
kaputusan
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Meja karyawan. Entuk dhaptar karyawan kanthi gaji luwih gedhe tinimbang gaji rata-rata kabeh karyawan.
kaputusan
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Karyawan, Departemen, Tabel Lokasi. Entuk kutha ing ngendi karyawan total entuk paling sithik.
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);
Meja karyawan. Entuk dhaptar karyawan sing manajer entuk gaji luwih saka 15000.
kaputusan
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Meja Karyawan, Departemen. Tampilake kabeh departemen sing ora ana karyawan
kaputusan
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Meja karyawan. Tampilake kabeh karyawan sing dudu manajer
kaputusan
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Meja karyawan. Tampilake kabeh manajer sing duwe karyawan luwih saka 6 bawahan
kaputusan
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Meja Karyawan, Departemen. Tampilake karyawan sing kerja ing departemen IT
kaputusan
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tabel Karyawan, Jobs, Departemen. Tampilake karyawan ing format: First_name, Job_title, Department_name.
Conto:
jeneng_pisanan | Job_title | Jeneng_jabatan
Donald | Pangiriman | Petugas Pengiriman
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;
Meja karyawan. Njaluk dhaptar karyawan sing manajer entuk kerja ing taun 2005, nanging ing wektu sing padha karyawan kasebut entuk kerja sadurunge 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');
Meja karyawan. Entuk dhaptar karyawan sing manajer entuk kerja ing wulan Januari saben taun lan dawa job_title karyawan kasebut luwih saka 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;
Semono wae.
Muga-muga tugas kasebut menarik lan nyenengake.
Aku bakal nambah dhaptar tugas iki sabisa.
Aku uga bakal seneng nampa komentar lan saran.
PS: Yen wong teka munggah karo tugas PILIH menarik, nulis ing komentar lan aku bakal nambah menyang dhaftar.
Π‘ΠΏΠ°ΡΠΈΠ±ΠΎ.
Source: www.habr.com