Hello Habr!
Fun diẹ sii ju ọdun 3 Mo ti nkọ SQL ni awọn ile-iṣẹ ikẹkọ lọpọlọpọ, ati ọkan ninu awọn akiyesi mi ni pe awọn ọmọ ile-iwe ni oye ati oye SQL dara julọ ti wọn ba fun wọn ni iṣẹ-ṣiṣe kan, kii ṣe sọrọ nipa awọn iṣeeṣe ati awọn ipilẹ imọ-jinlẹ.
Ninu nkan yii, Emi yoo pin pẹlu rẹ atokọ awọn iṣẹ ṣiṣe ti Mo fun awọn ọmọ ile-iwe bi iṣẹ amurele ati lori eyiti a ṣe ọpọlọpọ iru awọn ọpọlọ ọpọlọ, eyiti o yori si oye ti o jinlẹ ati oye ti SQL.
SQL (ˈɛsˈkjuˈɛl; eng. Èdè ìbéèrè tí a ṣe ìkéde) jẹ́ èdè ìkéde ìkéde tí a lò láti ṣẹ̀dá, ṣàtúnṣe àti láti ṣàkóso dátà nínú ibùdó dátà ìbátan tí a ń ṣàkóso nípasẹ̀ ètò ìṣàkóso ibùdó dátà tó yẹ.
O le ka nipa SQL lati oriṣiriṣi .
Nkan yii kii ṣe ipinnu lati kọ ọ SQL lati ibere.
Nitorinaa jẹ ki a lọ.
A yoo lo awọn daradara-mọ ni Oracle pẹlu awọn tabili rẹ ():
Mo ṣe akiyesi pe a yoo gbero awọn iṣẹ-ṣiṣe nikan lori Yan. Ko si awọn iṣẹ-ṣiṣe lori DML ati DDL.
Awọn iṣẹ-ṣiṣe
Idinamọ ati Titọ Data
Abáni tabili. Gba akojọ kan pẹlu alaye nipa gbogbo awọn oṣiṣẹ
Ipinnu
SELECT * FROM employees
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti a npè ni 'David'
Ipinnu
SELECT *
FROM employees
WHERE first_name = 'David';
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ pẹlu job_id dọgba si 'IT_PROG'
Ipinnu
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ lati ẹka 50th (department_id) pẹlu owo osu (oya) ti o tobi ju 4000
Ipinnu
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ lati 20th ati lati ẹka 30th (department_id)
Ipinnu
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti lẹta ikẹhin ni orukọ wọn jẹ 'a'
Ipinnu
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ lati 50th ati lati ẹka 80th (department_id) ti o ni ẹbun (iye ninu iwe Commission_pct ko ṣofo)
Ipinnu
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti orukọ wọn ni o kere ju awọn lẹta 2 'n' ninu.
Ipinnu
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti orukọ wọn gun ju awọn lẹta mẹrin lọ
Ipinnu
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti owo-osu wọn wa laarin 8000 ati 9000 (pẹlu)
Ipinnu
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti orukọ wọn ni aami ninu '%'
Ipinnu
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Abáni tabili. Gba atokọ ti gbogbo awọn ID oluṣakoso
Ipinnu
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ pẹlu awọn ipo wọn ni ọna kika: Donald (sh_clerk)
Ipinnu
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Lilo Awọn iṣẹ Ọna Kan-kan lati Ṣe akanṣe Ijade
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti orukọ wọn gun ju awọn lẹta mẹrin lọ
Ipinnu
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti o ni lẹta 'b' ni orukọ wọn (ọran aibikita)
Ipinnu
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti orukọ wọn ni o kere ju awọn lẹta 2 'a'
Ipinnu
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti owo-osu wọn jẹ ọpọ ti 1000
Ipinnu
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Abáni tabili. Gba nọmba oni-nọmba mẹta akọkọ ti nọmba foonu oṣiṣẹ ti nọmba rẹ ba wa ni ọna kika ХХХ.ХХХ.ХХХХ
Ipinnu
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Eka tabili. Gba ọrọ akọkọ lati orukọ ẹka fun awọn ti o ni ọrọ ju ọkan lọ ni orukọ naa
Ipinnu
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Abáni tabili. Gba awọn orukọ oṣiṣẹ laisi lẹta akọkọ ati ikẹhin ni orukọ
Ipinnu
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti lẹta ikẹhin ninu orukọ jẹ dọgba si 'm' ati ipari orukọ naa tobi ju 5 lọ.
Ipinnu
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Tabili meji. Gba ọjọ Jimọ ti nbọ
Ipinnu
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti o ti wa pẹlu ile-iṣẹ fun ọdun 17 ti o ju
Ipinnu
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti nọmba ikẹhin ti nọmba foonu jẹ aiṣedeede ati pe o ni awọn nọmba 3 ti o yapa nipasẹ aami kan.
Ipinnu
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti iye iṣẹ_id lẹhin ami '_' ni o kere ju awọn kikọ mẹta, ṣugbọn iye yii lẹhin '_' ko dọgba si 'CLERK'
Ipinnu
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ nipa rirọpo gbogbo '.' ni iye PHONE_NUMBER lori '-'
Ipinnu
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Lilo Awọn iṣẹ Iyipada ati Awọn ikosile Ni majemu
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti o wa lati ṣiṣẹ ni ọjọ akọkọ ti oṣu (eyikeyi)
Ipinnu
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti o wa lati ṣiṣẹ ni ọdun 2008
Ipinnu
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
DUAL tabili. Ṣe afihan ọjọ ọla ni ọna kika: Ọla jẹ ọjọ keji ti Oṣu Kini
Ipinnu
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ati ọjọ ibẹrẹ wọn ni ọna kika: 21st ti Oṣu kẹfa, ọdun 2007
Ipinnu
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ pẹlu awọn owo osu ti o pọ si nipasẹ 20%. Ṣe afihan owo osu pẹlu ami dola
Ipinnu
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ti o wa lati ṣiṣẹ ni Kínní 2007.
Ipinnu
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 tabili. Ṣe okeere ọjọ lọwọlọwọ, + iṣẹju-aaya, + iṣẹju, + wakati, + ọjọ, + oṣu, + ọdun
Ipinnu
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;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ pẹlu owo osu ni kikun (ekunwo + commission_pct(%)) ni ọna kika: $24,000.00
Ipinnu
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Abáni tabili. Gba atokọ ti gbogbo awọn oṣiṣẹ ati alaye nipa wiwa awọn ẹbun owo-oṣu (Bẹẹni / Bẹẹkọ)
Ipinnu
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Abáni tabili. Gba ipele oya ti oṣiṣẹ kọọkan: Kere ju 5000 ni a gba pe ipele kekere, Ti o tobi ju tabi dogba si 5000 ati pe o kere ju 10000 ni a gba ni ipele deede, Ti o tobi ju tabi dogba si 10000 ni a gba ni ipele giga
Ipinnu
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;
tabili awọn orilẹ-ede. Fun orilẹ-ede kọọkan, ṣafihan agbegbe ti o wa: 1-Europe, 2-America, 3-Asia, 4-Africa (laisi Darapọ mọ)
Ipinnu
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;
Ijabọ Data Akopọ Lilo Awọn iṣẹ Ẹgbẹ
Abáni tabili. Gba ijabọ nipasẹ ẹka_id pẹlu owo osu ti o kere julọ ati ti o pọju, tete ati awọn ọjọ dide ti pẹ ati nọmba awọn oṣiṣẹ. Too nipasẹ nọmba awọn oṣiṣẹ (desc)
Ipinnu
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;
Abáni tabili. Awọn oṣiṣẹ melo ni orukọ wọn bẹrẹ pẹlu lẹta kanna? Too nipa opoiye. Ṣe afihan awọn nikan nibiti nọmba naa ti tobi ju 1 lọ
Ipinnu
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;
Abáni tabili. Awọn oṣiṣẹ melo ni o ṣiṣẹ ni ẹka kanna ati gba owo osu kanna?
Ipinnu
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Abáni tabili. Gba ijabọ ti iye awọn oṣiṣẹ ti a gba ni ọjọ kọọkan ti ọsẹ. Too nipa opoiye
Ipinnu
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Abáni tabili. Gba ijabọ lori iye awọn oṣiṣẹ ti a gba nipasẹ ọdun. Too nipa opoiye
Ipinnu
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Abáni tabili. Gba nọmba awọn ẹka ti o ni awọn oṣiṣẹ
Ipinnu
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Abáni tabili. Gba atokọ ti ẹka_id pẹlu diẹ sii ju awọn oṣiṣẹ 30 lọ
Ipinnu
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Abáni tabili. Gba atokọ ti awọn ẹka_ids ati apapọ owo-oṣu apapọ ti awọn oṣiṣẹ ni ẹka kọọkan.
Ipinnu
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
tabili awọn orilẹ-ede. Gba atokọ ti agbegbe_id apao ti gbogbo awọn lẹta ti gbogbo awọn orukọ orilẹ-ede ninu eyiti o ju 60 lọ
Ipinnu
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Abáni tabili. Gba atokọ ti ẹka_id ninu eyiti awọn oṣiṣẹ ti ọpọlọpọ (> 1) job_id ṣiṣẹ
Ipinnu
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Abáni tabili. Gba atokọ ti manager_id ti nọmba awọn alabojuto rẹ tobi ju 5 ati apapọ gbogbo awọn owo osu ti awọn ọmọ abẹ rẹ tobi ju 50000 lọ
Ipinnu
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Abáni tabili. Gba atokọ ti manager_id ti apapọ owo-osu ti gbogbo awọn ti o wa labẹ rẹ wa laarin 6000 ati 9000 ti ko gba awọn ẹbun (commission_pct jẹ ofo)
Ipinnu
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;
Abáni tabili. Gba owo osu ti o pọju lati ọdọ gbogbo awọn oṣiṣẹ job_id ti o pari pẹlu ọrọ 'CLERK'
Ipinnu
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';
Abáni tabili. Gba owo osu ti o pọju laarin gbogbo awọn owo osu apapọ fun ẹka naa
Ipinnu
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Abáni tabili. Gba nọmba awọn oṣiṣẹ pẹlu nọmba kanna ti awọn lẹta ni orukọ wọn. Ni akoko kanna, fihan nikan awọn ti orukọ wọn gun ju 5 lọ ati pe nọmba awọn oṣiṣẹ pẹlu orukọ kanna jẹ diẹ sii ju 20. Too nipasẹ ipari orukọ
Ipinnu
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);
Ifihan Data lati Awọn tabili pupọ Lilo Awọn Isopọpọ
Awọn oṣiṣẹ tabili, Awọn ẹka, Awọn agbegbe, Awọn orilẹ-ede, Awọn agbegbe. Gba atokọ ti awọn agbegbe ati nọmba awọn oṣiṣẹ ni agbegbe kọọkan
Ipinnu
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;
Awọn oṣiṣẹ tabili, Awọn ẹka, Awọn agbegbe, Awọn orilẹ-ede, Awọn agbegbe. Gba alaye alaye nipa oṣiṣẹ kọọkan:
Orukọ_akọkọ, Oruko idile, Ẹka, Iṣẹ, opopona, Orilẹ-ede, Ẹkun
Ipinnu
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);
Abáni tabili. Ṣe afihan gbogbo awọn alakoso ti o ni diẹ sii ju awọn oṣiṣẹ 6 lọ
Ipinnu
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;
Abáni tabili. Ṣe afihan gbogbo awọn oṣiṣẹ ti ko ṣe ijabọ si ẹnikẹni
Ipinnu
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;
Tabili abáni, Job_history. Tabili Oṣiṣẹ tọju gbogbo awọn oṣiṣẹ. Tabili Job_history tọju awọn oṣiṣẹ ti o lọ kuro ni ile-iṣẹ naa. Gba ijabọ kan lori gbogbo awọn oṣiṣẹ ati ipo wọn ni ile-iṣẹ naa (Ṣiṣe tabi fi ile-iṣẹ silẹ pẹlu ọjọ ti ilọkuro)
Apeere:
oruko_akọkọ | ipo
jennifer | Fi ile-iṣẹ silẹ ni 31 Oṣu kejila, ọdun 2006
Clara | Lọwọlọwọ Ṣiṣẹ
Ipinnu
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);
Awọn oṣiṣẹ tabili, Awọn ẹka, Awọn agbegbe, Awọn orilẹ-ede, Awọn agbegbe. Gba atokọ ti awọn oṣiṣẹ ti o ngbe ni Yuroopu (orukọ agbegbe)
Ipinnu
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';
Table Employees, Eka. Ṣe afihan gbogbo awọn ẹka pẹlu diẹ sii ju awọn oṣiṣẹ 30 lọ
Ipinnu
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Table Employees, Eka. Ṣe afihan gbogbo awọn oṣiṣẹ ti ko si ni ẹka eyikeyi
Ipinnu
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;
Table Employees, Eka. Ṣe afihan gbogbo awọn ẹka laisi oṣiṣẹ
Ipinnu
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Abáni tabili. Ṣe afihan gbogbo awọn oṣiṣẹ ti ko ni awọn abẹlẹ
Ipinnu
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Table Employees, Jobs, Eka. Fi awọn oṣiṣẹ han ni ọna kika: First_name, Job_title, Department_name.
Apeere:
oruko_akọkọ | akọle iṣẹ | Orukọ_Ẹka
Donald | sowo | Sowo Akọwe
Ipinnu
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);
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ ti awọn alakoso gba iṣẹ ni ọdun 2005, ṣugbọn ni akoko kanna, awọn oṣiṣẹ wọnyi funrararẹ gba iṣẹ ṣaaju ọdun 2005.
Ipinnu
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');
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ ti awọn alakoso gba iṣẹ ni oṣu Oṣu Kini ti ọdun eyikeyi ati ipari ti akọle iṣẹ ti awọn oṣiṣẹ wọnyi jẹ diẹ sii ju awọn kikọ 15 lọ.
Ipinnu
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;
Lilo Awọn ibeere lati yanju awọn ibeere
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ pẹlu orukọ to gunjulo.
Ipinnu
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ pẹlu owo-oṣu ti o tobi ju owo-oṣu apapọ ti gbogbo awọn oṣiṣẹ lọ.
Ipinnu
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Awọn oṣiṣẹ tabili, Awọn ẹka, Awọn ipo. Gba ilu ti awọn oṣiṣẹ gba o kere ju lapapọ.
Ipinnu
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);
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ ti oluṣakoso rẹ gba owo-oṣu ti o ju 15000 lọ.
Ipinnu
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Table Employees, Eka. Ṣe afihan gbogbo awọn ẹka laisi oṣiṣẹ
Ipinnu
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Abáni tabili. Ṣe afihan gbogbo awọn oṣiṣẹ ti kii ṣe alakoso
Ipinnu
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Abáni tabili. Ṣe afihan gbogbo awọn alakoso ti o ni diẹ sii ju awọn oṣiṣẹ 6 lọ
Ipinnu
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Table Employees, Eka. Ṣe afihan awọn oṣiṣẹ ti o ṣiṣẹ ni ẹka IT
Ipinnu
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Table Employees, Jobs, Eka. Fi awọn oṣiṣẹ han ni ọna kika: First_name, Job_title, Department_name.
Apeere:
oruko_akọkọ | akọle iṣẹ | Orukọ_Ẹka
Donald | sowo | Sowo Akọwe
Ipinnu
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;
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ ti awọn alakoso gba iṣẹ ni ọdun 2005, ṣugbọn ni akoko kanna, awọn oṣiṣẹ wọnyi funrararẹ gba iṣẹ ṣaaju ọdun 2005.
Ipinnu
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');
Abáni tabili. Gba atokọ ti awọn oṣiṣẹ ti awọn alakoso gba iṣẹ ni oṣu Oṣu Kini ti ọdun eyikeyi ati ipari ti akọle iṣẹ ti awọn oṣiṣẹ wọnyi jẹ diẹ sii ju awọn kikọ 15 lọ.
Ipinnu
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;
Iyẹn ni gbogbo fun bayi.
Mo lero awọn iṣẹ-ṣiṣe wà awon ati ki o moriwu.
Emi yoo ṣafikun si atokọ yii bi o ti ṣee ṣe.
Emi yoo tun jẹ dun si eyikeyi comments ati awọn didaba.
PS: Ti ẹnikan ba wa pẹlu iṣẹ ti o nifẹ lori Yan, kọ sinu awọn asọye, Emi yoo ṣafikun si atokọ naa.
O ṣeun.
orisun: www.habr.com