Tables and Queries

How can we use SQL to sort and get valuable insights from our data?

Creating Tables

Before we get into that complicated "select" statement from the previous section, we should first go over the simplest of the SQL processes: creating tables.

Easily enough, the command to do this in SQL is CREATE TABLE, and the syntax is as follows:

CREATE TABLE table_name (
    column1_name data_type constraints,
    column2_name data_type constraints,
    ...
)

Let's walk through this process line by line.

1. Using the CREATE TABLE command, we create a table with any given name.

2. We give a name to our first column. Furthermore, we specify what type of data it contains (for example, INT for integer values, VARCHAR for arbitrary strings, DATE for dates, etc.)

3. We follow the same process for line 2 for our second column

It's important to note here that while the above syntax is indented and spaced into a paragraph format, SQL does not require us to follow any indentation procedures like in python. Theoretically, placing all of the data from lines 1-5 above could be done on a single line.

Now, to get into our dogs table from the previous section, all we would have to do is replace table_name, column1_name, column2_name , and column3_name with dogs, Name, Breed, and Age (years) respectively. However, this does not fill up our table. To accomplish filling values into our table, we can use the INSERT INTO command. The syntax for the command is as follows:

INSERT INTO table_name (column1, column2, column3,
...)
VALUES (value1, value2, value3, ...)

To insert Louis the 13 year old French Bulldog into our table, we could input:

INSERT INTO dogs (Name, Breed, Age (years) VALUES (Louis, French Bulldog, 13)

This would result in the following table:

Name	Breed	Age (years)
Louis	French Bulldog	13

And to get to the table from the previous section, we would repeat this process for multiple dog rows! While this process is extremely long, the highest value from SQL is it's power to interpret data, rather than create tables from scratch, in a process known as querying.

Queries

Let's take a closer look at that complicated "select" statement from the previous section. Again, we have our dogs table.

Name	Breed	Age (years)
Louis	French Bulldog	13
Lucy	Yellow Lab	2
Bailey	Beagle	1
Charlie	Border Collie	4
Milo	German Shepherd	6
Joe	Beagle	15

To access data in SQL, we write out a collection of statements and function calls known as queries. Queries are essentially just a question or request of data.

select * from dogs where breed = "Beagle";

Let's dive deeper into the statements used here.

The Select Statement

select statements define a new table either by listing the values in a single row or, more commonly, by projecting an existing table using the from clause. If we'd like to return a table that follows a specific set of rules (for example, only a certain dog breed) we can use the following syntax:

select [column description] from [existing table name]

If we simply wanted to return every column from the table, we would use select * (with "*" meaning all columns). For example, querying select * from dogs would return the following

Name	Breed	Age (years)
Louis	French Bulldog	13
Lucy	Yellow Lab	2
Bailey	Beagle	1
Charlie	Border Collie	4
Milo	German Shepherd	6
Joe	Beagle	15

Notice how there is no difference from before. To reiterate what select does, it defines a new table based on the information we put in our query. To get better insights, say we just wanted the names of the dogs in our table. Looking back to our handy select instructions above, we would query select Name from dogs;, with Name being our column description.

Also, it is important to note here that we place a semicolon whenever we end our query. In more complex server calls, semicolons would separate multiple statements. However, as we only have one here, we just place it at the end. The previous query returns the following table:

Name
Louis
Lucy
Bailey
Charlie
Milo
Joe

Where Clauses

The higher-powered use of SQL lies in its ability to easily filter data. Often, we want more than just a returned list of all the elements in the table. The where clause serves as a filter for our select statements. where filters the rows that are projected from the select statement, and only rows for which the filtering expression evaluates to a true value will be used to produce a row in the resulting table. Going back to our original query, we wanted to only return beagles from our table. As SQL syntax is fairly intuitive, all we would need to add to our original query is where query = "Beagle";

Name	Breed	Age
Bailey	Beagle	1
Joe	Beagle	15

Order By Clause

Like we've seen in Python with sort(), sometimes it's nice to have sorted information for a more organized approach to visualizing our data. If we'd like to sort columns of our table alphabetically or numerically, we can use the ORDER BY clause at the end of our query, followed by an ascending/descending keyword depending on how we'd like to sort. If we wanted to sort multiple columns, we would separate our sort operations by columns. For our previous table, let's just sort in reverse alphabetical order. This means we'd sort Name in descending order, so we would query ORDER BY Name DESC;

Name	Breed	Age
Joe	Beagle	15
Bailey	Beagle	1

Having Clause

The last of the major clauses we are going to outline in this module is HAVING. Having allows us to specify conditions that filter the type of rows we want to appear in our results.

Having must precede ORDER BY clauses. To perform a HAVING operation, we would simply precede whatever condition we want to specify with the HAVING keyword. To specify dogs with an age greater than 10, we would query HAVING COUNT(age) > 10;

Name	Breed	Age
Joe	Beagle	15

And now we have arrived at our final table! As a gift for completing your first extension module, here is a picture of my beagle Joe in real life.

Name: Joe. Breed: Beagle. Age: 15