Everything I have learned about databases in my three years of school (part 2)
Everything about SQL
In case you missed the first part and want to know how to create a database scheme, click here. If you already read it or only want to know how SQL works, then please continue reading.
End result: https://www.db-fiddle.com/f/jTEqWCFCrGinLtekLV9jxv/7
We have this beautiful database schema and now it’s time to actually create it.
But first of all, I have to admit that I made a mistake while creating this schema… Spaces are not allowed in SQL which means instead of Release year it should be Release_year. Same with Release Date, First name, and Last name. And the PK in BookToAuthor is also wrong (oops…). It should be IDBtA.
It’s not much, but I still wanted to mention it. I mean, sure, I could go back and recreate the entire schema (because I didn’t save it, yes stupid, I know) and act like it was always correct, but then I remembered this YouTube video and just thought “ok, admitting that I was wrong is probably just easier and tbh it should also be more normalized”.
If you clicked on the link: yes, I am that kind of person who plays Wordle every day and yes I hate that the NYTimes bought it and no I do not know what a CAULK or a ULCER is and yes I failed to guess VIVID because two double letters? seriously??
What is SQL?
SQL stands for Structured Query Language. As you can probably guess, SQL is used to create, delete, insert, update, etc. databases, database records and tables.
In order to do all of the above mentioned queries, we will use DDL, DML, DCL, and DQL.
SQL Syntax
DDL
In order to create and modify a database, we will use DDL — Data Definition Language. The best-known commands of DDL are the following:
- CREATE: used to create a database or its records.
- ALTER: used to modify the structure of the database.
- DROP: used to delete the database or its records.
I think they are pretty self explanatory, but I still added a short explanation.
DML
In order to modify the existing data of a database, we use DML — Data Manipulation Language. The best-known commands of DML are:
- INSERT: used to insert data into a table.
- UPDATE: used to update existing data.
- DELETE: used to delete data from a database.
Also, this is rather self explanatory.
DCL
In order to give permission to certain users, we use DCL — Data Control Language. The best-known commands of DCL are:
- GRANT: used to give a user permission.
- REVOKE: used to withdraw a user permission.
This is not as intuitive as the others, but basically it’s to give certain users certain permissions. For example, user A has permission to alter tables, but they can’t drop them meanwhile user B can alter and drop tables. However, we won’t use this in this example.
DQL
Now… the moment we all came here for… In order to query a database, we use Data Query Language. The best-known command of DQL is:
- SELECT: used to query a database.
This is probably the best-known and most important command.
While SQL itself is not case sensitive, the only easy-to-use SQL online playground I could find is case sensitive. (Case sensitive means that it cares if you write CREATE or create or even cReaTe.)
Create database schema
Create database
Now we can finally create the database itself with DDL and good news, it’s really easy:
CREATE DATABASE FavBooks;That’s it.
In order to modify it, you have to USE it:
USE FavBooks;Also very simple.
Create tables
Creating tables is not that difficult, but there are still some things you need to watch out for. The first mistake you could make is creating a table that has a foreign key before the table the foreign key is from.
In our example from above, it would be the table BookToAuthor, for example. If you were to create this table, you would get an error, because the reference tables (Book and Author) do not exist (yet). This means we have to create a table without a foreign key first. Let’s create Movie.
As you can see, it’s always name, datatype, additions/constraints. Don’t forget to define the primary key at the end, the semicolon after the closing bracket, and do not add a comma after the last line. The 40 in the brackets just means it can only have 40 characters (one character = one letter/symbol).
Now that we have the table Movie, we can also create the table Book:
To define the foreign key, the syntax is a bit different. It’s actually just how you’d say it: The foreign key is IDM which is from the table Movie’s IDM.
Now we only need to create Author and BookToAuthor:
The ID names might be confusing for you, so feel free to just use whatever works for you.
Summarized and typed in the online editor db-fiddle.com, it looks like this:
Make sure to select MySQL v8.0 in the upper left corner if you decide to use db-fiddle as well. Also, the indention is important too.
Change table column name
You might think “why is it called name instead of title?” when looking at the Book table. The answer is simple: so we can use the ALTER statement!
It’s really simple and almost reads like plain English text, but I still wanted to show it:
ALTER TABLE Book RENAME COLUMN Name TO Title;“Alter the table Book and rename the column Name to Title”. Very straightforward and easy.
Insert data
Now we have the structure, but no data. To insert something, we need DML’s INSERT statement:
INSERT INTO Book (title, genre, release_date, movie) VALUES ("Fight Club", "Novel", "1996-08-17", true);It once more almost reads like normal English: “Insert into the table Book the title, genre, release date, and whether it has a movie and the values are Fight Club, novel, 17th August 1996, and true.”
And since there is a movie, we also need to create the movie.
INSERT INTO Movie (name, release_year) VALUES ("Fight Club", 1999);Change existing data
We have a book and a movie, but no relation between them. We still need to insert the movie’s primary key value to the book’s foreign key. In order to do that, we need to update the already existing entry in our book table.
UPDATE Book SET IDM = 1 WHERE title = "Fight Club";The WHERE clause is very important because if we had multiple records (books) and didn’t use the WHERE clause, all of our books would change their IDM to 1.
Add more data
To actually work with a database, we need much more data. Right now we have one book and one movie and not even an author. In order to use DQL, add some more data to your database.
This is what I used (including Fight Club from above):
A nice dataset of some books I had to read in my English classes and Stephen King.
Query a database
Query over one table
The time has come for the fun part: the SELECT statement.
As before, the select statement is rather self explanatory — you SELECT something from the database. For example, if you wanted to select all titles:
SELECT title FROM Book;In order to select all columns from a table, an asterisk is used:
SELECT * FROM Book; db-fiddle.com displays a nice table with all the data when you execute a query.
Don’t forget the
USE FavBooks;statement at the very top of your queries.
WHERE
To “filter” the “searches”, the WHERE clause is used. For instance, you want to select the titles from books where the genre equals “novel”:
SELECT title FROM Book WHERE genre = "Novel";
AND, query a date
It is also possible to chain the WHERE clauses with the help of AND. Example: You want the titles of books with the genre “novel”, but only the ones that came out after 2000:
SELECT Title FROM Book WHERE Genre = "Novel" AND Release_Date > '2000-01-01';
Not only is it possible to use the equals sign, but also the greater and less than signs for dates and numbers.
Important to remember is that dates require single quotes. Otherwise, it will get interpreted as a mathematical operation and not a date.
Search for parts/patterns/%
You can also search for parts or patterns. Maybe you want to know which of your favourite books start with the word “The”:
SELECT title FROM Book WHERE title LIKE "The%";The % indicates that it doesn’t matter what comes afterward (or before if you put it in front of a letter/word) and instead of an equals sign, the word LIKE is used. I usually used this to search for names that start with a certain letter (select all people whose names start with ‘m’).
COUNT, AS
Searching for patterns is often used with the COUNT function. COUNT counts the selected database records and displays the number only.
SELECT COUNT(title) FROM Book WHERE title LIKE "The%";Since COUNT is a function, the column’s name must be written inside parentheses. It is also possible to use the asterisk instead of a column name.
This will display 2 with the column title “COUNT(title)” which is not very nice. We can change that by adding an alias:
SELECT COUNT(title) AS "Book titles with 'The'" FROM Book WHERE title LIKE "The%";
Looks much better in my eyes.
Etc…
As you can probably guess, there are many more operations. This was only a short overview of the statements, clauses, and functions I used the most at school.
Query over two tables
Now, the EXTREMELY fun part begins! We’ve only ever queried over one table, but now we will query over multiple tables! The keyword for this is “JOINS” or more precisely “SQL JOINS”:
They’re a bit difficult to understand in the beginning, but once you’re used to them, they’re rather easy to use. Most important is this infographic above. It can save lives (at least it saved my sanity during my 3h long SQL exam).
Let’s take LEFT JOIN (top left) as an example. A and B are tables and the query basically says “Select all records from the left table (A) and the matching records based on KEY from the right table (B)”. KEY is a column name such as “title” or “genre”. We can give the two tables aliases by simply adding an abbreviation after the table name (here I used B for book and M for movie):
SELECT * FROM Book LEFT JOIN Movie ON Book.title = Movie.name;# Same as:SELECT * FROM Book B LEFT JOIN Movie M ON B.title = M.name;
In the query above, we want to select all books and all movies which names are the same as the book’s titles:
Since there is no movie for Under the dome, the records of the movie columns are null.
If we wouldn’t want to have null as an output, we’d need to use the INNER JOIN. This JOIN only returns values that are present in both tables:
SELECT * FROM Book B INNER JOIN Movie M ON B.title = M.name;
This time, there is no row full of null values.
On the other hand, you can also do the opposite. Right under the LEFT JOIN is the LEFT JOIN KEY NULL. This returns all the records in the left table which are not in the right table:
SELECT * FROM Book B LEFT JOIN Movie M ON B.title = M.name WHERE M.name IS NULL;
As expected, only Under the dome is returned since it’s the only book without a movie (but a really good series, I used to be obsessed with it).
Summary
When working with JOINS, it’s important to be sure what you want as an output; do you want null values? Only null values? What two columns do you want to compare? It can be tricky at first, but as I already said, it will get easier with time. If the picture is not very clear to you, here’s a small summary of what the different JOINS do:
LEFT JOIN — returns all records from the left table and the same ones from the right table.
WHERE B.Key IS NULL — all records from A which are not in B.
RIGHT JOIN — returns all records from the right able and the same ones from the left table.
WHERE A.Key IS NULL — all records from B which are not in A.
(the opposite of LEFT JOIN)
INNER JOIN — returns all records that are present in both tables.
FULL OUTER JOIN — returns all records no matter if there are matching records or not.
WHERE clause — returns only records which are null/
Thank you
If you made it until here, props to you. This is my longest article to date and I hope you learned something!
