iu0csD2H

1DROP DATABASE IF EXISTS horsedb;
2CREATE DATABASE IF NOT EXISTS horsedb;
3USE horsedb;
4/* Question 2.1 */
5CREATE TABLE entry (
6    Event_id INT(8),
7    Horse_id INT(8),
8    Place VARCHAR(8),
9    PRIMARY KEY (Event_id)
10);
11/* Question 2.2 */
12LOAD DATA LOCAL INFILE '/Data/entry.txt' INTO TABLE ENTRY 
13LINES TERMINATED BY '\r\n';
14CREATE TABLE event (
15    Event_id INTEGER(15),
16    Show_id INT(15),
17    Event_name VARCHAR(30),
18    Judge_id INT(15),
19    PRIMARY KEY (Event_id)
20);
21LOAD DATA LOCAL INFILE '/Data/event.txt' INTO TABLE EVENT 
22LINES TERMINATED BY '\r\n';
23CREATE TABLE horse (
24    Horse_id INTEGER(15),
25    Name VARCHAR(15),
26    Colour VARCHAR(30),
27    Sire VARCHAR(15),
28    Dam VARCHAR(15),
29    Born VARCHAR(15),
30    Trainer_id INT(15),
31    PRIMARY KEY (Horse_id)
32);
33LOAD DATA LOCAL INFILE '/Data/horse.txt' INTO TABLE HORSE 
34LINES TERMINATED BY '\r\n';
35CREATE TABLE judge (
36    Judge_id INT(11),
37    Name VARCHAR(10),
38    Address VARCHAR(20),
39    FOREIGN KEY (Judge_id) 
40    REFERENCES judge (Judge_id),
41	ON DELETE NO ACTION
42	ON UPDATE CASCADE
43    );
44LOAD DATA LOCAL INFILE '/Data/judge.txt' INTO TABLE JUDGE 
45LINES TERMINATED BY '\r\n';
46CREATE TABLE prize (
47    Event_id INTEGER(15),
48    Place VARCHAR(15),
49    Money VARCHAR(8),
50    PRIMARY KEY (Event_id)
51);
52LOAD DATA LOCAL INFILE '/Data/prize.txt' INTO TABLE PRIZE 
53LINES TERMINATED BY '\r\n';
54CREATE TABLE showsite (
55    Show_id INTEGER(15),
56    Name VARCHAR(15),
57    Address VARCHAR(200),
58    PRIMARY KEY (Show_id)
59);
60LOAD DATA LOCAL INFILE '/Data/showsite.txt' INTO TABLE SHOWSITE 
61LINES TERMINATED BY '\r\n';
62CREATE TABLE trainer (
63    Trainer_id INTEGER(15),
64    Name VARCHAR(15),
65    PRIMARY KEY (Trainer_id)
66);
67LOAD DATA LOCAL INFILE '/Data/trainer.txt' INTO TABLE TRAINER 
68LINES TERMINATED BY '\r\n';
69/* Question 2.5 */
70SELECT 
71    *
72FROM
73    horse;
74/* Question 2.6 */
75UPDATE horse 
76SET 
77    Colour = 'White',
78    Born = '1992'
79WHERE
80    Horse_id = 301;
81/* Question 2.7 */
82ALTER TABLE horse
83ADD Died int (4), 
84ADD Gender varchar (1);
85UPDATE horse 
86SET 
87    Died = '0',
88    Gender = 'S'
89WHERE
90    Horse_id = 101;
91UPDATE horse 
92SET 
93    Died = '0',
94    Gender = 'M'
95WHERE
96    Horse_id = 102;
97UPDATE horse 
98SET 
99    Died = '0',
100    Gender = 'S'
101WHERE
102    Horse_id = 201;
103UPDATE horse 
104SET 
105    Died = '0',
106    Gender = 'M'
107WHERE
108    Horse_id = 301;
109UPDATE horse 
110SET 
111    Died = '1995',
112    Gender = 'S'
113WHERE
114    Horse_id = 401;
115UPDATE horse 
116SET 
117    Died = '1993',
118    Gender = 'M'
119WHERE
120    Horse_id = 501;
121UPDATE horse 
122SET 
123    Died = '1998',
124    Gender = 'M'
125WHERE
126    Horse_id = 502;
127UPDATE horse 
128SET 
129    Died = '0',
130    Gender = 'M'
131WHERE
132    Horse_id = 9998;
133UPDATE horse 
134SET 
135    Died = '0',
136    Gender = 'S'
137WHERE
138    Horse_id = 9999;
139/* Question 2.8 */
140DESCRIBE judge;
141/* Question 2.9 */
142ALTER TABLE judge 
143CHANGE Address Suburb varchar (20);
144/* Question 2.10 */
145DELETE FROM judge 
146WHERE
147    Judge_id = 6;
148DELETE FROM judge 
149WHERE
150    Judge_id = 8;
151/* Question 2.11 */
152ALTER TABLE horse
153DROP COLUMN Trainer_id;
154/* Question 2.12 */
155DROP TABLE trainer;