USA: +1 725-224-8795 
Sign in
Question about MySQL:
You are to do a number of queries on a database with the following tables:
APT BUILDING (building id int, name varchar(20), address varchar(20), zone int, primary key (building id) );
SUITES (suite id int, building id int, bedrooms int, rent int, occupied boolean, primary key (suite id));
TENANT (name varchar(20), suite id int, year of birth int);
These tables help an apartment rental management company look after a number of apartments. The APT BUILDING table contains the id, name and address for the apartment buildings as well as which zone each is in. The SUITES table contains information about all apartment suites in the buildings. In particular each suite is identified by a suite id and information is given as to which building the suite is in as well as the number of bedrooms, the rent and whether it is occupied. The TENANT table contains the names and years of birth of tenants. Each tenant tuple also indicates a suite id.
Below are a number of queries. For each query, express the query in MySQL. Submit a text document that contains the MySQL queries executed and their output in interactive output format. This can be done in batch mode or via a mysql script, but for this small assignment it is probably just as easy to copy the command and output from your interactive window running mysql and paste it into a text document.
Note that you will probably want to initially type your commands/queries into a second text file, so that when an error occurs it is easy to edit them and recopy them into the interactive window to try them again.
In order to execute your MySQL queries, you will need to first create the 3 tables. In addition, you will need to fill the tables with their data. With this assignment, there are four text files: a5tables.txt, apt.txt, suites.txt, and tenant.txt. The first contains the sql commands to create the tables, and the other three contain test data to be loaded into the three tables. The way to load the APT BUILDING data is as follows:
load data local infile ’apt.txt’ into table APT BUILDING;
assuming that the file apt.txt is in the current directory (log into mysql when in the directory that contains the files). The other two are similar. The mysql system expects that the files use LF for end-of-line, and the tab character is used to separate the attribute values of one tuple. The data files have been set up in this way, so be careful not to change the file content.
Of course, before you start your queries, you should output the description and contents of each table to ensure that everything was set up correctly. Include this output with your assignment.
The required queries are:
a. How many vacant 2 bedroom suites are being managed?
b. Give the addresses of buildings in zone 1 that do not have any suites renting for more than $1000 per month.
c. Give the names of buildings with no vacant suites.
d. Give the names of the tenants that are renting in the building called sutherland tower
apt:
1000 smug towers 145 downtown st. 2
1002 skinny towers 150 downtown st. 2
1004 bigshot towers 175 downtown st. 2
1010 middle manor 25 city part ave. 3
1020 midrise 2.0 35 city park ave. 3
1030 mid to good place 45 city park st. 3
1160 slow towers 2 sutherland place 1
1170 lowrise manor 4 sutherland place 1
1180 tiny apt 6 sutherland St. 1
1006 sutherland tower 100 sutherland ave. 1
suits:
745 1000 1 800 0
746 1000 1 810 1
747 1000 2 1000 0
748 1002 2 1010 0
749 1004 2 1020 1
750 1010 1 750 0
751 1020 2 950 0
752 1020 1 400 1
753 1020 2 1200 0
754 1020 5 3000 0
755 1030 2 1100 0
760 1006 1 500 1
761 1006 2 900 1
762 1006 2 950 1
770 1180 2 990 1
771 1180 1 600 1
772 1180 2 900 0
773 1170 3 1100 1
774 1170 2 1000 1
780 1160 1 200 1
781 1160 2 400 1
782 1004 2 1100 1
783 1004 2 1111 1
tenant:
Jaabb 745 1994
Jaabb 749 1994
Jaabb 761 1994
Jaacc 752 1914
Jaadd 760 1924
Keebb 762 1934
Keecc 770 1944
Keedd 771 1954
Liiff 773 1964
Liigg 774 1974
Liihh 780 1984
Moojj 781 2004
Mookk 782 2014
Mooll 783 2000
tables:
drop table APT_BUILDING;
drop table SUITES;
drop table TENANT;
create table APT_BUILDING (building_id int, name varchar(20), address varchar(20), zone int, primary key (building_id) );
create table SUITES (suite_id int, building_id int, bedrooms int, rent int, occupied boolean, primary key (suite_id));
create table TENANT (name varchar(20), suite_id int, year_of_birth int);
Expert Answer
create table APT_BUILDING (building_id int, name varchar(20), address varchar(20), zone int, primary key (building_id) );
You have made changes to the database.
insert into APT_BUILDING values(1000, ‘smug towers’, ‘145, downtown st’, 2);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1002, ‘skinney towers’, ‘150, downtown st’, 2);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1004, ‘bigshot towers’, ‘175, downtown st’, 2);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1010, ‘middle manor’, ’25, city part ave.’, 3);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1020, ‘midrose 2.0′, ’35, city park ave.’, 3);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1030, ‘mid to good place’, ’45, city park ave.’, 3);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1160, ‘slow towers’, ‘2 sutherland place’, 1);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1170, ‘lowrise manor’, ‘4 sutherland place’, 1);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1180, ‘tiny apt’, ‘6 sutherland place’, 1);
You have made changes to the database. Rows affected: 1
insert into APT_BUILDING values(1006, ‘sutherland tower’, ‘100 sutherland ave.’, 1);
You have made changes to the database. Rows affected: 1
create table SUITES (suite_id int, building_id int, bedrooms int, rent int, occupied boolean, primary key (suite_id));
You have made changes to the database.
insert into SUITES values(745, 1000, 1, 800 ,0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(746, 1000, 1, 810 ,1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(747, 1000, 2, 1000 ,0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(748, 1002, 2, 1010 ,0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(749, 1004, 2, 1020 ,1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(750, 1010, 1, 750 ,0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(751, 1020, 2, 950 ,0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(752, 1020, 1, 400, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(753, 1020, 2, 1200, 0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(754, 1020, 5, 3000, 0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(755, 1030, 2, 1100, 0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(760, 1006, 1, 500, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(761, 1006, 2, 900, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(762, 1006, 2, 950, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(770, 1180, 2, 990, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(771, 1180, 1, 600, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(772, 1180, 2, 900, 0);
You have made changes to the database. Rows affected: 1
insert into SUITES values(773, 1170, 3, 1100, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(774, 1170, 2, 1000, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(780, 1160, 1, 200, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(781, 1160, 2, 400, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(721, 1004, 2, 1100, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(782, 1004, 2, 1100, 1);
You have made changes to the database. Rows affected: 1
insert into SUITES values(783, 1004, 2, 1111, 1);
You have made changes to the database. Rows affected: 1
create table TENANT (name varchar(20), suite_id int, year_of_birth int);
You have made changes to the database.
insert into TENANT values(‘jaabb’, 745, 1994);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘jaabb’, 749, 1994);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘jaabb’, 761, 1994);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Jaacc’, 752, 1914);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Jaadd’, 760, 1924);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Keebb’, 762, 1934);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Keecc’, 770, 1944);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Keedd’, 771, 1954);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Liiff’, 773, 1964);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Liigg’, 777, 1974);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Liihh’, 780, 1984);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Moojj’, 781, 2004);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Mookk’, 782, 2014);
You have made changes to the database. Rows affected: 1
insert into TENANT values(‘Mooll’, 783, 2000);
You have made changes to the database. Rows affected: 1
