-- 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