3Q7n2zie

1drop table if exists kklientci
2drop table if exists kfaktury
3drop table if exists ksamochody
4drop procedure if exists zadk1
5drop procedure if exists zadk2
6go
7create table kklienci (
8idk int identity primary key,
9imie varchar(20),
10nazwisko varchar(20))
11create table ksamochody(
12ids int identity primary key,
13data_prod date,
14cena float not null)
15create table kfaktury(
16idf int identity primary key,
17idk int,
18ids int,
19data date)
20go
21insert into kklienci values
22('Stefan','haha'),
23('Kuba','Guzik'),
24('Jez','Jerzy')
25insert into ksamochody values
26('1990-01-01',60000.0),
27('1997-02-01',122000.0),
28('2000-01-03',30000.0)
29insert into kfaktury values
30(1,3,'2003-01-01'),
31(2,2,'2005-12-11'),
32(3,1,'2006-05-06')
33go
34select*from kklienci
35select*from ksamochody
36select*from kfaktury
37go
38
39create procedure zadk1
40as
41select kfaktury.ids as 'id_samochodu', kfaktury.data as 'data sprzedazy',kfaktury.idk as'id_klienta','sprzedany' as 'status',data_prod as'data produkcji samochodu'
42from kfaktury join ksamochody on kfaktury.ids=ksamochody.ids
43go
44exec zadk1
45go
46
47create procedure zadk2
48as
49select idf as 'id_faktury',data as'data wystawienia faktury',idk as 'id_klienta',kfaktury.ids as 'id_samochodu',cena as 'koszt_netto',1.24*cena as 'koszt_brutto',
50rabat=case 
51	when cena>50000 then cena-(cena*0.05)
52	when cena>90000 then cena-(cena*0.1)
53	when cena>120000 then cena-(cena*0.85)
54	else cena
55end
56from kfaktury join ksamochody on ksamochody.ids=kfaktury.ids
57go
58exec zadk2