· 9 years ago · Dec 05, 2016, 09:06 AM
1-- MySQL dump 10.16 Distrib 10.1.17-MariaDB, for Linux (x86_64)
2--
3-- Host: sql8.freemysqlhosting.net Database: sql8146748
4-- ------------------------------------------------------
5-- Server version 5.5.49-0ubuntu0.14.04.1
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `Customers`
20--
21
22DROP TABLE IF EXISTS `Customers`;
23/*!40101 SET @saved_cs_client = @@character_set_client */;
24/*!40101 SET character_set_client = utf8 */;
25CREATE TABLE `Customers` (
26 `cnum` int(11) NOT NULL AUTO_INCREMENT,
27 `cname` varchar(45) DEFAULT NULL,
28 `city` varchar(45) DEFAULT NULL,
29 `rating` int(11) DEFAULT NULL,
30 `snum` int(11) DEFAULT NULL,
31 PRIMARY KEY (`cnum`),
32 KEY `fk_Customers_Salespeople_idx` (`snum`),
33 CONSTRAINT `fk_Customers_Salespeople` FOREIGN KEY (`snum`) REFERENCES `Salespeople` (`snum`) ON DELETE NO ACTION ON UPDATE NO ACTION
34) ENGINE=InnoDB AUTO_INCREMENT=2009 DEFAULT CHARSET=latin1;
35/*!40101 SET character_set_client = @saved_cs_client */;
36
37--
38-- Dumping data for table `Customers`
39--
40
41LOCK TABLES `Customers` WRITE;
42/*!40000 ALTER TABLE `Customers` DISABLE KEYS */;
43INSERT INTO `Customers` VALUES (2001,'Haffman','London',100,1001),(2002,'Giovanni','Rome',200,1003),(2003,'Liu','San Jose',200,1002),(2004,'Grass','Berlin',300,1002),(2006,'Clemesn','London',100,1001),(2007,'Pereira','Rome',100,1004),(2008,'Cisneros','San Jose',300,1007);
44/*!40000 ALTER TABLE `Customers` ENABLE KEYS */;
45UNLOCK TABLES;
46
47--
48-- Table structure for table `Orders`
49--
50
51DROP TABLE IF EXISTS `Orders`;
52/*!40101 SET @saved_cs_client = @@character_set_client */;
53/*!40101 SET character_set_client = utf8 */;
54CREATE TABLE `Orders` (
55 `onum` int(11) NOT NULL AUTO_INCREMENT,
56 `amt` decimal(10,2) DEFAULT NULL,
57 `odate` date DEFAULT NULL,
58 `cnum` int(11) DEFAULT NULL,
59 `snum` int(11) DEFAULT NULL,
60 PRIMARY KEY (`onum`),
61 KEY `fk_Orders_1_idx` (`cnum`),
62 KEY `fk_Orders_2_idx` (`snum`),
63 CONSTRAINT `fk_Orders_1` FOREIGN KEY (`cnum`) REFERENCES `Customers` (`cnum`) ON DELETE NO ACTION ON UPDATE NO ACTION,
64 CONSTRAINT `fk_Orders_2` FOREIGN KEY (`snum`) REFERENCES `Salespeople` (`snum`) ON DELETE NO ACTION ON UPDATE NO ACTION
65) ENGINE=InnoDB AUTO_INCREMENT=3012 DEFAULT CHARSET=latin1;
66/*!40101 SET character_set_client = @saved_cs_client */;
67
68--
69-- Dumping data for table `Orders`
70--
71
72LOCK TABLES `Orders` WRITE;
73/*!40000 ALTER TABLE `Orders` DISABLE KEYS */;
74INSERT INTO `Orders` VALUES (3001,18.69,'1990-10-03',2008,1007),(3002,1900.10,'1990-10-03',2007,1004),(3003,767.19,'1990-10-03',2001,1001),(3005,5160.45,'1990-10-03',2003,1002),(3006,1098.16,'1990-10-03',2008,1007),(3007,75.75,'1990-10-04',2004,1002),(3008,4723.00,'1990-10-05',2006,1001),(3009,1713.23,'1990-10-04',2002,1003),(3010,1309.95,'1990-10-06',2004,1002),(3011,9891.88,'1990-10-06',2006,1001);
75/*!40000 ALTER TABLE `Orders` ENABLE KEYS */;
76UNLOCK TABLES;
77
78--
79-- Table structure for table `Salespeople`
80--
81
82DROP TABLE IF EXISTS `Salespeople`;
83/*!40101 SET @saved_cs_client = @@character_set_client */;
84/*!40101 SET character_set_client = utf8 */;
85CREATE TABLE `Salespeople` (
86 `snum` int(11) NOT NULL AUTO_INCREMENT,
87 `sname` varchar(45) DEFAULT NULL,
88 `city` varchar(45) DEFAULT NULL,
89 `comm` varchar(45) DEFAULT NULL,
90 PRIMARY KEY (`snum`)
91) ENGINE=InnoDB AUTO_INCREMENT=1008 DEFAULT CHARSET=latin1;
92/*!40101 SET character_set_client = @saved_cs_client */;
93
94--
95-- Dumping data for table `Salespeople`
96--
97
98LOCK TABLES `Salespeople` WRITE;
99/*!40000 ALTER TABLE `Salespeople` DISABLE KEYS */;
100INSERT INTO `Salespeople` VALUES (1001,'Peel','London','0.12'),(1002,'Serres','San Jose','0.13'),(1003,'Motika','London','0.11'),(1004,'Rifkin','Barcelona','0.15'),(1007,'Axelrod','New York','0.10');
101/*!40000 ALTER TABLE `Salespeople` ENABLE KEYS */;
102UNLOCK TABLES;
103
104--
105-- Table structure for table `account`
106--
107
108DROP TABLE IF EXISTS `account`;
109/*!40101 SET @saved_cs_client = @@character_set_client */;
110/*!40101 SET character_set_client = utf8 */;
111CREATE TABLE `account` (
112 `acct_num` int(11) DEFAULT NULL,
113 `amount` decimal(10,2) DEFAULT NULL
114) ENGINE=InnoDB DEFAULT CHARSET=latin1;
115/*!40101 SET character_set_client = @saved_cs_client */;
116
117--
118-- Dumping data for table `account`
119--
120
121LOCK TABLES `account` WRITE;
122/*!40000 ALTER TABLE `account` DISABLE KEYS */;
123/*!40000 ALTER TABLE `account` ENABLE KEYS */;
124UNLOCK TABLES;
125
126--
127-- Dumping routines for database 'sql8146748'
128--
129/*!50003 DROP FUNCTION IF EXISTS `hello` */;
130/*!50003 SET @saved_cs_client = @@character_set_client */ ;
131/*!50003 SET @saved_cs_results = @@character_set_results */ ;
132/*!50003 SET @saved_col_connection = @@collation_connection */ ;
133/*!50003 SET character_set_client = utf8 */ ;
134/*!50003 SET character_set_results = utf8 */ ;
135/*!50003 SET collation_connection = utf8_general_ci */ ;
136/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
137/*!50003 SET sql_mode = '' */ ;
138DELIMITER ;;
139CREATE DEFINER=`sql8146748`@`%` FUNCTION `hello`() RETURNS text CHARSET latin1
140return 'hello world' ;;
141DELIMITER ;
142/*!50003 SET sql_mode = @saved_sql_mode */ ;
143/*!50003 SET character_set_client = @saved_cs_client */ ;
144/*!50003 SET character_set_results = @saved_cs_results */ ;
145/*!50003 SET collation_connection = @saved_col_connection */ ;
146/*!50003 DROP FUNCTION IF EXISTS `maxRatinginRome` */;
147/*!50003 SET @saved_cs_client = @@character_set_client */ ;
148/*!50003 SET @saved_cs_results = @@character_set_results */ ;
149/*!50003 SET @saved_col_connection = @@collation_connection */ ;
150/*!50003 SET character_set_client = utf8 */ ;
151/*!50003 SET character_set_results = utf8 */ ;
152/*!50003 SET collation_connection = utf8_general_ci */ ;
153/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
154/*!50003 SET sql_mode = '' */ ;
155DELIMITER ;;
156CREATE DEFINER=`sql8146748`@`%` FUNCTION `maxRatinginRome`() RETURNS int(11)
157BEGIN
158declare result int;
159 select min(maxrating) into result from (select city, max(rating) as maxrating from Customers group by city) M;
160RETURN result;
161END ;;
162DELIMITER ;
163/*!50003 SET sql_mode = @saved_sql_mode */ ;
164/*!50003 SET character_set_client = @saved_cs_client */ ;
165/*!50003 SET character_set_results = @saved_cs_results */ ;
166/*!50003 SET collation_connection = @saved_col_connection */ ;
167/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
168
169/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
170/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
171/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
172/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
173/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
174/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
175/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
176
177-- Dump completed on 2016-12-02 19:39:33