Problem Set 8 — Comments

Question 1: For each question, provide the SQL command that performs the requested action and run your command on your database. Note that the commands modify the state of the database, so you need to do them in order.

Insert a user into your users table with user name alyssa, email aph@cs.virginia.edu, last name Hacker and firstnames Alyssa P..

INSERT INTO users (user, email, lastname, firstnames)
  VALUES ('alyssa', 'aph@cs.virginia.edu', 'Hacker', 'Alyssa P.')

Insert a user into your users table with user name ben, email bb@cs.virginia.edu, last name Bitdiddle and firstnames Ben.

INSERT INTO users (user, email, lastname, firstnames)
  VALUES ('ben', 'bb@cs.virginia.edu', 'Bitdiddle', 'Ben')

Select the lastname of all users in your table. The response should be a table like this (of course, your result will be different because you put yourself in the table instead of me):

lastname

Evans

Hacker

Bitdiddle
```
SELECT lastname FROM users
```
Select the lastname and firstnames of all users in your table in alphabetical order by firstnames. The response should be a table like:

firstnames lastname

Alyssa P. Hacker

Ben Bitdlddle

David Evans
```
SELECT firsnames, lastname FROM users ORDER BY firstnames
```
Select the email address of all users in your table with lastname matching Hacker. The response should be the table:

email

aph@cs.virginia.edu
```
SELECT email FROM users WHERE lastname='Hacker'
```
Delete all entries from your table whose id does not equal the id for your entry. (Note that the MySQL interface will give you a confirmation on DELETE commands, since a mistake could remove all the records you want from the table. It is a good idea with DELETE commands to use a LIMIT n as part of the query to make sure only the right number of entries are deleted. For example, for this question you would do DELETE FROM users ... LIMIT 2 to ensure that no more than 2 entries are deleted.
```
DELETE FROM users WHERE id <> 2 LIMIT 2
```

lastname
Evans
Hacker
Bitdiddle

firstnames	lastname
Alyssa P.	Hacker
Ben	Bitdlddle
David	Evans

email
aph@cs.virginia.edu

Question 2: The provided lookup(restaurantno) method in restaurants.py is missing the SQL query needed to find the restaurant. Complete the definition of lookup by filling in the missing SQL query. If your definition is correct, you should be able to click on the map markers, and then the restaurant name in the pop-up, to see a page with information on the selected restaurant.

query = "SELECT * FROM restaurants WHERE restaurantid = " + str(restaurantno)

Question 3: Complete the show-reviews.cgi file so that clicking on Display Reviews displays a page showing all the restaurant reviews, sorted from highest star rating to worst. To accomplish this, you should examine and understand the code in show-restaurants.cgi. To display the reviews in the right order, you will also need to modify the reviews.getAll() method defined in reviews.py.

Here is the modification to reviews.getAll() query:

c.execute ("SELECT * FROM reviews ORDER BY stars DESC")

Here is the relevant code in show-reviews.cgi:

rlist = reviews.getAll ()

for rev in rlist:
    rid = rev.getRestaurantId()
    rest = restaurants.lookup(rid)
    print "" + rest.getName() + " (" + rest.getCuisine() + ")"
    print ""
    rev.display()
    print ""
    print ""

print "
"