-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathWorkshop_8.sql
168 lines (140 loc) · 5.18 KB
/
Workshop_8.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
create database [BP2.Ispit.07.09.2017.Integralni] on(
name='data[BP2.Ispit.07.09.2017',
filename='D:\BP2\Data\dataproba.mdf',
size= 5MB,
maxsize=10MB,
filegrowth=20%
)
log on(
name='log[BP2.Ispit.07.09.2017',
filename='D:\BP2\Log\logproba.ldf',
size= 5MB,
maxsize=10MB,
filegrowth=20%
)
use [BP2.Ispit.07.09.2017.Integralni]
create table Klijenti(
KlijentID int identity(1,1) primary key,
Ime nvarchar(50) not null,
Prezime nvarchar(50) not null,
Grad nvarchar(50) not null,
Telefon nvarchar(50) not null,
Email nvarchar(50) not null
)
create table Racuni(
RacunID int identity(1,1) primary key,
DatumOtvaranja date not null,
TipRacuna nvarchar(50) not null,
BrojRacuna nvarchar(16) not null,
Stanje decimal not null,
KlijentID int not null constraint fk_KlijentID foreign key references Klijenti(KlijentID)
)
drop table Transakcije
create table Transakcije(
TransakcijaID int identity(1,1) primary key,
Datum date not null,
Primatelj nvarchar(50) not null,
BrojRacunaPrimatelja nvarchar(16) not null,
MjestoPrimatelja nvarchar(50) not null,
AdresaPrimatelja nvarchar(50) null,
Svrha nvarchar(200) null,
Iznos decimal not null,
RacunID int not null constraint fk_racunid foreign key references Racuni(RacunID)
)
drop table Transakcije
alter table Klijenti
add constraint unq_email unique(Email)
alter table Racuni
add constraint unq_brojracuna unique(BrojRacuna)
create procedure unesi_racun(
@DatumOtvaranja date,
@TipRacuna nvarchar(50),
@BrojRacuna nvarchar(16),
@Stanje decimal,
@KlijentID int
)
as
begin
insert into Racuni
values(@DatumOtvaranja,@TipRacuna,@BrojRacuna,@Stanje,@KlijentID)
end
select * from Klijenti
insert into Klijenti
values('Faris','Gogic','Konjic','061602460','anskdn@email.com')
exec unesi_racun '9/1/2019','Transferni','82818ASMBA',28.9,1
insert into Klijenti
select distinct left(c.ContactName,CHARINDEX(' ',c.ContactName)) as 'Ime',right(c.ContactName,len(c.ContactName)-CHARINDEX(' ',c.ContactName)) as 'Prezime',c.City,
replace(c.ContactName,' ','.')+'@northwind.ba' as 'Email',c.Phone
from NORTHWND.dbo.Customers as c join NORTHWND.dbo.Orders as o on c.CustomerID=o.CustomerID
where year(o.RequiredDate) =1996
exec unesi_racun '9/1/2019','Transferni','82818AuMBA',23.9,1
exec unesi_racun '9/1/2019','Transferni','8281AAMBA',22.9,1
exec unesi_racun '9/1/2019','Transferni','828SSoMBA',21.9,3
exec unesi_racun '9/1/2019','Transferni','8281SSASMBA',98.9,3
exec unesi_racun '9/1/2019','Transferni','8118ASMBA',88.9,4
exec unesi_racun '9/1/2019','Transferni','825818ASMBA',78.9,6
exec unesi_racun '9/1/2019','Transferni','810118ASMBA',68.9,6
exec unesi_racun '9/1/2019','Transferni','82998ASMBA',58.9,4
exec unesi_racun '9/1/2019','Transferni','82228ASMBA',48.9,7
exec unesi_racun '9/1/2019','Transferni','83338ASMBA',31.9,7
select * from Racuni
select * from Klijenti where Klijenti.Grad = 'Madrid'
insert into Transakcije
select top 10 o.OrderDate,o.ShipName,'00000'+convert(nvarchar(30),o.OrderID+ convert(int,'00000123456')) as 'Broj racuna',o.ShipCity,o.ShipAddress,null as 'Svrha',
od.UnitPrice,12 as 'RacunID'
from NORTHWND.dbo.Orders as o join NORTHWND.dbo.[Order Details] as od on o.OrderID=od.OrderID
order by newid()
update Racuni
set Stanje= Stanje+500
where Racuni.KlijentID IN (select k.KlijentID from Klijenti as k join Racuni as r on k.KlijentID=r.KlijentID where k.Grad='Madrid' and MONTH(r.DatumOtvaranja)=9 )
CREATE view pogled
as
select k.Ime+k.Prezime as 'Ime i prezime',k.Grad,k.Email,k.Telefon,r.TipRacuna,r.BrojRacuna,r.Stanje,t.BrojRacunaPrimatelja,t.Iznos
from Klijenti as k left join Racuni as r on k.KlijentID=r.KlijentID left join Transakcije as t on r.RacunID=t.RacunID
select * from pogled
alter procedure procaa(
@BrojRacuna nvarchar(16) = null
)
as
begin
select isnull(p.[Ime i prezime],'N/A'),ISNULL(p.Grad,'N/A'),ISNULL(p.Telefon,'N/A'),ISNULL(p.BrojRacuna,'N/A'),
ISNULL(CONVERT(nvarchar(6),p.Stanje),'N/A'),isnull(convert(nvarchar(6),sum(p.Iznos)),'N/A') as 'Ukupan iznos sa racuna'
from pogled as p
where @BrojRacuna = p.BrojRacuna or @BrojRacuna is null
group by p.[Ime i prezime],p.Grad,p.Telefon,p.BrojRacuna,p.Stanje
end
exec procaa '82818ASMBA'
alter procedure brisi(
@KlijentID int
)
as
begin
delete Transakcije
where RacunID IN (select t.RacunID from Klijenti as k join Racuni as r on k.KlijentID=r.KlijentID join Transakcije as t on r.RacunID=t.RacunID
where k.KlijentID=@KlijentID)
delete Racuni
where @KlijentID = KlijentID
delete Klijenti
where KlijentID=@KlijentID
end
select * from Transakcije
exec brisi 4
select * from Racuni
select * from Klijenti
create procedure uvecaj(
@Grad nvarchar(20),
@Mjesec int,
@IznosUvecanja decimal
)
as
begin
update Racuni
set Stanje= Stanje+@IznosUvecanja
where Racuni.KlijentID IN (select k.KlijentID from Klijenti as k join Racuni as r on k.KlijentID=r.KlijentID where k.Grad=@Grad and MONTH(r.DatumOtvaranja)=@Mjesec )
end
exec uvecaj 'Toulouse',9,25.9
backup database [BP2.Ispit.07.09.2017.Integralni]
to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLA\MSSQL\Backup\pit.07.09.2017.Integralni]full.bak'
backup database [BP2.Ispit.07.09.2017.Integralni]
to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLA\MSSQL\Backup\pit.07.09.2017.Integralni]dif.bak'
with differential