-- 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';