CREATE TABLE Student (
nr_studenta INT,
nazwisko VARCHAR(15) CONSTRAINT nazwisko_NN NOT NULL,
imie VARCHAR(15) CONSTRAINT imie_NN NOT NULL,
data_ur DATE CONSTRAINT data_NN NOT NULL,
plec CHAR(1),
CONSTRAINT studenci_pk PRIMARY KEY(nr_studenta),
CONSTRAINT plec_CH CHECK((plec='K') OR (plec='M'))
);

CREATE TABLE oceny_studentow (
id_oceny NUMBER,
nr_studenta NUMBER,
data_oceny DATE CONSTRAINT oceny_dataoceny_NN NOT NULL,
ocena NUMBER(2,1) CONSTRAINT oceny_ocena_NN NOT NULL,
slownie VARCHAR2(6) CONSTRAINT oceny_slownie_NN NOT NULL,
CONSTRAINT oceny_ocena_CH CHECK((ocena>=2) AND (ocena<=5)),
CONSTRAINT oceny_idoceny_PK PRIMARY KEY(id_oceny),
CONSTRAINT oceny_nrstudenta_FK FOREIGN KEY(nr_studenta) REFERENCES Student(nr_studenta)
);

insert into student
values (1, 'Tkaczyk', 'Jerzy', TO_DATE('15/10/1964','DD/MM/YYYY'), 'M');
insert into student
values (2, 'Krawczyk', 'Justyna', TO_DATE('08/05/1973','DD/MM/YYYY'), 'K');
insert into student
values (3, 'Maczyk', 'Maria', TO_DATE('03/12/1975','DD/MM/YYYY'), 'K');

CREATE SEQUENCE student_nrstudenta_seq
INCREMENT BY 1
START WITH 4
MINVALUE 1
MAXVALUE 999;

CREATE SEQUENCE oceny_nroceny_seq
INCREMENT BY 1
START WITH 1
MINVALUE 1;

SELECT *
FROM user_sequences;

insert into student
values (student_nrstudenta_seq.nextval,'Wesołowski', 'Robert', TO_DATE('07/06/1990','DD/MM/YYYY'), 'M');

insert into oceny_studentow
values (oceny_nroceny_seq.nextval, 4, to_date('05/12/2011','dd/mm/yyyy'), 4, 'dobry');

CREATE INDEX Student
ON Student(nazwisko ASC, imie ASC);

select * from user_indexes;

CREATE TABLE Studentki (
nr_studentki INT,
nazwisko VARCHAR(15) CONSTRAINT nazwiskoK_NN NOT NULL,
imie VARCHAR(15) CONSTRAINT imieK_NN NOT NULL,
data_ur DATE CONSTRAINT dataK_NN NOT NULL,
plec CHAR(1),
CONSTRAINT studentki_pk PRIMARY KEY(nr_studentki),
CONSTRAINT pleck_CH CHECK((plec='K') OR (plec='M'))
);

INSERT INTO STUDENTKI
SELECT *
FROM STUDENT
WHERE (PLEC='K');

CREATE TABLE Studentki2 AS
SELECT *
FROM STUDENT
WHERE (PLEC='K');

ALTER TABLE Student DISABLE CONSTRAINT PLEC_CH;

insert into student
values (student_nrstudenta_seq.nextval,'Balcerek', 'Janusz', TO_DATE('02/09/1962','DD/MM/YYYY'), 'A');

ALTER TABLE Student ENABLE CONSTRAINT PLEC_CH; /*nie da sie juz wlaczyc bo nie spełnia warunków tabela*/

UPDATE STUDENT
SET PLEC='M'
WHERE NR_STUDENTA=5;

ALTER TABLE Student ENABLE CONSTRAINT PLEC_CH;


=================
Tabela kadry

select imie1, imie2, nazwisko
from osoby
order by imie1 asc, imie2 asc, nazwisko asc;

select nazwisko, imie1, imie2
from osoby
order by 1 asc, 2 asc, 3 asc; /*po numerach kolumn, tzw notacja pozycyjna*/

select nazwisko, imie1, imie2, d_ur
from osoby
order by 4 asc; /*ale jezeli d_ur nie wystapi w select to nie posortujemy ta metoda*/

select nazwisko, imie1, imie2
from osoby
order by d_ur asc;