αα½ααααΈ α αΆα!
α’ααααααααααΆα 3 ααααΆαααα αΎαααααααα»αααΆααααααα SQL αα αααα»ααααααααααααααα»ααααααΆααααααα α αΎαααΆααααααααααααααα»αααΊααΆαα·αααααΌαα αα·αααα SQL ααΆαααααααααΎαααααα·αααΎαα½αααααααΌαααΆααααααααΆααα·α αα α αΎααα·αααααΉαααααααΆααα’αααΈααααααΆα αα·αααΌαααααΆαααααΉαααααΉααααΈααα»αααααααα
αα αααα»αα’αααααααα αααα»αααΉαα αααααααααΆαα½αα’αααααΌααααααΈαααα αΆαααααααα»αααααααα»ααααααα±αααα·αααααΆαα·α αα ααΆααααα αα·ααααααΎαααααΎααα αΆαααα½αααααΆααααααααααααα αααααΆααα αααααΆααααααΉααααΈααααα αα·αα αααΆααααΆααα’αααΈ SQL α
SQL (ΛΙsΛkjuΛΙl; English structured query language) ααΊααΆααΆααΆααααααααααα·ααΈαααααΆααααααααΎααΎααααΈαααααΎα αααααα αα·αααααααααααα·αααααααα
αααα»αααΌαααααΆααα·ααααααααααΆααααααααααααααααααααααααααααααααααααααααΌαααααΆααα·ααααααααααααα
α’αααα’αΆα
α’αΆαα’αααΈ SQL ααΈαααααα
α’αααααααααα·αααΆαααααααααααα’αααααΈ SQL ααΈααααΌαα‘αΎαα
α’ααα
αΉαααααα
α
ααΎαααΉαααααΎααΆαααααΈααααΆα
αααα»αααααααααΆααααΆααΎαααΉααα·α
αΆαααΆαααα·α
αα
ααΆα SELECT ααα»αααααα αα·αααΆααα·α
αα
ααΆα DML α¬ DDL αα
ααΈαααααα
ααΆααα·α αα
ααΆαααΆααααααα·α αα·ααααααααα·αααααα
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαααααΆαααααααΆαα’αααΈαα»ααααα·αααΆααα’ααα
ααΆααααααα
α
α·ααα
SELECT * FROM employees
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααΆαααααα 'David'
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE first_name = 'David';
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααΆα job_id ααααΎααΉα 'IT_PROG'
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’ααααΈααΆααααααΆαααΈ 50 (department_id) αααααΆαααααΆαααα (ααααΆαααα) ααΎαααΈ 4000
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’ααααΈααΆααααααΆαααΈ 20 αα·αααΈ 30 (department_id)
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααΆαα’ααααα
α»αααααααα
αααα»αααααααααααα½αααααΊ 'a'
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE first_name LIKE '%a';
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’ααααΈααΆααααααΆαααΈ 50 αα·αααΈ 80 (department_id) αααααΆαααααΆαααααααΆαα (ααααααα
αααα»ααα½ααα 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;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααΆαα’αααα 'b' αα
αααα»αααααααααααα½ααα (αα·ααααααΆααα’ααααααΌα
αα)
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααΆαααααααααΆαα αα
ααΆαα 2 α’αααα 'a'
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name),'a',1,2) > 0;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααααΆααααααΆαα
ααα½αα
αααΎααα 1000
ααΆααααααα
α
α·ααα
SELECT *
FROM employees
WHERE MOD (salary, 1000) = 0;
ααΆααΆααα»ααααα·αα ααα½αααΆαααα 3 αααααααααΌααααααααΌαααααααααααα·αααα·α ααααα·αααΎαααααααααΆαααααα·ααααα»ααααααα XXX.XXX.XXXX
ααΆααααααα
α
α·ααα
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';
ααΆααΆα DUAL α αααα αΆαααΆαααα·α
αααααα
ααααααα’αααααα»αααααααα ααααααα’ααααΆααααααΈααΈαααααααααΆ
ααΆααααααα
α
α·ααα
SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info
FROM DUAL;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈααααααα»ααααα·αααΆααα’αα αα·αααΆαααα·α
ααααααααα·αααα·αααααΆαααααΆααααααααααααααααΎααΆαααΆαααααααα ααααααΈ α’α‘ αααα·αα»ααΆ ααααΆα α’α α α§
ααΆααααααα
α
α·ααα
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';
ααΆααΆα DUAL α αααα αΆαααΆαααα·α
αααααα
αα
α»αααααα + αα·ααΆααΈ + ααΆααΈ + αααα + αααα + αα + ααααΆα
ααΆααααααα
α
α·ααα
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;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·αααΆααα’αααααααΆαααααΆααααααα (ααααΆαααα + commission_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 ααααΌαααΆαα
αΆαααα»αααΆααΆααααα·ααααααα
ααΆααααααα
α
α·ααα
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_ids αααααΆααα»ααααα·αα
αααΎαααΆα 30 ααΆααα
ααΆααααααα
α
α·ααα
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (*) > 30;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα department_ids αα·αααααΆααααααααααααααααα»ααααα»ααααα·ααα
αααα»αααΆααααααΆαααΈαα½ααα
ααΆααααααα
α
α·ααα
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;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα department_ids ααααα»ααααα·ααα job_ids ααΆα
αααΎα (>1) ααααΎααΆα
ααΆααααααα
α
α·ααα
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈααααα manager_ids αααα
ααα½αα’αααααααααααααΆααααααΆα 5 α αΎαααααΌαααααααΆααααααΆααα’ααααααα’αααααααααααααΆααααααααΆααααΊααααΆα 50000
ααΆααααααα
α
α·ααα
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈααααα manager_ids αααααααΆααααααΆαααααααααα’αααααααααααααΆααααΆααα’ααααααααΆαααααα·ααααα»αα
αααααααΈ 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);
ααΆααααα αΆααα·ααααααααΈααΆααΆαα αααΎααααααααΎ Joins
αα» αα»ααααα·α ααΆααααααΆα ααΈααΆαα αααααα αααααα ααα½αααΆααααααΈααααα αα·αα
ααα½ααα»ααααα·ααααα»ααααααααΈαα½αα
ααΆααααααα
α
α·ααα
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, ααΆααααααΆα, ααΆαααΆα, ααααΌα, αααααα, ααααα
ααΆααααααα
α
α·ααα
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. αα»αα»ααααα·ααααααΆαα»ααα»ααααα·αααΆααα’ααα ααΆααΆα Job_history αααααΆαα»ααα»ααααα·ααααααΆαα
αΆαα
ααααΈαααα»αα αα»αα ααα½αααΆααααΆαααΆαααα’αααΈαα»ααααα·αααΆααα’αα αα·αααααΆαααΆααααααα½ααααα
αααα»ααααα»αα αα»α (ααααΎααΆα α¬α
αΆαα
ααααΈαααα»αα αα»αααΆαα½αααΉαααΆαααα·α
ααααααααΆαα
αΆαα
αα)
α§ααΆα ααα:
αααααααααΌα | ααααΆαααΆα
αααααΈα αααΊα | ααΆαα
αΆαα
ααααΈαααα»αα αα»ααα
ααααααΈ α£α‘ ααααααΌ ααααΆα α’α α α¦
ααααΆαααΆ | αα
αα
α»αααααααααα»αααααΎααΆα
ααΆααααααα
α
α·ααα
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α
α§ααΆα ααα:
αααααααααΌα | Job_title | αααααααΆααααααΆα
ααΌααΆαα | ααΆαααΉααααααΌα | αααααααΉααααααΌα
ααΆααααααα
α
α·ααα
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');
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·ααααα’ααααααααααααααα½αααΆαααΆαααΆααα
ααααααΆααααααΆαααΆαα½α α αΎααααααααα job_title αααααα»ααααα·αααΆαααααααΊα
αααΎαααΆα 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α
α§ααΆα ααα:
αααααααααΌα | Job_title | αααααααΆααααααΆα
ααΌααΆαα | ααΆαααΉααααααΌα | αααααααΉααααααΌα
ααΆααααααα
α
α·ααα
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');
ααΆααΆααα»ααααα·αα ααα½αααΆααααααΈαα»ααααα·ααααα’ααααααααααααααα½αααΆαααΆαααΆααα
ααααααΆααααααΆαααΆαα½α α αΎααααααααα job_title αααααα»ααααα·αααΆαααααααΊα
αααΎαααΆα 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;
αααα αΎαααΆααΆααα’αααααααΆααααααααα
αααα»ααααααΉαααΆαα·α
αα
ααΆααα½αα±ααα
αΆααα’αΆαααααα αα·ααα½αα±ααααααΎαα
αααα»αααΉααααααααα
αααα»ααααααΈαα·α
αα
ααΆααααα±ααααΆαα
αααΎαααΆααααα’αΆα
ααααΎαα
ααΆαα
αααα»αβααβααΉαβααΈαααΆαβαααα»αβααΆαβααα½αβααΆαβααα·βαααααβαα·αβαααααβααΆβαα½αβα
PS: ααααα·αααΎααααΆααααΆααααααΆαα½αααΉααα·α αα ααΆα SELECT αα½αα±ααα αΆααα’αΆαααααα ααΌαααααααα αααα»αααα·ααααα α αΎααααα»αααΉαααααααααΆαα αααα»ααααααΈα
ααααααΈαα
ααααα: www.habr.com