CV 05, 100%

DESC A_SKOLA.UCITELE;
DESC A_SKOLA.STUDENTI;
DESC A_SKOLA.UCI;
DESC A_SKOLA.KNIHY;

CREATE TABLE STUDENTI(
ID_STUDENTA CHAR(7) PRIMARY KEY,
JMENO VARCHAR2(40),
PRIJMENI VARCHAR2(40),
NASTUP DATE
);

CREATE TABLE UCITELE(
ID_UCITELE NUMBER(5) PRIMARY KEY,
JMENO varchar2(40),
PRIJMENI VARCHAR2(40)
);

CREATE TABLE UCI(
ID_STUDENTA CHAR(7)NOT NULL,
ID_UCITELE NUMBER(5)NOT NULL,
PREDMET VARCHAR2(30)NOT NULL,
ZNAMKA NUMBER(2,1)
);

ALTER TABLE UCI
ADD CONSTRAINT uci_pk PRIMARY KEY (ID_STUDENTA, ID_UCITELE, PREDMET);

CREATE TABLE KNIHY(
ID_KNIHY NUMBER(7) PRIMARY KEY,
ID_STUDENTA CHAR(7),
NAZEV VARCHAR2(150)
);


DESC uci;
DESC knihy;
DESC studenti;
DESC ucitele;

INSERT INTO  uci (ID_STUDENTA,ID_UCITELE,PREDMET,ZNAMKA)SELECT ID_STUDENTA, ID_UCITELE, PREDMET, ZNAMKA FROM A_SKOLA.UCI;
INSERT INTO UCITELE(ID_UCITELE,JMENO,PRIJMENI) SELECT ID_UCITELE,JMENO,PRIJMENI FROM A_SKOLA.UCITELE;
INSERT INTO KNIHY(ID_KNIHY,ID_STUDENTA,NAZEV) SELECT ID_KNIHY,ID_STUDENTA,NAZEV FROM A_SKOLA.KNIHY;
INSERT INTO STUDENTI(ID_STUDENTA,JMENO,NASTUP,PRIJMENI) SELECT ID_STUDENTA,JMENO,NASTUP,PRIJMENI FROM A_SKOLA.STUDENTI;
SELECT * FROM uci;
SELECT jmeno, prijmeni FROM Ucitele;
SELECT jmeno FROM UCITELE WHERE prijmeni LIKE 'M%';
CREATE OR REPLACE VIEW v_ucitele_p_na_m AS SELECT jmeno FROM UCITELE WHERE prijmeni LIKE 'M%';
SELECT * FROM v_ucitele_p_na_m;
UPDATE STUDENTI SET jmeno='Honza' WHERE jmeno='Jan';
SELECT prijmeni, nastup FROM studenti ORDER BY 2 DESC;
CREATE OR REPLACE VIEW v_pr_nas_studentu_desc AS SELECT prijmeni, nastup FROM studenti ORDER BY 2 DESC;
SELECT * FROM v_pr_nas_studentu_desc;
SELECT ID_STUDENTA||'-'||PRIJMENI||', '||JMENO AS Student FROM studenti ORDER BY jmeno ASC,nastup DESC;
CREATE OR REPLACE VIEW v_studenti AS SELECT ID_STUDENTA||'-'||PRIJMENI||', '||JMENO AS Student FROM studenti ORDER BY jmeno ASC,nastup DESC;
SELECT * FROM V_STUDENTI;
SELECT ID_KNIHY AS ID, nazev AS Kniha FROM knihy ORDER BY nazev ASC, ID_knihy ASC;
CREATE OR REPLACE VIEW v_knihy_razeni AS SELECT ID_KNIHY AS ID, nazev AS Kniha FROM knihy ORDER BY nazev ASC, ID_knihy ASC;
SELECT * FROM v_knihy_razeni;
SELECT * FROM studenti WHERE JMENO NOT LIKE 'Petr';
DELETE FROM UCI;
SELECT * FROM UCI;
DELETE FROM STUDENTI WHERE jmeno='Josef';
SELECT jmeno FROM studenti WHERE jmeno='Josef';

SELECT * FROM IDAS12015_HODNOCENI;