SQL Queries

Sample Solution

Objectives

• To construct a database using phpMyAdmin
• To practice forming MySQL queries

Database

Take a look at this database information and then load it into phpMyAdmin.

Queries

Record each query for each task below and a copy of the answer that is produced when it is run in the phpMyAdmin environment.

1. Write a query that prints the name and population of all cities whose population is less than or equal to 50,000.
2. Write a query that prints the name and population of all cities whose population is less than or equal to 50,000. Print the cities in ascending order based on population.
3. Write a query that prints the name and population of the two most populous cities. Print the cities in descending order based on population.
4. Write a query that prints the number of cities whose population is less than or equal to 50,000.
5. Write a query that prints the number of users who have browse permission.
6. Write a query that prints the first name and last name of all users who either live in Bozeman or Billings.
7. Write a query that prints the first name and last name of all users who live in Bozeman and have admin permission.
8. Write a query that prints the name of each access type (add, admin, browse) and the number of users who have that access type.
9. Write a query that prints the name of each state and the number of users that come from that state. Only print states where there is at least 1 user.
10. Write a query that prints the name of each city and the number of users who come from that city. Only print cities that have at least 2 users. The table should be printed in descending order by number. In the event of a tie, the tie should be broken by printing the city names in ascending order.