SQL is a language used to interact with databases. You can create, read, update, and delete data from a database using SQL. Before we get too deep, let’s talk briefly about what a database is.
What is a database?
Most web apps need to be able to store some kind of data. A database is a common way to do that. Let’s look at StackOverflow as an example:
Not exactly StackOverflow, but it’s relevant to the subject matter, and it’s a StackExchange site. It stores the same kinds of data as StackOverflow.
Data stored by Stack
- Users
- username
- bio
- location
- Github
- URL (web site)
- date joined
- probably others
- Questions
- title
- body
- tags
- time posted
- views
- upvotes
- downvotes
- Answers
- Mostly the same as questions
- Comments
- body
- upvotes
- time
These are each distinct kinds of data Stack has to store. Each one is defined as what’s called a table. Think of that like an Excel spreadsheet. Each table has columns that you declare when you create the table. You name them, set what type of data they will store (string, integer, float, array) and add additional constraints – maybe one column should never be empty for any new record (NOT NULL), or maybe each record has to have a totally unique value that isn’t shared by any existing record (UNIQUE).
Relational
Let’s look at the question table. (No insider knowledge about the Stack database. I’m just using it as an example to illustrate typical needs of a web application.) Each question has a user who asked it. Put a different way, a question has a relationship to the user who posted it, and those relationships can be defined in your database. That’s why this kind of database is called a relational database.
Most tables in a relational database have a column (or sometimes a combination of columns) that serve as a unique identifier for each record in that table. This is called a primary key. You can create relationships between two tables by creating a column in one of the tables that references the primary key value in another.
Here’s an example. Imagine I create a StackOverflow account. My record in the user table gets assigned a primary key of 1. Then, I ask a question. The questions table has a column called “asker” or something like that. When I ask my question, the app stored the question record with the value “1” in the “asker” column to show that the question is related to my user record.
When you run queries, which is what it’s called when you get data out of the database, you can join records together based on the relationships they have to one another.
SQL is case insensitive, but it’s customarily written in all caps.
Start the workshop
Pull up db-fiddle
Point out that we are using the MySQL database engine, version 5.7. There are other engines, but SQL is mostly universal across them. There are differences, but you’ll discover those as you get deeper into working with whatever engine you end up working with.
That’s enough of an intro. Let’s get our hands dirty and write some SQL!
https://github.com/WebDevSimplified/Learn-SQL
Exercises
1. Create a songs table
Do this in the Schema SQL pane. Schema is the definition of your database’s tables and the columns in those tables.
Create Table documentation: https://dev.mysql.com/doc/refman/5.7/en/create-table.html
Use documentation to find data types: https://dev.mysql.com/doc/refman/5.7/en/data-types.html
create table songs (
id int not null auto_increment,
name varchar(255) not null,
length float not null,
foreign key (album_id) references albums(id)
);2. Select only the Names of all the Bands
Retrieve data using select
select
select name as "Band Name" from bands;3. Select the Oldest Album
select * from albums
where release_year is not null
order by release_year asc
limit 1;4. Get all Bands that have Albums
select distinct bands.name as "Band Name" from bands
join albums on bands.id = albums.band_id;5. Get all Bands that have No Albums
select bands.name as 'Band Name'
from bands
left join albums on bands.id = albums.band_id
group by bands.name
having count(albums.id) = 0;Bonus:
select bands.name as 'Band Name', group_concat(albums.name separator ', ')
from bands
left join albums on bands.id = albums.band_id
group by bands.name, albums.band_id;6. Get the Longest Album
select
albums.name as 'Name',
albums.release_year as 'Release Year',
sum(songs.length) as 'Duration' from albums
left join songs on songs.album_id = albums.id
group by albums.name, albums.release_year
order by sum(songs.length) desc
limit 1;7. Update the Release Year of the Album with no Release Year
in schema
update albums set release_year = 1986 where id = 4;8. Insert a record for your favorite Band and one of their Albums
in schema:
insert into bands (name) values ('The Offspring');
insert into albums (name, release_year, band_id)
values ('Smash', 1994, last_insert_id());and in query:
select * from bands;
select * from albums;or:
select
albums.name,
albums.release_year,
bands.name from albums
join bands on albums.band_id = bands.id;9. Delete the Band and Album you added in #8
in schema:
delete from albums where name = 'Smash';
delete from bands where name = 'The Offspring';Since those two cancel each other out, we can now just remove them from the schema
10. Get the Average Length of all Songs
select avg(length) as 'Average Song Length' from songs;11. Select the longest Song off each Album
select
albums.name as 'Album',
albums.release_year as 'Release Year',
max(length) as 'Duration'
from songs
join albums
on songs.album_id = albums.id
group by albums.name, albums.release_year;12. Get the number of Songs for each Band
select
bands.name as 'band',
count(songs.name) as 'Number of Songs'
from bands
join albums
on albums.band_id = bands.id
join songs
on songs.album_id = albums.id
group by bands.name;