MySQL-PHP-Application/ywen58script2.txt at master · nickcom007/MySQL-PHP-Application · 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
USE ywen58assign2db;
-- load txt file
SELECT * FROM patient;
LOAD DATA LOCAL INFILE '/home/centos/assignment2/loaddatafall2019.txt'
INTO TABLE patient
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
-- add myself as patient
INSERT INTO patient(OHIP,firstname,lastname) VALUES(987654321,'Yizhe','Wen');
SELECT * FROM patient;

-- INSERT doctors
SELECT * FROM doctor;
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Bernie', 'Kavorikian', 'RD34', '1980-09-09', 'Urologist');
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Joey', 'Shabado', 'GD56', '1960-06-24', 'Podiatrist');
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Ross', 'Clooney', 'HT45', '1987-06-20', 'Surgeon');
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Mandy', 'Webster', 'JK78', '1990-08-08', 'Surgeon');
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Colleen', 'Aziz', 'SE66', '1989-08-24', 'Surgeon');
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Ben', 'Spock', 'YT67', '1955-02-20', 'Pediatrician');
INSERT INTO doctor(firstname,lastname,license,licensedate,specialty)
VALUES('Doctor', 'Strange', 'MV01', '2016-11-04', 'Avengers');
SELECT * FROM doctor;

-- INSERT hospitals
SELECT * FROM hospital;
INSERT INTO hospital(name,city,province,numberofbeds,code,head,headdate)
VALUES('St.Joseph','London','On',1000,'BBC','GD56','1999-09-10');
INSERT INTO hospital(name,city,province,numberofbeds,code,head,headdate)
VALUES('Victoria','London','On',1600,'ABC','SE66','1994-12-05');
INSERT INTO hospital(name,city,province,numberofbeds,code,head,headdate)
VALUES('Victoria','Victoria','BC',1200,'DDE','YT67','1997-12-07');
SELECT * FROM hospital;

-- INSERT treat relationships
SELECT * FROM treat;
INSERT INTO treat(OHIP,license) VALUES(110112113,'GD56');
INSERT INTO treat(OHIP,license) VALUES(444555666,'HT45');
INSERT INTO treat(OHIP,license) VALUES(444555666,'JK78');
INSERT INTO treat(OHIP,license) VALUES(111222333,'SE66');
INSERT INTO treat(OHIP,license) VALUES(111222333,'YT67');
INSERT INTO treat(OHIP,license) VALUES(111222333,'JK78');
INSERT INTO treat(OHIP,license) VALUES(333444555,'GD56');
INSERT INTO treat(OHIP,license) VALUES(667766777,'YT67');
INSERT INTO treat(OHIP,license) VALUES(667766777,'JK78');
INSERT INTO treat(OHIP,license) VALUES(667766777,'GD56');
INSERT INTO treat(OHIP,license) VALUES(111222111,'YT67');
INSERT INTO treat(OHIP,license) VALUES(987654321,'MV01');
SELECT * FROM treat;

-- UPDATE FOREIGN keys
UPDATE doctor SET hospitalcode = 'BBC' WHERE license = 'RD34';
UPDATE doctor SET hospitalcode = 'BBC' WHERE license = 'GD56';
UPDATE doctor SET hospitalcode = 'BBC' WHERE license = 'JK78';
UPDATE doctor SET hospitalcode = 'DDE' WHERE license = 'HT45';
UPDATE doctor SET hospitalcode = 'DDE' WHERE license = 'YT67';
UPDATE doctor SET hospitalcode = 'ABC' WHERE license = 'SE66';
UPDATE doctor SET hospitalcode = 'ABC' WHERE license = 'MV01';
SELECT * FROM doctor;


UPDATE patient SET lastname = 'Brown' WHERE OHIP = 111222111;
SELECT * FROM patient;

UPDATE treat SET license = (SELECT DISTINCT license FROM doctor
WHERE lastname = 'Clooney')
WHERE license = (SELECT DISTINCT license FROM doctor WHERE lastname = 'Spock');
SELECT * FROM treat;