-
Notifications
You must be signed in to change notification settings - Fork 0
/
script.sql
114 lines (98 loc) · 3.34 KB
/
script.sql
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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
CREATE TABLE Branch(
branchNo VARCHAR(5) PRIMARY KEY,
street VARCHAR(100),
city VARCHAR(100),
postcode VARCHAR(50)
);
CREATE TABLE Staff(
staffNo VARCHAR(5) PRIMARY KEY,
fName VARCHAR(20),
lName VARCHAR(20),
position VARCHAR(10),
sex CHAR(1),
DOB DATE,
salary REAL,
branchNo VARCHAR(5),
FOREIGN KEY(branchNo) REFERENCES Branch(branchNo)
);
CREATE TABLE PrivateOwner(
ownerNo VARCHAR(5) PRIMARY KEY,
fName VARCHAR(20),
lName VARCHAR(20),
address VARCHAR(100),
tel VARCHAR(15)
);
CREATE TABLE PropertyForRent(
propertyNo VARCHAR(5) PRIMARY KEY,
street VARCHAR(50),
city VARCHAR(50),
postcode VARCHAR(50),
propertyType VARCHAR(20),
rooms INT,
rent REAL,
ownerNo VARCHAR(5),
staffNo VARCHAR(5),
branchNo VARCHAR(5),
FOREIGN KEY(ownerNo) REFERENCES PrivateOwner(ownerNo),
FOREIGN KEY(staffNo) REFERENCES Staff(staffNo),
FOREIGN KEY(branchNo) REFERENCES Branch(branchNo)
);
CREATE TABLE Client(
clientNo VARCHAR(5) PRIMARY KEY,
fName VARCHAR(20),
lName VARCHAR(20),
telNo VARCHAR(15),
prefType VARCHAR(10),
maxRent REAL
);
CREATE TABLE Viewing(
clientNo VARCHAR(5),
propertyNo VARCHAR(5),
viewDate DATE,
comments VARCHAR(100),
PRIMARY KEY(clientNo, propertyNo)
);
CREATE TABLE Registration(
clientNo VARCHAR(5),
branchNo VARCHAR(5),
staffNo VARCHAR(5),
dateJoined DATE
);
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/branch.csv" into table Branch CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/client.csv" into table Client CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/privateowner.csv" into table PrivateOwner CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/propertyforrent.csv" into table PropertyForRent CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/registration.csv" into table Registration CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/Staff.csv" into table Staff CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Load data local infile "Z:/CMIS 2123 -DBMS/Tables/viewing.csv" into table Viewing CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
-- Question 1
SELECT staffNo, fName, lName, salary
FROM Staff;
-- Question 2
SELECT propertyNo
FROM Viewing;
-- Question 3
SELECT staffNo, fName, lName
FROM Staff
WHERE position = "Manager" OR position = "Supervisor";
-- Question 4
SELECT *
FROM PrivateOwner
WHERE address LIKE "%Glasgow%";
-- Question 5
SELECT *
FROM Branch
WHERE city = "London" OR city = "Glasgow";
-- Question 6
SELECT *
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
-- Question 7
SELECT *
FROM Viewing
WHERE propertyNo = "PG4" AND comments = "null";
-- Question 8
SELECT COUNT(clientNo)
FROM viewing
WHERE viewDate = "2001-05";
-- Question 9