ืฉืืื ืืืจ!
ืืืจ ืืืชืจ ื-3 ืฉื ืื ืฉืื ื ืืืื SQL ืืืจืืื ืืืจืื ืฉืื ืื, ืืืืช ืืชืฆืคืืืช ืฉืื ืืื ืฉืกืืืื ืืื ืฉืืืืื ืืืืื ืื ืืื ืืืชืจ ืืช SQL ืื ื ืืชื ืื ืืื ืืฉืืื, ืืื ืจืง ืืืืจืื ืขื ืืืคืฉืจืืืืช ืืืืกืืืืช ืืชืืืืจืืืื.
ืืืืืจ ืื ืืฉืชืฃ ืืชืื ืืจืฉืืืช ืืืฉืืืืช ืฉืื ืฉืื ื ื ืืชื ืืชืืืืืื ืืฉืืขืืจื ืืืช ืืขืืืื ืื ื ืขืืจืืื ืกืืขืืจ ืืืืืช ืืกืืืื ืฉืื ืื, ืื ืฉืืืืื ืืืื ื ืขืืืงื ืืืจืืจื ืฉื SQL.
SQL (หษsหkjuหษl; eng. structured query language) ืืื ืฉืคืช ืชืื ืืช ืืฆืืจืชืืช ืืืฉืืฉืช ืืืฆืืจื, ืฉืื ืื ืื ืืืื ืฉื ื ืชืื ืื ืืืกื ื ืชืื ืื ืืืกื ืืื ืืื ืขื ืืื ืืขืจืืช ื ืืืื ืืกื ื ืชืื ืื ืืชืืืื.
ืืชื ืืืื ืืงืจืื ืขื SQL ืืืืืื
ืืืืจ ืื ืื ื ืืขื ืืืื ืืืชื SQL ืืืคืก.
ืื ืืืื ื ืื.
ื ืฉืชืืฉ ืืืืืข
ืืฆืืื ืื ื ืฉืงืื ืจืง ืืฉืืืืช ื-SELECT. ืืื ืืฉืืืืช ื-DML ื-DDL.
ืืฉืืืืช
ืืืืืช ืืืืื ื ืชืื ืื
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืขื ืืืืข ืขื ืื ืืขืืืืื
ืืืืื
SELECT * FROM employees
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืืฉื 'ืืื'
ืืืืื
SELECT *
FROM employees
WHERE first_name = 'David';
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืขื job_id ืฉืืื ื-'IT_PROG'
ืืืืื
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืืืืืืงื ื-50 (ืืืื_ืืืืงื) ืขื ืฉืืจ (ืฉืืจ) ืืืื ื-4000
ืืืืื
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืืืืืืงื ื-20 ืืืืืืืงื ื-30 (ืืืื_ืืืืงื)
ืืืืื
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืืืช ืืืืจืื ื ืืฉืื ืืื 'a'
ืืืืื
SELECT *
FROM employees
WHERE first_name LIKE '%a';
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืืืืืืงื ื-50 ืืืืืืืงื ื-80 (ืืืื_ืืืืงื) ืฉืืฉ ืืื ืืื ืืก (ืืขืจื ืืขืืืื commission_pct ืืื ื ืจืืง)
ืืืืื
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืฉืื ืืืื ืืคืืืช 2 ืืืชืืืช 'n'
ืืืืื
SELECT *
FROM employees
WHERE first_name LIKE '%n%n%';
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืฉืื ืืจืื ื-4 ืืืชืืืช
ืืืืื
SELECT *
FROM employees
WHERE first_name LIKE '%_____%';
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืฉืืจ ืฉืืื ืืื ืืื 8000 ื-9000 (ืืืื)
ืืืืื
SELECT *
FROM employees
WHERE salary BETWEEN 8000 AND 9000;
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืฉืื ืืืื ืืช ืืกืื '%'
ืืืืื
SELECT *
FROM employees
WHERE first_name LIKE '%%%' ESCAPE '';
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืืืื ืืื ืืืื
ืืืืื
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืขื ืชืคืงืืืืื ืืคืืจืื: Donald(sh_clerk)
ืืืืื
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
ืฉืืืืฉ ืืคืื ืงืฆืืืช ืฉื ืฉืืจื ืืืช ืืืชืืื ืืืฉืืช ืฉื ืืคืื
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืฉืื ืืจืื ื-10 ืืืชืืืช
ืืืืื
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืืืช 'ื' ืืฉืื (ืื ืชืืื ืจืืฉืืืช)
ืืืืื
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืฉืื ืืืื ืืคืืืช 2 ืืืชืืืช 'ื'
ืืืืื
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืฉืืจ ืฉืืื ืืื ืืคืืื ืฉื 1000
ืืืืื
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
ืืืืช ืขืืืืื. ืงืื ืืช ืืืกืคืจ ืืจืืฉืื ืื 3 ืืกืคืจืืช ืฉื ืืกืคืจ ืืืืคืื ืฉื ืืขืืื ืื ืืืกืคืจ ืฉืื ืืื ืืคืืจืื ะฅะฅะฅ.ะฅะฅะฅ.ะฅะฅะฅะฅ
ืืืืื
SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number
FROM employees
WHERE phone_number LIKE '___.___.____';
ืืืืช ืืืืงืืช. ืงืื ืืช ืืืืื ืืจืืฉืื ื ืืฉื ืืืืืงื ืขืืืจ ืืื ืขื ืืืชืจ ืืืืื ืืืช ืืฉื
ืืืืื
SELECT department_name,
SUBSTR (department_name, 1, INSTR (department_name, ' ')-1)
first_word
FROM departments
WHERE INSTR (department_name, ' ') > 0;
ืืืืช ืขืืืืื. ืงืื ืฉืืืช ืขืืืืื ืืื ืืืืช ืืจืืฉืื ื ืืืืืจืื ื ืืฉื
ืืืืื
SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name
FROM employees;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืืืช ืืืืจืื ื ืฉืืื ืืฉื ืฉืืื ื'm' ืืืืจื ืืฉื ืืืื ื-5
ืืืืื
SELECT *
FROM employees
WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5;
ืฉืืืื ืืคืื. ืงืืื ืืช ืืชืืจืื ืฉื ืืื ืฉืืฉื ืืื
ืืืืื
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืขืืืืื ืืืืจื ืืืขืื ื-17 ืฉื ืื
ืืืืื
SELECT *
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืกืคืจื ืืืืจืื ื ืฉื ืืกืคืจ ืืืืคืื ืฉืืื ืืื ืื ืืืืืช ืืืืจืืืช ื-3 ืืกืคืจืื ืืืคืจืืื ืื ืงืืื
ืืืืื
SELECT *
FROM employees
WHERE MOD (SUBSTR (phone_number, -1), 2) != 0
AND INSTR (phone_number,'.',1,3) = 0;
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืขืจื ื-job_id ืฉืืื ืืืจื ืืกืืื '_' ืืืื ืืคืืืช 3 ืชืืืื, ืื ืขืจื ืื ืืืจื ื-'_' ืืื ื ืฉืืื ื-'CLERK'
ืืืืื
SELECT *
FROM employees
WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3
AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK';
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืขื ืืื ืืืืคืช ืื '.' ืืขืจื PHONE_NUMBER ืขื '-'
ืืืืื
SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number
FROM employees;
ืฉืืืืฉ ืืคืื ืงืฆืืืช ืืืจื ืืืืืืืื ืืืชื ืื
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืืืขื ืืขืืืื ืืืื ืืจืืฉืื ืฉื ืืืืืฉ (ืื)
ืืืืื
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'DD') = '01';
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืืืขื ืืขืืืื ื-2008
ืืืืื
SELECT *
FROM employees
WHERE TO_CHAR (hire_date, 'YYYY') = '2008';
ืฉืืืื ืืคืื. ืืฆื ืืช ืืชืืจืื ืฉื ืืืจ ืืคืืจืื: ืืืจ ืืืื ืืฉื ื ืฉื ืื ืืืจ
ืืืืื
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืืชืืจืื ืืืชืืื ืฉืืื ืืคืืจืื: 21 ืืืื ื, 2007
ืืืืื
SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date
FROM employees;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืขื ืฉืืจ ืืืืื ื-20%. ืืฆื ืืฉืืืจืช ืขื ืกืืื ืืืืจ
ืืืืื
SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary
FROM employees;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืฉืืืืขื ืืขืืืื ืืคืืจืืืจ 2007.
ืืืืื
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';
ืฉืืืื ืืคืื. ืืืฆื ืชืืจืื ื ืืืื, + ืฉื ืืื, + ืืงื, + ืฉืขื, + ืืื, + ืืืืฉ, + ืฉื ื
ืืืืื
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;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืขื ืืฉืืืจืืช ืืืืืช (ืืฉืืืจืช + ืขืืื_pct(%)) ืืคืืจืื: $24,000.00
ืืืืื
SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary
FROM employees;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืืขืืืืื ืืืืืข ืขื ืืืื ืืช ืืขื ืงื ืืฉืืจ (ืื/ืื)
ืืืืื
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
ืืืืช ืขืืืืื. ืงืื ืืช ืจืืช ืืฉืืจ ืฉื ืื ืขืืื: ืคืืืช ื-5000 ื ืืฉื ืืจืื ื ืืืื, ืืืื ื-5000 ืื ืฉืืื ื-10000 ืืคืืืช ื-10000 ื ืืฉื ืืจืื ื ืืจืืืืช, ืืืื ืื ืฉืืื ื-XNUMX ื ืืฉื ืืจืื ืืืืื
ืืืืื
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;
ืืืืช ืืืื ืืช. ืขืืืจ ืื ืืืื ื, ืืฆื ืืช ืืืืืจ ืฉืื ืืื ืืืืงืืช: 1-ืืืจืืคื, 2-ืืืจืืงื, 3-ืืกืื, 4-ืืคืจืืงื (ืืื ืืฆืืจืคืืช)
ืืืืื
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;
ืืืืื ืขื ื ืชืื ืื ืืฆืืืจืื ืืืืฆืขืืช ืคืื ืงืฆืืืช ืืงืืืฆื
ืืืืช ืขืืืืื. ืงืืื ืืื ืืคื department_id ืขื ืฉืืจ ืืื ืืืื ืืืงืกืืืื, ืชืืจืืื ืืืขื ืืืงืืืื ืืืืืืจืื ืืืกืคืจ ืขืืืืื. ืืืื ืืคื ืืกืคืจ ืขืืืืื (ืชืืืืจ)
ืืืืื
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;
ืืืืช ืขืืืืื. ืืื ืขืืืืื ืฉืฉืื ืืชืืื ืืืืชื ืืืช? ืืืื ืืคื ืืืืช. ืืฆื ืจืง ืืช ืืื ืฉืืื ืืืกืคืจ ืืืื ื-1
ืืืืื
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;
ืืืืช ืขืืืืื. ืืื ืขืืืืื ืขืืืืื ืืืืชื ืืืืงื ืืืงืืืื ืืช ืืืชื ืฉืืจ?
ืืืืื
SELECT department_id, salary, COUNT (*)
FROM employees
GROUP BY department_id, salary
HAVING COUNT (*) > 1;
ืืืืช ืขืืืืื. ืงืื ืืื ืฉื ืืื ืขืืืืื ืืชืงืืื ืืขืืืื ืืื ืืื ืืฉืืืข. ืืืื ืืคื ืืืืช
ืืืืื
SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_Date, 'Day')
ORDER BY 2 DESC;
ืืืืช ืขืืืืื. ืงืื ืืื ืขื ืืื ืขืืืืื ืืชืงืืื ืืขืืืื ืืคื ืฉื ื. ืืืื ืืคื ืืืืช
ืืืืื
SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*)
FROM employees
GROUP BY TO_CHAR (hire_date, 'YYYY');
ืืืืช ืขืืืืื. ืงืื ืืช ืืกืคืจ ืืืืืงืืช ืฉืืฉ ืืื ืขืืืืื
ืืืืื
SELECT COUNT (COUNT (*)) department_count
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื department_id ืขื ืืืชืจ ื-30 ืขืืืืื
ืืืืื
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืืืืงืืช_ืืืืืช ืืืช ืืฉืืจ ืืืืืฆืข ืืืขืืื ืฉื ืืขืืืืื ืืื ืืืืงื.
ืืืืื
SELECT department_id, ROUND (AVG (salary)) avg_salary
FROM employees
GROUP BY department_id;
ืืืืช ืืืื ืืช. ืงืื ืจืฉืืื ืฉื ืกืืื region_id ืฉื ืื ืืืืชืืืช ืฉื ืื country_names ืฉืืื ืืืชืจ ื-60
ืืืืื
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืืืื_ืืืืงื ืฉืื ืขืืืืื ืขืืืืื ืฉื ืืกืคืจ (>1) ืืืื_ืชืคืงืื
ืืืืื
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
ืืืืช ืขืืืืื. ืงืื ืจืฉืืื ืฉื ืื ืื_ืืืื ืฉืืกืคืจ ืืืคืืคืื ืื ืืืื ื-5 ืืกืืื ืื ืืืฉืืืจืืช ืฉื ืืืคืืคืื ืื ืืืื ื-50000
ืืืืื
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืื ืื_ืืืื ืฉืืฉืืจ ืืืืืฆืข ืฉื ืื ืืืคืืคืื ืื ืืื ืืื 6000 ื-9000 ืฉืืื ื ืืงืืืื ืืื ืืกืื (commission_pct ืจืืง)
ืืืืื
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;
ืืืืช ืขืืืืื. ืงืื ืืช ืืฉืืจ ืืืงืกืืืื ืืื ืืขืืืืื job_id ืฉืืกืชืืื ืืืืื 'CLERK'
ืืืืื
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';
ืืืืช ืขืืืืื. ืงืื ืืช ืืฉืืจ ืืืงืกืืืื ืืืื ืื ืืืฉืืืจืืช ืืืืืฆืขืืช ืืืืืงื
ืืืืื
SELECT MAX (AVG (salary))
FROM employees
GROUP BY department_id;
ืืืืช ืขืืืืื. ืงืื ืืช ืืกืคืจ ืืขืืืืื ืขื ืืืชื ืืกืคืจ ืืืชืืืช ืืฉืื. ืืืงืืื, ืืฆื ืจืง ืืช ืื ืฉืฉืื ืืจืื ื-5 ืืืกืคืจ ืืขืืืืื ืืืืชื ืฉื ืืื ืืืชืจ ื-20. ืืืื ืืคื ืืืจื ืืฉื
ืืืืื
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);
ืืฆืืช ื ืชืื ืื ืืืกืคืจ ืืืืืืช ืืืืฆืขืืช ืืืืืจืื
ืขืืืื ืืืื, ืืืืงืืช, ืืืงืืืื, ืืืื ืืช, ืืืืจืื. ืงืืื ืจืฉืืื ืฉื ืืืืจืื ืืืกืคืจ ืืขืืืืื ืืื ืืืืจ
ืืืืื
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;
ืขืืืื ืืืื, ืืืืงืืช, ืืืงืืืื, ืืืื ืืช, ืืืืจืื. ืงืื ืืืืข ืืคืืจื ืขื ืื ืขืืื:
First_name, Last_name, Department, Job, Street, Country, Region
ืืืืื
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);
ืืืืช ืขืืืืื. ืืฆื ืืช ืื ืืื ืืืื ืฉืืฉ ืืื ืืืชืจ ื-6 ืขืืืืื
ืืืืื
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;
ืืืืช ืขืืืืื. ืืฆื ืืช ืื ืืขืืืืื ืฉืื ืืืืืืื ืืืฃ ืืื
ืืืืื
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;
ืืืืช ืขืืืืื, ืืืกืืืจืื_ืขืืืื. ืืืืช ืืขืืืืื ืืืืกื ืช ืืช ืื ืืขืืืืื. ืืืืช Job_history ืืืืกื ืช ืขืืืืื ืฉืขืืื ืืช ืืืืจื. ืงืืืช ืืื ืขื ืื ืืขืืืืื ืืืขืืื ืืืืจื (ืืืขืกืงืื ืื ืขืืื ืืช ืืืืจื ืขื ืชืืจืื ืืขืืืื)
ืืืืืื:
ืฉื_ืฉื | ืกืึธืืึผืก
ื'ื ืืคืจ | ืขืื ืืช ืืืืจื ื-31 ืืืฆืืืจ, 2006
ืงืืจื | ืืจืืข ืขืืื
ืืืืื
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);
ืขืืืื ืืืื, ืืืืงืืช, ืืืงืืืื, ืืืื ืืช, ืืืืจืื. ืงืื ืจืฉืืื ืฉื ืขืืืืื ืืืชืืืจืจืื ืืืืจืืคื (ืฉื_ืืืืจ)
ืืืืื
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';
ืขืืืื ืฉืืืื, ืืืืงืืช. ืืฆื ืืช ืื ืืืืืงืืช ืขื ืืืชืจ ื-30 ืขืืืืื
ืืืืื
SELECT department_name, COUNT (*)
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
GROUP BY department_name
HAVING COUNT (*) > 30;
ืขืืืื ืฉืืืื, ืืืืงืืช. ืืฆื ืืช ืื ืืขืืืืื ืฉืืื ื ืืืฃ ืืืืงื
ืืืืื
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;
ืขืืืื ืฉืืืื, ืืืืงืืช. ืืฆื ืืช ืื ืืืืืงืืช ืืื ืขืืืืื
ืืืืื
SELECT department_name
FROM employees e
RIGHT JOIN departments d ON (e.department_id = d.department_id)
WHERE first_name IS NULL;
ืืืืช ืขืืืืื. ืืฆื ืืช ืื ืืขืืืืื ืฉืืื ืืื ืืคืืคืื
ืืืืื
SELECT man.first_name
FROM employees emp
RIGHT JOIN employees man ON (emp.manager_id = man.employee_id)
WHERE emp.FIRST_NAME IS NULL;
ืขืืืื ืฉืืืื, ืืฉืจืืช, ืืืืงืืช. ืืฆื ืขืืืืื ืืคืืจืื: First_name, Job_title, Department_name.
ืืืืืื:
ืฉื_ืฉื | ืชืคืงืื | ืฉื ืืืืงื
ืืื ืื | ืืฉืืื | ืคืงืื ืืฉืืื
ืืืืื
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);
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืฉืื ืืืืื ืงืืืื ืขืืืื ืืฉื ืช 2005, ืื ืืืงืืื, ืืขืืืืื ืืืื ืขืฆืื ืงืืืื ืขืืืื ืืคื ื 2005
ืืืืื
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');
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืฉืื ืืืืื ืงืืืื ืขืืืื ืืืืืฉ ืื ืืืจ ืฉื ืื ืฉื ื ืืืืจื ืชืคืงืื_ืืืชืจืช ืืขืืืืื ืืืื ืืื ืืืชืจ ื-15 ืชืืืื
ืืืืื
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;
ืฉืืืืฉ ืืฉืืืืชืืช ืืฉื ื ืืคืชืจืื ืฉืืืืชืืช
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืขื ืืฉื ืืืจืื ืืืืชืจ.
ืืืืื
SELECT *
FROM employees
WHERE LENGTH (first_name) =
(SELECT MAX (LENGTH (first_name)) FROM employees);
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืขื ืฉืืจ ืืืื ืืืฉืืจ ืืืืืฆืข ืฉื ืื ืืขืืืืื.
ืืืืื
SELECT *
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees);
ืขืืืื ืฉืืืื, ืืืืงืืช, ืืืงืืืื. ืงืืื ืืช ืืขืืจ ืฉืื ืืขืืืืื ืืจืืืืืื ืืื ืคืืืช ืืกื ืืื.
ืืืืื
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);
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืฉืืื ืื ืฉืืื ืืงืื ืฉืืจ ืฉื ืืืชืจ ื-15000.
ืืืืื
SELECT *
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE salary > 15000)
ืขืืืื ืฉืืืื, ืืืืงืืช. ืืฆื ืืช ืื ืืืืืงืืช ืืื ืขืืืืื
ืืืืื
SELECT *
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees
WHERE department_id IS NOT NULL);
ืืืืช ืขืืืืื. ืืฆื ืืช ืื ืืขืืืืื ืฉืืื ื ืื ืืืื
ืืืืื
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL)
ืืืืช ืขืืืืื. ืืฆื ืืช ืื ืืื ืืืื ืฉืืฉ ืืื ืืืชืจ ื-6 ืขืืืืื
ืืืืื
SELECT *
FROM employees e
WHERE (SELECT COUNT (*)
FROM employees
WHERE manager_id = e.employee_id) > 6;
ืขืืืื ืฉืืืื, ืืืืงืืช. ืืฆื ืขืืืืื ืฉืขืืืืื ืืืืืงืช ื-IT
ืืืืื
SELECT *
FROM employees
WHERE department_id = (SELECT department_id
FROM departments
WHERE department_name = 'IT');
ืขืืืื ืฉืืืื, ืืฉืจืืช, ืืืืงืืช. ืืฆื ืขืืืืื ืืคืืจืื: First_name, Job_title, Department_name.
ืืืืืื:
ืฉื_ืฉื | ืชืคืงืื | ืฉื ืืืืงื
ืืื ืื | ืืฉืืื | ืคืงืื ืืฉืืื
ืืืืื
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;
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืฉืื ืืืืื ืงืืืื ืขืืืื ืืฉื ืช 2005, ืื ืืืงืืื, ืืขืืืืื ืืืื ืขืฆืื ืงืืืื ืขืืืื ืืคื ื 2005
ืืืืื
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');
ืืืืช ืขืืืืื. ืงืืื ืจืฉืืื ืฉื ืขืืืืื ืฉืื ืืืืื ืงืืืื ืขืืืื ืืืืืฉ ืื ืืืจ ืฉื ืื ืฉื ื ืืืืจื ืชืคืงืื_ืืืชืจืช ืืขืืืืื ืืืื ืืื ืืืชืจ ื-15 ืชืืืื
ืืืืื
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;
ืื ืืื ืืขืช ืขืชื.
ืื ื ืืงืืื ืฉืืืฉืืืืช ืืื ืืขื ืืื ืืช ืืืจืืฉืืช.
ืืืกืืฃ ืืจืฉืืื ืื ืืื ืืืคืฉืจ.
ืืฉืื ืื ืืื ืืขืจื ืืืฆืขื.
ื .ื: ืื ืืืฉืื ืืขืื ืขื ืืฉืืื ืืขื ืืื ืช ื-SELECT, ืืชืื ืืชืืืืืช, ืื ื ืืืกืืฃ ืืืชื ืืจืฉืืื.
ืชืืื.
ืืงืืจ: www.habr.com