3u854NAZ

1DROP TABLE IF EXISTS `Branch`;
2
3CREATE TABLE `Branch` (
4  `branchNo` VARCHAR(5), 
5  `street` VARCHAR(50), 
6  `city` VARCHAR(50), 
7  `postcode` VARCHAR(10)
8);
9
10INSERT INTO `Branch` (`branchNo`, `street`, `city`, `postcode`) VALUES ('B002', '56 Cover Drive', 'London', 'NW10 6EU');
11INSERT INTO `Branch` (`branchNo`, `street`, `city`, `postcode`) VALUES ('B003', '163 Main street', 'Glasgow', 'G11 9QX');
12INSERT INTO `Branch` (`branchNo`, `street`, `city`, `postcode`) VALUES ('B004', '32 Manse Road', 'Bristol', 'BS99 1NZ');
13INSERT INTO `Branch` (`branchNo`, `street`, `city`, `postcode`) VALUES ('B005', '22 Deer Road', 'London', 'SW1 4EH');
14INSERT INTO Branch VALUES ('B007', '16 Argyll St', 'Aberdeen', 'AB2 3SU');
15
16DROP TABLE IF EXISTS `Client`;
17
18CREATE TABLE `Client` (
19  `clientNo` VARCHAR(5),
20  `fName` VARCHAR(50), 
21  `lName` VARCHAR(50), 
22  `telNo` VARCHAR(20), 
23  `street` VARCHAR(50), 
24  `city` VARCHAR(50), 
25  `postCode` VARCHAR(10), 
26  `email` VARCHAR(40), 
27  `joinedOn` DATETIME, 
28  `region` VARCHAR(30), 
29  `preType` VARCHAR(10), 
30  `maxRent` INTEGER
31) ;
32
33INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('C0001', 'John', 'Smith', '3153122001', '23 Oneida street', 'Oswego', '13126', 'john.smith@google.com', '2012-11-01 00:00:00', 'Central London', 'Single', 1000);
34INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('C0002', 'Jane', 'Doe', '3153122002', '5 N 18th street', 'Oswego', '13126', 'jane.doe@google.com', '2012-11-02 00:00:00', 'Scotland', 'Condo', 1000);
35INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('C0003', 'Janet', 'Scott', '3153122003', '57 Central street', 'Oswego', '13126', 'janet.scott@google.com', '2012-11-03 00:00:00', 'suburb', 'Single', 2000);
36INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('C0004', 'David', 'Blank', '3153122004', '45 SW 3rd street', 'Oswego', '13126', 'david.blank@google.com', '2012-11-04 00:00:00', 'airport', 'Single', 5000);
37INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR56', 'Fred', 'Flintstone', '555 1234', '12 Rock Way', 'Bedrock', 'BD3 8RK', 'fred@flintyrock.com', '2004-05-09 00:00:00', NULL, 'House', 450);
38INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR62', 'Wilma', 'Flintstone', '555 1234', '12 Rock Way', 'Bedrock', 'BD3 8RK', 'wilma@flintyrock.com', '2004-05-09 00:00:00', NULL, 'Flat', 350);
39INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR74', 'Albert', 'Johnstone', '555 6677', '1 Way St.', 'Chicago', 'PO34 5FB', 'albie@johnstone.com', '2004-05-09 00:00:00', '(N/A)', 'Flat', 450);
40INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR77', 'Clark', 'Kent', '555 9999', '1 Super Way', 'Smallville', 'SM4 2ME', 'clark@supersite.com', '2004-05-09 00:00:00', '(N/A)', 'Flat', 400);
41INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR79', 'Joe', 'Bloggs', '123 4567', '5 High St', 'Paisley', 'PA2 2BB', 'joe@paisley.com', '2004-05-10 00:00:00', 'London (North West)', 'House', 450);
42INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR83', 'Edward', 'Scissorhands', '123 4567', '1 Snip St.', 'Scissorland', 'SC1 2XX', 'eddie@scix.com', '2004-05-21 00:00:00', '(N/A)', 'House', 300);
43INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR84', 'Albert', 'Enistein', '555 6789', '12 Long Island Way', 'New Jersey', 'NJ44 2RD', 'bert@nuclearintent.com', '2004-05-21 00:00:00', 'London (North West)', NULL, 450);
44INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR85', 'Snorrie', 'Sturrluson', '333 4567', '1 Vik Way', 'Rekjavik', 'RK22 3RD', 'snorrie@iceland.com', '2004-05-21 00:00:00', '(N/A)', NULL, 400);
45INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR86', 'Ferdinand', 'Oblogiotta', '123 5555', '12 Strumpetwise Street', 'Lagrange Orage', 'PP2 1BB', 'ferdy@orage.com', '2004-05-31 00:00:00', '(N/A)', 'House', 450);
46INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR87', 'Joe', 'Schmoe', '123 45678', '1 High St', 'Largs', 'KA30 9DD', 'joes@largy.com', '2004-05-31 00:00:00', '(N/A)', 'House', 550);
47INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR88', 'Bill', 'Gates', '123 5555', '1 Rich Street', 'Seattle', 'SE2 TTL', 'bill@gatesland.com', '2004-06-01 00:00:00', 'London (South)', 'House', 1000);
48INSERT INTO `Client` (`clientNo`, `fName`, `lName`, `telNo`, `street`, `city`, `postCode`, `email`, `joinedOn`, `region`, `preType`, `maxRent`) VALUES ('CR89', 'Bruce', 'Wayne', '555 6789', '1 Wayne Manor', 'Gotham', 'BA01 TT0', 'wayne@batty.com', '2004-11-22 00:00:00', 'Glasgow', 'House', 900);
49
50
51DROP TABLE IF EXISTS `PropertyForRent`;
52
53CREATE TABLE `PropertyForRent` (
54  `propertyNo` VARCHAR(5),
55  `street` VARCHAR(50), 
56  `city` VARCHAR(50), 
57  `postcode` VARCHAR(10), 
58  `type` VARCHAR(10), 
59  `rooms` INTEGER, 
60  `rent` INTEGER, 
61  `ownerNo` VARCHAR(5), 
62  `staffNo` VARCHAR(5), 
63  `branchNo` VARCHAR(5), 
64  `picture` VARCHAR(50), 
65  `floorPlan` VARCHAR(50)
66) ;
67
68
69
70INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PA14', '16 Holhead', 'Aberdeen', 'AB7 5SU', 'Single', 6, 650, 'CO46', 'SA9', 'B005', 'images/house2.jpg', 'images/plan1.jpg');
71INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PG16', '5 Novar Drive', 'Glasgow', 'G12 9AX', 'Condo', 4, 450, 'CO93', 'SG14', 'B003', 'images/house3.jpg', 'images/plan1.jpg');
72INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PG21', '18 Dale Road', 'Glasgow', 'G12', 'Single', 5, 600, 'CO87', 'SG37', 'B003', 'images/house4.jpg', 'images/plan1.jpg');
73INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PG36', '2 Manor Road', 'Glasgow', 'G32 4QX', 'Condo', 3, 375, 'CO93', 'SG37', 'B003', 'images/house5.jpg', 'images/plan1.jpg');
74INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PG4', '6 Lawrence Street', 'Glasgow', 'G11 9QX', 'Condo', 3, 350, 'CO40', 'SA9', 'B003', 'images/house2.jpg', 'images/plan1.jpg');
75INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PG97', 'Muir Drive', 'Aberdeen', 'AB42 1DD', 'Single', 3, 380, 'CO46', 'SA9', 'B003', 'images/house1.jpg', 'images/plan1.jpg');
76INSERT INTO `PropertyForRent` (`propertyNo`, `street`, `city`, `postcode`, `type`, `rooms`, `rent`, `ownerNo`, `staffNo`, `branchNo`, `picture`, `floorPlan`) VALUES ('PL94', '6 Argyll Street', 'London', 'NW2', 'Condo', 4, 400, 'CO87', 'SL41', 'B005', 'images/house3.jpg', 'images/plan1.jpg');
77
78
79DROP TABLE IF EXISTS `Staff`;
80
81CREATE TABLE staff (
82  `staffNo` VARCHAR(5),
83  `fName` VARCHAR(50), 
84  `lName` VARCHAR(50), 
85  `position` VARCHAR(50), 
86  `sex` int(1) DEFAULT 0, 
87  `DOB` DATETIME, 
88  `salary` FLOAT NULL, 
89  `branchNo` VARCHAR(5), 
90  `telephone` VARCHAR(20), 
91  `mobile` VARCHAR(20), 
92  `email` VARCHAR(40)
93) ;
94
95
96
97INSERT INTO `Staff` (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`, `telephone`, `mobile`, `email`) VALUES ('SA9', 'Marie', 'Howe', 'Assistant', 0, '1970-02-19 00:00:00', 9000, 'B005', '079912345', '07995674', 'MaryHowe@dream');
98INSERT INTO `Staff` (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`, `telephone`, `mobile`, `email`) VALUES ('SG14', 'David', 'Ford', 'Supervisor', 0, '1958-03-24 00:00:00', 18000, 'B003', '07998888', '078888888', 'DavidFord@dream');
99INSERT INTO `Staff` (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`, `telephone`, `mobile`, `email`) VALUES ('SG37', 'Ann', 'Beech', 'Assistant', 0, '1967-02-15 00:00:00', 9500, 'B003', '07877777', '0788999', 'AnnBeech@dream');
100INSERT INTO `Staff` (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`, `telephone`, `mobile`, `email`) VALUES ('SG5', 'Susan', 'Brand', 'Manager', 0, '1940-06-03 00:00:00', 30000, 'B004', '089999', '08899999', 'Susanbrand@dream');
101INSERT INTO `Staff` (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`, `telephone`, `mobile`, `email`) VALUES ('SL21', 'John', 'White', 'Manager', 0, '1945-10-01 00:00:00', 30000, 'B005', '1512345', '090555', 'Johnwhite@dreamHome');
102INSERT INTO `Staff` (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`, `telephone`, `mobile`, `email`) VALUES ('SL41', 'Julie', 'Lee', 'Assistant', 0, '1965-06-13 00:00:00', 9000, 'B005', '1514', '09055512346', 'JulieLee@dream');
103
104DROP TABLE IF EXISTS `Viewing`;
105
106CREATE TABLE `Viewing` (
107  `ID` INT,
108  `clientID` VARCHAR(5), 
109  `propertyNo` VARCHAR(5), 
110  `viewDate` DATETIME, 
111  `viewHour` VARCHAR(5), 
112  `comment` VARCHAR(255), 
113  `wishToRent` INT(1) DEFAULT 0
114) ;
115
116
117INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (1, 'CR83', 'PA14', '2004-07-01 00:00:00', '11', NULL, 0);
118INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (2, 'CR56', 'PG36', '2004-07-01 00:00:00', '12', NULL, 0);
119INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (3, 'CR85', 'PG4', '2004-07-01 00:00:00', '12', 'Tidy but too small', 0);
120INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (4, 'CR56', 'PA14', '2004-07-02 00:00:00', '10', NULL, 0);
121INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (5, 'CR77', 'PG4', '2004-07-02 00:00:00', '14', NULL, 0);
122INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (6, 'CR85', 'PA14', '2004-06-20 00:00:00', '12', NULL, 0);
123INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (7, 'CR85', 'PG21', '2004-06-21 00:00:00', '13', NULL, 0);
124INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (8, 'CR85', 'PA14', '2004-06-20 00:00:00', '12', NULL, 0);
125INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (9, 'CR88', 'PG21', '2004-11-22 00:00:00', '9', 'Not bad at all.', 0);
126INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (10, 'CR88', 'PG36', '2004-11-23 00:00:00', '9', 'Kitchen too small.', 0);
127INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (11, 'CR88', 'PG16', '2004-11-23 00:00:00', '9', NULL, 0);
128INSERT INTO `Viewing` (`ID`, `clientID`, `propertyNo`, `viewDate`, `viewHour`, `comment`, `wishToRent`) VALUES (12, 'CR77', 'PG21', '2004-11-25 00:00:00', '11', NULL, 0);
129
130CREATE TABLE PrivateOwner (
131ownerNo    VARCHAR(7) NOT NULL,
132fName      VARCHAR(15) NOT NULL, 
133lName      VARCHAR(15) NOT NULL, 
134address    VARCHAR(50) NOT NULL, 
135telNo      VARCHAR(13) NOT NULL
136);
137
138INSERT INTO PrivateOwner VALUES ('CO46', 'Joe',  'Keogh', '2 Fergus Dr, Aberdeen AB2 7SX','01224-861212');
139INSERT INTO PrivateOwner VALUES ('CO87', 'Carol','Farrel','6 Achray St, Glasgow G32 9DX', '0141-357-7419');
140INSERT INTO PrivateOwner VALUES ('CO40', 'Tina', 'Murphy','63 Well St, Glasgow G42',      '0141-943-1728');
141INSERT INTO PrivateOwner VALUES ('CO93', 'Tony', 'Shaw',  '12 Park Pl, Glasgow G4 0QR',   '0141-225-7025');
142
143select*from branch;
144select*from client;
145select*from propertyforrent;
146select*from staff;
147select*from viewing;
148select*from privateowner;