10  SQL Queries Introduction

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.

In this course we will use Jupyter Notebooks to communicate with our database server. We will execute queries in the notebooks and look at the results which are displayed. Eventually we will learn how to execute queries against the database server from inside Python code.

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 SQL 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.

To make debugging easier we will build up our queries step by step using a sequence of cells in Jupyter. This enables us to have a history of the queries that we’ve run, and to see the results at each step.

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.

First, though, we need to be able to create databases and set up table structures. This is very similar to our table sketches, except we need to tell the server about the tables and columns rather than just drawing them as we have done with our table sketch.

Datacamp

This semester we will be executing SQL queries using the workspaces on DataCamp. At the end of the semester we will discuss other ways to execute SQL.

You should have received an invitation to DataCamp (it was sent to the email address officially on file with UT, if you cannot use that email address with DataCamp send me a note and I’ll shift your account over.)

I think this direct link will take you to our class space on DataCamp

Once you are logged in, navigate to “Workspace” and then ensure that the drop-down on the left shows “Fall 2023 - Data…” rather than your name and “Starter”. This will ensure you can create as many workspaces as needed (I have signed the class up for a “Premium” account this semester).