-- Database: "Lab7"

-- DROP DATABASE "Lab7";

CREATE DATABASE "Lab7"
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;


CREATE TABLE credits(
	cred_num int PRIMARY KEY,
	lect_hours int,
	lab_hours int,
	pract_hours int,
	ind_st_hours int,
	total_hours int
	);

CREATE TABLE subjects(
	subj_id int PRIMARY KEY,
	name varchar(20),
	cred_num int REFERENCES credits
	);

CREATE TABLE students(
	stud_id int PRIMARY KEY,
	fname varchar(20),
	lname varchar(20),
	bdate date,
	phnum int,
	email varchar(30),
	group_id int
	);

CREATE TABLE groups(
	group_id int PRIMARY KEY,
	name varchar(10),
	headstud_id int REFERENCES students(stud_id),
	major_id int
	);

ALTER TABLE students ADD FOREIGN KEY(group_id) REFERENCES groups(group_id);

CREATE TABLE ed_process(
	proc_id int PRIMARY KEY,
	group_id int REFERENCES groups,
	subj_id int REFERENCES subjects,
	term int
	);

CREATE TABLE progress(
	progress_id int PRIMARY KEY,
	proc_id int REFERENCES ed_process,
	stud_id int REFERENCES students,
	rk1 int,
	rk2 int,
	rk_avg int,
	exam int,
	final int
	);

CREATE TABLE teachers(
	teach_id int PRIMARY KEY,
	fname varchar(20),
	lname varchar(20),
	phnum int,
	email varchar(30),
	position varchar(30),
	dep_id int
	);

CREATE TABLE departments(
	dep_id int PRIMARY KEY,
	name varchar(50),
	room int,
	phnum int,
	email varchar(30),
	headteach_id int REFERENCES teachers(teach_id)
	);

ALTER TABLE teachers ADD FOREIGN KEY(dep_id) REFERENCES departments(dep_id);

CREATE TABLE majors(
	major_id int PRIMARY KEY,
	name varchar(100),
	dep_id int REFERENCES departments
	);

ALTER TABLE groups ADD FOREIGN KEY(major_id) REFERENCES majors(major_id);

CREATE TABLE classes(
	class_id int PRIMARY KEY,
	type varchar(15)
	);

CREATE TABLE schedule(
	sch_id int PRIMARY KEY,
	proc_id int REFERENCES ed_process,
	class_id int REFERENCES classes,
	teach_id int REFERENCES teachers,
	room int,
	time time,
	day_of_week varchar(5)
	);

INSERT INTO departments VALUES (1,'CSSE&T',409,124525,'caf.csse@gmail.com',NULL),
				(2,'IS',800,124525,'caf.is@gmail.com',NULL),
				(3,'EM',200,124525,'caf.em@gmail.com',NULL);

INSERT INTO majors VALUES (1,'CSSE',1),
			(2,'RET',1);

INSERT INTO groups VALUES (1,'CSSE-131',NULL, 1),
			(2,'CSSE-132',NULL, 1),
			(3,'CSSE-133',NULL, 1),
			(4,'CSSE-134',NULL, 1),
			(5,'CSSE-135',NULL, 1),
			(6,'CSSE-136',NULL, 1),
			(7,'CSSE-137',NULL, 1),
			(8,'CSSE-138',NULL, 1);

INSERT INTO students VALUES (1,'Shakhmardan','Abdiganiev','01/01/1995',1234567,'abdiganiev@gmail.com',1);
INSERT INTO students VALUES (2,'Saulet','Abai','12/01/1995',1234567,'abai@gmail.com',2);
INSERT INTO students VALUES (3,'Alina','Achilova','11/03/1995',1234567,'achilova@gmail.com',3);
INSERT INTO students VALUES (4,'Tursyn','Adilkhan','12/10/1995',1234567,'adilkhan@gmail.com',4);
INSERT INTO students VALUES (5,'Alisher','Asainov','12/10/1995',1234567,'asainov@gmail.com',5);
INSERT INTO students VALUES (6,'Anuarkhan','Aimenov','03/24/1995',1234567,'aimenov@gmail.com',6);
INSERT INTO students VALUES (7,'Almat','Akhat','03/25/1995',1234567,'akhat@gmail.com',7);
INSERT INTO students VALUES (8,'Erkesh','Akkoshkarov','03/26/1995',1234567,'akkoshkarov@gmail.com',8);

INSERT INTO teachers VALUES (1,'Olga','Zvyaginceva',1234567,'zvyaginceva@gmail.com','Senior lecturer',1),
			(2,'Kairat','Sariyev',1234567,'sariyev@gmail.com','Senior lecturer',1),
			(3,'Nazgul','Rakhimzhanova',1234567,'rakhimzhanova@gmail.com','Senior lecturer',1),
			(4,'Lyudmila','Kozina',1234567,'kozina@gmail.com','Senior lecturer',1);
INSERT INTO teachers VALUES (5,'Serik','Baibolat',1234567,'serik@gmail.com','Professor',1),
			(6,'Elaman','Kaipov',1234567,'kaipov@gmail.com','Senior lecturer',1),
			(7,'Nicole','Tesla',1234567,'Tesla@gmail.com','Professor',1),
			(8,'Rene','Descartes',1234567,'Descartes@gmail.com','Professor',1),
			(9,'Azamat','Maratovich',1234567,'Azicus@gmail.com','Senior lecturer',1),
			(10,'Almat','Meirbay',1234567,'Almat@gmail.com','Senior lecturer',1),
			(11,'Ersultan','Sayzin',1234567,'SZN@gmail.com','Professor',1);

INSERT INTO credits VALUES (1,15,15,0,15,45),
			(2,15,30,0,45,90),
			(3,15,30,15,60,120);

INSERT INTO subjects VALUES (1,'Database Design',2),
			(2,'Physics',3);
INSERT INTO subjects VALUES(3, 'C++', 2),
			(4, 'Java', 3),
			(5, 'History', 1);

INSERT INTO ed_process VALUES (1,1,1,4),
			(2,2,1,4),
			(3,3,1,4),
			(4,4,1,4),
			(5,5,1,4),
			(6,6,1,4);

INSERT INTO ed_process VALUES(7,7,1,4),
			(8, 8, 1, 4),
			(9, 1, 2, 2),
			(10, 2, 2, 2),
			(11, 3, 3, 4),
			(12, 4, 2, 4),
			(13, 5, 4, 4),
			(14, 6, 4, 4),
			(15, 7, 2, 4),
			(16, 8, 2, 4);

INSERT INTO progress VALUES (1,1,1,90,90,90,90,90),
			(2,2,2,90,90,90,90,90),
			(3,2,3,90,90,90,90,90);
INSERT INTO progress VALUES(4,4,4,80,80,80,90,90);
INSERT INTO progress VALUES(5,5,4,60,90,75,90,90);
INSERT INTO progress VALUES(6,5,6,50,100,75,90,90),
			(7,6,7,70,90,80,90,90),
			(8,7,8,40,90,65,80,90),
			(9,8,2,90,50,70,90,90),
			(10,9,3,60,60,60,90,90),
			(11,10,4,80,80,80,90,90),
			(12,11,8,70,70,70,90,90),
			(13,12,7,90,90,90,90,90);

INSERT INTO classes VALUES (1,'lecture'),
			(2,'lab'),
			(3,'pract');

INSERT INTO schedule VALUES (1,1,1,4,901,'10:00:00','mon'),
			(2,2,2,4,303,'08:00:00','mon');
INSERT INTO schedule VALUES(3, 3, 2, 1, 700, '09:00:00', 'tue'),
			(4, 4, 1, 2, 800, '12:00:00', 'wed'),
			(5, 5, 3, 3, 701, '10:00:00', 'fri');
INSERT INTO schedule VALUES(6, 6, 3, 5, 609, '09:00:00', 'mon'),
			(7, 7, 2, 6, 609, '09:00:00', 'thu'),
			(8, 8, 3, 7, 609, '09:00:00', 'mon'),
			(9, 9, 3, 8, 609, '09:00:00', 'mon'),
			(10, 10, 3, 9, 609, '09:00:00', 'sun');
INSERT INTO schedule VALUES(11, 11, 1, 7, 901, '12:00:00', 'mon'),
			(12, 12, 1, 8, 907, '13:00:00', 'mon');

select g.name, sub.name, s.room, s.time, s.day_of_week, c.type, t.lname from Schedule s, Groups g, Subjects sub, Classes c, Teachers t, Ed_process ed
	where s.proc_id = ed.proc_id and ed.group_id = g.group_id and ed.subj_id = sub.subj_id and s.class_id = c.class_id and s.teach_id = t.teach_id;

select s.fname || ' ' || s.lname as FIO, sub.name, p.rk1, p.rk2, p.rk_avg, p.exam, p.final
from Progress p, Ed_process ed, Subjects sub, Students s
where p.proc_id = ed.proc_id and ed.group_id = s.group_id and ed.subj_id = sub.subj_id;

select g.name, sub.name, avg(p.final) from Ed_process ed, Groups g, Subjects sub, Progress p
where ed.group_id = g.group_id and ed.subj_id = sub.subj_id group by g.name, sub.name;

select s.fname || ' ' || s.lname as FIO, avg(p.final) from Students s, Progress p
where s.stud_id = p.stud_id group by s.fname, s.lname having avg(p.final) > 89 order by s.fname asc, s.lname asc;

select t.lname, sub.name, s.time, s.room from Schedule s, Subjects sub, Teachers t, Ed_Process ed
where s.proc_id = ed.proc_id and s.teach_id = t.teach_id and ed.subj_id = sub.subj_id and s.class_id = 1 and s.day_of_week = 'mon';

select t.lname, count(*) as hours_in_a_week from Schedule s, Teachers t
where s.teach_id = t.teach_id group by t.lname having count(*) > 0;

select g.name, ed.term, cd.total_hours from Groups g, Ed_process ed, Credits cd, Subjects sub
where ed.group_id = g.group_id and ed.subj_id = sub.subj_id and sub.cred_num = cd.cred_num;

select s.fname || ' ' || s.lname as FIO, sub.name, p.rk1, p.rk2, p.rk_avg, p.exam, p.final
from Progress p, Ed_process ed, Subjects sub, Students s
where p.proc_id = ed.proc_id and ed.group_id = s.group_id and ed.subj_id = sub.subj_id and p.final between 80 and 95;