-- SQL (Structured Query Language)
-- 1. DDL (Data Definition Language)
CREATE TABLE PERSON(ID INTEGER, NAME VARCHAR2(4000));
ALTER TABLE PERSON ADD constraint PK_PERSON PRIMARY KEY(ID);
alter table PERSON drop constraint SYS_C0047302;
DROP TABLE PERSON;
CREATE UNIQUE INDEX I_PERSON ON PERSON(NAME);
ALTER TABLE PERSON ADD FOREIGN KEY FK_PERSON_ADDRESS ON PERSON(ADDRESS_ID) REFERENCES ADDRESS(ADDRESS_ID);
alter table PERSON ADD ADDRESS_ID INTEGER;
alter table PERSON MODIFY NAME VARCHAR2(4000) NOT NULL;
alter table PERSON ADD BIRTH_DATE DATE;
insert into PERSON(ID, NAME) values (1, 'Петров');
select * from PERSON;
-- 1. DML (Data Manipulation Language) CRUD (Create/Read/Update/Delete)
-- 1.1. INSERT
INSERT INTO PERSON(ID, NAME) values (2, 'James Brown');
INSERT INTO PERSON(ID, NAME, BIRTH_DATE) values (4, 'Steve Jobs', TO_DATE('23.09.2017', 'DD.MM.YYYY'));
commit;
rollback;
-- 1.3. UPDATE
UPDATE PERSON SET
NAME = 'John Lennon',
BIRTH_DATE = TO_DATE('23.09.2017', 'DD.MM.YYYY')
where ID = 2;
-- 1.4. DELETE
DELETE FROM PERSON
where ID = 3;
truncate table PERSON;
-- 1.2. SELECT
select * from PERSON;
select distinct ID, 'Имя: ' || NAME, UPPER(NAME) as UPPER_NAME, 'Строка' as CONSTANT_LITERAL
from PERSON
where ID <> 2 or UPPER(NAME) like '%S%'
order by ID desc;
select C1, C2, count(*), avg(C3), max(C3), min(C3), sum(C3)
from T
where ...
group by C1, C2
having ..
select * from all_tables where table_name='COMPANY'
select count(distinct *),
avg(MARKET_CAPITAL),
min(MARKET_CAPITAL),
max(MARKET_CAPITAL),
sum(MARKET_CAPITAL),
SECTOR, SUBSECTOR
from COMPANY
where COUNTRY_OF_INC = 'Russia'
group by SECTOR, SUBSECTOR
having SUM(MARKET_CAPITAL) > 100000
order by SUM(MARKET_CAPITAL) desc;
select SECTOR,
LISTAGG(SUBSECTOR, ', ') within group (order by SUBSECTOR)
from (
select distinct SECTOR, SUBSECTOR from COMPANY
)
group by SECTOR;
SET DEFINE OFF
select sum(MARKET_CAPITAL) from company where COUNTRY_OF_INC = 'Russia'
and SUBSECTOR = 'Integrated Oil & Gas' and SECTOR='Oil & Gas Producers';