-- MySQL dump 10.14  Distrib 5.5.43-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: demo
-- ------------------------------------------------------
-- Server version	5.5.43-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `addrbook`
--

DROP TABLE IF EXISTS `addrbook`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `addrbook` (
  `lname` varchar(20) NOT NULL,
  `fname` varchar(20) NOT NULL,
  `street` varchar(50) NOT NULL,
  `city` varchar(20) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` char(5) NOT NULL,
  `phone` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `addrbook`
--

LOCK TABLES `addrbook` WRITE;
/*!40000 ALTER TABLE `addrbook` DISABLE KEYS */;
INSERT INTO `addrbook` VALUES ('Anile','Joseph','35 Forest Avenue','Glen Cove','NY','12345','(516)937-4021'),('Brown','Robert','96 Landing Road','Brooklyn','NY','12345','(718)921-7505'),('Colin','Albert','64 Lisa Drive','Plainview','NY','12345','(516)671-2895'),('Davies','Todd','129 Georgia Street','Forest Hills','NY','12345','(718)496-3717'),('Eisner','Jack','6 Sunset Drive','East Norwich','CT','12345','(215)759-1976'),('Field','Diane','254 West 79 Street','New York','NY','12345','(212)647-9372'),('Gambi','Teresa','12 Cypress Avenue','Syosset','NY','12345','(516)299-5478'),('Hart','Richard','31 Cherry Lane','Hicksville','NY','12345','(516)679-7920'),('Izzo','Anthony','342 Coleridge Drive','Locus Grove','NY','12345','(516)367-3066'),('Johnson','Carl','8 Bayview Drive','Bayville','NY','12345','(516)299-7643'),('Sullivan','John','22 Oakdale Avenue','Huntington','NY','12345','(516)453-3239'),('Sultan','Sam','123 Main Street','New York','NY','12345','(212)123-1234'),('Sultan','Carol','123 Main Street','New York','NY','12345','(212)123-1234'),('Tillman','Christopher','70 Oyster Bay Road','Oyster Bay','NY','12345','(516)922-4941'),('Urich','Robert','123 Hollywood Lane','Century City','CA','12345','(415)498-5301'),('Valante','Vincent','67 Redbank Road','Holmdel','NJ','12345','(212)123-1234'),('Williams','Leonard','1267 2nd Ave','New York','NY','12345','(212)765-3476'),('Xu','Wei','16 Jericho Turnpike','Jericho','NY','12345','(516)628-8309'),('Yen','Jerry','123 Johnson Circle','Bethpage','NY','12345','(631)327-8567');
/*!40000 ALTER TABLE `addrbook` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (
  `address_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) DEFAULT NULL,
  `inst_id` int(11) DEFAULT NULL,
  `addr_type` char(1) NOT 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=19 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `address` WRITE;
/*!40000 ALTER TABLE `address` DISABLE KEYS */;
INSERT INTO `address` VALUES (1,NULL,1,'H','123 Main Street','','Bronx','NY',''),(2,NULL,1,'W','7 East 12 Street','','New York','NY',''),(3,15,NULL,'H','828 Eight Avenue',NULL,'New York','NY',''),(4,3,NULL,'H','222 Second Avenue',NULL,'Brooklyn','NY',''),(5,3,NULL,'W','126 Madison Avenue',NULL,'New York','NY',''),(6,NULL,3,'H','5 West 4th Street','Apt 44','New York','NY',''),(7,5,NULL,'W','323 Third Avenue',NULL,'Bronx','NY',''),(8,7,NULL,'H','424 Forth Avenue',NULL,'New York','NY',''),(9,9,NULL,'W','525 Fifth Avenue',NULL,'Yonkers','NY',''),(10,1,NULL,'H','121 First Avenue',NULL,'New York','NY',''),(11,11,NULL,'W','626 Sixth Avenue',NULL,'Hoboken','NJ',''),(12,13,NULL,'H','727 Seventh Avenue',NULL,'New York','NY',''),(13,17,NULL,'W','929 Ninth Avenue',NULL,'Brooklyn','NY',''),(14,19,NULL,'H','101 Tenth Avenue',NULL,'Newark','NJ',''),(15,NULL,5,'H','121 Eleventh Avenue',NULL,'New York','NY',''),(16,NULL,5,'W','48 East 42 Street',NULL,'New York','NY',''),(17,21,NULL,'H','1 Ontario Drive',NULL,'Toronto',NULL,'Canada'),(18,21,NULL,'W','1155 Browdway',NULL,'New York','NY','');
/*!40000 ALTER TABLE `address` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
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');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
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);
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`demo`@`localhost`*/ /*!50003 TRIGGER course_trig AFTER UPDATE ON course FOR EACH ROW
BEGIN
    INSERT INTO output 
        VALUES(concat(NOW(),' ',USER(),
             ' changed: price ',OLD.price,' to: ',NEW.price));

    IF (OLD.description != NEW.description) THEN            -- only if different
        INSERT INTO output 
            VALUES(concat(NOW(),' ',USER(),
                 ' changed: desc  ',OLD.description,' to: ',NEW.description));
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `email_msg`
--

DROP TABLE IF EXISTS `email_msg`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `email_msg` (
  `msg_id` int(11) NOT NULL AUTO_INCREMENT,
  `email_id` int(11) NOT NULL,
  `receipt_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sender` varchar(99) NOT NULL,
  `message` varchar(500) NOT NULL,
  PRIMARY KEY (`msg_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `email_msg`
--

LOCK TABLES `email_msg` WRITE;
/*!40000 ALTER TABLE `email_msg` DISABLE KEYS */;
INSERT INTO `email_msg` VALUES (1,1,'2018-07-21 04:00:00','sam.sultan@nyu.edu','This is my first email message'),(2,1,'2018-07-22 04:00:00','sam.sultan@nyu.edu','This is my second email message'),(3,3,'2018-07-23 04:00:00','sam.sultan@nyu.edu','Hello World'),(4,3,'2018-07-24 04:00:00','sam.sultan@nyu.edu','Goodbye World'),(5,4,'2018-07-25 04:00:00','sam.sultan@nyu.edu','Using a different email address'),(6,7,'2018-07-26 04:00:00','sam.sultan@nyu.edu','How are you?'),(7,7,'2018-07-27 04:00:00','sam.sultan@nyu.edu','Have not heard from you in a while'),(8,7,'2018-07-28 04:00:00','sam.sultan@nyu.edu','Are you there?'),(9,10,'2018-07-29 04:00:00','sam.sultan@nyu.edu','What a beautiful day to learn SQL');
/*!40000 ALTER TABLE `email_msg` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `hierarchy`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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`),
  CONSTRAINT `hier_mgr_fk` FOREIGN KEY (`manager_id`) REFERENCES `hierarchy` (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `hierarchy` WRITE;
/*!40000 ALTER TABLE `hierarchy` DISABLE KEYS */;
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);
/*!40000 ALTER TABLE `hierarchy` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `instructor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `instructor` WRITE;
/*!40000 ALTER TABLE `instructor` DISABLE KEYS */;
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,'O\'Brien','Mary','000-02-0005','F'),(6,'Katz','Eric','000-02-0006','M');
/*!40000 ALTER TABLE `instructor` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `instructor_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `instructor_info` WRITE;
/*!40000 ALTER TABLE `instructor_info` DISABLE KEYS */;
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');
/*!40000 ALTER TABLE `instructor_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `output`
--

DROP TABLE IF EXISTS `output`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `output` (
  `line` varchar(4000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `output`
--

LOCK TABLES `output` WRITE;
/*!40000 ALTER TABLE `output` DISABLE KEYS */;
INSERT INTO `output` VALUES ('inserting using a loop'),('loop number 10'),('loop number 11'),('loop number 12'),('loop number 13'),('loop number 14'),('loop number 15'),('loop number 16'),('loop number 17'),('loop number 18'),('loop number 19'),('loop number 20'),('-- using a WHILE loop with min/max-- '),('loop number:  20'),('loop number:  21'),('loop number:  22'),('loop number:  23'),('loop number:  24'),('loop number:  25'),('loop number:  26'),('loop number:  27'),('loop number:  28'),('loop number:  29'),('loop number:  30'),('loop number:  31'),('loop number:  32'),('loop number:  33'),('loop number:  34'),('loop number:  35'),('loop number:  36'),('loop number:  37'),('loop number:  38'),('loop number:  39'),('loop number:  40');
/*!40000 ALTER TABLE `output` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `payment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `payment` WRITE;
/*!40000 ALTER TABLE `payment` DISABLE KEYS */;
INSERT INTO `payment` VALUES (1,'Con Edison',125.15,'Electric','2012-02-01 05:00:00'),(2,'Verizon',54.79,'Home Phone','2012-03-01 05:00:00'),(3,'Allstate',1240.50,'Home Insurance','2012-03-02 05:00:00'),(4,'Verizon',49.95,'Cell Phone','2012-03-03 05:00:00'),(5,'Verizon',39.95,'DSL Line','2012-03-04 05:00:00'),(6,'Con Edison',59.63,'Electric','2012-03-05 05:00:00'),(7,'Department of Water',155.43,'Water and Sewers','2012-03-06 05:00:00'),(8,'Allstate',1550.25,'Car Insurance','2012-03-07 05:00:00');
/*!40000 ALTER TABLE `payment` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`demo`@`localhost`*/ /*!50003 TRIGGER payment_trig BEFORE UPDATE ON payment FOR EACH ROW
    BEGIN
    IF NEW.amount > OLD.amount * 1.05 THEN
        INSERT INTO output 
            VALUES('New amount cannot exceed old amount + 5%');
        SELECT 'Force error' INTO @error FROM dummy_table;       -- invalid table
    END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `relationship`
--

DROP TABLE IF EXISTS `relationship`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `relationship` (
  `row_pk` int(11) NOT NULL AUTO_INCREMENT,
  `from_id` int(11) NOT NULL,
  `to_id` int(11) NOT NULL,
  PRIMARY KEY (`row_pk`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `relationship`
--

LOCK TABLES `relationship` WRITE;
/*!40000 ALTER TABLE `relationship` DISABLE KEYS */;
INSERT INTO `relationship` VALUES (1,1,3),(2,2,3),(3,3,4),(4,6,4),(5,3,5),(6,6,5),(7,4,7),(8,4,8),(9,4,9);
/*!40000 ALTER TABLE `relationship` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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=26 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
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,'James','Phyllis','000-01-0024','F'),(25,'Sultan','Sam','000-02-0001','M');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `student_email`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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;
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `student_email` WRITE;
/*!40000 ALTER TABLE `student_email` DISABLE KEYS */;
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,'none'),(10,15,'soleyj@nyu.edu');
/*!40000 ALTER TABLE `student_email` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `name` varchar(10) DEFAULT NULL,
  `pop` decimal(10,0) DEFAULT NULL,
  `cases` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES ('us',1000,50),('us',500,10),('us',1000,50),('us',500,10),('x',1000,10),('x',500,5);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `v_key_column`;
/*!50001 DROP VIEW IF EXISTS `v_key_column`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_key_column` (
  `table_name` tinyint NOT NULL,
  `column_name` tinyint NOT NULL,
  `constraint_type` tinyint NOT NULL,
  `constraint_name` tinyint NOT NULL,
  `ordinal_position` tinyint NOT NULL,
  `referenced_table_name` tinyint NOT NULL,
  `referenced_column_name` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `v_student_info`
--

DROP TABLE IF EXISTS `v_student_info`;
/*!50001 DROP VIEW IF EXISTS `v_student_info`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `v_student_info` (
  `fname` tinyint NOT NULL,
  `lname` tinyint NOT NULL,
  `student_id` tinyint NOT NULL,
  `sex` tinyint NOT NULL,
  `ssn` tinyint NOT NULL,
  `email` tinyint NOT NULL,
  `as_of` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- 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 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_key_column` AS select `information_schema`.`key_column_usage`.`TABLE_NAME` AS `table_name`,`information_schema`.`key_column_usage`.`COLUMN_NAME` AS `column_name`,'FOREIGN KEY' AS `constraint_type`,`information_schema`.`key_column_usage`.`CONSTRAINT_NAME` AS `constraint_name`,`information_schema`.`key_column_usage`.`ORDINAL_POSITION` AS `ordinal_position`,`information_schema`.`key_column_usage`.`REFERENCED_TABLE_NAME` AS `referenced_table_name`,`information_schema`.`key_column_usage`.`REFERENCED_COLUMN_NAME` AS `referenced_column_name` from `information_schema`.`key_column_usage` where (`information_schema`.`key_column_usage`.`REFERENCED_TABLE_NAME` is not null) union select `information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`COLUMN_NAME` AS `column_name`,(case when (`information_schema`.`statistics`.`INDEX_NAME` = 'PRIMARY') then ' PRIMARY KEY' when (`information_schema`.`statistics`.`NON_UNIQUE` = 1) then 'INDEX' when (`information_schema`.`statistics`.`NON_UNIQUE` = 0) then 'UNIQUE INDEX' end) AS `Name_exp_3`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,`information_schema`.`statistics`.`SEQ_IN_INDEX` AS `seq_in_index`,NULL AS `NULL`,NULL AS `NULL` from `information_schema`.`statistics` order by 1,3,4,5,2 */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `v_student_info`
--

/*!50001 DROP TABLE IF EXISTS `v_student_info`*/;
/*!50001 DROP VIEW IF EXISTS `v_student_info`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = latin1 */;
/*!50001 SET character_set_results     = latin1 */;
/*!50001 SET collation_connection      = latin1_swedish_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`demo`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_student_info` AS select `s`.`fname` AS `fname`,`s`.`lname` AS `lname`,`s`.`student_id` AS `student_id`,`s`.`sex` AS `sex`,`s`.`ssn` AS `ssn`,`e`.`email` AS `email`,now() AS `as_of` from (`student` `s` left join `student_email` `e` on((`s`.`student_id` = `e`.`student_id`))) order by `s`.`lname` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-08-18 17:21:30