Habari, Habr!
Kwa zaidi ya miaka 3 sasa nimekuwa nikifundisha SQL katika vituo mbali mbali vya mafunzo, na moja ya uchunguzi wangu ni kwamba wanafunzi wanajua vizuri na kuelewa SQL ikiwa watapewa kazi, na sio tu kuambiwa juu ya uwezekano na misingi ya kinadharia.
Katika nakala hii, nitashiriki nanyi orodha yangu ya shida ambazo ninawapa wanafunzi kama kazi ya nyumbani na ambayo tunafanya aina anuwai za mawazo, ambayo husababisha uelewa wa kina na wazi wa SQL.
SQL (ΛΙsΛkjuΛΙl; Lugha ya uulizaji iliyopangwa kwa Kiingereza) ni lugha ya kutangaza programu inayotumiwa kuunda, kurekebisha na kudhibiti data katika hifadhidata ya uhusiano inayodhibitiwa na mfumo ufaao wa usimamizi wa hifadhidata.
Unaweza kusoma kuhusu SQL kutoka kwa anuwai .
Nakala hii haikusudiwa kukufundisha SQL kutoka mwanzo.
Basi twende.
Tutatumia wanaojulikana katika Oracle na meza zake ():
Ninakumbuka kuwa tutazingatia kazi SELECT pekee. Hakuna kazi za DML au DDL hapa.
kazi
Kuzuia na Kupanga Data
Jedwali la wafanyikazi. Pata orodha yenye taarifa kuhusu wafanyakazi wote
uamuzi
SELECT * FROM employees
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote wanaoitwa 'David'
uamuzi
SELECT *
FROM employees
WHERE first_name = 'David';
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote walio na job_id sawa na 'IT_PROG'
uamuzi
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote kutoka idara ya 50 (idara_id) wenye mshahara (mshahara) zaidi ya 4000.
uamuzi
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote kutoka idara ya 20 na 30 (idara_id)
uamuzi
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao herufi ya mwisho katika majina yao ni 'a'
uamuzi
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote kutoka idara ya 50 na 80 (idara_id) ambao wana bonasi (thamani katika safu ya commission_pct si tupu)
uamuzi
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao majina yao yana angalau herufi 2 'n'
uamuzi
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao majina yao ni zaidi ya herufi 4
uamuzi
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao mishahara yao iko kati ya 8000 hadi 9000 (pamoja)
uamuzi
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao majina yao yana alama ya '%'
uamuzi
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Jedwali la wafanyikazi. Pata orodha ya vitambulisho vyote vya wasimamizi
uamuzi
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi walio na nafasi zao katika umbizo: Donald(sh_clerk)
uamuzi
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Kutumia Vitendo vya Safu Mlalo Moja ili Kubinafsisha Pato
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao majina yao ni zaidi ya herufi 10
uamuzi
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao wana herufi 'b' kwa majina yao (haijalishi)
uamuzi
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao majina yao yana angalau herufi 2 'a'
uamuzi
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao mishahara yao ni nyongeza ya 1000
uamuzi
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Jedwali la wafanyikazi. Pata nambari ya kwanza yenye tarakimu 3 ya nambari ya simu ya mfanyakazi ikiwa nambari yake iko katika umbizo la XXX.XXX.XXXX
uamuzi
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Jedwali la idara. Pata neno la kwanza kutoka kwa jina la idara kwa wale ambao wana neno zaidi ya moja kwa jina
uamuzi
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Jedwali la wafanyikazi. Pata majina ya wafanyikazi bila herufi ya kwanza na ya mwisho kwa jina
uamuzi
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao herufi ya mwisho katika jina lao ni 'm' na ambao jina ni refu zaidi ya 5
uamuzi
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Jedwali Mbili. Pata tarehe ya Ijumaa ijayo
uamuzi
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao wamefanya kazi kwa kampuni kwa zaidi ya miaka 17
uamuzi
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote ambao nambari ya mwisho ya nambari yao ya simu ni isiyo ya kawaida na ina nambari 3 zilizotenganishwa na nukta.
uamuzi
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote ambao thamani ya job_id baada ya ishara ya '_' ina angalau vibambo 3, lakini thamani hii baada ya '_' si sawa na 'CLERK'
uamuzi
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote kwa kubadilisha '.' katika thamani ya PHONE_NUMBER juu ya '-'
uamuzi
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Kutumia Vitendo vya Kugeuza na Vielezi vya Masharti
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote waliokuja kazini siku ya kwanza ya mwezi (yoyote)
uamuzi
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote waliokuja kufanya kazi mnamo 2008
uamuzi
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
Jedwali DUAL. Onyesha tarehe ya kesho katika umbizo: Kesho ni Siku ya Pili ya Januari
uamuzi
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote na tarehe ambayo kila mfanyakazi alifika kazini katika muundo: Juni 21, 2007
uamuzi
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi walioongeza mishahara kwa 20%. Onyesha mshahara na ishara ya dola
uamuzi
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi wote walioanza kufanya kazi mnamo Februari 2007.
uamuzi
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';
Jedwali DUAL. Toa tarehe ya sasa, + pili, + dakika, + saa, + siku, + mwezi, + mwaka
uamuzi
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;
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote walio na mishahara kamili (mshahara + commission_pct(%)) katika muundo: $24,000.00
uamuzi
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi wote na taarifa kuhusu upatikanaji wa bonasi za mishahara (Ndiyo/Hapana)
uamuzi
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Jedwali la wafanyikazi. Pata kiwango cha mshahara cha kila mfanyakazi: Chini ya 5000 inachukuliwa kuwa kiwango cha chini, Kubwa kuliko au sawa na 5000 na chini ya 10000 inachukuliwa kuwa kiwango cha kawaida, Zaidi ya au sawa na 10000 inachukuliwa kuwa kiwango cha juu.
uamuzi
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;
Nchi za Meza. Kwa kila nchi, onyesha eneo ambalo iko: 1-Ulaya, 2-Amerika, 3-Asia, 4-Afrika (bila Kujiunga)
uamuzi
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;
Kuripoti Data Iliyojumlishwa Kwa Kutumia Kazi za Kikundi
Jedwali la wafanyikazi. Pokea ripoti ya department_id yenye kima cha chini na cha juu zaidi cha mshahara, tarehe za mapema na marehemu za kuwasili kazini na idadi ya wafanyikazi. Panga kwa idadi ya wafanyikazi (kushuka)
uamuzi
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;
Jedwali la wafanyikazi. Ni wafanyikazi wangapi ambao majina yao yanaanza na herufi moja? Panga kwa wingi. Onyesha zile tu ambapo idadi ni kubwa kuliko 1
uamuzi
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;
Jedwali la wafanyikazi. Ni wafanyikazi wangapi wanaofanya kazi katika idara moja na wanapokea mishahara sawa?
uamuzi
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Jedwali la wafanyikazi. Pata ripoti ya wafanyikazi wangapi waliajiriwa kila siku ya juma. Panga kwa wingi
uamuzi
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Jedwali la wafanyikazi. Pata ripoti ya wafanyikazi wangapi waliajiriwa kwa mwaka. Panga kwa wingi
uamuzi
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Jedwali la wafanyikazi. Pata idadi ya idara ambazo zina wafanyikazi
uamuzi
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Jedwali la wafanyikazi. Pata orodha ya vitambulisho vya idara ambavyo vina wafanyakazi zaidi ya 30
uamuzi
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Jedwali la wafanyikazi. Pata orodha ya vitambulisho vya idara na wastani wa wastani wa mshahara wa wafanyikazi katika kila idara.
uamuzi
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Nchi za Meza. Pata orodha_id ya orodha jumla ya herufi zote za majina_ya_mataifa yote ambayo kuna zaidi ya 60
uamuzi
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Jedwali la wafanyikazi. Pata orodha ya vitambulisho vya idara ambapo wafanyikazi wa vitambulisho kadhaa (>1) hufanya kazi
uamuzi
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Jedwali la wafanyikazi. Pata orodha ya vitambulisho vya meneja ambayo idadi ya wasaidizi wake ni kubwa kuliko 5 na jumla ya mishahara yote ya wasaidizi wake ni zaidi ya 50000.
uamuzi
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Jedwali la wafanyikazi. Pata orodha ya vitambulisho vya meneja ambayo wastani wa mshahara wa wasaidizi wake wote ni kati ya 6000 hadi 9000 na ambao hawapati bonasi (commission_pct is empty)
uamuzi
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;
Jedwali la wafanyikazi. Pata mshahara wa juu zaidi kutoka kwa wafanyikazi wote job_id ambao unaisha kwa neno 'CLERK'
uamuzi
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';
Jedwali la wafanyikazi. Pata mshahara wa juu kati ya mishahara yote ya wastani ya idara
uamuzi
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Jedwali la wafanyikazi. Pata idadi ya wafanyikazi walio na idadi sawa ya herufi kwa majina yao. Wakati huo huo, onyesha wale tu ambao urefu wa jina ni zaidi ya 5 na idadi ya wafanyakazi wenye jina sawa ni zaidi ya 20. Panga kwa urefu wa jina
uamuzi
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);
Kuonyesha Data kutoka kwa Jedwali Nyingi Kwa Kutumia Viunga
Wafanyikazi wa Jedwali, Idara, Maeneo, Nchi, Mikoa. Pata orodha ya mikoa na idadi ya wafanyikazi katika kila mkoa
uamuzi
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;
Wafanyikazi wa Jedwali, Idara, Maeneo, Nchi, Mikoa. Pata maelezo ya kina kuhusu kila mfanyakazi:
Jina_la_Kwanza, Jina_la_Ukoo, Idara, Kazi, Mtaa, Nchi, Eneo
uamuzi
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);
Jedwali la wafanyikazi. Onyesha wasimamizi wote ambao wana wafanyakazi zaidi ya 6 walio chini yao
uamuzi
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;
Jedwali la wafanyikazi. Onyesha wafanyikazi wote ambao hawaripoti kwa mtu yeyote
uamuzi
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;
Wafanyikazi wa Jedwali, Historia_ya_ya Kazi. Jedwali la Wafanyikazi huhifadhi wafanyikazi wote. Jedwali la historia_ya kazi huhifadhi wafanyakazi walioacha kampuni. Pata ripoti kuhusu wafanyikazi wote na hali yao katika kampuni (Anafanya kazi au aliacha kampuni na tarehe ya kuondoka)
Mfano:
jina la kwanza | hali
Jennifer | Aliondoka kwenye kampuni tarehe 31 Desemba, 2006
Clara | Inafanya Kazi Kwa Sasa
uamuzi
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);
Wafanyikazi wa Jedwali, Idara, Maeneo, Nchi, Mikoa. Pata orodha ya wafanyikazi wanaoishi Ulaya (jina_la_mkoa)
uamuzi
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';
Wafanyikazi wa Jedwali, Idara. Onyesha idara zote zilizo na wafanyikazi zaidi ya 30
uamuzi
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Wafanyikazi wa Jedwali, Idara. Onyesha wafanyikazi wote ambao hawako katika idara yoyote
uamuzi
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;
Wafanyikazi wa Jedwali, Idara. Onyesha idara zote ambazo hazina wafanyikazi
uamuzi
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Jedwali la wafanyikazi. Onyesha wafanyikazi wote ambao hawana mtu yeyote chini yao
uamuzi
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Wafanyikazi wa Jedwali, Ajira, Idara. Onyesha wafanyakazi katika umbizo: First_name, Job_title, Department_name.
Mfano:
Jina la kwanza | Jina_la_kazi | Jina la Idara
Donald | Usafirishaji | Karani Shipping
uamuzi
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);
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi ambao mameneja wao walipata kazi mnamo 2005, lakini wakati huo huo wafanyikazi hawa wenyewe walipata kazi kabla ya 2005.
uamuzi
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');
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi ambao wasimamizi wao walipata kazi Januari mwaka wowote na urefu wa jina la kazi la wafanyakazi hawa ni zaidi ya vibambo 15
uamuzi
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;
Kutumia Subqueries Kutatua Maswali
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi walio na jina refu zaidi.
uamuzi
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi walio na mshahara mkubwa kuliko wastani wa mshahara wa wafanyikazi wote.
uamuzi
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Wafanyikazi, Idara, Jedwali la Maeneo. Pata jiji ambalo wafanyikazi wote wanapata mapato kidogo zaidi.
uamuzi
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);
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi ambao meneja wao anapokea mshahara wa zaidi ya 15000.
uamuzi
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Wafanyikazi wa Jedwali, Idara. Onyesha idara zote ambazo hazina wafanyikazi
uamuzi
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Jedwali la wafanyikazi. Onyesha wafanyikazi wote ambao sio wasimamizi
uamuzi
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Jedwali la wafanyikazi. Onyesha wasimamizi wote ambao wana wafanyakazi zaidi ya 6 walio chini yao
uamuzi
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Wafanyikazi wa Jedwali, Idara. Onyesha wafanyikazi wanaofanya kazi katika idara ya IT
uamuzi
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Wafanyikazi wa Jedwali, Ajira, Idara. Onyesha wafanyakazi katika umbizo: First_name, Job_title, Department_name.
Mfano:
Jina la kwanza | Jina_la_kazi | Jina la Idara
Donald | Usafirishaji | Karani Shipping
uamuzi
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;
Jedwali la wafanyikazi. Pata orodha ya wafanyikazi ambao mameneja wao walipata kazi mnamo 2005, lakini wakati huo huo wafanyikazi hawa wenyewe walipata kazi kabla ya 2005.
uamuzi
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');
Jedwali la wafanyikazi. Pata orodha ya wafanyakazi ambao wasimamizi wao walipata kazi Januari mwaka wowote na urefu wa jina la kazi la wafanyakazi hawa ni zaidi ya vibambo 15
uamuzi
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;
Ni hayo tu kwa sasa.
Natumaini kazi zilikuwa za kuvutia na za kusisimua.
Nitaongeza kwenye orodha hii ya kazi iwezekanavyo.
Pia nitafurahi kupokea maoni na mapendekezo yoyote.
PS: Ikiwa mtu anakuja na kazi ya kuvutia SELECT, andika kwenye maoni na nitaiongeza kwenye orodha.
Asante.
Chanzo: mapenzi.com