qdZTtamB

1drop database if exists sql_workshop;
2
3create database sql_workshop;
4use sql_workshop;
5
6#creating tables
7create table student
8(sid int not null primary key,
9 sname varchar(15) not null);
10
11create table course
12(cid char(8) not null primary key,
13 cname varchar(15) not null);
14
15create table registration
16(sid int not null,
17 cid char(8) not null,
18 semester char(6) not null,
19 constraint registration_pk primary key(sid, cid),
20 constraint registration_fk1 foreign key(sid) references student(sid),
21 constraint registration_fk2 foreign key(cid) references course(cid));
22
23create table faculty
24(fid int not null primary key,
25 fname varchar(15) not null);
26
27create table qualification
28(fid int not null,
29 cid char(8) not null,
30 date_qualified date not null,
31 constraint qualification_pk primary key(fid, cid),
32 constraint qualification_fk1 foreign key(fid) references faculty(fid),
33 constraint qualification_fk2 foreign key(cid) references course(cid));
34
35create table assessment
36(aid int not null,
37 aname varchar(15) not null, 
38 weight decimal(3,2) not null, 
39 constraint assessment_pk primary key(aid));
40
41create table performance
42(sid int not null,
43 cid char(8) not null,
44 aid int not null, 
45 mark int not null,
46 constraint performance_pk primary key(sid, cid, aid),
47 constraint performance_fk1 foreign key(sid, cid) references registration(sid,cid),
48 constraint performance_fk2 foreign key(aid) references assessment(aid));
49
50create table room
51(rid int not null primary key,
52 type varchar(20) not null,
53 capacity int not null);
54
55
56#inserting data
57insert into student values
58(38214, 'Ama'),
59(54907, 'Bob'),
60(66324, 'Clark'),
61(70542, 'Dave'),
62(15789, 'Eme');
63
64insert into course values
65('ISM 3113', 'Syst Analysis'),
66('ISM 3112', 'Syst Design'),
67('ISM 4212', 'Database'),
68('ISM 4930', 'Networking');
69
70insert into registration values
71(38214, 'ISM 4212', 'I-2001'),
72(54907, 'ISM 4212', 'I-2001'),
73(54907, 'ISM 4930', 'I-2001'),
74(54907, 'ISM 3112', 'I-2001'),
75(54907, 'ISM 3113', 'I-2001'),
76(66324, 'ISM 3113', 'I-2002'),
77(66324, 'ISM 3112', 'I-2001'),
78(70542, 'ISM 3112', 'I-2001'),
79(70542, 'ISM 4212', 'I-2002');
80
81insert into faculty values
82(2143, 'Ama'),
83(3467, 'Berry'),
84(4756, 'Coke'),
85(5123, 'Dave'),
86(1789, 'Ester');
87
88insert into qualification values
89(2143, 'ISM 3112', '1988-09-01'),
90(2143, 'ISM 3113', '1988-05-01'),
91(2143, 'ISM 4212', '1995-01-01'),
92(2143, 'ISM 4930', '2000-11-25'),
93(3467, 'ISM 4212', '1995-06-01'),
94(3467, 'ISM 4930', '1996-09-01'),
95(4756, 'ISM 3113', '1991-09-01'),
96(4756, 'ISM 3112', '1991-07-01'),
97(5123, 'ISM 3112', '1992-06-01');
98
99insert into room values
100(1, 'meeting room', 6),
101(2, 'class room', 20),
102(3, 'class room', 20),
103(4, 'meeting room', 3),
104(5, 'class room', 40),
105(6, 'seminar room', 50),
106(7, 'seminar room', 70),
107(8, 'meeting room', 3),
108(9, 'study room', 2);
109
110insert into assessment values
111(1, 'Midterm Exam', 0.3),
112(2, 'Final Exam', 0.4),
113(3, 'Project', 0.3);
114
115insert into performance values
116(38214, 'ISM 4212', 1, 64),
117(38214, 'ISM 4212', 2, 79),
118(38214, 'ISM 4212', 3, 80),
119(54907, 'ISM 4212', 1, 92),
120(54907, 'ISM 4212', 2, 89),
121(54907, 'ISM 4212', 3, 94),
122(54907, 'ISM 4930', 1, 60),
123(54907, 'ISM 4930', 2, 72),
124(54907, 'ISM 4930', 3, 68),
125(54907, 'ISM 3112', 1, 54),
126(54907, 'ISM 3112', 2, 61),
127(54907, 'ISM 3112', 3, 70),
128(54907, 'ISM 3113', 1, 88),
129(54907, 'ISM 3113', 2, 93),
130(54907, 'ISM 3113', 3, 98),
131(66324, 'ISM 3113', 1, 78),
132(66324, 'ISM 3113', 2, 82),
133(66324, 'ISM 3113', 3, 66),
134(66324, 'ISM 3112', 1, 67),
135(66324, 'ISM 3112', 2, 72),
136(66324, 'ISM 3112', 3, 70),
137(70542, 'ISM 3112', 1, 68),
138(70542, 'ISM 3112', 2, 77),
139(70542, 'ISM 3112', 3, 74),
140(70542, 'ISM 4212', 1, 84),
141(70542, 'ISM 4212', 2, 78),
142(70542, 'ISM 4212', 3, 65);