Prerequisites

Prequel to the sequel

Oh, hi! In this course, we'll tackle SQL, which stands for Structured Query Language and is a programming language we use with databases.

SELECT name
FROM friends;

//Output Below

 id | name  
----+-------
  1 | Robin

Woohoo! We're about to become database whisperers! But what is a database, anyway?

Databases

In a nutshell, databases help us search, select and store information in an organized way.

Who do you think needs databases?

Nice! Businesses may use databases to keep track of their inventory, governments for their citizens, libraries for their books and schools for their students.

Tables

Within a database, a table is a collection of data that's organized into columns and rows.

Might friends be a table?

SELECT *
FROM friends;

//Output Below

 | name  | age  | height 
----+-------+------+--------
  1 | Robin |   42 |   1.72 

Indeed, friends is a table and id, name, age and height are its columns. A row in this table represents a unique friend and is referred to as a record.

Data types

Data types are like guidelines that we give the columns, or fields, to follow so that they store the right kind of values.

Let's select a data type that we can use for so-called strings

SELECT name
FROM friends;

//Output Below

 name  
-------
 Robin 

Ah, yes! name is a TEXT-type field for strings, while age is an INT-type field for whole numbers and height can take decimal numbers of type FLOAT.

Psst: we'll go further into these data types a little later.

Syntax

Any task we want to perform requires a statement, which starts with a keyword and ends with a semicolon.

CREATE DATABASE adventure;

//Output Below

Query OK, 1 row affected (0.01 sec)

With the CREATE DATABASE statement, we create a database and give it a name.

Psst: we write keywords in uppercase letters and the names of databases, columns, and so on in lowercase letters.

Statements

For example, we can build a statement to get data from a table.

SELECT name
FROM friends;

//Output Below

 name  
-------
 Robin 

Select statement helps us get, or retrieve, data from a table. SELECT and FROM.

Identifiers

When we write a statement, we often provide so-called parameters.

SELECT name
FROM friends;

//Output Below

 name  
-------
 Robin 

See that? We often use the names of the fields and tables we want to perform a task on as parameters.

Clauses

The lines of such statements are what we call clauses. In a select statement, we use a SELECT clause for the columns and a FROM clause for the tables.

What about this clause?

SELECT name
FROM friends
WHERE name = 'Robin';

//Output Below

 name  
-------
 Robin 

Nice! We can use a WHERE clause to get data based on certain conditions. If we skip it, we get every row of a table.

Psst: some database systems require single quotes for values, which is why we'll use them over double quotes.

Results

Queries are statements that display, or return, data based on parameters. We get these results as temporary tables.

The select statement is a query that returns a temporary table with the name column from the friends table.

Compendium

We've already learned loads about SQL: what databases and tables are and how clauses and parameters form statements.

SELECT name
FROM family
WHERE name = "Mom";

//Output Below

name 
------
 Mom  

Statements can consist of many clauses. The statements, however, have to end with a semicolon.

Tables

So, back to tables! We've heard that they're made up of related data that's stored in columns and rows.

Before we move on to create a table, let's have a last look at friends using a select statement!

SELECT name, age, height
FROM friends;

//Output Below

 name  | age | height 
-------+-----+--------
 Robin |  42 |   1.72 

See that? We can separate columns, or fields, with commas. Each row is referred to as a record.

Creating

But how do we actually create a table like friends? Well, there's a statement for that.

CREATE TABLE friends;

//Output Below

ERROR 1113 (42000): A table must have at least 1 column

Whoa! What happened? As it turns out, we need to add at least a single column at the same time we create a table.

Columns and data types

Let's add that column, then! But which data type should the name column expect?

CREATE TABLE friends (
 name TEXT
);

//Output Below

Query OK, 0 rows affected (0.01 sec)

Right! Because we'll use the name column for strings, the TEXT type is an ideal fit. We use data types like INT and FLOAT to store numbers.

Identifiers

What if there are records with the same name value, though? Well, that's why we want every record in a table to have a unique identifier, or id.

CREATE TABLE friends (
 id INT NOT NULL,
 name TEXT
);

Sweet! We've also applied a so-called constraint to id: NOT NULL, which means that the column must contain a value.

Altering tables

Alright, we have a table with an id and a name column. That isn't a lot, is it? Let's add an age and a height column!

ALTER TABLE friends
ADD name VARCHAR(20) NOT NULL, 
ADD age INT;

//Output Below

Query OK, 0 rows affected (0.01 sec)

Great! In an ALTER TABLE statement, we can use as many ADD clauses as we want, as long as we separate them with commas.

Psst: VARCHAR is similar to TEXT but allows us to set a maximum length for a string and, therefore, save space.

Inserting

Alright, we have a friends table, but there's nothing in it. Let's use an insert statement to, well, insert a record, shall we?

INSERT INTO friends
VALUES (1, 'Robin', 42, 1.72);

//Output Below

Query OK, 1 row affected (0.01 sec)

Nice! With an insert statement, we insert the values we provide in the parentheses of the VALUES clause into the sequential columns of the table.

Psst: if we don't specify the columns we want to insert values into, we have to provide values for all of them.

Inserting #2

We can also insert records with specific values into a table and leave other fields blank.

INSERT INTO friends (id, name)
VALUES (2, 'Win');

//Output Below

Query OK, 1 row affected (0.00 sec)

Perfect! The fields that don't receive a value use their default value or NULL. We can't skip fields that have a NOT NULL constraint, though.

Updating

What if we want to change, or update, information to an already existing record?

UPDATE friends
SET age = 12
WHERE name = 'Win';

//Output Below

Query OK, 1 row affected (0.01 sec)

That's it! We can use an update statement with SET and WHERE clauses to set a different value for age in the record where the name equals 'Win'.

Deleting

You know, we can remove records from tables and the statement starts with a keyword that sounds very much like what it's for.

DELETE FROM friends
WHERE id = 2;

//Output Below

Query OK, 1 row affected (0.01 sec)

Poof! The record with an id of 2, is gone from the friends table.

Selecting

Last but by no means least, let's revisit the select statement and have the results include multiple columns!

SELECT *
FROM friends
WHERE name = 'Robin';

//Output Below

 id | name  | age | height 
----+-------+-----+--------
  1 | Robin |  42 |   1.72 

Nice! We can use a * sign to select all of columns at once.

Psst: again, a select statement needs at least a SELECT and a FROM clause to indicate the table that we want to query.

Compendium

Tables are a fundamental concept of SQL. We've learned how to create, alter and delete them and have seen ways to work with their records.

CREATE TABLE team (
 number INT NOT NULL,
 name VARCHAR(20)
);
INSERT INTO team
VALUES (23, 'Michael');
SELECT number, name
FROM team
WHERE number = 23;

//Output Below

 number | name    
--------+---------
     23 | Michael 

Aren't they great, those tables? How about some tests?

Statements and clauses

As we already know, statements help us interact with databases and are made up of clauses.

Do you remember clauses that we've put at the beginning of statements?

  • INSERT INTO
  • UPDATE
  • SELECT
  • DELETE

Sweet! Usually, the name of the clause that a statements starts with is also the name of the statement.

Syntax

Clauses start with a keyword and continue with an identifier, a so-called expression or some other parameter.

SELECT name
FROM friends
WHERE name = 'Robin';

//Output Below

 name  
-------
 Robin

See that? In a select statement, the SELECT and FROM keywords take identifiers and the WHERE keyword requires an expression.

WHERE

How can we use a WHERE clause to find records in the family table where the age is greater than, say, 40?

SELECT *
FROM family
WHERE age > 40;

//Output Below

 id   | name | age 
----+------+-----
  1 | Dad  |  50 
  2 | Mom  |  48

Nice! With age > 40, a so-called boolean expression, we only select records where the expression evaluates to TRUE.

Psst: again, the splat operator asks for every column in the record.

AND

We also have a keyword that we can use to chain clauses, which allows us to get more specific.

How can we get another expression into this query?

SELECT *
FROM family
WHERE age > 40
AND name = 'Dad';

//Output Below

 id | name | age 
----+------+-----
  1 | Dad  |  50 

Great! We want records where the age is greater than 40 and the name is equal to 'Dad'.

OR

Say we want a result set that requires at least one out of two expressions to evaluate to TRUE.

SELECT name
FROM family
WHERE age > 40
OR name = 'Dad';

//Output Below

 name
------
 Dad
 Mom

That's it! We can use an OR clause to select records with an age of more than 40 or a name that equals 'Dad', which brings back 'Mom'.

ORDER BY

With an ORDER BY clause, we can sort a result set by any column.

Let's insert some more records into the family table, select them and sort the whole thing by id in descending order.

INSERT INTO family (id, name, age)
VALUES (3, 'Robin', 12),
  (4, 'Win', 12);
SELECT *
FROM family
ORDER BY id DESC;

//Output Below

 id | name  | age 
----+-------+-----
  4 | Win   |  12 
  3 | Robin |  12
  2 | Mom   |  48 
  1 | Dad   |  50 

We can use the DESC keyword to specify a descending order. If we wanted an ascending order, we would have used the 'ASC' keyword.

Psst: if we would order by age alone, without specifying the order, the result set would be sorted in ascending order.

DISTINCT

Columns other than id can contain duplicate values but, at times, we only want to retrieve the different, or distinct, values.

Did you notice the duplicate values in the age column of the family table?

SELECT DISTINCT age
FROM family;

//Output Below

 age 
-----
  50
  48
  12

Great! The DISTINCT keyword, which we can use in a SELECT clause, only selects the distinct values in the age column and skips the duplicate  12.

LIMIT

Depending on the number of records in a table, sometimes we get larger result sets than we bargained for. That's why we can use a LIMIT clause.

SELECT *
FROM family
LIMIT 2;

//Output Below

 id | name  | age 
----+------+-----
  1 |  Dad   |  50 
  2 |  Mom   |  48 

Excellent! The LIMIT clause requires an INT-type value with the maximum number of records we want to have in the result set.

LIKE

What if we don't have specific values but patterns that we want to query for? Well, that's what the LIKE keyword, or operator, is for.

SELECT *
FROM friends
WHERE name LIKE '%in%';

//Output Below

 id | name  | age
----+-------+-----
  3 | Robin |  12
  4 | Win   |  12

Great! Because the % sign fills in for any characters before and after 'in', the LIKE operator looks for records that contain 'in'.

Psst: these substitutes for other characters are also known as wildcards.

Wildcards

There's another so-called wildcard that we can use with the LIKE operator, which fills in for any single character.

SELECT *
FROM friends
WHERE name LIKE '_in';

//Output Below

 id |name  | age
----+------+-----
  4 | Win  |  12

Fantastic! We can use the _ sign to look for records with a name field that consists of three characters and ends with 'in'.

Compendium

Brilliant work! In this lesson, we've taken a closer look at clauses like WHERE, AND, OR, ORDER BY and LIMIT as well as the keywords DISTINCT and  LIKE.

So, what if we just want to see the record in the pole position of the family table?

SELECT *
FROM family
LIMIT 1;

//Output Below

 id |name | age
----+------+-----
  1 | Dad  |  50

Bingo! We've limited our result set to 1, and since the table is ordered by id by default, we got the first record!

Assignments

In SQL, one of the most widely used operators is =, the assignment operator.

UPDATE friends
SET age = 24;

//Output Below

Query OK, 1 row affected (0.01 sec)

See that? We can use = to assign 24 to the age field.

Comparisons

We can also use = as a comparison operator, though.

SELECT *
FROM friends 
WHERE age = 24;

//Output Below

 id | name  | age  | height 
----+-------+-----+--------
  1 | Robin |  24 |   1.72 

In this context, the = operator compares if age is equal to 24.

Psst: because age = 24 can either be TRUE or FALSE, it's also known as a boolean statement.

Boolean statements

With comparison operators like =, >, and <, we can form conditions, or boolean statements.

What do you figure the condition below will return?

10 < 20
  • TRUE
  • FALSE

If you chose TRUE you are right! The < operator compares fields or values and returns TRUE, which is a BOOL-type value.

Negations

Negation operators look for results that don't match a condition.

SELECT *
FROM friends 
WHERE age != 99;

//Output Below

 id | name  
----+-------
  1 | Robin 

Great! For comparisons, we can combine ! with other operators. != is not equal to, just as !> is not greater than and !< is not less than.

Psst: for logical expressions, we can use the NOT operator.

BETWEEN

There's a logical operator that helps us look at ranges and works very well with the AND operator.

SELECT id, name, age
FROM friends
WHERE age BETWEEN 0 AND 99;

//Output Below

id | name | age|
----+-------+---
1 | Robin | 24 |

Great! This query fetches every record with an age field between 0 and 99.

Psst: this is an inclusive range, which means that 0 and 99 are part of it.

NOT BETWEEN

If we want to exclude any records that have fields between certain values, we use the art of negation.

SELECT *
FROM people
WHERE age NOT BETWEEN 10 AND 50;

//Output Below

 id |   name   | age
----+----------+----- 
  3 | Lancelot | 800
  4 | Tom      | 75
  5 | Jerry    | 75 

Pretty straight forward, right? Let's move on!

IN

We can get results based on whether or not a record has a specific field.

SELECT *
FROM people;
WHERE age IN ('75', '20');

//Output Below

 id | name  | age
----+-------+-----
  1 | Bob   | 20
  4 | Tom   | 75
  5 | Jerry    | 75 

Sweet! We can add multiple values to the IN operator, and it'll give us all of the records that meet the condition.

NOT IN

Again, we can negate conditions.

SELECT * FROM people
WHERE age NOT IN ('75', '20');

//Output Below

 id |   name   | age
----+----------+-----
  2 | Smith    | 35
  3 | Lancelot | 800 

Like it's opposite day! Have you noticed how easy it is to read these queries in plain English?

IS NULL

In programming, we tend to refer to empty as NULL. With this operator, we can find empty fields in our records.

SELECT *
FROM people
WHERE age IS NULL; 

That's right! There are no empty fields in our table, though, which means that there's no result!

IS NOT NULL

Let's check the people table for fields that aren't empty, shall we?

SELECT name
FROM people;
WHERE age IS NOT NULL;

//Output Below

 id |   name   | age
----+----------+-----
  1 | Bob      | 20
  2 | Smith    | 35
  3 | Lancelot | 800
  4 | Tom      | 75
  5 | Jerry    | 75

Well, that was to be expected because none of the fields in the people table are empty.

Compendium

So, we've ventured into assignment, logic, comparison, and negation operators. Phew!

Let's get name values based on age values that are greater than 20, shall we?

SELECT name
FROM people
WHERE age > 20;

//Output Below

  name
-------
 Bob
 Smith

Great! You've really gotten a hang of this operator stuff, haven't you?

Manipulation

So now that we're equipped with lots of statements and clauses, let's look into queries with more detail!

WHOLE TABLE?

For this chapter, our people table has been extended to make room for more queries.

We've got the following fields now: id, first_name, last_name, age, email, and city. Let's dive in!

Many Results

How about we get to know all of the people in our table?

SELECT first_name , last_name 
FROM people;

//Output Below

 first_name |  last_name
------------+-------------
 Bob        | Jones
 Smith      | Agent
 Lancelot   | du Lac
 Sherlock   | Holmes
 Margaret   | Hamilton
 Ada        | Lovelace
 Joan       | of Arc
 Amelia     | Earhart
 SpongeBob  | SquarePants
 Leia       | Organa

We can separate our fields with commas! Cool, right? Again, our results are stored in a temporary result table.

Sorting

How would we go about finding the cities our people live in and showing them in ascending order?

SELECT city, first_name FROM people
ORDER BY city ASC;

//Output Below

     city      | first_name
---------------+------------
 Atchison      | Amelia
 Atlantis      | Bob
 Bikini Bottom | SpongeBob
 Camelot       | Lancelot
 London        | Ada
 London        | Sherlock
 Mega City     | Smith
 Paoli         | Margaret
 Polis Massa   | Leia
 Rouen         | Joan

Great! We can use the ASC keyword to order fields such as city.

Psst: just using 'city' would work as well since that would ask is implied when using ORDER BY.

Limitations

So this screen is a bit small to keep showing us all of the records in a table.

How do we limit these to 5?

SELECT last_name, email FROM people
LIMIT 5;

//Output Below

 last_name |        email
-----------+-----------------------
 Jones     | bob@explorer.com
 Agent     | agent@smith.com
 du Lac    | sir@roundtable.com
 Holmes    | private@detective.com
 Hamilton  | genius@nasa.com

That's right! We're limiting our result set to five records.

We'll be seeing how to pick the first or the last records in a later chapter.

Multiple Conditions

Say we want to see a record where 3 conditions are true.

SELECT email FROM people
WHERE city = 'London'
AND age > 50
AND last_name = 'Holmes';

//Output Below

         email
-----------------------
 private@detective.com

That's right! We can chain together as many conditions as we like with AND.

Even More Conditions

We can use multiple conditions with different clauses as well!

SELECT email, age FROM people
WHERE city = 'London'
AND (age > 50 OR age < 50);

//Output Below

         email         | age
-----------------------+-----
 private@detective.com | 100
 countess@math.com     |  36

That's right! We can use OR to determine either condition as true, which means we'll get a result set that fits both bills.

Intermittent

Wait a minute, doesn't that look similar to another clause we learned about?

SELECT email, age FROM people
WHERE age BETWEEN 36 AND 45;

//Output Below

           email           | age
---------------------------+-----
 agent@smith.com           |  45
 countess@math.com         |  36
 aviator@atlantic.com      |  41

Ok, so it's not the same. The comparison operators we used in the last lesson (> and <) are not inclusive, whereas BETWEEN, is!

Note that if we don't specify an order, the result set will order itself by the record's position in the table.

Patterns

How can we use patterns to find multiple fields in our table?

SELECT first_name, city FROM people
WHERE first_name LIKE '%ob' ;

//Output Below

 first_name |     city
------------+---------------
 Bob        | Atlantis
 SpongeBob  | Bikini Bottom

That's it! % matches any characters that come before ob in this case.

Specific Patterns

What if we want a very specific pattern?

SELECT email FROM people
WHERE email LIKE '__________@%';

//Output Below

           email
---------------------------
 private@detective.com
 heroine@france.com
 aviator@atlantic.com
 general@rebelalliance.com

Ha! We've asked for emails that have 7 characters before the @. Remember, each _ represents an individual character, and % represents any.

Negation

Let's work with some negation and sorting.  Let's sort last names in descending order, as long as the city for the record is not London.

SELECT first_name, last_name
FROM people
WHERE city NOT IN ('London')
ORDER BY last_name DESC;

//Output Below

 first_name |  last_name
------------+-------------
 Joan             | of Arc
 Lancelot     | du Lac
 SpongeBob   | SquarePants
 Leia             | Organa
 Bob               | Jones
 Margaret   | Hamilton
 Amelia         | Earhart
 Smith           | Agent

Yeah! Did you notice that SQL sorts capital letters first?

Psst: we can also use the != operator instead of writing NOT IN.

Compendium

We've really got a handle on it now, seeing as we've learned to combine multiple statements, clauses and operators to get the results we want from our table!

How do we get the first 5 distinct cities in our table, in alphabetical order?

SELECT DISTINCT city FROM people
ORDER BY city
LIMIT 5;

Yep! We need to select the cities, order them, and then limit the results!

Aggregate Functions

SQL also has built in functions. A function combines many lines of code into one word we can use.

Aggregate functions give us a single value from a calculation performed on all values in a column.

FUNCTION(column_name)

This is the conventional syntax for aggregate functions

Scalar Functions

But calculating values isn't the only thing we can do.

Scalar functions returns a value based on the input we have given.

FUNCTION(input)

The input we give a function is called a parameter. All parameters are placed in between parentheses.

COUNT()

This aggregate function counts the number of records in whatever field we give it.

SELECT COUNT(*) FROM people;

//Output Below

 count
------- 
  10

Tada! That's pretty simple, isn't it? We're asking for a count of all records in the people table!.

MAX()

This function works with integers and will give us the largest number in a field.

SELECT MAX(age) FROM people;

//Output Below

 max
-----
 800

That's it! The oldest person in our table is 800 years old!

MIN()

And as for the opposite? The youngest person in our table?

SELECT MIN(age) FROM people;

//Output Below

 min
-----
  15

Yeah! MIN will give us the minimum value in a column.

SUM()

We have the oldest and youngest ages in our table. What about adding all the ages in our table together?

SELECT SUM(age) FROM people;

//Output Below

 sum
------
 1209

Whew, that's a lot of years!

AVG()

While we're tackling ages, what is the average age of the records in people?

SELECT AVG(age) FROM people;

//Output Below

         avg
----------------------
 120.9000000000000000

Yes it is! It's also very precise isn't it?

ROUND()

We can put functions inside functions. That average is kind of unwieldy, so why don't we round it up to something more useful?

SELECT ROUND(AVG(age)) FROM people;

//Output Below

 round
-------
  121

ROUND automatically rounds up. Since we need something to round, the AVG function gets called first.

LENGTH()

What about those moments when we want to know how long a text field is?

SELECT LENGTH(email) from people;

//Output Below

 length
--------
   16
   15
   18
   21
   15
   17
   18
   20
   20
   25

This brings up the length of each record's email address.

CASE

We can also modify the result set as it is returned to us.

SELECT first_name, UCASE(last_name) 
FROM people;,

//Output Below

first_name | upper
------------+-------------
Bob | JONES
Smith | AGENT
Lancelot | DU LAC
Sherlock | HOLMES
Margaret | HAMILTON
Ada | LOVELACE
Joan | OF ARC
Amelia | EARHART
SpongeBob | SQUAREPANTS
 Leia | ORGANA

Pretty cool, huh? Can you think of cases where it could be useful to have result sets that are all upper or lower case?

Psst: if we wanted to change all of the letters to lowercase, we'd need to use the LCASE keyword instead.

Compendium

Now that we know how to work with aggregate and scalar functions, how would we find out the number of people who live in London?

SELECT COUNT(*)
FROM people
WHERE city = 'London'; 

//Output Below

 count-------   8

Brilliant! We've got this under control. IN and = both work.

Psst: we can also use the IN statement instead of =.

NOT NULL

We've seen NOT NULL a few times before. But let us iterate that it is valid for any data type, not just integers.

CREATE TABLE playlist(
  id INT  NOT NULL, 
  song_title TEXT NOT NULL, 
  length INT,
  album_id INT);

By default, fields can be NULL, in other words, have no value.

UNIQUE

Another useful constraint is UNIQUE. We add a unique column slightly differently.

Let's modify the id in the table we just created. We may have many songs in our playlist, but each one is unique.

ALTER TABLE playlist
  ADD UNIQUE (id)

That's right! UNIQUE constraint requires a value for a field, but this value cannot be duplicated anywhere in that column.

CHECK

There is a constraint that checks whether a value we want to add meets a certain condition.

Let us make sure that songs in the playlist table can not be less than three minutes long!

ALTER TABLE playlist(
  ADD CONSTRAINT length
  CHECK (length >= 3);

Spot on! With the CHECK constraint, we can make sure that any value in the length column is greater than 3.

DEFAULT

When we do not have a value to add to a column, we can set a DEFAULT constraint that will fill it in for us.

If we're just building a playlist, surely we haven't played the song yet!

ALTER TABLE playlist
  ADD played BOOLEAN 
   DEFAULT true;

Great! So we're adding a new column that accepts the data type BOOLEAN to our table.

If we don't specify whether or not we've played a song, it will automatically be filled in as true.

Dropping Columns

You know, I don't think we need the length column. How do we get rid of it?

ALTER TABLE playlist
  DROP length;

Excellent! Now that column, and any constraints associated with it, have been dropped from the table.

Keys

Keys are an important concept in relational databases.

They are primarily used to create relationships among the different tables in a database. Remember we did this?

CREATE TABLE playlist(
  id INT NOT NULL);

A key is essentially an id, much like the one we illustrate above.

But if we assign a key constraint to a field, that field will automatically be populated with a unique integer every time we add a new record to a table.

PRIMARY KEY

We have mentioned that all records need unique identifiers and have used a number of constraints to build that field.

However, PRIMARY KEY does that for us.

ALTER TABLE playlist
 ADD PRIMARY KEY(id);

That's correct! This automatically requires that the field be unique and that it cannot be empty. A primary key can only be used in one field per table.

Multiple Tables

With SQL, we can use multiple tables, and relate them to each other in order to compartmentalize information.

How relevant is it to our playlist table to have every song's album information on it?

CREATE TABLE albums(
  id INT PRIMARY KEY,
  name TEXT,
  year INT,
  artist TEXT);

We can just create a new table that has album information and relate it to our playlist!

FOREIGN KEY

It's important to understand that tables in the same database relate to one another through keys.

A foreign key in a table is a primary key from a different one!

ALTER TABLE playlist
  ADD FOREIGN KEY (album_id) 
    REFERENCES albums(id);

Bingo! We're using the primary key from our new albums table in the album_id field in the playlist table.

Reiteration

Have a look at our related tables:

  -------- albums table ---------
 id |   name   | year | artist
----+----------+------+--------
  1 | Jazz     | 1978 | Queen
  2 | The Game | 1980 | Queen

  ------- playlist table --------
 id |    song_title     | album_id 
----+-------------------+----------
  1 | Save Me           |        2 
  2 | Don't Stop Me Now |        1
  3 | Bicycle Race      |        1

Unlike primary keys, a table can have multiple foreign keys, and foreign keys can be empty fields.

Two songs in our playlist belong to the same album. SQL is pretty cool, huh?

Compendium

Great! Since we've covered primary keys, foreign keys, and constraints, let's recap and make sure that the artist field in the albums table cannot be empty.

ALTER TABLE albums
  ALTER COLUMN artist SET NOT NULL;

Yippee! Now we will always have to provide an artist name when creating a new record for this table.

Introduction

SQL (Standard Query Language) is the standard language for anything database related.

What We'll Need

To build a web site that shows data from a database, you will need to get and install the following;

An RDBMS ( Relational Database Management System) program like MySQL from https://dev.mysql.com/downloads/ or,

You can also install SQL Server Express from https://www.microsoft.com/en-in/download/details.aspx?id=30438 . It is a free version of Microsoft SQL Server which allows up to 10GB of database storage.

Alternatively, if you're planning to develop an application with PHP and MySQL you can install WampServer or XAMPP. WampServer is a Windows web development environment. 

It allows you to create web applications with Apache2, PHP and a MySQL database. It will also provide the MySQL administrative tool PhpMyAdmin to easily manage your databases using a web browser. 

You can download it from here http://www.wampserver.com/en/.

And that's all you'll need to get started.