The data below is for a real estate service. They track individual sales people and how much of a sale (stored as a decimal, not a percentage) that they get. There are the owners of properties, then the sales data.

If a property is listed with no price and no salesperson, it has not yet sold.

Start by running the following SQL to set up the database:

create table owners (

id int auto_increment not null primary key,

firstname varchar(200) not null,

lastname varchar(200) not null

);

create table realtors (

id int auto_increment not null primary key,

name varchar(200) not null,

region varchar(200) not null,

commission decimal(2,2) not null

);

create table sales (

id int auto_increment not null primary key,

owner_id int not null,

realtor_id int null,

address varchar(200) not null,

city varchar(200) not null,

state varchar(2) not null,

sale_price decimal(9,0) null

);

insert into owners (id,firstname,lastname) values (1,'Jane','Doe');

insert into owners (id,firstname,lastname) values (2,'Bill','Smith');

insert into owners (id,firstname,lastname) values (3,'Fred','Johnson');

insert into owners (id,firstname,lastname) values (4,'Svetlana','O\'Rourke');

insert into realtors (name, region, commission) values ('Honest Bill','Providence',.05);

insert into realtors (name, region, commission) values ('Freddy Fingers','Providence',.08);

insert into realtors (name, region, commission) values ('Helen Homeseller','Warwick',.05);

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (1,1,100000, '10 Main St','Providence','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (1,2,200000, '12 Main St','Providence','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (2,1,150000, '15 Main St','Providence','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (3,2,350000, '30 Rolfe Sq','Cranston','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (4,3,50000, '30 Alex St','Warwick','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (4,3,150000, '101 George Ave','Warwick','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (4,NULL,NULL, '37 Meyers Ct','East Greenwcih','RI');

insert into sales (owner_id, realtor_id, sale_price, address, city, state) values (4,NULL,NULL, '10 Owens Cir','Warwick','RI');

Provide queries in a plain text file that answer the follow requests:

1) The business is trying to get a list of all owners and all realtors, so they can check off who worked with who. Provide a query that generates this, including the owner firstname, owner lastname, and realtor name

2) Give a list of properties that have been sold. Include the address, salesperson name, and owner name.

3) Give a list of realtors and the total number of properties they have sold. Include salesperson name and property count.

4) Give a list of all towns and the average sale price for the town.

5) Give a list of all properties in the database, and the realtor name, if available. Include address, city, state, and realtor name.