YX2j33mC

1drop database if exists fom_exercise4 ;
2CREATE DATABASE  fom_exercise4;
3use fom_exercise4;
4
5
6/* drop table if exists noten;
7
8drop table if exists kurs;
9drop table if exists studentendatenbank;
10drop table if exists zimmer;
11
12drop table if exists semester; */
13
14
15create table if not exists zimmer(
16	znr int unsigned,
17	tel varchar(255),
18	primary key(znr)
19);
20
21create table if not exists studentendatenbank(
22	matr int unsigned,
23	vorname varchar(255),
24	name varchar(255),
25	znr int unsigned,
26	primary key (matr),
27
28	foreign key (znr) references zimmer(znr)
29);
30create table if not exists kurs(
31	kursnr varchar(4),
32	kursname varchar(255),
33	primary key (kursnr)
34);
35
36create table if not exists noten(
37	matr int unsigned,
38	kursnr varchar(4),
39	sem varchar(3),
40	note float,
41	primary key (matr, kursnr),
42	foreign key (matr) references studentendatenbank(matr),
43	foreign key (kursnr) references kurs(kursnr)
44);
45
46insert into zimmer values (120, 136);
47insert into zimmer values (117, 211);
48insert into studentendatenbank values (300215, 'Anton', 'Angeber', 120);
49insert into studentendatenbank values (300321, 'Frieda', 'Fröhlich', 117);
50insert into studentendatenbank values (300322, 'Bernd', 'Brot', 120);
51
52insert into kurs values ('MAT1', 'Mathe 1');
53insert into kurs values ('BWL1', 'BWL 1');
54insert into kurs values ('ITB', 'IT-Basics');
55insert into kurs values ('WP', 'WWW');
56insert into kurs values ('KT', 'Kreatives Töpfern');
57
58
59insert into noten values (300215, 'MAT1', 'W14', 1.7);
60insert into noten values (300215, 'BWL1', 'S15', 2.3);
61insert into noten values (300215, 'ITB', 'S15', 2);
62insert into noten values (300215, 'WP', 'W16', 1.7);
63insert into noten values (300321, 'KT', 'S15', 2);
64insert into noten values (300321, 'ITB', 'S16', 1.7);
65insert into noten values (300321, 'WP', 'W16', 2.3);
66
67insert into noten values (300322, 'MAT1', 'W12', 3.3);
68insert into noten values (300322, 'BWL1', 'S12', 2.3);
69
70-- 1.c.i
71select distinct vorname, name from studentendatenbank where znr = 120;
72
73-- 1.c.ii
74select distinct vorname, name from studentendatenbank where lower(name) like 'i' or lower(vorname) like 'i';
75
76-- 1.c.iii
77select count(*) from studentendatenbank s
78	right join kurs k on k.kursnr = k.kursnr
79	right join noten n on n.matr = s.matr and n.kursnr = k.kursnr
80	where n.note > 1.3;
81
82-- 1.c.iv
83select kursnr, avg(note) from noten
84	group by kursnr
85	order by kursnr;
86
87-- 1.c.v
88select sem, count(*) from noten group by sem;
89
90-- 1.c.vi
91insert into zimmer values (124, 224);
92insert into studentendatenbank values (300444, 'Hartumut', 'Hacker', 124);
93insert into noten values (300444, 'KT', 'S16', 4);
94
95-- update noten set note=note-1 where note > 2;
96-- update noten set note = IF(note >= 2.0, NOTE-1, 1) WHERE note >= 2.0;
97update noten set note = GREATEST(1, note-1) ;
98
99# Ausgabe der Ursprünglichen Tabelle
100select s.matr, concat(name, ' ', vorname) as name, z.znr, tel, k.kursnr, kursname, sem, note from studentendatenbank s
101	right join kurs k on k.kursnr = k.kursnr
102	right join noten n on n.matr = s.matr and n.kursnr = k.kursnr
103	right join zimmer z on z.znr = s.znr
104	order by matr;