· 6 years ago · May 28, 2019, 05:38 PM
1drop table if exists tabelka
2drop procedure if exists proc2
3drop function if exists faktureczka
4drop function if exists rabacik
5drop procedure if exists proc1
6drop view if exists towarki
7drop view if exists fakturki
8drop view if exists widook
9drop view if exists widoczek_pom
10drop table if exists clients
11drop table if exists invoice
12drop table if exists stuf
13drop table if exists deliveryguys
14drop table if exists category
15drop table if exists delivers
16drop table if exists storehouse
17drop view if exists widoczek
18go
19---------------------------------------------------tabele
20create table clients(
21ID_klienta int primary key,
22imie varchar(20),
23nazwisko varchar(20),
24PESEL varchar(11) not null
25)
26create table invoice(
27ID_faktury int IDENTITY primary key,
28klient int,
29co int,
30ile int,
31suma int,
32data date,
33rabat float,
34wysyl int
35)
36create table stuf(
37ID_towaru int primary key,
38nazwa varchar(20),
39kategoria int,
40cena int
41)
42create table deliveryguys(
43ID_dostawcy int primary key,
44nazwa varchar(20)
45)
46create table category(
47ID_kategori int primary key,
48nazwa varchar(20),
49)
50create table delivers(
51ID_wysylki int primary key,
52kto int,
53faktura int,
54data_wysylki date,
55data_dostarczenia date
56)
57create table storehouse(
58co int,
59ile int,
60)
61go
62---------------------------------------------------insert into
63insert into clients values
64(1,'Kuba','Guzik','98558718787'),
65(2,'Mario','Luidzi','97435353481')
66go
67insert into invoice values
68(2,2,2,120,'02-02-2010',0,1),
69(1,1,1,240,'01-01-2011',0,2),
70(2,1,1,240,'02-01-2011',0,3),
71(1,2,10,600,'03-01-2012',0,4)
72insert into stuf values
73(1,'odchody',1,240),
74(2,'drzewko',2,60)
75insert into deliveryguys values
76(1,'AVIZO'),
77(2,'OZIVA')
78insert into category values
79(1,'nawozy'),
80(2,'rosliny')
81insert into delivers values
82(1,1,1,'03-02-2010','10-02-2011'),
83(2,2,2,'02-01-2011','04-01-2011'),
84(3,3,2,'03-01-2011','05-01-2011'),
85(4,4,1,'05-01-2012','05-03-2019')
86insert into storehouse values
87(1,1000),
88(2,1)
89go
90--------------------------------------------------
91create view widoczek_pom as
92select sum(suma)as'kasa',klient from invoice group by klient
93go
94select ID_klienta,nazwisko,PESEL,kasa
95into tabelka
96from clients join widoczek_pom on ID_klienta=klient
97go
98create view widook as
99select ID_klienta,nazwisko,PESEL,kasa as'suma' from tabelka where kasa=(select max(kasa) from tabelka)
100go
101select*from widook
102go
103---------------------------------------------------------------------
104create view fakturki as
105select ID_faktury,nazwisko as'nazwisko klienta',ID_klienta,data
106from invoice join clients on klient=ID_klienta
107go
108select*from fakturki
109go
110-------------------------------------------------------------------------
111create view towarki as
112select stuf.nazwa as 'nazwa towaru',ID_towaru,deliveryguys.nazwa as 'nazwa dostawcy',ID_dostawcy,category.nazwa as 'nazwa kategorii',ID_kategori,ile as 'stan na magazynie'
113from stuf join category on ID_kategori=kategoria join invoice on ID_towaru=co join delivers on ID_faktury=faktura join deliveryguys on ID_dostawcy=delivers.kto
114go
115select*from towarki
116go
117--------------------------------------------------------------------------
118create procedure proc1 @idfakt int,@idtow int,@ilosctow int as
119 declare @odp varchar(3)
120 set @odp=
121 case
122 when exists (select*from invoice where ID_faktury=@idfakt)
123 and exists (select*from stuf where ID_towaru=@idtow)
124 then 'tak' else 'nie'
125 end
126 if @odp='tak'
127 begin
128 update invoice set co=@idtow,ile=@ilosctow,suma=@ilosctow*(select cena from stuf where ID_towaru=@idtow) where @idfakt=ID_faktury
129 select stuf.nazwa as 'nazwa towaru',ID_towaru,deliveryguys.nazwa as 'nazwa dostawcy',storehouse.ile as 'stan w magazynie'
130 from deliveryguys join delivers on ID_dostawcy=delivers.kto join invoice on ID_faktury=faktura join stuf on ID_towaru=invoice.co join storehouse on ID_towaru=storehouse.co
131 where ID_towaru=@idtow
132 end
133go
134exec proc1 1,1,20
135go
136--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
137create function rabacik(@idklienta int)
138returns float
139 begin
140 declare @rabat float
141 declare @wydane float
142 set @wydane=(select kasa from tabelka where ID_klienta=@idklienta)
143 set @rabat = case
144 when @wydane<1000 then 0
145 when @wydane<3000 then 0.05
146 when @wydane<10000 then 0.07
147 else 0.1
148 end
149 return @rabat
150 end
151go
152update invoice set rabat=(dbo.rabacik(klient))
153go
154-------------------------------------------------------------------------------------------------
155create function faktureczka(@idfakt int)
156returns table as
157return (
158 select ID_faktury,klient,co,ile,suma,data,rabat,suma-suma*rabat as 'po rabacie' from invoice where ID_faktury=@idfakt
159 )
160go
161select*from faktureczka(2)
162go
163--------------------------------------------------------------------------------------------------
164create procedure proc2 @idklienta int,@idwysylki int
165as
166 declare @odp varchar(3)
167 set @odp=case
168 when exists (select*from clients where ID_klienta=@idklienta)
169 and exists (select*from delivers where ID_wysylki = @idwysylki)
170 then 'tak'
171 else 'nie'
172 end
173 if @odp='tak'
174 begin
175 insert into invoice values(@idklienta,1,1,(select cena from stuf where ID_towaru=1),getdate(),dbo.rabacik(@idklienta),@idwysylki)
176 select*from invoice
177 end
178 else select 'blad'
179go
180exec proc2 2,3
181go