Beispiel Studenten Tabelle - Drei Beispiel Tabellen zum lernen von verschiedenen SQL-Statement

Frage

Ich möchte verschiedene SQL-Statment zum üben erstellen. Woher erhalte ich Muster Tabellen?

Antwort

Im folgenden werden drei Tabellen vorgestellt.Das unten stehende SQL-Script kann in einem SQL-Command ausgeführt werden. Abschließend die drei Tabellen mit folgender Relation in der Datenbank wieder zufinden.

Ex­em­p­li­fi­ka­ti­on

Vorgehen

Erläuterung

Wie auf dem Bild zu sehen ist, stehen die Tabellen mit einander wie folgt in Relation.

1. Ein Student kann keine oder viele Module belegen.
2. Ein Modul kann von keinen oder vielen Studenten besucht werden.
3. Bei jeder Modul Belegung bzw. Studenten Besuch zum Modul muss eine Note vergeben werden.

Bild

Demonstration

Download

Quellcode

Für diesen Anwendungsfall wurde das folgende Quellcode verwendet.

-- drop table drop table blog_students; drop table blog_modules; drop table blog_marks; CREATE TABLE blog_students ( stud_no number primary key , stud_surname VARCHAR2 (200 CHAR) NOT NULL , stud_forename VARCHAR2 (200 CHAR) NOT NULL ) ; CREATE TABLE blog_modules ( modu_code VARCHAR2 (200 CHAR) primary key , modu_name VARCHAR2 (200 CHAR) NOT NULL ) ; CREATE TABLE blog_marks ( mark_stud_no number not null , mark_modu_code VARCHAR2 (200 CHAR) not null , mark_mark number not null , constraint stud_modu_pk primary key (mark_stud_no,mark_modu_code) ) ; -- Insert blog_students insert into blog_students values ('20060101','Dickens','Charles'); insert into blog_students values ('20060102','ApGwilym','Dafydd'); insert into blog_students values ('20060103','Zola','Emile'); insert into blog_students values ('20060104','Mann','Thomas'); insert into blog_students values ('20060105','Stevenson','Robert'); -- Insert blog_modules insert into blog_modules values ('CM0001', 'Databases'); insert into blog_modules values ('CM0002', 'Programming Languages'); insert into blog_modules values ('CM0003', 'Operating Systems'); insert into blog_modules values ('CM0004', 'Graphics'); -- Insert blog_marks insert into blog_marks values ('20060101', 'CM0001', 80); insert into blog_marks values ('20060101', 'CM0002', 65); insert into blog_marks values ('20060101', 'CM0003', 50); insert into blog_marks values ('20060102', 'CM0001', 75); insert into blog_marks values ('20060102', 'CM0003', 45); insert into blog_marks values ('20060102', 'CM0004', 70); insert into blog_marks values ('20060103', 'CM0001', 60); insert into blog_marks values ('20060103', 'CM0002', 75); insert into blog_marks values ('20060103', 'CM0004', 60); insert into blog_marks values ('20060104', 'CM0001', 55); insert into blog_marks values ('20060104', 'CM0002', 40); insert into blog_marks values ('20060104', 'CM0003', 45); insert into blog_marks values ('20060105', 'CM0001', 55); insert into blog_marks values ('20060105', 'CM0002', 50); insert into blog_marks values ('20060105', 'CM0004', 65); commit;

​x

-- drop table

32

insert into blog_students values ('20060102','ApGwilym','Dafydd');

33

insert into blog_students values ('20060103','Zola','Emile');

34

insert into blog_students values ('20060104','Mann','Thomas');

35

insert into blog_students values ('20060105','Stevenson','Robert');

36

​

37

-- Insert blog_modules

38

insert into blog_modules values ('CM0001', 'Databases');

39

insert into blog_modules values ('CM0002', 'Programming Languages');

40

insert into blog_modules values ('CM0003', 'Operating Systems');

41

insert into blog_modules values ('CM0004', 'Graphics');

42

​

43

-- Insert blog_marks

44

insert into blog_marks values ('20060101', 'CM0001', 80);

45

insert into blog_marks values ('20060101', 'CM0002', 65);

46

insert into blog_marks values ('20060101', 'CM0003', 50);

47

insert into blog_marks values ('20060102', 'CM0001', 75);

48

insert into blog_marks values ('20060102', 'CM0003', 45);

49

insert into blog_marks values ('20060102', 'CM0004', 70);

50

insert into blog_marks values ('20060103', 'CM0001', 60);

51

insert into blog_marks values ('20060103', 'CM0002', 75);

52

insert into blog_marks values ('20060103', 'CM0004', 60);

53

insert into blog_marks values ('20060104', 'CM0001', 55);

54

insert into blog_marks values ('20060104', 'CM0002', 40);

55

insert into blog_marks values ('20060104', 'CM0003', 45);

56

insert into blog_marks values ('20060105', 'CM0001', 55);

57

insert into blog_marks values ('20060105', 'CM0002', 50);

58

insert into blog_marks values ('20060105', 'CM0004', 65);

59

commit;

Für diesen Anwendungsfall wurde das folgende Quellcode verwendet.

-- drop table drop table blog_students; drop table blog_modules; drop table blog_marks; CREATE TABLE blog_students ( stud_no number primary key , stud_surname VARCHAR2 (200 CHAR) NOT NULL , stud_forename VARCHAR2 (200 CHAR) NOT NULL ) ; CREATE TABLE blog_modules ( modu_code VARCHAR2 (200 CHAR) primary key , modu_name VARCHAR2 (200 CHAR) NOT NULL ) ; CREATE TABLE blog_marks ( mark_stud_no number not null , mark_modu_code VARCHAR2 (200 CHAR) not null , mark_mark number not null , constraint stud_modu_pk primary key (mark_stud_no,mark_modu_code) ) ; -- Insert blog_students insert into blog_students values ('20060101','Dickens','Charles'); insert into blog_students values ('20060102','ApGwilym','Dafydd'); insert into blog_students values ('20060103','Zola','Emile'); insert into blog_students values ('20060104','Mann','Thomas'); insert into blog_students values ('20060105','Stevenson','Robert'); -- Insert blog_modules insert into blog_modules values ('CM0001', 'Databases'); insert into blog_modules values ('CM0002', 'Programming Languages'); insert into blog_modules values ('CM0003', 'Operating Systems'); insert into blog_modules values ('CM0004', 'Graphics'); -- Insert blog_marks insert into blog_marks values ('20060101', 'CM0001', 80); insert into blog_marks values ('20060101', 'CM0002', 65); insert into blog_marks values ('20060101', 'CM0003', 50); insert into blog_marks values ('20060102', 'CM0001', 75); insert into blog_marks values ('20060102', 'CM0003', 45); insert into blog_marks values ('20060102', 'CM0004', 70); insert into blog_marks values ('20060103', 'CM0001', 60); insert into blog_marks values ('20060103', 'CM0002', 75); insert into blog_marks values ('20060103', 'CM0004', 60); insert into blog_marks values ('20060104', 'CM0001', 55); insert into blog_marks values ('20060104', 'CM0002', 40); insert into blog_marks values ('20060104', 'CM0003', 45); insert into blog_marks values ('20060105', 'CM0001', 55); insert into blog_marks values ('20060105', 'CM0002', 50); insert into blog_marks values ('20060105', 'CM0004', 65); commit;

Weitere Links

Nützliche Links

https://docs.cs.cf.ac.uk/notes/oracle-sample-tables/

Demo Anwendung & Blog

APEX Demo Anwendung

https://apex.oracle.com/pls/apex/f?p=74153:600

Blog Link

https://apexnote.blogspot.com/2018/09/muster-tabellen-fur-verschiedene.html

 

 

Erstellt am:

08-SEP-2018

Zuletzt aktualisiert am:

17-NOV-2018