

--
-- Host: localhost    Database: university
-- ------------------------------------------------------


--
-- Table structure for table `course`
--

CREATE DATABASE university;
USE university;
CREATE TABLE `course` (
  `Cid` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(45) NOT NULL,
  `KK` int(11) NOT NULL,
  PRIMARY KEY (`Cid`),
  KEY `fk_Lesson_Professor` (`KK`),
  CONSTRAINT `fk_Course_Professor` FOREIGN KEY (`KK`) REFERENCES `professor` (`KK`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `course`
--

INSERT INTO `course` VALUES (3,'Baseis Dedomenwn',1),(4,'Domes Dedomenwn',2),(5,'Programmatismos I',3),(6,'Programmatismos II',4),(7,'Texnologies Diadiktuou',5);

--
-- Table structure for table `exam`
--

CREATE TABLE `exam` (
  `AM` int(11) NOT NULL,
  `Cid` int(11) NOT NULL,
  `Date` date NOT NULL,
  `Grade` float NOT NULL,
  PRIMARY KEY (`AM`,`Cid`,`Date`),
  KEY `fk_Exam_Course` (`Cid`),
  CONSTRAINT `fk_Exam_Course` FOREIGN KEY (`Cid`) REFERENCES `course` (`Cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Student_Exam` FOREIGN KEY (`AM`) REFERENCES `student` (`AM`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `exam`
--

INSERT INTO `exam` VALUES (1,3,'2015-10-05',6),(2,4,'2015-10-03',8),(3,5,'2014-03-03',3),(4,4,'2014-03-03',5),(5,4,'2015-10-03',2);

--
-- Table structure for table `professor`
--

CREATE TABLE `professor` (
  `KK` int(11) NOT NULL AUTO_INCREMENT,
  `Firstname` varchar(45) NOT NULL,
  `Lastname` varchar(45) NOT NULL,
  PRIMARY KEY (`KK`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `professor`
--

INSERT INTO `professor` VALUES (1,'Panos','Tzimas'),(2,'Eleni','Andriopoulou'),(3,'Nikos','Theocharopoulos'),(4,'Loukas','Mpakos'),(5,'Eleni','Anagnwstopoulou');

--
-- Table structure for table `requires`
--

CREATE TABLE `requires` (
  `Cid1` int(11) NOT NULL,
  `Cid2` int(11) NOT NULL,
  PRIMARY KEY (`Cid1`,`Cid2`),
  KEY `fk_Requires_CourseB` (`Cid2`),
  CONSTRAINT `fk_CourseA_Requires` FOREIGN KEY (`Cid1`) REFERENCES `course` (`Cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Requires_CourseB` FOREIGN KEY (`Cid2`) REFERENCES `course` (`Cid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `requires`
--

INSERT INTO `requires` VALUES (6,5);

--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `AM` int(11) NOT NULL AUTO_INCREMENT,
  `Firstname` varchar(45) NOT NULL,
  `Lastname` varchar(45) NOT NULL,
  `YearOfStudy` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`AM`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `student`
--

INSERT INTO `student` VALUES (1,'Alexandros','Theocharis','3'),(2,'Maria','Karagianni','2'),(3,'Eleni','Karaxaliou','4'),(4,'Kosmas','Pitas','4'),(5,'Nikos','Kouris','2');

--
-- Table structure for table `telephone`
--

CREATE TABLE `telephone` (
  `AM` int(11) NOT NULL,
  `TelephoneNumber` varchar(10) NOT NULL,
  PRIMARY KEY (`TelephoneNumber`,`AM`),
  KEY `fk_Telephone_Student` (`AM`),
  CONSTRAINT `fk_Telephone_Student` FOREIGN KEY (`AM`) REFERENCES `student` (`AM`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `telephone`
--

INSERT INTO `telephone` VALUES (1,'6977009988'),(2,'2763103344'),(3,'6977777777'),(4,'6980000099'),(5,'2693032400');

--
-- Table structure for table `thesis`
--

CREATE TABLE `thesis` (
  `SID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(45) NOT NULL,
  `AM` int(11) NOT NULL,
  `KK` int(11) NOT NULL,
  PRIMARY KEY (`SID`),
  KEY `fk_Tesis_Student` (`AM`),
  KEY `fk_Tesis_Professor` (`KK`),
  CONSTRAINT `fk_Tesis_Professor` FOREIGN KEY (`KK`) REFERENCES `professor` (`KK`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Tesis_Student` FOREIGN KEY (`AM`) REFERENCES `student` (`AM`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `thesis`
--


--
-- Dumping routines for database 'university'
--
/*!50003 DROP PROCEDURE IF EXISTS `new1` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new1`(inout var1 int, in grade int)
begin
case
when(grade>5)
then select count(E.Grade) into var1 from Exam as E, Course as C where E.Cid=C.Cid and C.Title="Domes Dedomenwn" and E.Grade>"5";
when(grade<5)
then select count(E.Grade) into var1 from Exam as E, Course as C where E.Cid=C.Cid and C.Title="Domes Dedomenwn" and E.Grade<"5";
end case;
end ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new11` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new11`(IN n INT)
BEGIN
  WHILE(@x<n)
  DO SELECT E.Grade INTO @x FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Domes Dedomenwn";
SET @x=@x+1;
  END WHILE;
  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new12` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new12`(IN n INT)
BEGIN
SET @x=1;
  WHILE(@x<n)
  DO SELECT E.Grade INTO @x FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Domes Dedomenwn";
SET @x=@x+1;
  END WHILE;
  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new2` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new2`(INOUT var1 INT, IN grade INT)
BEGIN
  CASE
  WHEN(grade>5)
  THEN SELECT COUNT(E.Grade) INTO var1 FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Baseis Dedomenwn" AND E.Grade>"5";
WHEN(grade<5)
  THEN SELECT COUNT(E.Grade) INTO var1 FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Baseis Dedomenwn" AND E.Grade<"5";
ELSE (SELECT COUNT(E.Grade) INTO var1 FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Baseis Dedomenwn" AND E.Grade="5");

  END CASE;
  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new3` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new3`(IN n INT)
BEGIN
  WHILE(@x<n)
  DO SELECT E.Grade INTO n FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Domes Dedomenwn" AND E.Grade<"5";
SET @x=@x+1;
  END WHILE;
  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new4` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new4`(IN n INT)
BEGIN
DECLARE x INT;
SET x=1;
  WHILE(x<n)
  DO SELECT E.Grade INTO x FROM Exam as E, Course as C WHERE E.Cid=C.Cid AND C.Title="Domes Dedomenwn";
SET x=x+1;
  END WHILE;
  END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
select * from Student where YearOfStudy="3" ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test1` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test1`(IN var1 INT)
select * from Student limit var1 ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test2` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test2`(IN var1 INT)
select * from Student where YearOfStudy="5" limit var1 ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test4` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test4`(IN var1 INT)
select max(E.Grade) from Exam as E, Course as C where E.Cid=C.Cid and C.title="Domes Dedomenwn" ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `test5` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test5`(OUT var1 INT)
select max(E.Grade) INTO var1 from Exam as E, Course as C where E.Cid=C.Cid and C.title="Domes Dedomenwn" ;;
DELIMITER ;


