In this lab, you will write more SELECT
statements to solidify your ability to query a SQL database. You will also write more specific queries using the tools you learned in the previous lesson.
You will be able to:
- Write SQL queries to filter and order results
- Order the results of your queries by using
ORDER BY
(ASC
&DESC
) - Limit the number of records returned by a query using
LIMIT
- Filter results using
BETWEEN
andIS NULL
Here's a database full of famous dogs! The dogs
table is populated with the following data:
name | age | gender | breed | temperament | hungry |
---|---|---|---|---|---|
Snoopy | 3 | M | beagle | friendly | 1 |
McGruff | 10 | M | bloodhound | aware | 0 |
Scooby | 6 | M | great dane | hungry | 1 |
Little Ann | 5 | F | coonhound | loyal | 0 |
Pickles | 13 | F | black lab | mischievous | 1 |
Clifford | 4 | M | big red | smiley | 1 |
Lassie | 7 | F | collie | loving | 1 |
Snowy | 8 | F | fox terrier | adventurous | 0 |
NULL | 4 | M | golden retriever | playful | 1 |
First, import sqlite3 and establish a connection to the database dogs.db. Then, create a cursor object so that you can pass SQL queries to the database.
#Your code here; import sqlite, create a connection and then a cursor object.
Display the outputs for each of the following query descriptions.
#Your code here
Select the names of all dogs listed in alphabetical order. Notice that SQL lists the nameless dog first.
#Your code here
#Your code here
#Your code here
#Your code here
#Your code here
Select the name and breed of the dogs who are between five and ten years old, ordered from oldest to youngest
#Your code here
Select the name, age, and hungry columns for hungry dogs between the ages of two and seven. This query should also list these dogs in alphabetical order.
#Your code here
Great work! In this lab you practiced writing more complex SQL statements to not only query specific information but also define the quantity and order of your results.