ITEC 340

Homework 2 – Multiple Listing Service

Fall 2005

 

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 tudors 3000 square feet or larger.

 

SELECT listingnum, city, sqfootage, fname, lname, phone

FROM mls.homes NATURAL INNER JOIN mls.agents

WHERE sqfootage>=3000 AND style='TUDOR';

h2b.sql

A list of all Agents that includes last name, first name, phone, office phone, and office fax.

 

SELECT lname, fname, phone, officephone, officefax

FROM mls.agencies INNER JOIN mls.agents

ON mls.agencies.name = mls.agents.agency;

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.

 

SELECT lname, fname, phone

FROM mls.agents

    UNION

SELECT managerlname, managerfname,officephone

FROM mls.agencies

ORDER BY lname, fname;

h2d.sql

The agency name, office phone and a count of the number of agents for each agency.

 

SELECT agency, officephone, COUNT(agentid) AS NumOfAgents

FROM mls.agents INNER JOIN mls.agencies

ON mls.agents.agency = mls.agencies.name

GROUP BY agency, officephone;

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.

 

SELECT agency, COUNT(listingnum) AS NumOfHomes, SUM(price) AS SumOfPrice

FROM mls.agents NATURAL INNER JOIN mls.homes

GROUP BY agency;

h2f.sql

The first name, last name, and agency name of any agent who does not have a house listed..

 

SELECT fname, lname, agency

FROM mls.agents NATURAL LEFT OUTER JOIN mls.homes

WHERE listingnum IS NULL;

 

  /* or another query that will work is  */

 

SELECT fname, lname, agency

FROM mls.agents

WHERE agentid IN (SELECT agentid FROM mls.agents

                                         MINUS

                                    SELECT agentid FROM mls.homes);

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)

 

SELECT lname, fname, agency

FROM mls.agents

WHERE agentid IN

       (SELECT agentid

        FROM mls.homes

        WHERE city='Blacksburg'

        GROUP BY agentid

        HAVING COUNT(listingnum) =

                (SELECT MAX(CountOfHomes)

                 FROM

                           (SELECT COUNT(listingnum) AS CountOfHomes

                           FROM mls.homes

                           WHERE city='Blacksburg'

                           GROUP BY agentid )));

 

  /* or another query that works which was submitted by a student nests the max and count functions to save on query nesting */

 

SELECT lname, fname, agency

FROM mls.agents

WHERE agentid IN

       (SELECT agentid

        FROM mls.homes

        WHERE city='Blacksburg'

        GROUP BY agentid

        HAVING COUNT(listingnum) =

                (SELECT MAX(COUNT(listingnum)) AS CountOfHomes

                           FROM mls.homes

                           WHERE city='Blacksburg'

                           GROUP BY agentid ));

h2h.sql

The first name and last name of any agent that has listings in both Blacksburg and Radford.

 

SELECT fname, lname

FROM agents

WHERE agentid IN (SELECT agentid FROM mls.homes WHERE city='Radford'

                                         INTERSECT

                                    SELECT agentid FROM mls.homes WHERE city='Blacksburg');

 

  /* or another query that will work is  */

 

SELECT fname, lname FROM mls.agents NATURAL INNER JOIN mls.homes WHERE city='Radford'

    intersect

SELECT fname, lname FROM mls.agents NATURAL INNER JOIN mls.homes WHERE city='Blacksburg';

 

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.

 

SELECT lname, fname, COUNT(listingnum) AS CountOfHomes

FROM mls.agents NATURAL LEFT OUTER JOIN mls.homes

GROUP BY lname, fname

ORDER BY CountofHomes DESC;

h2.j

The agentid, lname, and fname of any agent who has listings in Roanoke ONLY.

 

SELECT agentid, lname, fname

FROM mls.homes NATURAL INNER JOIN mls.agents

WHERE city='Roanoke'

      MINUS

SELECT agentid, lname, fname

FROM mls.homes NATURAL INNER JOIN mls.agents

WHERE city !=('Roanoke');

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.

 

SELECT lname, fname, MaxTable.agency AS Agency, MaxTable.MaxAgentTotal AS Total FROM

(SELECT lname, fname, agency, SUM(price) AS AgentTotal

        FROM mls.agents NATURAL INNER JOIN mls.homes

        GROUP BY lname, fname, agency) SumTable

INNER JOIN

(SELECT agency, MAX(AgentTotal) AS MaxAgentTotal

            FROM

                        (SELECT agentid, agency, SUM (price) AS AgentTotal

                        FROM mls.homes NATURAL INNER JOIN mls.agents

                        GROUP BY agentid, agency)

            GROUP BY agency) MaxTable

on SumTable.agency = MaxTable.agency and SumTable.AgentTotal=MaxTable.MaxAgentTotal;