Hello, Habr!
In ka badan 3 sano ayaan SQL ka dhigayay xarumo kala duwan oo lagu tababaro, waxaana ka mid ah waxyaabaha aan arkay in ardaydu ay si fiican u fahmaan oo ay u fahmaan SQL haddii loo dhiibo hawl, oo aan kaliya loo sheegin suurtagalnimada iyo aasaaska aragtida.
Maqaalkan waxa aan idinkula wadaagi doonaa liiskayga dhibaatooyinka aan ardayda u dhiibo shaqada-guri ahaan oo aan ku samayno noocyo kala duwan oo maskaxeed, taas oo keenta in si qoto dheer oo hufan loo fahmo SQL.
SQL (ΛΙsΛkjuΛΙl; luqadda su'aalaha Ingiriisida habaysan) waa luuqad barraamijeedka caddaynta loo isticmaalo in lagu abuuro, wax ka beddelo oo lagu maamulo xogta ku jirta kaydka xogta ee xidhiidhka ah ee uu maamulo nidaamka maaraynta xogta ku habboon.
Waxaad ka akhrisan kartaa SQL siyaabo kala duwan .
Maqaalkan looguma talogelin inuu SQL ku baro meel eber ah.
Markaa ina keen.
Waxaan isticmaali doonaa si fiican loo yaqaan gudaha Oracle oo wata miisaskeeda ():
Waxaan ogsoonahay inaan kaliya tixgelin doono hawlaha SELECT. Ma jiraan hawlo DML ama DDL ah halkan.
hawlaha
Xakamaynta iyo kala-soocidda xogta
Shaxda shaqaalaha. Hel liiska macluumaadka ku saabsan dhammaan shaqaalaha
go'aanka
SELECT * FROM employees
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha lagu magacaabo 'David'
go'aanka
SELECT *
FROM employees
WHERE first_name = 'David';
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha shaqo_id u dhigma 'IT_PROG'
go'aanka
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Shaxda shaqaalaha. Ka hel liiska dhammaan shaqaalaha waaxda 50aad (qaybta_id) oo leh mushahar (mushahar) ka badan 4000
go'aanka
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha waaxda 20aad iyo 30aad (qaybta_id)
go'aanka
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha xarafka ugu dambeeya ee magacooda ku qoran yahay 'a'
go'aanka
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha waaxda 50aad iyo 80aad (qaybta_id) ee haysta gunnada
go'aanka
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha magacyadoodu ka kooban yihiin ugu yaraan 2 xaraf 'n'
go'aanka
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha magacyadoodu ka badan yihiin 4 xaraf
go'aanka
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha mushaharkoodu u dhexeeyo 8000 ilaa 9000 (lagu daro)
go'aanka
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha magacoodu ka kooban yahay calaamadda '%'
go'aanka
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Shaxda shaqaalaha. Hel liiska dhammaan aqoonsiga maamulaha
go'aanka
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Shaxda shaqaalaha. Hel liiska shaqaalaha boosaskooda u qaabaysan: Donald (sh_clerk)
go'aanka
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Isticmaalka Hawlaha Saf-kaliya si aad u habaynayso wax soo saarka
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha magacyadoodu ka badan yihiin 10 xaraf
go'aanka
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha ku haysta xarafka 'b' magacooda (kiis aan dareen lahayn)
go'aanka
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha magacyadoodu ka kooban yihiin ugu yaraan 2 xaraf 'a'
go'aanka
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha mushaharkoodu yahay tiro 1000 ah
go'aanka
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Shaxda shaqaalaha. Hel lambarka 3-god ee ugu horreeya lambarka taleefanka shaqaalaha haddii nambarkiisu yahay qaabka XXX.XXX.XXXX
go'aanka
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Miiska qaybaha. Ka hel erayga ugu horreeya magaca waaxda kuwa leh wax ka badan hal erey oo magaca ku jira
go'aanka
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Shaxda shaqaalaha. Hel magacyada shaqaalaha oo aan lahayn xarafka hore iyo kan dambe ee magaca
go'aanka
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha xarafka ugu dambeeya ee magacooda ku qoran yahay 'm' oo magacooda ka dheer yahay 5
go'aanka
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Shaxda Dual. Hel taariikhda Jimcaha soo socda
go'aanka
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha shirkadda u shaqaynayay in ka badan 17 sano
go'aanka
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha lambarkooda ugu dambeeya ee nambarkooda taleefoonkoodu uu yahay mid aan caadi ahayn oo ka kooban 3 lambar oo ay u qaybsan tahay dhibic
go'aanka
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalahooda qiimihiisu_id-ga shaqada ka dib calaamadda '_' ay leedahay ugu yaraan 3 xaraf, laakiin qiimahan ka dambeeya '_' lama sina 'CLERK'
go'aanka
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha adiga oo beddelaya dhammaan '.' ee ku jira qiimaha PHONE_NUMBER on '-'
go'aanka
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Isticmaalka Hawlaha Beddelka iyo Odhaahyada Shuruudaha ah
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha u yimid shaqada maalinta ugu horreysa ee bisha (mid kasta)
go'aanka
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalihii shaqada yimid 2008dii
go'aanka
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Shaxda DUAL. U muuji taariikhda berri qaab ahaan: Berri waa maalinta labaad ee Janaayo
go'aanka
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha iyo taariikhda shaqaale kastaa uu shaqada yimid qaabka: 21st June, 2007
go'aanka
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Shaxda shaqaalaha. Hel liiska shaqaalaha mushaharkooda la kordhiyay 20%. Mushahar ku muuji calaamad dollar
go'aanka
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalihii bilaabay shaqada Febraayo 2007.
go'aanka
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';
Shaxda DUAL. Soo saar taariikhda hadda, + labaad, + daqiiqo, + saacad, + maalin, + bisha, + sanadka
go'aanka
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;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha haysta mushahar buuxa (mushaharka + commission_pct(%) qaab ahaan: $24,000.00
go'aanka
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Shaxda shaqaalaha. Hel liiska dhammaan shaqaalaha iyo macluumaadka ku saabsan helitaanka gunnada mushaharka (Haa/Maya)
go'aanka
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Shaxda shaqaalaha. Hesho heerka mushaharka shaqaale kasta: In ka yar 5000 waxa loo arkaa heer hoose, Ka weyn ama la mid ah 5000 iyo in ka yar 10000 waxa loo arkaa heer caadi ah, in ka badan ama la mid ah 10000 waxa loo arkaa heer sare
go'aanka
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;
Wadamada Miiska. Dal kasta, tus gobolka uu ku yaal: 1-Europe, 2-America, 3-Asia, 4-Africa (aan ku biirin)
go'aanka
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;
Ka warbixinta Xogta La isku daray iyadoo la adeegsanayo Hawlaha Kooxda
Shaxda shaqaalaha. Ku hel warbixin waaxda_id oo leh mushaharka ugu yar iyo kan ugu badan, goor hore iyo taariikhda soo daahay ee shaqada iyo tirada shaqaalaha. Ku kala sooc tirada shaqaalaha (ka soo degaya)
go'aanka
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;
Shaxda shaqaalaha. Immisa shaqaale ah oo magacyadoodu xaraf isku mid ah ka bilowdaan? Ku kala sooc tiro. Tus kaliya meelaha ay tiradu ka badan tahay 1
go'aanka
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;
Shaxda shaqaalaha. Immisa shaqaale ah ayaa isku waax ka shaqeeya oo qaata isku mushahar?
go'aanka
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Shaxda shaqaalaha. Hel warbixin ku saabsan inta shaqaale ee la shaqaaleysiiyay maalin kasta ee toddobaadka. Ku kala sooc tiro
go'aanka
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Shaxda shaqaalaha. Hel warbixin ku saabsan inta shaqaale ee la shaqaaleysiiyay sannadkii. Ku kala sooc tiro
go'aanka
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Shaxda shaqaalaha. Hel tirada waaxyaha ee shaqaalaha leh
go'aanka
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Shaxda shaqaalaha. Hel liiska waaxda_ids oo leh in ka badan 30 shaqaale
go'aanka
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Shaxda shaqaalaha. Hel liiska waaxda_ids iyo celceliska mushaharka shaqaalaha ee waax kasta.
go'aanka
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Wadamada Miiska. Hel liiska gobolka_id wadarta dhammaan xarfaha waddan_magacyada ay ku jiraan in ka badan 60
go'aanka
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Shaxda shaqaalaha. Hel liiska waaxda_ids ee ay ku shaqeeyaan shaqaale dhowr ah (>1) job_ids
go'aanka
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Shaxda shaqaalaha. Hel liiska maareeye_ids oo tirada shaqaalahoodu ay ka badan yihiin 5 iyo wadarta dhammaan mushaharka shaqaalihiisa ay ka badan yihiin 50000
go'aanka
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Shaxda shaqaalaha. Hel liiska manager_ids oo celcelis ahaan mushaharkooda dhammaan shaqaalaha ka hooseeya ay u dhexeeyaan 6000 ilaa 9000 oo aan helin gunnooyin (commission_pct waa madhan)
go'aanka
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;
Shaxda shaqaalaha. Ka hel mushaharka ugu badan dhammaan shaqaalaha job_id ee ku dhammaada ereyga 'CLERK'
go'aanka
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';
Shaxda shaqaalaha. Hel mushaharka ugu badan dhammaan celceliska mushaharka waaxda
go'aanka
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Shaxda shaqaalaha. Hel tirada shaqaalaha leh tirada xarfaha ee magacooda. Isla mar ahaantaana, tus kaliya kuwa magacooda dhererkoodu ka badan yahay 5 iyo tirada shaqaalaha ee isku magaca ah ay ka badan yihiin 20. Ku kala sooc magaca dhererka
go'aanka
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);
Soo Bandhigida Xogta Shaxano Badan Isticmaalka Ku biirista
Shaqaalaha Miiska, Waaxyaha, Goobaha, Waddamada, Gobollada. Hel liiska gobollada iyo tirada shaqaalaha gobol kasta
go'aanka
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;
Shaqaalaha Miiska, Waaxyaha, Goobaha, Waddamada, Gobollada. Hel macluumaad faahfaahsan oo ku saabsan shaqaale kasta:
Magaca hore, Magaca Dambe, Waaxda, Shaqada, Waddada, Dalka, Gobolka
go'aanka
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);
Shaxda shaqaalaha. Tus dhammaan maamulayaasha leh in ka badan 6 shaqaale ah oo iyaga hoos yimaada
go'aanka
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;
Shaxda shaqaalaha. Tus dhammaan shaqaalaha aan cidna u soo wargelin
go'aanka
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;
Shaqaalaha miiska, taariikhda shaqada. Miiska shaqaaluhu wuxuu kaydiyaa dhammaan shaqaalaha. Shaxda_Taariikhda Shaqada waxa kaydiya shaqaalihii ka tagay shirkadda. Hel warbixin ku saabsan dhammaan shaqaalaha iyo heerka ay ku sugan yihiin shirkadda (Shaqeeyay ama ka tagay shirkadda taariikhda bixitaan)
Tusaale:
Magaca hore | heerka
Jennifer | Wuxuu ka tagay shirkadda 31-kii Diseembar, 2006
Clara | Hadda shaqeeya
go'aanka
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);
Shaqaalaha Miiska, Waaxyaha, Goobaha, Waddamada, Gobollada. Hel liiska shaqaalaha ku nool Yurub (gobolka_name)
go'aanka
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';
Shaqaalaha Miiska, Waaxyaha. Tus dhammaan waaxaha leh in ka badan 30 shaqaale
go'aanka
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Shaqaalaha Miiska, Waaxyaha. Tus dhammaan shaqaalaha aan ka tirsanayn waax kasta
go'aanka
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;
Shaqaalaha Miiska, Waaxyaha. Tus dhammaan waaxyada aysan ku jirin shaqaale
go'aanka
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Shaxda shaqaalaha. Tus dhammaan shaqaalaha aan haysan cid iyaga hoos timaada
go'aanka
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Shaqaalaha Miiska, Shaqooyinka, Waaxyaha. U muuji shaqaalaha qaabka: First_name, Job_title, Department_name.
Tusaale:
Magaca hore | Shaqada_ciwaanka | Magaca Waaxda
Donald | Rarida | Rarida karraaniga
go'aanka
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);
Shaxda shaqaalaha. Soo hel liiska shaqaalaha ay maareeyahoodu shaqo heleen 2005, laakiin isla markaana shaqaalahan laftoodu waxay heleen shaqo ka hor 2005
go'aanka
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');
Shaxda shaqaalaha. Hel liiska shaqaalaha ay maareeyahoodu shaqo heleen January sanad kasta
go'aanka
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;
Isticmaalka Su'aalaha si loo Xaliyo Weydiimaha
Shaxda shaqaalaha. Hel liiska shaqaalaha leh magaca ugu dheer.
go'aanka
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Shaxda shaqaalaha. Hel liis shaqaale leh mushahar ka badan celceliska mushaharka dhammaan shaqaalaha.
go'aanka
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Shaqaalaha, Waaxyaha, Shaxda Goobaha. Hel magaalada ay guud ahaan shaqaaluhu ka helaan ugu yaraan.
go'aanka
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);
Shaxda shaqaalaha. Hel liiska shaqaalaha maamulahoodu uu qaato mushahar ka badan 15000.
go'aanka
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Shaqaalaha Miiska, Waaxyaha. Tus dhammaan waaxyada aysan ku jirin shaqaale
go'aanka
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Shaxda shaqaalaha. Tus dhammaan shaqaalaha aan ahayn maareeyayaasha
go'aanka
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Shaxda shaqaalaha. Tus dhammaan maamulayaasha leh in ka badan 6 shaqaale ah oo iyaga hoos yimaada
go'aanka
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Shaqaalaha Miiska, Waaxyaha. Tus shaqaalaha ka shaqeeya waaxda IT-ga
go'aanka
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Shaqaalaha Miiska, Shaqooyinka, Waaxyaha. U muuji shaqaalaha qaabka: First_name, Job_title, Department_name.
Tusaale:
Magaca hore | Shaqada_ciwaanka | Magaca Waaxda
Donald | Rarida | Rarida karraaniga
go'aanka
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;
Shaxda shaqaalaha. Soo hel liiska shaqaalaha ay maareeyahoodu shaqo heleen 2005, laakiin isla markaana shaqaalahan laftoodu waxay heleen shaqo ka hor 2005
go'aanka
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');
Shaxda shaqaalaha. Hel liiska shaqaalaha ay maareeyahoodu shaqo heleen January sanad kasta
go'aanka
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;
Waa intaas hadda.
Waxaan rajeynayaa in hawluhu ahaayeen kuwo xiiso leh oo xiiso leh.
Waxaan ku dari doonaa liiskan hawlaha sida ugu badan ee suurtogalka ah.
Waxa kale oo aan ku farxi doonaa in aan helo faallooyin iyo talooyin.
PS: Haddii qof la yimaado hawl xiisa leh oo XUL ah, ku qor faallooyinka oo waxaan ku dari doonaa liiska.
Π‘ΠΏΠ°ΡΠΈΠ±ΠΎ.
Source: www.habr.com