1. What’s a Datab...
2. What’s a Relat...
3. What can a Database ...
To make the most use out of learning SQL, I think it’s very useful to examine these questions:
- What exactly is a database?
- What’s the difference with a relational database?
- Why should I care about databases at all?
What’s a Database?
It’s exactly what it says: the base where all your data resides. More importantly, it’s nothing but data. It doesn’t have a layout, or programmed behaviour, or whatever – it’s only data. It’s the reason that you need other systems and languages to do something with databases, but it’s also the reason why they are so fast, and can hold huge amounts of data.
This “data” can be anything: numbers, pieces of text, prices, passwords, even huge strings that make up an image.
To store the data, we use the best method known to mankind: tables. Every row within the table is a single entry, while the columns of the table designate the different properties of that entry. For example, one entry could be a specific user account, with the columns “username”, “password” and “email”.
What’s a Relational Database?
Essentially, a relational database is one that consists of multiple tables that can be related. These tables can reference rows from other tables, and therefore create useful relationships between data.
For example, say you have an online gaming website, where you can win achievements if you finish a game. You have one table containing all users, and one table containing the achievements per game.
Now, one way to store this information, is by adding the username to the string of users that got a certain achievement. With a relational database, however, you can simply reference the corresponding row in the users table. This way, if the username is changed some day, all data is still correct.
If it’s so useful, what other databases could there possibly be? As I mentioned in the introduction, NoSQL database systems also exist. One of the huge differences between them and SQL systems, is that all SQL systems work with relational databases, while NoSQL doesn’t necessarily work that way.
For example, you could have a hierarchical database that is one big table, which has lots of “children tables” that contain data on one specific aspect of the parent. With such a system, data becomes more like a tree structure. Which is useful if you have, you know, data that is connected more like a tree. Don’t worry though, we won’t be doing anything with that in this course.
What can a Database do?
A database is the best possible way to handle data – and data is everywhere. Data can be large, such as a complete blog post or subtitles for a video, or very small, such as a username or the amount of credits in somebody’s account. Databases not only store this data in a sensible manner, SQL also provides numerous ways to select exactly the data that you want. You want to show the last 10 comments made? Easily done. You want to find all blog posts with the word “panda”? Done. You want to send a “thank you” message to every user that has been registered for more than a year? You get the idea.
Every (proper) database has 4 fundamental operations, known as CRUD:
- Create: Creating a new database, table, or entry.
- Read: Requesting certain information from the database
- Update: Changing a certain entry, or the columns of a table
- Delete: Deleting entries or columns from a table, or deleting a whole table or database
With fundamental, I mean that you really can’t do anything else. And that’s fine, because you won’t ever need more than this.
Of these 4 operations, the first two are by far the most complex. First of all, there are many different settings you can use when creating a table. You can set a column to allow only integer numbers, or to allow only strings of text, or to automatically increment every time you add a new entry.
Secondly, there are even more ways to select data. You can select all existing entries, or all entries within a certain category, or all entries with a username that starts with the letter “P” – the list goes on and on.
On the other hand, there aren’t many ways to change an already existing entry, or to delete data from a table.
I will, therefore, start the course by explaining the CRUD syntax in short, and then use all of the remaining chapters to talk about creating and reading data. From now on, I will also call the columns of a table its structure, and one specific value (e.g., row 2 column 2) a field. I might also call entries rows or records, because it’s more intuitive sometimes.