Molo Habr!
Ngaphezulu kweminyaka emi-3 bendifundisa iSQL kumaziko oqeqesho ahlukeneyo, kwaye enye into endiyiqwalaseleyo kukuba abafundi bayakwazi kwaye baqonde ngcono iSQL ukuba banikwe umsebenzi, kwaye bangathethi nje ngamathuba kunye neziseko zethiyori.
Kweli nqaku, ndiza kubelana nawe ngoluhlu lwam lwemisebenzi endiyinika abafundi njengomsebenzi wasekhaya kwaye apho siqhuba khona iintlobo ngeentlobo zeengqondo, ezikhokelela ekuqondeni okunzulu nokucacileyo kweSQL.
I-SQL (ˈɛsˈkjuˈɛl; Ulwimi lombuzo olwakhiwe ngesiNgesi - “ulwimi lombuzo olucwangcisiweyo”) lulwimi oluchazayo lwenkqubo olusetyenziselwa ukuyila, ukuguqula nokulawula idatha kuvimba weenkcukacha onxibeleleneyo olawulwa yinkqubo efanelekileyo yolawulo lwesiseko sedatha.
Unokufunda malunga neSQL ukusuka ezahlukeneyo .
Eli nqaku ayenzelwanga ukukufundisa iSQL ukusuka ekuqaleni.
Masihambe ke.
Siza kusebenzisa owaziwayo kwiOracle neetafile zayo ():
Ndiqaphela ukuba siza kuthathela ingqalelo kuphela imisebenzi ku-KHETHA. Akukho misebenzi kwi-DML kunye ne-DDL.
Iinjongo
Ukumisela imiqathango kunye nokuhlela iDatha
Itafile yabasebenzi. Fumana uluhlu olunolwazi malunga nabo bonke abasebenzi
Isisombululo
SELECT * FROM employees
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi ababizwa ngokuba 'nguDavid'
Isisombululo
SELECT *
FROM employees
WHERE first_name = 'David';
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abane job_id elingana ne 'IT_PROG'
Isisombululo
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi kwisebe lama-50 (kwisebe_id) abanomvuzo (umvuzo) ongaphezu kwama-4000
Isisombululo
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi ukusuka kwi-20 nakwisebe lama-30 (isebe_id)
Isisombululo
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanobumba bokugqibela egameni labo ngu 'a'
Isisombululo
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abavela kwi-50 kunye nakwisebe le-80 (isebe_id) abanebhonasi (ixabiso kwikholamu ye-commission_pct ayinanto)
Isisombululo
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abamagama abo aqulethe ubuncinane oonobumba aba-2 'n'
Isisombululo
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abagama lide kunoonobumba aba-4
Isisombululo
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanomvuzo ophakathi kwe-8000 kunye ne-9000 (iquka)
Isisombululo
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanamagama aqulathe uphawu '%'
Isisombululo
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Itafile yabasebenzi. Fumana uluhlu lwazo zonke ii-ID zabaphathi
Isisombululo
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abanezikhundla zabo ngendlela: uDonald(sh_clerk)
Isisombululo
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Ukusebenzisa iMisebenzi yomqolo omnye ukwenza iSiphumo siSiko
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abagama lide kunoonobumba aba-10
Isisombululo
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanobumba 'b' egameni labo (akukho mvakalelo)
Isisombululo
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abamagama abo aqulethe ubuncinane oonobumba aba-2 'a'
Isisombululo
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanomvuzo ophindaphindwe ka-1000
Isisombululo
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Itafile yabasebenzi. Fumana inombolo yokuqala enamanani ama-3 kwinombolo yefowuni yomqeshwa ukuba inombolo yakhe ikwifomathi ХХХ.ХХХ.ХХХХ
Isisombululo
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Itheyibhile yamaSebe. Fumana igama lokuqala kwigama lesebe labo banamagama angaphezu kwelinye egameni
Isisombululo
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Itafile yabasebenzi. Fumana amagama abasebenzi ngaphandle kweleta yokuqala neyokugqibela egameni
Isisombululo
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanonobumba wokugqibela egameni olingana no 'm' kwaye ubude begama bukhulu kuno-5
Isisombululo
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Itafile ezimbini. Fumana umhla wangoLwesihlanu olandelayo
Isisombululo
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi ababekunye nenkampani iminyaka engaphezu kwe-17
Isisombululo
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanedijithi yokugqibela yenombolo yefowuni engaqhelekanga kwaye ineenombolo ezi-3 ezahlulwe ngamachaphaza.
Isisombululo
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi umsebenzi_id ixabiso emva kophawu '_' lunamagama ubuncinane ama-3, kodwa eli xabiso emva kwe '_' alilingani no 'CLERK'
Isisombululo
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi ngokubuyisela bonke '.' kwi- PHONE_NUMBER ixabiso kwi-'-'
Isisombululo
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Ukusebenzisa iMisebenzi yoGuqulo kunye neMibonakaliso eneMiqathango
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abeze emsebenzini ngosuku lokuqala lwenyanga (nayiphi na)
Isisombululo
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abeze emsebenzini ngo-2008
Isisombululo
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
itafile DUAL. Bonisa umhla wangomso ngendlela: Ngomso ngumhla weSibini kuJanuwari
Isisombululo
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi kunye nomhla wabo wokuqalisa ngokwefomathi: 21st kaJuni, 2007
Isisombululo
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abanyuswe imivuzo ngama-20%. Bonisa umvuzo ngophawu lwedola
Isisombululo
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abeze emsebenzini ngoFebruwari 2007.
Isisombululo
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';
itafile DUAL. Thumela ngaphandle umhla wangoku, + okwesibini, + umzuzu, + iyure, + usuku, + inyanga, + unyaka
Isisombululo
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;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi abanemivuzo epheleleyo (umvuzo + ikhomishini_pct(%)) kwifomathi: $24,000.00
Isisombululo
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Itafile yabasebenzi. Fumana uluhlu lwabo bonke abasebenzi kunye nolwazi malunga nokufumaneka kweebhonasi zemivuzo (Ewe / Hayi)
Isisombululo
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Itafile yabasebenzi. Fumana inqanaba lomvuzo womqeshwa ngamnye: Ngaphantsi kwe-5000 ithathwa njengenqanaba eliphantsi, Elikhulu kune okanye elilingana ne-5000 kwaye ngaphantsi kwe-10000 ithathwa njengeNqanaba eliQhelekileyo, Elikhulu okanye elilingana ne-10000 lithathwa njengenqanaba eliphezulu.
Isisombululo
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;
Itheyibhile yamazwe. Kwilizwe ngalinye, bonisa indawo ekuyo: 1-Europe, 2-America, 3-Asia, 4-Afrika (ngaphandle kokujoyina)
Isisombululo
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;
Ukunika ingxelo ngeDatha eDityanisiweyo kusetyenziswa imiSebenzi yeQela
Itafile yabasebenzi. Fumana ingxelo nge-department_id enomvuzo omncinci kunye nowona mkhulu, imihla yokufika kwangethuba nangemva kwexesha kunye nenani labasebenzi. Hlela ngenani labasebenzi (desc)
Isisombululo
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;
Itafile yabasebenzi. Bangaphi abasebenzi abamagama aqala ngonobumba omnye? Hlela ngokobungakanani. Bonisa kuphela ezo apho inani likhulu kuno-1
Isisombululo
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;
Itafile yabasebenzi. Bangaphi abasebenzi abasebenza kwisebe elinye kwaye bafumana umvuzo ofanayo?
Isisombululo
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Itafile yabasebenzi. Fumana ingxelo yokuba bangaphi abasebenzi abaqeshiweyo ngosuku ngalunye lweveki. Hlela ngokobungakanani
Isisombululo
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Itafile yabasebenzi. Fumana ingxelo yokuba bangaphi na abasebenzi abaqeshwe ngonyaka. Hlela ngokobungakanani
Isisombululo
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Itafile yabasebenzi. Fumana inani lamasebe anabasebenzi
Isisombululo
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Itafile yabasebenzi. Fumana uluhlu lwe department_id enabasebenzi abangaphezu kwama-30
Isisombululo
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Itafile yabasebenzi. Fumana uluhlu lwe- department_ids kunye ne-avareji eyi-avareji yomvuzo wabasebenzi kwisebe ngalinye.
Isisombululo
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Itheyibhile yamazwe. Fumana uluhlu lwengingqi_id isixa sabo bonke oonobumba bazo zonke ilizwe_amagama apho ngaphezu kwama-60
Isisombululo
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Itafile yabasebenzi. Fumana uluhlu lwe department_id apho abasebenzi abaninzi (>1) job_id basebenza khona
Isisombululo
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Itafile yabasebenzi. Fumana uluhlu lwe-manager_id elinenani labangaphantsi lingaphezulu kwe-5 kwaye isixa sayo yonke imivuzo yabaphantsi kwakhe sikhulu kune-50000.
Isisombululo
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Itafile yabasebenzi. Fumana uluhlu lwe manager_id onomvuzo ophakathi kwabo bonke abaphantsi kwakhe uphakathi kwama-6000 kunye nama-9000 abangafumani bhonasi (icommission_pct ayinanto)
Isisombululo
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;
Itafile yabasebenzi. Fumana owona mvuzo uphezulu kubo bonke abasebenzi job_id ophela ngegama 'CLERK'
Isisombululo
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';
Itafile yabasebenzi. Fumana owona mvuzo uphezulu kuyo yonke i-avareji yemivuzo yesebe
Isisombululo
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Itafile yabasebenzi. Fumana inani labasebenzi abanenani elifanayo loonobumba egameni labo. Kwangaxeshanye, bonisa kuphela abo ubude bamagama bungaphezulu kwe-5 kwaye inani labasebenzi abanegama elifanayo lingaphezulu kwama-20. Hlela ngobude bamagama
Isisombululo
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);
Ukubonisa iDatha kwiiThebhile ezininzi usebenzisa ukudibanisa
Abasebenzi beTheyibhile, amaSebe, iiNdawo, amazwe, iiNgingqi. Fumana uluhlu lwemimandla kunye nenani labasebenzi kwingingqi nganye
Isisombululo
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;
Abasebenzi beTheyibhile, amaSebe, iiNdawo, amazwe, iiNgingqi. Fumana ulwazi oluneenkcukacha malunga nomsebenzi ngamnye:
Igama_lokuqala, ifani, iSebe, umsebenzi, isitalato, ilizwe, iNgingqi
Isisombululo
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);
Itafile yabasebenzi. Bonisa bonke abaphathi abanabasebenzi abangaphezu kwe-6 abangaphantsi kwabo
Isisombululo
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;
Itafile yabasebenzi. Bonisa bonke abasebenzi abanganikezi ngxelo nakubani na
Isisombululo
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;
Itafile yabasebenzi, imbali_yemisebenzi. Itafile yabaSebenzi igcina bonke abasebenzi. Itafile ye-Job_history igcina abasebenzi abayishiyileyo inkampani. Fumana ingxelo kubo bonke abasebenzi kunye nesimo sabo kwinkampani (Uqeshwe okanye ushiye inkampani kunye nomhla wokuhamba)
Umzekelo:
igama_lokuqala | ubume
uJennifer | Ishiye inkampani nge-31 kaDisemba, 2006
Clara | Ngoku iyasebenza
Isisombululo
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);
Abasebenzi beTheyibhile, amaSebe, iiNdawo, amazwe, iiNgingqi. Fumana uluhlu lwabasebenzi abahlala eYurophu (region_name)
Isisombululo
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 abakwiiThebhile, amaSebe. Bonisa onke amasebe anabasebenzi abangaphezu kwama-30
Isisombululo
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 baseTheyibhile, amaSebe. Bonisa bonke abasebenzi abangekho kulo naliphi na isebe
Isisombululo
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 baseTheyibhile, amaSebe. Bonisa onke amasebe angenabasebenzi
Isisombululo
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Itafile yabasebenzi. Bonisa bonke abasebenzi abangenabasebenzi abangaphantsi kwabo
Isisombululo
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 baseTheyibhile, iMisebenzi, amaSebe. Bonisa abasebenzi ngendlela: First_name, Job_title, Department_name.
Umzekelo:
igama_lokuqala | isihloko somsebenzi | Igama_leSebe
UDonald | ngenqanawa | UMabhalane ngeShipping
Isisombululo
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);
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abaphathi babo bafumana umsebenzi ngo-2005, kodwa kwangaxeshanye, aba basebenzi bafumana umsebenzi ngaphambi kuka-2005.
Isisombululo
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');
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abaphathi babo bafumene umsebenzi kwinyanga kaJanuwari nawuphi na unyaka kwaye ubude be-job_title yaba basebenzi bungaphezulu kwe-15 characters
Isisombululo
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 iiSubqueries ukuSombulula imibuzo
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abanelona gama lide.
Isisombululo
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abanomvuzo omkhulu kunomyinge womvuzo wabo bonke abasebenzi.
Isisombululo
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Abaqeshwa kwiTheyibhile, amaSebe, iiNdawo. Fumana isixeko apho abasebenzi barhola ubuncinci bebonke.
Isisombululo
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);
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abangumphathi wabo ofumana umvuzo ongaphezulu kwe-15000.
Isisombululo
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Abasebenzi baseTheyibhile, amaSebe. Bonisa onke amasebe angenabasebenzi
Isisombululo
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Itafile yabasebenzi. Bonisa bonke abasebenzi abangengabo abaphathi
Isisombululo
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Itafile yabasebenzi. Bonisa bonke abaphathi abanabasebenzi abangaphezu kwe-6 abangaphantsi kwabo
Isisombululo
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Abasebenzi abakwiiThebhile, amaSebe. Bonisa abasebenzi abasebenza kwisebe le-IT
Isisombululo
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Abasebenzi baseTheyibhile, iMisebenzi, amaSebe. Bonisa abasebenzi ngendlela: First_name, Job_title, Department_name.
Umzekelo:
igama_lokuqala | isihloko somsebenzi | Igama_leSebe
UDonald | ngenqanawa | UMabhalane ngeShipping
Isisombululo
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;
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abaphathi babo bafumana umsebenzi ngo-2005, kodwa kwangaxeshanye, aba basebenzi bafumana umsebenzi ngaphambi kuka-2005.
Isisombululo
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');
Itafile yabasebenzi. Fumana uluhlu lwabasebenzi abaphathi babo bafumene umsebenzi kwinyanga kaJanuwari nawuphi na unyaka kwaye ubude be-job_title yaba basebenzi bungaphezulu kwe-15 characters
Isisombululo
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;
Kuphelele apho okwangoku.
Ndiyathemba ukuba imisebenzi ibinika umdla kwaye inika umdla.
Ndiya kongeza kolu luhlu kangangoko kunokwenzeka.
Nam ndiya kuvuya kuzo naziphi na izimvo kunye neengcebiso.
I-PS: Ukuba umntu uza nomsebenzi onomdla kwi-KHETHA, bhala kwizimvo, ndiya kukongeza kuloluhlu.
Спасибо.
umthombo: www.habr.com