CREATE TABLE Student (
nr_studenta INT,
nazwisko VARCHAR(15) NOT NULL,
imie VARCHAR(15) NOT NULL,
data_ur DATE NOT NULL,
plec CHAR(1) CHECK((plec='K') OR (plec='M')),
PRIMARY KEY(nr_studenta)
);
Check może być też taki CHECK(plec IN('K','M'))
--------------------
DESC Student;
wypisuje tabelkę Student
------------
SELECT * FROM user_tables;
SELECT * FROM user_objects WHERE object_type='TABLE';
Pokazuje jakie tabelki utworzyliśmy
---------
SELECT * FROM user_constraints;
Ograniczenia, jakie nadaliśmy. Jeśli chcemy zobaczyć ograniczenia tabeli Student należy wykonać
SELECT * FROM user_constraints WHERE table_name='STUDENT';
---------------------------
CREATE TABLE Student2 (
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'))
);
Tak tworzymy także nazwy warunków
---------------------------
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)
);
Tabela powiązana z Tabelą student. Wszystkie warunki nazywane.
-----------------------
RENAME oceny_studentow TO oceny;
Zmiana nazwy tabeli
------------------
ALTER TABLE Student MODIFY (nazwisko VARCHAR2(30));
zmiana parametrów
-----------------
ALTER TABLE Student ADD (telefon VARCHAR2(9));
dodawanie pola telefon
-----------------
ALTER TABLE student DROP COLUMN telefon;
usuwanie kolumny telefon
---------------------
CREATE TABLE czytelnicy (
id_czytelnika CHAR(5),
nazwisko VARCHAR(50) CONSTRAINT nazwisko_NN NOT NULL,
imie VARCHAR(30) CONSTRAINT imie_NN NOT NULL,
pesel CHAR(11) CONSTRAINT pesel_NN NOT NULL,
data_ur DATE CONSTRAINT data_NN NOT NULL,
plec CHAR(1),
telefon VARCHAR(10),
CONSTRAINT czytelnicy_id_PK PRIMARY KEY(id_czytelnika),
CONSTRAINT czytelnicy_id_CH CHECK(id_czytelnika REGEXP_LIKE '[A-Z][A-Z][0-9][0-9][0-9]'), /*źle*/
CONSTRAINT plec_CH CHECK(plec IN('K','M'))
);
CREATE TABLE wydawnictwa (
id_wydawnictwa NUMBER,
nazwa VARCHAR(50) CONSTRAINT wydawnictwa_nazwa_NN NOT NULL,
miasto VARCHAR(50) CONSTRAINT wydawnictwa_miasto_NN NOT NULL,
telefon VARCHAR(10),
CONSTRAINT wydawnictwa_id_PK PRIMARY KEY(id_wydawnictwa)
);
CREATE TABLE pracownicy (
id_pracownika NUMBER,
nazwisko VARCHAR(50) CONSTRAINT pracownicy_nazwisko_NN NOT NULL,
imie VARCHAR(30) CONSTRAINT pracownicy_imie_NN NOT NULL,
data_ur DATE CONSTRAINT pracownicy_dataur_NN NOT NULL,
data_zatr DATE,
szef_id NUMBER,
plec CHAR(1) CONSTRAINT pracownicy_plec_NN NOT NULL,
CONSTRAINT pracownicy_id_PK PRIMARY KEY(id_pracownika),
CONSTRAINT pracownicy_idszefa_FK FOREIGN KEY(szef_id) REFERENCES pracownicy(id_pracownika),
CONSTRAINT pracownicy_plec_CH CHECK(plec IN('K','M')),
CONSTRAINT pracownicy_datazatr_CH CHECK(data_ur<data_zatr)
);
CREATE TABLE ksiazki (
sygn NUMBER,
id_wyd NUMBER,
tytul VARCHAR(50) CONSTRAINT ksiazki_tytol_NN NOT NULL,
cena NUMBER(5,2) CONSTRAINT ksiazki_cena_NN NOT NULL,
strony NUMBER,
gatunek VARCHAR(25),
CONSTRAINT ksiazki_sygn_PK PRIMARY KEY(sygn),
CONSTRAINT ksiazki_idwyd_FK FOREIGN KEY(id_wyd) REFERENCES WYDAWNICTWA(id_wydawnictwa),
CONSTRAINT ksiazki_cena_CH CHECK(cena>0),
CONSTRAINT ksiazki_gatunel_CH CHECK(gatunek IN('powieść','powieść historyczna','dla dzieci','wiersze','kryminal','powieść science fiction','książka naukowa'))
);
CREATE TABLE wypozyczenia (
id_w NUMBER,
sygn NUMBER,
id_cz CHAR(5),
id_p NUMBER,
data_w DATE CONSTRAINT wypozyczenia_dataw_NN NOT NULL,
data_z DATE,
kara NUMBER(5,2) DEFAULT 0,
CONSTRAINT wypozyczenia_id_PK PRIMARY KEY(id_w),
CONSTRAINT wypozyczenia_sygn_FK FOREIGN KEY(sygn) REFERENCES KSIAZKI(sygn),
CONSTRAINT wypozyczenia_idczyt_FK FOREIGN KEY(id_cz) REFERENCES CZYTELNICY(id_czytelnika),
CONSTRAINT wypozyczenia_idprac_FK FOREIGN KEY(id_p) REFERENCES PRACOWNICY(id_pracownika),
CONSTRAINT wypozyczenia_dataw_CH CHECK(data_w<data_z),
CONSTRAINT wypozyczenia_kara_CH CHECK(kara>0)
);
-----------------------------
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;
---------------------------------
Wydruk osób w jednek komórce nazwisko i imie
SELECT NAZWISKO||' '||IMIE1 As OSOBA
FROM OSOBY
ORDER BY OSOBA;
===
SELECT upper(nazwa), substr(nazwa,0,1) AS LITERA
FROM WYDZIALY
ORDER BY NAZWA;
==
SELECT id_os AS PRACOWNIK, PENSJA, TRUNC(PENSJA/1000) AS PELNE_TYS_ZL
FROM ZATRUDNIENIA
ORDER BY PENSJA DESC;
===
SELECT IMIE1||' '||NAZWISKO AS OSOBA, LENGTH(NAZWISKO) AS DLUGOSC
FROM OSOBY
ORDER BY DLUGOSC;
===
SELECT decode(plec, 'K', 'Pani', 'M', 'Pan')||' '||NAZWISKO||' '||IMIE1||' - data urodzenia '||d_ur AS KOMUNIKAT
FROM OSOBY
ORDER BY NAZWISKO, IMIE;
===
SELECT IMIE1||' '||NAZWISKO AS OSOBA
FROM OSOBY
WHERE NAZWISKO LIKE '%ek'
ORDER BY NAZWISKO ASC;
SELECT IMIE1||' '||NAZWISKO AS OSOBA
FROM OSOBY
WHERE SUBSTR(NAZWISKO,-2,2)='ek';
===
SELECT ID_OS AS PRACOWNIK, trunc(months_between(SYSDATE,OD)/12) AS LATA_PRACY
FROM ZATRUDNIENIA
WHERE DO IS NULL
ORDER BY 2 DESC;
===
wyswietlic id tych, ktorzy sa zatrudnieni lub byli zatrudnienia na jakimkolwiek wydziale
SELECT DISTINCT ID_OS
FROM ZATRUDNIENIA
ORDER BY 1;
===
SELECT *
FROM OSOBY
WHERE (NAZWISKO LIKE 'K%' OR NAZWISKO LIKE 'L%') AND (TO_Char(D_UR,'MM') IN ('06','11','12')) OR (PLEC='K' and imie2 is not null);
----------------------------
SELECT WYDZIALY.NAZWA, OSOBY.IMIE1||' '||OSOBY.NAZWISKO AS OSOBA
FROM WYDZIALY, OSOBY, ZATRUDNIENIA
WHERE (ZATRUDNIENIA.ID_W = WYDZIALY.ID_W AND ZATRUDNIENIA.ID_OS = OSOBY.ID_OS AND ZATRUDNIENIA.DO IS NULL)
ORDER BY 1 ASC, 2 ASC;
=============================
Z aliasami
SELECT W.NAZWA, O.IMIE1||' '||O.NAZWISKO AS OSOBA
FROM WYDZIALY W, OSOBY O, ZATRUDNIENIA Z
WHERE (Z.ID_W = W.ID_W AND Z.ID_OS = O.ID_OS AND Z.DO IS NULL)
ORDER BY 1 ASC, 2 ASC;
===============================
CROSS JOIN:
SELECT WYDZIALY.NAZWA, OSOBY.IMIE1||' '||OSOBY.NAZWISKO AS OSOBA
FROM WYDZIALY CROSS JOIN OSOBY CROSS JOIN ZATRUDNIENIA
WHERE (ZATRUDNIENIA.ID_W = WYDZIALY.ID_W AND ZATRUDNIENIA.ID_OS = OSOBY.ID_OS AND ZATRUDNIENIA.DO IS NULL)
ORDER BY 1 ASC, 2 ASC;
=================================
NATURAL JOIN
SELECT WYDZIALY.NAZWA, OSOBY.IMIE1||' '||OSOBY.NAZWISKO AS OSOBA
FROM WYDZIALY NATURAL JOIN OSOBY NATURAL JOIN ZATRUDNIENIA
WHERE ZATRUDNIENIA.DO IS NULL
ORDER BY 1 ASC, 2 ASC;
=======================================
ZWYKLY JOIN
SELECT WYDZIALY.NAZWA, OSOBY.IMIE1||' '||OSOBY.NAZWISKO AS OSOBA
FROM (OSOBY JOIN ZATRUDNIENIA ON OSOBY.ID_OS=ZATRUDNIENIA.ID_OS) JOIN WYDZIALY ON WYDZIALY.ID_W=ZATRUDNIENIA.ID_W
WHERE ZATRUDNIENIA.DO IS NULL
ORDER BY 1 ASC, 2 ASC;
===================================
KWERENDY AGREGUJĄCE:
SELECT COUNT(OSOBY.ID_OS) AS ILE_OSOB
FROM OSOBY;
==================================
WYSWIETLENIE NAJMLODSZEJ KOBIETY
SELECT MAX(OSOBY.D_UR) AS DATA_UR
FROM OSOBY
WHERE OSOBY.PLEC='K';
===================================
SELECT PLEC, COUNT(OSOBY.ID_OS) AS ILE
FROM OSOBY
GROUP BY PLEC
ORDER BY 2 DESC;
==================================
SELECT SUBSTR(INITCAP(IMIE1),1,1) AS LITERA1, COUNT(OSOBY.ID_OS) AS ILE
FROM OSOBY
GROUP BY SUBSTR(INITCAP(IMIE1),1,1)
ORDER BY 1 ASC;
==================================
INSERT INTO OSOBY (ID_OS, NAZWISKO, IMIE1, D_UR, PLEC) VALUES (100,'Milczarek','Michal',TO_DATE('12/01/1970','DD/MM/YYYY'),'M');
SELECT IMIE1||' '||NAZWISKO AS OSOBA, NVL(ZATRUDNIENIA.PENSJA,0)
FROM OSOBY LEFT JOIN ZATRUDNIENIA ON OSOBY.ID_OS=ZATRUDNIENIA.ID_OS
WHERE ZATRUDNIENIA.DO IS NULL
ORDER BY 1 ASC;
===================================
SELECT WYDZIALY.NAZWA, COUNT(ZATRUDNIENIA.ID_Z) AS ZATRUDNIONYCH
FROM WYDZIALY NATURAL JOIN ZATRUDNIENIA
GROUP BY WYDZIALY.NAZWA
ORDER BY 2 DESC;
===================================
Wyświetlić te wydziały, które zatrudniają aktualnie ponad 2 osoby i wyświetlić
SELECT WYDZIALY.NAZWA, COUNT(ZATRUDNIENIA.ID_Z) AS ZATRUDNIONYCH
FROM WYDZIALY NATURAL JOIN ZATRUDNIENIA
WHERE ZATRUDNIENIA.DO IS NULL
HAVING COUNT(ZATRUDNIENIA.ID_Z)>2
GROUP BY WYDZIALY.NAZWA
ORDER BY 2 DESC, 1 ASC;
===================================
SELECT WYDZIALY.NAZWA, OSOBY.PLEC, SUM(ZATRUDNIENIA.PENSJA)
FROM OSOBY, WYDZIALY, ZATRUDNIENIA
WHERE ZATRUDNIENIA.DO IS NULL AND WYDZIALY.ID_W=ZATRUDNIENIA.ID_W
GROUP BY WYDZIALY.NAZWA, OSOBY.PLEC
ORDER BY 1 ASC, 3 ASC;
-----------------------------------------