RptZtyGM

1drop table student;
2create table student(
3NrLeg integer not null,
4Nume varchar(20),
5Initiala varchar(3),
6Prenume varchar(20),
7sex varchar(1),
8data_nastere datetime,
9stare_civila varchar(1),
10grupa integer);
11
12insert into student values (101, 'Popa','I','Aurel','M','06/08/1990','N',221);
13insert into student values (102, 'Popescu','G','ion','M','09/18/1991','N',221);
14insert into student values (103, 'Iota','V','Gabriel','M','11/20/1991','N',221);
15insert into student values (104, 'Toba','F','Andrei','M','02/05/1992','N',221);
16insert into student values (105, 'Zara','G','Nicusor','M','01/03/1990','N',221);
17insert into student values (106, 'Iliescu','D','Ana','F','10/09/1990','N',221);
18insert into student values (107, 'Trusca','M','Adriana','F','07/26/1991','N',221);
19insert into student values (108, 'Vancea','I','Nicoleta','F','02/08/1990','N',221);
20insert into student values (109, 'Coman','H','Viorica','F','04/03/1990','N',221);
21insert into student values (110, 'Stan','A','Nicoleta','F','06/08/1990','N',221);
22
23drop table catalog;
24create table catalog(
25NrLeg integer not null,
26Cod_Disciplina integer,
27Nota integer,
28Data date);
29
30insert into catalog values (101, 1, 7,'02/06/2014');
31insert into catalog values (101, 2, 5,'02/07/2014');
32insert into catalog values (101, 3, 4,'02/10/2014');
33insert into catalog values (101, 4, 8,'02/15/2014');
34insert into catalog values (101, 5, 9,'02/17/2014');
35
36insert into catalog values (102, 1, 6,'02/06/2014');
37insert into catalog values (102, 2, 3,'02/07/2014');
38insert into catalog values (102, 3, 7,'02/10/2014');
39insert into catalog values (102, 4, 9,'02/15/2014');
40
41
42insert into catalog values (103, 1, 10,'02/06/2014');
43insert into catalog values (103, 2, 7,'02/07/2014');
44insert into catalog values (103, 3, 6,'02/10/2014');
45
46insert into catalog values (104, 1, 5,'02/06/2014');
47insert into catalog values (104, 2, 5,'02/07/2014');
48insert into catalog values (104, 3, 5,'02/10/2014');
49
50insert into catalog values (105, 1, 6,'02/06/2014');
51insert into catalog values (105, 2, 6,'02/07/2014');
52insert into catalog values (105, 3, 6,'02/10/2014');
53insert into catalog values (105, 3, 6,'02/10/2014');
54
55
56drop table discipline;
57create table discipline(
58Cod_disciplina integer,
59Denumire varchar(20));
60
61insert into discipline values (1,'Baze de date');
62insert into discipline values (2,'Java');
63insert into discipline values (3,'Programare');
64insert into discipline values (4,'POO');
65insert into discipline values (5,'SO');
66
67-InnerJoin
68SELECT S.Nume,S.Prenume,C.cod_disciplina,C.Nota,C.Data
69FROM Student S INNER JOIN Catalog C
70ON S.NrLeg=C.NrLeg
71
72SELECT S.Nume,S.Prenume,C.cod_disciplina,C.Nota,C.Data
73FROM Student S, Catalog C
74WHERE S.NrLeg=C.NrLeg
75
76-Equijoin
77SELECT *
78FROM Student S, Catalog C
79WHERE S.NrLeg=C.NrLeg
80
81SELECT *
82FROM Student S INNER JOIN Catalog C
83ON S.NrLeg=C.NrLeg
84
85-Unirea naturala
86SELECT S.*,C.cod_disciplina,C.Nota,C.Data
87FROM Student S INNER JOIN Catalog C
88ON S.NrLeg=C.NrLeg
89
90SELECT S.*,C.cod_disciplina,C.Nota,C.Data
91FROM Student S, Catalog C
92WHERE S.NrLeg=C.NrLeg
93
94-Unire externa la stanga
95
96SELECT S.NrLeg, Nume, Prenume, Nota
97FROM Student S LEFT OUTER JOIN Catalog C
98ON S.Nrleg=C.NrLeg
99
100SELECT S.NrLeg, Nume, Prenume, Nota
101FROM Student S, Catalog C
102WHERE S.Nrleg=C.NrLeg
103
104SELECT S.NrLeg, Nume, Prenume, Nota
105FROM Student S LEFT OUTER JOIN Catalog C
106ON S.Nrleg=C.NrLeg AND C.Nrleg='101'
107
108SELECT S.NrLeg, Nume, Prenume, Nota
109FROM Student S, Catalog C
110WHERE S.Nrleg=C.NrLeg
111AND C.Nrleg='101'
112
113-Unirea externa la dreapta
114insert into catalog values (111, 3, 6,'02/10/2014');
115SELECT S.NrLeg, Nume, Prenume, Nota,C.NrLeg
116FROM Student S RIGHT OUTER JOIN Catalog C 
117ON S.Nrleg=C.NrLeg
118
119SELECT S.NrLeg, Nume, Prenume, Nota,C.NrLeg
120FROM Student S, Catalog C
121WHERE S.Nrleg=C.NrLeg
122
123SELECT S.NrLeg, Nume, Prenume, Nota
124FROM Student S RIGHT OUTER JOIN Catalog C
125ON S.Nrleg=C.NrLeg AND C.Nrleg='101'
126
127SELECT S.NrLeg, Nume, Prenume, Nota
128FROM Student S, Catalog C
129WHERE S.Nrleg=C.NrLeg AND C.Nrleg='101'
130
131insert into discipline values (6,'WEB');
132SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
133FROM Catalog C RIGHT OUTER JOIN Discipline D
134ON C.cod_disciplina=D.cod_disciplina
135
136SELECT C.cod_disciplina,Nota, D.cod_disciplina, Denumire
137FROM Catalog C, Discipline D
138WHERE C.cod_disciplina=D.cod_disciplina
139
140SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
141FROM Catalog C RIGHT OUTER JOIN Discipline D
142ON C.cod_disciplina=D.cod_disciplina AND C.cod_disciplina='2'
143
144SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
145FROM Catalog C, Discipline D
146WHERE C.cod_disciplina=D.cod_disciplina AND C.cod_disciplina='2'
147
148
149Unirea completa Full =??=
150SELECT S.NrLeg, Nume, Prenume, Nota
151FROM Student S FULL OUTER JOIN Catalog C
152ON S.Nrleg=C.NrLeg
153
154insert into catalog values (112, 7, 6,'02/10/2014');
155SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
156FROM Catalog C FULL OUTER JOIN Discipline D
157ON C.cod_disciplina=D.cod_disciplina
158
159-Autounirea (Self join)
160
161ALTER TABLE Student ADD NrLeg_sot varchar(5)
162
163UPDATE Student
164SET NrLeg_sot='108'
165WHERE NrLeg='102'
166
167UPDATE Student
168SET NrLeg_sot='103'
169WHERE NrLeg='106'
170
171SELECT *
172FROM Student S1, Student S2
173WHERE S1.NrLeg=S2.NrLeg_sot
174
175SELECT *
176FROM Student S1 INNER JOIN Student S2
177ON S1.NrLeg=S2.NrLeg_sot
178
179SELECT *
180FROM Student S1, Student S2
181WHERE S1.NrLeg=S2.NrLeg_sot AND S1.sex='M'
182
183SELECT *
184FROM Student S1 INNER JOIN Student S2
185ON S1.NrLeg=S2.NrLeg_sot AND S1.sex='M'
186
187SELECT *
188FROM Student S1, Student S2
189WHERE S1.NrLeg=S2.NrLeg_sot
190AND S1.Data_nastere>S2.Data_nastere
191
192SELECT *
193FROM Student S1, Student S2
194WHERE S1.NrLeg=S2.NrLeg_sot
195AND Datediff(year,S1.Data_nastere, getdate())>Datediff(year,S2.Data_nastere, getdate())
196
197SELECT *
198FROM Student S1, Student S2
199WHERE Datediff(year,S1.Data_nastere, getdate())>Datediff(year,S2.Data_nastere, getdate())
200AND S1.NrLeg<S2.NrLeg
201
202SELECT * FROM Student;
203
204
205Semiunirea (Semijoin)
206SELECT S.*
207FROM Student S, Catalog C
208WHERE S.NrLeg=C.NrLeg
209
210SELECT S.*
211FROM Student S INNER JOIN Catalog C
212ON S.NrLeg=C.NrLeg
213
214 Non-echiunire (non-Equijoin)
215SELECT *
216FROM Student S, Catalog C
217WHERE S.NrLeg>C.NrLeg
218
219SELECT *
220FROM Student S INNER JOIN Catalog C
221ON S.NrLeg>C.NrLeg
222
223-Lab 7
224
225CREATE VIEW Date_stud
226AS SELECT * FROM student
227
228SELECT *
229FROM Date_stud
230WHERE Nume like 'Po%'
231
232CREATE VIEW Date_stud_M
233AS
234SELECT * FROM student
235WHERE Sex in ('M', 'm')
236
237SELECT * FROM Date_stud_M
238
239UPDATE student set stare_civila='C' WHERE NrLeg=102
240SELECT *
241FROM Date_stud_M
242WHERE Stare_civila='C'
243
244
245CREATE VIEW Medii(NrLeg, Student, Grupa, Media,Nr_ex)
246AS
247 SELECT S.NrLeg, Nume+' '+Initiala+'.'+Prenume, Grupa, AVG(Nota), count(*)
248 FROM Student AS S,Catalog AS C
249 WHERE S.NrLeg=C.NrLeg
250 GROUP BY S.NrLeg, Nume+' '+Initiala+'.'+Prenume,Grupa
251
252SELECT *
253FROM Medii
254
255SELECT *
256FROM Medii
257WHERE Media>6.5
258
259CREATE VIEW Note_stud
260AS
261 SELECT s.NrLeg, Nume+' '+Initiala+'.'+Prenume student ,
262 Grupa, Denumire, Nota
263 FROM student AS s,Catalog AS c, Discipline AS d
264 WHERE s.NrLeg=c.NrLeg AND c.cod_disciplina=d.Cod_disciplina
265
266SELECT *
267FROM Note_stud
268
269SELECT *
270FROM Note_stud
271WHERE Denumire LIKE 'baze de date%'
272
273
274
275-Eroare - la crearea unei vederi nu puteti folosi in SELECT clauzele ORDER BY siUNION
276
277 CREATE VIEW Note_stud_err
278AS
279 SELECT S.NrLeg, Nume+' '+Initiala+'.'+Prenume Student,
280 Grupa, Denumire, Nota
281 FROM Student AS S,catalog AS c, Discipline AS d
282 WHERE S.NrLeg=c.NrLeg AND c.Cod_disciplina=d.Cod_disciplina
283 ORDER BY S.NrLeg
284
285CREATE VIEW Medii_Bursieri
286AS
287 SELECT s.NrLeg, Nume+' '+Initiala+'.'+Prenume Student, Grupa, AVG (Nota)
288Media
289 FROM Student AS s,Catalog AS C
290WHERE s.NrLeg=C.NrLeg
291GROUP BY s.NrLeg, Nume+' '+Initiala+'.'+Prenume,Grupa
292HAVING AVG(Nota) >6.5
293
294
295SELECT *
296FROM Medii_Bursieri
297
298CREATE VIEW Varste
299AS
300SELECT NrLeg, Nume, Prenume, Datediff(year,Data_nastere,getdate()) Varsta
301FROM Student
302
303SELECT *
304FROM Varste
305
306-Modificarea datelor folosind vederile Se folosesc comenzile UPDATE, INSERT si DELETE
307
308CREATE VIEW note_catalog
309AS
310SELECT *
311FROM catalog
312
313 SELECT *
314FROM note_catalog
315
316Scade 1 punct din toate notele din vederea Note_catalog
317UPDATE Note_catalog
318SET Nota=Nota-1
319
320 SELECT *
321FROM note_catalog
322
323DELETE FROM Note_catalog
324WHERE NrLeg='105'
325
326Scade un punct la toate notele studentilor cu NrLeg par
327UPDATE note_catalog
328SET Nota=Nota-1
329WHERE NrLeg%2=0
330
331SELECT *
332FROM note_catalog
333
334INSERT INTO note_catalog VALUES('120',8)
335Eroare pt ca in Note nu pot introduce NULL la Cod_disciplina
336
337CREATE VIEW Note2 AS
338SELECT NrLeg, Nota, Cod_Disciplina
339FROM note_catalog
340
341SELECT * FROM Note2
342INSERT INTO Note2 VALUES('120',8,'1')
343
344SELECT *
345FROM Note2
346
347-Lab8
348
349CREATE PROCEDURE determina_studenti
350 as
351SELECT * FROM student
352
353 EXEC determina_studenti
354
355
356 CREATE PROCEDURE inserare
357 @n integer,
358 @p varchar(20)
359  as
360 INSERT INTO discipline VALUES(@n,@p)
361
362
363 exec inserare 10, 'Calcul Stiintific'
364
365IF EXISTS (SELECT name
366 FROM sysobjects
367 WHERE name = 'medie' AND type = 'P')
368 DROP PROCEDURE medie
369
370CREATE PROCEDURE medie
371 @Gr INT
372AS
373 SELECT S.NrLeg, Nume, Prenume, Avg(C.Nota) Media
374 FROM Student S, Catalog C
375 WHERE S.NrLeg=C.NrLeg AND Grupa=@Gr
376 GROUP BY S.NrLeg, Nume, Prenume
377
378EXEC medie 221
379
380
381IF EXISTS (SELECT name FROM sysobjects
382 WHERE name =' medie_stud ' AND type = 'P')
383 DROP PROCEDURE medie_stud
384
385CREATE PROCEDURE medie_stud
386 @nume varchar(40),
387 @Medie float OUTPUT
388AS
389SELECT S.NrLeg, Nume, Prenume, Avg(C.Nota) Medie
390 FROM Student S, Catalog C
391 WHERE S.NrLeg=C.NrLeg and Nume=@nume
392 GROUP BY S.NrLeg, Nume, Prenume
393
394EXEC Medie 'Ionescu'
395
396DROP PROCEDURE medie_stud;
397
398CREATE PROCEDURE medie_stud
399@@numest varchar(40) = '%',
400@@medie float OUTPUT
401AS
402SELECT Nume
403FROM Student
404WHERE Nume LIKE @@numest
405SELECT @@medie=Avg(C.Nota)
406FROM Student S, Catalog C
407WHERE S.NrLeg=C.NrLeg and Nume like @@numest
408
409DECLARE @@med float
410EXECUTE medie_stud '%', @@med OUTPUT
411SELECT @@med
412PRINT @@med --pentru afisare mod text
413
414
415
416DECLARE @@med float
417EXECUTE medie_stud 'Iota', @@med OUTPUT
418SELECT @@med
419PRINT @@med --pentru afisare mod text
420
421
422
423DECLARE @@med float
424EXECUTE medie_stud 'I%', @@med OUTPUT
425IF @@med < 8
426BEGIN
427PRINT ' '
428PRINT 'Media mai mica ca 8'
429END
430ELSE
431BEGIN
432PRINT ' '
433PRINT 'Media mai mare ca 8'
434SELECT 'Aceasta este ' + RTRIM(CAST(@@med AS varchar(20)))
435END