--
-- 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 `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_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_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 ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_student2` */;
DELIMITER ;;;
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 ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_1b` */;
DELIMITER ;;;
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 ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_2c` */;
DELIMITER ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_2d` */;
DELIMITER ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_3a` */;
DELIMITER ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_3b` */;
DELIMITER ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_3c` */;
DELIMITER ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_4a` */;
DELIMITER ;;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `proc_4b` */;
DELIMITER ;;;
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 */;


