File Modified
Microsoft Powerpoint Presentation SQL Practice.pptx Sep 19, 2020 by Eugene Gavrilov
Microsoft Powerpoint Presentation SQL - 2.pptx Sep 19, 2020 by Eugene Gavrilov


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


  • No labels