DBMS_Examples/create_tables.sql at master · lucas-acc/DBMS_Examples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
USE dbms_exercises;

-- create Sailors
DROP TABLE IF EXISTS `Sailors`;
CREATE TABLE `Sailors` (
  `sid` integer NOT NULL,
  `sname` varchar(10) NOT NULL,
  `rating` integer NOT NULL,
  `age` double NOT NULL
);
INSERT INTO Sailors ( sid, sname, rating, age)
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', 3, 25.5),
( 95, 'Bob', 3, 63.5);
/*result
+-----+---------+--------+------+
| sid | sname   | rating | age  |
+-----+---------+--------+------+
|  22 | Dustin  |      7 |   45 |
|  29 | Brutus  |      1 |   33 |
|  31 | Lubber  |      8 | 55.5 |
|  32 | Andy    |      8 | 25.5 |
|  58 | Rusty   |     10 |   35 |
|  64 | Horatio |      7 |   35 |
|  71 | Zorba   |     10 |   16 |
|  74 | Horatio |      9 |   35 |
|  85 | Art     |      3 | 25.5 |
|  95 | Bob     |      3 | 63.5 |
+-----+---------+--------+------+
*/


-- create Reserves
DROP TABLE IF EXISTS `Reserves`;
CREATE TABLE `Reserves` (
  `sid` integer NOT NULL,
  `bid` integer NOT NULL,
  `day` date NOT NULL
);
INSERT INTO Reserves ( sid, bid, day)
VALUES
( 22, 101, '1998-10-10'),
( 22, 102, '1998-10-10'),
( 22, 103, '1998-10-8'),
( 22, 104, '1998-10-7'),
( 31, 102, '1998-11-10'),
( 31, 103, '1998-11-6'),
( 31, 104, '1998-11-12'),
( 64, 101, '1998-9-5'),
( 64, 102, '1998-9-8'),
( 74, 103, '1998-9-8');
/* result
+-----+-----+------------+
| sid | bid | day        |
+-----+-----+------------+
|  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 |
+-----+-----+------------+
*/

-- create Boats
DROP TABLE IF EXISTS `Boats`;
CREATE TABLE `Boats` (
  `bid` integer NOT NULL,
  `bname` varchar(10) NOT NULL,
  `color` varchar(10) NOT NULL
);
INSERT INTO Boats ( bid, bname, color)
VALUES
(101, 'Interlake', 'blue'),
(102, 'Interlake', 'red'),
(103, 'Clipper', 'green'),
(104, 'Marine', 'red');
/*result
+-----+-----------+-------+
| bid | bname     | color |
+-----+-----------+-------+
| 101 | Interlake | blue  |
| 102 | Interlake | red   |
| 103 | Clipper   | green |
| 104 | Marine    | red   |
+-----+-----------+-------+
*/