ITEC 340

Homework 2 – Multiple Listing Service

Fall 2005

Log on to Oracle and examine the tables mls.homes, mls.agents, and mls.agencies using the describe and select * commands.

Create .sql scripts for the following and give them the names suggested in the first column. As you work please remember….

• Please place each major clause of your sql query on a separate line.
• Do not “hard code” your query, i.e. your query should still work if the data changes.
• WORK ALONE, do not work on these queries together with a classmate, they are to be done individually.

h2a.sql	The listing number, city, square footage, listing agent's first name, listing agent's last name, and listing agent's phone number for all tutors 3000 square feet or larger.
h2b.sql	A list of all Agents that includes last name, first name, phone, office phone, and office fax.
h2c.sql	A single phone list of office managers and agents. The list should include only three columns: last name, first name and phone number. The list should be ordered by last name and within last name by first name.
h2d.sql	The agency name, office phone and a count of the number of agents for each agency.
h2e.sql	A list of agency names, the number of homes listed with the agency, and the total dollar value of homes listed for each agency.
h2f.sql	The first name, last name, and agency name of any agent who does not have a house listed..
h2g.sql	The first name, last name, and agency name of the agent that has the most houses listed is Blacksburg. (may be the most difficult other than the bonus)
h2h.sql	The first name and last name of any agent that has listings in both Blacksburg and Radford.
h2i.sql	The last name, first name, and number of homes listed for ALL agents including those that don't have any listings ordered starting with the agent with the most listings.
h2.j	The agentid, lname, and fname of any agent who has listings in Roanoke ONLY.
bonus.sql	The agency name, agent last name, agent first name, and total dollar value of homes listed for the top agent of each agency.

Some of these are quite challenging. DO NOT wait until the last minute to start these.

Create a folder named RU02 in your itec340 student folder on Neelix. 

Place your scripts in that folder by 5pm on Thursday,  September 15th.