SP2009_310_Samplefinal_solution

SP2009_310_Samplefinal_solution - Interested = yes means...

Info icon This preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
ANY DEVICE WITH AN ON/OFF SWITCH MUST BE TURNED OFF FOR THE DURATION OF THE TEST. YOU MAY NOT LEAVE THE CLASSROOM UNTIL YOU HAVE HANDED IN YOUR TEST. BUYER HFORSALE VISITINFO Interested = yes, means that after visiting the house, the potential buyer showed an interest in possibly buying the house. AGENT GEORGE MASON UNIVERSITY SCHOOL OF MANAGEMENT MIS 310/001 INTRODUCTION TO DATABASE MANAGEMENT CLOSED BOOK Sample Final SP2009 _Name:__________________________________________________________________ Q1. A small real estate brokerage company has a small database to keep track of visits by potential buyers to the houses its agents are trying to sell. The database keeps track of actual as well as potential buyers. Many potential buyers visit a house but only one of them will ultimately buy it, if at all. Answer the following questions based on the database shown below. Primary keys are shown in solid underline, and foreign keys by dotted underline. BNUM BNAME BEMPLOYER BUDGET B230 Shelly Johnson World Bank $ 500,000 B231 James Kirkland Smithsonian $ 750,000 B232 James Johnson Washington Post $ 1,200,000 B233 Arthur Miller IMF $ 900,000 B234 Shelly Jefferson Johns Hopkins Univ $ 375,000 B235 Arthur Jones American Univ $ 377,000 HNUM HLOCATION LISTPRICE SOLDPRICE SOLDTO ------------- LISTINGAGENT -------------------- H0001 Burke $425,000 $435,000 B235 A1001 H0007 Fairfax City $690,000 $ 675,000 B233 A1008 H0012 Centreville $800,000 A1012 H0019 Clifton $1,100,000 A1001 H0023 Annandale $500,000 $550,000 B232 A1024 BNUM ------------- HNUM --------------- INTERESTED VDATE B233 H0023 Yes 1-sep-07 B233 Page 1 of 13
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
H0007 Yes 1-sep-07 B235 H0001 Yes 8-aug-07 B230 H0012 Yes 11-nov-07 B230 H0019 No 11-nov-07 B231 H0001 No 9-oct-07 B231 H0019 Yes 9-dec-07 B232 H0007 Yes 6-dec-07 B232 H0019 No 30-nov-07 B232 H0023 Yes 4-oct-07 ANUM ANAME 2007_SALES EXPERIENCE A1001 George Eckerle 6,000,000 15 A1002 Ross Vann 3,000,000 7 A1003 Jill Raines 11,000,000 23 A1007 Cindy Ashwood 9,000,000 11 A1008 Carla Fuentes 0 0 A1012 Boris Johnson 0 0 A1024 Brenda Ferguson 10,000,000 Page 2 of 13
Image of page 2
17 EXPERIENCE refers to the number of years the agent has been selling houses. For new agents – i.e. those who started in 2007, experience=0. 2007_SALES refers to the total dollar value of houses sold by the agent in 2007. Page 3 of 13
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
(a) [7points] Write an SQL statement to answer the following query: “For every house that has not yet been sold, list the house ID number and the total number of buyer visits to the house.
Image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern