Kia ora Habr!
Neke atu i te 3 tau e ako ana ahau i te SQL i roto i nga tini whare whakangungu, a, ko tetahi o aku kitenga he pai ake te mohio o nga akonga ki te SQL mena ka whakawhiwhia ki a raatau he mahi, kaua e korero noa mo nga tupono me nga turanga ariā.
I roto i tenei tuhinga, ka korero atu ahau ki a koe i taku rarangi mahi ka hoatu e au ki nga akonga hei mahi kainga, ka whakahaerehia e matou nga momo momo whakaaro, ka puta te maaramatanga hohonu me te maarama ki te SQL.
Ko te SQL (ˈɛsˈkjuˈɛl; eng. te reo uiui hanganga) he reo hötaka whakapuakanga e whakamahia ana ki te hanga, ki te whakarerekë me te whakahaere raraunga i roto i tëtahi pätengi raraunga hononga e whakahaeretia ana e tëtahi pünaha whakahaere raraunga tika.
Ka taea e koe te panui mo te SQL mai i nga rereke .
Ko tenei tuhinga ehara i te mea ki te ako i a koe i te SQL mai i te wahanga.
Na, kia haere tatou.
Ka whakamahia e matou nga mea rongonui i Oracle me ona tepu ():
Ka kite ahau ka whakaarohia e matou nga mahi anake mo te SELECT. Karekau he mahi mo DML me DDL.
mahi
Te Rahui me te Raraunga Raraunga
Tepu kaimahi. Tikina he rarangi korero mo nga kaimahi katoa
whakatau
SELECT * FROM employees
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa ko 'David'
whakatau
SELECT *
FROM employees
WHERE first_name = 'David';
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa whai job_id rite ki 'IT_PROG'
whakatau
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa mai i te tari 50 (department_id) me te utu (utu) nui ake i te 4000
whakatau
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa mai i te 20 me mai i te tari 30 (department_id)
whakatau
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa ko te reta whakamutunga i runga i o raatau ingoa ko 'a'
whakatau
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa mai i te 50th me te tari 80th (department_id) kei a raatau he putea (kaore te uara o te pouako_pct i te kau)
whakatau
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa e 2 nga reta 'n' kei roto o ratou ingoa
whakatau
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa he roa ake te ingoa i te 4 reta
whakatau
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa kei waenga i te 8000 me te 9000 to ratou utu (kei roto)
whakatau
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa kei roto i te ingoa te tohu '%'
whakatau
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Tepu kaimahi. Tikina he rarangi o nga ID kaiwhakahaere katoa
whakatau
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Tepu kaimahi. Tikina he rarangi o nga kaimahi me o raatau tuunga ki te whakatakotoranga: Donald(sh_clerk)
whakatau
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Te whakamahi i nga Mahi Rarangi-Kotahi hei Whakaritea i te Putanga
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa he roa ake te ingoa i te 10 reta
whakatau
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa kei a raatau te reta 'b' i o raatau ingoa (he koretake)
whakatau
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa e 2 nga reta 'a' kei o ratou ingoa
whakatau
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa he maha nga utu mo te 1000
whakatau
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Tepu kaimahi. Tikina te nama 3-mati tuatahi o te nama waea a te kaimahi mena kei te ahua tona nama ХХХ.ХХХ.ХХХХ
whakatau
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Tepu tari. Tikina te kupu tuatahi mai i te ingoa tari mo te hunga neke atu i te kotahi kupu i roto i te ingoa
whakatau
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Tepu kaimahi. Tikina nga ingoa kaimahi kaore he reta tuatahi me te reta whakamutunga i te ingoa
whakatau
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa e rite ana te reta whakamutunga o te ingoa ki te 'm' me te roa o te ingoa nui ake i te 5
whakatau
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Tepu takirua. Tikina te ra o te Paraire e tu mai nei
whakatau
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa kua noho ki te kamupene mo nga tau 17 neke atu
whakatau
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa he tau kee to ratou mati whakamutunga o te tau waea, e 3 nga tau kua wehea e te ira.
whakatau
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa na ratou uara_id_mahi i muri i te tohu '_' e 3 neke atu nga tohu, engari ko tenei uara i muri i te '_' kaore e rite ki te 'CLERK'
whakatau
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa ma te whakakapi i nga '.' katoa i te uara PHONE_NUMBER i runga '-'
whakatau
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Te Whakamahi i nga Mahi Huringa me nga Whakaaturanga Tikanga
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa i tae mai ki te mahi i te ra tuatahi o te marama (tetahi)
whakatau
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa i tae mai ki te mahi i te tau 2008
whakatau
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
tepu DUAL. Whakaatuhia te ra apopo ki te whakatakotoranga: Ko apopo te ra tuarua o Hanuere
whakatau
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa me o raatau ra tiimata i te whakatakotoranga: 21 o Pipiri, 2007
whakatau
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Tepu kaimahi. Tikina he rarangi o nga kaimahi kua piki ake nga utu e 20%. Whakaatuhia te utu me te tohu taara
whakatau
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa i tae mai ki te mahi i te Hui-tanguru 2007.
whakatau
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';
tepu DUAL. Kaweake te ra o naianei, + tuarua, + meneti, + haora, + ra, + marama, + tau
whakatau
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;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa me nga utu katoa (utu + commission_pct(%)) i te whakatakotoranga: $24,000.00
whakatau
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Tepu kaimahi. Tikina he rarangi o nga kaimahi katoa me nga korero mo te waatea o nga putea utu (Ae/Kao)
whakatau
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Tepu kaimahi. Tikina te taumata utu mo ia kaimahi: He iti ake i te 5000 ka kiia he taumata iti, He nui ake, he rite ranei ki te 5000 me te iti iho i te 10000 ka kiia ko te taumata noa, He nui ake, he rite ranei ki te 10000 ka kiia he taumata teitei
whakatau
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;
Tepu whenua. Mo ia whenua, whakaatu te rohe kei reira: 1-Europe, 2-Amerika, 3-Ahia, 4-Afrika (kahore he Hononga)
whakatau
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;
Te Ripoata i nga Raraunga Whakakotahi Ma te Whakamahinga Roopu
Tepu kaimahi. Tikina he ripoata ma te department_id me te utu iti me te utu nui, nga ra tae wawe me te mutunga mai me te maha o nga kaimahi. Kōmaka mā te maha o ngā kaimahi (desc)
whakatau
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;
Tepu kaimahi. Tokohia nga kaimahi e timata ana o raatau ingoa ki te reta kotahi? Kōmaka mā te rahinga. Whakaaturia ko ērā anake he nui ake te tau i te 1
whakatau
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;
Tepu kaimahi. Tokohia nga kaimahi e mahi ana i te tari kotahi, ka whiwhi i te utu kotahi?
whakatau
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Tepu kaimahi. Tikina he ripoata mo te maha o nga kaimahi i utua i ia ra o te wiki. Kōmaka mā te rahinga
whakatau
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Tepu kaimahi. Tikina he ripoata mo te maha o nga kaimahi i utua i te tau. Kōmaka mā te rahinga
whakatau
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Tepu kaimahi. Tikina te maha o nga tari e whai kaimahi ana
whakatau
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Tepu kaimahi. Tikina he rarangi o department_id me neke atu i te 30 nga kaimahi
whakatau
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Tepu kaimahi. Tikina he rarangi o nga department_ids me te utu toharite porohita o nga kaimahi o ia tari.
whakatau
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Tepu whenua. Tikina he rarangi ingoa rohe_id o nga reta katoa o nga ingoa_whenua katoa e neke atu i te 60
whakatau
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Tepu kaimahi. Tikina he rarangi o department_id e mahi ana nga kaimahi o etahi (>1) job_id
whakatau
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Tepu kaimahi. Tikina he rarangi o manager_id he nui ake te maha o nga kaimahi i te 5 me te tapeke o nga utu katoa o ana kaimahi he nui ake i te 50000
whakatau
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Tepu kaimahi. Tikina he rarangi o manager_id ko tona utu toharite o ana kaimahi katoa kei waenga i te 6000 ki te 9000 karekau e whiwhi putea (komihana_pct he kau)
whakatau
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;
Tepu kaimahi. Tikina te utu morahi mai i nga kaimahi katoa job_id ka mutu ki te kupu 'CLERK'
whakatau
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';
Tepu kaimahi. Tikina te utu morahi i waenga i nga utu toharite mo te tari
whakatau
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Tepu kaimahi. Tikina te maha o nga kaimahi e rite ana te maha o nga reta ki o raatau ingoa. I taua wa ano, whakaatu ko te hunga he roa ake o ratou ingoa i te 5 me te maha o nga kaimahi e rite ana te ingoa nui atu i te 20. Kōmakahia ma te roa o te ingoa.
whakatau
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);
Te Whakaatu Raraunga mai i nga Ripanga Maha Ma te Hononga
Kaimahi Ripanga, Tari, Tauwāhi, Whenua, Rohe. Tikina he rarangi o nga rohe me te maha o nga kaimahi i ia rohe
whakatau
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;
Kaimahi Ripanga, Tari, Tauwāhi, Whenua, Rohe. Tikina nga korero taipitopito mo ia kaimahi:
Ingoa_mua, Ingoa_whakamutunga, Tari, Mahi, Tiriti, Whenua, Rohe
whakatau
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);
Tepu kaimahi. Whakaatuhia nga kaiwhakahaere katoa neke atu i te 6 nga kaimahi
whakatau
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;
Tepu kaimahi. Whakaatuhia nga kaimahi katoa kaore e tuku korero ki tetahi
whakatau
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;
Tepu kaimahi, Job_history. Kei te tepu Kaimahi nga kaimahi katoa. Ko te ripanga Job_history e penapena ana i nga kaimahi i wehe atu i te kamupene. Tikina he ripoata mo nga kaimahi katoa me o raatau mana i roto i te kamupene (I mahi, i wehe atu ranei i te kamupene me te ra i wehe atu ai)
Hei tauira:
ingoa_mua | tūnga
jennifero | I wehe atu i te kamupene i te 31 o Hakihea, 2006
Karara | Kei te Mahi inaianei
whakatau
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);
Kaimahi Ripanga, Tari, Tauwāhi, Whenua, Rohe. Tikina he rarangi o nga kaimahi e noho ana i Uropi (rohe_ingoa)
whakatau
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';
Kaimahi Ripanga, Tari. Whakaatuhia nga tari katoa neke atu i te 30 nga kaimahi
whakatau
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Kaimahi Ripanga, Tari. Whakaatuhia nga kaimahi katoa kaore i roto i tetahi tari
whakatau
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;
Kaimahi Ripanga, Tari. Whakaatuhia nga tari katoa kaore he kaimahi
whakatau
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Tepu kaimahi. Whakaatuhia nga kaimahi katoa kaore he tangata o raro
whakatau
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Nga Kaimahi Ripanga, Nga Mahi, Nga Tari. Whakaatuhia nga kaimahi i roto i te whakatakotoranga: Ingoa_Tuamua, Taitara_mahi, Ingoa_Tuhinga.
Hei tauira:
ingoa_mua | taitara mahi | Ingoa_Tānga
Tanara | kaipuke | Kaituhi Tukunga
whakatau
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);
Tepu kaimahi. Tikina he rarangi o nga kaimahi i whai mahi nga kaiwhakahaere i te tau 2005, engari i taua wa ano, ko enei kaimahi ano i whiwhi mahi i mua i te tau 2005
whakatau
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');
Tepu kaimahi. Tikina he rarangi o nga kaimahi i whiwhi mahi a ratou kaiwhakahaere i te marama o Hanuere o ia tau me te roa o te ingoa_mahi o enei kaimahi neke atu i te 15 nga tohu
whakatau
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;
Te Whakamahi Uiuitanga hei Whakaoti Uiui
Tepu kaimahi. Tikina he rarangi o nga kaimahi me te ingoa roa rawa atu.
whakatau
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Tepu kaimahi. Tikina he rarangi o nga kaimahi me te utu nui ake i te utu toharite o nga kaimahi katoa.
whakatau
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Kaimahi Ripanga, Tari, Tauwāhi. Tikina te taone e whiwhi ai nga kaimahi i te iti rawa o te katoa.
whakatau
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);
Tepu kaimahi. Tikina he rarangi o nga kaimahi ka whiwhi te kaiwhakahaere i te utu neke atu i te 15000.
whakatau
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Kaimahi Ripanga, Tari. Whakaatuhia nga tari katoa kaore he kaimahi
whakatau
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Tepu kaimahi. Whakaatuhia nga kaimahi katoa ehara i te kaiwhakahaere
whakatau
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Tepu kaimahi. Whakaatuhia nga kaiwhakahaere katoa neke atu i te 6 nga kaimahi
whakatau
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Kaimahi Ripanga, Tari. Whakaatuhia nga kaimahi e mahi ana i te tari IT
whakatau
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Nga Kaimahi Ripanga, Nga Mahi, Nga Tari. Whakaatuhia nga kaimahi i roto i te whakatakotoranga: Ingoa_Tuamua, Taitara_mahi, Ingoa_Tuhinga.
Hei tauira:
ingoa_mua | taitara mahi | Ingoa_Tānga
Tanara | kaipuke | Kaituhi Tukunga
whakatau
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;
Tepu kaimahi. Tikina he rarangi o nga kaimahi i whai mahi nga kaiwhakahaere i te tau 2005, engari i taua wa ano, ko enei kaimahi ano i whiwhi mahi i mua i te tau 2005
whakatau
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');
Tepu kaimahi. Tikina he rarangi o nga kaimahi i whiwhi mahi a ratou kaiwhakahaere i te marama o Hanuere o ia tau me te roa o te ingoa_mahi o enei kaimahi neke atu i te 15 nga tohu
whakatau
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;
Heoi ano mo naianei.
Ko te tumanako i pai, i whakahihiri hoki nga mahi.
Ka taapiri atu ahau ki tenei rarangi ka taea.
Ka koa ahau ki nga korero me nga whakaaro.
PS: Mena ka puta mai he mahi whakamere i runga i te SELECT, tuhia ki roto i nga korero, ka tapiritia e ahau ki te rarangi.
Mauruuru.
Source: will.com