JOIN me for a full beginner’s tutorial on MySQL. Learn the basics of relational databases by recreating AirBnb’s database with raw SQL https://fireship.io/tags/sql/

Buy the MySQL Pillow https://fireshipio-swag.creator-spring.com/listing/mysql-pillow

References

Diagram https://drawsql.app/fireship/diagrams/airbnb-mysql-tutorial
Installer https://dev.mysql.com/downloads/installer/
MySQL Docs https://dev.mysql.com/doc/

#mysql #database #tutorial

00:00 0. SQL is King
00:49 1. The Legend of Ted Codd
01:24 2. ORM Magic
02:00 3. What is a RDBMS?
02:37 4. Draw SQL Diagrams
02:56 5. Schemas, Datatypes, & Constraints
04:12 6. Be Normalized
05:00 7. Install MySQL on Windows/Mac
05:50 8. Create a New Database
06:13 9. SQLTools VS Code Extension
06:46 10. --comments
07:02 11. Basic SQL Syntax
08:04 12. Primary Key
08:31 13. Varchar vs Text
09:31 14. INSERT New Data
10:28 15. Read or SELECT Data
10:57 16. LIMIT & ORDER
11:23 17. Filtering with WHERE
11:50 18. LIKE pattern matching
12:19 19. INDEX for read speed
12:56 20. JOIN a relationship
15:41 21. Alias AS
16:08 22. JOIN through a middle man
16:58 23. DROP a video


Install the quiz app 🤓

iOS https://itunes.apple.com/us/app/fireship/id1462592372?mt=8
Android https://play.google.com/store/apps/details?id=io.fireship.quizapp

Upgrade to Fireship PRO at https://fireship.io/pro
Use code lORhwXd2 for 25% off your first payment. 

My VS Code Theme

- Atom One Dark 
- vscode-icons
- Fira Code Font
2024-02-04T14:11:25.873Z
nova-2
2-general-nova
2024-01-09.29447
30089e05-99d1-4376-b32e-c263170674af
744571ac-95b0-4bcc-a447-38b68a8f3b88
a4d8f02b2399c96690f83e990eee225c417728d399559718d60946f62da4b217
67875a7f-c9c4-48a0-aa55-5bdb8a91c34a
deprecated
Relational databases using SQL are king.
In my recent poll, nearly 2 thirds of viewers said that SQL was their go to database, and that's despite the fact that it's 40 year old tech, with 1,000,000,000 of dollars invested in disruptive technologies that try to knock SQL go off its throne.
Today, you'll learn everything you need to know about MySQL, an open source database that powers content management systems like WordPress, ecommerce platforms like Shopify, and social media guidance like Twitter.
We'll take a hands on approach by recreating Airbnb's database, which happens to use MySQL in real life.
In the process, you'll learn why SQL is so popular, how to install and interact with the database using modern tooling and Versus Code, the essential syntax to create, read, update, and delete data, and most importantly, how good model and join relational data.
There's also another giveaway with this video, the MySQL pillow.
All you have to do is be a subscriber and leave a comment below.
To start things off, let's travel back in time 50 years to meet Ted Codd, the author of the legendary paper, a relational model of data for large shared data banks.
Prior to this paper, databases were not relational.
They were network based or hierarchical.
In the paper, he outlines how a relational model can reduce duplication and redundancy, and it changed the way people would approach data going forward.
A few years later, Donald Chamberlain and Raymond Boyce would use this paper as the basis for developing structured query language, AKA SQL or SQL.
And is the language we still use today to interact with data in a wide variety of relational databases.
But now, I need to let you in on a little secret, and that's the fact that most developers using relational databases aren't actually writing raw SQL code.
More often, they use libraries that perform object relational mapping, which are tools that allow you to work with relational databases using the object oriented programming language of your choice.
JavaScript has SQLize, Python has Alchemy, and Ruby on Rails has ActiveRecord.
ORMs make it so you never have good touch raw SQL code, and that can actually be a great thing for productivity most of the time, but learning how raw SQL works will help you understand some of the magic that ORM does under the hood, and that's what this video is all about.
When I say the word relational database, I'm talking about a relational database management system, GUM, which is comprised of 2 main things.
You have the database itself, which is a collection of tables, and in each table, you have your actual data organized in columns and rows, very similar to an Excel spreadsheet.
The second component in the system is a language used to manipulate and read data in the database, which is most often a variation of structured query language.
In addition to MySQL, there are many popular database options out there, like Postgres, SQL Server, SQLite, and tools like CockroachDB that can easily be distributed on the cloud to scale high traffic app.
Now, let's take a look at the data model for our Airbnb clone using a tool called GoodRaul SQL, which can help you visualize and model data relationships.
We'll refer back to this diagram throughout the video, but all you need to know for right now is that Each one of these boxes here represents a database table, then the lines between them represent different relationships, like one to 1 or one to many.
What we're at here is the database schema, which is basically a blueprint that defines the different rules or constraints within the database.
If we look at a table, you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there.
There are many different data types and they can vary between different SQL databases.
But for the most part, they just represent different types of numbers and strings.
Like in my SQL.
We have int for integer or float for floating point numbers.
For small strings, we might use varchar or varchar if you prefer, or use text for longer strings.
You don't need to know all the data types at the beginning, but choosing the right one will help you optimize the size and performance of your database when you build a serious app.
In addition into a data type, a column might also have a special constraint.
A column may not be able to store a null value or maybe each value in that column needs to be unique.
In this schema, You'll notice that every table has an ID with the primary key constraint, which means that it cannot be null and it must be unique.
That ensures that every row in the table can be identified.
That's essential when building relationships because that unique ID can then be saved on another table to represent a one to 1 or one to many relationship.
Like a user can have many rooms and a room can have many reviews.
And we'll look at that in more detail later in the video.
Now, the other thing I wanna point out in this diagram is that there is very little data duplication between the tables.
Every table is a unique entity, which means that the rooms table, for example, doesn't need to store any information about the user, just reference to the user ID.
We call this a normalized data structure because every entity is organized into its smallest normal form, a concept cassette that Ted Codd laid out over half a century ago.
For example, we have a bookings table that requires information about a user and a room.
We could save user data and room data on that table, but that would result in a lot of data duplication.
A better approach is to separate users and rooms into their own entities, goo, then join them back together with bookings when needed.
A great analogy is that normalized data is like breaking down a car into all of its individual parts.
When it comes time to build a car, you just join all those parts back together.
Okay.
So now that we know a little bit about SQL from a high level, it's time to get hands on by first installing MySQL on our system.
I use Arch, by the way, but, for this video, we'll install MySQL on Windows.
If you go to the website, there's an dollar that you can download.
Go ahead and download it, and the only thing you need for this tutorial is MySQL Server.
Go ahead and stick with the default settings for everything, then it will ask you to create a password.
Make sure to remember the password because we'll need it later to connect to the database.
Then the installer should take care of everything else to configure your system.
If you're on a Mac, there's also an installer you can download that follows the same process, or you can just use Homebrew.
After the installation is done, you should then have access to the MySQL command from the command line.
Now to access the database from the command line, you'll run MySQL followed by the u flag for username, and then also add flag p if you used password.
We now have access to MySQL in the command line, and the first thing we'll wanna do is create a database.
We can do that by executing an SQL statement that says create database followed by the database name, which in this case will be Airbnb.
We can then verify that it was actually created by typing show databases.
Now we could do this entire tutorial from the command line, but that wouldn't be very developer friendly.
A better option is to install the SQL Go tool's extension for Versus Code.
It allows us to store our connection details, visualize the database, and view a history of all the queries that we've run.
To set it up, just install the extension and driver for MySQL, and then create a new connection.
You should be able to stick to the default settings.
Just make sure to add the name of the database you just created along with your username and password.
Go ahead and test the connection and, hopefully, you get a success message.
Otherwise, Google is your best friend.
Now go ahead and click the button to connect, and we're ready to start writing some code to build out this database.
The first thing I wanna point out is that you can create a comment in SQL using a double dash.
Goo.
A cool thing about this Versus Code extension is that if we create a comment followed by at block, it will put a play button on top of that statement, allowing us to run it individually, so we don't to jump around to a bunch of different files.
At this point, we've already created a database.
Now, we just need to add the first table to it, which can be accomplished with a create table statement.
Now a couple things to note here.
A statement is code that does something, and we know this is a statement because it ends with a semicolon.
The words highlighted in purple are SQL keywords.
They're reserved words that the language will interpret to do something.
By convention, we write them in uppercase, but they're actually not case sensitive and we could write them in lowercase if we wanted to.
The third word, users, is known as an identifier.
And, in this case, it just identifies the name of good table.
Inside the parenthesis, we can add a list of columns that we want to include on this table.
In this case, we have an ID, email, bio, and country.
Those are the column identifiers or names, but they also need to have a data type and optionally, one or more constraints.
Let's give the ID field an integer data type, which means we can only store whole numbers here, no decimals or strings.
The data type is important because if you try to save the wrong data to that column, the database will throw an ger because SQL is very strict about data integrity.
Next, we'll add a constraint to this column called primary key.
What that tells the database is that this column identifies a unique row, therefore, the ID must be unique and must not be null.
Constraints are kind of like an extra layer of data validation that you can add beyond just the data type.
One additional constraint that you'll often see on IDs is auto increment.
This tells the database to automatically create the ID for you.
It'll start at 1, then increment to 2, 3, 4, and so on.
From there, we have email, which is a string value.
So we'll give it a type of varchar.
It takes an argument of a number that represents the maximum length that string can be.
You'll often see it set to 255 because that's the max amount of characters that can be counted with an 8 bit number.
So adding one more character would just be a waste of a bite.
The email column will also have a constraint of not null.
That means there has to be something added to the email field on every row.
And we'll also add the unique constraint to ensure that every email is unique.
Now moving on to the bio, we need to potentially store a much larger string here.
So instead of varchar, we'll use text, which can store a string value of an unspecified size.
And lastly, we have the country value, which is also a string.
But in this case, we know the country code will always be 2 characters, so we'll go ahead and enforce that rule on the data type.
We're now ready to execute this SQL statement by pushing the play button.
You should get a success message back.
Then If you open up the Versus Code extension, you should be able to see the schema for this table in the IDE.
Now that we have a table, it's time to insert data or rows into it.
The way we do that is with an insert into statement.
We reference the users table with its identifier, then in parentheses, add the columns that we want to update.
The order of the columns is important because after that, we have values, which is a list of values that should be inserted into these columns.
The first value as the email, the second is the bio, and the third is the country.
One thing you might notice is that I'm leaving out the ID field because if you remember from the last step, we added auto increment to it, so it's going to be created by MySQL automatically.
If I go ahead and run the query, you'll notice I get an error back that says the value used for the country is too long.
If you remember earlier, we gave it a max length of 2.
So because it has 3 characters here, it's not allowed to write that data to the database.
When we change it back to 2 characters, the query then runs successfully.
That inserted the first row into the database.
And it's also worth noting that you can insert multiple rows at the same time by separating them with commas.
So now that we have some data in the database.
The question becomes, how do we retrieve it or query it?
A select statement allows us to read data in the database and have it returned to us in a result table.
If we wanna read an entire table, we can say select star from the users table.
That will return the entire table along with every single column.
But in most cases, you are not going to want all that data.
You can filter out columns by simply separating them with commas in the like statement.
For example, we might only want the email and ID in the result set, but a table might also have millions upon millions of rows.
An easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want.
Notice how only 2 rows of return now, but what if we want them to be ordered by a certain field like a timestamp or ID?
We can achieve that by adding an order by clause that specifies column we want to order by, then ascending or descending based on the direction of ordering.
Ordering is useful, but you'll often need to filter out rows with more complex logic.
Goo.
SQL starts to feel more like a real programming language when you introduce the where clause.
For example, we may only want the rows that have a country code of US.
Goo.
A where clause allows us to run conditional logic on the query.
In this case, it will only return rows where the country equals US.
We can also chain additional conditions to it using and, or, or, like, where the country equals US and the ID is greater than 1.
But in some cases, you may not want to filter by an exact value, but rather a pattern.
Like, maybe you want all the emails that start with an h.
In which case, you can say where email like followed by a pattern.
In this case, it'll find all of the columns where the email starts with an h.
Goo we get 2 results back from that query, but if we change the pattern to hello, then we only get one result back.
So like is kind of like a basic full text search feature.
The problem with this query though is that it's going to become very slow as our dataset grows larger.
If you need to retrieve data quickly, you'll need an index, which is basically a lookup table that allows you to retrieve data faster.
A database index is just like the index in the back of a textbook.
It helps the database find important keywords without having scan the entire dataset, but it comes at a cost of slower rights and the requirement of additional memory.
In this case though, we'll definitely want an index on the email field, which we can do by creating a statement that says create index, which will give a name of email index and point it to the users table email column.
And now when making a query for the email based on a pattern, it should be much faster.
Now that we have our users table set up, we're ready to create our first relationship.
If you're a host on Airbnb, you might have multiple properties available.
So the relationship is that a user can have many rooms or a one to many relationship between users and rooms.
Let's go ahead and write a query to create another table called rooms.
It has an ID field for the room itself, but also notice how it has an owner ID field.
That represents the landlord or the ID of the user who owns this room.
The ID of the room is our primary key, then the owner ID is known as a foreign key because it references an ID in a different table.
On the next line, we can set up the primary key by pointing it to the ID field, then we can do the same thing for the foreign key.
But we also need to tell it which table and column to reference, which in this case will be the users table ID column.
When you set up a foreign key constraint like this, it tells the database not to delete anything that holds data about that relationship.
So that means it'll be impossible to delete users who have an associated room at the same time.
In other words, you're guaranteed to have data integrity.
Goo.
Let's go ahead and create the table and then we'll need to insert some data into it.
I'm using an insert into statement just like we did in the previous example.
Goal.
And notice how I'm setting the owner ID to 1.
That means that the user with an ID of 1 owns these 4 properties.
And now we're ready to make our first relational query using a good join.
In this case, we want to query the users that own rooms and include some information about each room that they own.
There are 4 different types of joins, inner, left, right and outer.
To understand the difference, you first need to understand that when you make a join, you're reading data from 2 different tables.
Guess.
Think of users as the left table and rooms as the right table.
When you perform a join, you'll be matching the user ID to the owner ID on a room.
Goo so the relationship will only exist between certain rows on each table.
The type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query.
Let's take a look at a few examples.
1st, we'll say select everything from the users table, then run an inner join for rooms on the condition where the room owner ID equals the user ID.
That gives us a result set with 4 rows in it, one for each room that has a corresponding owner.
In this case, the owner is the same for every room, but each row has also been joined with the room data.
Now in some cases, you may want to fetch all of the users even if they don't have an associated room.
You can do that with a left join.
This time, the result set has 3 more rows for the other users, but all the room data is just null.
As you might imagine, a right join is the exact opposite of guess.
It would return all the rooms that don't have an associated owner.
And, lastly, you have a full outer join, which actually isn't supported in MySQL, but it would look something like this in other guid basis.
Now one thing you'll notice in the result set is that when we have conflicting column names in the join, MySQL will automatically rename them for us, like it did with users ID and rooms ID.
You can easily change that by selecting a specific column, then use the as operator to cast it to a different name.
Let's use underscores to make the name more friendly with programming languages that use dot notation.
That's pretty cool, but there's one more big question that we need to answer with our data model, and that is how does a user book a room from another user?
We can model that relationship by creating a 3rd intermediate table called reservations or bookings.
Most importantly, a row in the bookings table will have a check-in time along with a guest ID or user ID and the room ID, both of which are foreign keys.
That allows us to create complex relationships where a user has reserved many rooms through the bookings table.
Or on the other side, a room has many past guests through the bookings table.
Once we have that data model established, we can then run joins through the bookings table.
For example, we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest ID.
Or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room ID.
And that's all you need to know about databases to build a multi $1,000,000,000 startup.
Actually, wait.
There is one last thing I forgot to show you, but you need to be careful with this one because if you use it incorrectly, you could delete your entire database and lose a lot of money.
A drop statement allows you to delete data, like an individual table or an entire database.
In fact, goo YouTube uses my SQL under the hood, so I should be able to delete this video by clicking

Speaker 0: Relational databases using SQL are king. In my recent poll, nearly 2 thirds of viewers said that SQL was their go to database, and that's despite the fact that it's 40 year old tech, with 1,000,000,000 of dollars invested in disruptive technologies that try to knock SQL go off its throne. Today, you'll learn everything you need to know about MySQL, an open source database that powers content management systems like WordPress, ecommerce platforms like Shopify, and social media guidance like Twitter. We'll take a hands on approach by recreating Airbnb's database, which happens to use MySQL in real life. In the process, you'll learn why SQL is so popular, how to install and interact with the database using modern tooling and Versus Code, the essential syntax to create, read, update, and delete data, and most importantly, how good model and join relational data.

There's also another giveaway with this video, the MySQL pillow. All you have to do is be a subscriber and leave a comment below. To start things off, let's travel back in time 50 years to meet Ted Codd, the author of the legendary paper, a relational model of data for large shared data banks. Prior to this paper, databases were not relational. They were network based or hierarchical.

In the paper, he outlines how a relational model can reduce duplication and redundancy, and it changed the way people would approach data going forward. A few years later, Donald Chamberlain and Raymond Boyce would use this paper as the basis for developing structured query language, AKA SQL or SQL. And is the language we still use today to interact with data in a wide variety of relational databases. But now, I need to let you in on a little secret, and that's the fact that most developers using relational databases aren't actually writing raw SQL code. More often, they use libraries that perform object relational mapping, which are tools that allow you to work with relational databases using the object oriented programming language of your choice.

JavaScript has SQLize, Python has Alchemy, and Ruby on Rails has ActiveRecord. ORMs make it so you never have good touch raw SQL code, and that can actually be a great thing for productivity most of the time, but learning how raw SQL works will help you understand some of the magic that ORM does under the hood, and that's what this video is all about. When I say the word relational database, I'm talking about a relational database management system, GUM, which is comprised of 2 main things. You have the database itself, which is a collection of tables, and in each table, you have your actual data organized in columns and rows, very similar to an Excel spreadsheet. The second component in the system is a language used to manipulate and read data in the database, which is most often a variation of structured query language.

In addition to MySQL, there are many popular database options out there, like Postgres, SQL Server, SQLite, and tools like CockroachDB that can easily be distributed on the cloud to scale high traffic app. Now, let's take a look at the data model for our Airbnb clone using a tool called GoodRaul SQL, which can help you visualize and model data relationships. We'll refer back to this diagram throughout the video, but all you need to know for right now is that Each one of these boxes here represents a database table, then the lines between them represent different relationships, like one to 1 or one to many. What we're at here is the database schema, which is basically a blueprint that defines the different rules or constraints within the database. If we look at a table, you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there.

There are many different data types and they can vary between different SQL databases. But for the most part, they just represent different types of numbers and strings. Like in my SQL. We have int for integer or float for floating point numbers. For small strings, we might use varchar or varchar if you prefer, or use text for longer strings.

You don't need to know all the data types at the beginning, but choosing the right one will help you optimize the size and performance of your database when you build a serious app. In addition into a data type, a column might also have a special constraint. A column may not be able to store a null value or maybe each value in that column needs to be unique. In this schema, You'll notice that every table has an ID with the primary key constraint, which means that it cannot be null and it must be unique. That ensures that every row in the table can be identified.

That's essential when building relationships because that unique ID can then be saved on another table to represent a one to 1 or one to many relationship. Like a user can have many rooms and a room can have many reviews. And we'll look at that in more detail later in the video. Now, the other thing I wanna point out in this diagram is that there is very little data duplication between the tables. Every table is a unique entity, which means that the rooms table, for example, doesn't need to store any information about the user, just reference to the user ID.

We call this a normalized data structure because every entity is organized into its smallest normal form, a concept cassette that Ted Codd laid out over half a century ago. For example, we have a bookings table that requires information about a user and a room. We could save user data and room data on that table, but that would result in a lot of data duplication. A better approach is to separate users and rooms into their own entities, goo, then join them back together with bookings when needed. A great analogy is that normalized data is like breaking down a car into all of its individual parts.

When it comes time to build a car, you just join all those parts back together. Okay. So now that we know a little bit about SQL from a high level, it's time to get hands on by first installing MySQL on our system. I use Arch, by the way, but, for this video, we'll install MySQL on Windows. If you go to the website, there's an dollar that you can download.

Go ahead and download it, and the only thing you need for this tutorial is MySQL Server. Go ahead and stick with the default settings for everything, then it will ask you to create a password. Make sure to remember the password because we'll need it later to connect to the database. Then the installer should take care of everything else to configure your system. If you're on a Mac, there's also an installer you can download that follows the same process, or you can just use Homebrew.

After the installation is done, you should then have access to the MySQL command from the command line. Now to access the database from the command line, you'll run MySQL followed by the u flag for username, and then also add flag p if you used password. We now have access to MySQL in the command line, and the first thing we'll wanna do is create a database. We can do that by executing an SQL statement that says create database followed by the database name, which in this case will be Airbnb. We can then verify that it was actually created by typing show databases.

Now we could do this entire tutorial from the command line, but that wouldn't be very developer friendly. A better option is to install the SQL Go tool's extension for Versus Code. It allows us to store our connection details, visualize the database, and view a history of all the queries that we've run. To set it up, just install the extension and driver for MySQL, and then create a new connection. You should be able to stick to the default settings.

Just make sure to add the name of the database you just created along with your username and password. Go ahead and test the connection and, hopefully, you get a success message. Otherwise, Google is your best friend. Now go ahead and click the button to connect, and we're ready to start writing some code to build out this database. The first thing I wanna point out is that you can create a comment in SQL using a double dash.

Goo. A cool thing about this Versus Code extension is that if we create a comment followed by at block, it will put a play button on top of that statement, allowing us to run it individually, so we don't to jump around to a bunch of different files. At this point, we've already created a database. Now, we just need to add the first table to it, which can be accomplished with a create table statement. Now a couple things to note here.

A statement is code that does something, and we know this is a statement because it ends with a semicolon. The words highlighted in purple are SQL keywords. They're reserved words that the language will interpret to do something. By convention, we write them in uppercase, but they're actually not case sensitive and we could write them in lowercase if we wanted to. The third word, users, is known as an identifier.

And, in this case, it just identifies the name of good table. Inside the parenthesis, we can add a list of columns that we want to include on this table. In this case, we have an ID, email, bio, and country. Those are the column identifiers or names, but they also need to have a data type and optionally, one or more constraints. Let's give the ID field an integer data type, which means we can only store whole numbers here, no decimals or strings.

The data type is important because if you try to save the wrong data to that column, the database will throw an ger because SQL is very strict about data integrity. Next, we'll add a constraint to this column called primary key. What that tells the database is that this column identifies a unique row, therefore, the ID must be unique and must not be null. Constraints are kind of like an extra layer of data validation that you can add beyond just the data type. One additional constraint that you'll often see on IDs is auto increment.

This tells the database to automatically create the ID for you. It'll start at 1, then increment to 2, 3, 4, and so on. From there, we have email, which is a string value. So we'll give it a type of varchar. It takes an argument of a number that represents the maximum length that string can be.

You'll often see it set to 255 because that's the max amount of characters that can be counted with an 8 bit number. So adding one more character would just be a waste of a bite. The email column will also have a constraint of not null. That means there has to be something added to the email field on every row. And we'll also add the unique constraint to ensure that every email is unique.

Now moving on to the bio, we need to potentially store a much larger string here. So instead of varchar, we'll use text, which can store a string value of an unspecified size. And lastly, we have the country value, which is also a string. But in this case, we know the country code will always be 2 characters, so we'll go ahead and enforce that rule on the data type. We're now ready to execute this SQL statement by pushing the play button.

You should get a success message back. Then If you open up the Versus Code extension, you should be able to see the schema for this table in the IDE. Now that we have a table, it's time to insert data or rows into it. The way we do that is with an insert into statement. We reference the users table with its identifier, then in parentheses, add the columns that we want to update.

The order of the columns is important because after that, we have values, which is a list of values that should be inserted into these columns. The first value as the email, the second is the bio, and the third is the country. One thing you might notice is that I'm leaving out the ID field because if you remember from the last step, we added auto increment to it, so it's going to be created by MySQL automatically. If I go ahead and run the query, you'll notice I get an error back that says the value used for the country is too long. If you remember earlier, we gave it a max length of 2.

So because it has 3 characters here, it's not allowed to write that data to the database. When we change it back to 2 characters, the query then runs successfully. That inserted the first row into the database. And it's also worth noting that you can insert multiple rows at the same time by separating them with commas. So now that we have some data in the database.

The question becomes, how do we retrieve it or query it? A select statement allows us to read data in the database and have it returned to us in a result table. If we wanna read an entire table, we can say select star from the users table. That will return the entire table along with every single column. But in most cases, you are not going to want all that data.

You can filter out columns by simply separating them with commas in the like statement. For example, we might only want the email and ID in the result set, but a table might also have millions upon millions of rows. An easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want. Notice how only 2 rows of return now, but what if we want them to be ordered by a certain field like a timestamp or ID? We can achieve that by adding an order by clause that specifies column we want to order by, then ascending or descending based on the direction of ordering.

Ordering is useful, but you'll often need to filter out rows with more complex logic. Goo. SQL starts to feel more like a real programming language when you introduce the where clause. For example, we may only want the rows that have a country code of US. Goo.

A where clause allows us to run conditional logic on the query. In this case, it will only return rows where the country equals US. We can also chain additional conditions to it using and, or, or, like, where the country equals US and the ID is greater than 1. But in some cases, you may not want to filter by an exact value, but rather a pattern. Like, maybe you want all the emails that start with an h.

In which case, you can say where email like followed by a pattern. In this case, it'll find all of the columns where the email starts with an h. Goo we get 2 results back from that query, but if we change the pattern to hello, then we only get one result back. So like is kind of like a basic full text search feature. The problem with this query though is that it's going to become very slow as our dataset grows larger.

If you need to retrieve data quickly, you'll need an index, which is basically a lookup table that allows you to retrieve data faster. A database index is just like the index in the back of a textbook. It helps the database find important keywords without having scan the entire dataset, but it comes at a cost of slower rights and the requirement of additional memory. In this case though, we'll definitely want an index on the email field, which we can do by creating a statement that says create index, which will give a name of email index and point it to the users table email column. And now when making a query for the email based on a pattern, it should be much faster.

Now that we have our users table set up, we're ready to create our first relationship. If you're a host on Airbnb, you might have multiple properties available. So the relationship is that a user can have many rooms or a one to many relationship between users and rooms. Let's go ahead and write a query to create another table called rooms. It has an ID field for the room itself, but also notice how it has an owner ID field.

That represents the landlord or the ID of the user who owns this room. The ID of the room is our primary key, then the owner ID is known as a foreign key because it references an ID in a different table. On the next line, we can set up the primary key by pointing it to the ID field, then we can do the same thing for the foreign key. But we also need to tell it which table and column to reference, which in this case will be the users table ID column. When you set up a foreign key constraint like this, it tells the database not to delete anything that holds data about that relationship.

So that means it'll be impossible to delete users who have an associated room at the same time. In other words, you're guaranteed to have data integrity. Goo. Let's go ahead and create the table and then we'll need to insert some data into it. I'm using an insert into statement just like we did in the previous example.

Goal. And notice how I'm setting the owner ID to 1. That means that the user with an ID of 1 owns these 4 properties. And now we're ready to make our first relational query using a good join. In this case, we want to query the users that own rooms and include some information about each room that they own.

There are 4 different types of joins, inner, left, right and outer. To understand the difference, you first need to understand that when you make a join, you're reading data from 2 different tables. Guess. Think of users as the left table and rooms as the right table. When you perform a join, you'll be matching the user ID to the owner ID on a room.

Goo so the relationship will only exist between certain rows on each table. The type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query. Let's take a look at a few examples. 1st, we'll say select everything from the users table, then run an inner join for rooms on the condition where the room owner ID equals the user ID. That gives us a result set with 4 rows in it, one for each room that has a corresponding owner.

In this case, the owner is the same for every room, but each row has also been joined with the room data. Now in some cases, you may want to fetch all of the users even if they don't have an associated room. You can do that with a left join. This time, the result set has 3 more rows for the other users, but all the room data is just null. As you might imagine, a right join is the exact opposite of guess.

It would return all the rooms that don't have an associated owner. And, lastly, you have a full outer join, which actually isn't supported in MySQL, but it would look something like this in other guid basis. Now one thing you'll notice in the result set is that when we have conflicting column names in the join, MySQL will automatically rename them for us, like it did with users ID and rooms ID. You can easily change that by selecting a specific column, then use the as operator to cast it to a different name. Let's use underscores to make the name more friendly with programming languages that use dot notation.

That's pretty cool, but there's one more big question that we need to answer with our data model, and that is how does a user book a room from another user? We can model that relationship by creating a 3rd intermediate table called reservations or bookings. Most importantly, a row in the bookings table will have a check-in time along with a guest ID or user ID and the room ID, both of which are foreign keys. That allows us to create complex relationships where a user has reserved many rooms through the bookings table. Or on the other side, a room has many past guests through the bookings table.

Once we have that data model established, we can then run joins through the bookings table. For example, we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest ID. Or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room ID. And that's all you need to know about databases to build a multi $1,000,000,000 startup. Actually, wait.

There is one last thing I forgot to show you, but you need to be careful with this one because if you use it incorrectly, you could delete your entire database and lose a lot of money. A drop statement allows you to delete data, like an individual table or an entire database. In fact, goo YouTube uses my SQL under the hood, so I should be able to delete this video by clicking
Relational databases using SQL are king. In my recent poll, nearly 2 thirds of viewers said that SQL was their go to database, and that's despite the fact that it's 40 year old tech, with 1,000,000,000 of dollars invested in disruptive technologies that try to knock SQL go off its throne. Today, you'll learn everything you need to know about MySQL, an open source database that powers content management systems like WordPress, ecommerce platforms like Shopify, and social media guidance like Twitter. We'll take a hands on approach by recreating Airbnb's database, which happens to use MySQL in real life. In the process, you'll learn why SQL is so popular, how to install and interact with the database using modern tooling and Versus Code, the essential syntax to create, read, update, and delete data, and most importantly, how good model and join relational data.
databases
data management
There's also another giveaway with this video, the MySQL pillow. All you have to do is be a subscriber and leave a comment below. To start things off, let's travel back in time 50 years to meet Ted Codd, the author of the legendary paper, a relational model of data for large shared data banks.
Prior to this paper, databases were not relational. They were network based or hierarchical. In the paper, he outlines how a relational model can reduce duplication and redundancy, and it changed the way people would approach data going forward. A few years later, Donald Chamberlain and Raymond Boyce would use this paper as the basis for developing structured query language, AKA SQL or SQL. And is the language we still use today to interact with data in a wide variety of relational databases. But now, I need to let you in on a little secret, and that's the fact that most developers using relational databases aren't actually writing raw SQL code. More often, they use libraries that perform object relational mapping, which are tools that allow you to work with relational databases using the object oriented programming language of your choice. JavaScript has SQLize, Python has Alchemy, and Ruby on Rails has ActiveRecord. ORMs make it so you never have good touch raw SQL code, and that can actually be a great thing for productivity most of the time, but learning how raw SQL works will help you understand some of the magic that ORM does under the hood, and that's what this video is all about. When I say the word relational database, I'm talking about a relational database management system, GUM, which is comprised of 2 main things. You have the database itself, which is a collection of tables, and in each table, you have your actual data organized in columns and rows, very similar to an Excel spreadsheet. The second component in the system is a language used to manipulate and read data in the database, which is most often a variation of structured query language. In addition to MySQL, there are many popular database options out there, like Postgres, SQL Server, SQLite, and tools like CockroachDB that can easily be distributed on the cloud to scale high traffic app. Now, let's take a look at the data model for our Airbnb clone using a tool called GoodRaul SQL, which can help you visualize and model data relationships. We'll refer back to this diagram throughout the video, but all you need to know for right now is that Each one of these boxes here represents a database table, then the lines between them represent different relationships, like one to 1 or one to many. What we're at here is the database schema, which is basically a blueprint that defines the different rules or constraints within the database. If we look at a table, you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there. There are many different data types and they can vary between different SQL databases. But for the most part, they just represent different types of numbers and strings. Like in my SQL. We have int for integer or float for floating point numbers. For small strings, we might use varchar or varchar if you prefer, or use text for longer strings. You don't need to know all the data types at the beginning, but choosing the right one will help you optimize the size and performance of your database when you build a serious app. In addition into a data type, a column might also have a special constraint. A column may not be able to store a null value or maybe each value in that column needs to be unique. In this schema, You'll notice that every table has an ID with the primary key constraint, which means that it cannot be null and it must be unique. That ensures that every row in the table can be identified. That's essential when building relationships because that unique ID can then be saved on another table to represent a one to 1 or one to many relationship. Like a user can have many rooms and a room can have many reviews. And we'll look at that in more detail later in the video. Now, the other thing I wanna point out in this diagram is that there is very little data duplication between the tables. Every table is a unique entity, which means that the rooms table, for example, doesn't need to store any information about the user, just reference to the user ID. We call this a normalized data structure because every entity is organized into its smallest normal form, a concept cassette that Ted Codd laid out over half a century ago. For example, we have a bookings table that requires information about a user and a room. We could save user data and room data on that table, but that would result in a lot of data duplication. A better approach is to separate users and rooms into their own entities, goo, then join them back together with bookings when needed.
databases
data analysis
data management
data
relationships
modeling
programming
A great analogy is that normalized data is like breaking down a car into all of its individual parts.
When it comes time to build a car, you just join all those parts back together. Okay. So now that we know a little bit about SQL from a high level, it's time to get hands on by first installing MySQL on our system. I use Arch, by the way, but, for this video, we'll install MySQL on Windows.
databases
If you go to the website, there's an dollar that you can download.
Go ahead and download it, and the only thing you need for this tutorial is MySQL Server. Go ahead and stick with the default settings for everything, then it will ask you to create a password. Make sure to remember the password because we'll need it later to connect to the database.
databases
Then the installer should take care of everything else to configure your system. If you're on a Mac, there's also an installer you can download that follows the same process, or you can just use Homebrew. After the installation is done, you should then have access to the MySQL command from the command line. Now to access the database from the command line, you'll run MySQL followed by the u flag for username, and then also add flag p if you used password.
We now have access to MySQL in the command line, and the first thing we'll wanna do is create a database. We can do that by executing an SQL statement that says create database followed by the database name, which in this case will be Airbnb. We can then verify that it was actually created by typing show databases. Now we could do this entire tutorial from the command line, but that wouldn't be very developer friendly. A better option is to install the SQL Go tool's extension for Versus Code. It allows us to store our connection details, visualize the database, and view a history of all the queries that we've run. To set it up, just install the extension and driver for MySQL, and then create a new connection.
databases
microsoft
You should be able to stick to the default settings.
Just make sure to add the name of the database you just created along with your username and password. Go ahead and test the connection and, hopefully, you get a success message. Otherwise, Google is your best friend. Now go ahead and click the button to connect, and we're ready to start writing some code to build out this database. The first thing I wanna point out is that you can create a comment in SQL using a double dash. Goo. A cool thing about this Versus Code extension is that if we create a comment followed by at block, it will put a play button on top of that statement, allowing us to run it individually, so we don't to jump around to a bunch of different files.
databases
google
At this point, we've already created a database. Now, we just need to add the first table to it, which can be accomplished with a create table statement. Now a couple things to note here. A statement is code that does something, and we know this is a statement because it ends with a semicolon.
databases
The words highlighted in purple are SQL keywords. They're reserved words that the language will interpret to do something. By convention, we write them in uppercase, but they're actually not case sensitive and we could write them in lowercase if we wanted to. The third word, users, is known as an identifier. And, in this case, it just identifies the name of good table. Inside the parenthesis, we can add a list of columns that we want to include on this table.
In this case, we have an ID, email, bio, and country. Those are the column identifiers or names, but they also need to have a data type and optionally, one or more constraints. Let's give the ID field an integer data type, which means we can only store whole numbers here, no decimals or strings. The data type is important because if you try to save the wrong data to that column, the database will throw an ger because SQL is very strict about data integrity. Next, we'll add a constraint to this column called primary key. What that tells the database is that this column identifies a unique row, therefore, the ID must be unique and must not be null. Constraints are kind of like an extra layer of data validation that you can add beyond just the data type. One additional constraint that you'll often see on IDs is auto increment. This tells the database to automatically create the ID for you. It'll start at 1, then increment to 2, 3, 4, and so on. From there, we have email, which is a string value. So we'll give it a type of varchar. It takes an argument of a number that represents the maximum length that string can be.
data
databases
data management
programming
data analysis
You'll often see it set to 255 because that's the max amount of characters that can be counted with an 8 bit number.
So adding one more character would just be a waste of a bite. The email column will also have a constraint of not null. That means there has to be something added to the email field on every row. And we'll also add the unique constraint to ensure that every email is unique.
email
Now moving on to the bio, we need to potentially store a much larger string here. So instead of varchar, we'll use text, which can store a string value of an unspecified size. And lastly, we have the country value, which is also a string.
But in this case, we know the country code will always be 2 characters, so we'll go ahead and enforce that rule on the data type. We're now ready to execute this SQL statement by pushing the play button. You should get a success message back. Then If you open up the Versus Code extension, you should be able to see the schema for this table in the IDE. Now that we have a table, it's time to insert data or rows into it. The way we do that is with an insert into statement. We reference the users table with its identifier, then in parentheses, add the columns that we want to update. The order of the columns is important because after that, we have values, which is a list of values that should be inserted into these columns.
data management
databases
The first value as the email, the second is the bio, and the third is the country. One thing you might notice is that I'm leaving out the ID field because if you remember from the last step, we added auto increment to it, so it's going to be created by MySQL automatically.
email
If I go ahead and run the query, you'll notice I get an error back that says the value used for the country is too long.
If you remember earlier, we gave it a max length of 2. So because it has 3 characters here, it's not allowed to write that data to the database. When we change it back to 2 characters, the query then runs successfully. That inserted the first row into the database. And it's also worth noting that you can insert multiple rows at the same time by separating them with commas. So now that we have some data in the database. The question becomes, how do we retrieve it or query it? A select statement allows us to read data in the database and have it returned to us in a result table. If we wanna read an entire table, we can say select star from the users table.
databases
data
data management
That will return the entire table along with every single column.
But in most cases, you are not going to want all that data. You can filter out columns by simply separating them with commas in the like statement. For example, we might only want the email and ID in the result set, but a table might also have millions upon millions of rows.
databases
data
data analysis
An easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want. Notice how only 2 rows of return now, but what if we want them to be ordered by a certain field like a timestamp or ID? We can achieve that by adding an order by clause that specifies column we want to order by, then ascending or descending based on the direction of ordering.
Ordering is useful, but you'll often need to filter out rows with more complex logic. Goo. SQL starts to feel more like a real programming language when you introduce the where clause. For example, we may only want the rows that have a country code of US. Goo. A where clause allows us to run conditional logic on the query. In this case, it will only return rows where the country equals US. We can also chain additional conditions to it using and, or, or, like, where the country equals US and the ID is greater than 1. But in some cases, you may not want to filter by an exact value, but rather a pattern. Like, maybe you want all the emails that start with an h. In which case, you can say where email like followed by a pattern. In this case, it'll find all of the columns where the email starts with an h. Goo we get 2 results back from that query, but if we change the pattern to hello, then we only get one result back.
databases
email
united states of america
logic
So like is kind of like a basic full text search feature.
The problem with this query though is that it's going to become very slow as our dataset grows larger. If you need to retrieve data quickly, you'll need an index, which is basically a lookup table that allows you to retrieve data faster. A database index is just like the index in the back of a textbook. It helps the database find important keywords without having scan the entire dataset, but it comes at a cost of slower rights and the requirement of additional memory. In this case though, we'll definitely want an index on the email field, which we can do by creating a statement that says create index, which will give a name of email index and point it to the users table email column. And now when making a query for the email based on a pattern, it should be much faster. Now that we have our users table set up, we're ready to create our first relationship. If you're a host on Airbnb, you might have multiple properties available. So the relationship is that a user can have many rooms or a one to many relationship between users and rooms.
databases
email
data management
networking
data
Let's go ahead and write a query to create another table called rooms. It has an ID field for the room itself, but also notice how it has an owner ID field. That represents the landlord or the ID of the user who owns this room. The ID of the room is our primary key, then the owner ID is known as a foreign key because it references an ID in a different table. On the next line, we can set up the primary key by pointing it to the ID field, then we can do the same thing for the foreign key.
But we also need to tell it which table and column to reference, which in this case will be the users table ID column. When you set up a foreign key constraint like this, it tells the database not to delete anything that holds data about that relationship. So that means it'll be impossible to delete users who have an associated room at the same time. In other words, you're guaranteed to have data integrity. Goo. Let's go ahead and create the table and then we'll need to insert some data into it. I'm using an insert into statement just like we did in the previous example. Goal. And notice how I'm setting the owner ID to 1. That means that the user with an ID of 1 owns these 4 properties.
data management
databases
And now we're ready to make our first relational query using a good join. In this case, we want to query the users that own rooms and include some information about each room that they own. There are 4 different types of joins, inner, left, right and outer. To understand the difference, you first need to understand that when you make a join, you're reading data from 2 different tables.
databases
Guess. Think of users as the left table and rooms as the right table. When you perform a join, you'll be matching the user ID to the owner ID on a room.
Goo so the relationship will only exist between certain rows on each table. The type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query. Let's take a look at a few examples. 1st, we'll say select everything from the users table, then run an inner join for rooms on the condition where the room owner ID equals the user ID.
databases
That gives us a result set with 4 rows in it, one for each room that has a corresponding owner. In this case, the owner is the same for every room, but each row has also been joined with the room data. Now in some cases, you may want to fetch all of the users even if they don't have an associated room. You can do that with a left join. This time, the result set has 3 more rows for the other users, but all the room data is just null. As you might imagine, a right join is the exact opposite of guess. It would return all the rooms that don't have an associated owner. And, lastly, you have a full outer join, which actually isn't supported in MySQL, but it would look something like this in other guid basis. Now one thing you'll notice in the result set is that when we have conflicting column names in the join, MySQL will automatically rename them for us, like it did with users ID and rooms ID.
You can easily change that by selecting a specific column, then use the as operator to cast it to a different name. Let's use underscores to make the name more friendly with programming languages that use dot notation. That's pretty cool, but there's one more big question that we need to answer with our data model, and that is how does a user book a room from another user? We can model that relationship by creating a 3rd intermediate table called reservations or bookings. Most importantly, a row in the bookings table will have a check-in time along with a guest ID or user ID and the room ID, both of which are foreign keys. That allows us to create complex relationships where a user has reserved many rooms through the bookings table.
data
customers
programming
Or on the other side, a room has many past guests through the bookings table.
Once we have that data model established, we can then run joins through the bookings table. For example, we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest ID. Or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room ID. And that's all you need to know about databases to build a multi $1,000,000,000 startup. Actually, wait. There is one last thing I forgot to show you, but you need to be careful with this one because if you use it incorrectly, you could delete your entire database and lose a lot of money.
databases
data management
A drop statement allows you to delete data, like an individual table or an entire database. In fact, goo YouTube uses my SQL under the hood, so I should be able to delete this video by clicking
Relational databases using SQL are king. In my recent poll, nearly 2 thirds of viewers said that SQL was their go to database, and that's despite the fact that it's 40 year old tech, with 1,000,000,000 of dollars invested in disruptive technologies that try to knock SQL go off its throne. Today, you'll learn everything you need to know about MySQL, an open source database that powers content management systems like WordPress, ecommerce platforms like Shopify, and social media guidance like Twitter. We'll take a hands on approach by recreating Airbnb's database, which happens to use MySQL in real life. In the process, you'll learn why SQL is so popular, how to install and interact with the database using modern tooling and Versus Code, the essential syntax to create, read, update, and delete data, and most importantly, how good model and join relational data. There's also another giveaway with this video, the MySQL pillow. All you have to do is be a subscriber and leave a comment below. To start things off, let's travel back in time 50 years to meet Ted Codd, the author of the legendary paper, a relational model of data for large shared data banks. Prior to this paper, databases were not relational. They were network based or hierarchical. In the paper, he outlines how a relational model can reduce duplication and redundancy, and it changed the way people would approach data going forward. A few years later, Donald Chamberlain and Raymond Boyce would use this paper as the basis for developing structured query language, AKA SQL or SQL. And is the language we still use today to interact with data in a wide variety of relational databases. But now, I need to let you in on a little secret, and that's the fact that most developers using relational databases aren't actually writing raw SQL code. More often, they use libraries that perform object relational mapping, which are tools that allow you to work with relational databases using the object oriented programming language of your choice. JavaScript has SQLize, Python has Alchemy, and Ruby on Rails has ActiveRecord. ORMs make it so you never have good touch raw SQL code, and that can actually be a great thing for productivity most of the time, but learning how raw SQL works will help you understand some of the magic that ORM does under the hood, and that's what this video is all about. When I say the word relational database, I'm talking about a relational database management system, GUM, which is comprised of 2 main things. You have the database itself, which is a collection of tables, and in each table, you have your actual data organized in columns and rows, very similar to an Excel spreadsheet. The second component in the system is a language used to manipulate and read data in the database, which is most often a variation of structured query language. In addition to MySQL, there are many popular database options out there, like Postgres, SQL Server, SQLite, and tools like CockroachDB that can easily be distributed on the cloud to scale high traffic app. Now, let's take a look at the data model for our Airbnb clone using a tool called GoodRaul SQL, which can help you visualize and model data relationships. We'll refer back to this diagram throughout the video, but all you need to know for right now is that Each one of these boxes here represents a database table, then the lines between them represent different relationships, like one to 1 or one to many. What we're at here is the database schema, which is basically a blueprint that defines the different rules or constraints within the database. If we look at a table, you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there. There are many different data types and they can vary between different SQL databases. But for the most part, they just represent different types of numbers and strings. Like in my SQL. We have int for integer or float for floating point numbers. For small strings, we might use varchar or varchar if you prefer, or use text for longer strings. You don't need to know all the data types at the beginning, but choosing the right one will help you optimize the size and performance of your database when you build a serious app. In addition into a data type, a column might also have a special constraint. A column may not be able to store a null value or maybe each value in that column needs to be unique. In this schema, You'll notice that every table has an ID with the primary key constraint, which means that it cannot be null and it must be unique. That ensures that every row in the table can be identified. That's essential when building relationships because that unique ID can then be saved on another table to represent a one to 1 or one to many relationship. Like a user can have many rooms and a room can have many reviews. And we'll look at that in more detail later in the video. Now, the other thing I wanna point out in this diagram is that there is very little data duplication between the tables. Every table is a unique entity, which means that the rooms table, for example, doesn't need to store any information about the user, just reference to the user ID. We call this a normalized data structure because every entity is organized into its smallest normal form, a concept cassette that Ted Codd laid out over half a century ago. For example, we have a bookings table that requires information about a user and a room. We could save user data and room data on that table, but that would result in a lot of data duplication. A better approach is to separate users and rooms into their own entities, goo, then join them back together with bookings when needed. A great analogy is that normalized data is like breaking down a car into all of its individual parts. When it comes time to build a car, you just join all those parts back together. Okay. So now that we know a little bit about SQL from a high level, it's time to get hands on by first installing MySQL on our system. I use Arch, by the way, but, for this video, we'll install MySQL on Windows. If you go to the website, there's an dollar that you can download. Go ahead and download it, and the only thing you need for this tutorial is MySQL Server. Go ahead and stick with the default settings for everything, then it will ask you to create a password. Make sure to remember the password because we'll need it later to connect to the database. Then the installer should take care of everything else to configure your system. If you're on a Mac, there's also an installer you can download that follows the same process, or you can just use Homebrew. After the installation is done, you should then have access to the MySQL command from the command line. Now to access the database from the command line, you'll run MySQL followed by the u flag for username, and then also add flag p if you used password. We now have access to MySQL in the command line, and the first thing we'll wanna do is create a database. We can do that by executing an SQL statement that says create database followed by the database name, which in this case will be Airbnb. We can then verify that it was actually created by typing show databases. Now we could do this entire tutorial from the command line, but that wouldn't be very developer friendly. A better option is to install the SQL Go tool's extension for Versus Code. It allows us to store our connection details, visualize the database, and view a history of all the queries that we've run. To set it up, just install the extension and driver for MySQL, and then create a new connection. You should be able to stick to the default settings. Just make sure to add the name of the database you just created along with your username and password. Go ahead and test the connection and, hopefully, you get a success message. Otherwise, Google is your best friend. Now go ahead and click the button to connect, and we're ready to start writing some code to build out this database. The first thing I wanna point out is that you can create a comment in SQL using a double dash. Goo. A cool thing about this Versus Code extension is that if we create a comment followed by at block, it will put a play button on top of that statement, allowing us to run it individually, so we don't to jump around to a bunch of different files. At this point, we've already created a database. Now, we just need to add the first table to it, which can be accomplished with a create table statement. Now a couple things to note here. A statement is code that does something, and we know this is a statement because it ends with a semicolon. The words highlighted in purple are SQL keywords. They're reserved words that the language will interpret to do something. By convention, we write them in uppercase, but they're actually not case sensitive and we could write them in lowercase if we wanted to. The third word, users, is known as an identifier. And, in this case, it just identifies the name of good table. Inside the parenthesis, we can add a list of columns that we want to include on this table. In this case, we have an ID, email, bio, and country. Those are the column identifiers or names, but they also need to have a data type and optionally, one or more constraints. Let's give the ID field an integer data type, which means we can only store whole numbers here, no decimals or strings. The data type is important because if you try to save the wrong data to that column, the database will throw an ger because SQL is very strict about data integrity. Next, we'll add a constraint to this column called primary key. What that tells the database is that this column identifies a unique row, therefore, the ID must be unique and must not be null. Constraints are kind of like an extra layer of data validation that you can add beyond just the data type. One additional constraint that you'll often see on IDs is auto increment. This tells the database to automatically create the ID for you. It'll start at 1, then increment to 2, 3, 4, and so on. From there, we have email, which is a string value. So we'll give it a type of varchar. It takes an argument of a number that represents the maximum length that string can be. You'll often see it set to 255 because that's the max amount of characters that can be counted with an 8 bit number. So adding one more character would just be a waste of a bite. The email column will also have a constraint of not null. That means there has to be something added to the email field on every row. And we'll also add the unique constraint to ensure that every email is unique. Now moving on to the bio, we need to potentially store a much larger string here. So instead of varchar, we'll use text, which can store a string value of an unspecified size. And lastly, we have the country value, which is also a string. But in this case, we know the country code will always be 2 characters, so we'll go ahead and enforce that rule on the data type. We're now ready to execute this SQL statement by pushing the play button. You should get a success message back. Then If you open up the Versus Code extension, you should be able to see the schema for this table in the IDE. Now that we have a table, it's time to insert data or rows into it. The way we do that is with an insert into statement. We reference the users table with its identifier, then in parentheses, add the columns that we want to update. The order of the columns is important because after that, we have values, which is a list of values that should be inserted into these columns. The first value as the email, the second is the bio, and the third is the country. One thing you might notice is that I'm leaving out the ID field because if you remember from the last step, we added auto increment to it, so it's going to be created by MySQL automatically. If I go ahead and run the query, you'll notice I get an error back that says the value used for the country is too long. If you remember earlier, we gave it a max length of 2. So because it has 3 characters here, it's not allowed to write that data to the database. When we change it back to 2 characters, the query then runs successfully. That inserted the first row into the database. And it's also worth noting that you can insert multiple rows at the same time by separating them with commas. So now that we have some data in the database. The question becomes, how do we retrieve it or query it? A select statement allows us to read data in the database and have it returned to us in a result table. If we wanna read an entire table, we can say select star from the users table. That will return the entire table along with every single column. But in most cases, you are not going to want all that data. You can filter out columns by simply separating them with commas in the like statement. For example, we might only want the email and ID in the result set, but a table might also have millions upon millions of rows. An easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want. Notice how only 2 rows of return now, but what if we want them to be ordered by a certain field like a timestamp or ID? We can achieve that by adding an order by clause that specifies column we want to order by, then ascending or descending based on the direction of ordering. Ordering is useful, but you'll often need to filter out rows with more complex logic. Goo. SQL starts to feel more like a real programming language when you introduce the where clause. For example, we may only want the rows that have a country code of US. Goo. A where clause allows us to run conditional logic on the query. In this case, it will only return rows where the country equals US. We can also chain additional conditions to it using and, or, or, like, where the country equals US and the ID is greater than 1. But in some cases, you may not want to filter by an exact value, but rather a pattern. Like, maybe you want all the emails that start with an h. In which case, you can say where email like followed by a pattern. In this case, it'll find all of the columns where the email starts with an h. Goo we get 2 results back from that query, but if we change the pattern to hello, then we only get one result back. So like is kind of like a basic full text search feature. The problem with this query though is that it's going to become very slow as our dataset grows larger. If you need to retrieve data quickly, you'll need an index, which is basically a lookup table that allows you to retrieve data faster. A database index is just like the index in the back of a textbook. It helps the database find important keywords without having scan the entire dataset, but it comes at a cost of slower rights and the requirement of additional memory. In this case though, we'll definitely want an index on the email field, which we can do by creating a statement that says create index, which will give a name of email index and point it to the users table email column. And now when making a query for the email based on a pattern, it should be much faster. Now that we have our users table set up, we're ready to create our first relationship. If you're a host on Airbnb, you might have multiple properties available. So the relationship is that a user can have many rooms or a one to many relationship between users and rooms. Let's go ahead and write a query to create another table called rooms. It has an ID field for the room itself, but also notice how it has an owner ID field. That represents the landlord or the ID of the user who owns this room. The ID of the room is our primary key, then the owner ID is known as a foreign key because it references an ID in a different table. On the next line, we can set up the primary key by pointing it to the ID field, then we can do the same thing for the foreign key. But we also need to tell it which table and column to reference, which in this case will be the users table ID column. When you set up a foreign key constraint like this, it tells the database not to delete anything that holds data about that relationship. So that means it'll be impossible to delete users who have an associated room at the same time. In other words, you're guaranteed to have data integrity. Goo. Let's go ahead and create the table and then we'll need to insert some data into it. I'm using an insert into statement just like we did in the previous example. Goal. And notice how I'm setting the owner ID to 1. That means that the user with an ID of 1 owns these 4 properties. And now we're ready to make our first relational query using a good join. In this case, we want to query the users that own rooms and include some information about each room that they own. There are 4 different types of joins, inner, left, right and outer. To understand the difference, you first need to understand that when you make a join, you're reading data from 2 different tables. Guess. Think of users as the left table and rooms as the right table. When you perform a join, you'll be matching the user ID to the owner ID on a room. Goo so the relationship will only exist between certain rows on each table. The type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query. Let's take a look at a few examples. 1st, we'll say select everything from the users table, then run an inner join for rooms on the condition where the room owner ID equals the user ID. That gives us a result set with 4 rows in it, one for each room that has a corresponding owner. In this case, the owner is the same for every room, but each row has also been joined with the room data. Now in some cases, you may want to fetch all of the users even if they don't have an associated room. You can do that with a left join. This time, the result set has 3 more rows for the other users, but all the room data is just null. As you might imagine, a right join is the exact opposite of guess. It would return all the rooms that don't have an associated owner. And, lastly, you have a full outer join, which actually isn't supported in MySQL, but it would look something like this in other guid basis. Now one thing you'll notice in the result set is that when we have conflicting column names in the join, MySQL will automatically rename them for us, like it did with users ID and rooms ID. You can easily change that by selecting a specific column, then use the as operator to cast it to a different name. Let's use underscores to make the name more friendly with programming languages that use dot notation. That's pretty cool, but there's one more big question that we need to answer with our data model, and that is how does a user book a room from another user? We can model that relationship by creating a 3rd intermediate table called reservations or bookings. Most importantly, a row in the bookings table will have a check-in time along with a guest ID or user ID and the room ID, both of which are foreign keys. That allows us to create complex relationships where a user has reserved many rooms through the bookings table. Or on the other side, a room has many past guests through the bookings table. Once we have that data model established, we can then run joins through the bookings table. For example, we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest ID. Or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room ID. And that's all you need to know about databases to build a multi $1,000,000,000 startup. Actually, wait. There is one last thing I forgot to show you, but you need to be careful with this one because if you use it incorrectly, you could delete your entire database and lose a lot of money. A drop statement allows you to delete data, like an individual table or an entire database. In fact, goo YouTube uses my SQL under the hood, so I should be able to delete this video by clicking
Relational
relational
databases
databases
using
using
SQL
sql
are
are
king.
king
In
in
my
my
recent
recent
poll,
poll
nearly
nearly
2
2
thirds
thirds
of
of
viewers
viewers
said
said
that
that
SQL
sql
was
was
their
their
go
go
to
to
database,
database
and
and
that's
that's
despite
despite
the
the
fact
fact
that
that
it's
it's
40
40
year
year
old
old
tech,
tech
with
with
1,000,000,000
1,000,000,000
of
of
dollars
dollars
invested
invested
in
in
disruptive
disruptive
technologies
technologies
that
that
try
try
to
to
knock
knock
SQL
sql
go
go
off
off
its
its
throne.
throne
Today,
today
you'll
you'll
learn
learn
everything
everything
you
you
need
need
to
to
know
know
about
about
MySQL,
mysql
an
an
open
open
source
source
database
database
that
that
powers
powers
content
content
management
management
systems
systems
like
like
WordPress,
wordpress
ecommerce
ecommerce
platforms
platforms
like
like
Shopify,
shopify
and
and
social
social
media
media
guidance
guidance
like
like
Twitter.
twitter
We'll
we'll
take
take
a
a
hands
hands
on
on
approach
approach
by
by
recreating
recreating
Airbnb's
airbnb's
database,
database
which
which
happens
happens
to
to
use
use
MySQL
mysql
in
in
real
real
life.
life
In
in
the
the
process,
process
you'll
you'll
learn
learn
why
why
SQL
sql
is
is
so
so
popular,
popular
how
how
to
to
install
install
and
and
interact
interact
with
with
the
the
database
database
using
using
modern
modern
tooling
tooling
and
and
Versus
versus
Code,
code
the
the
essential
essential
syntax
syntax
to
to
create,
create
read,
read
update,
update
and
and
delete
delete
data,
data
and
and
most
most
importantly,
importantly
how
how
good
good
model
model
and
and
join
join
relational
relational
data.
data
There's
there's
also
also
another
another
giveaway
giveaway
with
with
this
this
video,
video
the
the
MySQL
mysql
pillow.
pillow
All
all
you
you
have
have
to
to
do
do
is
is
be
be
a
a
subscriber
subscriber
and
and
leave
leave
a
a
comment
comment
below.
below
To
to
start
start
things
things
off,
off
let's
let's
travel
travel
back
back
in
in
time
time
50
50
years
years
to
to
meet
meet
Ted
ted
Codd,
codd
the
the
author
author
of
of
the
the
legendary
legendary
paper,
paper
a
a
relational
relational
model
model
of
of
data
data
for
for
large
large
shared
shared
data
data
banks.
banks
Prior
prior
to
to
this
this
paper,
paper
databases
databases
were
were
not
not
relational.
relational
They
they
were
were
network
network
based
based
or
or
hierarchical.
hierarchical
In
in
the
the
paper,
paper
he
he
outlines
outlines
how
how
a
a
relational
relational
model
model
can
can
reduce
reduce
duplication
duplication
and
and
redundancy,
redundancy
and
and
it
it
changed
changed
the
the
way
way
people
people
would
would
approach
approach
data
data
going
going
forward.
forward
A
a
few
few
years
years
later,
later
Donald
donald
Chamberlain
chamberlain
and
and
Raymond
raymond
Boyce
boyce
would
would
use
use
this
this
paper
paper
as
as
the
the
basis
basis
for
for
developing
developing
structured
structured
query
query
language,
language
AKA
aka
SQL
sql
or
or
SQL.
sql
And
and
is
is
the
the
language
language
we
we
still
still
use
use
today
today
to
to
interact
interact
with
with
data
data
in
in
a
a
wide
wide
variety
variety
of
of
relational
relational
databases.
databases
But
but
now,
now
I
i
need
need
to
to
let
let
you
you
in
in
on
on
a
a
little
little
secret,
secret
and
and
that's
that's
the
the
fact
fact
that
that
most
most
developers
developers
using
using
relational
relational
databases
databases
aren't
aren't
actually
actually
writing
writing
raw
raw
SQL
sql
code.
code
More
more
often,
often
they
they
use
use
libraries
libraries
that
that
perform
perform
object
object
relational
relational
mapping,
mapping
which
which
are
are
tools
tools
that
that
allow
allow
you
you
to
to
work
work
with
with
relational
relational
databases
databases
using
using
the
the
object
object
oriented
oriented
programming
programming
language
language
of
of
your
your
choice.
choice
JavaScript
javascript
has
has
SQLize,
sqlize
Python
python
has
has
Alchemy,
alchemy
and
and
Ruby
ruby
on
on
Rails
rails
has
has
ActiveRecord.
activerecord
ORMs
orms
make
make
it
it
so
so
you
you
never
never
have
have
good
good
touch
touch
raw
raw
SQL
sql
code,
code
and
and
that
that
can
can
actually
actually
be
be
a
a
great
great
thing
thing
for
for
productivity
productivity
most
most
of
of
the
the
time,
time
but
but
learning
learning
how
how
raw
raw
SQL
sql
works
works
will
will
help
help
you
you
understand
understand
some
some
of
of
the
the
magic
magic
that
that
ORM
orm
does
does
under
under
the
the
hood,
hood
and
and
that's
that's
what
what
this
this
video
video
is
is
all
all
about.
about
When
when
I
i
say
say
the
the
word
word
relational
relational
database,
database
I'm
i'm
talking
talking
about
about
a
a
relational
relational
database
database
management
management
system,
system
GUM,
gum
which
which
is
is
comprised
comprised
of
of
2
2
main
main
things.
things
You
you
have
have
the
the
database
database
itself,
itself
which
which
is
is
a
a
collection
collection
of
of
tables,
tables
and
and
in
in
each
each
table,
table
you
you
have
have
your
your
actual
actual
data
data
organized
organized
in
in
columns
columns
and
and
rows,
rows
very
very
similar
similar
to
to
an
an
Excel
excel
spreadsheet.
spreadsheet
The
the
second
second
component
component
in
in
the
the
system
system
is
is
a
a
language
language
used
used
to
to
manipulate
manipulate
and
and
read
read
data
data
in
in
the
the
database,
database
which
which
is
is
most
most
often
often
a
a
variation
variation
of
of
structured
structured
query
query
language.
language
In
in
addition
addition
to
to
MySQL,
mysql
there
there
are
are
many
many
popular
popular
database
database
options
options
out
out
there,
there
like
like
Postgres,
postgres
SQL
sql
Server,
server
SQLite,
sqlite
and
and
tools
tools
like
like
CockroachDB
cockroachdb
that
that
can
can
easily
easily
be
be
distributed
distributed
on
on
the
the
cloud
cloud
to
to
scale
scale
high
high
traffic
traffic
app.
app
Now,
now
let's
let's
take
take
a
a
look
look
at
at
the
the
data
data
model
model
for
for
our
our
Airbnb
airbnb
clone
clone
using
using
a
a
tool
tool
called
called
GoodRaul
goodraul
SQL,
sql
which
which
can
can
help
help
you
you
visualize
visualize
and
and
model
model
data
data
relationships.
relationships
We'll
we'll
refer
refer
back
back
to
to
this
this
diagram
diagram
throughout
throughout
the
the
video,
video
but
but
all
all
you
you
need
need
to
to
know
know
for
for
right
right
now
now
is
is
that
that
Each
each
one
one
of
of
these
these
boxes
boxes
here
here
represents
represents
a
a
database
database
table,
table
then
then
the
the
lines
lines
between
between
them
them
represent
represent
different
different
relationships,
relationships
like
like
one
one
to
to
1
1
or
or
one
one
to
to
many.
many
What
what
we're
we're
at
at
here
here
is
is
the
the
database
database
schema,
schema
which
which
is
is
basically
basically
a
a
blueprint
blueprint
that
that
defines
defines
the
the
different
different
rules
rules
or
or
constraints
constraints
within
within
the
the
database.
database
If
if
we
we
look
look
at
at
a
a
table,
table
you'll
you'll
notice
notice
that
that
it's
it's
a
a
dictionary
dictionary
where
where
the
the
key
key
represents
represents
a
a
column
column
in
in
the
the
database
database
and
and
the
the
value
value
represents
represents
a
a
data
data
type
type
that
that
can
can
be
be
stored
stored
there.
there
There
there
are
are
many
many
different
different
data
data
types
types
and
and
they
they
can
can
vary
vary
between
between
different
different
SQL
sql
databases.
databases
But
but
for
for
the
the
most
most
part,
part
they
they
just
just
represent
represent
different
different
types
types
of
of
numbers
numbers
and
and
strings.
strings
Like
like
in
in
my
my
SQL.
sql
We
we
have
have
int
int
for
for
integer
integer
or
or
float
float
for
for
floating
floating
point
point
numbers.
numbers
For
for
small
small
strings,
strings
we
we
might
might
use
use
varchar
varchar
or
or
varchar
varchar
if
if
you
you
prefer,
prefer
or
or
use
use
text
text
for
for
longer
longer
strings.
strings
You
you
don't
don't
need
need
to
to
know
know
all
all
the
the
data
data
types
types
at
at
the
the
beginning,
beginning
but
but
choosing
choosing
the
the
right
right
one
one
will
will
help
help
you
you
optimize
optimize
the
the
size
size
and
and
performance
performance
of
of
your
your
database
database
when
when
you
you
build
build
a
a
serious
serious
app.
app
In
in
addition
addition
into
into
a
a
data
data
type,
type
a
a
column
column
might
might
also
also
have
have
a
a
special
special
constraint.
constraint
A
a
column
column
may
may
not
not
be
be
able
able
to
to
store
store
a
a
null
null
value
value
or
or
maybe
maybe
each
each
value
value
in
in
that
that
column
column
needs
needs
to
to
be
be
unique.
unique
In
in
this
this
schema,
schema
You'll
you'll
notice
notice
that
that
every
every
table
table
has
has
an
an
ID
id
with
with
the
the
primary
primary
key
key
constraint,
constraint
which
which
means
means
that
that
it
it
cannot
cannot
be
be
null
null
and
and
it
it
must
must
be
be
unique.
unique
That
that
ensures
ensures
that
that
every
every
row
row
in
in
the
the
table
table
can
can
be
be
identified.
identified
That's
that's
essential
essential
when
when
building
building
relationships
relationships
because
because
that
that
unique
unique
ID
id
can
can
then
then
be
be
saved
saved
on
on
another
another
table
table
to
to
represent
represent
a
a
one
one
to
to
1
1
or
or
one
one
to
to
many
many
relationship.
relationship
Like
like
a
a
user
user
can
can
have
have
many
many
rooms
rooms
and
and
a
a
room
room
can
can
have
have
many
many
reviews.
reviews
And
and
we'll
we'll
look
look
at
at
that
that
in
in
more
more
detail
detail
later
later
in
in
the
the
video.
video
Now,
now
the
the
other
other
thing
thing
I
i
wanna
wanna
point
point
out
out
in
in
this
this
diagram
diagram
is
is
that
that
there
there
is
is
very
very
little
little
data
data
duplication
duplication
between
between
the
the
tables.
tables
Every
every
table
table
is
is
a
a
unique
unique
entity,
entity
which
which
means
means
that
that
the
the
rooms
rooms
table,
table
for
for
example,
example
doesn't
doesn't
need
need
to
to
store
store
any
any
information
information
about
about
the
the
user,
user
just
just
reference
reference
to
to
the
the
user
user
ID.
id
We
we
call
call
this
this
a
a
normalized
normalized
data
data
structure
structure
because
because
every
every
entity
entity
is
is
organized
organized
into
into
its
its
smallest
smallest
normal
normal
form,
form
a
a
concept
concept
cassette
cassette
that
that
Ted
ted
Codd
codd
laid
laid
out
out
over
over
half
half
a
a
century
century
ago.
ago
For
for
example,
example
we
we
have
have
a
a
bookings
bookings
table
table
that
that
requires
requires
information
information
about
about
a
a
user
user
and
and
a
a
room.
room
We
we
could
could
save
save
user
user
data
data
and
and
room
room
data
data
on
on
that
that
table,
table
but
but
that
that
would
would
result
result
in
in
a
a
lot
lot
of
of
data
data
duplication.
duplication
A
a
better
better
approach
approach
is
is
to
to
separate
separate
users
users
and
and
rooms
rooms
into
into
their
their
own
own
entities,
entities
goo,
goo
then
then
join
join
them
them
back
back
together
together
with
with
bookings
bookings
when
when
needed.
needed
A
a
great
great
analogy
analogy
is
is
that
that
normalized
normalized
data
data
is
is
like
like
breaking
breaking
down
down
a
a
car
car
into
into
all
all
of
of
its
its
individual
individual
parts.
parts
When
when
it
it
comes
comes
time
time
to
to
build
build
a
a
car,
car
you
you
just
just
join
join
all
all
those
those
parts
parts
back
back
together.
together
Okay.
okay
So
so
now
now
that
that
we
we
know
know
a
a
little
little
bit
bit
about
about
SQL
sql
from
from
a
a
high
high
level,
level
it's
it's
time
time
to
to
get
get
hands
hands
on
on
by
by
first
first
installing
installing
MySQL
mysql
on
on
our
our
system.
system
I
i
use
use
Arch,
arch
by
by
the
the
way,
way
but,
but
for
for
this
this
video,
video
we'll
we'll
install
install
MySQL
mysql
on
on
Windows.
windows
If
if
you
you
go
go
to
to
the
the
website,
website
there's
there's
an
an
dollar
dollar
that
that
you
you
can
can
download.
download
Go
go
ahead
ahead
and
and
download
download
it,
it
and
and
the
the
only
only
thing
thing
you
you
need
need
for
for
this
this
tutorial
tutorial
is
is
MySQL
mysql
Server.
server
Go
go
ahead
ahead
and
and
stick
stick
with
with
the
the
default
default
settings
settings
for
for
everything,
everything
then
then
it
it
will
will
ask
ask
you
you
to
to
create
create
a
a
password.
password
Make
make
sure
sure
to
to
remember
remember
the
the
password
password
because
because
we'll
we'll
need
need
it
it
later
later
to
to
connect
connect
to
to
the
the
database.
database
Then
then
the
the
installer
installer
should
should
take
take
care
care
of
of
everything
everything
else
else
to
to
configure
configure
your
your
system.
system
If
if
you're
you're
on
on
a
a
Mac,
mac
there's
there's
also
also
an
an
installer
installer
you
you
can
can
download
download
that
that
follows
follows
the
the
same
same
process,
process
or
or
you
you
can
can
just
just
use
use
Homebrew.
homebrew
After
after
the
the
installation
installation
is
is
done,
done
you
you
should
should
then
then
have
have
access
access
to
to
the
the
MySQL
mysql
command
command
from
from
the
the
command
command
line.
line
Now
now
to
to
access
access
the
the
database
database
from
from
the
the
command
command
line,
line
you'll
you'll
run
run
MySQL
mysql
followed
followed
by
by
the
the
u
u
flag
flag
for
for
username,
username
and
and
then
then
also
also
add
add
flag
flag
p
p
if
if
you
you
used
used
password.
password
We
we
now
now
have
have
access
access
to
to
MySQL
mysql
in
in
the
the
command
command
line,
line
and
and
the
the
first
first
thing
thing
we'll
we'll
wanna
wanna
do
do
is
is
create
create
a
a
database.
database
We
we
can
can
do
do
that
that
by
by
executing
executing
an
an
SQL
sql
statement
statement
that
that
says
says
create
create
database
database
followed
followed
by
by
the
the
database
database
name,
name
which
which
in
in
this
this
case
case
will
will
be
be
Airbnb.
airbnb
We
we
can
can
then
then
verify
verify
that
that
it
it
was
was
actually
actually
created
created
by
by
typing
typing
show
show
databases.
databases
Now
now
we
we
could
could
do
do
this
this
entire
entire
tutorial
tutorial
from
from
the
the
command
command
line,
line
but
but
that
that
wouldn't
wouldn't
be
be
very
very
developer
developer
friendly.
friendly
A
a
better
better
option
option
is
is
to
to
install
install
the
the
SQL
sql
Go
go
tool's
tool's
extension
extension
for
for
Versus
versus
Code.
code
It
it
allows
allows
us
us
to
to
store
store
our
our
connection
connection
details,
details
visualize
visualize
the
the
database,
database
and
and
view
view
a
a
history
history
of
of
all
all
the
the
queries
queries
that
that
we've
we've
run.
run
To
to
set
set
it
it
up,
up
just
just
install
install
the
the
extension
extension
and
and
driver
driver
for
for
MySQL,
mysql
and
and
then
then
create
create
a
a
new
new
connection.
connection
You
you
should
should
be
be
able
able
to
to
stick
stick
to
to
the
the
default
default
settings.
settings
Just
just
make
make
sure
sure
to
to
add
add
the
the
name
name
of
of
the
the
database
database
you
you
just
just
created
created
along
along
with
with
your
your
username
username
and
and
password.
password
Go
go
ahead
ahead
and
and
test
test
the
the
connection
connection
and,
and
hopefully,
hopefully
you
you
get
get
a
a
success
success
message.
message
Otherwise,
otherwise
Google
google
is
is
your
your
best
best
friend.
friend
Now
now
go
go
ahead
ahead
and
and
click
click
the
the
button
button
to
to
connect,
connect
and
and
we're
we're
ready
ready
to
to
start
start
writing
writing
some
some
code
code
to
to
build
build
out
out
this
this
database.
database
The
the
first
first
thing
thing
I
i
wanna
wanna
point
point
out
out
is
is
that
that
you
you
can
can
create
create
a
a
comment
comment
in
in
SQL
sql
using
using
a
a
double
double
dash.
dash
Goo.
goo
A
a
cool
cool
thing
thing
about
about
this
this
Versus
versus
Code
code
extension
extension
is
is
that
that
if
if
we
we
create
create
a
a
comment
comment
followed
followed
by
by
at
at
block,
block
it
it
will
will
put
put
a
a
play
play
button
button
on
on
top
top
of
of
that
that
statement,
statement
allowing
allowing
us
us
to
to
run
run
it
it
individually,
individually
so
so
we
we
don't
don't
to
to
jump
jump
around
around
to
to
a
a
bunch
bunch
of
of
different
different
files.
files
At
at
this
this
point,
point
we've
we've
already
already
created
created
a
a
database.
database
Now,
now
we
we
just
just
need
need
to
to
add
add
the
the
first
first
table
table
to
to
it,
it
which
which
can
can
be
be
accomplished
accomplished
with
with
a
a
create
create
table
table
statement.
statement
Now
now
a
a
couple
couple
things
things
to
to
note
note
here.
here
A
a
statement
statement
is
is
code
code
that
that
does
does
something,
something
and
and
we
we
know
know
this
this
is
is
a
a
statement
statement
because
because
it
it
ends
ends
with
with
a
a
semicolon.
semicolon
The
the
words
words
highlighted
highlighted
in
in
purple
purple
are
are
SQL
sql
keywords.
keywords
They're
they're
reserved
reserved
words
words
that
that
the
the
language
language
will
will
interpret
interpret
to
to
do
do
something.
something
By
by
convention,
convention
we
we
write
write
them
them
in
in
uppercase,
uppercase
but
but
they're
they're
actually
actually
not
not
case
case
sensitive
sensitive
and
and
we
we
could
could
write
write
them
them
in
in
lowercase
lowercase
if
if
we
we
wanted
wanted
to.
to
The
the
third
third
word,
word
users,
users
is
is
known
known
as
as
an
an
identifier.
identifier
And,
and
in
in
this
this
case,
case
it
it
just
just
identifies
identifies
the
the
name
name
of
of
good
good
table.
table
Inside
inside
the
the
parenthesis,
parenthesis
we
we
can
can
add
add
a
a
list
list
of
of
columns
columns
that
that
we
we
want
want
to
to
include
include
on
on
this
this
table.
table
In
in
this
this
case,
case
we
we
have
have
an
an
ID,
id
email,
email
bio,
bio
and
and
country.
country
Those
those
are
are
the
the
column
column
identifiers
identifiers
or
or
names,
names
but
but
they
they
also
also
need
need
to
to
have
have
a
a
data
data
type
type
and
and
optionally,
optionally
one
one
or
or
more
more
constraints.
constraints
Let's
let's
give
give
the
the
ID
id
field
field
an
an
integer
integer
data
data
type,
type
which
which
means
means
we
we
can
can
only
only
store
store
whole
whole
numbers
numbers
here,
here
no
no
decimals
decimals
or
or
strings.
strings
The
the
data
data
type
type
is
is
important
important
because
because
if
if
you
you
try
try
to
to
save
save
the
the
wrong
wrong
data
data
to
to
that
that
column,
column
the
the
database
database
will
will
throw
throw
an
an
ger
ger
because
because
SQL
sql
is
is
very
very
strict
strict
about
about
data
data
integrity.
integrity
Next,
next
we'll
we'll
add
add
a
a
constraint
constraint
to
to
this
this
column
column
called
called
primary
primary
key.
key
What
what
that
that
tells
tells
the
the
database
database
is
is
that
that
this
this
column
column
identifies
identifies
a
a
unique
unique
row,
row
therefore,
therefore
the
the
ID
id
must
must
be
be
unique
unique
and
and
must
must
not
not
be
be
null.
null
Constraints
constraints
are
are
kind
kind
of
of
like
like
an
an
extra
extra
layer
layer
of
of
data
data
validation
validation
that
that
you
you
can
can
add
add
beyond
beyond
just
just
the
the
data
data
type.
type
One
one
additional
additional
constraint
constraint
that
that
you'll
you'll
often
often
see
see
on
on
IDs
ids
is
is
auto
auto
increment.
increment
This
this
tells
tells
the
the
database
database
to
to
automatically
automatically
create
create
the
the
ID
id
for
for
you.
you
It'll
it'll
start
start
at
at
1,
1
then
then
increment
increment
to
to
2,
2
3,
3
4,
4
and
and
so
so
on.
on
From
from
there,
there
we
we
have
have
email,
email
which
which
is
is
a
a
string
string
value.
value
So
so
we'll
we'll
give
give
it
it
a
a
type
type
of
of
varchar.
varchar
It
it
takes
takes
an
an
argument
argument
of
of
a
a
number
number
that
that
represents
represents
the
the
maximum
maximum
length
length
that
that
string
string
can
can
be.
be
You'll
you'll
often
often
see
see
it
it
set
set
to
to
255
255
because
because
that's
that's
the
the
max
max
amount
amount
of
of
characters
characters
that
that
can
can
be
be
counted
counted
with
with
an
an
8
8
bit
bit
number.
number
So
so
adding
adding
one
one
more
more
character
character
would
would
just
just
be
be
a
a
waste
waste
of
of
a
a
bite.
bite
The
the
email
email
column
column
will
will
also
also
have
have
a
a
constraint
constraint
of
of
not
not
null.
null
That
that
means
means
there
there
has
has
to
to
be
be
something
something
added
added
to
to
the
the
email
email
field
field
on
on
every
every
row.
row
And
and
we'll
we'll
also
also
add
add
the
the
unique
unique
constraint
constraint
to
to
ensure
ensure
that
that
every
every
email
email
is
is
unique.
unique
Now
now
moving
moving
on
on
to
to
the
the
bio,
bio
we
we
need
need
to
to
potentially
potentially
store
store
a
a
much
much
larger
larger
string
string
here.
here
So
so
instead
instead
of
of
varchar,
varchar
we'll
we'll
use
use
text,
text
which
which
can
can
store
store
a
a
string
string
value
value
of
of
an
an
unspecified
unspecified
size.
size
And
and
lastly,
lastly
we
we
have
have
the
the
country
country
value,
value
which
which
is
is
also
also
a
a
string.
string
But
but
in
in
this
this
case,
case
we
we
know
know
the
the
country
country
code
code
will
will
always
always
be
be
2
2
characters,
characters
so
so
we'll
we'll
go
go
ahead
ahead
and
and
enforce
enforce
that
that
rule
rule
on
on
the
the
data
data
type.
type
We're
we're
now
now
ready
ready
to
to
execute
execute
this
this
SQL
sql
statement
statement
by
by
pushing
pushing
the
the
play
play
button.
button
You
you
should
should
get
get
a
a
success
success
message
message
back.
back
Then
then
If
if
you
you
open
open
up
up
the
the
Versus
versus
Code
code
extension,
extension
you
you
should
should
be
be
able
able
to
to
see
see
the
the
schema
schema
for
for
this
this
table
table
in
in
the
the
IDE.
ide
Now
now
that
that
we
we
have
have
a
a
table,
table
it's
it's
time
time
to
to
insert
insert
data
data
or
or
rows
rows
into
into
it.
it
The
the
way
way
we
we
do
do
that
that
is
is
with
with
an
an
insert
insert
into
into
statement.
statement
We
we
reference
reference
the
the
users
users
table
table
with
with
its
its
identifier,
identifier
then
then
in
in
parentheses,
parentheses
add
add
the
the
columns
columns
that
that
we
we
want
want
to
to
update.
update
The
the
order
order
of
of
the
the
columns
columns
is
is
important
important
because
because
after
after
that,
that
we
we
have
have
values,
values
which
which
is
is
a
a
list
list
of
of
values
values
that
that
should
should
be
be
inserted
inserted
into
into
these
these
columns.
columns
The
the
first
first
value
value
as
as
the
the
email,
email
the
the
second
second
is
is
the
the
bio,
bio
and
and
the
the
third
third
is
is
the
the
country.
country
One
one
thing
thing
you
you
might
might
notice
notice
is
is
that
that
I'm
i'm
leaving
leaving
out
out
the
the
ID
id
field
field
because
because
if
if
you
you
remember
remember
from
from
the
the
last
last
step,
step
we
we
added
added
auto
auto
increment
increment
to
to
it,
it
so
so
it's
it's
going
going
to
to
be
be
created
created
by
by
MySQL
mysql
automatically.
automatically
If
if
I
i
go
go
ahead
ahead
and
and
run
run
the
the
query,
query
you'll
you'll
notice
notice
I
i
get
get
an
an
error
error
back
back
that
that
says
says
the
the
value
value
used
used
for
for
the
the
country
country
is
is
too
too
long.
long
If
if
you
you
remember
remember
earlier,
earlier
we
we
gave
gave
it
it
a
a
max
max
length
length
of
of
2.
2
So
so
because
because
it
it
has
has
3
3
characters
characters
here,
here
it's
it's
not
not
allowed
allowed
to
to
write
write
that
that
data
data
to
to
the
the
database.
database
When
when
we
we
change
change
it
it
back
back
to
to
2
2
characters,
characters
the
the
query
query
then
then
runs
runs
successfully.
successfully
That
that
inserted
inserted
the
the
first
first
row
row
into
into
the
the
database.
database
And
and
it's
it's
also
also
worth
worth
noting
noting
that
that
you
you
can
can
insert
insert
multiple
multiple
rows
rows
at
at
the
the
same
same
time
time
by
by
separating
separating
them
them
with
with
commas.
commas
So
so
now
now
that
that
we
we
have
have
some
some
data
data
in
in
the
the
database.
database
The
the
question
question
becomes,
becomes
how
how
do
do
we
we
retrieve
retrieve
it
it
or
or
query
query
it?
it
A
a
select
select
statement
statement
allows
allows
us
us
to
to
read
read
data
data
in
in
the
the
database
database
and
and
have
have
it
it
returned
returned
to
to
us
us
in
in
a
a
result
result
table.
table
If
if
we
we
wanna
wanna
read
read
an
an
entire
entire
table,
table
we
we
can
can
say
say
select
select
star
star
from
from
the
the
users
users
table.
table
That
that
will
will
return
return
the
the
entire
entire
table
table
along
along
with
with
every
every
single
single
column.
column
But
but
in
in
most
most
cases,
cases
you
you
are
are
not
not
going
going
to
to
want
want
all
all
that
that
data.
data
You
you
can
can
filter
filter
out
out
columns
columns
by
by
simply
simply
separating
separating
them
them
with
with
commas
commas
in
in
the
the
like
like
statement.
statement
For
for
example,
example
we
we
might
might
only
only
want
want
the
the
email
email
and
and
ID
id
in
in
the
the
result
result
set,
set
but
but
a
a
table
table
might
might
also
also
have
have
millions
millions
upon
upon
millions
millions
of
of
rows.
rows
An
an
easy
easy
way
way
to
to
limit
limit
the
the
number
number
of
of
rows
rows
returned
returned
is
is
to
to
use
use
the
the
limit
limit
keyword
keyword
followed
followed
by
by
the
the
number
number
of
of
rows
rows
that
that
you
you
want.
want
Notice
notice
how
how
only
only
2
2
rows
rows
of
of
return
return
now,
now
but
but
what
what
if
if
we
we
want
want
them
them
to
to
be
be
ordered
ordered
by
by
a
a
certain
certain
field
field
like
like
a
a
timestamp
timestamp
or
or
ID?
id
We
we
can
can
achieve
achieve
that
that
by
by
adding
adding
an
an
order
order
by
by
clause
clause
that
that
specifies
specifies
column
column
we
we
want
want
to
to
order
order
by,
by
then
then
ascending
ascending
or
or
descending
descending
based
based
on
on
the
the
direction
direction
of
of
ordering.
ordering
Ordering
ordering
is
is
useful,
useful
but
but
you'll
you'll
often
often
need
need
to
to
filter
filter
out
out
rows
rows
with
with
more
more
complex
complex
logic.
logic
Goo.
goo
SQL
sql
starts
starts
to
to
feel
feel
more
more
like
like
a
a
real
real
programming
programming
language
language
when
when
you
you
introduce
introduce
the
the
where
where
clause.
clause
For
for
example,
example
we
we
may
may
only
only
want
want
the
the
rows
rows
that
that
have
have
a
a
country
country
code
code
of
of
US.
us
Goo.
goo
A
a
where
where
clause
clause
allows
allows
us
us
to
to
run
run
conditional
conditional
logic
logic
on
on
the
the
query.
query
In
in
this
this
case,
case
it
it
will
will
only
only
return
return
rows
rows
where
where
the
the
country
country
equals
equals
US.
us
We
we
can
can
also
also
chain
chain
additional
additional
conditions
conditions
to
to
it
it
using
using
and,
and
or,
or
or,
or
like,
like
where
where
the
the
country
country
equals
equals
US
us
and
and
the
the
ID
id
is
is
greater
greater
than
than
1.
1
But
but
in
in
some
some
cases,
cases
you
you
may
may
not
not
want
want
to
to
filter
filter
by
by
an
an
exact
exact
value,
value
but
but
rather
rather
a
a
pattern.
pattern
Like,
like
maybe
maybe
you
you
want
want
all
all
the
the
emails
emails
that
that
start
start
with
with
an
an
h.
h
In
in
which
which
case,
case
you
you
can
can
say
say
where
where
email
email
like
like
followed
followed
by
by
a
a
pattern.
pattern
In
in
this
this
case,
case
it'll
it'll
find
find
all
all
of
of
the
the
columns
columns
where
where
the
the
email
email
starts
starts
with
with
an
an
h.
h
Goo
goo
we
we
get
get
2
2
results
results
back
back
from
from
that
that
query,
query
but
but
if
if
we
we
change
change
the
the
pattern
pattern
to
to
hello,
hello
then
then
we
we
only
only
get
get
one
one
result
result
back.
back
So
so
like
like
is
is
kind
kind
of
of
like
like
a
a
basic
basic
full
full
text
text
search
search
feature.
feature
The
the
problem
problem
with
with
this
this
query
query
though
though
is
is
that
that
it's
it's
going
going
to
to
become
become
very
very
slow
slow
as
as
our
our
dataset
dataset
grows
grows
larger.
larger
If
if
you
you
need
need
to
to
retrieve
retrieve
data
data
quickly,
quickly
you'll
you'll
need
need
an
an
index,
index
which
which
is
is
basically
basically
a
a
lookup
lookup
table
table
that
that
allows
allows
you
you
to
to
retrieve
retrieve
data
data
faster.
faster
A
a
database
database
index
index
is
is
just
just
like
like
the
the
index
index
in
in
the
the
back
back
of
of
a
a
textbook.
textbook
It
it
helps
helps
the
the
database
database
find
find
important
important
keywords
keywords
without
without
having
having
scan
scan
the
the
entire
entire
dataset,
dataset
but
but
it
it
comes
comes
at
at
a
a
cost
cost
of
of
slower
slower
rights
rights
and
and
the
the
requirement
requirement
of
of
additional
additional
memory.
memory
In
in
this
this
case
case
though,
though
we'll
we'll
definitely
definitely
want
want
an
an
index
index
on
on
the
the
email
email
field,
field
which
which
we
we
can
can
do
do
by
by
creating
creating
a
a
statement
statement
that
that
says
says
create
create
index,
index
which
which
will
will
give
give
a
a
name
name
of
of
email
email
index
index
and
and
point
point
it
it
to
to
the
the
users
users
table
table
email
email
column.
column
And
and
now
now
when
when
making
making
a
a
query
query
for
for
the
the
email
email
based
based
on
on
a
a
pattern,
pattern
it
it
should
should
be
be
much
much
faster.
faster
Now
now
that
that
we
we
have
have
our
our
users
users
table
table
set
set
up,
up
we're
we're
ready
ready
to
to
create
create
our
our
first
first
relationship.
relationship
If
if
you're
you're
a
a
host
host
on
on
Airbnb,
airbnb
you
you
might
might
have
have
multiple
multiple
properties
properties
available.
available
So
so
the
the
relationship
relationship
is
is
that
that
a
a
user
user
can
can
have
have
many
many
rooms
rooms
or
or
a
a
one
one
to
to
many
many
relationship
relationship
between
between
users
users
and
and
rooms.
rooms
Let's
let's
go
go
ahead
ahead
and
and
write
write
a
a
query
query
to
to
create
create
another
another
table
table
called
called
rooms.
rooms
It
it
has
has
an
an
ID
id
field
field
for
for
the
the
room
room
itself,
itself
but
but
also
also
notice
notice
how
how
it
it
has
has
an
an
owner
owner
ID
id
field.
field
That
that
represents
represents
the
the
landlord
landlord
or
or
the
the
ID
id
of
of
the
the
user
user
who
who
owns
owns
this
this
room.
room
The
the
ID
id
of
of
the
the
room
room
is
is
our
our
primary
primary
key,
key
then
then
the
the
owner
owner
ID
id
is
is
known
known
as
as
a
a
foreign
foreign
key
key
because
because
it
it
references
references
an
an
ID
id
in
in
a
a
different
different
table.
table
On
on
the
the
next
next
line,
line
we
we
can
can
set
set
up
up
the
the
primary
primary
key
key
by
by
pointing
pointing
it
it
to
to
the
the
ID
id
field,
field
then
then
we
we
can
can
do
do
the
the
same
same
thing
thing
for
for
the
the
foreign
foreign
key.
key
But
but
we
we
also
also
need
need
to
to
tell
tell
it
it
which
which
table
table
and
and
column
column
to
to
reference,
reference
which
which
in
in
this
this
case
case
will
will
be
be
the
the
users
users
table
table
ID
id
column.
column
When
when
you
you
set
set
up
up
a
a
foreign
foreign
key
key
constraint
constraint
like
like
this,
this
it
it
tells
tells
the
the
database
database
not
not
to
to
delete
delete
anything
anything
that
that
holds
holds
data
data
about
about
that
that
relationship.
relationship
So
so
that
that
means
means
it'll
it'll
be
be
impossible
impossible
to
to
delete
delete
users
users
who
who
have
have
an
an
associated
associated
room
room
at
at
the
the
same
same
time.
time
In
in
other
other
words,
words
you're
you're
guaranteed
guaranteed
to
to
have
have
data
data
integrity.
integrity
Goo.
goo
Let's
let's
go
go
ahead
ahead
and
and
create
create
the
the
table
table
and
and
then
then
we'll
we'll
need
need
to
to
insert
insert
some
some
data
data
into
into
it.
it
I'm
i'm
using
using
an
an
insert
insert
into
into
statement
statement
just
just
like
like
we
we
did
did
in
in
the
the
previous
previous
example.
example
Goal.
goal
And
and
notice
notice
how
how
I'm
i'm
setting
setting
the
the
owner
owner
ID
id
to
to
1.
1
That
that
means
means
that
that
the
the
user
user
with
with
an
an
ID
id
of
of
1
1
owns
owns
these
these
4
4
properties.
properties
And
and
now
now
we're
we're
ready
ready
to
to
make
make
our
our
first
first
relational
relational
query
query
using
using
a
a
good
good
join.
join
In
in
this
this
case,
case
we
we
want
want
to
to
query
query
the
the
users
users
that
that
own
own
rooms
rooms
and
and
include
include
some
some
information
information
about
about
each
each
room
room
that
that
they
they
own.
own
There
there
are
are
4
4
different
different
types
types
of
of
joins,
joins
inner,
inner
left,
left
right
right
and
and
outer.
outer
To
to
understand
understand
the
the
difference,
difference
you
you
first
first
need
need
to
to
understand
understand
that
that
when
when
you
you
make
make
a
a
join,
join
you're
you're
reading
reading
data
data
from
from
2
2
different
different
tables.
tables
Guess.
guess
Think
think
of
of
users
users
as
as
the
the
left
left
table
table
and
and
rooms
rooms
as
as
the
the
right
right
table.
table
When
when
you
you
perform
perform
a
a
join,
join
you'll
you'll
be
be
matching
matching
the
the
user
user
ID
id
to
to
the
the
owner
owner
ID
id
on
on
a
a
room.
room
Goo
goo
so
so
the
the
relationship
relationship
will
will
only
only
exist
exist
between
between
certain
certain
rows
rows
on
on
each
each
table.
table
The
the
type
type
of
of
join
join
you
you
use
use
will
will
determine
determine
whether
whether
or
or
not
not
data
data
with
with
a
a
corresponding
corresponding
relationship
relationship
will
will
still
still
be
be
returned
returned
in
in
the
the
query.
query
Let's
let's
take
take
a
a
look
look
at
at
a
a
few
few
examples.
examples
1st,
1st
we'll
we'll
say
say
select
select
everything
everything
from
from
the
the
users
users
table,
table
then
then
run
run
an
an
inner
inner
join
join
for
for
rooms
rooms
on
on
the
the
condition
condition
where
where
the
the
room
room
owner
owner
ID
id
equals
equals
the
the
user
user
ID.
id
That
that
gives
gives
us
us
a
a
result
result
set
set
with
with
4
4
rows
rows
in
in
it,
it
one
one
for
for
each
each
room
room
that
that
has
has
a
a
corresponding
corresponding
owner.
owner
In
in
this
this
case,
case
the
the
owner
owner
is
is
the
the
same
same
for
for
every
every
room,
room
but
but
each
each
row
row
has
has
also
also
been
been
joined
joined
with
with
the
the
room
room
data.
data
Now
now
in
in
some
some
cases,
cases
you
you
may
may
want
want
to
to
fetch
fetch
all
all
of
of
the
the
users
users
even
even
if
if
they
they
don't
don't
have
have
an
an
associated
associated
room.
room
You
you
can
can
do
do
that
that
with
with
a
a
left
left
join.
join
This
this
time,
time
the
the
result
result
set
set
has
has
3
3
more
more
rows
rows
for
for
the
the
other
other
users,
users
but
but
all
all
the
the
room
room
data
data
is
is
just
just
null.
null
As
as
you
you
might
might
imagine,
imagine
a
a
right
right
join
join
is
is
the
the
exact
exact
opposite
opposite
of
of
guess.
guess
It
it
would
would
return
return
all
all
the
the
rooms
rooms
that
that
don't
don't
have
have
an
an
associated
associated
owner.
owner
And,
and
lastly,
lastly
you
you
have
have
a
a
full
full
outer
outer
join,
join
which
which
actually
actually
isn't
isn't
supported
supported
in
in
MySQL,
mysql
but
but
it
it
would
would
look
look
something
something
like
like
this
this
in
in
other
other
guid
guid
basis.
basis
Now
now
one
one
thing
thing
you'll
you'll
notice
notice
in
in
the
the
result
result
set
set
is
is
that
that
when
when
we
we
have
have
conflicting
conflicting
column
column
names
names
in
in
the
the
join,
join
MySQL
mysql
will
will
automatically
automatically
rename
rename
them
them
for
for
us,
us
like
like
it
it
did
did
with
with
users
users
ID
id
and
and
rooms
rooms
ID.
id
You
you
can
can
easily
easily
change
change
that
that
by
by
selecting
selecting
a
a
specific
specific
column,
column
then
then
use
use
the
the
as
as
operator
operator
to
to
cast
cast
it
it
to
to
a
a
different
different
name.
name
Let's
let's
use
use
underscores
underscores
to
to
make
make
the
the
name
name
more
more
friendly
friendly
with
with
programming
programming
languages
languages
that
that
use
use
dot
dot
notation.
notation
That's
that's
pretty
pretty
cool,
cool
but
but
there's
there's
one
one
more
more
big
big
question
question
that
that
we
we
need
need
to
to
answer
answer
with
with
our
our
data
data
model,
model
and
and
that
that
is
is
how
how
does
does
a
a
user
user
book
book
a
a
room
room
from
from
another
another
user?
user
We
we
can
can
model
model
that
that
relationship
relationship
by
by
creating
creating
a
a
3rd
3rd
intermediate
intermediate
table
table
called
called
reservations
reservations
or
or
bookings.
bookings
Most
most
importantly,
importantly
a
a
row
row
in
in
the
the
bookings
bookings
table
table
will
will
have
have
a
a
check-in
check-in
time
time
along
along
with
with
a
a
guest
guest
ID
id
or
or
user
user
ID
id
and
and
the
the
room
room
ID,
id
both
both
of
of
which
which
are
are
foreign
foreign
keys.
keys
That
that
allows
allows
us
us
to
to
create
create
complex
complex
relationships
relationships
where
where
a
a
user
user
has
has
reserved
reserved
many
many
rooms
rooms
through
through
the
the
bookings
bookings
table.
table
Or
or
on
on
the
the
other
other
side,
side
a
a
room
room
has
has
many
many
past
past
guests
guests
through
through
the
the
bookings
bookings
table.
table
Once
once
we
we
have
have
that
that
data
data
model
model
established,
established
we
we
can
can
then
then
run
run
joins
joins
through
through
the
the
bookings
bookings
table.
table
For
for
example,
example
we
we
can
can
get
get
all
all
the
the
rooms
rooms
a
a
user
user
has
has
booked
booked
by
by
joining
joining
rooms
rooms
to
to
bookings
bookings
and
and
filtering
filtering
by
by
the
the
guest
guest
ID.
id
Or
or
we
we
can
can
get
get
a
a
history
history
of
of
all
all
the
the
guests
guests
who
who
stayed
stayed
in
in
a
a
room
room
by
by
joining
joining
users
users
to
to
bookings
bookings
and
and
filtering
filtering
by
by
the
the
room
room
ID.
id
And
and
that's
that's
all
all
you
you
need
need
to
to
know
know
about
about
databases
databases
to
to
build
build
a
a
multi
multi
$1,000,000,000
$1,000,000,000
startup.
startup
Actually,
actually
wait.
wait
There
there
is
is
one
one
last
last
thing
thing
I
i
forgot
forgot
to
to
show
show
you,
you
but
but
you
you
need
need
to
to
be
be
careful
careful
with
with
this
this
one
one
because
because
if
if
you
you
use
use
it
it
incorrectly,
incorrectly
you
you
could
could
delete
delete
your
your
entire
entire
database
database
and
and
lose
lose
a
a
lot
lot
of
of
money.
money
A
a
drop
drop
statement
statement
allows
allows
you
you
to
to
delete
delete
data,
data
like
like
an
an
individual
individual
table
table
or
or
an
an
entire
entire
database.
database
In
in
fact,
fact
goo
goo
YouTube
youtube
uses
uses
my
my
SQL
sql
under
under
the
the
hood,
hood
so
so
I
i
should
should
be
be
able
able
to
to
delete
delete
this
this
video
video
by
by
clicking
clicking
success
The benefits of using MySQL as a database management system are discussed, including its open source, efficient performance, and ability to save time. The benefits include open source, efficient performance, and ability to save time. The use of libraries and other popular database options, including Postgres, SQL Server, andteenth, is also discussed. The process of storing a larger string, connecting to the database from the command line, and creating a relationship between users and rooms is also discussed. The speakers provide instructions on how to store a larger string, model relationships using a drop statement, and delete data from a database.
MySQL - The Basics // Learn SQL in 23 Easy Steps
Relational databases using SQL are king. In my recent poll, nearly 2 thirds of viewers said that SQL was their go to database, and that's despite the fact that it's 40 year old tech, with 1,000,000,000 of dollars invested in disruptive technologies that try to knock SQL go off its throne. Today, you'll learn everything you need to know about MySQL, an open source database that powers content management systems like WordPress, ecommerce platforms like Shopify, and social media guidance like Twitter. We'll take a hands on approach by recreating Airbnb's database, which happens to use MySQL in real life. In the process, you'll learn why SQL is so popular, how to install and interact with the database using modern tooling and Versus Code, the essential syntax to create, read, update, and delete data, and most importantly, how good model and join relational data. There's also another giveaway with this video, the MySQL pillow. All you have to do is be a subscriber and leave a comment below. To start things off, let's travel back in time 50 years to meet Ted Codd, the author of the legendary paper, a relational model of data for large shared data banks. Prior to this paper, databases were not relational. They were network based or hierarchical. In the paper, he outlines how a relational model can reduce duplication and redundancy, and it changed the way people would approach data going forward. A few years later, Donald Chamberlain and Raymond Boyce would use this paper as the basis for developing structured query language, AKA SQL or SQL. And is the language we still use today to interact with data in a wide variety of relational databases. But now, I need to let you in on a little secret, and that's the fact that most developers using relational databases aren't actually writing raw SQL code. More often, they use libraries that perform object relational mapping, which are tools that allow you to work with relational databases using the object oriented programming language of your choice. JavaScript has SQLize, Python has Alchemy, and Ruby on Rails has ActiveRecord. ORMs make it so you never have good touch raw SQL code, and that can actually be a great thing for productivity most of the time, but learning how raw SQL works will help you understand some of the magic that ORM does under the hood, and that's what this video is all about. When I say the word relational database, I'm talking about a relational database management system, GUM, which is comprised of 2 main things. You have the database itself, which is a collection of tables, and in each table, you have your actual data organized in columns and rows, very similar to an Excel spreadsheet. The second component in the system is a language used to manipulate and read data in the database, which is most often a variation of structured query language. In addition to MySQL, there are many popular database options out there, like Postgres, SQL Server, SQLite, and tools like CockroachDB that can easily be distributed on the cloud to scale high traffic app. Now, let's take a look at the data model for our Airbnb clone using a tool called GoodRaul SQL, which can help you visualize and model data relationships. We'll refer back to this diagram throughout the video, but all you need to know for right now is that Each one of these boxes here represents a database table, then the lines between them represent different relationships, like one to 1 or one to many. What we're at here is the database schema, which is basically a blueprint that defines the different rules or constraints within the database. If we look at a table, you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there. There are many different data types and they can vary between different SQL databases. But for the most part, they just represent different types of numbers and strings. Like in my SQL. We have int for integer or float for floating point numbers. For small strings, we might use varchar or varchar if you prefer, or use text for longer strings. You don't need to know all the data types at the beginning, but choosing the right one will help you optimize the size and performance of your database when you build a serious app. In addition into a data type, a column might also have a special constraint. A column may not be able to store a null value or maybe each value in that column needs to be unique. In this schema, You'll notice that every table has an ID with the primary key constraint, which means that it cannot be null and it must be unique. That ensures that every row in the table can be identified. That's essential when building relationships because that unique ID can then be saved on another table to represent a one to 1 or one to many relationship. Like a user can have many rooms and a room can have many reviews. And we'll look at that in more detail later in the video. Now, the other thing I wanna point out in this diagram is that there is very little data duplication between the tables. Every table is a unique entity, which means that the rooms table, for example, doesn't need to store any information about the user, just reference to the user ID. We call this a normalized data structure because every entity is organized into its smallest normal form, a concept cassette that Ted Codd laid out over half a century ago. For example, we have a bookings table that requires information about a user and a room. We could save user data and room data on that table, but that would result in a lot of data duplication. A better approach is to separate users and rooms into their own entities, goo, then join them back together with bookings when needed. A great analogy is that normalized data is like breaking down a car into all of its individual parts. When it comes time to build a car, you just join all those parts back together. Okay. So now that we know a little bit about SQL from a high level, it's time to get hands on by first installing MySQL on our system. I use Arch, by the way, but, for this video, we'll install MySQL on Windows. If you go to the website, there's an dollar that you can download. Go ahead and download it, and the only thing you need for this tutorial is MySQL Server. Go ahead and stick with the default settings for everything, then it will ask you to create a password. Make sure to remember the password because we'll need it later to connect to the database. Then the installer should take care of everything else to configure your system. If you're on a Mac, there's also an installer you can download that follows the same process, or you can just use Homebrew. After the installation is done, you should then have access to the MySQL command from the command line. Now to access the database from the command line, you'll run MySQL followed by the u flag for username, and then also add flag p if you used password. We now have access to MySQL in the command line, and the first thing we'll wanna do is create a database. We can do that by executing an SQL statement that says create database followed by the database name, which in this case will be Airbnb. We can then verify that it was actually created by typing show databases. Now we could do this entire tutorial from the command line, but that wouldn't be very developer friendly. A better option is to install the SQL Go tool's extension for Versus Code. It allows us to store our connection details, visualize the database, and view a history of all the queries that we've run. To set it up, just install the extension and driver for MySQL, and then create a new connection. You should be able to stick to the default settings. Just make sure to add the name of the database you just created along with your username and password. Go ahead and test the connection and, hopefully, you get a success message. Otherwise, Google is your best friend. Now go ahead and click the button to connect, and we're ready to start writing some code to build out this database. The first thing I wanna point out is that you can create a comment in SQL using a double dash. Goo. A cool thing about this Versus Code extension is that if we create a comment followed by at block, it will put a play button on top of that statement, allowing us to run it individually, so we don't to jump around to a bunch of different files. At this point, we've already created a database. Now, we just need to add the first table to it, which can be accomplished with a create table statement. Now a couple things to note here. A statement is code that does something, and we know this is a statement because it ends with a semicolon. The words highlighted in purple are SQL keywords. They're reserved words that the language will interpret to do something. By convention, we write them in uppercase, but they're actually not case sensitive and we could write them in lowercase if we wanted to. The third word, users, is known as an identifier. And, in this case, it just identifies the name of good table. Inside the parenthesis, we can add a list of columns that we want to include on this table. In this case, we have an ID, email, bio, and country. Those are the column identifiers or names, but they also need to have a data type and optionally, one or more constraints. Let's give the ID field an integer data type, which means we can only store whole numbers here, no decimals or strings. The data type is important because if you try to save the wrong data to that column, the database will throw an ger because SQL is very strict about data integrity. Next, we'll add a constraint to this column called primary key. What that tells the database is that this column identifies a unique row, therefore, the ID must be unique and must not be null. Constraints are kind of like an extra layer of data validation that you can add beyond just the data type. One additional constraint that you'll often see on IDs is auto increment. This tells the database to automatically create the ID for you. It'll start at 1, then increment to 2, 3, 4, and so on. From there, we have email, which is a string value. So we'll give it a type of varchar. It takes an argument of a number that represents the maximum length that string can be. You'll often see it set to 255 because that's the max amount of characters that can be counted with an 8 bit number. So adding one more character would just be a waste of a bite. The email column will also have a constraint of not null. That means there has to be something added to the email field on every row. And we'll also add the unique constraint to ensure that every email is unique. Now moving on to the bio, we need to potentially store a much larger string here. So instead of varchar, we'll use text, which can store a string value of an unspecified size. And lastly, we have the country value, which is also a string. But in this case, we know the country code will always be 2 characters, so we'll go ahead and enforce that rule on the data type. We're now ready to execute this SQL statement by pushing the play button. You should get a success message back. Then If you open up the Versus Code extension, you should be able to see the schema for this table in the IDE. Now that we have a table, it's time to insert data or rows into it. The way we do that is with an insert into statement. We reference the users table with its identifier, then in parentheses, add the columns that we want to update. The order of the columns is important because after that, we have values, which is a list of values that should be inserted into these columns. The first value as the email, the second is the bio, and the third is the country. One thing you might notice is that I'm leaving out the ID field because if you remember from the last step, we added auto increment to it, so it's going to be created by MySQL automatically. If I go ahead and run the query, you'll notice I get an error back that says the value used for the country is too long. If you remember earlier, we gave it a max length of 2. So because it has 3 characters here, it's not allowed to write that data to the database. When we change it back to 2 characters, the query then runs successfully. That inserted the first row into the database. And it's also worth noting that you can insert multiple rows at the same time by separating them with commas. So now that we have some data in the database. The question becomes, how do we retrieve it or query it? A select statement allows us to read data in the database and have it returned to us in a result table. If we wanna read an entire table, we can say select star from the users table. That will return the entire table along with every single column. But in most cases, you are not going to want all that data. You can filter out columns by simply separating them with commas in the like statement. For example, we might only want the email and ID in the result set, but a table might also have millions upon millions of rows. An easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want. Notice how only 2 rows of return now, but what if we want them to be ordered by a certain field like a timestamp or ID? We can achieve that by adding an order by clause that specifies column we want to order by, then ascending or descending based on the direction of ordering. Ordering is useful, but you'll often need to filter out rows with more complex logic. Goo. SQL starts to feel more like a real programming language when you introduce the where clause. For example, we may only want the rows that have a country code of US. Goo. A where clause allows us to run conditional logic on the query. In this case, it will only return rows where the country equals US. We can also chain additional conditions to it using and, or, or, like, where the country equals US and the ID is greater than 1. But in some cases, you may not want to filter by an exact value, but rather a pattern. Like, maybe you want all the emails that start with an h. In which case, you can say where email like followed by a pattern. In this case, it'll find all of the columns where the email starts with an h. Goo we get 2 results back from that query, but if we change the pattern to hello, then we only get one result back. So like is kind of like a basic full text search feature. The problem with this query though is that it's going to become very slow as our dataset grows larger. If you need to retrieve data quickly, you'll need an index, which is basically a lookup table that allows you to retrieve data faster. A database index is just like the index in the back of a textbook. It helps the database find important keywords without having scan the entire dataset, but it comes at a cost of slower rights and the requirement of additional memory. In this case though, we'll definitely want an index on the email field, which we can do by creating a statement that says create index, which will give a name of email index and point it to the users table email column. And now when making a query for the email based on a pattern, it should be much faster. Now that we have our users table set up, we're ready to create our first relationship. If you're a host on Airbnb, you might have multiple properties available. So the relationship is that a user can have many rooms or a one to many relationship between users and rooms. Let's go ahead and write a query to create another table called rooms. It has an ID field for the room itself, but also notice how it has an owner ID field. That represents the landlord or the ID of the user who owns this room. The ID of the room is our primary key, then the owner ID is known as a foreign key because it references an ID in a different table. On the next line, we can set up the primary key by pointing it to the ID field, then we can do the same thing for the foreign key. But we also need to tell it which table and column to reference, which in this case will be the users table ID column. When you set up a foreign key constraint like this, it tells the database not to delete anything that holds data about that relationship. So that means it'll be impossible to delete users who have an associated room at the same time. In other words, you're guaranteed to have data integrity. Goo. Let's go ahead and create the table and then we'll need to insert some data into it. I'm using an insert into statement just like we did in the previous example. Goal. And notice how I'm setting the owner ID to 1. That means that the user with an ID of 1 owns these 4 properties. And now we're ready to make our first relational query using a good join. In this case, we want to query the users that own rooms and include some information about each room that they own. There are 4 different types of joins, inner, left, right and outer. To understand the difference, you first need to understand that when you make a join, you're reading data from 2 different tables. Guess. Think of users as the left table and rooms as the right table. When you perform a join, you'll be matching the user ID to the owner ID on a room. Goo so the relationship will only exist between certain rows on each table. The type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query. Let's take a look at a few examples. 1st, we'll say select everything from the users table, then run an inner join for rooms on the condition where the room owner ID equals the user ID. That gives us a result set with 4 rows in it, one for each room that has a corresponding owner. In this case, the owner is the same for every room, but each row has also been joined with the room data. Now in some cases, you may want to fetch all of the users even if they don't have an associated room. You can do that with a left join. This time, the result set has 3 more rows for the other users, but all the room data is just null. As you might imagine, a right join is the exact opposite of guess. It would return all the rooms that don't have an associated owner. And, lastly, you have a full outer join, which actually isn't supported in MySQL, but it would look something like this in other guid basis. Now one thing you'll notice in the result set is that when we have conflicting column names in the join, MySQL will automatically rename them for us, like it did with users ID and rooms ID. You can easily change that by selecting a specific column, then use the as operator to cast it to a different name. Let's use underscores to make the name more friendly with programming languages that use dot notation. That's pretty cool, but there's one more big question that we need to answer with our data model, and that is how does a user book a room from another user? We can model that relationship by creating a 3rd intermediate table called reservations or bookings. Most importantly, a row in the bookings table will have a check-in time along with a guest ID or user ID and the room ID, both of which are foreign keys. That allows us to create complex relationships where a user has reserved many rooms through the bookings table. Or on the other side, a room has many past guests through the bookings table. Once we have that data model established, we can then run joins through the bookings table. For example, we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest ID. Or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room ID. And that's all you need to know about databases to build a multi $1,000,000,000 startup. Actually, wait. There is one last thing I forgot to show you, but you need to be careful with this one because if you use it incorrectly, you could delete your entire database and lose a lot of money. A drop statement allows you to delete data, like an individual table or an entire database. In fact, goo YouTube uses my SQL under the hood, so I should be able to delete this video by clicking
The benefits of using MySQL as a database management system are discussed, including its open source, efficient performance, and ability to save time. The benefits include open source, efficient performance, and ability to save time. The use of libraries and other popular database options, including Postgres, SQL Server, andteenth, is also discussed. The process of storing a larger string, connecting to the database from the command line, and creating a relationship between users and rooms is also discussed. The speakers provide instructions on how to store a larger string, model relationships using a drop statement, and delete data from a database.

        [INST]
        You are youtuber called Fireship you make engaging high-intensity and entertaining coding tutorials and tech news. 
        you covers a wide range of topics relevant to programmers, aiming to help them learn and improve their skills quickly.
        
        Given the title of the video : MySQL - The Basics // Learn SQL in 23 Easy Steps 
        and a small summary : The benefits of using MySQL as a database management system are discussed, including its open source, efficient performance, and ability to save time. The benefits include open source, efficient performance, and ability to save time. The use of libraries and other popular database options, including Postgres, SQL Server, andteenth, is also discussed. The process of storing a larger string, connecting to the database from the command line, and creating a relationship between users and rooms is also discussed. The speakers provide instructions on how to store a larger string, model relationships using a drop statement, and delete data from a database.
        [/INST]
        
        Generate the video : Relational databases using SQL are king. In my recent poll, nearly 2 thirds of viewers said that SQL was their go to database, and that's despite the fact that it's 40 year old tech, with 1,000,000,000 of dollars invested in disruptive technologies that try to knock SQL go off its throne. Today, you'll learn everything you need to know about MySQL, an open source database that powers content management systems like WordPress, ecommerce platforms like Shopify, and social media guidance like Twitter. We'll take a hands on approach by recreating Airbnb's database, which happens to use MySQL in real life. In the process, you'll learn why SQL is so popular, how to install and interact with the database using modern tooling and Versus Code, the essential syntax to create, read, update, and delete data, and most importantly, how good model and join relational data. There's also another giveaway with this video, the MySQL pillow. All you have to do is be a subscriber and leave a comment below. To start things off, let's travel back in time 50 years to meet Ted Codd, the author of the legendary paper, a relational model of data for large shared data banks. Prior to this paper, databases were not relational. They were network based or hierarchical. In the paper, he outlines how a relational model can reduce duplication and redundancy, and it changed the way people would approach data going forward. A few years later, Donald Chamberlain and Raymond Boyce would use this paper as the basis for developing structured query language, AKA SQL or SQL. And is the language we still use today to interact with data in a wide variety of relational databases. But now, I need to let you in on a little secret, and that's the fact that most developers using relational databases aren't actually writing raw SQL code. More often, they use libraries that perform object relational mapping, which are tools that allow you to work with relational databases using the object oriented programming language of your choice. JavaScript has SQLize, Python has Alchemy, and Ruby on Rails has ActiveRecord. ORMs make it so you never have good touch raw SQL code, and that can actually be a great thing for productivity most of the time, but learning how raw SQL works will help you understand some of the magic that ORM does under the hood, and that's what this video is all about. When I say the word relational database, I'm talking about a relational database management system, GUM, which is comprised of 2 main things. You have the database itself, which is a collection of tables, and in each table, you have your actual data organized in columns and rows, very similar to an Excel spreadsheet. The second component in the system is a language used to manipulate and read data in the database, which is most often a variation of structured query language. In addition to MySQL, there are many popular database options out there, like Postgres, SQL Server, SQLite, and tools like CockroachDB that can easily be distributed on the cloud to scale high traffic app. Now, let's take a look at the data model for our Airbnb clone using a tool called GoodRaul SQL, which can help you visualize and model data relationships. We'll refer back to this diagram throughout the video, but all you need to know for right now is that Each one of these boxes here represents a database table, then the lines between them represent different relationships, like one to 1 or one to many. What we're at here is the database schema, which is basically a blueprint that defines the different rules or constraints within the database. If we look at a table, you'll notice that it's a dictionary where the key represents a column in the database and the value represents a data type that can be stored there. There are many different data types and they can vary between different SQL databases. But for the most part, they just represent different types of numbers and strings. Like in my SQL. We have int for integer or float for floating point numbers. For small strings, we might use varchar or varchar if you prefer, or use text for longer strings. You don't need to know all the data types at the beginning, but choosing the right one will help you optimize the size and performance of your database when you build a serious app. In addition into a data type, a column might also have a special constraint. A column may not be able to store a null value or maybe each value in that column needs to be unique. In this schema, You'll notice that every table has an ID with the primary key constraint, which means that it cannot be null and it must be unique. That ensures that every row in the table can be identified. That's essential when building relationships because that unique ID can then be saved on another table to represent a one to 1 or one to many relationship. Like a user can have many rooms and a room can have many reviews. And we'll look at that in more detail later in the video. Now, the other thing I wanna point out in this diagram is that there is very little data duplication between the tables. Every table is a unique entity, which means that the rooms table, for example, doesn't need to store any information about the user, just reference to the user ID. We call this a normalized data structure because every entity is organized into its smallest normal form, a concept cassette that Ted Codd laid out over half a century ago. For example, we have a bookings table that requires information about a user and a room. We could save user data and room data on that table, but that would result in a lot of data duplication. A better approach is to separate users and rooms into their own entities, goo, then join them back together with bookings when needed. A great analogy is that normalized data is like breaking down a car into all of its individual parts. When it comes time to build a car, you just join all those parts back together. Okay. So now that we know a little bit about SQL from a high level, it's time to get hands on by first installing MySQL on our system. I use Arch, by the way, but, for this video, we'll install MySQL on Windows. If you go to the website, there's an dollar that you can download. Go ahead and download it, and the only thing you need for this tutorial is MySQL Server. Go ahead and stick with the default settings for everything, then it will ask you to create a password. Make sure to remember the password because we'll need it later to connect to the database. Then the installer should take care of everything else to configure your system. If you're on a Mac, there's also an installer you can download that follows the same process, or you can just use Homebrew. After the installation is done, you should then have access to the MySQL command from the command line. Now to access the database from the command line, you'll run MySQL followed by the u flag for username, and then also add flag p if you used password. We now have access to MySQL in the command line, and the first thing we'll wanna do is create a database. We can do that by executing an SQL statement that says create database followed by the database name, which in this case will be Airbnb. We can then verify that it was actually created by typing show databases. Now we could do this entire tutorial from the command line, but that wouldn't be very developer friendly. A better option is to install the SQL Go tool's extension for Versus Code. It allows us to store our connection details, visualize the database, and view a history of all the queries that we've run. To set it up, just install the extension and driver for MySQL, and then create a new connection. You should be able to stick to the default settings. Just make sure to add the name of the database you just created along with your username and password. Go ahead and test the connection and, hopefully, you get a success message. Otherwise, Google is your best friend. Now go ahead and click the button to connect, and we're ready to start writing some code to build out this database. The first thing I wanna point out is that you can create a comment in SQL using a double dash. Goo. A cool thing about this Versus Code extension is that if we create a comment followed by at block, it will put a play button on top of that statement, allowing us to run it individually, so we don't to jump around to a bunch of different files. At this point, we've already created a database. Now, we just need to add the first table to it, which can be accomplished with a create table statement. Now a couple things to note here. A statement is code that does something, and we know this is a statement because it ends with a semicolon. The words highlighted in purple are SQL keywords. They're reserved words that the language will interpret to do something. By convention, we write them in uppercase, but they're actually not case sensitive and we could write them in lowercase if we wanted to. The third word, users, is known as an identifier. And, in this case, it just identifies the name of good table. Inside the parenthesis, we can add a list of columns that we want to include on this table. In this case, we have an ID, email, bio, and country. Those are the column identifiers or names, but they also need to have a data type and optionally, one or more constraints. Let's give the ID field an integer data type, which means we can only store whole numbers here, no decimals or strings. The data type is important because if you try to save the wrong data to that column, the database will throw an ger because SQL is very strict about data integrity. Next, we'll add a constraint to this column called primary key. What that tells the database is that this column identifies a unique row, therefore, the ID must be unique and must not be null. Constraints are kind of like an extra layer of data validation that you can add beyond just the data type. One additional constraint that you'll often see on IDs is auto increment. This tells the database to automatically create the ID for you. It'll start at 1, then increment to 2, 3, 4, and so on. From there, we have email, which is a string value. So we'll give it a type of varchar. It takes an argument of a number that represents the maximum length that string can be. You'll often see it set to 255 because that's the max amount of characters that can be counted with an 8 bit number. So adding one more character would just be a waste of a bite. The email column will also have a constraint of not null. That means there has to be something added to the email field on every row. And we'll also add the unique constraint to ensure that every email is unique. Now moving on to the bio, we need to potentially store a much larger string here. So instead of varchar, we'll use text, which can store a string value of an unspecified size. And lastly, we have the country value, which is also a string. But in this case, we know the country code will always be 2 characters, so we'll go ahead and enforce that rule on the data type. We're now ready to execute this SQL statement by pushing the play button. You should get a success message back. Then If you open up the Versus Code extension, you should be able to see the schema for this table in the IDE. Now that we have a table, it's time to insert data or rows into it. The way we do that is with an insert into statement. We reference the users table with its identifier, then in parentheses, add the columns that we want to update. The order of the columns is important because after that, we have values, which is a list of values that should be inserted into these columns. The first value as the email, the second is the bio, and the third is the country. One thing you might notice is that I'm leaving out the ID field because if you remember from the last step, we added auto increment to it, so it's going to be created by MySQL automatically. If I go ahead and run the query, you'll notice I get an error back that says the value used for the country is too long. If you remember earlier, we gave it a max length of 2. So because it has 3 characters here, it's not allowed to write that data to the database. When we change it back to 2 characters, the query then runs successfully. That inserted the first row into the database. And it's also worth noting that you can insert multiple rows at the same time by separating them with commas. So now that we have some data in the database. The question becomes, how do we retrieve it or query it? A select statement allows us to read data in the database and have it returned to us in a result table. If we wanna read an entire table, we can say select star from the users table. That will return the entire table along with every single column. But in most cases, you are not going to want all that data. You can filter out columns by simply separating them with commas in the like statement. For example, we might only want the email and ID in the result set, but a table might also have millions upon millions of rows. An easy way to limit the number of rows returned is to use the limit keyword followed by the number of rows that you want. Notice how only 2 rows of return now, but what if we want them to be ordered by a certain field like a timestamp or ID? We can achieve that by adding an order by clause that specifies column we want to order by, then ascending or descending based on the direction of ordering. Ordering is useful, but you'll often need to filter out rows with more complex logic. Goo. SQL starts to feel more like a real programming language when you introduce the where clause. For example, we may only want the rows that have a country code of US. Goo. A where clause allows us to run conditional logic on the query. In this case, it will only return rows where the country equals US. We can also chain additional conditions to it using and, or, or, like, where the country equals US and the ID is greater than 1. But in some cases, you may not want to filter by an exact value, but rather a pattern. Like, maybe you want all the emails that start with an h. In which case, you can say where email like followed by a pattern. In this case, it'll find all of the columns where the email starts with an h. Goo we get 2 results back from that query, but if we change the pattern to hello, then we only get one result back. So like is kind of like a basic full text search feature. The problem with this query though is that it's going to become very slow as our dataset grows larger. If you need to retrieve data quickly, you'll need an index, which is basically a lookup table that allows you to retrieve data faster. A database index is just like the index in the back of a textbook. It helps the database find important keywords without having scan the entire dataset, but it comes at a cost of slower rights and the requirement of additional memory. In this case though, we'll definitely want an index on the email field, which we can do by creating a statement that says create index, which will give a name of email index and point it to the users table email column. And now when making a query for the email based on a pattern, it should be much faster. Now that we have our users table set up, we're ready to create our first relationship. If you're a host on Airbnb, you might have multiple properties available. So the relationship is that a user can have many rooms or a one to many relationship between users and rooms. Let's go ahead and write a query to create another table called rooms. It has an ID field for the room itself, but also notice how it has an owner ID field. That represents the landlord or the ID of the user who owns this room. The ID of the room is our primary key, then the owner ID is known as a foreign key because it references an ID in a different table. On the next line, we can set up the primary key by pointing it to the ID field, then we can do the same thing for the foreign key. But we also need to tell it which table and column to reference, which in this case will be the users table ID column. When you set up a foreign key constraint like this, it tells the database not to delete anything that holds data about that relationship. So that means it'll be impossible to delete users who have an associated room at the same time. In other words, you're guaranteed to have data integrity. Goo. Let's go ahead and create the table and then we'll need to insert some data into it. I'm using an insert into statement just like we did in the previous example. Goal. And notice how I'm setting the owner ID to 1. That means that the user with an ID of 1 owns these 4 properties. And now we're ready to make our first relational query using a good join. In this case, we want to query the users that own rooms and include some information about each room that they own. There are 4 different types of joins, inner, left, right and outer. To understand the difference, you first need to understand that when you make a join, you're reading data from 2 different tables. Guess. Think of users as the left table and rooms as the right table. When you perform a join, you'll be matching the user ID to the owner ID on a room. Goo so the relationship will only exist between certain rows on each table. The type of join you use will determine whether or not data with a corresponding relationship will still be returned in the query. Let's take a look at a few examples. 1st, we'll say select everything from the users table, then run an inner join for rooms on the condition where the room owner ID equals the user ID. That gives us a result set with 4 rows in it, one for each room that has a corresponding owner. In this case, the owner is the same for every room, but each row has also been joined with the room data. Now in some cases, you may want to fetch all of the users even if they don't have an associated room. You can do that with a left join. This time, the result set has 3 more rows for the other users, but all the room data is just null. As you might imagine, a right join is the exact opposite of guess. It would return all the rooms that don't have an associated owner. And, lastly, you have a full outer join, which actually isn't supported in MySQL, but it would look something like this in other guid basis. Now one thing you'll notice in the result set is that when we have conflicting column names in the join, MySQL will automatically rename them for us, like it did with users ID and rooms ID. You can easily change that by selecting a specific column, then use the as operator to cast it to a different name. Let's use underscores to make the name more friendly with programming languages that use dot notation. That's pretty cool, but there's one more big question that we need to answer with our data model, and that is how does a user book a room from another user? We can model that relationship by creating a 3rd intermediate table called reservations or bookings. Most importantly, a row in the bookings table will have a check-in time along with a guest ID or user ID and the room ID, both of which are foreign keys. That allows us to create complex relationships where a user has reserved many rooms through the bookings table. Or on the other side, a room has many past guests through the bookings table. Once we have that data model established, we can then run joins through the bookings table. For example, we can get all the rooms a user has booked by joining rooms to bookings and filtering by the guest ID. Or we can get a history of all the guests who stayed in a room by joining users to bookings and filtering by the room ID. And that's all you need to know about databases to build a multi $1,000,000,000 startup. Actually, wait. There is one last thing I forgot to show you, but you need to be careful with this one because if you use it incorrectly, you could delete your entire database and lose a lot of money. A drop statement allows you to delete data, like an individual table or an entire database. In fact, goo YouTube uses my SQL under the hood, so I should be able to delete this video by clicking
        