áááºá¹ááá¬áá« Habrá
áá±á·áá»áá·áºáá±ážáá¬áá¡áá®ážáá®ážááœáẠSQL ááᯠá ááŸá áºáá»á±á¬áº áááºááŒá¬ážáá²á·ááŒá®ážá áá»áœááºá¯ááºá áá±á·áá¬ááœá±á·ááŸááá»ááºáá áºáá¯ááŸá¬ áá»á±á¬ááºážáá¬ážáá»á¬ážááẠá¡áá¯ááºáá áºáá¯ááᯠáá±ážá¡ááºáá«á ááŒá áºááá¯ááºááŒá±áá»á¬ážááŸáá·áº áá®á¡áá¯áá®á¡ááŒá±áá¶á¡á¯ááºááŒá áºáá»á¬ážááᯠááŒá±á¬ááá¯áá¯á¶áá¬áááá² SQL ááᯠáá»áœááºážáá»ááºááŒá®áž áá¬ážáááºááá¯ááºá á±áááºááŸá¬ ááá¯ááá¯áá±á¬ááºážááœááºáá«áááºá
á€áá±á¬ááºážáá«ážááœááºá áá»áœááºá¯ááºááẠáá»á±á¬ááºážáá¬ážáá»á¬ážá¡á¬áž á¡áááºá á¬á¡ááŒá Ạáá»áœááºá¯ááºáá±ážáá±á¬ á¡áá¯ááºáá»á¬ážá á¬áááºážááŸáá·áº SQL ááᯠáááºáá²ááŸááºážáááºážá áœá¬ áá¬ážáááºááá¯ááºá á±ááẠáŠážáááºááá·áº áŠážááŸá±á¬ááºáá¯ááºááá¯ááºážá¡áá»áá¯ážáá»áá¯ážááᯠáá¯ááºáá±á¬ááºáá±ážááá·áº áá¯ááºáááºážáá»á¬ážááᯠáá»áŸáá±áá«áááºá
SQL (ËÉsËkjuËÉl; eng. structured query language) ááẠááá·áºáá»á±á¬áºáá±á¬ áá±áá¬áá±á·á
Ạá
á®áá¶ááá·áºááœá²ááŸá¯á
áá
áºááŒáá·áº á
á®áá¶ááá·áºááœá²áá±á¬ áááºá
ááºáá±áá¬áá±á·á
áºáá
áºáá¯ááœáẠá¡áá»ááºá¡áááºáááºáá®ážááŒááºážá ááŒááºáááºááŒááºážááŸáá·áº á
á®áá¶ááá·áºááœá²ááẠá¡áá¯á¶ážááŒá¯ááá·áº ááŒá±ááŒá¬áá¬ážáá±á¬ áááá¯ááááºážáááºážáá¬áá¬á
áá¬ážáá
áºáá¯ááŒá
áºáááºá
SQL á¡ááŒá±á¬ááºáž á¡áá»áá¯ážáá»áá¯ážááᯠáááºááá¯ááºáá«áááºá
á€áá±á¬ááºážáá«ážááẠááá·áºá¡á¬áž SQL ááᯠá¡á
á០áááºááŒá¬ážááẠáááºááœááºááŒááºážááá¯ááºáá«á
áá«ááŒá±á¬ááºá·ááá¯ááŒááºááááºááá¯á·áááºááœá¬ážáá«á
áá°ááááá¯áá¯á¶ážáááºá
áá»áœááºá¯ááºááá¯á·ááẠSELECT ááœáẠáá¯ááºáá±á¬ááºá
áá¬áá»á¬ážááá¯áᬠááá·áºááœááºážá
ááºážá
á¬ážáááºááᯠáá»áœááºá¯ááºáááááŒá¯áááá«áááºá DML ááŸáá·áº DDL ááœáẠáá¯ááºáá±á¬ááºá
áá¬áá»á¬áž áááŸááá«á
áá¬áááºáá»á¬ážááá¯
ááá·áºáááºááŒááºážááŸáá·áº áá±áá¬á á®ááŒááºáž
áááºáááºážá
á¬ážááœá²á áááºáááºážá¡á¬ážáá¯á¶ážá á¡áá»ááºá¡áááºáá«ááŸááá±á¬ á
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT * FROM employees
áááºáááºážá
á¬ážááœá²á 'David' á¡áááºááŸá áááºáááºážá¡á¬ážáá¯á¶ážá á
á¬áááºážááᯠááá°áá«
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE first_name = 'David';
áááºáááºážá
á¬ážááœá²á 'IT_PROG' ááŸáá·áº áá®áá»áŸáá±á¬ job_id ááŸááá±á¬ áááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážá á
á¬áááºážááᯠááá°áá«
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
áááºáááºážá
á¬ážááœá²á áá
ᬠ(áá
á¬) 50 áááºáá»á¬ážáá±á¬ áá¬á 4000 (department_id) á០áááºáááºážáá»á¬ážá
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE department_id = 50 AND salary > 4000;
áááºáááºážá
á¬ážááœá²á 20th ááŸáá·áº 30th áá¬á (department_id) á០áááºáááºážáá»á¬ážá
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE department_id = 20 OR department_id = 30;
áááºáááºážá
á¬ážááœá²á áááºážááá¯á·áá¡ááẠáá±á¬ááºáá¯á¶ážá
á¬áá¯á¶áž 'a' ááŸááá±á¬ áááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážá á
á¬áááºážááᯠááá°áá«
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE first_name LIKE '%a';
áááºáááºážá
á¬ážááœá²á áá±á¬áááºá
áºáá
áºáá¯áááŸááá±á¬ 50th ááŸáá·áº 80th áá¬á (department_id) á០áááºáááºážáá»á¬ážá
á¬áááºážááᯠááá°áá« (áá±á¬áºáááŸááº_pct áá±á¬áºáá¶ááŸá áááºááá¯ážááẠááá¬ááá¯ááºáá«)
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE (department_id = 50 OR department_id = 80)
AND commission_pct IS NOT NULL;
áááºáááºážá
á¬ážááœá²á á¡áááºážáá¯á¶áž 'n' á
á¬áá¯á¶áž 2 áá¯á¶ážáá«ááŸááá±á¬ á¡áááºááŸááá±á¬ áááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážá á
á¬áááºážááᯠááá°áá«
áá¯á¶ážááŒááºáá»ááº
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 '';
áááºáááºážá
á¬ážááœá²á áááºáá±áá»á¬ ID áá»á¬ážá¡á¬ážáá¯á¶ážáá
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
áááºáááºážá
á¬ážááœá²á áá±á¬áºáááº- Donald(sh_clerk)á
áá¯á¶ážááŒááºáá»ááº
SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees;
Output ááá¯á áááºááŒáá¯ááºááŒááºáááºááẠSingle-Row Functions ááá¯á¡áá¯á¶ážááŒá¯ááŒááºážá
áááºáááºážá
á¬ážááœá²á á¡ááẠá
á¬áá¯á¶áž 10 áá¯á¶ážááẠááá¯ááŸááºáá±á¬ áááºáááºážá¡á¬ážáá¯á¶ážá á
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE LENGTH (first_name) > 10;
áááºáááºážá
á¬ážááœá²á áááºážááá¯á·áá¡áááºááœáẠá¡áá¹ááᬠ'b' áá«áá±á¬ áááºáááºážá¡á¬ážáá¯á¶ážá á
á¬áááºážááᯠááá°áá« (á¡áá±ážá
áááºááá«áá±á¬)
áá¯á¶ážááŒááºáá»ááº
SELECT *
FROM employees
WHERE INSTR (LOWER (first_name), 'b') > 0;
áááºáááºážá
á¬ážááœá²á á¡áááºážáá¯á¶áž 'a' á
á¬áá¯á¶áž 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;
áááºáááºážá
á¬ážááœá²á '_' ááá¯ááºážáá¯ááºáá±á¬ááºááœáẠá¡áááºážáá¯á¶áž á
á¬áá¯á¶áž 3 áá¯á¶ážáá«ááá·áº job_id áááºááá¯ážááŸááá±á¬ áááºáááºážáá»á¬ážáá
á¬áááºážááᯠááá°áá«á ááá¯á·áá±á¬áº '_' ááŒá®ážáá±á¬áẠá€áááºááá¯ážááẠ'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;
Conversion Functions ááŸáá·áº Conditional Expressions ááá¯á¡áá¯á¶ážááŒá¯ááŒááºážá
áááºáááºážá
á¬ážááœá²á ááááááá±á·ááœáẠá¡áá¯ááºáá¬áá¯ááºáá±á¬ áááºáááºážá¡á¬ážáá¯á¶áž (áááºáá°áááá¯) á
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
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;
áááºáááºážá
á¬ážááœá²á áááºáááºážá¡á¬ážáá¯á¶ážáá
á¬áááºážááŸáá·áº áááºážááá¯á·áá
áááºááá·áºáááºá
áœá²ááᯠáá±á¬áºáááº- ááœááºá 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;
áááºáááºážá
á¬ážááœá²á áááá áá¯ááŸá
Ạáá±áá±á«áºáá«áá®áááœáẠá¡áá¯ááºáá¬áá¯ááºáá±á¬ áááºáááºážáá»á¬ážá
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
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;
áááºáááºážá
á¬ážááœá²á áááºáááºážá¡á¬ážáá¯á¶ážáá
á¬áááºážááŸáá·áº áá
á¬áá±á¬áááºá
áºáááŸáááá¯ááºááŸá¯ááá¯ááºáᬠá¡áá»ááºá¡áááºááᯠááá°áá« (Yes/No)
áá¯á¶ážááŒááºáá»ááº
SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus
FROM employees;
áááºáááºážá
á¬ážááœá²á áááºáááºážáá
áºáŠážá
á®á áá
á¬á¡ááá·áºááᯠááá°áá«- 5000 á¡á±á¬ááºááᯠá¡áááá·áºá¡ááá·áºáᯠáááºááŸááºáááºá 5000 ááẠááŒá®ážááẠááá¯á·ááá¯áẠáá®áááºá 10000 á¡á±á¬ááºááᯠáá¯á¶ááŸááºá¡ááá·áºáᯠáááºááŸááºáááºá 10000 ááẠááŒá®ážááẠááá¯á·ááá¯áẠáá®ááẠHigh level áᯠáááºááŸááºáááº
áá¯á¶ážááŒááºáá»ááº
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;
ááá¯ááºáá¶áá»á¬ážááá¬ážá ááá¯ááºáá¶áá
áºáá¯á
á®á¡ááœááºá áááºážáááºááŸááá¬áá±áááᯠááŒááá«- á-á¥áá±á¬áá á-á¡áá±áááá á-á¡á¬ááŸá á-á¡á¬áááá (ááá°ážáá±á«ááºážáá²)
áá¯á¶ážááŒááºáá»ááº
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;
Group Functions ááá¯á¡áá¯á¶ážááŒá¯á á á¯á ááºážáá¬ážáá±á¬áá±áá¬ááᯠá¡á á®áááºáá¶ááŒááºážá
áááºáááºážá
á¬ážááœá²á á¡áááá·áºáá¯á¶ážááŸáá·áº á¡áá»á¬ážáá¯á¶ážáá
á¬á á¡á
á±á¬ááá¯ááºážááŸáá·áº áá±á¬ááºáá»áá±á¬ááºááŸáááá·áºáááºá
áœá²ááŸáá·áº áááºáááºážá¡áá±á¡ááœááºááá¯á·ááŸáá·áºá¡áá° department_id á០á¡á
á®áááºáá¶á
á¬ááᯠááá°áá«á áááºáááºážá¡áá±á¡ááœááºá¡ááá¯ááºá
á®áá« (desc)
áá¯á¶ážááŒááºáá»ááº
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;
áááºáááºážá
á¬ážááœá²á áááºáááºáž 30 áá»á±á¬áºááŸáá·áºá¡áá° department_id á
á¬áááºážááá¯ááá°áá«á
áá¯á¶ážááŒááºáá»ááº
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;
ááá¯ááºáá¶áá»á¬ážááá¬ážá 60 áá»á±á¬áºááŸáááá·áº country_name á¡á¬ážáá¯á¶ážá á
á¬áá¯á¶ážá¡á¬ážáá¯á¶ážá region_id áá±á«ááºážá
á¯á
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT region_id
FROM countries
GROUP BY region_id
HAVING SUM (LENGTH (country_name)) > 60;
áááºáááºážá
á¬ážááœá²á (>1) job_id á¡áá»á¬ážá¡ááŒá¬áž á¡áá¯ááºáá¯ááºáá±á¬ áááºáááºážáá»á¬ážá¡ááœáẠáá¬á_id á
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT (DISTINCT job_id) > 1;
áááºáááºážá
á¬ážááœá²á áááºá¡á±á¬ááºáááºáá¬ážá¡áá±á¡ááœáẠ5 áááºáá»á¬ážáá±á¬ áááºáá±áá»á¬_id á
á¬áááºážááᯠááá°ááŒá®áž áááºá¡á±á¬ááºáááºáá¬ážáá»á¬ážá áá
á¬á¡á¬ážáá¯á¶áž 50000 ááẠááá¯ááŒá®ážáááº
áá¯á¶ážááŒááºáá»ááº
SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT (*) > 5 AND SUM (salary) > 50000;
áááºáááºážá
á¬ážááœá²á áá±á¬áááºá
áºááááŸááá±á¬ áá°ááááºá¡á±á¬ááºáááºáá¬ážá¡á¬ážáá¯á¶ážá áá»ááºážáá»áŸáá
ᬠ6000 ááŸáá·áº 9000 ááŒá¬ážááŸááá±á¬ manager_id á
á¬áááºážááᯠááá°áá« (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;
áááºáááºážá
á¬ážááœá²á '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 ááá¯á¡áá¯á¶ážááŒá¯ááŒá®áž Multiple Tables á០Data ááá¯ááŒáááŒááºážá
ááá¬ážáááºáááºážáá»á¬ážá áá¬ááá»á¬ážá áááºáá±áá¬áá»á¬ážá ááá¯ááºáá¶áá»á¬ážá ááá¯ááºážáá±áááŒá®ážáá»á¬ážá ááá¯ááºážáá±áááŒá®ážá
á¬áááºážááŸáá·áº áá±ááá
áºáá¯á
á®ááŸá áááºáááºážá¡áá±á¡ááœááºááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
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);
áááºáááºážá
á¬ážááœá²á áááºáááºáž á áŠážáááºááá¯áá±á¬ áááºáá±áá»á¬á¡á¬ážáá¯á¶ážááᯠááŒáá«á
áá¯á¶ážááŒááºáá»ááº
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á Employee table ááẠáááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážááᯠááááºážáááºážáá¬ážáááºá Job_history table ááẠáá¯áá¹ááá®á០ááœááºááœá¬ážáá±á¬ áááºáááºážáá»á¬ážááᯠááááºážáááºážáá¬ážáááºá áá¯áá¹ááá®ááŸááááºáááºážáá»á¬ážá¡á¬ážáá¯á¶ážááŸáá·áº áááºážááá¯á·áá¡ááŒá±á¡áá±ááá¯ááºáᬠá¡á
á®áááºáá¶á
á¬ááá¯ááá°áá« (á¡áá¯ááºááœááºáá»ááẠááá¯á·ááá¯áẠáá¯áá¹ááá®ááŸááœááºááœá¬ááá·áºáá±á·á
áœá²ááŒáá·áº)
á¥ááá¬:
first_name | á¡ááŒá±á¡áá±
áá»áá®áᬠ| áááá áá®áááºáᬠáá ááœáẠáá¯áá¹ááá®á០ááœááºááœá¬áá²á·áááºá
Clara | áááºááŸáá¡áá¯ááºáá¯ááºáá±áá«áááºá
áá¯á¶ážááŒááºáá»ááº
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á
á¥ááá¬:
first_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);
áááºáááºážá
á¬ážááœá²á áááºáá±áá»á¬áá»á¬ážááẠáááá
áá¯ááŸá
áºááœáẠá¡áá¯ááºááá²á·áá±á¬ áááºáááºážáá»á¬ážá
á¬áááºážááᯠááá°áá²á·áá±á¬áºáááºáž áá
áºáá»áááºáááºážááœáẠá¡ááá¯áá« á¡áá¯ááºááá¬ážáá»á¬ážááá¯ááºááá¯áẠáááá
áá¯ááŸá
áºáááá¯ááºáá® á¡áá¯ááºááá²á·áááºá
áá¯á¶ážááŒááºáá»ááº
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;
áá±ážááœááºážáá»á¬ážááá¯ááŒá±ááŸááºážááẠSubqueries ááá¯á¡áá¯á¶ážááŒá¯ááŒááºážá
áááºáááºážá
á¬ážááœá²á áá¬áááºá¡ááŸááºáá¯á¶áž áááºáááºážá
á¬áááºážááᯠááá°áá«á
áá¯á¶ážááŒááºáá»ááº
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)
áááºáááºážá
á¬ážááœá²á áááºáááºáž á áŠážáááºááá¯áá±á¬ áááºáá±áá»á¬á¡á¬ážáá¯á¶ážááᯠááŒáá«á
áá¯á¶ážááŒááºáá»ááº
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á
á¥ááá¬:
first_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;
áááºáááºážá
á¬ážááœá²á áááºáá±áá»á¬áá»á¬ážááẠáááá
áá¯ááŸá
áºááœáẠá¡áá¯ááºááá²á·áá±á¬ áááºáááºážáá»á¬ážá
á¬áááºážááᯠááá°áá²á·áá±á¬áºáááºáž áá
áºáá»áááºáááºážááœáẠá¡ááá¯áá« á¡áá¯ááºááá¬ážáá»á¬ážááá¯ááºááá¯áẠáááá
áá¯ááŸá
áºáááá¯ááºáá® á¡áá¯ááºááá²á·áááºá
áá¯á¶ážááŒááºáá»ááº
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;
áá±á¬áá±á¬áááºáá±á¬á· áá®áá±á¬ááºáá«áá²á
á¡áá¯ááºááœá±á á
áááºáááºá
á¬ážááá¯á·áá±á¬ááºážááŒá®áž á
áááºááŸá¯ááºááŸá¬ážá
áá¬áá±á¬ááºážáááºááá¯á· áá»áŸá±á¬áºááá·áºáá«áááºá
áá®á
á¬áááºážáá²ááᯠáááºááá¯ááºááá»áŸ ááá·áºáááºá
ááŸááºáá»ááºáá»á¬ážááŸáá·áº á¡ááŒá¶ááŒá¯áá»ááºáá»á¬ážááá¯áááºáž áááºážááŒá±á¬ááºáááá«áááºá
PS: SELECT ááœáẠá áááºáááºá á¬ážá áá¬áá±á¬ááºážáá±á¬ á¡áá¯ááºáá áºáᯠáá±á«áºáá¬áá«áá ááŸááºáá»ááºáá»á¬ážááœáẠáá±ážáá±ážáá«á áááºážááᯠá á¬áááºážááœáẠááá·áºááœááºážáá«áááºá
ááá»ááá»ááá¯áá»á±ážáá°ážáááºáá«áááºá
source: www.habr.com