git.iko.soy/bat
on 2aa3ed9 help sign in
bat/tests/syntax-tests/highlighted/SQL/ims.sql
266 lines 40 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));
190/*
191შექმენით view სადაც იქნება სტაჟირების შესახებ ინფორმაცია:
192სახელი,
193დაწყების თარიღი,
194დასრულების თარიღი,
195მონაწილეების რაოდენობა.
196*/
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;
201/*
202შექმენით view სადაც იქნება მონაწილეებზე ინფორმაცია:
203სტაჟირების სახელი,
204მონაწილის სახელი,
205მიმართულება,
206შერჩევის რამდენი ეტაპი გაიარა,
207სტაჟირების სტატუსი(გაიარა, ვერ გაიარა, მიმდინარე,ვერ მოხვდა სტაჟირებაზე)
208*/
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);
223/*
224შექნენით view სადაც იქნება მიმართულებებზე ინფორმაცია:
225მიმართულების სახელი,
226რა საგნები ისწავლება
227*/
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;
236/*
237შექმენით view სადც იქნება საგნების ინფორმაცია:
238საგნის სახელი,
239საგნის ზღვარი,
240შეფასების კომპონენტები(სახელი, ზღვარი , წონა).
241*/
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;
250/*
251შექმენით view დასწრების აღრიცხვა მონაწილეების მიხედვით:
252სტაჟირებაზე მიმართულების მიხედვით:
253რამდენი ჩატარდა,
254რამდენს დაესწრო,
255რამდენს არ დაესწრო.
256*/
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;