Question

leaseNo banneriD flatNo flat Address F56 F78 11 Storrie Road, Paisley F79 4 High Streer, Paisley B017706 B017706 88 B01339989 B012124 102 B034511 01/09/2010 109/2011 01/09/2011 01/09/2011 01/09/2012 30V06/2011 30V06/2012 10259 10364 10566 11067 120 Lady Lane, Paisley 0V06/2012 30v06/2013 banneriD fName B017706 ane BO13399 Tom B012124 Karen B034511 Watt Jones Black F78 F78 Figure 14.24 Tables (in 3NF) displaying sample data of university accommodation.

Please provide SQL statement for the question below:

Configure who has been most frequently working as a banner? Please provide a name.

To note: Students can lease university flats and some of the details of lease held by students for places in university flats are shown in Figure 14.24. • Lease table use leaseNo as a primary key and bannerID and placeNo as foreign keys. • Flat table use flatNo as a primary key and stores addresses of flat – university apartment. • Banner table use bannerNo as a primary key and stores resident assistant student names. • Flat_room tables stores placeNo and flatNo. placeNo as a primary key and flatNo as foreign key.

Answer 1

Banner Table

create table banner (bannerID varchar(30) primary key, fName varchar(10), IName varchar(10) );

Flat table

create table flat (flatNo varchar(10) primary key, flat_Address varchar(30) );

Flat_room table

create table flat_room (placeNo int(10) primary key, flatNo varchar(10) references flat(flatNo));

Lease Table

create table lease (leaseNo int(10) primary key, bannerID varchar(10) references banner(bannerID), placeNo int(10) references flat_room (placeNo), startDate varchar(12),finishDate varchar(12));

===============================

Insert Values

Banner table

insert into banner (bannerID, fName,IName) values('B017706','Jane','Watt'), ('B013399', 'Tom', 'Jones'), ('B012124', 'Karen','Black'), ('B034511', 'Steeven','Smith');

Flat table

insert into flat(flatNo, flat_Address) values ('F56','34 High street paisley'), ('F78','111 Strrie Road, Paisley'), ('F79','120 Lady lane paisley');

Flat_room table

insert into flat_room (placeNo, flatNo) values (78,'F56'), (88,'F78'), (89,'F78'), (102,'F79');

Lease Table

insert into lease (leaseNo, bannerID, placeNo, startDate, finishDate) values (10003, 'B017706', 78,'1/09/2010' ,'30/06/2011'),(10259, 'B017706', 88,'1/09/2011' ,'30/06/2012'),(10364, 'B013399', 89,'1/09/2011' ,'30/06/2012'),(10566, 'B012124', 102,'1/09/2011' ,'30/06/2012'),(11067, 'B034511', 88,'1/09/2012' ,'30/06/2013'),(11169, 'B013399', 78,'1/09/2012' ,'30/06/2013');

Most freequently working as a banner

select fname,IName from banner where bannerID in (select bannerID from lease group by bannerID having count(bannerID)>1);

====================