Module overview: SQL
Getting set up for SQL
Please do not copy without permission. © ExploreAI 2023.
Module overview: SQL
Overview
In this module, we’ll be
In the next lesson, Database concepts, we’ll go through the steps
using:
of downloading, installing, and launching MySQL Workbench
and MySQL Community Server on Windows and Mac.
The various CSVs, SQL files, and queries that we'll need to create and
transform our database are provided with this document. However, they
MySQL Workbench can also be downloaded from the walk-throughs at the point we will
need them.
Throughout the module, we will need to use the provided CSVs, SQL files,
and queries to create or recreate our database. We will look at how we use
these files and queries.
2
Module overview: SQL
Overview
In this module, we’ll also In the SQL in production lesson, we’ll introduce Jupyter
be using: Notebooks and go through the steps of downloading, installing,
and launching Anaconda and Jupyter Notebooks on Windows
and Mac.
Included in the lessons beyond this point are [Walk-through]s in which
queries are run in MySQL Workbench, as well as the accompanying
[Notebook]s in which we can execute the same queries in Jupyter
Notebooks.
Throughout the module, we will be able to interact with and manipulate our
database the same way using either MySQL Workbench or Jupyter
Notebooks.
3
Module overview: SQL
Completing the practical learning activities
The [Walk-through]
learning activities will teach We’ll need to use the same
us how to query and database throughout this
transform our database. module and only recreate the
dataset when prompted.
The SQL module was
designed to take us
through various
In order for our queries to transformations of a
work, we’ll need to follow single database.
along and complete all of
the walk-through activities
in either MySQL If we do not follow
Workbench or Jupyter these instructions,
Notebooks. many of our queries
may not seem to
work.
4
Module overview: SQL
The effect of database transformations
For example, we will learn how to
remove all data from a table in
SQL.
Once we've removed the data
from the table, any query
thereafter will return an empty
table. In these cases, we'll be
prompted with the necessary
steps to recreate the database.
It's crucial to follow these
instructions closely so that
future queries work.
5
Module overview: SQL
The effect of database transformations
For example, we want to divide a
larger dataset into smaller tables
and link them using relationships.
The walk-throughs often build
on one another, so if we don't
complete them in order, we may
end up in a situation like this:
We created the
Geographic_location table in a
previous walk-through, and
therefore, if we skipped it, we
would get an error.
6
Module overview: SQL
When and how to download a new dataset
| As we transform our database, we will need a CSV or new SQL file to “reset” our database to a
usable format. We can find these files by clicking on the popup in the walk-through video.
Only some walk-through videos will include a dataset or file download since we will need to
persist some of the changes to the database for the learning activities that follow.
7
Module overview: SQL
Download CSVs and SQL files
Clicking on the Dataset
popup on a walk-through
will open the file in a new
tab.
We can click on the
download button, which
will save the file to our
local machine, very often
to a Downloads folder.
8
Module overview: SQL
Using notebook files and dependencies
We will receive database files
that we need to query in a
notebook.
The notebook environment will
look for the database file in the
current folder*. If the file is not in
the same folder as the notebook,
the system won't be able to
locate it and we will get a "no
such table" or similar error
message. We need to make sure
we have all the notebook
dependencies saved in the
same folder as the notebook.
9
Module overview: SQL
Using notebook files and dependencies
It is possible to specify the
correct file path in our code if
the file is not saved in the same
folder.
Should the database be saved in
a different folder we will need to
use the absolute or relative file
path to locate the folder.
● We use three slashes
sqlite:/// in order to use a
relative path.
● We use four slashes
sqlite://// in order to use
an absolute path.
10
