-- Data manupulations language select
1. select

select 
   OFFICE_ID, CITY_ID, OFFICE_NAME 
from OFFICE
where UPPER(OFFICE_NAME) like '%GROUP%'
order by OFFICE_NAME desc

select UPPER(MANAGER_FIRST_NAME), LOWER(MANAGER_LAST_NAME) from MANAGER
order by UPPER(MANAGER_FIRST_NAME), LOWER(MANAGER_LAST_NAME)

select AVG(PRODUCT_PRICE) from SALES_ORDER_LINE


select CITY_ID, count(*), listagg(OFFICE_NAME, ';') within group (order by OFFICE_NAME)
from OFFICE
group by CITY_ID

select  ROWNUM, S.* from SALES_ORDER_LINE S
where ROWNUM <= 10

select * from (
  select PRODUCT_ID, 
    count(PRODUCT_ID) COUNT, 
    max(PRODUCT_PRICE) MAX_PRICE, 
    min(PRODUCT_PRICE) MIN_PRICE, 
    AVG(PRODUCT_PRICE) AVG_PRICE
    from SALES_ORDER_LINE
  where PRODUCT_QTY >= 10
  group by PRODUCT_ID
  having count(PRODUCT_ID) > 100
  order by count(PRODUCT_ID) desc
) where ROWNUM <= 10

select PRODUCT_ID, 
  SALES_ORDER_ID,
  count(PRODUCT_ID) COUNT, 
  max(PRODUCT_PRICE) MAX_PRICE, 
  min(PRODUCT_PRICE) MIN_PRICE, 
  AVG(PRODUCT_PRICE) AVG_PRICE
  from SALES_ORDER_LINE
where PRODUCT_QTY >= 10
group by PRODUCT_ID, SALES_ORDER_ID

select * from PRODUCT, SALES_ORDER_LINE 
where PRODUCT.PRODUCT_ID = SALES_ORDER_LINE.PRODUCT_ID

select * from PRODUCT 
       left outer join SALES_ORDER_LINE on
             (PRODUCT.PRODUCT_ID = SALES_ORDER_LINE.PRODUCT_ID)
       inner join SALES_ORDER on
             (SALES_ORDER.SALES_ORDER_ID = SALES_ORDER_LINE.SALES_ORDER_ID)
             
select * from SALES_ORDER O
       right outer join MANAGER M
            on (O.MANAGER_ID = m.manager_id)
where O.MANAGER_ID > 900 or O.MANAGER_ID IS NULL


select PRODUCT_ID, PRODUCT_NAME from PRODUCT where LOWER(PRODUCT_NAME) like '%aspirin%'
union all
select MANAGER_ID, MANAGER_FIRST_NAME || ' ' || MANAGER_LAST_NAME from MANAGER

select * from OFFICE where OFFICE_ID in (
select M.OFFICE_ID
       from sales_order_line OL
       inner join sales_order O on (OL.SALES_ORDER_ID = O.SALES_ORDER_ID)
       inner join MANAGER M on (M.MANAGER_ID = O.MANAGER_ID)
       group by OFFICE_ID
       having SUM(PRODUCT_QTY * PRODUCT_PRICE) > 3000000 
)

select * from SALES_ORDER

select * from SALES_ORDER 
where ORDER_DATE = TO_DATE('08.08.2014', 'DD.MM.YYYY')

2. delete

3. insert

4. update
update SALES_ORDER set MANAGER_ID = NULL where MANAGER_ID = 999
commit;
-- Data definition language
Create table
alter table 
alter table SALES_ORDER MODIFY MANAGER_ID NULL
create index