learn-co-curriculum/sql-queries-basic-readme

Basic SQL Queries

Overview

In this lesson, we'll cover how to write SQL queries to retrieve and add specific data to SQL database tables.

Objectives

1. Define a query as an SQL statement that retrieves data from a database
2. Use the ORDER BYmodifier to order tables by specific SELECT statements
3. Use the ASC and DESC modifier to sort queries in ascending and descending orders
4. Use the LIMIT modifier to determine the number of records to retrieve from a dataset
5. Use the BETWEEN modifier to retrieve a specific data set between two ranges
6. Use the NULL data type keyword to insert new records into a table
7. Use the COUNT function to count the number of records that meet a certain condition
8. Use the GROUP BY function to group your results according to the values in a given column

What is a SQL Query?

The term "query" refers to any SQL statement that retrieves data from your database. In fact, we've already written a number of SQL queries using basic SELECT statements. We've already seen how to retrieve single units of data, or rows, with queries like these:

To select all of the rows from a cats table:

SELECT * FROM cats;

To select only rows representing data meeting certain conditions:

SELECT * FROM cats WHERE name = "Maru";

What if, however, we wanted to select the oldest cat? Or all of the cats that don't currently belong to an owner? Or all of the cats with short names?

Data storage isn't very useful if we can't manipulate, view, and analyze that data. Luckily for us, SQL is actually a powerful tool for doing just that.

In this exercise, we'll walk through executing a handful of common and handy SQL queries.

Code Along: SQL Queries

Creating our Database

In this code along, we'll be creating a cats table in a pets_database.db. So, let's navigate to our terminal and get started.

First let's create our pets_database by running the following command.

sqlite3 pets_database.db

Now that we have a database, let's create our cats table along with id, name, age, breed, and owner_id columns.

CREATE TABLE cats (
	id INTEGER PRIMARY KEY,
	name TEXT,
	age INTEGER,
	breed TEXT,
	owner_id INTEGER
);

Good work. Let's quit the SQL interface by typing: .quit. You'll be returned to the shell prompt. Now, output the list of files (ls on Linux-based machines and dir on Windows-based machines) in the terminal and see what just happened. A new file should appear called pets_database.db! This is the binary representation of the database. You can think of this like a .jpg file. It won't open up in a text editor, but it does open up in the image viewer app. It is the same way for .db files. They won't open in your editor, but they can be read by the appropriate database engine.

Let's add some cats to our cats table to make this more interesting:

sqlite> INSERT INTO cats (name, age, breed, owner_id) VALUES ("Maru", 3 , "Scottish Fold", 1);
sqlite> INSERT INTO cats (name, age, breed, owner_id) VALUES ("Hana", 1 , "Tabby", 1);
sqlite> INSERT INTO cats (name, age, breed) VALUES ("Lil\' Bub", 5, "American Shorthair");
sqlite> INSERT INTO cats (name, age, breed) VALUES ("Moe", 10, "Tabby");
sqlite> INSERT INTO cats (name, age, breed) VALUES ("Patches", 2, "Calico");

Let's check out our cats table now:

sqlite> SELECT * FROM cats;

This should return:

1|Maru|3|Scottish Fold|1
2|Hana|1|Tabby|1
3|Lil\' Bub|5|American Shorthair|
4|Moe|10|Tabby|
5|Patches|2|Calico|

Top-Tip: You can format the output of your select statements with a few helpful options:

.headers on       # output the name of each column
.mode column     # now we are in column mode, enabling us to run the next two .width commands
.width auto      # adjusts and normalizes column width
# or
.width NUM1, NUM2 # customize column width

Run the first two commands and then execute the above SELECT statement instead and you should see something like this:

id          name        age         breed          owner_id  
----------  ----------  ----------  -------------  ----------
1           Maru        3           Scottish Fold  1         
2           Hana        1           Tabby          1         
3           Lil\' Bub   5           American Shor            
4           Moe         10          Tabby                    
5           Patches     2           Calico                   

Much better.

ORDER BY

The first query modifier we'll explore is ORDER BY. This modifier allows us to order the table rows returned by a certain SELECT statement. Here's a boilerplate SELECT statement that uses ORDER BY:

SELECT column_name FROM table_name ORDER BY column_name ASC|DESC;

Let's select our cats and order them by age:

sqlite> SELECT * FROM cats ORDER BY age;

This should return the following:

id          name        age         breed       owner_id  
----------  ----------  ----------  ----------  ----------
2           Hana        1           Tabby       1         
5           Patches     2           Calico                
1           Maru        3           Scottish F  1         
3           Lil\' Bub   5           American S            
4           Moe         10          Tabby                 

When using ORDER BY, the default is to order in ascending order. If we want to specify though, we can use ASC for "ascending" or DESC for "descending." Let's try to select all of our cats and sort them by age in descending order.

sqlite> SELECT * FROM cats ORDER BY age DESC;

This should return

id          name        age         breed       owner_id  
----------  ----------  ----------  ----------  ----------
4           Moe         10          Tabby                 
3           Lil\' Bub   5           American S            
1           Maru        3           Scottish F  1         
5           Patches     2           Calico                
2           Hana        1           Tabby       1         

LIMIT

What if we want the oldest cat? If we want to select extremes from a database table––for example, the employee with the highest paycheck or the patient with the most recent appointment––we can use ORDER BY in conjunction with LIMIT.

LIMIT is used to determine the number of records you want to return from a dataset. For example:

SELECT * FROM cats ORDER BY age DESC LIMIT 1;

This part of the statement: SELECT * FROM cats ORDER BY age DESC returns all of the cats in order from oldest to youngest. Setting a LIMIT of 1 returns just the first, i.e. oldest, cat on the list.

Execute the above statement in your terminal and you should see:

id          name        age         breed       owner_id  
----------  ----------  ----------  ----------  ----------
4           Moe         10          Tabby                 

Let's get the two oldest cats:

SELECT * FROM cats ORDER BY age DESC LIMIT 2;

Execute that statement and you should see:

id          name        age         breed       owner_id  
----------  ----------  ----------  ----------  ----------
4           Moe         10          Tabby                 
3           Lil\' Bub   5           American S            

BETWEEN

As we've already established, being able to sort and select specific data sets is important. Continuing on with our example, let's say we urgently need to select all of the cats whose age is between 1 and 3. To create such a query, we can use BETWEEN. Here's an boilerplate SELECT statement using BETWEEN:

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Let's try it out on our cats table:

SELECT name FROM cats WHERE age BETWEEN 1 AND 3;

This should return:

Maru
Hana
Patches

NULL

Let's say the administrator of our Pets Database has found a new cat. This kitty doesn't have a name yet, but should be added to our database right away. We can add data with missing values using the NULL keyword.

Let's insert our new cat into the database. Our abandoned kitty has a breed, but no name or age as of yet:

INSERT INTO cats (name, age, breed) VALUES (NULL, NULL, "Tabby");

Now, if we look at our cats data with SELECT * FROM cats;, we should see:

id          name        age         breed          owner_id  
----------  ----------  ----------  -------------  ----------
1           Maru        3           Scottish Fold  1         
2           Hana        1           Tabby          1         
3           Lil\' Bub   5           American Shor            
4           Moe         10          Tabby                    
5           Patches     2           Calico                   
6                                   Tabby                    

We can even select the mysterious, nameless kitty with the following query:

SELECT * FROM cats WHERE name IS NULL;

This should return the following:

id          name        age         breed       owner_id  
----------  ----------  ----------  ----------  ----------
6                                   Tabby                 

COUNT

Now, we'll talk about a SQL aggregate function, COUNT.

SQL aggregate functions are SQL statements that retrieve minimum and maximum values from a column, sum values in a column, get the average of a column's values, or count a number of records that meet certain conditions. You can learn more about these SQL aggregators here and here.

For now, we'll just focus on COUNT. COUNT will count the number of records that meet certain condition. Here's a standard SQL query using COUNT:

 "SELECT COUNT([column name]) FROM [table name] WHERE [column name] = [value]"

Let's try it out and count the number of cats who have an owner_id of 1:

SELECT COUNT(owner_id) FROM cats WHERE owner_id = 1;

This should return:

COUNT(owner_id)
---------------
2              

GROUP BY

Lastly, we'll talk about the handy aggregate function GROUP BY. Like its name suggests, it groups your results by a given column.

Let's take our table of cats

id          name        age         breed          owner_id  
----------  ----------  ----------  -------------  ----------
1           Maru        3           Scottish Fold  1         
2           Hana        1           Tabby          1         
3           Lil\' Bub   5           American Shor            
4           Moe         10          Tabby                    
5           Patches     2           Calico                   
6                                   Tabby                    

Here, we can see at a glance that there are three tabby cats and one of every other breed — but what if we had a larger database where we couldn't just tally up the number of cats grouped by breed? That's where — you guessed it! — GROUP BY comes in handy.

SELECT breed, COUNT(breed) FROM cats GROUP BY breed;

This should return

breed               COUNT(breed)
------------------  ------------
American Shorthair  1           
Calico              1           
Scottish Fold       1           
Tabby               3           

GROUP BY is a great function for aggregating results into different segments — you can even use it on multiple columns!

SELECT breed, owner_id, COUNT(breed) FROM cats GROUP BY breed, owner_id;

breed               owner_id    COUNT(breed)
------------------  ----------  ------------
American Shorthair              1           
Calico                          1           
Scottish Fold       1           1           
Tabby                           2           
Tabby               1           1           

Note on SELECT

We are now familiar with this syntax:

SELECT name FROM cats;

However, you may not know that this can be written like this as well:

SELECT cats.name FROM cats;

Both return:

name      
----------
Maru      
Hana      
Lil\' Bub 
Moe       
Patches   
          

SQLite allows us to explicitly state the tableName.columnName we want to select. This is particularly useful when we want data from two different tables.

Imagine we have another table called dogs with a column for the dog names:

CREATE TABLE dogs (
	id INTEGER PRIMARY KEY,
	name TEXT
);

sqlite> INSERT INTO dogs (name) VALUES ("Clifford");

If we want to get the names of all the dogs and cats, we can no longer run a query with just the column name. SELECT name FROM cats,dogs; will return Error: ambiguous column name: name.

Instead, we must explicitly follow the tableName.columnName syntax.

SELECT cats.name, dogs.name FROM cats, dogs;

You may see this in the future. Don't let it trip you up.

View Basic SQL Queries on Learn.co and start learning to code for free.

View Basic SQL Queries on Learn.co and start learning to code for free.

sql-queries-basic-readme's Issues

Remove lab dependencies

This codealong references the previous one. Should be standalone:

in progress

Refer to Readme for outline of content

ls

Could confuse windows users by referencing ls. Suggest you clarify that ls is for linux and dir for windows.

.width auto doesn't work

I tried it and it didn't work for me, and this was also a question raised by a student in the chat function.

There is an extra "," in Group BY examples SELECT breed, owner_id, COUNT(breed) FROM cats GROUP BY breed, owner_id;

Spelling errors

In the code-along there are a few spelling errors. Each of the "owner_id"'s need to be replaced by "id". Here is where the issue occurs:

Let's try it out and count the number of cats who have an owner_id of 1:

SELECT COUNT(owner_id) FROM cats WHERE owner_id = 1;
This should return:

COUNT(owner_id)

2

Console Instructions Are in Python

The instructions for this lab assume that you're using sqlite with Python. i.e.

import sqlite3
connection = sqlite3.connect('pets_database.db')
cursor = connection.cursor()

We've been using this lab in the Full Stack Web program, where students learn Ruby, not Python. Can we revert this back to the old instructions? Why was this changed? If we want to re-use this cross course, we likely need to separate out the code along and portion and just focus on the SQL concept.

Creating an 'empty' database doesn't create a .db file (OSX 10.10.5 and SQLite version 3.8.5)

In the instructions for "Creating our Database", typing

sqlite3 pets_database.db
.quit
ls

the output of ls didn't include the new database. Apparently if you run:
sqlite3 pets_database.db ""
or a 'filler' command like:
sqlite3 pets_database.db .schema;
it does create the file, however. (No .db file is created until some sort of command is issued, even if that command is "")

talk about fields

in prep for bear organizer.

No resources at the bottom of the page

Towards the end of the section "WHAT IS A SQL QUERY?" it says feel free to checkout resources at the bottom of the page.

No links to additional resources exist at the bottom of the page.

Wrong command, missed ","

When i tried to run "SELECT breed COUNT(breed) FROM cats GROUP BY breed;" it gave me error "Error: near "(": syntax error". I fixed it by adding ",". to be "SELECT breed, COUNT(breed) FROM cats GROUP BY breed;"

Missing coma on SQL code

The below code doesn't work

SELECT breed COUNT(breed) FROM cats GROUP BY breed;

I believe it is because a coma is missing between "breed" and "COUNT(breed)"

Add info on 'group by'

Group by is needed to solve the Bear Organizer lab later in this unit, but is never covered.

Can use this as a resource: http://www.w3schools.com/sql/sql_groupby.asp

Incorrect camel case (compared w/ Learn.co curriculum)

In future Learn.co lessons, Learn uses the snake_case format for describing the correct SQL syntax of table_name.column_name. The SQL Queries Basic lesson here uses camelCase to describe the SQL syntax, incorrectly identifying it as tableName.columnName.