SQL

SELECT * FROM table_name;

SELECT colum(s)_name FROM table_name;

select DISTINCT colum(s)_name from table_name;

select * from table_name WHERE condition(s);

select * from table_name where age > >= < <= = <> != 5;

select * from table_name where age BETWEEN 5 AND 50;

select * from table_name where age IN (5, 10);

select * from table_name where age IS NULL / IS NOT NULL;

select * from table_name where name LIKE 'A%';

select * from table_name where name LIKE 'ар_а';

select * from table_name where name LIKE 'Dr\_%' escape '\'; (Dr_Anna)

select * from table_name where age<18 AND name LIKE 'A%';

select * from table_name where age<18 OR age>65;

select * from table_name where name NOT LIKE 'A%';

select * from table_name where NOT name = 'Anna';

select * from table_name ORDER BY name;

select * from table_name order by name DESC;

select * from table_name order by name NULLS FIRST / NULLS LAST;

LENGTH(value) --//get length from the word

LOWER(string) --//to small symbol
UPPER(string) --//to big symbol
INITCAP(string) --//to big first symbol in the each word

CONCAT(string, string) --//to concatanation two words
SELECT colum_name1 ||' '|| colum_name2 FROM table_name; --//to concatanation

LPAD(s,n,p) --//add to left symbol p, where n – length new word
RPAD(s,n,p) --//add to right symbol p, where n – length new word
select LPAD('123',4, '0') from dual; --//123 = 0123

TRIM()
select trim(' Valera ') from dual; --//delete all space
select trim(trailing 'a' from 'Valeraaaaa') from dual; --//delete all end 'a'
select trim(leading 'a' from 'aaValeraaaa') from dual; --//delete all begin 'a'
select trim(both 'a' from 'aaValeraaaa') from dual; --//delete all begin/end 'a'

INSTR()
select instr('Valera', 'a') from dual; --//get first position for 'a'
select instr('Valera','a',4) from dual; --//4 – start position for search
select instr('Valera','a',1,2) from dual; --//2 – second position for 'a'

SUBSTR(string, start position, number of characters)
select substr('Valera',2) from dual; --//alera
select substr('Valera',2,2) from dual; --//al

REPLACE(string, search item, replacement item)
select replace('Valera, hello', 'hello') from dual; --//Valera,
select replace('Valera, hello', 'hello', 'bye') from dual; --//Valera, bye
select replace('Valera, hello', 'l', '*') from dual; --//Va*era, he**o

ROUND(n, presition)
select round(3.14) from dual; --//3
select round(3.16, 1) from dual; --//3.2
select round(3568, -1) from dual; --//3570

TRUNC(n, presition)
select trunc(3.14) from dual; --//3
select trunc(3.16, 1) from dual; --//3.1
select trunc(3568, -1) from dual; --//3560

MOD(dividend, divisor)
select mod(5, 2) from dual; --//1 остаток от деления

SYSDATE
select sysdate from dual; --//get today date

MONTHS_BETWEEN(end_date, start_date)
ADD_MONTHS(date, number_of_months)
NEXT_DAY(date, day_of_the_week)
LAST_DAY(date)

ROUND(date, date precision format)
TRUNC(date, date precision format)
date precision format = centorial-CC, year-YYYY, quarter-Q, month-MM, week-W, day-DD, hour-HH, minut-MI

TO_CHAR(number, format_mask, nls_parameters)
select '$'||to_char(15) from dual; --//$15
format_mask = '99999' - 18
format_mask = '099999' - 000018
format_mask = '099999.999' - 000018.350
format_mask = '099999D999' - 000018.350
format_mask = '099,999,999' - 001,234,567
format_mask = '099999G999' – 001,234,567
format_mask = '$099999' - $000018 (dollar change)
format_mask = 'L099999' - $000018 (local change)
format_mask = '099999MI' - 000018- (end minus for negative number)
format_mask = '099999PR' - <000018> (<> for negative number)
format_mask = 'S099999' - +000018 (show sign of number)

---

TO_CHAR(date, format_mask, nls_parameters)
select to_char('20.10.2019') from dual;
select to_char(sysdate,'Month','NLS_DATE_LANGUAGE = AMERICAN') from dual;
format_mask = 'Y' – 9
format_mask = 'YY' - 19
format_mask = 'YYY' - 019
format_mask = 'YYYY' - 2019
format_mask = 'RR' - 19
format_mask = 'YEAR' - twenty nineteen
format_mask = 'MM' - 10
format_mask = 'MON' - OCT
format_mask = 'MONTH' - OCTOBER
format_mask = 'D' – 6 day number
format_mask = 'DD' – 20 day of month
format_mask = 'DDD' – 263 day of year
format_mask = 'DY' – FRI day of week
format_mask = 'DAY' – FRIDAY full day of week
format_mask = 'W' – 3 week of month
format_mask = 'WW' – 38 week of year
format_mask = 'Q' – 3 quarter of year
format_mask = 'CC' – 21 centure
format_mask = 'PM' – AM/PM show for time
format_mask = 'HH24' – show time in format 24 hour

---

TO_DATE(text, format_mask, nls_parameters)
select to_date('08-10-19', 'dd-mm-YYYY') from dual;
select to_char(to_date('08-10-19', 'yy-mm-dd'),'dd-MON-yyyy hh24:mi:ss') from dual;

---

TO_NUMBER(text, format_mask, nls_parameters)
select to_number('4555,77') from dual;
select to_number('4555.77', '9999.99') from dual;
select to_number('$4555.77', '$9999.99') from dual;
select to_number('4,555.77', '9,999.99') from dual;
select to_number('<4,555.77>', '9,999.99PR') from dual;

---

NVL(value, ifnull)
select nvl(18, 19) from dual;
select nvl(null, 19) from dual;

NVL2(value, ifnotnull, ifnull)
select nvl2(18, 19, 20) from dual;
select nvl2(null, 19, 20) from dual;

NULLIF(value1, value2)
select nullif(18, 19) from dual;
select nullif(19, 19) from dual;

COALESCE(value1, value2, …, valueN)
select coalesce(1, null, 2) from dual;
select coalesce(null, null, 2) from dual;

COUNT(1) – only number
select count(1) from table_name;
select count(field_name) from table_name; --//null not count
select count(field_name) from table_name where field_name < 5;
select count(distinct field_name) from table_name; --//uniq value count

SUM(field_name) – only number
select sum(field_name) from table_name; --//null not sum
select sum(distinct field_name) from table_name; --//uniq value sum

AVG(field_name) – only number
select avg(field_name) from table_name; --//null not avg
select avg(distinct field_name) from table_name; --//uniq value avg

MAX(field_name) – number and string
select max(field_name) from table_name;

MIN(field_name) – number and string
select min(field_name) from table_name;

GROUP BY
select department_id, count(1) from employees group by department_id;
select department_id, count(1) from employees group by department_id HAVING count(1) > 5;

Need to get a single subquery result if using = < <= > >= <>
Need to get a multi subquery result if using IN, NOT IN, >ANY, >ALL

---

select first_name, salary from employees
where salary > (select avg(salary) from employees);

select (
select min(salary) from jobs) min_zp,
(select max(length(first_name)) from employees) max_length_name
from dual;

select t1.fname from employee t1
where t1.salary >
(select avg(t2.salary) from employee t2
where t2.department_id = t1.department_id);

Горизонтальное объединение

• INNER JOIN = X,Y,K + Z,X,M = X
• LEFT JOIN = X,Y,K + Z,X,M = X,Y.K
• RIGHT JOIN = X,Y,K + Z,X,M = Z,X,M
• FULL JOIN = X,Y,K + Z,X,M = X,Y,K,Z,M
• CROSS JOIN = X,Y,K + Z,X,M = XZ, XX, XM, YZ, YX, YM, KZ, KX, KM

Вертикальное объединение

• UNION
• INTERSECT
• MINUS

INSERT
insert into table_name (columns list) values(value);
insert into countries (country_id, country_name, region_id) values('SW', 'Sweden', 1);

---

UPDATE
update table_name set column(s)=value(s) where condition(s);
update employees set salary=1000 where employee_id=15;

---

DELETE
delete from table_name where condition(s);
delete from new_emps where age=24;

---

MERGE
merge into new_emps ne
using employees e
on (ne.emp_id=e.employee_id)
when matched then
update set ne.start_date=sysdate
delete where ne.job like '%IT%'
when not matched then
insert (emp_id, name, start_date, job)
values (employee_id, last_name, hire_date, job_id);

transaction = DML commands list

COMMIT – show update for all sessions (users)
transaction;
commit;

---

ROLLBACK

---

SAVEPOINT
savepoint savepoint_name;

---

AUTOCOMMIT – do not use
set autocommit on;
set autocommit off;

---

LOCK
select * from employee for update;

CREATE
create table students(
student_id integer,
name varchar2(15)
);

---

ALTER
alter table table_name add (column_name data_type default expr);
alter table table_name modify (column_name data_type default expr);
alter table table_name read only;
alter table table_name drop column column_name;
alter table table_name set unused column column_name;
alter table table_name drop unused columns;

---

RENAME
alter table table_name rename column column_name1 to column_name2;

---

TRUNCATE
truncate table table_name;

---

DROP
drop table shema.table_name;

constraint – business rools: unique, not null, primary key, foreign key, check

UNIQUE CONSTRAINT
столбец(цы) содержит только уникальные значения (отсутствие дубликатов в столбце). Исключение – null.

NOT NULL CONSTRAINT
не разрешается столбцам содержать значение null.

PRIMARY KEY CONSTRAINT
столбец(цы) содержит только уникальные значения (отсутствие дубликатов в столбце) и не разрешает содержать значение null.

FOREIGN KEY CONSTRAINT
использовать только значения из определенного столбца таблицы-родителя или значение null. Связывает две таблицы между собой.

CHECK CONSTRAINT
использовать только те значения, которые удовлетворяют указанному условию(ям).

---

SEQUENCE
генерировать уникальные значения по заранее определенному алгоритму. Используется для primary key.

---

INDEX – set automation for primary key, unique

B-TREE INDEX – default, используется:

• когда много строк;
• когда количество строк в оутпуте составляет 2%-4% от общего кол-ва строк;
• когда много уникальных значений;
• когда используется WHERE, JOIN по столбцу.

BITMAP INDEX используется:

• когда много строк;
• когда мало уникальных значений;
• когда используется AND, OR, NOT по столбцу.

VIEW – select to table

• безопасность / ограничение (доступ к 3 полям из 5);
• упрощение написания запроса (не нужен JOIN);
• предотвращение ошибок;
• понятные названия полей;
• перфоменс.

SIMPLE VIEW

• one table
• no functions
• no aggregation

COMPLEX VIEW

• join tables
• functions
• aggregation

SYNONYM
предоставляет таблице другое имя.

---