tests/syntax-tests/source/SQL/ims.sql · bat · git.iko.soy

git.iko.soy/bat

on d6ed5e6 help sign in

bat/tests/syntax-tests/source/SQL/ims.sql

266 lines 9.1 kB history raw

1-- interships

2create table interships

3(intership_id number(7) constraint intership_id_pk primary key,

4name varchar2(50),

5start_date date,

6end_date date);

9insert into interships

10values

11 (1,

12 'Leaderator 2019',

13 to_date('15/02/2019', 'DD/MM/YYYY'),

14 to_date('01/09/2019', 'DD/MM/YYYY'));

16insert into interships

17 (intership_id, name, start_date)

18values

19 (2, 'Leaderator 2020', to_date('10/02/2019', 'DD/MM/YYYY'));

21commit;

23-- directions

24create table directions

25(direction_id number(7) constraint direction_id_pk primary key,

26name varchar2(50));

28insert into directions values (1, 'Data Science');

30insert into directions values (2, 'Oracle Development');

32commit;

34-- participants

35create table participants

36(participant_id number(7) constraint participant_id_pk primary key,

37first_name varchar2(25),

38last_name varchar2(25),

39personal_id number(11),

40intership_id number(7) constraint participant_inter_id_fk references interships (intership_id),

41direction_id number(7) constraint participant_direct_id_fk references directions (direction_id),

42constraint personal_id_unique unique (personal_id));

44insert into participants

45values

46 (1, 'Erekle', 'Tvinadze', 01011234567, 1, 1);

48insert into participants

49values

50 (2, 'Mariami', 'Chakhvadze', 01011234568, 2, 2);

52commit;

54-- hiring_layer_types

55create table hiring_layer_types

56(layer_type_id number(7) constraint layer_id_pk primary key,

57type varchar2(50));

59insert into hiring_layer_types values (1, 'GMAT');

61insert into hiring_layer_types values (2, 'Algorithms');

63insert into hiring_layer_types values (3, 'Interview');

65commit;

67-- hiring_layers

68create table hiring_layers

69(layer_id number(7) constraint layer_pk primary key,

70layer_type_id number(7) constraint layer_type_fk references hiring_layer_types (layer_type_id),

71participant_id number(7) constraint participant_id_fk references participants (participant_id),

72result number(3));

74insert into hiring_layers values (1, 1, 1, 52);

76insert into hiring_layers values (2, 1, 2, 80);

78insert into hiring_layers values (3, 2, 2, 75);

80insert into hiring_layers values (4, 3, 2, 100);

82commit;

84-- subjects

85create table subjects

86(subject_id number(7) constraint subject_id_pk primary key,

87name varchar2(100),

88minimum_score number(3));

90insert into subjects values (1, 'SQL', 70);

92insert into subjects values (2, 'Machine Learning', 70);

94commit;

96-- direction_subjects

97create table direction_subjects

98(direction_subject_id number(7) constraint direct_sub_id primary key,

99direction_id number(7) constraint direct_id_fk references directions (direction_id),

100subject_id number(7) constraint subject_id_fk references subjects (subject_id));

102insert into direction_subjects values (1, 2, 1);

104insert into direction_subjects values (2, 1, 1);

106insert into direction_subjects values (3, 1, 2);

108commit;

110-- component_types

111create table component_types

112(component_type_id number(7) constraint com_type_id_pk primary key,

113type varchar2(50));

115insert into component_types values (1, 'Homework');

117insert into component_types values (2, 'Quiz');

119commit;

121-- components

122create table components

123(component_id number(7) constraint component_id_pk primary key,

124subject_id number(7) constraint sub_id_fk references subjects (subject_id),

125minimum_score number(3),

126component_type_id number(7) constraint com_type_id_fk references component_types (component_type_id),

127weight number(3));

129insert into components values (1, 1, 60, 1, 20);

131insert into components values (2, 2, 65, 2, 50);

133commit;

135-- results

136create table results

137(result_id number(7) constraint result_id_pk primary key,

138issue_date date,

139grade number(3),

140participant_id number(7) constraint particip_id_fk references participants (participant_id),

141component_id number(7) constraint component_id_fk references components (component_id));

143insert into results

144values

145 (1, to_date('04/05/2020', 'DD/MM/YYYY'), 87, 2, 2);

147commit;

149-- learning_material_types

150create table learning_material_types

151(material_type_id number(7) constraint lear_material_id_pk primary key,

152type varchar(50));

154insert into learning_material_types values (1, 'Book');

156insert into learning_material_types values (2, 'PPT');

158insert into learning_material_types values (3, 'Youtube Video');

160commit;

162-- learning_materials

163create table learning_materials

164(learning_material_id number(7),

165url varchar2(3000),

166subject_id number(7) constraint subj_id_fk references subjects (subject_id),

167material_type_id number(7) constraint material_type_id_fk references learning_material_types (material_type_id));

169insert into learning_materials values (1, 'www.youtube.com', 1, 3);

171commit;

173-- sessions

174create table sessions

175(session_id number(7) constraint session_id_pk primary key,

176start_date date,

177end_date date,

178intership_id number(7) constraint inter_idd_fk references interships (intership_id),

179direction_id number(7) constraint direct_fk references directions (direction_id),

180subject_id number(7) constraint subject_fk references subjects (subject_id));

182-- attendances

183create table attendances

184(attendance_id number(7) constraint attend_id_pk primary key,

185participant_id number(7) constraint participant_fk references participants (participant_id),

186session_id number(7) constraint session_id_fk references sessions (session_id),

187status varchar2(25));

191შექმენით view სადაც იქნება სტაჟირების შესახებ ინფორმაცია:

192სახელი,

193დაწყების თარიღი,

194დასრულების თარიღი,

195მონაწილეების რაოდენობა.

197create view intership_info

198as select i.name, i.start_date, i.end_date, (select count(participant_id) from participants p where p.intership_id = i.intership_id) number_of_participants

199from interships i;

202შექმენით view სადაც იქნება მონაწილეებზე ინფორმაცია:

203სტაჟირების სახელი,

204მონაწილის სახელი,

205მიმართულება,

206შერჩევის რამდენი ეტაპი გაიარა,

207სტაჟირების სტატუსი(გაიარა, ვერ გაიარა, მიმდინარე,ვერ მოხვდა სტაჟირებაზე)

209create view participant_info

210as select i.name intership, p.first_name, p.last_name, d.name direction,

211(select count(h2.participant_id) from hiring_layers h2 where h2.participant_id = p.participant_id) number_of_layers,

212case when p.direction_id is null then 'Rejected'

213 when i.end_date is null then 'Present'

214 when (select h2.result from hiring_layers h2 join hiring_layer_types l on h2.layer_type_id = l.layer_type_id where h2.participant_id = p.participant_id and l.type = 'Intership') > (select minimum_score from subjects) then 'Passed'

215 else 'Failed'

216 end status

217from interships i,

218participants p,

219directions d

220where i.intership_id = p.intership_id

221and (d.direction_id = p.direction_id or p.direction_id is null);

224შექნენით view სადაც იქნება მიმართულებებზე ინფორმაცია:

225მიმართულების სახელი,

226რა საგნები ისწავლება

228create view direction_info

229as select d.name direction, s.name subject

230from directions d,

231direction_subjects ds,

232subjects s

233where d.direction_id = ds.direction_id

234and ds.subject_id = s.subject_id;

237შექმენით view სადც იქნება საგნების ინფორმაცია:

238საგნის სახელი,

239საგნის ზღვარი,

240შეფასების კომპონენტები(სახელი, ზღვარი , წონა).

242create view subject_info

243as select s.name, s.minimum_score subject_min_score, ct.type, c.minimum_score component_min_score, c.weight

244from subjects s,

245component_types ct,

246components c

247where s.subject_id = c.subject_id

248and c.component_type_id = ct.component_type_id;

251შექმენით view დასწრების აღრიცხვა მონაწილეების მიხედვით:

252სტაჟირებაზე მიმართულების მიხედვით:

253რამდენი ჩატარდა,

254რამდენს დაესწრო,

255რამდენს არ დაესწრო.

257create view attendance_info

258as select p.first_name, p.last_name,

259(select count(s2.session_id) from sessions s2 where s2.direction_id = p.direction_id) lectures,

260(select count(a2.attendance_id) from attendances a2 where a2.participant_id = p.participant_id and a2.status = 'Present') present,

261(select count(a2.attendance_id) from attendances a2 where a2.participant_id = p.participant_id and a2.status = 'Absent') absent

262from participants p,

263attendances a,

264sessions s

265where p.participant_id = a.participant_id

266and a.session_id = s.session_id;