cs122a-2016-spring: sample_script.sql

File sample_script.sql, 3.7 KB (added by waans11, 5 years ago)
Line 
1-- The Begin of the script
2CREATE DATABASE IF NOT EXISTS `cs122a` DEFAULT CHARACTER SET latin1;
3USE `cs122a`;
4
5
6-- Table structure for table `Boats`
7DROP TABLE IF EXISTS `Boats`;
8CREATE TABLE `Boats` (
9  `bid` int(11) NOT NULL,
10  `bname` varchar(45) DEFAULT NULL,
11  `color` varchar(15) DEFAULT NULL,
12  PRIMARY KEY (`bid`)
13) ENGINE=InnoDB DEFAULT CHARSET=latin1;
14
15
16-- Dumping data for table `Boats`
17ALTER TABLE `Boats` DISABLE KEYS;
18INSERT INTO `Boats` VALUES (101,'Interlake','blue'),(102,'Interlake','red'),(103,'Clipper','green'),(104,'Marine','red');
19ALTER TABLE `Boats` ENABLE KEYS;
20
21
22-- Table structure for table `Boats2`
23DROP TABLE IF EXISTS `Boats2`;
24CREATE TABLE `Boats2` (
25  `bid` int(11) NOT NULL,
26  `bname` varchar(45) DEFAULT NULL,
27  `color` varchar(15) DEFAULT NULL,
28  PRIMARY KEY (`bid`)
29) ENGINE=InnoDB DEFAULT CHARSET=latin1;
30
31
32-- Dumping data for table `Boats2`
33ALTER TABLE `Boats2` DISABLE KEYS;
34INSERT INTO `Boats2` VALUES (103,'Clipper','green'),(104,'Marine','red'),(105,'InterClipper','blue'),(106,'InterMarine','red');
35ALTER TABLE `Boats2` ENABLE KEYS;
36
37
38-- Table structure for table `Reserves`
39DROP TABLE IF EXISTS `Reserves`;
40CREATE TABLE `Reserves` (
41  `sid` int(11) DEFAULT NULL,
42  `bid` int(11) DEFAULT NULL,
43  `date` date DEFAULT NULL
44) ENGINE=InnoDB DEFAULT CHARSET=latin1;
45
46
47-- Dumping data for table `Reserves`
48ALTER TABLE `Reserves` DISABLE KEYS;
49INSERT INTO `Reserves` VALUES (22,101,'1998-10-10'),(22,102,'1998-10-10'),(22,103,'1998-10-08'),(22,104,'1998-10-07'),(31,102,'1998-11-10'),(31,103,'1998-11-06'),(31,104,'1998-11-12'),(64,101,'1998-09-05'),(64,102,'1998-09-08'),(74,103,'1998-09-08'),(NULL,103,'1998-09-09'),(1,NULL,'2001-01-11'),(1,NULL,'2002-02-02');
50ALTER TABLE `Reserves` ENABLE KEYS;
51
52
53-- Table structure for table `Reserves`
54DROP TABLE IF EXISTS `Reserves2`;
55CREATE TABLE `Reserves2` (
56  `sid` int(11) DEFAULT NULL,
57  `bid` int(11) DEFAULT NULL,
58  `date` date DEFAULT NULL
59) ENGINE=InnoDB DEFAULT CHARSET=latin1;
60
61
62-- Dumping data for table `Reserves2`
63ALTER TABLE `Reserves2` DISABLE KEYS;
64INSERT INTO `Reserves2` VALUES (22,103,'1998-10-10'),(22,104,'1998-10-10'),(22,105,'1998-10-08'),(22,106,'1998-10-07'),(31,103,'1998-11-10'),(31,104,'1998-11-06'),(31,105,'1998-11-12'),(64,104,'1998-09-05'),(64,105,'1998-09-08'),(74,105,'1998-09-08'),(NULL,104,'1998-09-09'),(108,NULL,'2001-01-11'),(108,NULL,'2002-02-02');
65ALTER TABLE `Reserves2` ENABLE KEYS;
66
67
68-- Table structure for table `Sailors`
69DROP TABLE IF EXISTS `Sailors`;
70CREATE TABLE `Sailors` (
71  `sid` int(11) NOT NULL,
72  `sname` varchar(45) NOT NULL,
73  `rating` int(11) DEFAULT NULL,
74  `age` decimal(5,1) DEFAULT NULL,
75  PRIMARY KEY (`sid`)
76) ENGINE=InnoDB DEFAULT CHARSET=latin1;
77
78
79-- Dumping data for table `Sailors`
80ALTER TABLE `Sailors` DISABLE KEYS;
81INSERT INTO `Sailors` VALUES (22,'Dustin',7,45.0),(29,'Brutus',1,33.0),(31,'Lubber',8,55.5),(32,'Andy',8,25.5),(58,'Rusty',10,35.0),(64,'Horatio',7,35.0),(71,'Zorba',10,16.0),(74,'Horatio',9,35.0),(85,'Art',4,25.5),(95,'Bob',3,63.5),(101,'Joan',3,NULL),(107,'Johannes',NULL,35.0);
82ALTER TABLE `Sailors` ENABLE KEYS;
83
84
85-- Table structure for table `Sailors2`
86DROP TABLE IF EXISTS `Sailors2`;
87CREATE TABLE `Sailors2` (
88  `sid` int(11) NOT NULL,
89  `sname` varchar(45) NOT NULL,
90  `rating` int(11) DEFAULT NULL,
91  `age` decimal(5,1) DEFAULT NULL,
92  PRIMARY KEY (`sid`)
93) ENGINE=InnoDB DEFAULT CHARSET=latin1;
94
95
96-- Dumping data for table `Sailors2`
97ALTER TABLE `Sailors2` DISABLE KEYS;
98INSERT INTO `Sailors2` VALUES (22,'Dustin',7,45.0),(31,'Lubber',8,55.5),(64,'Horatio',7,35.0),(71,'Zorba',10,16.0),(74,'Horatio',9,35.0),(85,'Art',4,25.5),(95,'Bob',3,63.5),(101,'Joan',3,NULL),(107,'Johannes',NULL,35.0),(108,'Sandy',NULL,36.0),(109,'James',5,38.0);
99ALTER TABLE `Sailors2` ENABLE KEYS;
100-- The end of the script
101