Bonjou Habr!
Pou plis pase 3 ane mwen te anseye SQL nan divès sant fòmasyon, ak youn nan obsèvasyon mwen yo se ke elèv yo metrize ak konprann SQL pi byen si yo bay yo yon travay, epi yo pa jis pale sou posibilite yo ak fondasyon teyorik.
Nan atik sa a, mwen pral pataje avèk ou lis travay mwen bay elèv yo kòm devwa epi sou ki nou fè divès kalite brainstorms, ki mennen nan yon konpreyansyon pwofon ak klè nan SQL.
SQL (ˈɛsˈkjuˈɛl; ang. langaj rechèch estriktire) se yon langaj pwogramasyon deklaratif ki itilize pou kreye, modifye ak jere done nan yon baz done relasyon ki jere pa yon sistèm jesyon baz done apwopriye.
Ou ka li sou SQL soti nan diferan .
Atik sa a pa fèt pou anseye ou SQL nan grafouyen.
Se konsa, kite a ale.
Nou pral sèvi ak byen li te ye a nan Oracle ak tab li yo ():
Mwen sonje ke nou pral konsidere sèlman travay sou SELECT. Pa gen okenn travay sou DML ak DDL.
travay
Restriksyon ak klasman done
Tablo anplwaye yo. Jwenn yon lis ak enfòmasyon sou tout anplwaye yo
desizyon
SELECT * FROM employees
Tablo anplwaye yo. Jwenn yon lis tout anplwaye yo rele 'David'
desizyon
SELECT *
FROM employees
WHERE first_name = 'David';
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen job_id egal a 'IT_PROG'
desizyon
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki soti nan 50yèm depatman an (department_id) ak yon salè (salè) ki pi gran pase 4000
desizyon
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki soti nan 20yèm ak nan 30yèm depatman an (department_id)
desizyon
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen dènye lèt nan non yo se 'a'
desizyon
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki soti nan 50yèm ak nan 80yèm depatman an (department_id) ki gen yon bonis (valè nan kolòn commission_pct la pa vid)
desizyon
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen non omwen 2 lèt 'n'
desizyon
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen non plis pase 4 lèt
desizyon
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen salè ant 8000 ak 9000 (enklizif)
desizyon
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen non senbòl '%'
desizyon
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Tablo anplwaye yo. Jwenn yon lis tout idantite manadjè yo
desizyon
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Tablo anplwaye yo. Jwenn yon lis anplwaye ak pozisyon yo nan fòma a: Donald(sh_clerk)
desizyon
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Sèvi ak Fonksyon sèl-ranje pou Customize Sòti a
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen non plis pase 10 lèt
desizyon
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen lèt 'b' nan non yo (yo pa sansib)
desizyon
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen non omwen 2 lèt 'a'
desizyon
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen yon salè miltip 1000
desizyon
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Tablo anplwaye yo. Jwenn premye nimewo 3 chif nimewo telefòn anplwaye a si nimewo li an fòma ХХХ.ХХХ.ХХХХ
desizyon
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Tablo Depatman. Jwenn premye mo nan non depatman an pou moun ki gen plis pase yon mo nan non an
desizyon
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Tablo anplwaye yo. Jwenn non anplwaye yo san premye ak dènye lèt nan non an
desizyon
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen dènye lèt nan non an ki egal a 'm' ak longè non an pi gran pase 5
desizyon
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Doub tab. Jwenn dat Vandredi kap vini an
desizyon
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki te nan konpayi an pou plis pase 17 ane
desizyon
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen dènye chif nan nimewo telefòn nan enpè epi ki gen 3 nimewo separe pa yon pwen.
desizyon
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen valè job_id apre siy '_' a gen omwen 3 karaktè, men valè sa a apre '_' pa egal a 'CLERK'
desizyon
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Tablo anplwaye yo. Jwenn yon lis tout anplwaye lè w ranplase tout '.' nan valè PHONE_NUMBER sou '-'
desizyon
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Sèvi ak Fonksyon Konvèsyon ak Ekspresyon Kondisyonèl
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki te vin travay nan premye jou mwa a (nenpòt)
desizyon
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki te vin travay an 2008
desizyon
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
DOUBLE tab la. Montre dat demen an nan fòma: Demen se dezyèm jou janvye
desizyon
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye yo ak dat kòmansman yo nan fòma a: 21 jen, 2007
desizyon
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Tablo anplwaye yo. Jwenn yon lis anplwaye yo ak salè ogmante pa 20%. Montre salè ak siy dola
desizyon
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki te vin travay nan mwa fevriye 2007.
desizyon
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';
DOUBLE tab la. Ekspòte dat aktyèl la, + segonn, + minit, + èdtan, + jou, + mwa, + ane
desizyon
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;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye ki gen salè konplè (salè + commission_pct(%)) nan fòma a: $24,000.00
desizyon
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Tablo anplwaye yo. Jwenn yon lis tout anplwaye yo ak enfòmasyon sou disponiblite bonis salè (Wi/Non)
desizyon
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Tablo anplwaye yo. Jwenn nivo salè chak anplwaye: Mwens pase 5000 yo konsidere kòm Nivo ki ba, Pi gran pase oswa egal a 5000 ak mwens pase 10000 yo konsidere kòm Nivo nòmal, Pi gran pase oswa egal a 10000 yo konsidere kòm Nivo segondè.
desizyon
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;
Tablo peyi yo. Pou chak peyi, montre rejyon kote li ye a: 1-Ewòp, 2-Amerik, 3-Azi, 4-Lafrik (san Join)
desizyon
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;
Rapòte Done Agrégées Sèvi ak Fonksyon Gwoup yo
Tablo anplwaye yo. Jwenn yon rapò pa depatman_id ak salè minimòm ak maksimòm, dat arive bonè ak an reta ak kantite anplwaye yo. Triye pa kantite anplwaye (desc)
desizyon
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;
Tablo anplwaye yo. Konbyen anplwaye ki gen non ki kòmanse ak menm lèt la? Triye pa kantite. Montre sèlman sa yo kote nimewo a pi gran pase 1
desizyon
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;
Tablo anplwaye yo. Konbyen anplwaye k ap travay nan menm depatman e yo resevwa menm salè?
desizyon
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Tablo anplwaye yo. Jwenn yon rapò sou konbyen anplwaye yo te anboche nan chak jou nan semèn nan. Triye pa kantite
desizyon
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Tablo anplwaye yo. Jwenn yon rapò sou konbyen anplwaye yo te anboche pa ane. Triye pa kantite
desizyon
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Tablo anplwaye yo. Jwenn kantite depatman ki gen anplwaye yo
desizyon
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Tablo anplwaye yo. Jwenn lis depatman_id ki gen plis pase 30 anplwaye
desizyon
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Tablo anplwaye yo. Jwenn yon lis depatman_id ak salè mwayèn awondi anplwaye nan chak depatman.
desizyon
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Tablo peyi yo. Jwenn yon lis rejyon_id sòm tout lèt nan tout non_peyi ki gen plis pase 60
desizyon
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Tablo anplwaye yo. Jwenn yon lis depatman_id kote anplwaye plizyè (>1) job_id travay
desizyon
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Tablo anplwaye yo. Jwenn yon lis manager_id ki gen kantite sibòdone ki pi gran pase 5 ak sòm tout salè sibòdone li yo pi gran pase 50000.
desizyon
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Tablo anplwaye yo. Jwenn yon lis manager_id ki gen salè mwayèn nan tout sibòdone li yo se ant 6000 ak 9000 ki pa resevwa bonis (commission_pct se vid)
desizyon
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;
Tablo anplwaye yo. Jwenn salè maksimòm nan men tout anplwaye job_id ki fini ak mo 'CLERK'
desizyon
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';
Tablo anplwaye yo. Jwenn salè maksimòm nan pami tout salè mwayèn pou depatman an
desizyon
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Tablo anplwaye yo. Jwenn kantite anplwaye ki gen menm kantite lèt nan non yo. An menm tan an, montre sèlman moun ki gen non ki pi long pase 5 ak kantite anplwaye ki gen menm non ki gen plis pase 20. Triye pa longè non.
desizyon
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);
Montre done ki soti nan tab miltip lè l sèvi avèk Join
Tablo Anplwaye, Depatman, Kote, Peyi, Rejyon. Jwenn yon lis rejyon yo ak kantite anplwaye nan chak rejyon
desizyon
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;
Tablo Anplwaye, Depatman, Kote, Peyi, Rejyon. Jwenn enfòmasyon detaye sou chak anplwaye:
First_name, Last_name, Department, Job, Street, Country, Region
desizyon
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);
Tablo anplwaye yo. Montre tout manadjè ki gen plis pase 6 anplwaye
desizyon
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;
Tablo anplwaye yo. Montre tout anplwaye ki pa rapòte pèsonn
desizyon
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;
Tablo anplwaye yo, Job_history. Tablo Anplwaye a estoke tout anplwaye yo. Tablo Job_history la estoke anplwaye ki te kite konpayi an. Jwenn yon rapò sou tout anplwaye yo ak sitiyasyon yo nan konpayi an (Anplwaye oswa kite konpayi an ak dat depa)
Egzanp:
premye_non | estati
jennifer | Te kite konpayi an nan 31 desanm 2006
Clara | Kounye a ap travay
desizyon
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);
Tablo Anplwaye, Depatman, Kote, Peyi, Rejyon. Jwenn yon lis anplwaye ki abite nan Ewòp (region_name)
desizyon
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';
Tablo Anplwaye, Depatman. Montre tout depatman ki gen plis pase 30 anplwaye yo
desizyon
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Tablo Anplwaye, Depatman. Montre tout anplwaye ki pa nan okenn depatman
desizyon
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;
Tablo Anplwaye, Depatman. Montre tout depatman ki pa gen anplwaye yo
desizyon
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Tablo anplwaye yo. Montre tout anplwaye ki pa gen sibòdone
desizyon
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Tablo Anplwaye, Travay, Depatman. Montre anplwaye yo nan fòma: First_name, Job_title, Department_name.
Egzanp:
premye_non | tit travay | Department_name
Donald | anbake | Grefye Shipping
desizyon
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);
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen manadjè yo te jwenn yon travay an 2005, men an menm tan, travayè sa yo tèt yo te jwenn yon travay anvan 2005.
desizyon
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');
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen manadjè yo te jwenn yon travay nan mwa janvye nenpòt ane ak longè job_title anplwaye sa yo plis pase 15 karaktè.
desizyon
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;
Sèvi ak Subqueries pou rezoud demann
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen non ki pi long.
desizyon
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen yon salè ki pi gran pase salè mwayèn tout anplwaye yo.
desizyon
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Tablo Anplwaye, Depatman, Kote yo ye. Jwenn vil kote anplwaye yo touche pi piti nan total.
desizyon
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);
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen manadjè resevwa yon salè ki gen plis pase 15000.
desizyon
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Tablo Anplwaye, Depatman. Montre tout depatman ki pa gen anplwaye yo
desizyon
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Tablo anplwaye yo. Montre tout anplwaye ki pa manadjè
desizyon
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Tablo anplwaye yo. Montre tout manadjè ki gen plis pase 6 anplwaye
desizyon
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Tablo Anplwaye, Depatman. Montre anplwaye ki travay nan depatman IT
desizyon
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Tablo Anplwaye, Travay, Depatman. Montre anplwaye yo nan fòma: First_name, Job_title, Department_name.
Egzanp:
premye_non | tit travay | Department_name
Donald | anbake | Grefye Shipping
desizyon
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;
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen manadjè yo te jwenn yon travay an 2005, men an menm tan, travayè sa yo tèt yo te jwenn yon travay anvan 2005.
desizyon
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');
Tablo anplwaye yo. Jwenn yon lis anplwaye ki gen manadjè yo te jwenn yon travay nan mwa janvye nenpòt ane ak longè job_title anplwaye sa yo plis pase 15 karaktè.
desizyon
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;
Se tout pou kounye a.
Mwen espere ke travay yo te enteresan ak enteresan.
Mwen pral ajoute nan lis sa a otank posib.
Mwen pral kontan tou pou nenpòt kòmantè ak sijesyon.
PS: Si yon moun vini ak yon travay enteresan sou SELECT, ekri nan kòmantè yo, mwen pral ajoute li nan lis la.
Mèsi poutèt ou.
Sous: www.habr.com