Moni, Habr!
Kwa zaka zoposa 3 tsopano ndakhala ndikuphunzitsa SQL m'malo osiyanasiyana ophunzitsira, ndipo chimodzi mwazomwe ndikuwona ndikuti ophunzira amaphunzira bwino ndikumvetsetsa SQL ngati apatsidwa ntchito, osati kungouzidwa za zotheka ndi maziko a chiphunzitso.
M'nkhaniyi, ndikugawana nanu mndandanda wa mavuto omwe ndimapereka kwa ophunzira monga homuweki komanso momwe timachitira mitundu yosiyanasiyana ya malingaliro, zomwe zimatsogolera kumvetsetsa kwakukulu ndi komveka kwa SQL.
SQL (ΛΙsΛkjuΛΙl; chilankhulo chachingerezi chokhazikika) ndi chilankhulo chofotokozera chomwe chimagwiritsidwa ntchito kupanga, kusintha ndi kuyang'anira deta mu nkhokwe yaubale yomwe imayendetsedwa ndi kasamalidwe koyenera ka database.
Mutha kuwerenga za SQL kuchokera kumitundu yosiyanasiyana .
Nkhaniyi sinapangidwe kuti ikuphunzitseni SQL kuyambira pachiyambi.
Ndiye tiyeni tizipita.
Tidzagwiritsa ntchito odziwika bwino mu Oracle ndi matebulo ake ():
Ndikuwona kuti tingoganizira za SELECT ntchito. Palibe ntchito za DML kapena DDL pano.
ntchito
Kuletsa ndi Kusanja Deta
Antchito tebulo. Pezani mndandanda wokhala ndi zambiri za ogwira ntchito
chisankho
SELECT * FROM employees
Antchito tebulo. Pezani mndandanda wa antchito onse otchedwa 'David'
chisankho
SELECT *
FROM employees
WHERE first_name = 'David';
Antchito tebulo. Pezani mndandanda wa antchito onse omwe ali ndi job_id yofanana ndi 'IT_PROG'
chisankho
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Antchito tebulo. Pezani mndandanda wa onse ogwira ntchito ku dipatimenti ya 50 (department_id) omwe ali ndi malipiro (malipiro) opitilira 4000
chisankho
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Antchito tebulo. Pezani mndandanda wa onse ogwira ntchito ku dipatimenti ya 20 ndi 30 (department_id)
chisankho
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe chilembo chomaliza m'dzina lawo ndi 'a'
chisankho
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Antchito tebulo. Pezani mndandanda wa onse ogwira ntchito ku dipatimenti ya 50 ndi 80 (department_id) omwe ali ndi bonasi (mtengo womwe uli patsamba la commission_pct ulibe kanthu)
chisankho
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe mayina awo ali ndi zilembo ziwiri 'n'
chisankho
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Antchito tebulo. Pezani mndandanda wa antchito onse omwe mayina awo ndi otalika kuposa zilembo zinayi
chisankho
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Antchito tebulo. Pezani mndandanda wa antchito onse omwe malipiro awo ali pakati pa 8000 mpaka 9000 (kuphatikiza)
chisankho
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe dzina lawo lili ndi chizindikiro '%'
chisankho
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Antchito tebulo. Pezani mndandanda wa ma ID onse oyang'anira
chisankho
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Antchito tebulo. Pezani mndandanda wa antchito omwe ali ndi maudindo awo motere: Donald(sh_clerk)
chisankho
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Kugwiritsa Ntchito Mzere Umodzi Kuti Musinthe Zotulutsa
Antchito tebulo. Pezani mndandanda wa antchito onse omwe mayina awo ndi otalika kuposa zilembo zinayi
chisankho
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Antchito tebulo. Pezani mndandanda wa onse ogwira ntchito omwe ali ndi chilembo 'b' m'dzina lawo (osakhudzidwa)
chisankho
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe mayina awo ali ndi zilembo ziwiri 'a'
chisankho
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe malipiro awo ndi ochulukitsa 1000
chisankho
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Antchito tebulo. Pezani nambala yoyamba ya manambala 3 ya nambala yafoni ya wogwira ntchito ngati nambala yake ili motere XXX.XXX.XXXX
chisankho
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Madipatimenti tebulo. Pezani liwu loyamba kuchokera ku dzina la dipatimenti kwa omwe ali ndi mawu oposa amodzi m'dzina
chisankho
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Antchito tebulo. Pezani mayina a antchito opanda chilembo choyamba ndi chomaliza m'dzina
chisankho
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe chilembo chomaliza m'dzina lawo ndi 'm' ndipo dzina lawo ndi lalitali kuposa 5
chisankho
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Table Dual. Pezani tsiku Lachisanu lotsatira
chisankho
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe agwira ntchito kukampaniyi kwa zaka zopitilira 17
chisankho
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe nambala yawo yomaliza ya foni yawo ndi yosamvetseka ndipo imakhala ndi manambala atatu olekanitsidwa ndi kadontho.
chisankho
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe mtengo wa job_id pambuyo pa chikwangwani cha '_' chili ndi zilembo zosachepera 3, koma mtengo uwu pambuyo pa '_' siwofanana ndi 'CLERK'
chisankho
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Antchito tebulo. Pezani mndandanda wa antchito onse posintha '.' mu mtengo wa PHONE_NUMBER pa '-'
chisankho
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Kugwiritsa Ntchito Zosintha ndi Mafotokozedwe Okhazikika
Antchito tebulo. Pezani mndandanda wa antchito onse omwe anabwera kudzagwira ntchito tsiku loyamba la mwezi (aliyense)
chisankho
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Antchito tebulo. Pezani mndandanda wa antchito onse omwe anabwera kudzagwira ntchito mu 2008
chisankho
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Table DUAL. Onetsani tsiku la mawa motere: Mawa ndi Tsiku Lachiwiri la Januware
chisankho
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Antchito tebulo. Pezani mndandanda wa antchito onse ndi tsiku limene wogwira ntchito aliyense anafika kuntchito motere: 21st June, 2007
chisankho
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Antchito tebulo. Pezani mndandanda wa antchito omwe awonjezeka malipiro ndi 20%. Onetsani malipiro okhala ndi chizindikiro cha dollar
chisankho
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe adayamba kugwira ntchito mu February 2007.
chisankho
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';
Table DUAL. Tulutsani deti lomwe lilipo, + chachiwiri, + miniti, + ola, + tsiku, + mwezi, + chaka
chisankho
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;
Antchito tebulo. Pezani mndandanda wa antchito onse omwe ali ndi malipiro athunthu (salary + commission_pct(%)) mumtundu: $24,000.00
chisankho
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Antchito tebulo. Pezani mndandanda wa antchito onse komanso zambiri za kupezeka kwa mabonasi amalipiro (Inde/Ayi)
chisankho
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Antchito tebulo. Pezani mulingo wamalipiro a wogwira ntchito aliyense: Ochepera 5000 amawerengedwa kuti ndi Otsika, Oposa kapena ofanana ndi 5000 ndipo ochepera 10000 amaonedwa ngati Mulingo Wamba, Kuposa kapena kufanana ndi 10000 kumawerengedwa kuti ndi High Level.
chisankho
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;
Mayiko a pa tebulo. Padziko lililonse, onetsani dera lomwe lili: 1-Europe, 2-America, 3-Asia, 4-Africa (popanda Kujowina)
chisankho
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;
Kupereka malipoti a Aggregated Data Pogwiritsa Ntchito Ntchito Zamagulu
Antchito tebulo. Landirani lipoti la department_id ndi malipiro ochepa komanso ochulukirapo, masiku oyambilira komanso mochedwa ofika kuntchito komanso kuchuluka kwa antchito. Sanjani ndi kuchuluka kwa antchito (kutsika)
chisankho
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;
Antchito tebulo. Ndi antchito angati omwe mayina awo amayamba ndi chilembo chomwecho? Sanjani ndi kuchuluka. Onetsani okhawo omwe kuchuluka kwake kuli kwakukulu kuposa 1
chisankho
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;
Antchito tebulo. Ndi antchito angati omwe amagwira ntchito m'dipatimenti imodzi ndikulandila malipiro ofanana?
chisankho
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Antchito tebulo. Pezani lipoti la kuchuluka kwa antchito omwe adalembedwa ntchito tsiku lililonse la sabata. Sanjani ndi kuchuluka
chisankho
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Antchito tebulo. Pezani lipoti la kuchuluka kwa antchito omwe adalembedwa ntchito pachaka. Sanjani ndi kuchuluka
chisankho
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Antchito tebulo. Pezani chiwerengero cha madipatimenti omwe ali ndi antchito
chisankho
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Antchito tebulo. Pezani mndandanda wamadipatimenti omwe ali ndi antchito opitilira 30
chisankho
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Antchito tebulo. Pezani mndandanda wa ma department_id ndi malipiro apakati a ogwira ntchito mu dipatimenti iliyonse.
chisankho
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Mayiko a pa tebulo. Pezani mndandanda wa dera_id kuchuluka kwa zilembo zonse zamayiko onse omwe muli oposa 60
chisankho
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Antchito tebulo. Pezani mndandanda wama department_id momwe ogwira ntchito angapo (>1) job_ids amagwira ntchito
chisankho
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Antchito tebulo. Pezani mndandanda wa ma manager_id omwe chiwerengero cha omwe ali pansi pake ndi choposa 5 ndipo malipiro onse a omwe ali pansi pake ndi oposa 50000
chisankho
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Antchito tebulo. Pezani mndandanda wa ma manager_ids omwe malipiro ake apakati pa onse omwe ali pansi pake ali pakati pa 6000 mpaka 9000 ndipo samalandira ma bonasi (commission_pct ilibe kanthu)
chisankho
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;
Antchito tebulo. Pezani malipiro ochuluka kuchokera kwa antchito onse job_id omwe amatha ndi mawu oti 'CLERK'
chisankho
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';
Antchito tebulo. Pezani malipiro apamwamba pakati pa malipiro onse apakati pa dipatimentiyi
chisankho
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Antchito tebulo. Pezani chiwerengero cha antchito omwe ali ndi zilembo zofanana m'dzina lawo. Panthawi imodzimodziyo, sonyezani okhawo omwe kutalika kwa maina awo ndi oposa 5 ndipo chiwerengero cha antchito omwe ali ndi dzina lomwelo ndi oposa 20. Sanjani ndi kutalika kwa mayina
chisankho
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);
Kuwonetsa Zomwe Zachokera ku Matebulo Angapo Pogwiritsa Ntchito Majowina
Ogwira Ntchito Patebulo, Madipatimenti, Malo, Mayiko, Zigawo. Pezani mndandanda wamagawo ndi kuchuluka kwa ogwira ntchito mdera lililonse
chisankho
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;
Ogwira Ntchito Patebulo, Madipatimenti, Malo, Mayiko, Zigawo. Pezani zambiri za wogwira ntchito aliyense:
First_name, Last_name, Department, Job, Street, Country, Region
chisankho
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);
Antchito tebulo. Onetsani mamanenjala onse omwe ali ndi antchito oposa 6 omwe ali pansi pawo
chisankho
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;
Antchito tebulo. Onetsani antchito onse omwe sapereka malipoti kwa aliyense
chisankho
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;
Ogwira Ntchito Patebulo, Job_history. Gome la Ogwira ntchito limasunga antchito onse. The Job_history table imasunga antchito omwe achoka pakampani. Pezani lipoti la ogwira ntchito onse komanso momwe alili pakampani (Amagwira ntchito kapena asiya kampaniyo ndi tsiku lonyamuka)
Chitsanzo:
dzina_loyamba | udindo
Jennifer | Adasiya kampaniyo pa 31 Disembala, 2006
Clara | Ikugwira Ntchito Panopa
chisankho
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);
Ogwira Ntchito Patebulo, Madipatimenti, Malo, Mayiko, Zigawo. Pezani mndandanda wa antchito omwe amakhala ku Europe (region_name)
chisankho
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';
Ogwira Ntchito Patebulo, Madipatimenti. Onetsani madipatimenti onse okhala ndi antchito oposa 30
chisankho
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Ogwira Ntchito Patebulo, Madipatimenti. Onetsani antchito onse omwe sali mu dipatimenti iliyonse
chisankho
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;
Ogwira Ntchito Patebulo, Madipatimenti. Onetsani madipatimenti onse omwe mulibe antchito
chisankho
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Antchito tebulo. Onetsani antchito onse omwe alibe aliyense wowagonjera
chisankho
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Ogwira Ntchito Patebulo, Ntchito, Madipatimenti. Onetsani antchito mumtundu: First_name, Job_title, Department_name.
Chitsanzo:
Dzina_loyamba | Job_mutu | Dipatimenti_dzina
Donald | Kutumiza | Clerk Shipping
chisankho
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);
Antchito tebulo. Pezani mndandanda wa antchito omwe mamenejala awo adapeza ntchito mu 2005, koma nthawi yomweyo antchitowo adapeza ntchito isanafike 2005.
chisankho
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');
Antchito tebulo. Pezani mndandanda wa antchito omwe mamenejala awo adapeza ntchito mu Januwale chaka chilichonse ndipo kutalika kwa job_title kwa antchitowa kumapitilira zilembo 15
chisankho
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;
Kugwiritsa Ntchito Subqueries Kuthetsa Mafunso
Antchito tebulo. Pezani mndandanda wa antchito omwe ali ndi dzina lalitali kwambiri.
chisankho
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Antchito tebulo. Pezani mndandanda wa antchito omwe ali ndi malipiro ochulukirapo kuposa malipiro apakati a antchito onse.
chisankho
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Ogwira ntchito, Madipatimenti, Malo tebulo. Pezani mzinda womwe antchito onse amapeza ndalama zochepa.
chisankho
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);
Antchito tebulo. Pezani mndandanda wa antchito omwe manejala wawo amalandila malipiro opitilira 15000.
chisankho
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Ogwira Ntchito Patebulo, Madipatimenti. Onetsani madipatimenti onse omwe mulibe antchito
chisankho
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Antchito tebulo. Onetsani antchito onse omwe si mamenejala
chisankho
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Antchito tebulo. Onetsani mamanenjala onse omwe ali ndi antchito oposa 6 omwe ali pansi pawo
chisankho
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Ogwira Ntchito Patebulo, Madipatimenti. Onetsani antchito omwe amagwira ntchito mu dipatimenti ya IT
chisankho
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Ogwira Ntchito Patebulo, Ntchito, Madipatimenti. Onetsani antchito mumtundu: First_name, Job_title, Department_name.
Chitsanzo:
Dzina_loyamba | Job_mutu | Dipatimenti_dzina
Donald | Kutumiza | Clerk Shipping
chisankho
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;
Antchito tebulo. Pezani mndandanda wa antchito omwe mamenejala awo adapeza ntchito mu 2005, koma nthawi yomweyo antchitowo adapeza ntchito isanafike 2005.
chisankho
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');
Antchito tebulo. Pezani mndandanda wa antchito omwe mamenejala awo adapeza ntchito mu Januwale chaka chilichonse ndipo kutalika kwa job_title kwa antchitowa kumapitilira zilembo 15
chisankho
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;
Ndizo zonse pakadali pano.
Ndikukhulupirira kuti ntchitozo zinali zosangalatsa komanso zosangalatsa.
Ndiwonjezera pa mndandanda wa ntchito momwe ndingathere.
Ndidzakhalanso wokondwa kulandira ndemanga ndi malingaliro.
PS: Ngati wina abwera ndi ntchito yosangalatsa YOSANKHA, lembani mu ndemanga ndipo ndikuwonjezera pamndandanda.
Zikomo.
Source: www.habr.com