License: Other

Jupyter Notebook 65.30% Python 33.55% PLpgSQL 1.15%

dsc-1-05-08-filtering-and-ordering-lab's Introduction

Filtering and Ordering - Lab

For the old version of this lab go here.

Introduction

In this lab, we will write more SELECT statements to solidify our ability to query a SQL database. We will also write more specific queries using the tools we learned in the previous lesson.

Objectives

You will be able to:

Limit the number of records returned by a query using LIMIT
Filter results using BETWEEN and IS NULL
Order the results of your queries by using ORDER BY (ASC & DESC)

Famous Dogs

We have 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

Connecting to the Database

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.

Review

As a quick review, here's the code we used to generate this table:

cur.execute("""CREATE TABLE dogs (id INTEGER PRIMARY KEY,
                                   name TEXT, age INTEGER,
                                   gender CHAR(1),
                                   breed TEXT,
                                   temperament TEXT,
                                   hungry BOOLEAN);""")

cur.execute("""INSERT INTO dogs (name, age, gender, breed, temperament, hungry) VALUES
                ("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);""")
conn.commit() #Save our changes to the database

Queries

Display the outputs for each of the following query descriptions.

Select the name and breed for all female dogs

#Your code here

Select the names of all dogs listed in alphabetical order. Notice that SQL lists the nameless dog first.

#Your code here

Select any dog that doesn't have a name

#Your code here

Select the name and breed of only the hungry dogs and lists them from youngest to oldest

#Your code here

Select the oldest dog's name, age, and temperament

#Your code here

Select the three youngest dogs

#Your code here

Select the name and breed of only the dogs who are between five and ten years old

#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

Summary

Great work! In this lab we practiced writing more complex SQL statements to not only query specific information but also define the quantity of results and the order of our results.

dsc-1-05-08-filtering-and-ordering-lab's People

Contributors

Watchers

Forkers

jet2016 doyarzunv olivialonyc shivambedi

dsc-1-05-08-filtering-and-ordering-lab's Issues

Tests failing in the solution branch

FF [100%]

============================================== FAILURES ==============================================
_____________________________ test_select_all_female_dogs_name_and_breed _____________________________

def test_select_all_female_dogs_name_and_breed():
    result = [('Little Ann', 'coonhound'), ('Pickles', 'black lab'), ('Lassie', 'collie'), ('Snowy', 'fox terrier')]

  assert table.execute(select_all_female_dogs_name_and_breed()).fetchall() == result

E AssertionError: assert [] == [('Little Ann', 'coonhound')...), ('Snowy', 'fox terrier')]
E Right contains more items, first extra item: ('Little Ann', 'coonhound')
E Use -v to get the full diff

pytests/test_index.py:20: AssertionError
__________________________ test_select_all_dogs_names_in_alphabetical_order __________________________

def test_select_all_dogs_names_in_alphabetical_order():
    result = [(None,), ('Clifford',), ('Lassie',), ('Little Ann',), ('McGruff',), ('Pickles',), ('Scooby',), ('Snoopy',), ('Snowy',)]

  assert table.execute(select_all_dogs_names_in_alphabetical_order()).fetchall() == result

E AssertionError: assert [] == [(None,), ('Clifford',), ('L...Gruff',), ('Pickles',), ...]
E Right contains more items, first extra item: (None,)
E Use -v to get the full diff

pytests/test_index.py:24: AssertionError
______________________________________ test_select_nameless_dog ______________________________________

def test_select_nameless_dog():
    result = [(9, None, 4, 'M', 'golden retriever', 'playful', 1)]

  assert table.execute(select_nameless_dog()).fetchall() == result

E AssertionError: assert [] == [(9, None, 4, 'M', 'golden retriever', 'playful', ...)]
E Right contains more items, first extra item: (9, None, 4, 'M', 'golden retriever', 'playful', ...)
E Use -v to get the full diff

pytests/test_index.py:28: AssertionError
________________ test_select_hungry_dogs_name_and_breed_ordered_by_youngest_to_oldest ________________

def test_select_hungry_dogs_name_and_breed_ordered_by_youngest_to_oldest():
    result = [('Snoopy', 'beagle'), ('Clifford', 'big red'), (None, 'golden retriever'), ('Scooby', 'great dane'), ('Lassie', 'collie'), ('Pickles', 'black lab')]

  assert table.execute(select_hungry_dogs_name_and_breed_ordered_by_youngest_to_oldest()).fetchall() == result

E AssertionError: assert [] == [('Snoopy', 'beagle'), ('Cli...), ('Pickles', 'black lab')]
E Right contains more items, first extra item: ('Snoopy', 'beagle')
E Use -v to get the full diff

pytests/test_index.py:32: AssertionError
_________________________ test_select_name_age_and_temperament_of_oldest_dog _________________________

def test_select_name_age_and_temperament_of_oldest_dog():
    result = [('Pickles', 13, 'mischievous')]

  assert table.execute(select_name_age_and_temperament_of_oldest_dog()).fetchall() == result

E AssertionError: assert [] == [('Pickles', 13, 'mischievous')]
E Right contains more items, first extra item: ('Pickles', 13, 'mischievous')
E Use -v to get the full diff

pytests/test_index.py:36: AssertionError
__________________________ test_select_name_and_age_of_three_youngest_dogs ___________________________

def test_select_name_and_age_of_three_youngest_dogs():
    result = [('Snoopy', 3), ('Clifford', 4), (None, 4)]

  assert table.execute(select_name_and_age_of_three_youngest_dogs()).fetchall() == result

E AssertionError: assert [] == [('Snoopy', 3), ('Clifford', 4), (None, 4)]
E Right contains more items, first extra item: ('Snoopy', 3)
E Use -v to get the full diff

pytests/test_index.py:40: AssertionError
_____ test_select_name_and_breed_of_dogs_between_age_five_and_ten_ordered_by_oldest_to_youngest ______

def test_select_name_and_breed_of_dogs_between_age_five_and_ten_ordered_by_oldest_to_youngest():
    result = [('McGruff', 'bloodhound'), ('Snowy', 'fox terrier'), ('Lassie', 'collie'), ('Scooby', 'great dane'), ('Little Ann', 'coonhound')]

  assert table.execute(select_name_and_breed_of_dogs_between_age_five_and_ten_ordered_by_oldest_to_youngest()).fetchall() == result

E AssertionError: assert [] == [('McGruff', 'bloodhound'), ...('Little Ann', 'coonhound')]
E Right contains more items, first extra item: ('McGruff', 'bloodhound')
E Use -v to get the full diff

pytests/test_index.py:44: AssertionError
test_select_select_name_age_and_hungry_of_hungry_dogs_between_age_two_and_seven_in_alphabetical_order

def test_select_select_name_age_and_hungry_of_hungry_dogs_between_age_two_and_seven_in_alphabetical_order():
    result = [(None, 4, 1), ('Clifford', 4, 1), ('Lassie', 7, 1), ('Scooby', 6, 1), ('Snoopy', 3, 1)]

  assert table.execute(select_name_age_and_hungry_of_hungry_dogs_between_age_two_and_seven_in_alphabetical_order()).fetchall() == result

E AssertionError: assert [] == [(None, 4, 1), ('Clifford', ...y', 6, 1), ('Snoopy', 3, 1)]
E Right contains more items, first extra item: (None, 4, 1)
E Use -v to get the full diff

pytests/test_index.py:48: AssertionError
================================= 8 failed, 1 passed in 0.15 seconds =========

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.

learn-co-curriculum / dsc-1-05-08-filtering-and-ordering-lab Goto Github PK