utMLWn9d

1DROP TABLE IF EXISTS studenti;
2DROP TABLE IF EXISTS cursuri;
3DROP TABLE IF EXISTS note;
4DROP TABLE IF EXISTS profesori;
5
6DROP TABLE IF EXISTS didactic;
7
8CREATE TABLE studenti(
9  nr_matricol CHAR(4) NOT NULL PRIMARY KEY,
10  nume VARCHAR(10),
11  prenume VARCHAR(10),
12  an INT(1),
13  grupa CHAR(2),
14  bursa FLOAT(6,2),
15  data_nastere DATE
16  );
17
18
19
20CREATE TABLE cursuri(
21  id_curs CHAR(2) NOT NULL PRIMARY KEY,
22  titlu_curs VARCHAR(15),
23  an INT(1),
24  semestru INT(1),
25  credite INT(2)
26  );
27
28
29CREATE TABLE note(
30  nr_matricol CHAR(4),
31  id_curs CHAR(2),
32  valoare INT(2),
33  data_notare DATE,
34  FOREIGN KEY (id_curs) REFERENCES cursuri(id_curs),
35  FOREIGN KEY (nr_matricol) REFERENCES studenti(nr_matricol)
36
37  );
38
39
40CREATE TABLE profesori(
41  id_prof CHAR(4) PRIMARY KEY,
42  nume CHAR(10),
43  prenume CHAR(10),
44  grad_didactic VARCHAR(5)
45  );
46
47
48CREATE TABLE didactic(
49  id_prof CHAR(4),
50  id_curs CHAR(4),
51  FOREIGN KEY (id_prof) REFERENCES profesori(id_prof),
52  FOREIGN KEY (id_curs) REFERENCES cursuri(id_curs)
53
54  );
55
56
57-- ACCEPT  dmy  PROMPT "Press [Enter] to continue ...";
58
59-- anul 3 de studiu 
60INSERT INTO studenti VALUES ('111', 'Popescu', 'Bogdan',3, 'A2',NULL, '1995-02-17');
61INSERT INTO studenti VALUES ('112', 'Prelipcean', 'Radu',3, 'A2',NULL, '1995-05-26');
62INSERT INTO studenti VALUES ('113', 'Antonie', 'Ioana',3, 'A2',450, '1995-01-03');
63INSERT INTO studenti VALUES ('114', 'Arhire', 'Raluca',3, 'A4',NULL, '1995-12-26');
64INSERT INTO studenti VALUES ('115', 'Panaite', 'Alexandru',3, 'B3',NULL, '1995-04-13');
65
66-- anul 2 de studiu
67INSERT INTO studenti VALUES ('116', 'Bodnar', 'Ioana',2, 'A1',NULL, '1996-08-26');
68INSERT INTO studenti VALUES ('117', 'Archip', 'Andrada',2, 'A1',350, '1996-04-03');
69INSERT INTO studenti VALUES ('118', 'Ciobotariu', 'Ciprian',2, 'A1',350, '1996-04-03');
70INSERT INTO studenti VALUES ('119', 'Bodnar', 'Ioana',2, 'B2',NULL, '1996-06-10');
71
72-- anul 1 de studiu
73INSERT INTO studenti VALUES ('120', 'Pintescu', 'Andrei',1, 'B1',250, '1997-08-26');
74INSERT INTO studenti VALUES ('121', 'Arhire', 'Alexandra',1, 'B1',NULL, '1997-07-02');
75INSERT INTO studenti VALUES ('122', 'Cobzaru', 'George',1, 'B1',350, '1997-04-29');
76INSERT INTO studenti VALUES ('123', 'Bucur', 'Andreea',1, 'B2',NULL, '1997-05-10');
77
78-- populare tabela cursuri - cate 3 pt fiecare an
79INSERT INTO cursuri VALUES ('21', 'Logica', 1, 1, 5);
80INSERT INTO cursuri VALUES ('22', 'Matematica', 1, 1, 4);
81INSERT INTO cursuri VALUES ('23', 'OOP', 1, 2, 5);
82INSERT INTO cursuri VALUES ('24', 'BD', 2, 1, 8);
83INSERT INTO cursuri VALUES ('25', 'Java', 2, 2, 5);
84INSERT INTO cursuri VALUES ('26', 'Tehnologii Web', 2, 2, 5);
85INSERT INTO cursuri VALUES ('27', 'Sec. Info.', 3, 1, 5);
86INSERT INTO cursuri VALUES ('28', 'DSFUM', 3, 1, 6);
87INSERT INTO cursuri VALUES ('29', 'Limbaje formale', 2, 1, 5);
88-- inca una din anu 3 :D
89
90
91-- populare tabela profesori
92INSERT INTO profesori VALUES ('1', 'Masalagiu', 'Cristian', 'Prof');
93INSERT INTO profesori VALUES ('2', 'Buraga', 'Sabin', 'Conf');
94INSERT INTO profesori VALUES ('3', 'Lucanu', 'Dorel', 'Prof');
95INSERT INTO profesori VALUES ('4', 'Tiplea', 'Laurentiu', 'Prof');
96INSERT INTO profesori VALUES ('5', 'Iacob', 'Florin', 'Lect');
97INSERT INTO profesori VALUES ('6', 'Breaban', 'Mihaela', 'Conf');
98INSERT INTO profesori VALUES ('7', 'Varlan', 'Cosmin', 'Lect');
99INSERT INTO profesori VALUES ('8', 'Frasinaru', 'Cristian', 'Prof');
100INSERT INTO profesori VALUES ('9', 'Ciobaca', 'Stefan', 'Conf');
101INSERT INTO profesori VALUES ('10', 'Captarencu', 'Oana', 'Lect');
102INSERT INTO profesori VALUES ('11', 'Moruz', 'Alexandru', 'Lect');
103
104
105-- populare tabela didactic
106INSERT INTO didactic VALUES ('1','21');
107INSERT INTO didactic VALUES ('9','21');
108INSERT INTO didactic VALUES ('5','22');
109INSERT INTO didactic VALUES ('3','23');
110INSERT INTO didactic VALUES ('6','24');
111INSERT INTO didactic VALUES ('7','24');
112INSERT INTO didactic VALUES ('8','25');
113INSERT INTO didactic VALUES ('2','26');
114INSERT INTO didactic VALUES ('4','27');
115INSERT INTO didactic VALUES ('7','28');
116
117
118-- populare tabel note - studentii din anul 3 au toate examenele date, cei din anul 2 doar pe cele din anul 1, cei din 1 - nimic
119INSERT INTO note VALUES ('111', '21',  8, '2014-02-17');
120INSERT INTO note VALUES ('111', '22',  9, '2014-02-19');
121INSERT INTO note VALUES ('111', '23', 10, '2014-06-24');
122INSERT INTO note VALUES ('111', '24',  9, '2015-02-17');
123INSERT INTO note VALUES ('111', '25',  7, '2015-06-20');
124INSERT INTO note VALUES ('111', '26',  8, '2015-06-21');
125
126INSERT INTO note VALUES ('112', '21',  7, '2014-02-25');
127INSERT INTO note VALUES ('112', '22',  6, '2014-02-19');
128INSERT INTO note VALUES ('112', '23',  5, '2014-06-24');
129INSERT INTO note VALUES ('112', '24',  6, '2015-02-17');
130INSERT INTO note VALUES ('112', '25',  7, '2015-06-20');
131INSERT INTO note VALUES ('112', '26',  4, '2015-06-21');
132
133INSERT INTO note VALUES ('113', '21',  9, '2014-02-17');
134INSERT INTO note VALUES ('113', '22',  9, '2014-02-19');
135INSERT INTO note VALUES ('113', '23',  7, '2014-06-24');
136INSERT INTO note VALUES ('113', '24', 10, '2015-02-17');
137INSERT INTO note VALUES ('113', '25',  4, '2015-06-20');
138INSERT INTO note VALUES ('113', '26',  7, '2015-06-21');
139
140INSERT INTO note VALUES ('114', '21',  6, '2014-02-17');
141INSERT INTO note VALUES ('114', '22',  9, '2014-02-19');
142INSERT INTO note VALUES ('114', '23', 10, '2014-06-24');
143INSERT INTO note VALUES ('114', '24',  4, '2015-02-17');
144INSERT INTO note VALUES ('114', '25',  5, '2015-06-20');
145INSERT INTO note VALUES ('114', '26',  4, '2015-06-21');
146
147INSERT INTO note VALUES ('115', '21', 10, '2014-02-17');
148INSERT INTO note VALUES ('115', '22',  7, '2014-02-19');
149INSERT INTO note VALUES ('115', '23', 10, '2014-06-24');
150INSERT INTO note VALUES ('115', '24', 10, '2015-02-17');
151INSERT INTO note VALUES ('115', '25',  8, '2015-06-20');
152INSERT INTO note VALUES ('115', '26',  9, '2015-06-21');
153
154
155INSERT INTO note VALUES ('116', '21', 10, '2015-02-18');
156INSERT INTO note VALUES ('116', '22', 10, '2015-02-20');
157INSERT INTO note VALUES ('116', '23',  9, '2015-06-21');
158
159INSERT INTO note VALUES ('117', '21',  7, '2015-02-18');
160INSERT INTO note VALUES ('117', '22',  6, '2015-02-20');
161INSERT INTO note VALUES ('117', '23',  4, '2015-06-25');
162
163INSERT INTO note VALUES ('118', '21',  7, '2015-02-22');
164INSERT INTO note VALUES ('118', '22',  7, '2015-02-24');
165INSERT INTO note VALUES ('118', '23',  7, '2015-06-21');
166
167INSERT INTO note VALUES ('119', '21',  7, '2015-02-18');
168INSERT INTO note VALUES ('119', '22',  8, '2015-02-20');
169INSERT INTO note VALUES ('119', '23',  9, '2015-06-21');
170
171
172
173INSERT INTO profesori VALUES('20', 'PASCARIU', 'GEORGIANA', null);
174INSERT INTO profesori VALUES('21', 'LAZAR', 'LUCIAN', null);
175INSERT INTO profesori VALUES('22', 'Kristo', 'ROBERT', null);
176INSERT INTO profesori VALUES('23', 'Nastasa', 'Laura', null);
177INSERT INTO profesori VALUES('24', 'PASAT', 'Tiberiu', null);
178
179COMMIT;
180
181
182
183
184
185
186
187
188