===================================================================

TWORZENIE TABEL

===================================================================

TABELA KOD

create table KOD

(kod_pocztowy varchar2(6) primary key,

poczta varchar2(15));

TABELA PRZEDMIOT

create table Przedmiot

(ID_przedmiot varchar2(3) primary key,

Przedmiot varchar2(20),

Skrot varchar2(3));

TABELA POSTANOWIENIE

create table Postanowienie

(ID_postanowienie varchar2(3) primary key,

postanowienie varchar2(20));

TABELA KANDYDAT

create table Kandydat

(indeks varchar2(4) primary key,

Nazwisko varchar2(20) not null,

Imie varchar2(15) not null,

Adres varchar2(20),

Kod_pocztowy varchar2(6),

ID_postanowienie varchar2(3),

foreign key (kod_pocztowy) references kod,

foreign key (id_postanowienie) references postanowienie);

TABELA DOKUMENTY

create table Dokumenty

(Indeks varchar2(4),

Oplata_wpisowego varchar2(3),

Swiadectwo varchar2(3),

Zasw_lekarskie varchar2(3),

Fotografia varchar2(3),

primary key (Indeks),

foreign key (Indeks) references Kandydat);

TABELA OCENY

create table Oceny

(Indeks varchar2(4),

ID_Przedmiot varchar2(3),

Ocena varchar2(3),

primary key (Indeks, ID_przedmiot),

foreign key (Indeks) references Kandydat,

foreign key (ID_przedmiot) references Przedmiot);

===================================================================

WPROWADZANIE DANYCH

===================================================================

TABELA PRZEDMIOT

insert into przedmiot

values ('1', 'matematyka', 'mat');

insert into przedmiot

values ('2', 'jezyk angielski', 'ja');

insert into przedmiot

values ('3', 'jezyk francuski', 'jf');

insert into przedmiot

values ('4', 'jezyk niemiecki', 'jn');

TABELA POSTANOWIENIE

insert into postanowienie

values ('1', 'Przyjety');

insert into postanowienie

values ('2', 'Nie przyjety');

insert into postanowienie

values ('3', 'Zrezygnowal');

TABELA KOD

insert into kod (kod_pocztowy, Poczta)

values ('22-300', 'Krasnystaw');

insert into kod (kod_pocztowy, Poczta)

values ('21-100', 'Warszawa');

insert into kod (kod_pocztowy, Poczta)

values ('23-300', 'Zamosc');

insert into kod (kod_pocztowy, Poczta)

values ('20-100', 'Lublin');

insert into kod (kod_pocztowy, Poczta)

values ('20-101', 'Lublin');

insert into kod (kod_pocztowy, Poczta)

values ('22-100', 'Warszawa');

TABELA KANDYDAT

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('1', 'Kowalski', 'Jan', '22-300', 'Szkolna7/9');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('2', 'Matwiej', 'Anna', '22-300', 'Polna 3/3');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('3', 'Polny', 'Zenon', '20-100', 'Kwiatowa 5');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('4', 'Grzmot', 'Ewelina', '23-300', 'Ulańska 3');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('5', 'Kuna', 'Adam', '20-101', 'Borowa 3/5');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('6', 'Nowak', 'Elżbieta', '22-100', 'Krzywa 1');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('7', 'Nowak', 'Kamil', '22-100', 'Krzywa 1');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('8', 'Adamowicz', 'Adam', '22-100', 'Prosta 123');

TABELA DOKUMENTY

insert into Dokumenty (Indeks, Oplata_wpisowego, Swiadectwo, Zasw_lekarskie, Fotografia)

values ('1', 'Tak', 'Tak', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('2', 'Tak', 'NIE', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('3', 'Tak', 'Tak', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('4', 'Tak', 'Tak', 'Tak', 'NIE');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('5', 'Tak', 'NIE', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('6', 'Tak', 'Tak', 'NIE', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('7', 'NIE', 'Tak', 'Tak', 'NIE');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('8', 'NIE', 'Tak', 'NIE', 'Tak');

TABELA OCENY

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('1', '1', '4');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('1', '2', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('2', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('2', '3', '2');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('3', '1', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('3', '2', '4');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('4', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('4', '4', '4');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('5', '1', '2');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('5', '3', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('6', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('6', '2', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('7', '1', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('7', '4', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('8', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('8', '2', '3');

UZUPEŁNIENIE TABELI KANDYDAT

update kandydat

set id_postanowienie=1

where indeks='1';

update kandydat

set id_postanowienie=2

where indeks='2';

update kandydat

set id_postanowienie=3

where indeks='3';

update kandydat

set id_postanowienie=1

where indeks='4';

update kandydat

set id_postanowienie=2

where indeks='5';

update kandydat

set id_postanowienie=1

where indeks='6';

update kandydat

set id_postanowienie=3

where indeks='7';

update kandydat

set id_postanowienie=1

where indeks='8';

===================================================================

TWORZENIE PERSPEKTYW

===================================================================

UTWORZENIE PERSPEKTYWY KANDYDACI

create or replace view kandydaci

as

select u.Nazwisko, u.Imie, p.Oplata_wpisowego, p.Swiadectwo, p.Zasw_lekarskie, p.Fotografia

from Kandydat u, dokumenty p

where u.indeks=p.indeks ;

UTWORZENIE PERSPEKTYWY PRZED_OCENY

create or replace view przed_oceny

as

select u.Nazwisko, u.Imie, p.ocena, pu.przedmiot

from Kandydat u, oceny p, przedmiot pu

where u.indeks=p.indeks and p.id_przedmiot=pu.id_przedmiot;

===================================================================

ZAPYTANIA

===================================================================

OSOBY KTÓRE OTRZYMAŁY OCENY POWYŻEJ 2

select upper(Nazwisko), imie

from kandydat

where indeks=any

(select indeks from oceny where ocena>2)

order by Nazwisko;

ILOŚĆ OSÓB PRZYJĘTYCH NA STUDIA

select count(*) Przyjęto_osób

from kandydat

where ID_postanowienie Like '1';

select id_postanowienie, count(*) from kandydat

where id_postanowienie='1'

group by id_postanowienie;

OSOBY KTÓRE OTRZYMAŁY OCENY 2

select Nazwisko, imie

from kandydat

where indeks=any

(select indeks from oceny where ocena<3);

DANE PERSONALNE KANDYDATÓW

select u.Nazwisko, u.Imie, u.Adres, u.kod_pocztowy, pu.poczta

from Kandydat u, kod pu

where u.kod_pocztowy=pu.kod_pocztowy

order by u.Nazwisko;

select Imie, Nazwisko, Oplata_wpisowego, Swiadectwo, Zasw_lekarskie, Fotografia

from Kandydaci

order by imie, Nazwisko;

WYKAZ OSÓB Z POSTANOWIENIAMI WG NAZWISK

select u.Nazwisko, u.Imie, u.Adres, p.Postanowienie

from Kandydat u, postanowienie p

where u.ID_postanowienie=p.ID_postanowienie

order by u.nazwisko;

WYKAZ OSÓB Z POSTANOWIENIAMI WG POSTANOWIEŃ

select nazwisko, id_postanowienie

from kandydat

where indeks=

(select indeks

from postanowienie

where id_postanowienie<2)

order by id_postanowienie, nazwisko;

WYKAZ OSÓB Z OCENAMI WG Nazwisk

select u.Nazwisko, u.Imie, p.przedmiot, pu.ocena

from Kandydat u, przedmiot p, oceny pu

where u.indeks=pu.indeks and p.Id_przedmiot=pu.Id_przedmiot

order by u.Nazwisko;

WYKAZ OSÓB Z OCENAMI WG OCEN

select Nazwisko, imie, ocena

from przed_oceny

where przedmiot='matematyka'

order by ocena;

WYKAZ OSÓB Z OCENAMI WG PRZEDMIOTÓW I OCEN

select p. Przedmiot, u.Nazwisko, u.Imie, pu.ocena

from Kandydat u, przedmiot p, oceny pu

where u.indeks=pu.indeks and p.Id_przedmiot=pu.Id_przedmiot

order by p.przedmiot, ocena;

ILOŚĆ OSÓB NIE MAJĄCYCH WPŁACONEGO WPISOWEGO

select oplata_wpisowego, count(*) brak_wpisowego from dokumenty

where oplata_wpisowego='NIE'

group by oplata_wpisowego;

ŚREDNIA OCEN Z PRZEDMIOTÓW

select id_przedmiot, avg(ocena) from oceny

group by id_przedmiot

having count(*) >1;

ILOŚĆ OSÓB ZDAJĄCYCH JĘZYKI

select przedmiot, count(*)ilosc_zdajacych from przed_oceny

where przedmiot Like `j%'

group by przedmiot;

ILOŚĆ ZDAJĄCYCH DZIEWCZĄT

select count(*) liczba_dziewczat

from Kandydaci

where imie like `%a';

===================================================================

DANE DO WPROWADZENIA DO SQL-a

create table KOD

(kod_pocztowy varchar2(6) primary key,

poczta varchar2(15));

create table Przedmiot

(ID_przedmiot varchar2(3) primary key,

Przedmiot varchar2(20),

Skrot varchar2(3));

create table Postanowienie

(ID_postanowienie varchar2(3) primary key,

postanowienie varchar2(20));

create table Kandydat

(indeks varchar2(4) primary key,

Nazwisko varchar2(20) not null,

Imie varchar2(15) not null,

Adres varchar2(20),

Kod_pocztowy varchar2(6),

ID_postanowienie varchar2(3),

foreign key (kod_pocztowy) references kod,

foreign key (id_postanowienie) references postanowienie);

create table Dokumenty

(Indeks varchar2(4),

Oplata_wpisowego varchar2(3),

Swiadectwo varchar2(3),

Zasw_lekarskie varchar2(3),

Fotografia varchar2(3),

primary key (Indeks),

foreign key (Indeks) references Kandydat);

create table Oceny

(Indeks varchar2(4),

ID_Przedmiot varchar2(3),

Ocena varchar2(3),

primary key (Indeks, ID_przedmiot),

foreign key (Indeks) references Kandydat,

foreign key (ID_przedmiot) references Przedmiot);

insert into przedmiot

values ('1', 'matematyka', 'mat');

insert into przedmiot

values ('2', 'jezyk angielski', 'ja');

insert into przedmiot

values ('3', 'jezyk francuski', 'jf');

insert into przedmiot

values ('4', 'jezyk niemiecki', 'jn');

insert into postanowienie

values ('1', 'Przyjety');

insert into postanowienie

values ('2', 'Nie przyjety');

insert into postanowienie

values ('3', 'Zrezygnowal');

insert into kod (kod_pocztowy, Poczta)

values ('22-300', 'Krasnystaw');

insert into kod (kod_pocztowy, Poczta)

values ('21-100', 'Warszawa');

insert into kod (kod_pocztowy, Poczta)

values ('23-300', 'Zamosc');

insert into kod (kod_pocztowy, Poczta)

values ('20-100', 'Lublin');

insert into kod (kod_pocztowy, Poczta)

values ('20-101', 'Lublin');

insert into kod (kod_pocztowy, Poczta)

values ('22-100', 'Warszawa');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('1', 'Kowalski', 'Jan', '22-300', 'Szkolna7/9');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('2', 'Matwiej', 'Anna', '22-300', 'Polna 3/3');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('3', 'Polny', 'Zenon', '20-100', 'Kwiatowa 5');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('4', 'Grzmot', 'Ewelina', '23-300', 'Ulańska 3');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('5', 'Kuna', 'Adam', '20-101', 'Borowa 3/5');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('6', 'Nowak', 'Elżbieta', '22-100', 'Krzywa 1');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('7', 'Nowak', 'Kamil', '22-100', 'Krzywa 1');

insert into Kandydat (Indeks, Nazwisko, imie, kod_pocztowy, adres)

values ('8', 'Adamowicz', 'Adam', '22-100', 'Prosta 123');

insert into Dokumenty (Indeks, Oplata_wpisowego, Swiadectwo, Zasw_lekarskie, Fotografia)

values ('1', 'Tak', 'Tak', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('2', 'Tak', 'NIE', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('3', 'Tak', 'Tak', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('4', 'Tak', 'Tak', 'Tak', 'NIE');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('5', 'Tak', 'NIE', 'Tak', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('6', 'Tak', 'Tak', 'NIE', 'Tak');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('7', 'NIE', 'Tak', 'Tak', 'NIE');

insert into Dokumenty (indeks, oplata_wpisowego, Swiadectwo, zasw_lekarskie, Fotografia)

values ('8', 'NIE', 'Tak', 'NIE', 'Tak');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('1', '1', '4');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('1', '2', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('2', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('2', '3', '2');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('3', '1', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('3', '2', '4');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('4', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('4', '4', '4');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('5', '1', '2');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('5', '3', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('6', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('6', '2', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('7', '1', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('7', '4', '5');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('8', '1', '3');

insert into Oceny (Indeks, ID_przedmiot, ocena)

values ('8', '2', '3');

update kandydat

set id_postanowienie=1

where indeks='1';

update kandydat

set id_postanowienie=2

where indeks='2';

update kandydat

set id_postanowienie=3

where indeks='3';

update kandydat

set id_postanowienie=1

where indeks='4';

update kandydat

set id_postanowienie=2

where indeks='5';

update kandydat

set id_postanowienie=1

where indeks='6';

update kandydat

set id_postanowienie=3

where indeks='7';

update kandydat

set id_postanowienie=1

where indeks='8';

create or replace view kandydaci

as

select u.Nazwisko, u.Imie, p.Oplata_wpisowego, p.Swiadectwo, p.Zasw_lekarskie, p.Fotografia

from Kandydat u, dokumenty p

where u.indeks=p.indeks ;

create or replace view przed_oceny

as

select u.Nazwisko, u.Imie, p.ocena, pu.przedmiot

from Kandydat u, oceny p, przedmiot pu

where u.indeks=p.indeks and p.id_przedmiot=pu.id_przedmiot;

col indeks for A6

col kod_pocztowy for A12

col id_przedmiot for A12

col skrot for A5

col id_postanowienie for A16

col oplata_wpisowego for A16

col swiadectwo for A10

col zasw_lekarskie for A14

col fotografia for A10

col ocena for A5

select upper(Nazwisko), imie

from kandydat

where indeks=any

(select indeks from oceny where ocena>2)

order by Nazwisko;

select count(*) Przyjęto_osób

from kandydat

where ID_postanowienie Like '1';

select id_postanowienie, count(*) from kandydat

where id_postanowienie='1'

group by id_postanowienie;

select Nazwisko, imie

from kandydat

where indeks=any

(select indeks from oceny where ocena<3);

select u.Nazwisko, u.Imie, u.Adres, u.kod_pocztowy, pu.poczta

from Kandydat u, kod pu

where u.kod_pocztowy=pu.kod_pocztowy

order by u.Nazwisko;

select Imie, Nazwisko, Oplata_wpisowego, Swiadectwo, Zasw_lekarskie, Fotografia

from Kandydaci

order by imie, Nazwisko;

select u.Nazwisko, u.Imie, u.Adres, p.Postanowienie

from Kandydat u, postanowienie p

where u.ID_postanowienie=p.ID_postanowienie

order by u.nazwisko;

select nazwisko, id_postanowienie

from kandydat

where indeks=

(select indeks

from postanowienie

where id_postanowienie<2)

order by id_postanowienie, nazwisko;

select u.Nazwisko, u.Imie, p.przedmiot, pu.ocena

from Kandydat u, przedmiot p, oceny pu

where u.indeks=pu.indeks and p.Id_przedmiot=pu.Id_przedmiot

order by u.Nazwisko;

select Nazwisko, imie, ocena

from przed_oceny

where przedmiot='matematyka'

order by ocena;

select p. Przedmiot, u.Nazwisko, u.Imie, pu.ocena

from Kandydat u, przedmiot p, oceny pu

where u.indeks=pu.indeks and p.Id_przedmiot=pu.Id_przedmiot

order by p.przedmiot, ocena;

select oplata_wpisowego, count(*) brak_wpisowego from dokumenty

where oplata_wpisowego='NIE'

group by oplata_wpisowego;

select id_przedmiot, avg(ocena) from oceny

group by id_przedmiot

having count(*) >1;

select przedmiot, count(*)ilosc_zdajacych from przed_oceny

where przedmiot Like `j%'

group by przedmiot;

select count(*) liczba_dziewczat

from Kandydaci

where imie like `%a';

SQL - baza na zaliczenie Siedlaczek Jacek 4kzz - 2000/2001

17

---