SQL for Noobs: Basics
Arjun Aravind • 11 October 2020 • 7 min read
If you've heard of SQL, then you know that it's probably the backbone of the software application world. If you haven't heard of it, then well, it's really important.
Most modern software applications use a database of some kind to store information and a lot of these applications use SQL. I'm very sure you might have across terms such as 'NoSQL', 'MySQL' or some such word and if you felt a bit overwhelmed by all this, then do not worry, as we will discuss all of this in this article.
- By the way, talking about software, my cousin's startup initiative, Streamworks!
In this article, we're gonna talk about why we need a technology like SQL, the fundamental concepts involved in understanding it and how it works.
Alright, let's do this stuff!
Why do we need SQL?
There are two main reasons, in my opinion. The first one is really basic.
Let's say you're building an application on your computer. You might have a need for data to be persistent, that is, for it to exist even after the application is closed. This means that you have to store this data somewhere, right? But where?
An obvious and straightforward choice would be to maybe store it as a file somewhere in your computer. That way, when the application starts again, you can code the application to read the contents of the file and get the data from it.
But this presents a few issues.There's no guarantee that the data entered in the file will be consistent. For example, let's say you're storing details of people in this file like this.
India, New Delhi, Asia
China, Beijing, Asia
Italy, Rome, EuropeThis approach seems nice. But we haven't considered the disadvantages that come with this method.
Your application is expecting a country, a capital and a continent to be present on each line, right? All seperated by commas.
What happens when a country, such as South Africa, has more than one capital? Or what happens if someone forgets to include the continent for a country? Or maybe someone can enter the same country twice.
Italy, Rome, Europe
South Africa, Bloemfontein, Cape Town, Pretoria, Africa
Norway, Oslo
Riyadh, Saudi Arabia, Asia
Italy, Rome, EuropeIn line 2, your application might think that Cape Town is a continent as it is the third term in that line. In line 3, your application might look for a continent and will not find anything there. Line 4 is an example of data duplication, which is bad.
If you don't take all the above-mentioned scenarios into account, your application might throw unexpected errors when reading the data. These are but a few of the problems which might arise if you store data in a very unstructured way.
You could always code for all these scenarios in advance but it's always good to use a platform which does all the work for you.So, this is where SQL comes into the picture!
I'll explain SQL later on in the article, but let's just say that it gives you a way to explicitly define all the data that you will need, which fields are required or have to be unique, etc.
So we just discussed one advantage of SQL. But this is just the tip of the iceberg.
The main advantage of SQL over most databases is that it is relational! Let's look at some data which is relational in nature.
All you DC fans, I got you.
You may ask me, just how the hell are we supposed to store and retrieve this information without possibly going insane?
The answer, my friends, lies with SQL.
What is SQL and how does it work?
Alright, before we discuss all the stuff, one small thing to note here. When I said that SQL is the answer to the above scenarios, I might've been a little bit......misleading.
SQL, Structured Query Language, is simply a language. The thing that actually stores the data in a structured and relational way is an RDBMS, or a 'Relational Database Management System'. Don't worry if the name is overtly complicated. It just means that it's a database which stores data in a relational manner.
However, with regards to SQL, you could say that both the RDBMS and the SQL make up a single technology.So, in this article or anytime someone mentions the word 'SQL' or 'SQL database', they're most probably referring to the entire technology, not any individual piece of it.
Alright, let's discuss how SQL works and its individual components. There are exactly four of them.
Tables
These are, by far, the most important part of an SQL database. But they're the easiest thing to understand.My favourite way to explain them is to think of them as exactly similar to Excel sheets. Take a look.
This is an excel sheet of dishes, the country they originate from and the type of dish they are!
Who knew that French Fries were from Belgium?
And here's an equivalent SQL table for the same data.
I mean, they're almost the same concept! You could almost say Excel is a sort of SQL for non-developers.....is that where Microsoft got the idea from? Hmmmmmm.
That's it, congrats, you just learnt what a table is. It was that simple.
Columns
A table is basically a collection of columns. In the table about 'dishes' that we saw above:-id, namecountry_of_origintypeA column can define the type of data that can be stored in it.For example, in the 'dishes' table, the
idIn SQL, you can define a column to have certain 'constraints'. For example, you can specify that a particular column can contain only unique values or that it cannot be empty. You can even specify the maximum length of data in a column; for example, we can say that a column can only contain text that is atmost 20 characters long.
Records (Data)
In a table, each row is called a record. It is a single entity.In the oft-mentioned 'dishes' table, the row containing Masala Dosa, India and Breakfast is a single entity meaning that these three values are related.
The name of the dish is Masala Dosa, it's country of origin is India and it's a type of breakfast dish. Can you see how we related these values by including them in a single row, just like an excel sheet row?
I'll give more examples of this, don't worry!
Databases
Well, quite simply, a collection of tables is called a database. That's it. Nothing special to it.
You can group a collection of related tables into a database. Like putting related files in a folder. This helps you to prevent any confusion that might arise out of having too many unrelated tables together.
Show me a fun example of an SQL database!
Remember that complicated graph of relationships about superheroes (click here to see it again) that we saw earlier! Here's the SQL database for that.
Firstly, it's completely alright if you can't understand this. But take a good look and try to see how SQL allows us to make complex relationships using just tables and columns!
I'll be teaching you guys how to build relationships like this, in SQL, in the coming articles. It would be a bit too overwhelming maybe.
What are the different types of SQL out there?
SQL is a bit like English.
There are a lot of English accents and variants out there, right? British English and American English are a few examples.
Similarly, there are a lot of variants of SQL out there with their own little twists and additional features.Different developers like different variants. You may have already heard of some of these like MySQL (my personal fav), T-SQL, Oracle, MariaDB, SQLite and PostgreSQL.
All of these serve different purposes. Let's talk about a few of them briefly.
- SQLite is a very light variant of SQL and is made for usage in mobile applications and embedded devices.
- PostgreSQL is a variant built for scale and can handle a large amount of users concurrently.
- MySQL is a general-purpose implementation of SQL. It's good for beginners.
- T-SQL or Transact-SQL is an SQL implementation by Microsoft. It adds a lot of functionality for 'transactions'. We'll learn about transactions in a later article.
---------------
Alright, so we've come to the end of this article!
The next articles will be covering concepts such as actually building tables and databases, transactions, handling relationships in SQL (like the superhero example we saw) and many others in detail. Stay tuned!
If you have any criticisms or additions to this article, feel free to contact me! Hope this was helpful, cheers!
