-- 1. DML - Data manipulation language 
-- (select, insert, update, delete)
-- 2. DDL - Data definition language

select SALES_ORDER_ID, 
       TO_CHAR(ORDER_DATE, 'YYYY-MM-DD') ORDER_DATE,
       DECODE(TO_CHAR(ORDER_DATE, 'YYYY'), '2016', 'Текущий год', 'Предыдущие года)') YEAR
from SALES_ORDER
where ORDER_DATE >= TO_DATE('01-01-2016', 'DD-MM-YYYY')
   and MOD(SALES_ORDER_ID,2) = 0 and ORDER_DATE
order by ORDER_DATE DESC, SALES_ORDER_ID

select * from CITY
where REGION IS NULL;

select count(*) from SALES_ORDER
select count(*) from SALES_ORDER_LINE

select *
from SALES_ORDER so, SALES_ORDER_LINE sol, MANAGER m
where so.sales_order_id = sol.sales_order_id
-- and m.manager_id = so.manager_id

select m.manager_first_name, m.manager_last_name, 
       so.order_date, sol.product_qty, sol.product_price,
       (select CITY_NAME from CITY c inner join OFFICE o on (o.city_id = c.city_id) where o.office_id = m.office_id)
from SALES_ORDER so
  inner join SALES_ORDER_LINE sol on (sol.sales_order_id = so.sales_order_id)
  inner join MANAGER m on (m.manager_id = so.manager_id)
where m.office_id in (
   select office_id from OFFICE o where o.city_id in (
      select city_id from CITY c where c.country = 'United States'
   )
)

-- left outer join
-- right outer join
-- full outer join
select * from CITY

select COUNTRY, count(*),
     min(CITY_ID), max(CITY_ID),
     avg(CITY_ID),
     listagg(city_name, ',') within group (order by city_name)
from CITY
where CITY_NAME not like 'A%'
group by COUNTRY
having count(*) > 50;

-- Выбрать города, находящиеся в странах, где кол-во городов > 50
-- 1. Выбираем страны, где кол-во городов > 50
select COUNTRY from CITY group by COUNTRY having count(*) > 50
-- 2. Выбрать города, в этих странах
select * from CITY where COUNTRY in (
   select COUNTRY from CITY group by COUNTRY having count(*) > 50
);
-- Сконвертировать в JOIN
select * from CITY c1
inner join 
  (select COUNTRY from CITY group by COUNTRY having count(*) > 50) c2
on (c1.COUNTRY = c2.COUNTRY)

select c1.* from CITY c1 
    inner join CITY c2 on (c1.COUNTRY = c2.COUNTRY)
    group by c1.COUNTRY, c1.city_id, c1.Region, c1.city_name 
    having count(*) > 50;
    
-- where c1.COUNTRY = 'Honduras';

create table PERSON(ID INTEGER, NAME VARCHAR2(1000));

insert into PERSON(ID, NAME)
values (1, 'Gerald');

insert into PERSON(ID, NAME)
values (2, 'John');

insert into PERSON(ID, NAME)
values (3, 'Steve');

insert into PERSON(ID, NAME)
values (4, 'John');

insert into PERSON(ID, NAME)
values (5, 'John');


select * from PERSON;

select * from PERSON where NAME in (
   select NAME from PERSON group by NAME having count(*) > 1
)

select * from PERSON p1 inner join PERSON p2 
   on (p2.name = p1.name and p1.id > p2.id)
   
select * from PERSON p1, PERSON p2 where 
   p2.name = p1.name and p1.id > p2.id
   
select * from SALES_ORDER_LINE

create view V_SALES_ORDER as 
select so.*, sum (PRODUCT_QTY * PRODUCT_PRICE) ORDER_AMOUNT 
from SALES_ORDER so 
inner join SALES_ORDER_LINE sol on (so.sales_order_id = sol.sales_order_id)
group by so.sales_order_id, so.order_date, so.manager_id

select * from V_SALES_ORDER

select so.* from SALES_ORDER so 
inner join SALES_ORDER_LINE sol on (so.sales_order_id = sol.sales_order_id)
group by so.sales_order_id, so.order_date, so.manager_id
having sum (PRODUCT_QTY * PRODUCT_PRICE) >=
( select avg(ORDER_AMOUNT) from (
   select sum(PRODUCT_QTY * PRODUCT_PRICE) ORDER_AMOUNT
     from SALES_ORDER_LINE sol group by sol.sales_order_id
)
)

with T_SALES_ORDER as (
   select so.*, sum (PRODUCT_QTY * PRODUCT_PRICE) ORDER_AMOUNT 
     from SALES_ORDER so 
     inner join SALES_ORDER_LINE sol on (so.sales_order_id = sol.sales_order_id)
     group by so.sales_order_id, so.order_date, so.manager_id
)
select * from T_SALES_ORDER
where ORDER_AMOUNT >= (select avg(ORDER_AMOUNT) from T_SALES_ORDER);