--
-- Host: localhost    Database: demo4
-- ------------------------------------------------------


--
-- Table structure for table `address`
--

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) DEFAULT NULL,
  `inst_id` int(11) DEFAULT NULL,
  `street1` varchar(50) NOT NULL,
  `street2` varchar(50) DEFAULT NULL,
  `city` varchar(25) NOT NULL,
  `state` char(2) DEFAULT NULL,
  `country` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`address_id`),
  KEY `addr_stu_idx` (`stu_id`),
  KEY `addr_inst_idx` (`inst_id`),
  CONSTRAINT `inst_id_fk` FOREIGN KEY (`inst_id`) REFERENCES `instructor` (`instructor_id`),
  CONSTRAINT `stu_id_fk` FOREIGN KEY (`stu_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `address`
--

INSERT INTO `address` VALUES (1,NULL,1,'48 Cooper Square','','New York','NY',''),(2,15,NULL,'828 Eight Avenue',NULL,'New York','NY',''),(3,3,NULL,'222 Second Avenue',NULL,'Brooklyn','NY',''),(4,NULL,3,'5 West 4th Street','Apt 44','New York','NY',''),(5,5,NULL,'323 Third Avenue',NULL,'Bronx','NY',''),(6,7,NULL,'424 Forth Avenue',NULL,'New York','NY',''),(7,9,NULL,'525 Fifth Avenue',NULL,'Yonkers','NY',''),(8,1,NULL,'121 First Avenue',NULL,'New York','NY',''),(9,11,NULL,'626 Sixth Avenue',NULL,'Hoboken','NJ',''),(10,13,NULL,'727 Seventh Avenue',NULL,'New York','NY',''),(11,17,NULL,'929 Ninth Avenue',NULL,'Brooklyn','NY',''),(12,19,NULL,'101 Tenth Avenue',NULL,'Newark','NJ',''),(13,NULL,5,'48 East 42 Street',NULL,'New York','NY',''),(14,21,NULL,'1 Ontario Drive',NULL,'Toronto',NULL,'Canada');

--
-- Table structure for table `class`
--

CREATE TABLE `class` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` char(8) NOT NULL,
  `session_id` int(11) NOT NULL,
  `inst_ssn` char(11) NOT NULL,
  `stu_ssn` char(11) NOT NULL,
  PRIMARY KEY (`class_id`),
  UNIQUE KEY `course_uniq` (`course_id`,`inst_ssn`,`stu_ssn`),
  KEY `class_course_idx` (`course_id`),
  KEY `class_inst_idx` (`inst_ssn`),
  KEY `class_stu_idx` (`stu_ssn`),
  CONSTRAINT `course_fk` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`),
  CONSTRAINT `inst_ssn_fk` FOREIGN KEY (`inst_ssn`) REFERENCES `instructor` (`ssn`),
  CONSTRAINT `stu_ssn_fk` FOREIGN KEY (`stu_ssn`) REFERENCES `student` (`ssn`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class`
--

INSERT INTO `class` VALUES (1,'X52-9272',1,'000-02-0001','000-01-0002'),(2,'X52-9272',1,'000-02-0001','000-01-0004'),(3,'X52-9272',1,'000-02-0001','000-01-0006'),(4,'X52-9272',1,'000-02-0001','000-01-0008'),(5,'X52-9272',1,'000-02-0001','000-01-0010'),(6,'X52-9272',1,'000-02-0001','000-01-0012'),(7,'X52-9272',1,'000-02-0001','000-01-0014'),(8,'X52-9272',1,'000-02-0001','000-01-0016'),(9,'X52-9272',1,'000-02-0001','000-01-0018'),(10,'X52-9272',1,'000-02-0001','000-01-0020'),(11,'X52-9272',1,'000-02-0001','000-01-0022'),(12,'X52-9759',1,'000-02-0001','000-01-0001'),(13,'X52-9759',1,'000-02-0001','000-01-0003'),(14,'X52-9759',1,'000-02-0001','000-01-0005'),(15,'X52-9759',1,'000-02-0001','000-01-0007'),(16,'X52-9759',1,'000-02-0001','000-01-0008'),(17,'X52-9759',1,'000-02-0001','000-01-0010'),(18,'X52-9759',1,'000-02-0001','000-01-0013'),(19,'X52-9759',1,'000-02-0001','000-01-0015'),(20,'X52-9759',1,'000-02-0001','000-01-0017'),(21,'X52-9759',1,'000-02-0001','000-01-0019'),(22,'X52-9740',1,'000-02-0002','000-01-0001'),(23,'X52-9740',1,'000-02-0002','000-01-0002'),(24,'X52-9740',1,'000-02-0002','000-01-0003'),(25,'X52-9740',1,'000-02-0002','000-01-0004'),(26,'X52-9740',1,'000-02-0002','000-01-0005'),(27,'X52-9740',1,'000-02-0002','000-01-0006'),(28,'X52-9740',1,'000-02-0002','000-01-0007'),(29,'X52-9740',1,'000-02-0002','000-01-0008'),(30,'X52-9740',1,'000-02-0002','000-01-0009'),(31,'X52-9740',1,'000-02-0002','000-01-0010'),(32,'X52-9740',2,'000-02-0003','000-01-0011'),(33,'X52-9740',2,'000-02-0003','000-01-0012'),(34,'X52-9740',2,'000-02-0003','000-01-0013'),(35,'X52-9740',2,'000-02-0003','000-01-0014'),(36,'X52-9740',2,'000-02-0003','000-01-0015'),(37,'X52-9740',2,'000-02-0003','000-01-0016'),(38,'X52-9740',2,'000-02-0003','000-01-0017'),(39,'X52-9740',2,'000-02-0003','000-01-0018'),(40,'X52-9740',2,'000-02-0003','000-01-0019'),(41,'X52-9740',2,'000-02-0003','000-01-0020'),(42,'X52-9740',2,'000-02-0003','000-01-0021'),(43,'X52-9740',2,'000-02-0003','000-01-0022'),(44,'X52-9755',1,'000-02-0006','000-01-0003'),(45,'X52-9755',1,'000-02-0006','000-01-0006'),(46,'X52-9755',1,'000-02-0006','000-01-0009'),(47,'X52-9755',1,'000-02-0006','000-01-0012'),(48,'X52-9755',1,'000-02-0006','000-01-0015'),(49,'X52-9755',1,'000-02-0006','000-01-0018'),(50,'X52-9755',1,'000-02-0006','000-01-0021');

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

CREATE TABLE `course` (
  `course_id` char(8) NOT NULL,
  `description` varchar(40) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `course` VALUES ('X52-9238','Introduction to Java',1095),('X52-9267','Object Oriented Analysis and Design',995),('X52-9272','SQL Programming Language',540),('X52-9562','Java web Services',1095),('X52-9740','Web Page Development with HTML',1095),('X52-9742','Intensive Web Development',3995),('X52-9755','JavaScript',1095),('X52-9759','XML for Web Development',1095);

--
-- Table structure for table `hierarchy`
--

CREATE TABLE `hierarchy` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT,
  `lname` varchar(20) NOT NULL,
  `fname` varchar(20) NOT NULL,
  `manager_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `hier_name_idx` (`lname`,`fname`),
  KEY `hier_mgr_fk` (`manager_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `hierarchy`
--

INSERT INTO `hierarchy` VALUES (1,'Burns','Barbara',NULL),(2,'Tobias','Wayne',1),(3,'Stack','Patrick',1),(4,'Milgrom','Anya',2),(5,'Vasquez','Lilian',4),(6,'Davidson','Duncan',4);

--
-- Table structure for table `instructor`
--

CREATE TABLE `instructor` (
  `instructor_id` int(11) NOT NULL AUTO_INCREMENT,
  `lname` varchar(20) NOT NULL,
  `fname` varchar(20) NOT NULL,
  `ssn` char(11) NOT NULL,
  `sex` char(1) NOT NULL,
  PRIMARY KEY (`instructor_id`),
  UNIQUE KEY `ssn` (`ssn`),
  KEY `inst_name_idx` (`lname`,`fname`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `instructor`
--

INSERT INTO `instructor` VALUES (1,'Sultan','Sam','000-02-0001','M'),(2,'Pefanis','George','000-02-0002','M'),(3,'Martin','Susan','000-02-0003','F'),(4,'Paller','Marc','000-02-0004','M'),(5,'Lee','Mary','000-02-0005','F'),(6,'Katz','Eric','000-02-0006','M');

--
-- Table structure for table `instructor_info`
--

CREATE TABLE `instructor_info` (
  `instructor_id` int(11) NOT NULL,
  `active_status` char(1) DEFAULT 'A',
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `specialty` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`instructor_id`),
  CONSTRAINT `inst_info_fk` FOREIGN KEY (`instructor_id`) REFERENCES `instructor` (`instructor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `instructor_info`
--

INSERT INTO `instructor_info` VALUES (1,'A','1999-09-01 04:00:00','Web technology, Java, XML, SQL'),(2,'A','2000-02-15 05:00:00','ASP, .NET, HTML'),(3,'A','1998-07-01 04:00:00',NULL),(4,'A','1995-01-01 05:00:00','Oracle, DB2'),(5,'I','2003-03-25 05:00:00',NULL),(6,'A','2001-05-15 04:00:00','JavaScript, C, Flash');

--
-- Table structure for table `payment`
--

CREATE TABLE `payment` (
  `payment_num` int(11) NOT NULL AUTO_INCREMENT,
  `vendor` varchar(40) NOT NULL,
  `amount` decimal(6,2) DEFAULT '0.00',
  `description` varchar(40) NOT NULL,
  `pay_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_num`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `payment`
--

INSERT INTO `payment` VALUES (1,'Con Edison','125.15','Electric','2010-02-01 05:00:00'),(2,'Verizon','54.79','Home Phone','2010-03-01 05:00:00'),(3,'Allstate','1240.50','Home Insurance','2010-03-02 05:00:00'),(4,'Verizon','49.95','Cell Phone','2010-03-03 05:00:00'),(5,'Verizon','39.95','DSL Line','2010-03-04 05:00:00'),(6,'Con Edison','59.63','Electric','2010-03-05 05:00:00'),(7,'Department of Water','155.43','Water and Sewers','2010-03-06 05:00:00'),(8,'Allstate','1550.25','Car Insurance','2010-03-07 05:00:00');

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

CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `lname` varchar(20) NOT NULL,
  `fname` varchar(20) NOT NULL,
  `ssn` char(11) NOT NULL,
  `sex` enum('F','M') NOT NULL,
  PRIMARY KEY (`student_id`),
  UNIQUE KEY `stu_ssn_uq` (`ssn`),
  KEY `stu_name_idx` (`lname`,`fname`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

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

INSERT INTO `student` VALUES (1,'Burns','Barbara','000-01-0001','F'),(2,'Cambria','Vincent','000-01-0002','M'),(3,'Davidson','Duncan','000-01-0003','M'),(4,'Smith','David','000-01-0004','M'),(5,'Thomas','Eugene','000-01-0005','M'),(6,'Owens','Cynthia','000-01-0006','F'),(7,'Willis','Eileen','000-01-0007','F'),(8,'Myers','Rick','000-01-0008','M'),(9,'Ryan','Natasha','000-01-0009','F'),(10,'Stack','Patrick','000-01-0010','M'),(11,'Tobias','Wayne','000-01-0011','M'),(12,'Race','Joseph','000-01-0012','M'),(13,'Nelson','Colette','000-01-0013','F'),(14,'Brinson','Angel','000-01-0014','F'),(15,'Soley','John','000-01-0015','M'),(16,'Grace','Robert','000-01-0016','M'),(17,'Tok','Kathy','000-01-0017','F'),(18,'Miller','Janet','000-01-0018','F'),(19,'Austin','Maria','000-01-0019','F'),(20,'Teagan','Edward','000-01-0020','M'),(21,'Milgrom','Anya','000-01-0021','F'),(22,'Vasquez','Lillian','000-01-0022','F'),(23,'Chan','David','000-01-0023','M'),(24,'Crandall','Phyllis','000-01-0024','F'),(25,'Ramos','Michael','000-01-0025','M'),(26,'Sultan','Samuel','000-01-0026','M');

--
-- Table structure for table `student_email`
--

CREATE TABLE `student_email` (
  `email_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`email_id`),
  KEY `stu_email_idx` (`student_id`),
  CONSTRAINT `stu_email_fk` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student_email`
--

INSERT INTO `student_email` VALUES (1,1,'barbara.burns@nyu.edu'),(2,2,'vcambria@yahoo.com'),(3,3,'duncan.davidson@nyu.edu'),(4,3,'davidson123@aol.com'),(5,5,'ethomas@xyz.com'),(6,8,'rick.myers@nyu.edu'),(7,8,'rick.myers@abc.com'),(8,8,'myers101@aol.com'),(9,12,'joerace321@aolc.om'),(10,15,'soleyj@nyu.edu');

--
-- Table structure for table `test_table`
--

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `data` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test_table`
--


--
-- Temporary table structure for view `v_key_column`
--

/*!50001 DROP VIEW IF EXISTS `v_key_column`*/;
/*!50001 CREATE TABLE `v_key_column` (
  `table_name` varchar(64),
  `column_name` varchar(64),
  `constraint_type` varchar(73),
  `constraint_name` varchar(64),
  `ordinal_position` bigint(10),
  `referenced_table_name` varchar(64),
  `referenced_column_name` varchar(64)
) ENGINE=MyISAM */;

--
-- Dumping routines for database 'demo4'
--
/*!50003 DROP FUNCTION IF EXISTS `add_bus_day` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 FUNCTION `add_bus_day`(in_date DATE, num_days INT) RETURNS date
BEGIN  
    DECLARE v_future_day DATE;
-- 
    SELECT TIMESTAMPADD(DAY, num_days + FLOOR((DAYOFWEEK(in_date) + (num_days*7/5)) / 7) * 2, in_date)  
           INTO v_future_day;
    RETURN v_future_day;
END */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `hello` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 FUNCTION `hello`(input VARCHAR(20)) RETURNS varchar(30) CHARSET latin1
RETURN CONCAT('Hello ', input) */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `hello2` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 FUNCTION `hello2`(input VARCHAR(20)) RETURNS varchar(30) CHARSET latin1
BEGIN
    DECLARE v_string VARCHAR(30);
    SET     v_string = CONCAT('Hello ', input);
    RETURN  v_string;
END */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `last_friday` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 FUNCTION `last_friday`(in_date DATE) RETURNS date
BEGIN
    DECLARE v_last_friday DATE;
    SET v_last_friday =  
        CASE DAYOFWEEK(LAST_DAY(NOW())) 
            WHEN 1 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 2 DAY)	
            WHEN 2 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 3 DAY)	
            WHEN 3 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 4 DAY)	
            WHEN 4 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 5 DAY)	
            WHEN 5 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 6 DAY)	
            WHEN 6 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 0 DAY)	
            WHEN 7 THEN DATE_SUB(LAST_DAY(NOW()), INTERVAL 1 DAY)	
    END;
    RETURN v_last_friday;
END */;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `last_friday2` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 FUNCTION `last_friday2`(in_date DATE) RETURNS date
BEGIN
    DECLARE v_last_day    DATE;		
    DECLARE v_day_offset  INT;		
    DECLARE v_last_friday DATE;
-- 
    SELECT LAST_DAY(in_date)     into v_last_day;	
    SELECT DAYOFWEEK(v_last_day) into v_day_offset;	
-- 
    SET v_day_offset = IF(v_day_offset>=6, v_day_offset-6, v_day_offset+1);   
    SELECT TIMESTAMPADD(DAY, -v_day_offset , v_last_day) into v_last_friday;
    RETURN v_last_friday;
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_student` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `get_student`(param_student_id INT)
BEGIN
    DECLARE v_fname	VARCHAR(30); 		--  local variables
    DECLARE v_lname	VARCHAR(30); 
    DECLARE v_ssn	VARCHAR(30); 
-- 
    SELECT fname, lname, ssn 
      INTO v_fname, v_lname, v_ssn
      FROM student
     WHERE student_id = param_student_id; 
-- 
    SELECT v_fname, v_lname, v_ssn;		--  select is inside the proc 
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_student2` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `get_student2`(param_student_id INT)
BEGIN
    SELECT fname, lname, ssn 
      INTO  @v_fname, @v_lname, @v_ssn		--  global variables
      FROM student
     WHERE student_id=param_student_id; 
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `median` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `median`()
BEGIN 
    SELECT FLOOR(COUNT(*)/2) INTO @offset FROM payment;		
-- 
    PREPARE MedianStmt FROM 
        "SELECT * FROM payment					  
         ORDER BY amount					  
         LIMIT ?,1"; 
-- 
    EXECUTE MedianStmt USING @offset; 
-- 
    DEALLOCATE PREPARE MedianStmt; 
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_1a` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_1a`()
BEGIN
    INSERT INTO student
           (student_id,lname,fname,ssn,sex)
    VALUES (null,'Sultan','Sam','000-01-0026','M');
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_1b` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_1b`(last VARCHAR(20), first VARCHAR(20))
BEGIN
    UPDATE student
    SET   fname=first
    WHERE lname=last;
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_1c` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_1c`( p_id INT )
BEGIN
        DECLARE i INT DEFAULT 1;        
        SET autocommit=0;
        DROP TABLE IF EXISTS test_table;            
        CREATE TABLE test_table 
        (
                id   INT PRIMARY KEY,
                data VARCHAR(30)
        )
        ENGINE=innodb;
        WHILE (i<=10) DO
                INSERT INTO test_table VALUES(i,CONCAT("record ",i));
                SET i=i+1;
        END WHILE;  
        SET i=p_id;
        UPDATE test_table                           
           SET data=CONCAT("I updated row ",i)
         WHERE id=i;
        DELETE FROM test_table                       
         WHERE id>i; 
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_2a` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_2a`()
BEGIN
    SELECT 'Hello World, this is my first procedure' AS "Greeting";
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_2b` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_2b`()
BEGIN
    SELECT 'total number of students', COUNT(*)
      FROM student
     WHERE fname = 'David';
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_2c` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_2c`(id_param INT)
BEGIN
    SELECT student_id, fname, lname, sex
      FROM student
     WHERE student_id >= id_param;
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_2d` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_2d`(IN lname_param VARCHAR(20))
BEGIN
    SELECT *
      FROM student
     WHERE lname like lname_param
       AND sex = 'M';
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_3a` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_3a`()
BEGIN
    DECLARE text_string   VARCHAR(50);
    DECLARE student_count INTEGER;
-- 
    SELECT 'total number of students', COUNT(*)
      INTO text_string, student_count
      FROM student
     WHERE fname = 'David';
-- 
    SELECT text_string, CONCAT('is... ', student_count) AS num_of_students;
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_3b` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_3b`(name_param VARCHAR(20))
BEGIN
    DECLARE student_count INTEGER;
-- 
    SELECT COUNT(*)
      INTO student_count
      FROM student
     WHERE lname LIKE name_param
        OR fname LIKE name_param;
-- 
    SELECT CONCAT('Num of students matching ',name_param,' is: ',student_count) 
           AS num_of_students; 
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_3c` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_3c`(IN lname_param VARCHAR(20), OUT result_param INT)
BEGIN
    SELECT COUNT(*)
      INTO result_param
      FROM student
     WHERE lname like lname_param;
 END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_4a` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_4a`()
BEGIN
    DECLARE done    INT DEFAULT 0;
    DECLARE v_id    INT;
    DECLARE v_fname VARCHAR(20);
    DECLARE v_lname VARCHAR(20);
    DECLARE v_ssn   VARCHAR(12);
    DECLARE v_sex   CHAR(1);
    DECLARE cursor1 CURSOR FOR 
        SELECT * FROM student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    OPEN cursor1;
L1: LOOP
        FETCH cursor1 INTO v_id,v_fname,v_lname,v_ssn,v_sex;
        IF done=1 THEN
            LEAVE L1;
        END IF;
    END LOOP;
    CLOSE cursor1;
-- 
    SELECT v_id, v_fname, v_lname, v_ssn, v_sex;
END */;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_4b` */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`demo4`@`localhost`*/ /*!50003 PROCEDURE `proc_4b`(p_first varchar(20), p_last varchar(20))
BEGIN
    DECLARE done_inst  INT DEFAULT 0;
    DECLARE done_stu   INT DEFAULT 0;
    DECLARE fi,li,ssn1 VARCHAR(20);
    DECLARE fs,ls      VARCHAR(20);
    DECLARE result     VARCHAR(5000) DEFAULT '';
    DECLARE cur1 CURSOR FOR 
        SELECT fname, lname, ssn  
          FROM instructor
         ORDER BY 2,1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_inst=1;
-- 
    OPEN cur1;
L1: LOOP					-- loop thru the instructors
        FETCH cur1 INTO fi,li,ssn1;
        IF done_inst THEN
            LEAVE L1;
        END IF;
        SET result=concat(result,'<b><u>Instructor: ',fi,' ',li,'</u></b><br>');
-- 
        IF (fi=p_first AND li=p_last) THEN	-- if the instructor from param
            BEGIN
                DECLARE cur2 CURSOR FOR 
                SELECT distinct fname, lname
                  FROM student, class
                 WHERE ssn=stu_ssn
                   AND inst_ssn=ssn1            -- ssn1 is a variable from cur1
                 ORDER BY 2,1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_stu=1;
-- 
                OPEN cur2;
L2:             LOOP				-- loop thru the students
                    FETCH cur2 INTO fs,ls;
                    IF done_stu THEN
                        LEAVE L2;
                    END IF;
                    SET result=concat(result,fs,' ',ls,'<br>');
                END LOOP;
                CLOSE cur2;
            END;
        END IF;
    END LOOP;
    CLOSE cur1;
-- 
    SET @result = result;                       -- make it a gloval variable
END */;;
DELIMITER ;

--
-- Final view structure for view `v_key_column`
--

/*!50001 DROP TABLE IF EXISTS `v_key_column`*/;
/*!50001 DROP VIEW IF EXISTS `v_key_column`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo4`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_key_column` AS select `k`.`TABLE_NAME` AS `table_name`,`k`.`COLUMN_NAME` AS `column_name`,replace(`t`.`CONSTRAINT_TYPE`,'PRIMARY',' PRIMARY') AS `constraint_type`,`k`.`CONSTRAINT_NAME` AS `constraint_name`,`k`.`ORDINAL_POSITION` AS `ordinal_position`,`k`.`REFERENCED_TABLE_NAME` AS `referenced_table_name`,`k`.`REFERENCED_COLUMN_NAME` AS `referenced_column_name` from (`information_schema`.`table_constraints` `t` join `information_schema`.`key_column_usage` `k`) where ((`t`.`TABLE_SCHEMA` = `k`.`TABLE_SCHEMA`) and (`t`.`TABLE_NAME` = `k`.`TABLE_NAME`) and (`t`.`CONSTRAINT_NAME` = `k`.`CONSTRAINT_NAME`)) order by 1,3,2,4,5 */;


