Sawubona, Habr!
Sekuyiminyaka engaphezu kwengu-3 manje ngifundisa i-SQL ezikhungweni zokuqeqesha ezihlukahlukene, futhi okunye engikuqaphelayo ukuthi abafundi bayakwazi futhi baqonde kangcono i-SQL uma benikezwa umsebenzi, hhayi nje ukutshelwa ngamathuba nezisekelo zethiyori.
Kulesi sihloko, ngizokwabelana nawe ngohlu lwami lwezinkinga engizinikeza abafundi njengomsebenzi wesikole wesikole futhi lapho senza khona izinhlobo ezahlukene zezingxoxo, okuholela ekuqondeni okujulile nokucacile kwe-SQL.
I-SQL (ΛΙsΛkjuΛΙl; Ulimi lwemibuzo oluhlelekile lwesiNgisi) iwulimi lohlelo olumemezelayo olusetshenziselwa ukwakha, ukuguqula nokuphatha idatha kusizindalwazi esihlobene esiphethwe uhlelo olufanele lokuphathwa kwesizindalwazi.
Ungafunda nge-SQL kusuka ezinhlobonhlobo .
Lesi sihloko asihloselwe ukukufundisa i-SQL kusukela ekuqaleni.
Ake sihambe.
Sizosebenzisa okwaziwayo e-Oracle namatafula ayo ():
Ngiyaqaphela ukuthi sizocubungula kuphela imisebenzi ethi KHETHA. Ayikho imisebenzi ye-DML noma ye-DDL lapha.
Imisebenzi
Ukukhawulela Nokuhlunga Idatha
Ithebula lezisebenzi. Thola uhlu olunolwazi ngabo bonke abasebenzi
Isixazululo
SELECT * FROM employees
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi okuthiwa 'David'
Isixazululo
SELECT *
FROM employees
WHERE first_name = 'David';
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi abane- job_id elingana ne-'IT_PROG'
Isixazululo
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezivela emnyangweni wama-50 (umnyango_id) ezineholo (iholo) elingaphezu kuka-4000
Isixazululo
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezivela emnyangweni wama-20 nowama-30 (umnyango_id)
Isixazululo
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezinohlamvu lokugcina olusegameni elithi 'a'
Isixazululo
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezivela emnyangweni wama-50 nowama-80 (umnyango_id) ezinebhonasi (inani elikukholomu ye-commission_pct alinalutho)
Isixazululo
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi amagama abo aqukethe okungenani izinhlamvu ezi-2 'n'
Isixazululo
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi amagama azo amade kunezinhlamvu ezi-4
Isixazululo
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi abamholo wabo osuka ku-8000 kuye ku-9000 (okuhlanganisayo)
Isixazululo
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi abagama labo liqukethe uphawu '%'
Isixazululo
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Ithebula lezisebenzi. Thola uhlu lwabo bonke omazisi abaphathi
Isixazululo
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Ithebula lezisebenzi. Thola uhlu lwabasebenzi abanezikhundla zabo ngefomethi: Donald(sh_clerk)
Isixazululo
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Ukusebenzisa Imisebenzi Yomugqa Owodwa Ukwenza Ngokwezifiso Okukhiphayo
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi amagama azo amade kunezinhlamvu ezi-10
Isixazululo
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezinohlamvu u-'b' emagameni azo (akezwa lutho)
Isixazululo
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi amagama abo aqukethe okungenani izinhlamvu ezi-2 'a'
Isixazululo
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezinamaholo aphindwe kayi-1000
Isixazululo
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Ithebula lezisebenzi. Thola inombolo yokuqala yamadijithi angu-3 yenombolo yocingo yomsebenzi uma inombolo yakhe ikufomethi ethi XXX.XXX.XXXX
Isixazululo
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Ithebula leminyango. Thola igama lokuqala egameni lomnyango kulabo abanamagama angaphezu kwelilodwa egameni
Isixazululo
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Ithebula lezisebenzi. Thola amagama abasebenzi ngaphandle kohlamvu lokuqala nelokugcina egameni
Isixazululo
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezinohlamvu lokugcina emagameni azo u-'m' futhi ogama lazo lide kuno-5
Isixazululo
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Ithebula Elikabili. Thola idethi elandelayo yangoLwesihlanu
Isixazululo
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi asebesebenzele inkampani iminyaka engaphezu kwe-17
Isixazululo
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezinedijithi yazo yokugcina yenombolo yocingo yazo eyinqaba futhi iqukethe izinombolo ezi-3 ezihlukaniswe ichashazi.
Isixazululo
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezinenani le- job_id ngemva kophawu '_' elinezinhlamvu okungenani ezingu-3, ββkodwa leli nani ngemva kwe-'_' alilingani nokuthi 'CLERK'
Isixazululo
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi ngokufaka esikhundleni sabo bonke '.' kunani elingu-PHONE_NUMBER ku-'-'
Isixazululo
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Ukusebenzisa Imisebenzi Yokuguqula kanye Nezinkulumo ezinemibandela
Ithebula lezisebenzi. Thola uhlu lwazo zonke izisebenzi ezize emsebenzini ngosuku lokuqala lwenyanga (noma yiziphi)
Isixazululo
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi abeza ukuzosebenza ngo-2008
Isixazululo
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Ithebula DUAL. Bonisa idethi yakusasa ngefomethi: Kusasa usuku Lwesibili lukaJanuwari
Isixazululo
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi kanye nosuku isisebenzi ngasinye safika ngaso emsebenzini ngefomethi: 21st of June, 2007
Isixazululo
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Ithebula lezisebenzi. Thola uhlu lwabasebenzi abanyuswe amaholo ngo-20%. Bonisa iholo elinophawu lwedola
Isixazululo
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi abaqale ukusebenza ngoFebhuwari 2007.
Isixazululo
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';
Ithebula DUAL. Khipha idethi yamanje, + yesibili, + iminithi, + ihora, + usuku, + inyanga, + unyaka
Isixazululo
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;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi abanemiholo egcwele (umholo + commission_pct(%)) ngefomethi: $24,000.00
Isixazululo
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Ithebula lezisebenzi. Thola uhlu lwabo bonke abasebenzi kanye nolwazi mayelana nokutholakala kwamabhonasi omholo (Yebo/Cha)
Isixazululo
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Ithebula lezisebenzi. Thola izinga lomholo lesisebenzi ngasinye: Ngaphansi kuka-5000 kuthathwa njengezinga eliphansi, Okukhulu noma kulingana no-5000 futhi ngaphansi kuka-10000 kuthathwa njengezinga Elivamile, Ngaphezu noma elilingana ne-10000 kuthathwa njengezinga eliphezulu.
Isixazululo
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;
Amazwe Amathebula. Ezweni ngalinye, bonisa isifunda lapho itholakala khona: 1-Europe, 2-America, 3-Asia, 4-Africa (ngaphandle Joyina)
Isixazululo
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;
Ukubika Idatha Ehlanganisiwe Ngokusebenzisa Imisebenzi Yeqembu
Ithebula lezisebenzi. Thola umbiko nge-department_id oneholo elincane neliwumkhawulo, izinsuku zokufika ngaphambi kwesikhathi nezakamuva zokufika emsebenzini kanye nenani labasebenzi. Hlunga ngenombolo yabasebenzi (beyehla)
Isixazululo
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;
Ithebula lezisebenzi. Bangaki abasebenzi amagama abo aqala ngohlamvu olufanayo? Hlunga ngobuningi. Bonisa kuphela lawo lapho inani likhulu kuno-1
Isixazululo
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;
Ithebula lezisebenzi. Bangaki abasebenzi abasebenza emnyangweni owodwa futhi abathola iholo elifanayo?
Isixazululo
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Ithebula lezisebenzi. Thola umbiko wokuthi bangaki abasebenzi abaqashwe ngosuku ngalunye lwesonto. Hlunga ngobuningi
Isixazululo
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Ithebula lezisebenzi. Thola umbiko wokuthi bangaki abasebenzi abaqashwe ngonyaka. Hlunga ngobuningi
Isixazululo
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Ithebula lezisebenzi. Thola inani leminyango enabasebenzi
Isixazululo
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Ithebula lezisebenzi. Thola uhlu lwama-department_id anabasebenzi abangaphezu kuka-30
Isixazululo
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Ithebula lezisebenzi. Thola uhlu lwama-department_id kanye ne-avareji yeholo labasebenzi emnyangweni ngamunye.
Isixazululo
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Amazwe Amathebula. Thola uhlu lwesifunda_id isamba sazo zonke izinhlamvu zawo wonke amazwe_amagama lapho kukhona angaphezu kuka-60
Isixazululo
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Ithebula lezisebenzi. Thola uhlu lwama-department_id lapho izisebenzi ze- job_id ezimbalwa (>1) zisebenza khona
Isixazululo
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Ithebula lezisebenzi. Thola uhlu lwama-manager_id anenani labasebenzi abangaphansi kwalo lingaphezu kuka-5 futhi inani lawo wonke amaholo alabo abangaphansi kwakhe lingaphezu kuka-50000
Isixazululo
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Ithebula lezisebenzi. Thola uhlu lwama-manager_ids umholo wawo omaphakathi wabo bonke abangaphansi kwakhe usebangeni elisuka ku-6000 kuye ku-9000 futhi abangawatholi amabhonasi (i-commission_pct ayinalutho)
Isixazululo
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;
Ithebula lezisebenzi. Thola iholo eliphezulu kubo bonke abasebenzi job_id egcina ngegama elithi 'CLERK'
Isixazululo
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';
Ithebula lezisebenzi. Thola iholo eliphezulu phakathi kwawo wonke amaholo amaphakathi omnyango
Isixazululo
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Ithebula lezisebenzi. Thola inombolo yezisebenzi ezinenombolo efanayo yezinhlamvu egameni labo. Ngesikhathi esifanayo, bonisa kuphela labo ubude bamagama abo bungaphezu kuka-5 kanye nenani labasebenzi abanegama elifanayo lingaphezu kwama-20. Hlunga ngobude bamagama
Isixazululo
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);
Ibonisa Idatha Kumathebula Amaningi Kusetshenziswa Ukujoyina
Ithebula Abasebenzi, Iminyango, Izindawo, Amazwe, Izifunda. Thola uhlu lwezifunda kanye nenani labasebenzi esifundeni ngasinye
Isixazululo
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;
Ithebula Abasebenzi, Iminyango, Izindawo, Amazwe, Izifunda. Thola ulwazi oluningiliziwe mayelana nomsebenzi ngamunye:
Igama_lokuqala, Isibongo, Umnyango, Umsebenzi, Umgwaqo, Izwe, Isifunda
Isixazululo
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);
Ithebula lezisebenzi. Bonisa bonke abaphathi abanabasebenzi abangaphezu kuka-6 abangaphansi kwabo
Isixazululo
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;
Ithebula lezisebenzi. Bonisa bonke abasebenzi abangabiki kunoma ubani
Isixazululo
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;
Izisebenzi Zethebula, Umlando_wemisebenzi. Itafula labasebenzi ligcina bonke abasebenzi. Ithebula le-Job_history ligcina abasebenzi abashiye inkampani. Thola umbiko mayelana nabo bonke abasebenzi kanye nesimo sabo enkampanini (Isebenza noma ishiye inkampani nosuku lokuhamba)
Isibonelo:
igama_lokuqala | isimo
Jennifer | Ushiye inkampani ngomhla zingama-31 kuZibandlela, 2006
Clara | Okwamanje Iyasebenza
Isixazululo
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);
Ithebula Abasebenzi, Iminyango, Izindawo, Amazwe, Izifunda. Thola uhlu lwabasebenzi abahlala e-Europe (region_name)
Isixazululo
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';
Abasebenzi basetafuleni, iminyango. Bonisa yonke iminyango enabasebenzi abangaphezu kuka-30
Isixazululo
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Abasebenzi basetafuleni, iminyango. Bonisa bonke abasebenzi abangekho kunoma yimuphi umnyango
Isixazululo
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;
Abasebenzi basetafuleni, iminyango. Bonisa yonke iminyango engenabasebenzi kuyo
Isixazululo
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Ithebula lezisebenzi. Bonisa bonke abasebenzi abangenaye umuntu ongaphansi kwabo
Isixazululo
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Abasebenzi basetafuleni, Imisebenzi, Iminyango. Bonisa abasebenzi ngefomethi: First_name, Job_title, Department_name.
Isibonelo:
Igama_lokuqala | Isihloko_somsebenzi | Igama_lomnyango
Donald | Ukuthumela | Umabhalane Shipping
Isixazululo
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);
Ithebula lezisebenzi. Thola uhlu lwabasebenzi abaphathi babo abathola umsebenzi ngo-2005, kodwa ngesikhathi esifanayo laba basebenzi bathola umsebenzi ngaphambi kuka-2005.
Isixazululo
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');
Ithebula lezisebenzi. Thola uhlu lwezisebenzi abaphathi bazo abathole umsebenzi ngoJanuwari wanoma yimuphi unyaka futhi ubude be-job_title balaba basebenzi bungaphezu kwezinhlamvu ezingu-15
Isixazululo
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;
Ukusebenzisa i-Subqueries ukuxazulula imibuzo
Ithebula lezisebenzi. Thola uhlu lwezisebenzi ezinegama elide kakhulu.
Isixazululo
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Ithebula lezisebenzi. Thola uhlu lwezisebenzi ezineholo elikhulu kunesilinganiso seholo labo bonke abasebenzi.
Isixazululo
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Abasebenzi, iminyango, ithebula lezindawo. Thola idolobha lapho abasebenzi sebebonke bahola kancane.
Isixazululo
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);
Ithebula lezisebenzi. Thola uhlu lwabasebenzi abaphathi babo abathola iholo elingaphezu kuka-15000.
Isixazululo
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Abasebenzi basetafuleni, iminyango. Bonisa yonke iminyango engenabasebenzi kuyo
Isixazululo
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Ithebula lezisebenzi. Bonisa bonke abasebenzi abangebona abaphathi
Isixazululo
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Ithebula lezisebenzi. Bonisa bonke abaphathi abanabasebenzi abangaphezu kuka-6 abangaphansi kwabo
Isixazululo
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Abasebenzi basetafuleni, iminyango. Bonisa abasebenzi abasebenza emnyangweni we-IT
Isixazululo
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Abasebenzi basetafuleni, Imisebenzi, Iminyango. Bonisa abasebenzi ngefomethi: First_name, Job_title, Department_name.
Isibonelo:
Igama_lokuqala | Isihloko_somsebenzi | Igama_lomnyango
Donald | Ukuthumela | Umabhalane Shipping
Isixazululo
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;
Ithebula lezisebenzi. Thola uhlu lwabasebenzi abaphathi babo abathola umsebenzi ngo-2005, kodwa ngesikhathi esifanayo laba basebenzi bathola umsebenzi ngaphambi kuka-2005.
Isixazululo
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');
Ithebula lezisebenzi. Thola uhlu lwezisebenzi abaphathi bazo abathole umsebenzi ngoJanuwari wanoma yimuphi unyaka futhi ubude be-job_title balaba basebenzi bungaphezu kwezinhlamvu ezingu-15
Isixazululo
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;
Yilokho kuphela okwamanje.
Ngethemba ukuthi imisebenzi ibithakazelisa futhi ijabulisa.
Ngizokwengeza kulolu hlu lwemisebenzi ngangokunokwenzeka.
Ngizophinde ngijabule ukwamukela noma yimiphi imibono neziphakamiso.
I-PS: Uma othile eza nomsebenzi othakazelisayo KHETHA, bhala kumazwana futhi ngizowengeza ohlwini.
Π‘ΠΏΠ°ΡΠΈΠ±ΠΎ.
Source: www.habr.com