CREATE TABLE Department(
	dId SERIAL NOT NULL,
	dName VARCHAR(255) NOT NULL,
	CONSTRAINT pk_Department PRIMARY KEY (dId)
);

INSERT INTO Department (dName) VALUES
	('IS'),
	('CSSE'),
	('MKM'),
	('CS'),
	('JUR'),
	('ITM');

/*============================================================================*/

CREATE TABLE Lecturer(
	lId SERIAL NOT NULL,
	lName VARCHAR(255) NOT NULL,
	dId INT,
	CONSTRAINT pk_Lecturer PRIMARY KEY (lId),
	CONSTRAINT fk_Department_Lecturer FOREIGN KEY (dId) REFERENCES Department (dId)
		ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO Lecturer (lName,dId) VALUES
	('Ospan Alua',1),
	('Mukashev Serik',4),
	('Tuleubekov Assyl',5),
	('Altaibek Aizhan',1),
	('Uteshova Rosa',3),
	('Mukhitova Karlygash',1),
	('Zhuanyshev Iliyas',1),
	('Serzhanov Timur',2),
	('Serbin Vasiliy',1),
	('Tabynbaev Assan',6),
	('Meldibekova Zulfiya',5),
	('Bagdinov Ruffat',5),
	('Saimassayeva Sholpan',1),
	('Bekbulatov Takhir',2);

/*============================================================================*/

CREATE TABLE Course(
	cId SERIAL NOT NULL,
	cName VARCHAR(255) NOT NULL,
	dId INT,
	CONSTRAINT pk_Course PRIMARY KEY (cId),
	CONSTRAINT fk_Department_Course FOREIGN KEY (dId) REFERENCES Department (dId)
		ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO Course (cName,dId)  VALUES
	('IS1',1),
	('IS2',1),
	('CSSE1',2),
	('CSSE2',2),
	('MKM1',3),
	('MKM2',3),
	('CS1',4),
	('CS2',4),
	('JUR1',5),
	('JUR2',5),
	('ITM1',6),
	('ITM2',6);

/*============================================================================*/

CREATE TABLE Module(
	mId SERIAL NOT NULL,
	mName VARCHAR(255) NOT NULL,
	lId INT,
	CONSTRAINT pk_Module PRIMARY KEY (mId),
	CONSTRAINT fk_Lecturer_Module FOREIGN KEY (lId) REFERENCES Lecturer (lId)
		ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO Module (mName,lId) VALUES
	('SDP1',1),
	('SDP2',1),
	('SDP3',3),
	('SDP4',3),
	('DataBase Systems',5),
	('Fundamentals of Law',8),
	('Philosophy',9),
	('Mat.Analysis',10),
	('C#',1),
	('C++',2),
	('Algebra',4),
	('Discrete Math',6),
	('English Language',7),
	('Russian Language',7),
	('Operating Systems',3);

/*============================================================================*/

CREATE TABLE Course_Module(
	cId INT,
	mId INT,
	CONSTRAINT pk_Course_Module PRIMARY KEY (cId, mId),
	CONSTRAINT fk_Course FOREIGN KEY (cId) REFERENCES Course (cId)
		ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT fk_Module FOREIGN KEY (mId) REFERENCES Module (mId)
		ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO Course_Module (cId,mId) VALUES
	(1,1),
	(2,2),
	(3,12),
	(4,3),
	(5,4),
	(6,6),
	(7,9),
	(8,11),
	(9,5),
	(10,7),
	(11,9);

/*============================================================================*/

CREATE TABLE Student(
	sId serial NOT NULL,
	sName VARCHAR(255) NOT NULL,
	cId INT,
	CONSTRAINT pk_Student PRIMARY KEY (sId),
	CONSTRAINT fk_Course_Student FOREIGN KEY (cId) REFERENCES Course (cId)
		ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO Student (sName,cID) VALUES
	('Abilgazy Akezhan',1),
	('Ainabek Zhandos',2),
	('Aliaidar Nurdaulet',3),
	('Akhmetzhan Aidos',4),
	('Bakytov Yertas',5),
	('Baltabayev Mukhtar',6),
	('Burakhanova Ayaulym',7),
	('Yerzhankyzy Assem',8),
	('Yeskendiruly Bizhan',9),
	('Zhaksibay Madi',10),
	('Zhilkyshieva Aigerym',11),
	('Zairov Olzhas',12),
	('Imangalieva Annela',1),
	('Issabek Ulykbek',2),
	('Karimov Azamat',3),
	('Kalymbek Azamat',4),
	('Niyetalin Askhat',5),
	('Nurtai Erzhan',6),
	('Sadyrbekov Adil',7),
	('Tolagai Zamanbek',8),
	('Uatbayeva Aibanu',9),
	('Tilenbayeva Aktoty',10),
	('Ratbek Shokan',11),
	('Nuradinova Amina',12),
	('Zaurbekova Gaukhar',1);

/*============================================================================*/

CREATE TABLE Grade(
	sId INT,
	mId INT,
	mark INT NOT NULL,
	CONSTRAINT pk_Grade PRIMARY KEY (sId, mId),
	CONSTRAINT fk_Student FOREIGN KEY (sId) REFERENCES Student(sId)
		ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT fk_Module FOREIGN KEY (mId) REFERENCES Module(mId)
		ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO Grade (sId,mId,mark) VALUES
(1,1,27),
(2,1,50),
(3,4,95),
(5,7,100),
(6,8,88),
(9,10,40),
(11,12,60),
(14,15,100),
(13,11,90),
(15,10,85),
(5,11,79),
(4,8,100),
(7,7,39);

/*============================================================================*/

SELECT * FROM Department;
SELECT * FROM Lecturer;
SELECT * FROM Course;
SELECT * FROM Module;
SELECT * FROM Course_Module;
SELECT * FROM Student;
SELECT * FROM Grade;

SELECT lName FROM Lecturer AS l, Department AS d WHERE l.dId = d.dId AND d.dName = 'IS';

SELECT sName FROM Student,Course  WHERE Course.cId = Student.cId AND Course.cName = 'CSSE1';
SELECT lName FROM Lecturer AS l,Course AS c  WHERE l.dId = c.dId AND c.cName = 'ITM2';
SELECT sName FROM Student AS s,Department d, Lecturer AS l,Course AS c,Course_Module AS cm, Module AS m WHERE l.lId = 4 AND s.cId = c.cId AND s.cId = cm.cId AND m.lId = l.lId AND m.mId = cm.mId AND d.dId = c.dId;
SELECT Student.sName,Course.cName FROM Student , Course WHERE Course.cId = Student.cId ORDER BY Course.cName;
SELECT *FROM Module ORDER BY mName DESC;
SELECT COUNT(Module.mId) FROM Module, Course_Module, Course WHERE Module.mId = Course_Module.mId AND Course_Module.cId = Course.cId AND Course.cName LIKE '%2';

SELECT sName,mName FROM Student AS s,Department d, Lecturer AS l,Course AS c,Course_Module AS cm, Module AS m WHERE m.mName LIKE '%SD__' AND s.cId = c.cId AND s.cId = cm.cId AND m.lId = l.lId AND m.mId = cm.mId AND d.dId = c.dId ;