db_queries.txt

/*** for getting data ***/

/* get base form title, id and deadline */

SELECT * FROM `BaseForm` WHERE 1


/* get general form details for blank form - based on base form id */

SELECT  `Sec_Name` ,  `Sec_Criteria` ,  `Sec_Percent` ,  `Sec_Order` 
FROM  `Section` 
WHERE  `Section`.`BForm_ID` =  1


/* get a specific form - based on form ID */

SELECT  `Section`.`Sec_Order` , `Section`.`Sec_Name` , `Section`.`Sec_Percent` , `Section`.`Sec_Criteria` , `SectionMarking`.`Comment` , `SectionMarking`.`Mark`  
FROM  `SectionMarking` 
JOIN `Section` ON `Section`.`Sec_ID` = `SectionMarking`.`Sec_ID`
WHERE  `SectionMarking`.`Form_ID` =  1


/* get mark weight and mark of a form - based on form ID */

SELECT `Section`.`Sec_Percent` , `SectionMarking`.`Mark`
FROM  `SectionMarking` 
JOIN `Section` ON `Section`.`Sec_ID` = `SectionMarking`.`Sec_ID`
WHERE  `SectionMarking`.`Form_ID` =  1


/* get total mark for a form - based on form ID */

SELECT SUM(`Section`.`Sec_Percent`*`SectionMarking`.`Mark` / 100) AS `Total`
FROM  `SectionMarking` 
JOIN `Section` ON `Section`.`Sec_ID` = `SectionMarking`.`Sec_ID`
WHERE  `SectionMarking`.`Form_ID` =  1


/* get student name and year for a specific form - based on form ID */

SELECT `Student`.`Fname` , `Student`.`Lname` , `Student`.`Year_Level`
FROM  `MS_Form`
JOIN  `MS` ON  `MS`.`MS_ID` =  `MS_Form`.`MS_ID` 
JOIN  `Student` ON  `Student`.`Student_ID` =  `MS`.`Student_ID` 
WHERE  `MS_Form`.`Form_ID` = 1


/* get markers for a specific form - based on form ID */

SELECT  `Marker`.`Fname` ,  `Marker`.`Lname` , `MS`.`IsSupervisor`
FROM  `MS_Form`
JOIN  `MS` ON  `MS`.`MS_ID` =  `MS_Form`.`MS_ID` 
JOIN  `Marker` ON  `Marker`.`Marker_ID` =  `MS`.`Marker_ID` 
WHERE  `MS_Form`.`Form_ID` = 1


/* get form title and is submitted or is merged - based on form ID */

SELECT `BaseForm`.`Form_title` , `Form`.`IsSubmitted` , `Form`.`IsMerged`
FROM `Form` 
JOIN `BaseForm` ON `BaseForm`.`BForm_ID` = `Form`.`BForm_ID`
WHERE `Form`.`Form_ID` = 1


/* get students and their forms for a specific marker - based on marker ID */

SELECT `Form`.`Form_ID`, `BaseForm`.`Form_title`, `Student`.`Fname` , `Student`.`Lname` , `Student`.`Year_Level`
FROM  `MS_Form`
JOIN  `MS` ON  `MS`.`MS_ID` =  `MS_Form`.`MS_ID` 
JOIN  `Marker` ON  `Marker`.`Marker_ID` =  `MS`.`Marker_ID` 
JOIN  `Student` ON  `Student`.`Student_ID` =  `MS`.`Student_ID`
JOIN `Form` ON `Form`.`Form_ID` = `MS_Form`.`Form_ID`
JOIN `BaseForm` ON `BaseForm`.`BForm_ID` = `Form`.`BForm_ID`
WHERE  `Marker`.`Marker_ID` = 'hkd4hdk'


/* get students and their forms for a specific marker where the marker is a supervisor or not - based on marker ID */

SELECT `Form`.`Form_ID`, `BaseForm`.`Form_title`, `Student`.`Fname` , `Student`.`Lname` , `Student`.`Year_Level`
FROM  `MS_Form`
JOIN  `MS` ON  `MS`.`MS_ID` =  `MS_Form`.`MS_ID` 
JOIN  `Marker` ON  `Marker`.`Marker_ID` =  `MS`.`Marker_ID` 
JOIN  `Student` ON  `Student`.`Student_ID` =  `MS`.`Student_ID`
JOIN `Form` ON `Form`.`Form_ID` = `MS_Form`.`Form_ID`
JOIN `BaseForm` ON `BaseForm`.`BForm_ID` = `Form`.`BForm_ID`
WHERE  `Marker`.`Marker_ID` = 'hkd4hdk' AND `MS`.`IsSupervisor` = 1


/* get students for a specific base form - based on marker id and base form id */

SELECT `Form`.`Form_ID`, `Student`.`Fname` , `Student`.`Lname` , `Student`.`Year_Level`
FROM  `MS_Form`
JOIN  `MS` ON  `MS`.`MS_ID` =  `MS_Form`.`MS_ID` 
JOIN  `Marker` ON  `Marker`.`Marker_ID` =  `MS`.`Marker_ID` 
JOIN  `Student` ON  `Student`.`Student_ID` =  `MS`.`Student_ID`
JOIN `Form` ON `Form`.`Form_ID` = `MS_Form`.`Form_ID`
JOIN `BaseForm` ON `BaseForm`.`BForm_ID` = `Form`.`BForm_ID`
WHERE  `Marker`.`Marker_ID` = 'hkd4hdk' AND `BaseForm`.`BForm_ID` = 1


/* get markers for a merged form - based on merged from id */

SELECT `Marker`.`Fname`, `Marker`.`Lname`
FROM `MergedForm`
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `MergedForm`.`EForm_ID` OR `MS_Form`.`Form_ID` = `MergedForm`.`SForm_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
JOIN `Marker` ON `Marker`.`Marker_ID` = `MS`.`Marker_ID`
WHERE `MergedForm`.`MForm_ID` = 8


/* get student for a merged form - based on merged from id */

SELECT `Student`.`Fname` , `Student`.`Lname` , `Student`.`Year_Level`
FROM `MergedForm`
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `MergedForm`.`EForm_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
JOIN  `Student` ON  `Student`.`Student_ID` =  `MS`.`Student_ID` 
WHERE `MergedForm`.`MForm_ID` = 9


/* get base form id, student id and if a marker is supervisor - based on form id */

SELECT `Form`.`BForm_ID`, `Student`.`Student_ID`, `MS`.`IsSupervisor`
FROM `Form` 
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `Form`.`Form_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
JOIN `Student` ON `Student`.`Student_ID` = `MS`.`Student_ID`
WHERE `Form`.`Form_ID` = 1


/* get the form ID of the other marker if he has submitted his form - based on student id, base form id, form id, and isSupervisor */

SELECT `Form`.`Form_ID`
FROM `Form` 
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `Form`.`Form_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
JOIN `Student` ON `Student`.`Student_ID` = `MS`.`Student_ID`
WHERE `Student`.`Student_ID` = 'cnng04' AND `Form`.`BForm_ID` = 1 
AND `Form`.`Form_ID` != 1 AND `Form`.`IsSubmitted` = 1 AND `MS`.`IsSupervisor` != 1
ORDER BY `Form`.`Time_Stamp` DESC


/* get the merged form id - based on a form id that is part of the merged form */

SELECT `MForm_ID` 
FROM `MergedForm` 
WHERE `EForm_ID` = 1 OR `SForm_ID` = 1


/* Returns the sections causing conflict in a merged form - based on section form ID */

SELECT `Sec_ID` 
FROM `SectionConflict` 
WHERE `Form_ID` = 8


/* Returns 1 if the current marker is the supervisor for this merged form, 0 if examiner */

SELECT `MS`.`IsSupervisor` 
FROM `MergedForm`
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `MergedForm`.`EForm_ID` OR `MS_Form`.`Form_ID` = `MergedForm`.`SForm_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
WHERE `MergedForm`.`MForm_ID` = 8 AND `MS`.`Marker_ID` = 'hkd4hdk'


/* check if a form is edited - based on merged form id */

SELECT `IsEdited` 
FROM `MergedForm` 
WHERE `MForm_ID` = 8


/* to find un-used merged form id */

SELECT `Form_ID`
FROM `Form`
LEFT JOIN `MergedForm` ON `MergedForm`.`MForm_ID` = `Form`.`Form_ID`
WHERE `IsMerged` = -1 AND `MergedForm`.`MForm_ID` IS NULL AND `BForm_ID` = 1
ORDER BY `Form_ID` ASC
LIMIT 1


/* Get all completed forms sorted by studentID */

SELECT `Student`.`Student_ID`, `Student`.`Fname`, `Student`.`Lname`, `Form`.`Form_ID`, `BaseForm`.`Form_Title`
FROM `Form`
JOIN `BaseForm` ON `BaseForm`.`BForm_ID` = `Form`.`BForm_ID`
JOIN `MergedForm` ON `MergedForm`.`MForm_ID` = `Form`.`Form_ID`
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `MergedForm`.`EForm_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
JOIN `Student` ON `Student`.`Student_ID` = `MS`.`Student_ID`
WHERE `Form`.`IsSubmitted` = 1 AND `Form`.`IsMerged` = -1
ORDER BY `Student`.`Student_ID` ASC


/*** for adding data ***/

/* inserting a comment and mark of a specific section - based on its order and the form ID */

INSERT INTO `SectionMarking`(`Sec_ID`, `Form_ID`, `Comment`, `Mark`) 
SELECT `Section`.`Sec_ID`, 1 , 'bad', 50.0
FROM `Section`
JOIN `Form` ON `Form`.`BForm_ID` = `Section`.`BForm_ID`
WHERE `Form`.`Form_ID` = 1 AND `Section`.`Sec_Order` = 1

--> for inserting multiple values at once

INSERT INTO `SectionMarking`(`Sec_ID`, `Form_ID`, `Comment`, `Mark`) 
SELECT `Section`.`Sec_ID`, 2 , 'bad', 50.0
FROM `Section`
JOIN `Form` ON `Form`.`BForm_ID` = `Section`.`BForm_ID`
WHERE `Form`.`Form_ID` = 2 AND `Section`.`Sec_Order` = 1
UNION ALL
SELECT `Section`.`Sec_ID`, 2 , 'GOOD', 60.0
FROM `Section`
JOIN `Form` ON `Form`.`BForm_ID` = `Section`.`BForm_ID`
WHERE `Form`.`Form_ID` = 2 AND `Section`.`Sec_Order` = 2


/* update a section - based on the section order and form ID */

UPDATE `SectionMarking` 
JOIN `Section` ON `Section`.`Sec_ID` = `SectionMarking`.`Sec_ID`
SET `SectionMarking`.`Comment` = 'Good job!',`SectionMarking`.`Mark`= 80 
WHERE `SectionMarking`.`Form_ID` = 1 AND `Section`.`Sec_Order` = 1


/* update submit form flag - based on form id */

UPDATE `Form` 
SET `IsSubmitted`= 1, `Time_Stamp` = NOW()
WHERE `Form_ID` = 1

/* insert a new merged form - based on examiner form id and supervisor form id */

INSERT INTO `Form`(`BForm_ID`, `IsMerged`) 
SELECT `Form`.`BForm_ID`, -1
FROM `Form`
WHERE `Form`.`Form_ID` = 3;

INSERT INTO `MergedForm`(`MForm_ID`,`EForm_ID`,`SForm_ID`)
SELECT LAST_INSERT_ID(), 3, 1;

SET group_concat_max_len = 65535;

INSERT INTO `SectionMarking`(`Sec_ID`, `Form_ID`, `Comment`, `Mark`) 
SELECT `Sec_ID`, LAST_INSERT_ID() , GROUP_CONCAT(`Comment` SEPARATOR ' '), AVG(`Mark`)
FROM (
	SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 3
    UNION ALL
    SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 1
) AS B
GROUP BY `Sec_ID`


/* insert conflicted section into the sectionConflict table - based on merged form id and an examiner or supervisor form id */

INSERT INTO `SectionConflict`(`Form_ID`,`Sec_ID`)
SELECT s1.`Form_ID`,s1.`Sec_ID`
FROM (SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 39) s1
INNER JOIN (SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 1) s2 ON s2.`Sec_ID` = s1.`Sec_ID`
WHERE s1.`Form_ID` = 39 AND s2.`Form_ID`= 3 AND s1.`Mark` - s2.`Mark` > 5 OR s2.`Mark` - s1.`Mark` > 5


/* duplicate a form - based on form id */

INSERT INTO `Form`(`BForm_ID`, `IsSubmitted`, `IsMerged`, `Comment`, `Time_Stamp`) 
SELECT `BForm_ID`, `IsSubmitted`, `IsMerged`, `Comment`, `Time_Stamp`
FROM `Form`
WHERE `Form_ID` = 3;

INSERT INTO `MS_Form`(`MS_ID`, `Form_ID`)
SELECT `MS_ID`, LAST_INSERT_ID()
FROM `MS_Form`
WHERE `Form_ID` = 3;

INSERT INTO `SectionMarking`(`Sec_ID`, `Form_ID`, `Comment`, `Mark`) 
SELECT `Sec_ID`, LAST_INSERT_ID() , `Comment`, `Mark`
FROM (
	SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 3
) AS B
GROUP BY `Sec_ID`


/* update a merged form - based on merged form id */

INSERT INTO `SectionMarking`(`Sec_ID`, `Form_ID`, `Comment`, `Mark`) 
SELECT `Sec_ID`, 39 , GROUP_CONCAT(`Comment` SEPARATOR ' '), AVG(`Mark`)
FROM (
	SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 3
    UNION ALL
    SELECT `Sec_ID`, `Form_ID`, `Comment`, `Mark` FROM `SectionMarking` WHERE `Form_ID` = 1
) AS B
GROUP BY `Sec_ID`
ON DUPLICATE KEY UPDATE
 `Sec_ID` = VALUES(`Sec_ID`), `Comment` = VALUES(`Comment`), `Mark`= VALUES(`Comment`)

/* add/update a student */

INSERT INTO `Student`(`Student_ID`, `Fname`, `Lname`, `Year_Level`) 
VALUES ('knlb5j','Luke','McBeth',3)
ON DUPLICATE KEY UPDATE
 `Fname` = VALUES(`Fname`), `Lname` = VALUES(`Lname`), `Year_Level`= VALUES(`Year_Level`)


/* add/update marker */

INSERT INTO `Marker`(`Marker_ID`, `Fname`, `Lname`) 
VALUES ('lmlhi6','Mark','Million')
ON DUPLICATE KEY UPDATE
 `Fname` = VALUES(`Fname`), `Lname` = VALUES(`Lname`)


/* connect marker and student - based on marker and student id */

INSERT INTO `MS`(`Marker_ID`, `Student_ID`, `IsSupervisor`) 
VALUES ('lmlhi6','knlb5j',1)


/* create a blank form for each base */

INSERT INTO `Form`(`BForm_ID`) 
SELECT `BForm_ID`
FROM `BaseForm`


/* connect the marker-student pair to the 5 form types (forms previously created but not used) - based on student and marker id */

INSERT INTO `MS_Form`(`MS_ID`, `Form_ID`) 
SELECT 
(
    SELECT `MS`.`MS_ID`
    FROM `MS`
    LEFT JOIN `MS_Form` ON `MS_Form`.`MS_ID` = `MS`.`MS_ID`
    WHERE `MS_Form`.`MS_ID` IS NULL AND `MS`.`Marker_ID` = 'lmlhi6' AND `MS`.`Student_ID` = 'knlb5j'
) M , `Form_ID`
FROM
(
    SELECT `Form`.`Form_ID`, `Form`.`BForm_ID`
	FROM `Form`
	LEFT JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `Form`.`Form_ID`
	WHERE `MS_Form`.`Form_ID` IS NULL AND `IsMerged` = 0
    GROUP BY `Form`.`BForm_ID`
) B


/* add the sections to the forms created for the marker-student pair - base on marker and student ids */

INSERT INTO `SectionMarking`(`Sec_ID`, `Form_ID`)
SELECT `Section`.`Sec_ID`, `Form`.`Form_ID`
FROM `Form`
JOIN `Section` ON `Section`.`BForm_ID` = `Form`.`BForm_ID`
JOIN `MS_Form` ON `MS_Form`.`Form_ID` = `Form`.`Form_ID`
JOIN `MS` ON `MS`.`MS_ID` = `MS_Form`.`MS_ID`
WHERE `MS`.`Marker_ID` = 'lmlhi6' AND `MS`.`Student_ID` = 'knlb5j'