数据库Dreamhome——lab1

-- 1
select * from staff;
-- 2
select fName,lName,position,salary from staff where salary >=8000 and salary <=20000;
-- 3
select * from staff where branchNo='B003'or branchNo='B007';
-- 4
select position,count(*)  from staff group by position order by count(*);
-- 5
select branchNo,street,city from branch where city='London';
-- 6
select * from staff where fName like "%an";
-- 7
select * from client order by ClientNo;
-- 8
select propertyNo,street from propertyforrent where staffNo is null;
-- 9
select branchNo, count(propertyNo) from propertyforrent group by branchNo;
-- 10
select * from propertyforrent order by rent desc;
-- 11
select propertyNo,street,city,postcode,type,rooms,rent*12 as yearRent from propertyforrent;
-- 12
select * from staff where position!='Manager' and salary >=12000;
-- 13
select count(*),position from staff where position='Manager';
-- 14
select avg(salary) from staff;
-- 15
select avg(salary),branchNo from staff group by branchNo;
-- 16
select avg(salary),branchNo,count(staffNo) from staff group by branchNo having count(staffNo)>1;
-- 17
select max(salary),min(salary),max(salary)-min(salary) as difference from staff;
-- 18
select *,count(propertyNo) from propertyforrent where rooms=3;
-- 19
select avg(salary),position from staff group by position having position ='Assistant' or position ='Manager';

select avg(salary),position from staff where position='Assistant' or position='Manager' group by position;//注意where必须在group by的前面
select avg(salary) from staff where position = 'Assistant'or position='Manager';
-- 20
select round(rent/4,2) as rentInPound from propertyforrent;
select round(rent/4*100,2) as rentInPenny from propertyforrent;
-- 21
select * from propertyforrent where propertyNo not in (select propertyNo from viewing);

建表语句

CREATE TABLE IF NOT EXISTS Branch (
   branchNo VARCHAR(4) NOT NULL,
	 street VARCHAR(40) NOT NULL,
	 city VARCHAR(20) NOT NULL,
	 postcode VARCHAR(10) NOT NULL,
	 PRIMARY KEY (branchNo)
)

INSERT INTO Branch (branchNo, street, city, postcode)
VALUES ("B005", "22 Deer Rd", "London", "SW1 4EH"),
			 ("B007", "16 Argyll St", "Aberdeen", "AB2 3SU"),
			 ("B003", "163 Main St", "Glasgow", "G11 9QX"),
			 ("B004", "32 Manse Rd", "Bristol", "BS99 1NZ"),
			 ("B002", "56 Clover Dr", "London", "NW10 6EU")

CREATE TABLE IF NOT EXISTS Staff (
   staffNo VARCHAR(4) NOT NULL,
	 fName VARCHAR(20) NOT NULL,
	 lName VARCHAR(20) NOT NULL,
	 position VARCHAR(20) NOT NULL, 
	 sex VARCHAR(1) NOT NULL, 
	 DOB DATE NOT NULL,
	 salary INT UNSIGNED,
	 branchNo VARCHAR(4),
	 PRIMARY KEY (staffNo)
)

INSERT INTO Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
VALUES ("SL21", "John", "White", "Manager", "M", '1945-10-01', 30000, "B005"),
			 ("SG37", "Ann", "Beech", "Assistant", "F", '1960-11-10', 12000, "B003"),
			 ("SG14", "David", "Ford", "Supervisor", "M", '1958-03-24', 18000, "B003"),
			 ("SA9", "Mary", "Howe", "Assistant", "F", '1970-02-19', 9000, "B007"),
			 ("SG5", "Susan", "Brand", "Manager", "F", '1940-06-03', 24000, "B003"),
			 ("SL41", "Julie", "Lee", "Assistant", "F", '1965-06-13', 9000, "B005")

CREATE TABLE IF NOT EXISTS PropertyForRent (
   propertyNo VARCHAR(4) NOT NULL,
	 street VARCHAR(40) NOT NULL,
	 city VARCHAR(20) NOT NULL,
	 postcode VARCHAR(10) NOT NULL,
	 type VARCHAR(10) NOT NULL,
	 rooms TINYINT UNSIGNED NOT NULL,
	 rent INT UNSIGNED NOT NULL,
	 ownerNo VARCHAR(4) NOT NULL,
	 staffNo VARCHAR(4),
	 branchNo VARCHAR(4) NOT NULL,
	 PRIMARY KEY (propertyNo)
)

INSERT INTO PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
VALUES ("PA14", "16 Holhead", "Aberdeen", "AB7 5SU", "House", 6, 650, "CO46", "SA9", "B007"),
			 ("PL94", "6 Argyll St", "London", "NW2", "Flat", 4, 400, "CO87", "SL41", "B005"),
			 ("PG4", "6 Lawrence St", "Glasgow", "G11 9QX", "Flat", 3, 350, "CO40", NULL, "B003"),
			 ("PG36", "2 Manor Rd", "Glasgow", "G32 4QX", "Flat", 3, 375, "CO93", "SG37", "B003"),
			 ("PG21", "18 Dale Rd", "Glasgow", "G12", "House", 5, 600, "CO87", "SG37", "B003"),
			 ("PG16", "5 Navor Dr", "Glasgow", "G12 9AX", "Flat", 4, 450, "CO93", "SG14", "B003")

CREATE TABLE IF NOT EXISTS Client (
   clientNo VARCHAR(4) NOT NULL,
	 fName VARCHAR(20) NOT NULL,
	 lName VARCHAR(20) NOT NULL,
	 telNo VARCHAR(20) NOT NULL,
	 prefType VARCHAR(10) NOT NULL,
	 maxRent INT UNSIGNED NOT NULL,
	 eMail VARCHAR(40) NOT NULL,
	 PRIMARY KEY (clientNo)
)

INSERT INTO Client (clientNo, fName, lName, telNo, prefType, maxRent, eMail)
VALUES ("CR76", "John", "Kay", "0207-774-5632", "Flat", 425, "john.kay@gmail.com" ),
			 ("CR56", "Aline", "Stewart", "0104-848-1825", "Flat", 350, "astewart@hotmail.com" ),
			 ("CR74", "Mike", "RitChie", "01475-392178", "House", 750, "mritchie01@yahoo.co.uk" ),
			 ("CR62", "Mary", "Tregear", "01224-196720", "Flat", 600, "maryt@hotmail.co.uk" )

CREATE TABLE IF NOT EXISTS PrivateOwner (
   ownerNo VARCHAR(4) NOT NULL,
	 fName VARCHAR(20) NOT NULL,
	 lName VARCHAR(20) NOT NULL,
	 address VARCHAR(40) NOT NULL,
	 telNo VARCHAR(20) NOT NULL,
	 eMail VARCHAR(40) NOT NULL,
	 password VARCHAR(20) NOT NULL,
	 PRIMARY KEY (ownerNo)
)

INSERT INTO PrivateOwner ()
VALUES ("CO46", "Joe", "Keogh", "2 Fergus Dr, Aberdeen AB2 7SX", "01224-861212", "jkeogh@lhh.com", "12345678" ),
			 ("CO87", "Carol", "Farrel", "6 Achray St, Glasgow G32 9DX", "0141-357-7419", "cfarrel@gmail.com", "12345678" ),
			 ("CO40", "Tina", "Murphy", "63 Well St, Glasgow G42", "0141-943-1728", "tinam@hotmail.com", "12345678" ),
			 ("CO93", "Tony", "Shaw", "12 Park Pl, Glasgow G4 0Qr", "0141-225-7025", "tony.shaw@ark.com", "12345678" )

CREATE TABLE IF NOT EXISTS Viewing (
	 clientNo VARCHAR(4) NOT NULL,
	 propertyNo VARCHAR(4) NOT NULL,
	 viewDate DATE NOT NULL,
	 comment VARCHAR(40)
)

INSERT INTO Viewing (clientNo, propertyNo, viewDate, comment)
VALUES ("CR56", "PA14", '2013-03-24', "too small" ),
			 ("CR76", "PG4", '2013-04-20', "too remote" ),
			 ("CR56", "PG4", '2013-05-26', NULL ),
			 ("CR62", "PA14", '2013-05-14', "no dining room" ),
			 ("CR56", "PG36", '2013-04-28', NULL )

CREATE TABLE IF NOT EXISTS Registration (
	 clientNo VARCHAR(4) NOT NULL,
	 branchNo VARCHAR(4) NOT NULL,
	 staffNo VARCHAR(4) NOT NULL,
	 dateJoined DATE NOT NULL
)

INSERT INTO Registration (clientNo, branchNo, staffNo, dateJoined)
VALUES ("CR76", "B005", "SL41", '2013-01-02' ),
			 ("CR56", "B003", "SG37", '2012-04-11' ),
			 ("CR74", "B003", "SG37", '2011-11-16' ),
			 ("CR62", "B007", "SA9", '2012-05-07' )