uPr2vFK0

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
41insert into catalog values (103, 1, 10,'02/06/2014');
42insert into catalog values (103, 2, 7,'02/07/2014');
43insert into catalog values (103, 3, 6,'02/10/2014');
44
45insert into catalog values (104, 1, 5,'02/06/2014');
46insert into catalog values (104, 2, 5,'02/07/2014');
47insert into catalog values (104, 3, 5,'02/10/2014');
48
49insert into catalog values (105, 1, 6,'02/06/2014');
50insert into catalog values (105, 2, 6,'02/07/2014');
51insert into catalog values (105, 3, 6,'02/10/2014');
52insert into catalog values (105, 3, 6,'02/10/2014');
53
54
55drop table discipline;
56create table discipline(
57Cod_disciplina integer,
58Denumire varchar(20));
59
60insert into discipline values (1,'Baze de date');
61insert into discipline values (2,'Java');
62insert into discipline values (3,'Programare');
63insert into discipline values (4,'POO');
64insert into discipline values (5,'SO');
65
66--InnerJoin
67SELECT S.Nume,S.Prenume,C.cod_disciplina,C.Nota,C.Data
68FROM Student S INNER JOIN Catalog C
69ON S.NrLeg=C.NrLeg
70
71SELECT S.Nume,S.Prenume,C.cod_disciplina,C.Nota,C.Data
72FROM Student S, Catalog C
73WHERE S.NrLeg=C.NrLeg
74
75--Equijoin
76SELECT *
77FROM Student S, Catalog C
78WHERE S.NrLeg=C.NrLeg
79
80SELECT *
81FROM Student S INNER JOIN Catalog C
82ON S.NrLeg=C.NrLeg
83
84--Unirea naturala
85SELECT S.*,C.cod_disciplina,C.Nota,C.Data
86FROM Student S INNER JOIN Catalog C
87ON S.NrLeg=C.NrLeg
88
89SELECT S.*,C.cod_disciplina,C.Nota,C.Data
90FROM Student S, Catalog C
91WHERE S.NrLeg=C.NrLeg
92
93--Unire externa la stanga
94
95SELECT S.NrLeg, Nume, Prenume, Nota
96FROM Student S LEFT OUTER JOIN Catalog C
97ON S.Nrleg=C.NrLeg
98
99SELECT S.NrLeg, Nume, Prenume, Nota
100FROM Student S, Catalog C
101WHERE S.Nrleg=C.NrLeg
102
103SELECT S.NrLeg, Nume, Prenume, Nota
104FROM Student S LEFT OUTER JOIN Catalog C
105ON S.Nrleg=C.NrLeg AND C.Nrleg='101'
106
107SELECT S.NrLeg, Nume, Prenume, Nota
108FROM Student S, Catalog C
109WHERE S.Nrleg=C.NrLeg
110AND C.Nrleg='101'
111
112--Unirea externa la dreapta
113insert into catalog values (111, 3, 6,'02/10/2014');
114SELECT S.NrLeg, Nume, Prenume, Nota,C.NrLeg
115FROM Student S RIGHT OUTER JOIN Catalog C 
116ON S.Nrleg=C.NrLeg
117
118SELECT S.NrLeg, Nume, Prenume, Nota,C.NrLeg
119FROM Student S, Catalog C
120WHERE S.Nrleg=C.NrLeg
121
122SELECT S.NrLeg, Nume, Prenume, Nota
123FROM Student S RIGHT OUTER JOIN Catalog C
124ON S.Nrleg=C.NrLeg AND C.Nrleg='101'
125
126SELECT S.NrLeg, Nume, Prenume, Nota
127FROM Student S, Catalog C
128WHERE S.Nrleg=C.NrLeg AND C.Nrleg='101'
129
130insert into discipline values (6,'WEB');
131SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
132FROM Catalog C RIGHT OUTER JOIN Discipline D
133ON C.cod_disciplina=D.cod_disciplina
134
135SELECT C.cod_disciplina,Nota, D.cod_disciplina, Denumire
136FROM Catalog C, Discipline D
137WHERE C.cod_disciplina=D.cod_disciplina
138
139SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
140FROM Catalog C RIGHT OUTER JOIN Discipline D
141ON C.cod_disciplina=D.cod_disciplina AND C.cod_disciplina='2'
142
143SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
144FROM Catalog C, Discipline D
145WHERE C.cod_disciplina=D.cod_disciplina AND C.cod_disciplina='2'
146
147
148--Unirea completa Full =??=
149SELECT S.NrLeg, Nume, Prenume, Nota
150FROM Student S FULL OUTER JOIN Catalog C
151ON S.Nrleg=C.NrLeg
152
153insert into catalog values (112, 7, 6,'02/10/2014');
154SELECT C.cod_disciplina,Nota, D.cod_disciplina,Denumire
155FROM Catalog C FULL OUTER JOIN Discipline D
156ON C.cod_disciplina=D.cod_disciplina
157
158--Autounirea (Self join)
159
160ALTER TABLE Student ADD NrLeg_sot varchar(5)
161
162UPDATE Student
163SET NrLeg_sot='108'
164WHERE NrLeg='102'
165
166UPDATE Student
167SET NrLeg_sot='103'
168WHERE NrLeg='106'
169
170SELECT *
171FROM Student S1, Student S2
172WHERE S1.NrLeg=S2.NrLeg_sot
173
174SELECT *
175FROM Student S1 INNER JOIN Student S2
176ON S1.NrLeg=S2.NrLeg_sot
177
178SELECT *
179FROM Student S1, Student S2
180WHERE S1.NrLeg=S2.NrLeg_sot AND S1.sex='M'
181
182SELECT *
183FROM Student S1 INNER JOIN Student S2
184ON S1.NrLeg=S2.NrLeg_sot AND S1.sex='M'
185
186SELECT *
187FROM Student S1, Student S2
188WHERE S1.NrLeg=S2.NrLeg_sot
189AND S1.Data_nastere>S2.Data_nastere
190
191SELECT *
192FROM Student S1, Student S2
193WHERE S1.NrLeg=S2.NrLeg_sot
194AND Datediff(year,S1.Data_nastere, getdate())>Datediff(year,S2.Data_nastere, getdate())
195
196SELECT *
197FROM Student S1, Student S2
198WHERE Datediff(year,S1.Data_nastere, getdate())>Datediff(year,S2.Data_nastere, getdate())
199AND S1.NrLeg<S2.NrLeg
200
201SELECT * FROM Student;
202
203
204--Semiunirea (Semijoin)
205SELECT S.*
206FROM Student S, Catalog C
207WHERE S.NrLeg=C.NrLeg
208
209SELECT S.*
210FROM Student S INNER JOIN Catalog C
211ON S.NrLeg=C.NrLeg
212
213--Non-echiunire (non-Equijoin)
214SELECT *
215FROM Student S, Catalog C
216WHERE S.NrLeg>C.NrLeg
217
218SELECT *
219FROM Student S INNER JOIN Catalog C
220ON S.NrLeg>C.NrLeg
221
222
223
224--Lab 7
225
226CREATE VIEW Date_stud
227AS SELECT * FROM student
228
229SELECT *
230FROM Date_stud
231WHERE Nume like 'Po%'
232
233CREATE VIEW Date_stud_M
234AS
235SELECT * FROM student
236WHERE Sex in ('M', 'm')
237
238SELECT * FROM Date_stud_M
239
240UPDATE student set stare_civila='C' WHERE NrLeg=102
241SELECT *
242FROM Date_stud_M
243WHERE Stare_civila='C'
244
245
246CREATE VIEW Medii(NrLeg, Student, Grupa, Media,Nr_ex)
247AS
248 SELECT S.NrLeg, Nume+' '+Initiala+'.'+Prenume, Grupa, AVG(Nota), count(*)
249 FROM Student AS S,Catalog AS C
250 WHERE S.NrLeg=C.NrLeg
251 GROUP BY S.NrLeg, Nume+' '+Initiala+'.'+Prenume,Grupa
252
253SELECT *
254FROM Medii
255
256SELECT *
257FROM Medii
258WHERE Media>6.5
259
260CREATE VIEW Note_stud
261AS
262 SELECT s.NrLeg, Nume+' '+Initiala+'.'+Prenume student ,
263 Grupa, Denumire, Nota
264 FROM student AS s,Catalog AS c, Discipline AS d
265 WHERE s.NrLeg=c.NrLeg AND c.cod_disciplina=d.Cod_disciplina
266
267SELECT *
268FROM Note_stud
269
270SELECT *
271FROM Note_stud
272WHERE Denumire LIKE 'baze de date%'
273
274
275--Eroare - la crearea unei vederi nu puteti folosi in SELECT clauzele ORDER BY si UNION
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
316--Scade 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
326--Scade 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)
335--Eroare pt ca in Note nu pot introduce NULL la Cod_disciplina(Cod_disciplina este cheie)
336
337CREATE VIEW Note2 AS
338SELECT NrLeg, Nota, Cod_Disciplina
339FROM note_catalog
340
341SELECT * FROM Note2
342
343INSERT INTO Note2 VALUES('120',8,'1')
344
345SELECT *
346FROM Note2
347
348--Lab8
349
350CREATE PROCEDURE determina_studenti
351 as
352SELECT * FROM student
353
354 EXEC determina_studenti
355
356
357 CREATE PROCEDURE inserare
358 @n integer,
359 @p varchar(20)
360  as
361 INSERT INTO discipline VALUES(@n,@p)
362
363
364 exec inserare 10, 'Calcul Stiintific'
365
366IF EXISTS (SELECT name
367 FROM sysobjects
368 WHERE name = 'medie' AND type = 'P')
369 DROP PROCEDURE medie
370
371CREATE PROCEDURE medie
372 @Gr INT
373AS
374 SELECT S.NrLeg, Nume, Prenume, Avg(C.Nota) Media
375 FROM Student S, Catalog C
376 WHERE S.NrLeg=C.NrLeg AND Grupa=@Gr
377 GROUP BY S.NrLeg, Nume, Prenume
378
379EXEC medie 221
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_stud 'Popescu'
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
409--Inlocuim % cu numele studentului caruia vrem sa-i aflam media
410DECLARE @@med float
411EXECUTE medie_stud '%', @@med OUTPUT
412SELECT @@med
413PRINT @@med --pentru afiÅŸare mod text
414
415
416
417DECLARE @@med float
418EXECUTE medie_stud 'Iota', @@med OUTPUT
419SELECT @@med
420PRINT @@med --pentru afiÅŸare mod text
421
422
423
424DECLARE @@med float
425EXECUTE medie_stud 'I%', @@med OUTPUT
426IF @@med < 8
427BEGIN
428PRINT ' '
429PRINT 'Media mai mică ca 8'
430END
431ELSE
432BEGIN
433PRINT ' '
434PRINT 'Media mai mare ca 8'
435SELECT 'Aceasta este ' + RTRIM(CAST(@@med AS varchar(20)))
436END