--
-- Host: localhost    Database: IT00001
-- ------------------------------------------------------


--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8_latvian_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `courses`
--


--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `log` varchar(255) COLLATE utf8_latvian_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `log`
--

INSERT INTO `log` VALUES ('Student was created'),('Student was created'),('Student was created'),('Student was created');

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `surname` varchar(100) COLLATE utf8_latvian_ci NOT NULL,
  `name` varchar(100) COLLATE utf8_latvian_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` VALUES (1,'Name1','Surname1'),(2,'Name2','Surname2'),(3,'Name3','Surname3'),(4,'Name4','Surname4');
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER students_log
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO log VALUES ("Student was created");
END */;;
DELIMITER ;

--
-- Table structure for table `students_courses`
--

CREATE TABLE `students_courses` (
  `students_id` int(11) NOT NULL,
  `courses_id` int(11) NOT NULL,
  PRIMARY KEY (`students_id`,`courses_id`),
  KEY `fk_students_has_courses_courses1` (`courses_id`),
  CONSTRAINT `fk_students_has_courses_courses1` FOREIGN KEY (`courses_id`) REFERENCES `courses` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_students_has_courses_students` FOREIGN KEY (`students_id`) REFERENCES `students` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_latvian_ci;

--
-- Dumping data for table `students_courses`
--


--
-- Dumping routines for database 'IT00001'
--
/*!50003 DROP PROCEDURE IF EXISTS `create_students` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `create_students`()
BEGIN
DECLARE id INT;

DECLARE name VARCHAR(50);

DECLARE surname VARCHAR(50);
SET id = 1;
DELETE FROM students;
WHILE id < 5 DO

    SET name = CONCAT ("Name", id);
    SET surname = CONCAT ("Surname", id);
    INSERT INTO students VALUES (id, name, surname);
    SET id = id + 1;
END WHILE;
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p1` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `p1`()
SELECT * FROM students */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p7` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `p7`(INOUT incr_param INT)
BEGIN
  
  SET incr_param = incr_param + 1;
END */;;
DELIMITER ;


