[SQL Basic] What is SQL? — Introduction of Databases
#1. Marketing/Data Analysis for Beginners
Database? What’s that?
What’s the first thing you think of when you hear the word ‘database’? Maybe you have an image of a dark room filled with hundreds of computers. Maybe you’re thinking of a block of text that is frustrating to even look at, or maybe you think it’s just zeros and ones.
Does data even have anything to do with our day to day life?
The answer is… yes! In fact, we create data every day and we don’t even realize it. When we wake up in the morning to check our notifications on our phones, take the subway or bus to commute, or browse social media it is all stored somewhere. It’s stored on a database!
Hundreds of millions of services create data every day, and it is all stored in a given place. But it is hidden somewhere and only designated people have access to it. Passwords are necessary, and of course special invitations to view the database.
“But it’s MY data!” you say, “Why do I need a password to access it?”
Because many people’s data are all collected on the database, so security is necessary to keep everyone’s data safe!
SQL?
Let’s imagine that we have a key to access a secret room holding tons of data. But the only thing we can make out are numbers and abbreviations. If we don’t have a way to translate it, that data is useless and the key to the secret room becomes meaningless.
That’s where SQL comes in! SQL stands for Structured Query Language. It is the language that translates all that data into understandable information.
For now, let’s say that SQL is a ‘Question.’ We ask the database a question by using SQL. The word ‘structured’ is key here because the question won’t make sense if it’s not asked correctly. So we have to learn the language as well as the correct questions to ask in order to make sense of the database.
Thankfully, we don’t have to learn all the words to begin understanding databases. The most important thing is to know the patterns.
So let’s learn some patterns together! Below, we cover the basic concept of the database with Tables and Columns.
Table? Column? What?
If you can use Excel, you’ll have no problem understanding the basic concept of SQL. And if you have heard about SQL before, you will be able to follow along in our tutorials with no problems. The most important thing is to not let the scary word ‘database’ hold you back.
But we’re assuming that if you’re reading this, you’re a complete beginner. So let’s step back a bit. Let’s go back to Excel.
Excel is a collection of sheets. It contains data in rows and columns.
It’s usually pretty easy to figure out the information you need. For example let’s say you want to call William Patterson, an employee at your company. You’re not sure what the extension code is to his phone, but you have a handy Employee Number sheet to assist you.
Take a look at the image provided below. Can you find William’s extension?
That’s right! It’s #6! Following the row that has William’s name, we can look under the column that’s titled ‘officeCode’ to find the corresponding number. The information is provided in the F5 cell.
Now let’s take a look at the database.
The database has tables instead of sheets.
The name of the Table here is ‘Employees’. Tables can be as large or as small as you’d like, depending on your needs. The crucial thing to focus on here is Columns. Since columns act as categories that combine individual values together, the names of columns before important reference points for finding data.
Take a look at the image provided below. Let’s say we want to find Willaim’s information in our database. What kind of question can we ask?
Keep in mind, there is no concept of rows in databases. What you see here (row 1, row 2, row 3, etc) is just the sequence of results for the question we asked the database. In this case, the question we asked is (SELECT * FROM employees WHERE Officecode=6).
Not sure what that means?
Don’t worry! That’s why we’ve made this tutorial series.
SQLGate, Marketing / Data Analysis for Beginners
The basics of SQL from downloading our software to writing queries in the program will all be covered in this tutorial. We created our tutorials with the mindset that complete beginners are following along, so if you don’t have any prior knowledge about SQL then this tutorial series is perfect for you! Let’s do this together!
# [SQL Basic Series] — Marketing/Data Analysis for Beginners
1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
