· 5 years ago · Feb 02, 2020, 01:26 PM
1USE mydb;
2
3DROP TABLE IF EXISTS courses, teachers, students, coursestudents;
4
5CREATE TABLE courses (
6 id int(11) AUTO_INCREMENT PRIMARY KEY,
7 name varchar(50) NOT NULL,
8 code varchar(10) NOT NULL,
9 size int(11) NOT NULL,
10 start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
11 end TIMESTAMP DEFAULT CURRENT_TIMESTAMP
12);
13
14INSERT INTO courses (name, code, size) VALUES
15 ('Svenska 1', 'SV1', '50'),
16 ('Svenska 2', 'SV2', '50'),
17 ('Svenska 3', 'SV3', '50'),
18 ('Programmering 1', 'PR1', '100'),
19 ('Engelska 1', 'EN1', '100');
20
21CREATE TABLE teachers (
22 id int(11) AUTO_INCREMENT PRIMARY KEY,
23 courseid int NOT NULL,
24 name varchar(50) NOT NULL,
25 email varchar(50) NOT NULL,
26 code varchar(50) NOT NULL
27);
28
29INSERT INTO teachers (courseid, name, email, code) VALUES
30 ('1', 'Kalle Kvist', 'kalle@hotmail.com', 'KtK'),
31 ('2', 'Arne Anka', 'arnis@hemnet.se', 'AaA'),
32 ('3', 'Gunnar Gren', 'gogge@gmail.com', 'GnG'),
33 ('4', 'Krille Krokodil', 'krille@yahoo.se', 'KlK'),
34 ('5', 'Sune Sundin', 'a@b.com', 'SnS');
35
36CREATE TABLE students (
37 id int(11) AUTO_INCREMENT PRIMARY KEY,
38 name varchar(50) NOT NULL,
39 email varchar(50) NOT NULL,
40 class varchar(50) NOT NULL
41);
42
43INSERT INTO students (name, email, class) VALUES
44 ('Kalle Kvist', 'kalle@hotmail.com', 'TE17A'),
45 ('Sune Sundin', 'a@b.com', 'TE17A'),
46 ('Max Power', 'max@gmail.com', 'TE17A'),
47 ('Svenne Rubin', 'Sr@rubins.se', 'SA18C'),
48 ('Karl Pedal', 'kalp@cnn.com', 'SA18C'),
49 ('Sara Svensson', 'ss@twenty.com', 'SA18C'),
50 ('Helga Helmin', 'hh@yahoo.com', 'NA19F'),
51 ('Krille Krokodil', 'krille@yahoo.se', 'NA19F'),
52 ('Kalle Kvist', 'kalle@hotmail.com', 'NA19F'),
53 ('Gunnar Gren', 'gogge@gmail.com', 'NA19F');
54
55CREATE TABLE coursestudents (
56 id int(11) AUTO_INCREMENT PRIMARY KEY,
57 courses_id int(11) NOT NULL,
58 students_id int(11) NOT NULL
59);
60
61INSERT INTO coursestudents (courses_id, students_id) VALUES
62 (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7), (1,8), (1,9), (1,10),
63 (2,1), (2,2), (2,3), (2,4), (2,5), (2,6), (2,7), (2,8), (2,9),
64 (3,1), (3,2), (3,3), (3,4), (3,5), (3,6), (3,7), (3,8),
65 (4,1), (4,2), (4,3), (4,4), (4,5), (4,6), (4,7),
66 (5,1), (5,2), (5,3), (5,4), (5,5), (5,6),
67 (6,1), (6,2), (6,3), (6,4), (6,5),
68 (7,1), (7,2), (7,3), (7,4),
69 (8,1), (8,2), (8,3),
70 (9,1), (9,2),
71 (10,1);
72
73#SELECT teachers.courseid, teachers.name, teachers.code, teachers.email, courses.name FROM teachers, courses
74#WHERE teachers.courseid = courses.id;
75
76#SELECT students.id, students.name, students.email, students.class, courses.name, courses.code FROM students, courses, coursestudents
77#WHERE students.id = coursestudents.students_id AND courses.id = coursestudents.courses_id;
78
79SELECT courses.id, courses.name, courses.code, courses.size, courses.start, courses.end, students.name
80FROM courses, students, coursestudents
81WHERE students.id = coursestudents.students_id AND courses.id = coursestudents.courses_id AND students.name = 'Gunnar Gren';