Hello Habr!
Sama da shekaru 3 ina koyar da SQL a cibiyoyin horo daban-daban, kuma daya daga cikin abubuwan da na lura shi ne cewa ɗalibai sun ƙware da fahimtar SQL sosai idan aka ba su wani aiki, ba kawai magana game da yuwuwar da tushe na ka'ida ba.
A cikin wannan labarin, zan ba ku jerin ayyuka na da nake ba ɗalibai a matsayin aikin gida kuma a kan su ne muke gudanar da nau'o'in kwakwalwa daban-daban, wanda ke haifar da zurfin fahimtar SQL.
SQL (ˈɛsˈkjuˈɛl; eng. struktured harshen tambaya) harshe ne na bayyana shirye-shirye da ake amfani da shi don ƙirƙira, gyarawa da sarrafa bayanai a cikin ma'ajin bayanai masu alaƙa da tsarin sarrafa bayanai da ya dace ke gudanarwa.
Kuna iya karanta game da SQL daga daban-daban .
Ba a yi nufin wannan labarin don koya muku SQL daga karce ba.
Don haka mu tafi.
Za mu yi amfani da sanannun a cikin Oracle tare da teburinsa ():
Na lura cewa za mu yi la'akari kawai ayyuka akan SELECT. Babu ayyuka akan DML da DDL.
ayyuka
Ƙuntatawa da Rarraba Bayanai
Teburin ma'aikata. Samun lissafi tare da bayanai game da duk ma'aikata
yanke shawara
SELECT * FROM employees
Teburin ma'aikata. Samu jerin sunayen duk ma'aikata masu suna 'David'
yanke shawara
SELECT *
FROM employees
WHERE first_name = 'David';
Teburin ma'aikata. Samun jerin duk ma'aikatan da ke da job_id daidai da 'IT_PROG'
yanke shawara
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
Teburin ma'aikata. Samun jerin duk ma'aikata daga sashen na 50th (Department_id) tare da albashi (albashi) fiye da 4000
yanke shawara
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
Teburin ma'aikata. Samo jerin sunayen duk ma'aikata daga 20th kuma daga sashi na 30 (Department_id)
yanke shawara
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
Teburin ma'aikata. Samu jerin sunayen duk ma'aikatan da harafin ƙarshe a cikin sunansu shine 'a'
yanke shawara
SELECT *
FROM employees
WHERE first_name LIKE '%a';
Teburin ma'aikata. Samu jerin sunayen duk ma'aikata daga 50th kuma daga sashen 80th (Department_id) waɗanda ke da kari (darajar da ke cikin ginshiƙin commission_pct ba komai bane)
yanke shawara
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
Teburin ma'aikata. Samu jerin sunayen duk ma'aikatan da sunansu ya ƙunshi aƙalla haruffa 2 'n'
yanke shawara
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
Teburin ma'aikata. Samu jerin sunayen duk ma'aikatan da sunansu ya fi haruffa 4
yanke shawara
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
Teburin ma'aikata. Samu jerin sunayen duk ma'aikatan da albashinsu ya kasance tsakanin 8000 zuwa 9000 (haɗe)
yanke shawara
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
Teburin ma'aikata. Samun jerin duk ma'aikatan da sunansu ya ƙunshi alamar '%'
yanke shawara
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
Teburin ma'aikata. Samu jerin duk ID ɗin mai sarrafa
yanke shawara
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
Teburin ma'aikata. Samun jerin ma'aikata tare da matsayinsu a cikin tsari: Donald (sh_clerk)
yanke shawara
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Amfani da Ayyukan Layi Guda ɗaya don Keɓance Fitarwa
Teburin ma'aikata. Samu jerin sunayen duk ma'aikatan da sunansu ya fi haruffa 10
yanke shawara
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
Teburin ma'aikata. Samo jerin sunayen duk ma'aikatan da ke da harafin 'b' a cikin sunansu (harafin da ba ya da hankali)
yanke shawara
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
Teburin ma'aikata. Samu jerin sunayen duk ma'aikatan da sunansu ya ƙunshi aƙalla haruffa 2 'a'
yanke shawara
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
Teburin ma'aikata. Sami jerin sunayen duk ma'aikatan da albashinsu ya ninka na 1000
yanke shawara
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
Teburin ma'aikata. Samu lambar lambobi 3 na farko na lambar wayar ma'aikaci idan lambar sa tana cikin tsari ХХХ.ХХХ.ХХХХ
yanke shawara
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
Teburin sassan. Samu kalmar farko daga sunan sashen ga waɗanda ke da kalma fiye da ɗaya a cikin sunan
yanke shawara
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
Teburin ma'aikata. Samo sunayen ma'aikata ba tare da harafin farko da na ƙarshe a cikin sunan ba
yanke shawara
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
Teburin ma'aikata. Samun jerin duk ma'aikatan da harafin ƙarshe a cikin sunan yayi daidai da 'm' kuma tsawon sunan ya fi 5
yanke shawara
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
Tebura biyu. Samu ranar Juma'a mai zuwa
yanke shawara
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
Teburin ma'aikata. Samun jerin duk ma'aikatan da suka kasance tare da kamfanin sama da shekaru 17
yanke shawara
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
Teburin ma'aikata. Samo jerin sunayen duk ma'aikatan da lambar ƙarshe ta lambar wayarsu ba ta da kyau kuma ta ƙunshi lambobi 3 da aka raba su da digo.
yanke shawara
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
Teburin ma'aikata. Samun jerin sunayen duk ma'aikatan da ƙimar aikinsu bayan alamar '_' tana da aƙalla haruffa 3, amma wannan ƙimar bayan '_' ba ta kai 'CLERK'
yanke shawara
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
Teburin ma'aikata. Samu jerin sunayen duk ma'aikata ta wurin maye gurbin duk '.' a cikin ƙimar PHONE_NUMBER na '-'
yanke shawara
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
Amfani da Ayyukan Juyawa da Kalamai na Sharadi
Teburin ma'aikata. Samun jerin duk ma'aikatan da suka zo aiki a ranar farko ta wata (kowane)
yanke shawara
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
Teburin ma'aikata. Samun jerin duk ma'aikatan da suka zo aiki a 2008
yanke shawara
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
DUAL tebur. Nuna ranar gobe a cikin tsari: Gobe rana ta biyu ga Janairu
yanke shawara
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
Teburin ma'aikata. Sami jerin sunayen duk ma'aikata da ranar farawarsu a cikin tsari: 21 ga Yuni, 2007
yanke shawara
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
Teburin ma'aikata. Samo jerin sunayen ma'aikata masu karin albashi da kashi 20%. Nuna albashi tare da alamar dala
yanke shawara
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
Teburin ma'aikata. Samun jerin duk ma'aikatan da suka zo aiki a watan Fabrairun 2007.
yanke shawara
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';
DUAL tebur. Fitar kwanan wata, + na biyu, + minti, + awa, + rana, + wata, + shekara
yanke shawara
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;
Teburin ma'aikata. Samun jerin duk ma'aikata masu cikakken albashi (albashi + commission_pct(%) a cikin tsari: $24,000.00
yanke shawara
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
Teburin ma'aikata. Samun jerin duk ma'aikata da bayanai game da wadatar kari na albashi (Ee / A'a)
yanke shawara
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
Teburin ma'aikata. Samun matakin albashi na kowane ma'aikaci: Kasa da 5000 ana ɗaukar matakin ƙasa, Mafi girma ko daidai da 5000 kuma ƙasa da 10000 ana ɗaukar matakin al'ada, Mafi girma ko daidai da 10000 ana ɗaukar matakin Babban matakin.
yanke shawara
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;
Teburin ƙasashe. Ga kowace ƙasa, nuna yankin da take: 1-Turai, 2-Amurka, 3-Asiya, 4-Afrika (ba tare da Shiga ba)
yanke shawara
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;
Bayar da Haɗaɗɗen Bayanai Ta Amfani da Ayyukan Ƙungiya
Teburin ma'aikata. Sami rahoto ta sashen_id tare da mafi ƙarancin albashi da mafi girman albashi, farkon zuwa ƙarshen kwanan watan da adadin ma'aikata. Tsara ta adadin ma'aikata (desc)
yanke shawara
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;
Teburin ma'aikata. Ma'aikata nawa ne sunayensu ya fara da harafi ɗaya? Tsara da yawa. Nuna waɗanda adadin ya fi 1 kawai
yanke shawara
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;
Teburin ma'aikata. Ma’aikata nawa ne suke aiki a sashe daya kuma suke karbar albashi daya?
yanke shawara
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
Teburin ma'aikata. Sami rahoton nawa ma'aikata aka dauka a kowace rana ta mako. Tsara da yawa
yanke shawara
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
Teburin ma'aikata. Samu rahoto kan ma'aikata nawa aka dauka a shekara. Tsara da yawa
yanke shawara
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
Teburin ma'aikata. Sami adadin sassan da ke da ma'aikata
yanke shawara
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
Teburin ma'aikata. Samu jerin sassan_id tare da ma'aikata sama da 30
yanke shawara
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
Teburin ma'aikata. Sami jerin sassan_ids da matsakaicin matsakaicin albashin ma'aikata a kowane sashe.
yanke shawara
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
Teburin ƙasashe. Samun jerin jimlar yanki_id na duk haruffan duk sunayen ƙasar waɗanda sama da 60 a cikinsu
yanke shawara
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
Teburin ma'aikata. Samu jerin sashen_id wanda ma'aikata da yawa (>1) job_id ke aiki
yanke shawara
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
Teburin ma'aikata. A sami list na manager_id wanda yawan ma'aikatansa ya zarce 5 kuma adadin albashin da yake karkashinsa ya haura 50000.
yanke shawara
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
Teburin ma'aikata. Sami lissafin manager_id wanda matsakaicin albashin duk ma'aikatansa yana tsakanin 6000 zuwa 9000 waɗanda ba sa karɓar kari (commission_pct ba komai)
yanke shawara
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;
Teburin ma'aikata. Sami mafi girman albashi daga duk ma'aikata job_id wanda ya ƙare da kalmar 'CLERK'
yanke shawara
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';
Teburin ma'aikata. Sami mafi girman albashi tsakanin duk matsakaicin albashin sashen
yanke shawara
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
Teburin ma'aikata. Sami adadin ma'aikata masu adadin haruffa iri ɗaya a cikin sunayensu. A lokaci guda kuma, nuna wa waɗanda sunansu ya fi 5 kawai kuma adadin ma'aikatan da ke da suna iri ɗaya ya fi 20. Tsare da tsayin suna.
yanke shawara
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);
Nuna bayanai daga Tables da yawa Ta amfani da Joins
Ma'aikatan Tebur, Sassan, Wurare, Kasashe, Yankuna. Samu jerin yankuna da adadin ma'aikata a kowane yanki
yanke shawara
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;
Ma'aikatan Tebur, Sassan, Wurare, Kasashe, Yankuna. Samun cikakken bayani game da kowane ma'aikaci:
Sunan farko, Sunan Ƙarshe, Sashe, Aiki, Titin, Ƙasa, Yanki
yanke shawara
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);
Teburin ma'aikata. Nuna duk manajoji waɗanda ke da ma'aikata sama da 6
yanke shawara
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;
Teburin ma'aikata. Nuna duk ma'aikatan da ba su bayar da rahoto ga kowa ba
yanke shawara
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;
Tebur na ma'aikata, Ayuba_history. Teburin Ma'aikata yana adana duk ma'aikata. Teburin Ayuba_history yana adana ma'aikatan da suka bar kamfanin. Samun rahoto kan duk ma'aikata da matsayinsu a cikin kamfani (Aikin ko barin kamfanin tare da ranar tashi)
Alal misali:
sunan farko | matsayi
jennifer | Bar kamfanin a ranar 31 ga Disamba, 2006
Clara | A halin yanzu Aiki
yanke shawara
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);
Ma'aikatan Tebur, Sassan, Wurare, Kasashe, Yankuna. Samu jerin ma'aikatan da ke zaune a Turai (yankin_name)
yanke shawara
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';
Ma'aikatan Tebur, Sashen. Nuna duk sassan da ma'aikata sama da 30
yanke shawara
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
Ma'aikatan Tebur, Sashen. Nuna duk ma'aikatan da ba sa cikin kowane sashe
yanke shawara
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;
Ma'aikatan Tebur, Sashen. Nuna duk sassan da babu ma'aikata
yanke shawara
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
Teburin ma'aikata. Nuna duk ma'aikatan da ba su da ma'aikata
yanke shawara
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
Ma'aikatan Tebur, Ayyuka, Sassan. Nuna ma'aikata a cikin tsari: First_name, Ayuba_title, Sashen_name.
Alal misali:
sunan farko | taken aiki | Sunan sashen
Donald | sufuri | Shipping ma'aikaci
yanke shawara
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);
Teburin ma'aikata. A samu jerin sunayen ma’aikatan da manajoji suka samu aiki a shekarar 2005, amma a lokaci guda wadannan ma’aikatan da kansu sun sami aiki kafin 2005.
yanke shawara
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');
Teburin ma'aikata. Samun jerin sunayen ma'aikatan da manajoji suka sami aiki a watan Janairu na kowace shekara kuma tsawon aikin_ma'aikatan ya wuce haruffa 15.
yanke shawara
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;
Amfani da Subqueries don warware Tambayoyi
Teburin ma'aikata. Sami jerin sunayen ma'aikata da mafi tsayi suna.
yanke shawara
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
Teburin ma'aikata. Samu jerin sunayen ma'aikata da albashi mafi girma fiye da matsakaicin albashin duk ma'aikata.
yanke shawara
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
Ma'aikatan Tebur, Sassan, Wurare. Sami birnin da ma'aikata ke samun mafi ƙarancin kuɗi a cikin duka.
yanke shawara
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);
Teburin ma'aikata. Samo jerin sunayen ma'aikatan da manajan su ke karbar albashi sama da 15000.
yanke shawara
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
Ma'aikatan Tebur, Sashen. Nuna duk sassan da babu ma'aikata
yanke shawara
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
Teburin ma'aikata. Nuna duk ma'aikatan da ba manajoji ba
yanke shawara
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
Teburin ma'aikata. Nuna duk manajoji waɗanda ke da ma'aikata sama da 6
yanke shawara
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
Ma'aikatan Tebur, Sashen. Nuna ma'aikatan da ke aiki a sashen IT
yanke shawara
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
Ma'aikatan Tebur, Ayyuka, Sassan. Nuna ma'aikata a cikin tsari: First_name, Ayuba_title, Sashen_name.
Alal misali:
sunan farko | taken aiki | Sunan sashen
Donald | sufuri | Shipping ma'aikaci
yanke shawara
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;
Teburin ma'aikata. A samu jerin sunayen ma’aikatan da manajoji suka samu aiki a shekarar 2005, amma a lokaci guda wadannan ma’aikatan da kansu sun sami aiki kafin 2005.
yanke shawara
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');
Teburin ma'aikata. Samun jerin sunayen ma'aikatan da manajoji suka sami aiki a watan Janairu na kowace shekara kuma tsawon aikin_ma'aikatan ya wuce haruffa 15.
yanke shawara
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;
Shi ke nan a yanzu.
Ina fata ayyukan sun kasance masu ban sha'awa da ban sha'awa.
Zan ƙara zuwa wannan jerin gwargwadon iko.
Zan kuma yi farin ciki da kowane sharhi da shawarwari.
PS: Idan wani ya zo da aiki mai ban sha'awa akan SELECT, rubuta a cikin sharhi, zan ƙara shi zuwa jerin.
Спасибо.
source: www.habr.com