Answered Essay: 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

Buy Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Help Me Write My Essay - Reasons:

Best Online Essay Writing Service

We strive to give our customers the best online essay writing experience. We Make sure essays are submitted on time and all the instructions are followed.

Our Writers are Experienced and Professional

Our essay writing service is founded on professional writers who are on stand by to help you any time.

Free Revision Fo all Essays

Sometimes you may require our writers to add on a point to make your essay as customised as possible, we will give you unlimited times to do this. And we will do it for free.

Timely Essay(s)

We understand the frustrations that comes with late essays and our writers are extra careful to not violate this term. Our support team is always engauging our writers to help you have your essay ahead of time.

Customised Essays &100% Confidential

Our Online writing Service has zero torelance for plagiarised papers. We have plagiarism checking tool that generate plagiarism reports just to make sure you are satisfied.

24/7 Customer Support

Our agents are ready to help you around the clock. Please feel free to reach out and enquire about anything.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

HOW OUR ONLINE ESSAY WRITING SERVICE WORKS

Let us write that nagging essay.

STEP 1

Submit Your Essay/Homework Instructions

By clicking on the "PLACE ORDER" button, tell us your requires. Be precise for an accurate customised essay. You may also upload any reading materials where applicable.

STEP 2

Pick A & Writer

Our ordering form will provide you with a list of writers and their feedbacks. At step 2, its time select a writer. Our online agents are on stand by to help you just in case.

STEP 3

Editing (OUR PART)

At this stage, our editor will go through your essay and make sure your writer did meet all the instructions.

STEP 4

Receive your Paper

After Editing, your paper will be sent to you via email.

× How can I help you?