Week 1: Introduction to Databases

Week 1: Introduction to Databases

Here's what we're going to do today:

1. Housekeeping
   • Talking to the prof (that's me)
   • Attending & reviewing class
   • Submitting your assignments
   • Course introduction
2. The Big Picture
   • What's a database?
   • What does a database do?
3. History, terms & definitions
   • History
   • Terminology
4. What is a query? (Let's learn SQL!)
   • Parts of a query: SELECT and FROM
     • Basic query structure
     • * Wildcard
     • Multiple Columns
     • Aliases
     • Concatenation
   • Parts of a query: Operators
     • Arithmetic operators
     • Comparison operators
     • Logical operators
   • Parts of a query: Sorting & Filtering
     • The WHERE clause
     • The DISTINCT clause
     • The ORDER BY clause
   • How to format your code
   • Lab time!

Housekeeping

You can put other stuff in the file name as well, if it helps you stay organized, so long as it's personally identifiable, and has the .sql file extension.

So, for example, this week you might submit a file called Rosetta_Tharpe_HTTP5112Week1Lab.sql

Course introduction

Anybody can do just fine in this course.

Show up to class every week.

Do your assignments on time.

Ask questions.

Don't cheat.

A book (you probably don't need to buy)

• Murach’s MySQL, 2nd Edition
  • ISBN 978-1-890774-82-0

Resources I do recommend

• Select Star SQL Opens in a new window
• Become a Select Star (Julia Evans) Opens in a new window

A few aspects of my teaching philosophy.

1. I'm here for you to bug. Please ask me all your questions.
2. Muscle memory is key. It's going to bug you how often you have to type things you already know in your head, but you need to know it in your fingers.
3. Authors aren't judged on their vocabulary. You'll need to know the basics off the top of your head, but the important thing is not syntax (which is easy to look up), it's structure.

That's it for housekeeping! Now let's, you know, learn some stuff.

In this class today, we'll learn...

• what databases are used for in web development,
• some database history,
• some words I want you to remember for later, and
• the first steps with SQL.

The Big Picture

What is a database?

In this program, you are learning to be full-stack developers. Data management (typically through a database) is one component of that "full stack", along with server-side code, and front-end code (code that runs in the browser).

Databases have many uses outside web development, but we'll be focussing on databases in the context of the web.

A database can remember almost anything, but the most common uses are:

• Storing the content of your website, especially things that change frequently
• Remembering users
• Remembering things from one page to the next (for example, items in a shopping cart)

Our kind of database

There are all different kinds of databases. The kind we'll mostly be working with in this course are called relational databases, because they're really good at connecting different kinds of data together.

Relational databases are made up of tables. A table is not a spreadsheet, but that's a handy way to think of them.

Each table is for a specific type of thing. What counts as a "thing" will depend on the purpose of your database. What's important is that you are collecting the same types of data about each thing in your table.

If we were creating a database for a school, we'd want a database table for students.

• Each type of information a school needs about students would be in a separate column (i.e. student number, first name, last name, email address, etc.)
• Each student would be in their own row, and
• each different piece of data about each student would be in the cell where the data type's column intersects with that student's row.

If your database is well-designed, the columns will be broken down into narrow categories of data, as it is easier to tell a computer how to put things together (i.e. "Patrick" + " " + "Stewart") than it is to tell a computer how to split things apart (i.e. trying to explain to a computer how to find the last name of "Sir Patrick James Stewart III, Ph.D").

When designing a database, you'll want to split things into their smallest useful pieces.

What makes a database "relational" is that there are relationships between the tables.

Each row (each "thing") gets a unique ID. When there is a relationship between two different types of thing (for example, students and classes), we can map the unique IDs together across tables.

This means we don't have to re-record all the information about every class a student takes - we can just create a reference to the class ID.

We look up data in a database (either in single tables, or many at once) by using a language called SQL.

Learning SQL will be most of the content of this course.

SQL can also do everything else we need to do in order to interact with a database server - from creating the database and the rows and tables within, to updating the data within those tables, to deleting data.

SQL is a (relatively) old, and very reliable language, and integrates easily with many other languages that you'll learn in this program (javaScript, PHP, .NET, etc.). It's such a nicely designed language that those languages haven't tried to replace SQL, because it's easier just to write SQL in your PHP.

What a database does

Let's look at where a database sits in relation to other technologies in order to make the internet a thing.

History, Terms & Definitions

History (you can forget if you want to)

Relational databases have been around since 1970, and Oracle has been around (in one form or another) for 40 years.

IBM (DB2) and Microsoft (SQL Server) released their relational database systems in the mid-80s.

Since the technology world was a bit slower then, they were able to agree on standards (a.k.a. SQL).

In 1995, just as the world wide web was being born, along came the open-source MySQL, democratizing data for the whole world.

In 2008, Sun Microsystems bought MySQL, and, two years later, Oracle bought Sun Microsystems, because that's just how the world is now, I guess.

Terminology

Before we dive into SQL, let's take a moment to get familiar with some of the database terms we'll be using.

Rows, columns & cells

Primary key

Another important element that every table should have is a primary key. The only job of a primary key is to be unique.

Let's learn SQL!

We now have a rough idea of what a database does, and how it operates:

• It is a store of data that a website can access through server-side languages.
• It gives our websites a "memory".
• If it's a relational database, it gives different types of thing their own table, and then lets you connect these tables together.
• Tables have columns to store different categories of information.
• Tables have rows to store information about different things.

Now we can look at SQL - the old, venerable computer language that lets us interact with the data in our databases.

The most basic part of SQL is the "query" (which is a fancy way of saying "question").

A query is when we ask the database a question, and it returns an answer.

Today we're going to learn how to ask the database to return specific data from the database. Among other things, we'll choose which columns we want returned, we'll filter out some rows, and we'll sort the data that gets returned.

Parts of a query: SELECT and FROM

These are the two keywords that are necessary to make a SQL query.

SELECT means get me some data.

The FROM clause says where to get the data from. It's that simple.

Basic query structure

Wildcard

Not sure what columns you want? Want them all? Just type
SELECT * FROM {table name}

The asterisk (*) is a "wildcard" character, meaning it can be any character(s).

Selecting multiple columns

Want more than one column? No problem. Just separate the column names with a comma.

SELECT firstName, lastName FROM students

Aliases

By default, the columns in your results tables will be named the same as the columns in your database table.

However it's important to note that your results tables are not your database tables - they are the result of the data returned by your query. As an example of this, we can give a name to the columns in our results table.

We can use the AS clause to give our columns aliases ("nicknames").

Concatenation

Again: your results tables are not the same as you database tables. We can combine the data from our database table columns into a single column in our results table. This smushing together of things is called "concatenation".

In our queries, we can do this by using "pipes", two vertical lines that look like this: ||

Luckily, we can also use strings in our select statements. What is a string? Glad you asked!

A string is a type of computer data. It contains characters (letters, numbers, punctuation), and is intended to just be those characters - you're not meant to calculate anything with it. In SQL, we enclose them in 'single' quotation marks.

We've already used a string when giving a column an alias. We can also use them to better format our concatenated columns, for example:

SELECT city || ', ' || country AS 'Places' 
    FROM customer

Parts of a query: operators

Operators are keywords and characters we can use to manipulate or compare results. In SQL, there are three kinds of operators: arithmetic (which let us do simple math), logical (which let us perform logic), and comparison (which let us compare things).

Operators are most commonly used in filtering and sorting data, which we'll talk about today in the next section.

Arithmetic operators

Most of these will be pretty familiar, and work similarly in most computing languages. Note that you can use brackets to change the order of operations.

Comparison operators

Is one thing more, less, equal, etc? Use these operators to compare them!

Logical operators

Returns data if the statement is true

Parts of a query: sorting and filtering

Filtering

Now we're getting spicy 🌶

The WHERE clause is our results filter. This is where things get interesting, and we'll work on this a bunch next week. WHERE lets us use our operators to filter out any rows that don't match the filter(s) we define.

If you're following along in the notes, you've already seen how WHERE works in the examples provided for the operators.

Another kind of filtering: Unique data with the DISTINCT keyword

If we want our results table to only show unique results, we start our query with SELECT DISTINCT.

If you are selecting more than one column, this will return results with that unique combination of data.

Sorting

ORDER BY orders our results by specified column(s). It can also order by a column alias if you created one.

Sorting is done in ascending order (lowest to highest) by default, but the order can be declared with the keywords DESC and ASC.

This can be applied to any column in the table, not just the ones being output in the results table, (unless you're using DISTINCT).

In summary...

SELECT gets data, FROM specifies the table.

After you write SELECT, you can specify the column you want to get data from, or get them all with *

You can give the results' columns custom names with AS.

You can combine data in your results with ||.

You can perform arithemetic on data, compare data and create logic with different operators.

You can filter your results with WHERE and DISTINCT.

You can sort your results with ORDER BY.

Formatting your code

You should always code according to a formatting convention.

Different languages have different formatting conventions (sometimes due to restrictions in the language, sometimes to distinguish it from other languages).

Your workplace team may have conventions that supplement/supercede common conventions, but always default to common conventions.

In SQL, we...

• capitalize our keywords (SELECT, ORDER BY, etc.),
• reference column names in lowercase,
• have line breaks between statements (and longer clauses),
• indent wrapped lines.

Exercise

During our lab time this week, rather than having a graded lab assignment, I just want to get to know you folks a bit. That being said, I do want you to have the opportunity to stretch your coding muscles and ask me questions when you get stuck.

Follow this link for some exercises!