NOTE: ThIS assignment is MUCH more important THAN PREVIOUS ASSIGNMENTS – 5% OF COURSE GRADE.
Task:
There is a set of tables owned by “csc240r” available on the server at La Salle (password red, and host string cscdart). The schema for the tables is given below. All attributes for a table are listed, with the PK indicated via an underlined attribute. FKs are also indicated. Any questions about the contents of the database, please ask!!!! Your task is to develop queries to answer the questions given below, and run them against the demo database. Capture the execution/results in a (spool) file, and turn in the file on disk, and a print out – showing the commands and the successful execution of them. Remember to set the pagesize and the linesize to reasonable numbers so that results are fairly readable! Also, if you wait to spool until you have all of the queries correct (save your work using notepad or wordpad) then you will have a nice clean file that is easy to read and grade, and which will look like you got them all right on the first try! J
THIS IS AN INDIVIDUAL ASSIGNMENT. YOU ARE NOT TO COLLABORATE WITH OTHER STUDENTS!
There are 11 queries, each is worth 1 point; you can get a max of 11/10 on this assignment.
Schema:
OWNER – owner of a beach house
· Ownerid, first, middle, last, gender, streetaddress, city, county, state, zip, phone
HOUSE – a beach house
· Houseid, address, city, state, zip, phone, neighborhood, bedrooms, bathrooms, totalrooms, cots, sleeps, floor, type, blocks, facebeach, oceanview, oceanfront, garage, parking, tvs, cable, ac, heat, washer, dryer, frontporch, backporch, outsideshower, pool, hottub, tennis, beachtags, smoking, pets, securitydep, datelisted, condition, ownerid, listagent, baseprice
· FK ownerid to OWNER (M:1)
· FK listagent to AGENT (M:1) (an agent who has arranged with the owner to try to rent the house)
· NOTE: hottub has values of ‘Yes’ or ‘No’
OFFICE – realty office that rents beach houses
· Officeid, company,officename,address,city,state, zip, phone, fax
AGENT – real estate agent that works for a realty office
· Agentid, first, middle, last, officeid, phone, position, officenum
· FK officeid to OFFICE (M:1)
WEEKS – weeks that anybody might rent a beach house (even weird ones)
· Weekid, startdate, enddate, season, special
RENTER – a person who might rent a beach house
§ Renterid, first, middle, last, gender, address, city, county, state, zip, phone, age, looking, agentid
§ FK agentid to AGENT (M:1) (an agent who is helping the person try to find a house to rent)
AVAIL – a bridge between house and weeks showing all possible weeks that houses may be available (last attribute indicates if it is still available as opposed to already rented)
§ Availid, houseid, weekid, askingprice, available
§ FK houseid to HOUSE (M:1)
§ FK weekid to WEEKS (M:1)
§ NOTE – available has values of ‘Y’ or ‘N’
RENTAL – central transaction in the system – a house is rented to a particular renter for a particular week. A particular agent gets credit for the rental (could be the listing agent for the house, the agent working with the renter, or another agent)
§ Rentalid, houseid, renterid, weekid, askprice, agentid, reserved, discount, pricepaid
§ FK houseid to HOUSE (M:1)
§ FK renterid to RENTER (M:1)
§ FK weekid to WEEKS (M:1)
§ FK agentid to AGENT (M:1) (agent who gets credit for the rental)
Queries:
1) Show all unique seasons of the year (no duplicates, no other info other than season).
2) Show all info about the agent for all agents whose position is ‘Broker’
3) Show all houses that have at least 5 bedrooms, are considered to be in ‘New’ condition, and are less than 1 block from the beach – for each, show the houseid, address (skip city, state, and zip), bedrooms, bathrooms, sleeps, and ac (air conditioning).
4) For house 5246, show all rentals including the asking price and price paid, the name (last and first) and county of the renter, and the weekid for the week rented, sorted by the weekid (descending)
5) For owner 802, show all rentals of all their houses, including the house street address, the starting date for the week rented, the date reserved, the price paid, the last name and age of the renter, and the name (first and last) of the agent responsible for renting the house (NOT other agents such as the listing agent). Sort by house address with ties broken by week starting date. NO SHORTCUTTING by giving Ids instead of the info asked for!!!
6) Show all houses in ‘Poor’ condition on ‘Haven’ . Show the listing agent’s agent id, last name and first name, and the house’s street address, and number of bedrooms. Sort by last name of the listing agent (ascending)
7) For rentals in week 249 that received more than $50 discount, show the houseid, asking price, price paid, discount, and the ratio of price paid to asking price. Sort by the ratio (ascending).
8) For all houses less than 1 block from the beach, what is the lowest base price (HINT: base price is a general idea of the price, not adjusted for weeks. It is included under house)?
9) Show for all weeks (you can use weekid), the total number of rentals for each week. Sort by the weekid ascending.
10) Show total price paid for all rentals for houses with greater than $10,000 in bookings (total price paid). Show the address and the total price paid. Sort by the total, descending.
11) Show info about the house with the highest asking price that is still available for week 235. Show the street address, number of bedrooms, number of bathrooms, blocks from the beach, ac, condition, and asking price.