MySQL tasks

task_C.sql

DROP DATABASE IF EXISTS dbTaskC;

CREATE DATABASE IF NOT EXISTS dbTaskC;

USE dbTaskC;

CREATE TABLE Student (
  Id int(11) UNIQUE NOT NULL AUTO_INCREMENT,
  FirstName varchar(50),
  LastName varchar(50),
  ZipCode int(11),

  PRIMARY KEY (Id)
);


CREATE TABLE Seminar (
  Id int(11) UNIQUE NOT NULL AUTO_INCREMENT,
  Name varchar(100),

  PRIMARY KEY (Id)
);


CREATE TABLE SeminarEnrollment (
  StudentId int(11),
  SeminarId int(11),
  MarkDate date,
  Mark int(11),

  FOREIGN KEY (StudentId) REFERENCES Student (Id),
  FOREIGN KEY (SeminarId) REFERENCES Seminar (Id)
);


DELIMITER //
CREATE PROCEDURE StudentEducation()
BEGIN
	SELECT	st.Id,
		st.FirstName,
		st.LastName,
		sm.Name,
		avg(se.Mark) AS AverageMark

	FROM Student AS st, Seminar AS sm, SeminarEnrollment AS se
	WHERE
		st.Id = se.StudentId AND
		sm.Id = se.SeminarId AND
		st.ZipCode = 10001 AND
		sm.Name = 'Math' AND
		year(se.MarkDate) = 2013

	GROUP BY st.Id

	HAVING AverageMark > 75

	ORDER BY AverageMark DESC;
END//
DELIMITER ;



INSERT INTO Student
	(
		Id, FirstName, LastName, ZipCode
	)
VALUES
	(1, 'Mario', 'Gotze', 10001),
	(2, 'Marco', 'Rues', 10002),
	(3, 'Dimitri', 'Payet', 10001),
	(4, 'Diego', 'Milito', 10003),
	(5, 'Carlos', 'Vela', 10002),
	(6, 'Angel', 'Di Maria', 10001);



INSERT INTO Seminar
	(
		Id, Name
	)
VALUES (1, 'Java'), (2, 'Math'), (3, 'English Language'), (4, 'Unix');



INSERT INTO SeminarEnrollment
	(
		StudentId, SeminarId, MarkDate, Mark
	)
VALUES
	(1, 1, '2013-01-11', 70),
	(1, 2, '2012-10-01', 65),
	(1, 2, '2013-08-08', 89),
	(1, 2, '2011-10-01', 65),
	(1, 2, '2013-08-08', 75),

	(2, 1, '2013-04-05', 100),
	(2, 2, '2011-11-03', 78),
	(2, 2, '2013-09-13', 79),
	(2, 2, '2010-11-03', 80),
	(2, 2, '2013-09-13', 81),

	(3, 1, '2013-04-05', 100),
	(3, 2, '2012-11-03', 80),
	(3, 2, '2013-09-13', 91),
	(3, 2, '2013-11-03', 75),
	(3, 2, '2013-09-13', 79),

	(4, 1, '2013-04-05', 100),
	(4, 2, '2012-11-03', 80),
	(4, 2, '2013-09-13', 50),
	(4, 2, '2013-11-03', 70),
	(4, 2, '2013-09-13', 79),

	(5, 1, '2013-09-13', 79),
	(5, 2, '2013-11-10', 75),
	(5, 3, '2013-11-10', 90),
	(5, 3, '2013-12-07', 90),
	(5, 3, '2012-11-10', 77),
	(5, 3, '2013-11-08', 77),

	(6, 2, '2013-11-10', 77),
	(6, 2, '2013-11-10', 98),
	(6, 2, '2012-09-10', 71);

task_D.sql

DROP DATABASE IF EXISTS dbTaskD;

CREATE DATABASE IF NOT EXISTS dbTaskD;

USE dbTaskD;

CREATE TABLE Patient (
	Id int UNIQUE NOT NULL AUTO_INCREMENT,
	FirstName varchar(50),
	LastName varchar(50),
	DoB date,

	PRIMARY KEY (Id)
);


CREATE TABLE Physician (
	Id int UNIQUE NOT NULL AUTO_INCREMENT,
	FirstName varchar(50),
	LastName varchar(50),
	PhysicianOrganizationId int,

	primary key (Id)
);

CREATE TABLE PatientPhysician (
	IdPatient int,
	IdPhysician int,

	FOREIGN KEY (IdPatient) REFERENCES Patient(Id),
	FOREIGN KEY (IdPhysician) REFERENCES Physician(Id)
);


DELIMITER //
CREATE PROCEDURE dupPatients()
BEGIN
	SELECT pt.Id, tmp.*
	FROM (

		SELECT	pt.FirstName AS fname,
	         	pt.LastName AS lname,
	         	pt.DoB AS dob,
	         	ph.PhysicianOrganizationId AS poid

		FROM Patient pt, Physician ph, PatientPhysician pp
		WHERE
			pt.Id = pp.IdPatient AND
			ph.Id = pp.IdPhysician

		GROUP BY fname, lname, dob, poid

		HAVING COUNT(*) > 1

	) AS tmp

	JOIN Patient AS pt ON pt.FirstName = tmp.fname AND pt.LastName = tmp.lname AND pt.DoB = tmp.dob

	JOIN PatientPhysician AS pp ON pt.Id = pp.IdPatient

	JOIN Physician AS ph ON ph.Id = pp.IdPhysician AND tmp.poid = ph.PhysicianOrganizationId

	ORDER BY ph.PhysicianOrganizationId, pt.Id;
END//
DELIMITER ;


INSERT INTO Patient
	(
		Id, FirstName, LastName, DoB
	)
VALUES
	(1, 'Mario', 'Gotze', '1989-01-09'),
	(2, 'Mario', 'Gotze', '1989-01-09'),
	(3, 'Mario', 'Gotze', '1989-01-09'),
	(4, 'Fillip', 'Gotze', '1989-01-09'),

	(5, 'Marco', 'Rues', '1988-09-12'),

	(6, 'Dimitri', 'Payet', '1986-10-10'),
	(7, 'Dimitri', 'Payet', '1986-10-10'),
	(8, 'Dimitri', 'Payet', '1986-10-10'),

	(9, 'Zlatan', 'Ibrahimovic', '1982-01-12'),
	(10, 'Zlatan', 'Ibrahimovic', '1982-01-12'),

	(11, 'Diego', 'Milito', '1982-01-12'),
	(12, 'Diego', 'Milito', '1982-01-12'),
	(13, 'Gabriel', 'Milito', '1987-12-12'),
	(14, 'Diego', 'Milito', '1982-01-12');


INSERT INTO Physician
	(
		Id, FirstName, LastName, PhysicianOrganizationId
	)
VALUES
	(1, 'Mats', 'Hummels', 101),
	(2, 'Luis', 'Gustavo', 102),
	(3, 'Urgen', 'Klopp', 103);


INSERT INTO PatientPhysician
	(
		IdPatient, IdPhysician
	)
VALUES
	(1, 1),
	(2, 2),
	(3, 1),
	(4, 1),

	(5, 1),
	(5, 2),
	(5, 3),

	(6, 1),
	(7, 1),
	(8, 1),
	(8, 2),

	(9, 1),
	(9, 3),
	(10, 1),
	(10, 3),

	(11, 2),
	(12, 2),
	(12, 1),
	(14, 1),
	(13, 1),
	(14, 2);

task_E.sql

DROP DATABASE IF EXISTS dbTaskE;

CREATE DATABASE IF NOT EXISTS dbTaskE;

USE dbTaskE;

CREATE TABLE PatientMedicalRecord (
	Id int UNIQUE NOT NULL AUTO_INCREMENT,
	PatientId int,
	MedicalRecordId int,
	DatePerformed date,

PRIMARY KEY (Id)
);



DELIMITER //
CREATE PROCEDURE delDuplPatient()
BEGIN
	CREATE TEMPORARY TABLE tmp

		SELECT Id FROM
		(
			SELECT 	PatientId AS pid,
				MedicalRecordId AS mid,
				MAX(DatePerformed) AS maxDate

			FROM PatientMedicalRecord

			GROUP BY PatientId, MedicalRecordId

		) AS subPt

		JOIN PatientMedicalRecord

		ON PatientId = subPt.pid AND
		   MedicalRecordId = subPt.mid AND
		   DatePerformed = subPt.maxDate; 

	DELETE FROM PatientMedicalRecord WHERE Id NOT iN (SELECT * FROM tmp);

	DROP TEMPORARY TABLE tmp;

	SELECT * FROM PatientMedicalRecord;

END//
DELIMITER ;



INSERT INTO PatientMedicalRecord
	(
		PatientId, MedicalRecordId, DatePerformed
	)
VALUES
	(1, 1, '2012-01-12'),		
	(1, 1, '2012-09-02'),
	(1, 1, '2013-09-10'),
	(1, 1, '2011-10-11'),
	(1, 2, '2010-12-11'),
	(1, 4, '2010-12-11'),
	(1, 3, '2010-12-11'),

	(2, 2, '2011-10-11'),
	(2, 3, '2012-11-11'),
	(2, 4, '2012-11-11'),
	(2, 2, '2010-04-01'),

	(3, 1, '2010-04-01'),
	(3, 3, '2010-04-01'),
	(3, 3, '2013-12-08'),
	(3, 3, '2013-12-09'),

	(4, 4, '2010-10-09'),
	(4, 1, '2009-10-08'),
	(4, 2, '2011-04-08'),

	(5, 5, '2013-11-15'),
	(5, 1, '2011-08-08'),
	(5, 2, '2013-11-11'),
	(5, 2, '2010-12-12'),
	(5, 2, '2011-07-12'),
	(5, 2, '2013-11-15');


/*
DELETE FROM PatientMedicalRecord
WHERE Id NOT IN
      (
        SELECT Id FROM
          (
            SELECT Id FROM
                (
                  SELECT
                    PatientId          AS pid,
                    MedicalRecordId    AS mid,
                    MAX(DatePerformed) AS maxDate

                  FROM PatientMedicalRecord

                  GROUP BY PatientId, MedicalRecordId
                ) AS subPt

                JOIN PatientMedicalRecord

                  ON PatientId = subPt.pid AND
                     MedicalRecordId = subPt.mid AND
                     DatePerformed = subPt.maxDate

          ) AS superSubPt
      );
*/