Data Wrangling Course

James Howison's Data Wrangling course from the Information School at the University of Texas at Austin.

SQL queries allow us to talk to the server to execute the functions of a database. Unlike Excel we don’t directly view the data in tables, nor edit the data stored in the database. In SQL everything happens via queries, which can be thought of as commands from the user to the server. The server receives the commands and executes them, perhaps making changes to the data stored or returning results to the user.

Even when we are using phpmyadmin to view data on the server we are actually executing queries and showing results. You may have noticed that when you click to the Browse tab on phpmyadmin above the data table displayed is a grey box showing something like this:

SELECT * from objects

So phpmyadmin has executed a query for us, received the results, and wrapped html around them to show them to us. Similarly if we “sort” the table by a column (say the name column in the objects table in the objects database) we see:

SELECT * FROM colors ORDER BY colors.name ASC

When we use the Insert tab the server actually executes an INSERT query. Lets say that we want to add a new pen to the objects database. We click to the Insert tab and fill the boxes out (id and name):

When we click Go the next screen at the top shows us the query that was run:

INSERT INTO colors (id, name) VALUES ('34', 'grey pen');

So even things that feel like directly interacting with the server (via phpmyadmin) are actually executing queries. We’re going to learn how to write these ourselves.

SQL queries have a specific syntax. The server, unfortunately, can only work when we write our queries as it is expecting them. Unlike people, who are skilled in understanding context and filling in gaps in understanding, computers are extremely literal. So we’ll all get error messages back from the server as we learn to write our queries. Stick with it :)

To make queries easier to read we are going to write the mysql keywords (special words) in capital letters. That makes it easier to distinguish between, for example, keywords, the names of tables and columns, and little pieces of data in queries.

We will write our queries in the Atom editor, into a file we save with the .mysql ending. That will enable Atom to apply syntax coloring, as in the boxes above, to help us distinguish even more.

The four database functions

At the start of the semester we discussed the four functions of databases, using the acronym CRUD, each of the functions maps to a type of query in SQL:

C: Create.  INSERT
R: Read.    SELECT
U: Update.  UPDATE
D: Delete.  DELETE

The four query types share much of their syntax, so we’ll spend most of our time on SELECT queries before circling back to briefly talk about INSERT, UPDATE, and DELETE.

Onward to SELECT