· 9 years ago · Dec 15, 2016, 02:38 PM
1--2
2--2.1
3alter Table Projekt Add LeiterID varchar(20)
4Select * from Projekt
5--2.2
6Update Projekt Set LeiterID=(Select a.MitID
7 from Mitarbeiter a
8 Where a.Nachname=ProLeiter)
9--2.3
10Update Projekt Set ProLeiter='Uhu'
11where ProLeiter='Igel'
12--2.4
13Alter Table projekt
14Drop column ProLeiter
15
16--3
17--3.1
18Select * from Mitarbeiter
19Insert Into Mitarbeiter Select 210, 'Orawetz', 'Jimmy', 'Dresden', convert(date,'23.12.1996'), 'Student', '0315/5233410'
20--3.2
21Select * from Projekt
22Insert Into Projekt Select 46, 'MS-SQL-Prakt', 'Dresden',null, '3','210'
23--3.3
24Select * From Zuordnung
25Insert into Zuordnung Select 210, 46, 0.4, 0.7
26--3.4
27Insert into Zuordnung Select 210, 37, 0.6, 0.3
28Select * From Zuordnung
29--3.5
30Insert into Zuordnung Select 106, 37, 0.2, 0.1
31Select * from Zuordnung
32--3.6
33Insert into Projekt Select 47,'Oracle-Prakt.', 'Dresden',null,0,null
34Select * from Projekt
35
36--4.
37--4.1
38--a
39create procedure Projekte (@ProNr char(3))
40As
41Select a.ProNr, a.ProAufwand, b.MitID, b.Nachname, b.Vorname from Projekt a
42Left Join Mitarbeiter b
43 On a.LeiterID=b.MitID
44 Where @ProNr=a.ProNr
45Drop procedure Projekte
46Exec Projekte 46
47Select* from Projekt
48--b
49Create procedure Projekte_b(@ProNr char(3))
50As
51Declare @Plananteil float
52Declare @Istanteil float
53Declare @ProName char (30)
54Declare @PlanIstDiff float
55Declare @Vorname char(15)
56Declare @MitID char(3)
57Declare @Nachname char(15)
58Declare @Beruf varchar(30)
59Declare cur Cursor for
60 select z.Plananteil, z.Istanteil, z.Plananteil-z.Istanteil, m.MitID, m.Nachname, m.Vorname, m.Beruf
61 from Zuordnung z, Mitarbeiter m
62 where @ProNr=z.ProNr and m.MitID=z.MitID
63 open cur
64 fetch cur into @Plananteil, @Istanteil, @PlanIstDiff,@MitID, @Nachname, @Vorname, @Beruf
65 if(@@FETCH_STATUS = -2)
66 print 'Keine Mitarbeiter im Projekt vorhanden'
67 else
68 While @@Fetch_STATUS=0
69 Begin
70 print 'MitID:'+ convert(char(4),@MitID)
71 print 'Nachname: ' + convert(char(15),@Nachname)
72 print 'Vorname: ' + convert(char(15),@Vorname)
73 print 'Beruf: ' + convert(char(15),@Beruf)
74 print 'Plananteil: ' + convert(char(5), @Plananteil)
75 print 'Istanteil: ' + convert(char(5), @Istanteil)
76 print 'Abweichung(Ist-Plan): ' + convert(char(5), @PlanIstDiff)
77 print '-------------------------------------------------------------------'
78 fetch cur into @Plananteil, @Istanteil, @PlanIstDiff,@MitID, @Nachname, @Vorname, @Beruf
79 End
80 Close cur
81 DEALLOCATE cur
82Drop procedure Projekte_b
83Exec Projekte_b 37
84Select * From Projekt
85
86--4.1 zusammen
87Create procedure Projekte_c(@ProNr char(3))
88As
89Declare @Plananteil float
90Declare @Istanteil float
91Declare @ProName char (30)
92Declare @PlanIstDiff float
93Declare @Vorname char(15)
94Declare @MitID char(3)
95Declare @Nachname char(15)
96Declare @Beruf varchar(30)
97Select a.ProNr, a.ProAufwand, b.MitID, b.Nachname, b.Vorname from Projekt a
98Left Join Mitarbeiter b
99 On a.LeiterID=b.MitID
100 Where @ProNr=a.ProNr
101Declare cur Cursor for
102 select z.Plananteil, z.Istanteil, z.Plananteil-z.Istanteil, m.MitID, m.Nachname, m.Vorname, m.Beruf
103 from Zuordnung z, Mitarbeiter m
104 where @ProNr=z.ProNr and m.MitID=z.MitID
105 open cur
106 fetch cur into @Plananteil, @Istanteil, @PlanIstDiff,@MitID, @Nachname, @Vorname, @Beruf
107 if(@@FETCH_STATUS = -2)
108 print 'Keine Mitarbeiter im Projekt vorhanden'
109 else
110 While @@Fetch_STATUS=0
111 Begin
112 print 'MitID:'+ convert(char(4),@MitID)
113 print 'Nachname: ' + convert(char(15),@Nachname)
114 print 'Vorname: ' + convert(char(15),@Vorname)
115 print 'Beruf: ' + convert(char(15),@Beruf)
116 print 'Plananteil: ' + convert(char(5), @Plananteil)
117 print 'Istanteil: ' + convert(char(5), @Istanteil)
118 print 'Abweichung(Ist-Plan): ' + convert(char(5), @PlanIstDiff)
119 print '-------------------------------------------------------------------'
120 fetch cur into @Plananteil, @Istanteil, @PlanIstDiff,@MitID, @Nachname, @Vorname, @Beruf
121 End
122 Close cur
123 DEALLOCATE cur
124
125
126Drop procedure Projekte_c
127Exec Projekte_c 37
128--5
129--5.1 Where
130Select m.Nachname, m.Vorname, m.Ort, p.ProName from Mitarbeiter m, Projekt p
131Where p.LeiterID=m.MitID
132--5.1 Join
133Select m.Nachname, m.Vorname, m.Ort, p.ProName from Mitarbeiter m
134Join Projekt p on p.LeiterID=m.MitID
135
136--5.2 Where
137Select m.MitID,m.Nachname,m.Ort,m.Gebdat,m.Beruf,m.Telnr, z.ProNr, z.Istanteil from Mitarbeiter m, Zuordnung z
138where z.MitID=m.MitID
139
140--5.2 Join
141Select m.MitID,m.Nachname,m.Ort,m.Gebdat,m.Beruf,m.Telnr, z.ProNr, z.Istanteil from Mitarbeiter m
142Join Zuordnung z
143on m.MitID=z.MitID
144
145--5.3 Where noch testen!
146Select * from Projekt
147Select p.ProNr, p.ProName, p.ProOrt, p.ProBeschreibung, p.ProAufwand, p.LeiterID, m.Nachname, m.Vorname, m.Ort from Projekt p, Mitarbeiter m
148Where p.ProAufwand>=3 and p.LeiterID=m.MitID
149
150--5.3 Join
151Select p.ProNr, p.ProName, p.ProOrt, p.ProBeschreibung, p.ProAufwand, p.LeiterID, m.Nachname, m.Vorname, m.Ort from Projekt p
152Join Mitarbeiter m on p.LeiterID=m.MitID
153Where p.ProAufwand>=3
154
155--5.4 Join
156Select p.ProNr, p.ProName, Sum(z.Istanteil) as 'Summe der Istanteile' from Projekt p
157Left OUTER JOIN Zuordnung z on p.ProNr=z.ProNr
158Group BY z.ProNr,p.ProNr,p.ProName
159Order BY ProNr
160
161--5.4 Union
162Select p.ProNr, p.ProName, Sum(z.Istanteil) as 'Summe der Istanteile' from Projekt p
163Join Zuordnung z on p.ProNr=z.ProNr
164Group BY z.ProNr,p.ProNr,p.ProName
165union
166Select ProNr, ProName, '0.0' from Projekt
167Where not exists (Select ProNr from Zuordnung where Zuordnung.ProNr = Projekt.ProNr)
168Order By ProNr
169
170--5.5
171Select m.MitID,m.Nachname, 1-Sum(z.Plananteil) as 'Summer der Plananteile' from Zuordnung z
172Right OUTER Join Mitarbeiter m on m.MitID=z.MitID
173Group by m.MitID, m.Nachname
174Order by MitID
175
176Select * from Projekt
177
178--3.
179--3.1
180CREATE FUNCTION AlterErmitteln (@gebdat date)
181Returns int
182as
183Begin
184Declare @alter int
185If (Month(getdate())=Month(@gebdat)) and day(@gebdat)<day(getdate())
186Select @alter = Datediff(year,@gebdat,getdate())
187If (Month(getdate())<Month(@gebdat))
188Select @alter = Datediff(year,@gebdat,getdate())-1
189else
190Select @alter = Datediff(year,@gebdat,getdate())
191Return @alter
192End
193drop function AlterErmitteln
194
195Select MitID, Nachname, Vorname, Ort, Gebdat, Beruf, Telnr, dbo.AlterErmitteln(Gebdat) as 'Alter' from Mitarbeiter
196
197--3.2
198CREATE FUNCTION ErmittleAuslastung(@schwellwert float)
199Returns @table Table
200(MitID char(3),
201 Auslastung float
202 )
203 AS
204 Begin
205 INSERT INTO @table
206 Select m.MitID,Sum(z.Istanteil) from Mitarbeiter m, Zuordnung z
207 where m.MitID=z.MitID
208 Group by m.MitID
209 Having Sum(z.Istanteil)>@schwellwert
210 Return
211 End
212 drop function ErmittleAuslastung
213 Select * from ErmittleAuslastung(0.1)
214
215
216 --3. Teil II
217 --3.1
218 --1.1
219 --d)
220 Alter Table Mitarbeiter ADD CONSTRAINT DF_Mitarbeiter_Ort DEFAULT 'Dresden' FOR Ort
221 --e)
222 Alter Table Mitarbeiter ADD CONSTRAINT DF_Mitarbeiter_Beruf DEFAULT 'Dipl.-Ing' FOR Beruf
223
224 --3.2
225 --Da LeiterID nicht gleicher Datentyp wie MitID
226Select * From Mitarbeiter
227Select * From Projekt
228
229alter Table Projekt Add ChefID char(3)
230Update Projekt set ChefID=( Select a.MitID
231 from Mitarbeiter a
232 Where a.MitID=LeiterID)
233
234 --3.2.1
235 --f)
236 ALTER TABLE Mitarbeiter ADD CONSTRAINT CK_Mitarbeiter_Alter
237CHECK(dbo.AlterErmitteln(Gebdat)>=18 and dbo.AlterErmitteln(Gebdat)<=60)
238--g)
239ALTER TABLE Mitarbeiter ADD CONSTRAINT CK_Mitarbeiter_MitID
240CHECK( MitID like '[0-9][0-9][0-9]')
241
242Insert into Mitarbeiter(MitID, Nachname,Vorname,Gebdat,Telnr) Select '211','Peters', 'Hans','21-12-1996','01788885245'
243Select * From Mitarbeiter where Nachname ='Peters'
244--2.2
245ALTER TABLE Zuordnung ADD CONSTRAINT CK_Zuordnung_Istanteil
246CHECK( Istanteil <=1.0)
247
248--Teil III
249--3.1
250ALTER TABLE Projekt
251ADD CONSTRAINT co_forkey
252FOREIGN KEY(ChefID) REFERENCES Mitarbeiter(MitID);
253--h)
254--Insert into Mitarbeiter(MitID, Nachname,Vorname,Gebdat,Telnr) Select '211','Peters', 'Hans','21-12-1996','01788885245'
255Insert into Projekt Select 99, 'Malstunde','AlphaCentauri',null,2,'211','211'
256
257Update Mitarbeiter set MitID='212' Where MitID='211'--hat nicht geklappt Die UPDATE-Anweisung steht in Konflikt mit der REFERENCE-Einschränkung 'co_forkey'. Der Konflikt trat in der im15s73980-Datenbank, Tabelle 'dbo.Projekt', column 'ChefID' auf.
258--Die Anweisung wurde beendet.
259
260--i)
261Delete from Mitarbeiter where MitID='211'--geht nicht Die DELETE-Anweisung steht in Konflikt mit der REFERENCE-Einschränkung 'co_forkey'. Der Konflikt trat in der im15s73980-Datenbank, Tabelle 'dbo.Projekt', column 'ChefID' auf.
262--Die Anweisung wurde beendet.
263
264--j)
265Update Projekt set ChefID='119' where ProNr=99--hat geklappt
266Update Mitarbeiter set MitID='212' Where MitID='211'--hat geklappt
267Delete from Mitarbeiter where MitID='212'--hat geklappt
268--k)
269Update Projekt set LeiterID='220', ChefID='220' where ProNr=99--geht nicht Die UPDATE-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung 'co_forkey'. Der Konflikt trat in der im15s73980-Datenbank, Tabelle 'dbo.Mitarbeiter', column 'MitID' auf.
270--Die Anweisung wurde beendet.
271--l)
272Update Projekt set LeiterID=Null, ChefID=Null where ProNr=99--geht
273--Teil IV
274--4.1
275CREATE TRIGGER tr_Plananteil on Zuordnung
276FOR insert, update
277As
278If EXISTS
279 (Select Sum(z.Plananteil) From Zuordnung z
280 Group by z.MitID
281 Having Sum(z.Plananteil)>1.0)
282 Begin
283 print 'Projekttätigkeit größer als 1.0 unzulässig'
284 ROLLBACK TRANSACTION
285 End
286Drop Trigger tr_Plananteil
287--m)
288Update Zuordnung set Plananteil=Plananteil+0.3 where ProNr=31-- Ausgabe von
289--Projekttätigkeit größer als 1.0 unzulässig
290--Meldung 3609, Ebene 16, Status 1, Zeile 1
291--Die Transaktion endete mit dem Trigger. Der Batch wurde abgebrochen.
292Select * from Zuordnung where ProNr=31
293--n)
294Update Zuordnung set Plananteil=Plananteil+0.4 where MitID=105 --Ausgabe von
295-- Befehl wurde erfolgreich ausgeführt
296Select * from Zuordnung
297--4.2
298CREATE TRIGGER tr_Abschluss on Zuordnung
299for delete, update
300As
301Update Projekt set LeiterID=NULL where ProNr NOT in
302 (Select ProNr from Zuordnung)
303
304Drop Trigger tr_Abschluss
305
306--o
307Begin transaction
308Select * from Zuordnung where ProNr=36
309Delete from Zuordnung where MitID=106
310Select * from Zuordnung where ProNr=36
311Delete from Zuordnung where MitID=107
312Select * from Zuordnung where ProNr=36
313Delete from Zuordnung where MitID=130
314Select * from Zuordnung where ProNr=36
315Select * from Projekt where ProNR=36
316commit
317--p
318Begin transaction
319Select * from Zuordnung where ProNr=37
320Update Zuordnung set ProNr=35 where ProNr=37
321Select * from Zuordnung where ProNr=37
322Select * from Projekt where ProNr=37
323commit
324rollback
325
326--5
327--5.1
328Create Table Bprotokoll(
329MitID char(3),
330Nutzer char(16),
331Zeit datetime,
332Beruf_alt char(15),
333Beruf_neu char(15))
334
335Drop Table Bprotokoll
336
337create trigger tr_Protokoll on Mitarbeiter
338for update as if Update (Beruf)
339Begin
340Insert into Bprotokoll
341Select i.MitID, user_name(), getdate(),d.Beruf,i.Beruf
342From inserted i
343Join deleted d
344on i.MitID=d.MitID
345end
346
347drop trigger tr_Protokoll
348
349--5.3
350Begin transaction
351Select * From Mitarbeiter
352Update Mitarbeiter set Beruf = 'Dipl.-Math' where MitID=103
353Select * from Mitarbeiter
354Select * from Bprotokoll
355commit