create table marki
(markaId number(5) constraint marka_pk primary key,
nazwa varchar2(15) not null,
opis varchar2(15) not null,
rokPowstania date not null);

create table producenci
(producentId number(5) constraint producent_pk primary key,
nazwa varchar2(15) not null,
siedziba varchar2(15) not null,
roczneObroty number(16,2) not null);

create table kolory
(kolorId number(5) constraint kolor_pk primary key,
nazwa varchar2(15) not null,
rgb varchar2(15) not null);

create table samochody
(samochodId number(5) constraint samochod_pk primary key,

marka_Id number(5),
producent_Id number(5),
kolor_Id number(5),

constraint marka_fk_id foreign key (marka_Id) references marki (markaId),
constraint producent_fk_id foreign key (producent_Id) references producenci (producentId),
constraint kolor_fk_id foreign key (kolor_Id) references kolory (kolorId),
rokProdukcji date not null);

create sequence s_marki
increment by 1
start with 1
order;

create sequence s_producenci
increment by 1
start with 1
order;

create sequence s_kolory
increment by 1
start with 1
order;

create sequence s_samochody
increment by 1
start with 1
order;

insert into marki values (s_marki.nextval,'BMW','Bemki',to_date ('11/11/1974','dd/mm/yyyy'));
insert into marki values (s_marki.nextval,'Opel','Opelki',to_date ('02/11/1976','dd/mm/yyyy'));
insert into marki values (s_marki.nextval,'Mazda','Mazdy',to_date ('03/09/1977','dd/mm/yyyy'));
insert into marki values (s_marki.nextval,'VW','Wagen',to_date ('02/01/1987','dd/mm/yyyy'));
insert into marki values (s_marki.nextval,'Audi','Audice',to_date ('05/12/1954','dd/mm/yyyy'));

insert into producenci values (s_producenci.nextval,'Producent 1','Lorem Ipsum',22200);
insert into producenci values (s_producenci.nextval,'Producent 2','Warszawa',23200);
insert into producenci values (s_producenci.nextval,'Producent 3','Porto',42200);
insert into producenci values (s_producenci.nextval,'Producent 4','Nicea',62200);
insert into producenci values (s_producenci.nextval,'Producent 5','Manchester',72200);

insert into kolory values (s_kolory.nextval,'Czarny','0,0,0');
insert into kolory values (s_kolory.nextval,'Bialy','255,255,255');
insert into kolory values (s_kolory.nextval,'Czerwony','255,0,0');
insert into kolory values (s_kolory.nextval,'Niebieski','0,255,0');
insert into kolory values (s_kolory.nextval,'Zielony','0,0,255');

insert into samochody values (s_samochody.nextval,1, 1, 1,to_date ('02/01/2019','dd/mm/yyyy'));
insert into samochody values (s_samochody.nextval,2, 2, 2,to_date ('02/01/2019','dd/mm/yyyy'));
insert into samochody values (s_samochody.nextval,3, 3, 3,to_date ('02/01/2019','dd/mm/yyyy'));
insert into samochody values (s_samochody.nextval,4, 4, 4,to_date ('02/01/2019','dd/mm/yyyy'));
insert into samochody values (s_samochody.nextval,5, 5, 5,to_date ('02/01/2019','dd/mm/yyyy'));

SELECT * FROM samochody;
SELECT * FROM marki;
SELECT * FROM producenci;
SELECT * FROM kolory;

UPDATE samochody SET rokProdukcji = to_date ('30/12/2030','dd/mm/yyyy') WHERE samochodId = 3;
ALTER TABLE samochody ADD dataPrzegladu date;
UPDATE samochody SET dataPrzegladu = to_date ('10/11/2010','dd/mm/yyyy');

SELECT * FROM samochody;

drop sequence s_marki;
drop sequence s_producenci;
drop sequence s_kolory;
drop sequence s_samochody;

drop table marki cascade constraints;
drop table producenci cascade constraints;
drop table kolory cascade constraints;
drop table samochody cascade constraints;