Aloha Habr!
No nā makahiki ʻoi aku ma mua o 3 mau makahiki aʻu i aʻo ai i ka SQL ma nā kikowaena hoʻomaʻamaʻa like ʻole, a ʻo kekahi o kaʻu ʻike ʻana ʻo ka haku a hoʻomaopopo maikaʻi ʻana o nā haumāna iā SQL inā hāʻawi ʻia lākou i kahi hana, ʻaʻole e kamaʻilio wale i nā mea hiki a me nā kumu kumu.
Ma kēia ʻatikala, e kaʻana aku au iā ʻoe i kaʻu papa inoa o nā hana aʻu e hāʻawi ai i nā haumāna ma ke ʻano he haʻawina home a ma luna o mākou e alakaʻi ai i nā ʻano o ka lolo, e alakaʻi ana i kahi ʻike hohonu a maopopo o SQL.
ʻO SQL (ˈɛsˈkjuˈɛl; eng. structured query language) he ʻōlelo hoʻolālā hoʻolaha i hoʻohana ʻia e hana, hoʻololi a mālama i ka ʻikepili i loko o kahi waihona pili i mālama ʻia e kahi ʻōnaehana hoʻokele waiwai kūpono.
Hiki iā ʻoe ke heluhelu e pili ana i SQL mai nā ʻokoʻa .
ʻAʻole i manaʻo ʻia kēia ʻatikala e aʻo iā ʻoe i ka SQL mai ka wā kahiko.
No laila e hele.
E hoʻohana mākou i ka mea kaulana ma Oracle me kona mau papa ():
Hoʻomaopopo wau e noʻonoʻo mākou i nā hana ma SELECT. ʻAʻohe hana ma DML a me DDL.
hana
Kaohi ana a me ka wae ana i ka ikepili
Papaaina o na limahana. E kiʻi i kahi papa inoa me ka ʻike e pili ana i nā limahana āpau
olelo hooholo
SELECT * FROM employees
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana a pau i kapa ʻia ʻo 'David'
olelo hooholo
SELECT *
FROM employees
WHERE first_name = 'David';
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana me ka job_id like me 'IT_PROG'
olelo hooholo
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau mai ke keʻena 50th (department_id) me kahi uku (uku) ʻoi aku ma mua o 4000
olelo hooholo
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau mai ka lā 20 a mai ke keʻena 30th (department_id)
olelo hooholo
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau nona ka leka hope ma ko lākou inoa ʻo 'a'
olelo hooholo
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana a pau mai ka 50th a mai ke keʻena 80th (department_id) i loaʻa ka bonus (ʻaʻole nele ka waiwai ma ke kolamu commission_pct)
olelo hooholo
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau nona ka inoa i loaʻa i ka liʻiliʻi 2 hua 'n'
olelo hooholo
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau nona ka inoa ma mua o 4 mau leka
olelo hooholo
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau nona ka uku ma waena o 8000 a me 9000 (inclusive)
olelo hooholo
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau nona ka inoa i loaʻa ka hōʻailona '%'
olelo hooholo
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā ID luna āpau
olelo hooholo
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana me ko lākou kūlana ma ke ʻano: Donald(sh_clerk)
olelo hooholo
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Ke hoʻohana nei i nā hana o ka lālani hoʻokahi e hoʻopilikino i ka puka
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau nona ka inoa ma mua o 10 mau leka
olelo hooholo
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau i loaʻa ka leka 'b' ma ko lākou inoa (case insensitive)
olelo hooholo
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau nona ka inoa ma kahi o 2 mau hua 'a'
olelo hooholo
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau nona ka uku he nui o 1000
olelo hooholo
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Papaaina o na limahana. E kiʻi i ka helu 3-helu mua o ka helu kelepona o ka limahana inā aia kāna helu ma ke ʻano ХХХ.ХХХ.ХХХХ
olelo hooholo
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Papa Oihana. E kiʻi i ka huaʻōlelo mua mai ka inoa ʻoihana no ka poʻe i ʻoi aku ma mua o hoʻokahi huaʻōlelo ma ka inoa
olelo hooholo
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Papaaina o na limahana. E kiʻi i nā inoa limahana me ka ʻole o ka leka mua a me ka leka hope ma ka inoa
olelo hooholo
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau nona ka leka hope ma ka inoa ua like me 'm' a ʻoi aku ka lōʻihi o ka inoa ma mua o 5
olelo hooholo
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Pākaukau pālua. E kiʻi i ka lā o ka Pōʻalima aʻe
olelo hooholo
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana a pau i noho pū me ka hui no nā makahiki he 17
olelo hooholo
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau nona ka helu hope o ka helu kelepona he ʻano ʻē a loaʻa nā helu 3 i hoʻokaʻawale ʻia e kahi kiko.
olelo hooholo
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana a pau nona ka waiwai job_id ma hope o ka hōʻailona '_' he 3 mau huaʻōlelo, akā ʻaʻole like kēia waiwai ma hope o ka '_' me 'CLERK'
olelo hooholo
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau ma ke pani ʻana i nā '.' a pau ma ka helu PHONE_NUMBER ma '-'
olelo hooholo
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Ke hoʻohana nei i nā hana hoʻololi a me nā hōʻike kūlana
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau i hele mai e hana i ka lā mua o ka mahina (kekahi)
olelo hooholo
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau i hele mai e hana i ka makahiki 2008
olelo hooholo
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
papaʻaina DUAL. Hōʻike i ka lā ʻapōpō ma ke ʻano: ʻApōpō ʻelua lā o Ianuali
olelo hooholo
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana āpau a me ko lākou lā hoʻomaka ma ke ʻano: 21st o Iune, 2007
olelo hooholo
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana me ka uku uku ma 20%. Hōʻike i ka uku me ka hōʻailona kālā
olelo hooholo
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana a pau i hele mai e hana i Pepeluali 2007.
olelo hooholo
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';
papaʻaina DUAL. Hoʻokuʻu i ka lā i kēia manawa, + kekona, + minuke, + hola, + lā, + mahina, + makahiki
olelo hooholo
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;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau me nā uku piha (uku + komisina_pct(%)) ma ke ʻano: $24,000.00
olelo hooholo
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana āpau a me ka ʻike e pili ana i ka loaʻa ʻana o nā bonus uku (ʻAe/ʻAʻole)
olelo hooholo
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Papaaina o na limahana. E kiʻi i ka pae uku o kēlā me kēia limahana: ʻO ka liʻiliʻi ma mua o 5000 i manaʻo ʻia he pae haʻahaʻa, ʻoi aku ka nui ma mua o a i ʻole like me 5000 a ʻoi aku ka liʻiliʻi ma mua o 10000 i manaʻo ʻia he pae maʻamau, ʻoi aku ka nui a i ʻole like me 10000 i manaʻo ʻia ʻo Kiʻekiʻe.
olelo hooholo
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;
Papa aina. No kēlā me kēia ʻāina, e hōʻike i ka ʻāina i loaʻa ai: 1-Europe, 2-America, 3-Asia, 4-Africa (me ka hui ʻole)
olelo hooholo
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;
Hōʻike i ka ʻikepili i hōʻuluʻulu ʻia me ka hoʻohana ʻana i nā hana pūʻulu
Papaaina o na limahana. E kiʻi i kahi hōʻike e ka department_id me ka uku liʻiliʻi a me ka uku kiʻekiʻe, nā lā hiki mua a me ka lohi a me ka helu o nā limahana. Hoʻokaʻawale ma ka helu o nā limahana (desc)
olelo hooholo
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;
Papaaina o na limahana. ʻEhia mau limahana e hoʻomaka ana ko lākou inoa me ka leka hoʻokahi? Hoʻokaʻawale ma ka nui. Hōʻike wale i nā mea i ʻoi aku ka nui o ka helu ma mua o 1
olelo hooholo
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;
Papaaina o na limahana. ʻEhia mau limahana e hana ana ma ka ʻoihana hoʻokahi a loaʻa ka uku like?
olelo hooholo
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Papaaina o na limahana. E kiʻi i kahi hōʻike no ka nui o nā limahana i hoʻolimalima ʻia i kēlā me kēia lā o ka pule. Hoʻokaʻawale ma ka nui
olelo hooholo
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Papaaina o na limahana. E kiʻi i kahi hōʻike e pili ana i ka nui o nā limahana i hoʻolimalima ʻia e ka makahiki. Hoʻokaʻawale ma ka nui
olelo hooholo
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Papaaina o na limahana. E kiʻi i ka helu o nā keʻena i loaʻa nā limahana
olelo hooholo
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Papaaina o na limahana. E kiʻi i ka papa inoa o department_id me 30 mau limahana
olelo hooholo
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā department_ids a me ka awelika uku o nā limahana ma kēlā me kēia keʻena.
olelo hooholo
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Papa aina. E kiʻi i ka papa inoa o ka huina_id o nā leka a pau o nā inoa_āina a ʻoi aku ma mua o 60
olelo hooholo
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Papaaina o na limahana. E kiʻi i kahi papa inoa o department_id kahi e hana ai nā limahana o kekahi (>1) job_id
olelo hooholo
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Papaaina o na limahana. E kiʻi i kahi papa inoa o manager_id nona ka nui o nā subordinates i ʻoi aku ma mua o 5 a ʻo ka huina o nā uku āpau o kāna mau subordinates ʻoi aku ka nui ma mua o 50000
olelo hooholo
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Papaaina o na limahana. E kiʻi i kahi papa inoa o manager_id nona ka awelika o ka uku o kāna mau subordinates ma waena o 6000 a me 9000 i loaʻa ʻole nā bonus (komi_pct ʻaʻohe mea)
olelo hooholo
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;
Papaaina o na limahana. E kiʻi i ka uku kiʻekiʻe loa mai nā limahana job_id e pau ana me ka huaʻōlelo 'CLERK'
olelo hooholo
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';
Papaaina o na limahana. E kiʻi i ka uku kiʻekiʻe ma waena o nā uku awelika no ka ʻoihana
olelo hooholo
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Papaaina o na limahana. E kiʻi i ka helu o nā limahana me ka helu like o nā leka ma ko lākou inoa. I ka manawa like, e hōʻike wale i ka poʻe i ʻoi aku ka lōʻihi o ko lākou inoa ma mua o 5 a ʻoi aku ka nui o nā limahana me ka inoa like ma mua o 20. E hoʻokaʻawale i ka lōʻihi o ka inoa.
olelo hooholo
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);
Hōʻike i ka ʻikepili mai nā papa he nui me ka hoʻohana ʻana i nā hui
Nā limahana papaʻaina, nā ʻoihana, nā wahi, nā ʻāina, nā ʻāpana. E kiʻi i ka papa inoa o nā wahi a me ka helu o nā limahana ma kēlā me kēia māhele
olelo hooholo
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;
Nā limahana papaʻaina, nā ʻoihana, nā wahi, nā ʻāina, nā ʻāpana. E kiʻi i ka ʻike kikoʻī e pili ana i kēlā me kēia limahana:
inoa_mua, inoa_hope, Oihana, Hana, Alanui, Aina, Aina
olelo hooholo
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);
Papaaina o na limahana. Hōʻike i nā mana āpau i ʻoi aku ma mua o 6 mau limahana
olelo hooholo
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;
Papaaina o na limahana. Hōʻike i nā limahana a pau i hōʻike ʻole i kekahi
olelo hooholo
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;
Papakaukau limahana, Job_history. Mālama ka papa ʻaina limahana i nā limahana a pau. Mālama ka papa ʻaina Job_history i nā limahana i haʻalele i ka hui. E kiʻi i kahi hōʻike e pili ana i nā limahana āpau a me ko lākou kūlana ma ka hui (Hoʻohana a haʻalele paha i ka hui me ka lā haʻalele)
Pākuhi:
inoa_mua | kūlana
jennifero | Ua haʻalele i ka hui ma 31 o Dekemaba, 2006
Clara | Ke hana nei
olelo hooholo
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);
Nā limahana papaʻaina, nā ʻoihana, nā wahi, nā ʻāina, nā ʻāpana. E kiʻi i ka papa inoa o nā limahana e noho ana ma ʻEulopa (region_name)
olelo hooholo
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';
Papa Pakaukau, Oihana. Hōʻike i nā keʻena āpau me nā limahana 30 oi aku
olelo hooholo
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Papa Pakaukau, Oihana. Hōʻike i nā limahana a pau ʻaʻole i loko o kekahi keʻena
olelo hooholo
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;
Papa Pakaukau, Oihana. Hōʻike i nā keʻena āpau me nā limahana ʻole
olelo hooholo
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Papaaina o na limahana. E hōʻike i nā limahana a pau ʻaʻohe o lalo
olelo hooholo
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Papa Pakaukau, Hana, Oihana. Hōʻike i nā limahana ma ke ʻano: First_name, Job_title, Department_name.
Pākuhi:
inoa_mua | inoa hana | Oihana_inoa
Donald | moku | Kakauolelo Hoouna
olelo hooholo
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);
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana i loaʻa i nā luna ka hana ma 2005, akā i ka manawa like, ua loaʻa i kēia mau limahana i kahi hana ma mua o 2005
olelo hooholo
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');
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana i loaʻa i nā mana mana kahi hana i ka mahina o Ianuali o kēlā me kēia makahiki a ʻo ka lōʻihi o ka job_title o kēia mau limahana ʻoi aku ma mua o 15 mau huapalapala
olelo hooholo
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;
Ke hoʻohana nei i nā Subqueries e hoʻoholo i nā nīnau
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana me ka inoa lōʻihi loa.
olelo hooholo
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana me ka uku ʻoi aku ma mua o ka uku maʻamau o nā limahana āpau.
olelo hooholo
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Papa Pakaukau, Oihana, Wahi. E kiʻi i ke kūlanakauhale kahi e loaʻa ai nā limahana liʻiliʻi loa.
olelo hooholo
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);
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana i loaʻa ka uku o ka luna ma mua o 15000.
olelo hooholo
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Papa Pakaukau, Oihana. Hōʻike i nā keʻena āpau me nā limahana ʻole
olelo hooholo
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Papaaina o na limahana. Hōʻike i nā limahana a pau ʻaʻole nā mana
olelo hooholo
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Papaaina o na limahana. Hōʻike i nā mana āpau i ʻoi aku ma mua o 6 mau limahana
olelo hooholo
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Papa Pakaukau, Oihana. Hōʻike i nā limahana e hana ana ma ka ʻoihana IT
olelo hooholo
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Papa Pakaukau, Hana, Oihana. Hōʻike i nā limahana ma ke ʻano: First_name, Job_title, Department_name.
Pākuhi:
inoa_mua | inoa hana | Oihana_inoa
Donald | moku | Kakauolelo Hoouna
olelo hooholo
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;
Papaaina o na limahana. E kiʻi i ka papa inoa o nā limahana i loaʻa i nā luna ka hana ma 2005, akā i ka manawa like, ua loaʻa i kēia mau limahana i kahi hana ma mua o 2005
olelo hooholo
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');
Papaaina o na limahana. E kiʻi i kahi papa inoa o nā limahana i loaʻa i nā mana mana kahi hana i ka mahina o Ianuali o kēlā me kēia makahiki a ʻo ka lōʻihi o ka job_title o kēia mau limahana ʻoi aku ma mua o 15 mau huapalapala
olelo hooholo
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;
ʻO ia wale nō i kēia manawa.
Manaʻo wau he hoihoi a hoihoi nā hana.
E hoʻohui au i kēia papa inoa e like me ka hiki.
E hauʻoli nō wau i nā manaʻo a me nā manaʻo.
PS: Inā hele mai kekahi me kahi hana hoihoi ma SELECT, e kākau i nā manaʻo, e hoʻohui wau i ka papa inoa.
Спасибо.
Source: www.habr.com