Question & Answer: You are to do a number of queries on a database with the following tables:…..

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

Question & Answer: You are to do a number of queries on a database with the following tables:..... 1Question & Answer: You are to do a number of queries on a database with the following tables:..... 2Question & Answer: You are to do a number of queries on a database with the following tables:..... 3

Still stressed from student homework?
Get quality assistance from academic writers!