use ja1
create table dbo.uczen
(U_id int identity,
U_nazwisko varchar(30) not null,
U_imie varchar(30) not null,
U_miejscowosc varchar(30) not null,
constraint klucz1 primary key (U_id)
)
create table dbo.nauczyciel
(N_id int identity,
N_nazwisko varchar(30) not null,
N_imie varchar(30) not null,
N_miejscowosc varchar(30) not null,
N_telefon int,
K_id int not null,
constraint klucz2 primary key (N_id)
)
create table dbo.kontakt
(U_id int,
U_nazwisko varchar(30) not null,
U_matka varchar(30) not null,
U_ojciec varchar(30) not null,
U_telefon int,
U_miejscowosc varchar(30) not null
)
create table dbo.kurs
(K_id int identity,
K_nazwa varchar(30) not null,
K_cena int not null
constraint klucz3 primary key (K_id)
)
create table dbo.zajecia
(K_id int,
U_id int,
Z_godzina int not null,
Z_dzien varchar(30) not null
)
create table dbo.konsultacje
(N_id int,
K_godz int not null,
K_dzien varchar(30) not null
)
create table dbo.oceny
(U_id int,
U_ocena int not null
)
create table dbo.platnosci
(U_id int,
P_ilegodzin int,
P_wynik int,
P_wplata int,
P_dozaplaty int not null
)
create table dbo.wyplaty
(N_id int,
W_ilegodzin int,
W_stawka int not null,
W_wynik int,
W_wyplata int,
W_dozaplaty int not null
)
create table dbo.historia_wyplat
(N_id int,
H_wyplata int
)
create table dbo.historia_wplat
(U_id int,
H_wplata int)
insert into dbo.uczen (U_nazwisko, U_imie, U_miejscowosc)
values ('Kowalski','Jan','Gdynia'),
('Nowak','Pawe³','Gdansk'),
('Pietrzak','Agnieszka','Sopot')
insert into dbo.kurs (K_nazwa, K_cena)
values ('MatematykaG',30),
('FizykaG',30),
('MatematykaM',40),
('FizykaM',40)
insert into dbo.nauczyciel (N_nazwisko, N_imie, N_miejscowosc, N_telefon, K_id)
values ('Kotkowski','Leszek','Wejherowo',598309278,1),
('Lubkowski','Marian','Wejherowo',394806578,2),
('Liszniewska','Judyta','Gdynia',498608579,3),
('Madej','Karolina','Rumia',509849738,4)
insert into dbo.kontakt (U_id, U_nazwisko, U_matka, U_ojciec, U_telefon, U_miejscowosc)
values (1,'Kowalski','Anna','Tomasz',308495820,'Gdynia'),
(2,'Nowak','Janina','Andrzej',950839209,'Gdansk'),
(3,'Pietrzak','Bogus³awa','Roman',409586079,'Sopot')
insert into dbo.zajecia (K_id, U_id, Z_godzina, Z_dzien)
values (1,1,16,'poniedzialek'),
(2,2,17,'sroda'),
(3,3,18,'czwartek'),
(4,3,18,'piatek')
insert into dbo.konsultacje (N_id, K_godz, K_dzien)
values (1,19,'sobota'),
(2,20,'sobota'),
(3,19,'piatek'),
(4,20,'piatek')
insert into dbo.oceny (U_id,U_ocena)
values (1,4),
(2,4),
(3,5)
insert into dbo.platnosci (U_id,P_ilegodzin, P_wynik, P_wplata, P_dozaplaty)
values (1,2,60,10,50),
(2,2,60,20,40),
(3,4,160,60,100)
insert into dbo.wyplaty (N_id,W_ilegodzin,W_stawka,W_wynik,W_wyplata,W_dozaplaty)
values (1,2,15,30,10,20),
(2,2,15,30,0,30),
(3,2,20,40,20,20),
(4,2,20,40,0,40)
insert into dbo.historia_wyplat (N_id,H_wyplata)
values (1,10),
(2,0),
(3,20),
(4,0)
insert into dbo.historia_wplat (U_id,H_wplata)
values (1,10),
(2,20),
(3,60)
alter table dbo.kontakt
add constraint rkontakt
foreign key(U_id) references dbo.uczen (U_id)
alter table dbo.oceny
add constraint roceny
foreign key(U_id) references dbo.uczen (U_id)
alter table dbo.platnosci
add constraint rplatnosci
foreign key(U_id) references dbo.uczen (U_id)
alter table dbo.zajecia
add constraint rzajecia
foreign key(U_id) references dbo.uczen (U_id)
alter table dbo.historia_wplat
add constraint rhistoria2
foreign key(U_id) references dbo.uczen (U_id)
alter table dbo.konsultacje
add constraint rkonsultacje
foreign key(N_id) references dbo.nauczyciel (N_id)
alter table dbo.historia_wyplat
add constraint rhistoria
foreign key(N_id) references dbo.nauczyciel (N_id)
alter table dbo.wyplaty
add constraint rwyplaty
foreign key(N_id) references dbo.nauczyciel (N_id)
alter table dbo.nauczyciel
add constraint rnauczyciel
foreign key(K_id) references dbo.kurs (K_id)
alter table dbo.zajecia
add constraint rzajecia2
foreign key(K_id) references dbo.kurs (K_id)
use ja1
select AVG(U_ocena)
as srednia_ocen
from dbo.oceny; --œrednia ocen wszystkich uczniów--
create view dbo.ktoigdzie
as
SELECT
uczen.U_imie, uczen.U_nazwisko, kurs.K_nazwa
FROM uczen
INNER JOIN zajecia ON
uczen.U_id = zajecia.U_id
INNER JOIN kurs ON
zajecia.K_id = kurs.K_id --widok: jaki uczeñ zapisa³ siê na jaki kurs--
create view dbo.ktokogoico
as
SELECT
uczen.U_nazwisko, uczen.U_imie, nauczyciel.N_nazwisko, nauczyciel.N_imie, kurs.K_nazwa
FROM uczen
INNER JOIN zajecia ON
uczen.U_id = zajecia.U_id
INNER JOIN kurs ON
zajecia.K_id = kurs.K_id
INNER JOIN nauczyciel ON
kurs.K_id = nauczyciel.K_id --widok: jakiego ucznia uczy jaki nauczyciel i czego--
create function dbo.wplacil (@id int)
returns varchar(30)
as
begin
declare @idd as int
select @idd=dbo.historia_wplat.U_id
from historia_wplat
where @id=dbo.historia_wplat.U_id;
declare @wpl as int
select @wpl=dbo.historia_wplat.H_wplata
from historia_wplat
declare @ret as varchar(30)
if @wpl>0
set @ret='wplacil'
if @wpl<=0
set @ret='nie wplacil'
return @ret
end
--funkcja sprawdzajaca czy uczen o danym ID wplacil pieniadze
create function dbo.zarobki(@id int)
returns int
as
begin
declare @iddd as int
select @iddd=dbo.historia_wyplat.N_id
from historia_wyplat
where @id=dbo.historia_wyplat.N_id;
declare @wypl as int
select @wypl=dbo.historia_wyplat.H_wyplata
from historia_wyplat
return @wypl
end
--funkcja zwracajaca ile wyplaty dostal juz nauczyciel o danym ID
create trigger dbo.powiadomieniee on dbo.zajecia
after insert
as
declare @i int, @w int
select @i=inserted.U_id from inserted
SELECT @w=count(K_id) from dbo.zajecia
where U_id=@i
if @w<3 print 'ok'
else
print 'Zastanow sie. Zapisales sie juz na 3 kursy'