Comprehensive SQL Course: From Basics to Advanced Database Design

SQL Tutorial - Full Database Course for Beginners In this course I’m going to teach you everything you need to know to get started using SQL. Now SQL is a language which is used to interact with relational database management systems.

And a relational database management system is basically just a software application which we can use to create and manage different databases. And so, we're going to talk about all of this stuff in this course.

We’re going to start off with the basics. So, we'll just look at what is a database. We’ll talk about the different types of databases.

We'll talk about what SQL is and what it actually means and how you can use it to create databases. And then we're going to go ahead and we're going to install something called a relational database management system.

Which like I said, is just software that we can use to manage a database. We're going to install a relational database management system called MySQL. And MySQL is one of the most popular database management systems for beginners.

And also, just in general. So, MySQL is a great first system to learn. And so, once we have that all install, then we'll start writing SQL.

So, we can write out little SQL code, little queries in order to create databases and create database tables and, you know, input information, retrieve information.

And then we're going to get into writing SQL queries. And queries are used to query a database. So, we'll create a database.

We’ll populate it with information. And I’ll show you guys how you can write these little SQL queries to get specific pieces of information. So, we'll start off with the basics and we'll just learn all of the fundamentals.

And then I’m going to show you guys some more advanced techniques to getting information out of a database. And finally, I’m going to show you guys how you can actually design database schemas.

So, a database schema is basically just like all of the different tables and all the different relations that the database is going to store. And if you don’t understand what any of that means, don’t worry, we're going to cover all of it.

But this course will cover basically everything about SQL, all of the fundamentals, all of the things that you need to get started. And we'll also look at database design and schema design.

So, it’s going to be a pretty awesome course. I’m excited to be bringing you guys this stuff. And just know that all of the code, all the stuff that’s in this course

is going to be available on the Giraffe Academy website. GiraffeAcademy.com. And there’s also going to be some additional things on the website

that isn’t going to be in course just because I didn’t have time to cover all of it. So, check out the website for more information. But without further ado, lets get into it.

Let’s learn SQL which is one of the most popular languages for not only jobs, but just for developers in general. [What is a database?]

In this video I’m going to give you guys a full introduction into databases. So, we're not going to get too specific on any one point. Basically, I want to show you guys just what databases are in general.

We're going to talk about how databases are used, why we use databases. And the different types of databases that you’ll find out in the world. So, this should basically just give you an introduction to databases in general.

If you don’t know anything about databases, then this video will kind of walk you through and get you up to speed with what you need to know so you can then go off and start learning how to use an actual database.

So, I just have a little PowerPoint slide here that I want to kind of walk you guys through. And we'll look at some different features of databases. So, the first question is, “What is a database?”

You’ll often see databases abbreviated as DB. So DB would stand for database. And, a database is any collection of related information.

What I’m sharing with you guys here is the absolute most general definition of this word. A database really, if you want to get down to it, is just any collection of related information. So, that could be something like a phonebook,

a shopping lists, a to-do list, your five best friends, Facebook’s userbase. All of these are examples of databases. All of these are collections of information that store related stuff, right?

So, the phone book, right? This stores people’s phone numbers. Their names and their phone numbers.

It’s a collection of related information. A to-do list – this stores a list of things that you want to do during the day, right? It’s related information.

Your five best friends. That’s information. It’s information that you’re storing somewhere.

You have – you know, if I came up to you and asked you who your five best friends were, I’m sure you could list them off. That, in essence, is a database.

It’s a collection of related information. And then Facebook’s userbase, right? So, all of the users that Facebook keeps track of and stores.

That’s another example of a database. So, databases can be stored in different ways. So, you could store a database on paper.

If I had a shopping list or a to-do list, I might just scribble that down on a piece of paper. You could store a database in your mind. So, your five best friends, for example.

If I came up to you and I asked you, “List off your five best friends.” And you probably don’t have it written down somewhere. You don’t have an app on your phone that tells you who all your friends are, right?

You just know that information in your mind naturally. And so, that’s another way that you can store a database. You can just store the information in your mind.

You can store information on a computer. And this is probably the most common use case is people will create a database and they’ll store all the information on their computer.

This PowerPoint, the PowerPoint that we're looking at right now. This is an example of a database. It has related information on it.

And I’m using it to teach this lesson. And then finally like a comment section. So, if you go down to the comment section of this video, that’s a database, right?

It’s a general database that’s storing comments for the video. So, that really, in essence, is everything you need to know about databases to get started. It’s a collection of related information that can be stored in different ways.

So, now that we understand the very general definition, I want to kind of walk you guys through more specifically what we can do with databases. So, over here I have another slide.

It’s Computers + Databases equals heart. Now, the point I’m trying to drive home with this slide is that storing a database, storing a collection of related information on a computer is extremely useful.

And computers are actually great for storing databases. So, I have here two things. We have over here on the left Amazon vs a Shopping List.

So, these are two examples of situations where we have a database. We kind of talked about how a shopping list is a database. It’s a collection of related information, right?

A collection of products that you want to buy from the store. Amazon.com is also a database. Amazon is storing all this product information, all this user information.

They’re storing reviews on the products, the prices of the products. They’re storing all of this information. So, let’s take a look and we'll compare and contrast these two databases

and we'll see why computers are the preferred medium for storing data. So, Amazon.com keeps track of products, reviews, purchase orders, credit cards, users, media, etc. So, Amazon is keeping track of so much information.

Trillions of pieces of information need to be stored and readily available. Over here we have a shopping list. A shopping list keeps track of consumer products that need to be purchased, right?

We're talking 10 to 20 pieces of information need to be stored and readily available. So, Amazon, we need to store trillions of pieces of information. With the shopping list, we need to store 10 or 20.

Both of these are databases, but the one we have trillions of stuff. The other one we have 10 to 20 things. And so, over here on Amazon, the information on Amazon is extremely valuable

and it’s critical to Amazon.com’s functioning, right? So, the information that Amazon is storing it’s database is absolutely essential. And another thing is that security is essential, right?

Amazon stores people’s personal information, like social security number or credit card, address, phone. Like that information needs to be locked down and secure. A shopping list on the other hand, the information is for convenience’s sake only, right?

It’s not absolutely necessary for shopping. Like if you didn’t have a shopping list, you could still go to the store and, for the most part, you’d be able to find everything that you need.

Also, with the shopping list, security is just not important at all, right? If you drop your shopping list on the ground – I mean if somebody else was to pick it up and look at it, it’s like not a big deal.

It’s a shopping list. Finally, over here on Amazon, the information is stored on a computer. Whereas with the shopping list, the information is stored maybe on a piece of paper,

or it could even just be stored in someone’s memory, right? So, maybe you just memorized your shopping list and that’s where you’re storing your shopping list database.

So, what I’m trying to show you guys is that a database doesn’t just have to be on a computer and it doesn’t just have to be like in your memory or on a piece of paper, right? Databases are in all of these different environments.

But here’s the thing, for an application like Amazon, storing information on something like a computer makes it really easy to store trillions of pieces of information. And really easy to secure the data and make sure that security is taken care of.

And it also makes it really easy to back up that information and duplicate that information and store that information on different computers. So, the main point of this slide is that computers are great at keeping track of large amounts of information.

And so, going forward, we're going to be talking about how can we create databases on the computers because computers are so good at storing that information. So, let’s talk about how we can go about creating databases on a computer.

Now, a database could be as simple as like a text file where you store information. Or it could be like an Excel file, right? Microsoft Excel file.

But generally, if you’re going to be using a database with an application or you’re going to be using a database to store huge amounts of information, a lot of times what people will do is they’ll use special software

which is designed to help you create and maintain the database. This is called database management systems. So a database management system is a special software program

that helps users create and maintain a database on a computer. So, it makes it really easy for us to manage large amounts of information. So, if you’re a company like Amazon and you have trillions of pieces of information

that you need to keep track of, well, the database management system can make it pretty easy for you to store trillions of pieces of information, right? It’s not like all that information is just in like some single text file.

The database management system will make it really easy for you to store that. Database management systems can also handle security. So, they can make it so only certain people with the usernames and passwords can access the data.

It’ll also help you to backup your data and import and export data from other sources. So, if you have a bunch of information and you want to back it up, a database management system can help you do that.

Database management systems can also interact with software applications. So, you know, Amazon.com is a website. And it’s interacting with the Amazon database

which is stored mostly likely using a database management system. So, you could write a program that could interact with the database management system. All right, so let’s take a look at this quick little diagram that I have here.

So, we have Amazon over here. And this would be like Amazon.com. And Amazon is communicating with a database management system.

And I don’t work for Amazon so I’m not 100% sure exactly how they're doing this. But for the most part, this is the general use case. So, we have our database management system which is this little box here.

And the database management system is creating and storing and keeping track of a database. So, the database management system isn’t the actual database. The database management system is the software application

that is creating, maintaining, updating, deleting information from the actual database. So, Amazon.com will interact with the database management system in order to create, read, update, and delete information.

So, Amazon isn’t creating or reading or updating this information directly. Amazon is telling the database management system to do that for it. And by going through the database management system,

we can be sure that all the data is getting stored correctly and there’s going to be no problems with the data. So, let’s talk about C.R.U.D.

And this is an acronym that you’re going to hear a lot. C-R-U-D. It stands for Create, Read, Update, and Delete.

You’ll also hear people call this Create, Retrieve, Update, and Delete. Now, CRUD represents the four main operations that we're going to be doing with the database. So, you’re going to be creating information in the database.

So, creating new database entries. You’re going to be reading information from the database. So, you know, retrieving or getting the information that you already stored in there.

You’re going to be updating the existing information. And you're going to be deleting the information that’s already in there. And these are the core four operations that we want the database management system to perform for us.

So, any good database management system is going to be able to do all four of these things. So, now that we've talked a little bit about database management systems, I want to talk to you guys about the two main types of databases that you'll find in computing.

So, the first is called a relational database. You’ll hear people refer to these as SQL[Sequel] databases or S-Q-L databases. And then we also have what are called non-relational databases.

And you’ll hear people refer to non-relational databases as no-SQL or not just SQL databases. We're going to talk about what SQL is in a second. But I want to show you guys these two types.

So, a relational database which we have over here on the left, organizes data into one or more tables. So, each table has columns and rows and a unique key identifies each row. Now, relational databases are by far the most popular types of databases.

And relational databases are going to store everything inside of these tables. So, we'll organize all the data that we want to store inside of these predefined tables and then we can just insert information into there.

A relational database is a lot like an Excel spreadsheet. So, if you’ve ever used a spreadsheet where we have columns and rows and we're storing information, that’s essentially what a relational database is.

And then over here on the right we have non-relational databases. And a non-relational database is basically just any type of database that’s not a relational database. So, like I said, relational databases are by far the most popular type.

And so, because they’re so popular, any other type of database that’s not technically relational is just referred to as non-relational. And non-relational databases, you know, it’s very general.

Because any database that’s not relational is getting put into this category. So, non-relational database is organized database in anything but a traditional table. So, you’ll have things like key-value stores documents

which would be kind of like JavaScript Object Notation. Or like XMl. They’ll store data in graphs.

Or even flexible tables. So, really non-relational database is a very general category. It’s just anything that’s not relational.

So, let’s take a look real quick at a relational database. Or you’ll hear people, like I said, refer to this as a SQL database. So, over here, we have two examples of tables.

So, this would be basically how we would store information in a relational database. So, over here I have a student table. And this student table might store individual students.

So, you’ll see over here we have an ID, a name, and a major. So, for every single one of my students, I have an entry just like this. And you'll notice over here, I’m giving each of these entries an ID.

And that ID will uniquely identify that row in the table. And over here, we have a users table. So, maybe you're creating users for your application.

So, they have a username, a password, and then an email, right? So, the username is something that’s going to be unique. It’s something that uniquely identifies each row in the table.

And then we're also storing like password and email. So, we kind of talked about how relational databases store data in tables. And when we want to create a relational database,

we can use a relational database management system. And a relational database management system, or an RDBMS is just a database management system that helps you create and maintain a relational database.

And some of the most popular are my MySQL, Oracle, PostgreSQL, and MariaDB. There’s a bunch of these that are, you know, extremely popular. And relational database management systems use something called Structured Query Language, or SQL.

And SQL is a standardized language for interacting with relational database management systems. So, remember, a relational database management system is just a software application that we can use in order to create, maintain

and do different things to our relational database. And SQL or Structured Query Language is the language that we can use to interact with those relational database management systems.

So, we can use SQL to perform CRUD operations as well as other administrative tasks like user management, security, backup, etc. And we can use SQL to define tables and structures.

So, a relational database uses tables in order to organize its information. And we can use SQL to define those tables. And then insert information into those tables.

And SQL is a standardized language which means it’s pretty much used on every relational database management system. However, different relational database management systems will implement SQL just a little bit differently.

So, not all SQL code that you use on one relational database management system will port over to another one without slight modification. So, now let’s talk a little bit about non-relational databases.

And in non-relational databases is just anything that’s not relational. So, a non-relational database stores data in anything but a, you know, a static table. So, I’m showing you guys a couple different examples of non-relational databases

and how they would store data. So, over here we have a document database. And this would store information inside of like little objects or documents.

So, we're talking about things like JSON or XML. Basically, I think JSON is kind of a very popular format and that’s kind of what I have up here. So, JSON is JavaScript Object Notation.

So, there’s a lot of non-relational databases that will store data in documents just like this. They’ll also store data in graphs. So, over here we have graphs.

And there’s like these little nodes. And then there’s, you know, connecting lines between the nodes which would represent like relationships. And then over here we have a key value hash.

So, we would have a key and it gets mapped to a value. And that could really be anything. It could be a string.

It could be JSON. It could be a graph. It could be anything like that.

So, these are just three common examples of what SQL or non-relational databases might look like or how they might store their data. And just like with relational databases we can have non-relational database management systems.

And these help users to create and maintain non-relational databases. So, some examples would be mongoDB, DynamoDB, apache cassandra, firebase. There’s a bunch of these popular noSQL databases

that have been sprouting up over the last decade or so. And these are all implementation specific. So, unlike relational database management systems where we have SQL

which is just a standard language that can interact with all of them, there isn’t a standard language for interacting with non-relational database management system. So, generally, the non-relational database management system

will implement their own language for performing CRUD and administrative operations on the database. So, now that we've looked at the different types of databases, I want to talk to you guys about some core concepts within databases.

So, one of the most common things that you’re going to be doing are queries. So, a database query is a request that’s made to the database management system for specific information.

So, you’ll hear the word query thrown around a lot. And generally, a query is you’re just asking the database management system to give you some information.

So, as a databases structure becomes more and more complex, it becomes more difficult to get the specific pieces of information that we want. And if you have a very complex database layout or schema,

then getting a specific piece of information can be a little bit tricky. And that’s why we write database queries. So, can write a very complex database query which in a lot of ways is like a program,

if you’ve ever used a programming language before. And that query will then instruct the relational database management system to grab a specific piece or specific pieces of information from the database.

So, a good way to think of a query, is it’s kind of like a Google search, right? So, if I go on Google and I would generally type in the specific information that I want. And then Google would give me the information that matches that search query.

That’s a lot like a database query. Except with a database query we're not going to be writing the query in English like we would with Google.

So, I can, you know, go up to my Google search bar and I can type something in in English or Spanish or whatever language you speak. But with a relational database management system we have to write our queries using either SQL

or a specific language that’s meant for that database management system. So, let’s wrap this up real quick. We kind of covered a lot of the main points with databases.

So, a database is any collection of related information. It’s a very general term. And computers are great for storing databases because computers are really fast.

They can store lots of pieces of information. And they can handle things like security really easily. And database management system make it easy to create, maintain,

and secure a database on a computer. Database management systems will allow you to perform the CRUD operations and other administrative tasks on the database.

There’s two main types of databases, relational and non-relational databases. Relational databases use SQL and they store data in tables with rows and columns. Non-relational databases store data using other data structures.

So, things that aren’t relational database tables. And then we talked a little bit about querying. A query is just a require that you would make to the database management system

for a specific piece of information. So, that is kind of the basics, the overall high-level basics of databases. And really, if you understand everything in this PowerPoint,

then you have a good grasp of database fundamentals. And as we go forward, we're going to learn more and more things about databases like how to create them, how to store data, and how to organize our data so that it’s easy to retrieve when we want it.

[Tables and Keys] In this tutorial, we're going to look at some of the core concepts in relational databases. So, we're going to talk about how we store information in relational databases.

Namely, in tables. Let’s talk about the different things about those tables. We're going to talk about things called keys.

We're also going to look at rows and columns. We'll talk about different tables can be related to each other and how we can define relationships between tables.

So, this will really just give you guys a full introduction into some of the core concepts of relational databases which you can then apply when creating your own relational database using a relational database management system.

So, let’s get started. Over here I have an example of a table. And this is a student table.

And so, the student table defines specific information about a student. So, up here we have the column names. So, the student ID, the same of the student, and then the major.

So, I’m storing three pieces of information about each student, right? They have their ID, their name, and their major. So, over here we have Jack.

His student ID is 1. And he’s majoring in biology. Down here, we have Claire.

Her student ID is 3 and she is majoring in English. So, all tables in relational databases are going to have two things. They’re going to have columns and they're going to have rows.

Now, columns are these vertical sections right here. So, a column would define a single attribute, right? So, we have our name column.

And inside the name column we have the names of all the students. We have the major column. Inside the major column we have the majors of all the students, right?

Makes sense. And then we have rows. And a row is an individual entry in the student table.

So, a row or a horizontal entry would represent a single student, right? So, in a single row we're storing the student ID, the name, and the major. So, the columns represent a single attribute and the row represents an entry or an actual student.

Whenever we're creating a table in a relational database we always want to have one very special column which is called the primary key. And a primary key is basically an attribute which uniquely defines the row in the database.

So, it’s an attribute about a specific entry which will uniquely define it. So, over here, we have student ID. And notice that I colored this in red and I also underlined it.

And this underline is basically going to specify that this column or this attribute of the student is going to be the primary key. In other words, we can use this attribute to uniquely identify a specific row.

So, over here we have Kate and she’s a sociology major and her ID is 1. So, we would say that Kate’s primary key is 1. Now, let me show you guys why primary keys can come in handy.

So, down here, inside of these gold squares I have two entries in this database. So, we have two students, both of which are named Jack. And both of which are biology majors.

Now, this isn’t that, you know, special of a case, right? You can imagine that in a school you might have two students with the same name who are the same major.

But in this case, how can we differentiate between this guy and this guy? Well, we can use this primary key. So, even though the name and the major of this row in the table

are the same as the name and the major of this row, the student ID is different. The primary key is different. And that’s the whole point of a primary key,

is that the primary key is always going to be unique for each row in the table. So, even if all of the attributes of the row are the same, the primary key won’t be. And therefore we can differentiate between the two rows.

So, I can differentiate between this Jack and this Jack by referring to their student IDs. And so, generally, whenever we have a table in a relational database, you always want to define a primary key.

And a primary key can be anything. It can be a number. It could be a string of text.

It could really be anything that you want. But it has to uniquely identify the specific row. So, in other words, another row in this student table couldn't have a primary key of 2.

So, down here I have another example of a database table. This is a user table. So, just like that student table, except it’s storing information about users.

So, over here we have email, password, date created and type. So, email is going to be our primary key. You can see I put that in red and I also underlined it.

And these emails are unique to each entry in the table, right? So, [email protected]. No, other rows in this table could have that exact email.

And then over here we're storing the password. We're also storing a date. So, a lot of times in databases you can store date values or time values.

And then over here we're storing a type. And so, basically whenever you want to store data, what you’re going to do is define a table. So, you’re going to define, you know, all this information up here

and then you'll just insert specific pieces of information into that table. And so, over here we have one more example of a database table. And we're actually going to be looking at this example as we go forward.

So, this is an example of an employee. So, imagine that we have like a company database and we were storing information about employees. So, we have the employee ID which is the primary key.

We're storing first name, last name, birthdate, sex, and salary. And we're storing all of this specific information about an employee. Now, I want you guys to notice the primary key over here.

So, employee ID, this is just a, you know, some sort of random number that we’ve assigned to each employee, right? This employee’s ID is 100.

This employee ID is 101, etc. And this over here, this employee ID, this is what we would call a surrogate key. And a surrogate key is basically a key that has no mapping to anything in the real world, right?

It’s essentially just, you know, like in this case a random number that we assign to an employee, right? So this, employee Jan has an employee ID of 100. That doesn’t necessarily mean anything.

100 is just a value that we're using to represent Jan inside of the database. And so, we would refer to that as a surrogate key, which is just a type of primary key. Surrogate key is a key that has no mapping to anything in the real world.

We can also have something called a natural key. And over here you'll see that I have the same exact table except instead of having employee ID, I have employee SSN.

And SSN stands for Social Security Number. So, Social Security Number is a number that we use here in the United States in order to uniquely identify each citizen.

So, in this case we're using the Social Security Number in order to uniquely identify each row in the table. In other words, we're using the Social Security Number as the primary key of the table. And this is an example of what we would call a natural key.

And this is a key that has a mapping or has a purpose in the real world, not just in the database. So, a lot of times you'll hear people refer to surrogate keys or natural keys and that’s the difference. A surrogate key is a primary key that has no mapping to the real world.

And a natural key is a key that has a mapping to the real world, just like Social Security Number. So, those are kind of the two different types of primary keys that you might see being stored. Another thing I want to talk to you guys about are what are called foreign keys.

And a foreign key is basically an attribute that we can store on a database table that will link us to another database table. So, over here I have this same exact employee table.

And then I also have this other attribute over here, branch_id. And you notice that I colored this in green. And this is what we would call a foreign key.

And a foreign key stores the primary key of a row in another database table. So, here we have our employee table and I’m defining information about the employee. But let’s say that an employee belongs to a specific branch in our company, right?

So, a company might have different branches. And we can store the information about what branch the employee belongs to inside of a foreign key. So, the foreign key over here is actually a primary key inside of another table.

In our case, a branch table. So, let me show you guys that. So, down here, we have this branch table in our database, right?

So, the branch is it’s own separate table. And we have – the branch has a primary key over here which is 2, 3, 1. And the branch names are Scranton, Stamford, and Corporate.

So this in itself is its own table, right? It’s the branch table. And over here we can define which branch a specific employee belongs to,

by referring to the ID, the primary key of the branch. So, here, Jan Levinson, her branch ID is 1, which means she is in the corporate branch, right? Because branch ID number 1 is corporate.

Michael Scott, his branch ID is 2, which means he’s in the Scranton branch. So this number is mapping this row over here into this other table. And that’s what a foreign key does.

A foreign key is essentially just a way that we can define relationships between the two tables. So, a foreign key is just a primary key of another table. So, Andy Bernard, right?

His branch ID is 3 which means he belongs to the Stamford branch. And so, that’s what a foreign key does. A foreign key allows us to link up or define relationships between tables.

I can say that Andy belongs to the Stamford branch and I can define that using the foreign key. And over here you'll see on the branch table, I also defined another foreign key which is manager ID. mgr_id, that would stand for manager ID.

And now this is actually going to be a foreign key which connects branch to the employee table. So, manager ID is going to be the ID of a particular employee who is the manager of the branch. So, let’s take a look at the Scranton branch.

So, the branch ID is 2. The name of the branch is Scranton, and the manager ID is 101. So, let’s go check it out.

So, over here in the employee table, employee 101 is Michael Scott. What that means is that Michael Scott is the manager of the Scranton branch, right? We were able to define that relational by using these foreign keys.

So, down here, the Stamford branch, the manager is 102. If we come up here, we can see employee 102 is Josh Porter. So, Josh Porter is the manager of the Stamford branch.

Corporate over here, the manager ID is 108. So, over here we didn’t actually include 108 over there. So, obviously, this isn’t like, you know, a huge table of employees.

But the manager for the corporate branch would be the employee with the ID of 108, whatever that would be inside of this table. So, that’s what a foreign key does.

A foreign key is able to help us to define relationships between the tables, right? I can define which branch a specific employee belongs to by including the branch’s ID as a foreign key in the employee table.

Similarly, I can define which employee is the manager of a branch by including that employee’s ID as a foreign key in the branch table. And so, that’s really why foreign keys are going to come in handy.

And it’s also important to note that a particular table can have more than one foreign key on it. So, ever here I have my employee table, right? And it’s the same exact thing as we have before.

But I also added another attribute or other column over here which is super_id. And that stands for supervisor ID. And so, what this is going to do is it’s going to define who is the supervisor of a particular employee.

And what’s cool about this foreign key is it’s actually relating back to the same table. So, an employee can be a supervisor of another employee. So, over here we have Angela Martin, right?

Her employee ID is 103. And her supervisor ID is 101. And that means her supervisor is the employee with the ID 101.

So, if we come over here we can look. Angela Martin’s supervisor is Michael Scott because Michael Scott has an employee ID of 101. So, Michael Scott.

Michael Scott’s super ID is 100. That means Michael Scott’s supervisor is the employee with ID 100, which is Jan Levison. So, we can these this foreign key over here on the employee table

to define relationships between employees. So, before we define the relationship between the employee table and the branch table, and now we're defining a relationship between the employee table and the employee table, right?

And so, what’s cool about this is then basically inside of this table, we can tell what the different supervisor or supervisee relationships are, right? I can tell that Josh Porter’s supervisor is Jan Levinson.

And I can tell that Angela Martin’s supervisor is Michael Scott. Defining that information by using a foreign key. All right, so let’s take a look at another table.

I actually added in another table down here. So, over here we have employee, just like we had before. We have branch.

And then we also have branch supplier. And branch supplier is another table that we can look at. So, a branch supplier would basically define who the suppliers are for specific branches.

So, we have these branches. Maybe they're selling a certain product. Maybe they're selling like paper products.

And the branch suppliers would define who are the suppliers for that branch. And you'll notice up here, the primary key actually consists of two columns. And this is what we would call a composite key or a composite key.

And a composite key is basically a key that needs two attributes. So, this key is made up of two columns, right? It’s made up of branch_id and supplier_name.

So, branch_id is actually going to refer to the specific branch. And supplier_name is going to refer to the specific supplier, right? So, we can say over here that Hammer Mill supplies paper to branch number 2.

So Hammer Mill supplies paper to Scranton. Uni-ball supplies writing utensils to branch ID number 2, which is Scranton. Patriot Paper supplies paper to branch ID number 3 which is Stamford.

So, inside of this branch supplier table, I’m able to define which different suppliers are supplying what to which different branches. Now, the reason that I need this composite key or this composite key,

is because the supplier name doesn’t uniquely identify each row. And the branch_id doesn’t uniquely identify each row. Only together can they uniquely identify each row, right?

So, for example, supplier name, Hammer Mill. You’ll notice that Hammer Mill shows up here and it also shows up here. So, Hammer Mill supplies to branch number 2.

And they also supply to branch number 3. Uni-ball supplies to branch number 3 and Uni-ball supplies to branch number 2. So, this column has repeated values, right?

This column has Hammer Mill repeated. It has Uni-ball repeated. branch_id also has those things repeated, right?

So, branch_id 2 shows up here a bunch of times. branch_id 3 shows up here a bunch of times, right? And so, the branch_id column can’t uniquely identify the table.

And the supplier name column can’t uniquely identify the table. Only together can they uniquely identify it, right? So, we would say Hammer Mill supplies branch 2.

That combination only shows up once. We wouldn't put that combination again because we’re already defining it. So, Hammer Mill supplies branch 2.

Hammer Mill supplies branch 3. These two rows are uniquely identified by the identified by the supplier name and the branch ID. And so, over here in these employee and the branch table, we only find one column as the primary key.

But over here, we define two columns as the primary key, which would be a composite key. And that’s actually pretty common, where we're going to have two columns uniquely identifying each row. All right, so down here I want to show you guys one more example.

And this is going to show you one other way that we can define like different relationships with these tables. So, I actually added in two other tables. We have a client table and we have this Works With table.

So, let me kind of walk you guys through this a little bit. We still have employee. We still have branch. But over here, we're defining clients.

And so, a client might be like a customer, right? So, a client would buy paper products or, you know, whatever products from the branch and employee. So, the client has a client ID.

They have a client name. And then they also have a foreign key which is a branch ID. So, a client is going to be associated with a specific branch, right?

So, we would say Lackawana County is associated with branch 2. Which means the Lackawana County is going to buy products from the Scranton branch. The John Daly Law, LLC is going to buy products from branch ID number 3.

So, they're going to buy products from the Stamford branch, right? So, this is a client table. And down here, we have this Works_With table.

And what the Works_With table is doing is it’s defining the relationships between the employees and the clients. Namely, how much paper an employee sells to a specific client.

So over here we have employee ID. We have client ID. And we have total sales.

So, the employee ID is going to refer to an employee in the employee table. The client ID is going to refer to a client in the client table. And then we're going to define how much in product the client has bought from the employee.

So, employee 101, for example, that is Michael Scott. Michael Scott has sold client ID 401. Michael Scott has sold Lackawana County $267,000 worth of product, right?

How about this one, 104. So, Andy Bernard has sold client number 403 – has sold John Daly Law $5000 in paper products. And so, this Works_With table is able to define how much an employee has sold to a client.

And you'll notice over here we have a composite key. And the composite key is employee ID and client ID. And this is actually a special type of composite key

because both of these columns are actually foreign keys. So, employee ID is a foreign key, right? It relates to the employee table.

Client ID is also a foreign key. It relates to the client table. And both of those foreign keys together makes up the primary key of the table.

And that is actually a very useful way to define a primary key. Because when we do something like this, we can define a relationship, right? So, I can define how much product the client has bought from the employee.

And that is something that can because were useful to keep track of. So, as you can see, like these tables can either be very simple or they can be very complex. And the more complex your database design or your database schema is,

the more complex these tables and these keys are going to have to be, right? So, this employee table has a primary key and two foreign keys. The client table has a primary key and a foreign key.

This Works_With table has a composite key. You know what I mean? Both of which are foreign keys.

Like it can get very complex, but we can use the primary keys and the foreign keys in order to define different relationships. But really, that is just a, you know, I think a pretty good introduction into kind of how tables work.

We talked about rows and columns. We talked about primary keys. We talked about surrogate keys and natural keys.

You know, the different types of keys that might map to the real world or not. We talked about foreign keys. We talked about composite keys.

And I kind of showed you guys a few different examples of ways that things might work. So, this would be like our company database. And obviously, we would have a lot more information in here.

But this is kind of a good example of how we might define different tables and then define the relationships between those tables in a relational database. [SQL Basics]

In this tutorial I’m going to walk you guys through SQL which stands for Structured Query Language. So, SQL is a term that you're going to hear thrown around all the time when you're talking about databases.

And SQL actually is a language. And it’s a language that’s used for interacting with relational database management systems. So, SQL is – it’s similar to – kind of like a programming language.

A lot of times you’ll hear people refer to SQL as a programming language. It’s not technically a programming language in the tradition sense. Although, you can use SQL to provide instructions to a relational database management system.

So, I guess if you want, you could call it a programming language. So, like I said, SQL, it’s a language that’s used for interacting with relational database management systems.

A relational database management system is a piece of software. It’s a software application that you can use to create and maintain a relational database. You might use a relational database management system

to create a database for an application that you're building. And then that relational database management system can make sure that the database is structured correctly

and store everything the way that it needs to be stored. So, in order to interact with a relational database management system, we need to use a special language.

So, I can’t just talk to it in English. I can’t just be like, “Hey, relational database management system, get me this piece of information.”

Relational database management systems don’t speak English. But what they do speak is a language called SQL. They speak Structured Query Language.

And so, if we want to ask a relational database management system to do something for us, for example, like store a piece of information or create a table, update a piece of information. We can ask the relational database management system to do that using SQL.

So, SQL is the language that we can use to communicate with the database management system. And, you know, we can use SQL to get the relational database management system to do stuff for us, like create, retrieve, update, and delete data.

Create and manage different databases. Design and create database tables. So, we can define like a database schema which would just be like the overall like table design.

And perform administrative tasks. So, things like security. We could do like user management, importing, exporting, backup, all that stuff.

So, SQL can be used to tell the relational database management system to do all of that stuff for us. Now, here’s the thing, SQL implementation can vary between the systems. So, SQL, the actual language actually does have a formal specification.

So, there is like an overall formal specification which defines, you know, how SQL needs to be used and all the different commands that can be used. But the problem is that there’s a bunch of these relational database management system.

Some of the popular ones we hear about, like Postegres, MySQL, Oracle, Microsoft SQL Server. Like all of these relational database management systems are going implement SQL just a little bit differently.

So, you could write SQL code that would work on one relational database management system, but then if you tried to use it on another one, it might not work 100%. Now, for the most part, everything should be the same,

but you might just need to tweak a couple little things. So, one thing you want to keep in mind with SQL is that, you know, SQL is used on all of the major relationship database management systems,

but it’s used slightly differently. So, certain instructions might work on one database management system and they might not work on another, or vice versa.

Or you might do things just a little bit differently, depending. But for the most part, they’re all implementing SQL which means it’s all basically the same. So, the concepts are the same but the implementation may vary just a little bit.

So, let’s talk a little bit more about SQL. It’s actually a hybrid language. So, it’s basically four types of languages in one.

And you know, you don’t need to worry too much about all this stuff, but you are going to hear people talking about these different aspects of SQL. And so, I want to introduce them to you so you understand what they are.

So, SQL, you know like I said, it’s four types of languages all mashed into one single language. So, SQL is a data query language, which means it can be used to query the database for information. So, you can write queries in SQL which tell the relational database management system

what pieces of information that you want to get from the database. And so, a data query language is used to get data that’s already stored in the database. SQL is also a data definition language.

And what that means is you can use SQL to define database schemas. Now, a schema is basically just like the overall layout of the database. Like what tables are going to be in the database, what columns those tables are going to have,

and the data types that those columns are going to be able to store. So, we can use SQL to define data in the different, like I said, database schemas. SQL is also a data control language which means

it’s used for controlling access to the data in the database. Basically, you can use it to configure like users and permissions. So, I could define a bunch of different users for the database.

And I can say like, okay, this user can write to this table. Or this user can read information from this table. Or this user can update and delete information from this table.

So, you can use SQL to control the access to the data. And SQL is also a data manipulation language. So, it’s used for inserting, updating, and deleting data from the database.

So, these are the four different types of things that you can do with SQL. And you'll hear people using these terms kind of a lot. And even like database management systems might throw error messages or certain things.

And they'll say like data query language or data definition language. So, it is good to just understand these different types of things that SQL can do and how they're broken up into these four like broad types of languages.

But basically, the whole point of this slide is that SQL can do a bunch of stuff. And it’s super powerful. And that’s one of the reasons why it’s used in all these relational database management systems.

So, now let’s talk about queries. And one of the things that we're going to be doing a done of in this course, and you know, if you’re working with databases, you’re going to be doing this all the time,

which is querying. So, query is a set of instructions given to the relational database management system generally written in SQL, that tell the RDBMS what information you want it to retrieve for you.

So, if you have a database that has like millions or billions of records in it, like there’s tons of data. And a lot of times that data is, you know, spread out across different tables, right? It’s sort of hidden in this complex schema.

You know what I mean? Like you have one piece of information stored over here and then another piece over here, another piece over here in different tables.

And you want to kind of grab all that information and organize it in a specific way. We can use queries in order to tell the RDBMS to do that for us. So, the goal of writing a query is that we only want to get the information that we need, right?

Imagine that if you, you know, every time you wanted a piece of information from the database you had to grab all of the information in the database, parse through it, and then find the information that you want.

We don’t have to do that. Instead, you can write a query which will tell the RDBMS exactly what piece of information you want and it’ll be able to grab just that information and send it back to you.

So, over here I just have a little example of a query. So, this would be like maybe we're trying to get – this would be SELECT employee.name, employee.age. So, select the employee’s name and the age from the employee table

where the employee’s salary is greater than $30,000. So, what this will do is it’ll give us every employee in an employee table who makes more than $30,000, right?

That’s kind of like a general query. Don’t worry too much about specifically what all this stuff means. We're going to get into that in this course.

But that’s kind of like what a query would look like. We're telling the RDBMS what information we want and then it only gives us back that information. Not like all the information in the database.

All right, so that is kind of an overview of SQL. I mean obviously I didn’t get into everything. We're going to look at all of these, you know, little specific things

that we can do with SQL as we go forward. But for now, that should kind of give you an idea of what SQL is, why it’s useful, and you know, sort of what it’s doing for us and how we can use it to work with our databases.

[MySQL Windows Installation] In this tutorial I’m going to show you guys how to download and install two things that we're going to need going forward in this course.

The first thing and the most important is called MySQL. And MySQL is a RDBMS. Which basically means it’s a software application

which we can use to maintain and create and do all that sort of stuff with databases. And so, when we have MySQL installed on our computer, we can actually set up what’s called a MySQL database server.

And that would basically be a server where MySQL is running. And then we can write SQL in all sorts of queries and instructions in order to create and do all sorts of stuff with databases.

So, the first thing we'll do is download and install MySQL. And the second thing we're going to do is we're going to install another program which is called PopSQL, or PopSQL.

And it’s basically a program that I’m going to be using in this course in order to kind of like write all of my queries. It’s essentially a text editor.

But it’s a text editor that can hook up with our MySQL database. And it’ll just be a really easy interface and a really easy way for us to write queries and get the information back.

So, I’m going to show you guys how to download and install both of those things. The first thing we want to do is open our browser and we're going to install MySQL. So, you just want to come up here

and you're just going to search in Google for MySQL community server. So MySQL community server is this first link right here. It’s just dev.MySQL.com.

And this is actually a free and opensource piece of software. It’s basically like the most basic version of MySQL. And it’s the version that we're going to be using.

So, if you’re a beginner, this is probably what you want to go for. It’s kind of just like a great environment for you to learn in. It’s pretty simple.

So, down here you have a couple different options. You can download just like a zip or you can download the actual installer. So, you’ll see over here there’s this option for the MySQL installer.

So, you’re going to go ahead and click on that. And then that’ll bring you to this menu over here. And you can see here’s the MSI installer.

So, we're just going to go ahead and download that. And it brings you to this page. It’s asking you to like log in or sign up, but you can just click No Thanks down here.

And it’ll go ahead and start the download. So, this installer is really great because it will kind of configure everything for us and it’ll allow us to pick and choose what MySQL products that we want to actually download.

So, let’s go ahead and accept the license and I’m going to click Next now. Here we have a couple options for how we want to set this up. You can see you can just do like Developer, Default, Server only, Client only.

What we want to do is – actually, we're going to do a custom install. Now, here’s the thing, if you want, you can just do the developer default. But that’s going to install a bunch of stuff that we're not actually going to be using.

Or at least that I’m not going to be using in this course. So, I’m going to ahead and click custom because I only need to install a couple things. So, the first thing we’ll do is come down here to MySQL Servers

and I’m just going to click MySQL server 5,700 right here. And I’m going to go ahead and add that over here to the right side. And then I’m going to come down here to applications.

And I’m going to come down here to MySQL Shell. And I’m going to do the same thing. So, I’m just going to click down here to MySQL shell.

I’m doing the 64 bit version. I’m going to add that over here. So, these are the only two things that I’m really going to be using for this course.

But if you want, you can download everything. That way, you just have it. So, I’m going to click Next.

I’m going to click Execute. And this is going to go ahead and install both of those things for us. So, now you can see they’re downloading.

And this shouldn't take too long. They should be downloaded pretty quickly. And then we'll be able to move on.

All right, once those two things are finished downloading, then we can just click next. And we can also just click next through here. And you’ll see it’s asking us what we want.

I’m just going to click standalone, MySQL Server, Classic, MySQL Replication. That’s all we need. And you can just leave all of this stuff, all this Type and Networking stuff as the default.

And then over here, it’s asking us to create a root password. So, whenever we're using this MySQL database server, we're going to have to log into it using an account. So, there’s already a default admin account set up for us which is called Root.

But we're going to have to give this a password. So, I’m just going to type in password as the password. And again, down here.

So, make sure that you remember what this password is because we're actually going to need it later. And if you want, you can add additional users. I’m not going to do that.

So, then we can just click Next. And you'll see there’s an option here. It says Start the MySQL Server at system start-up.

And if you want, MySQL to start whenever you start-up the system you can go ahead and keep that checked. And then I’m just going to click Next.

And click Next. And just click Execute. So, this is going to go through and do a bunch of stuff that we need to do to get this set up.

So, you'll notice that it’s actually, you know, not super trivial for us to set this up. I mean obviously the installer makes it really easy. But MySQL is a complex program.

And that’s why there’s all these little things that we have to keep configuring. But we're almost done and then we'll be able to move on. So, now that everything is finished configuring, we can just click finish and I’m going to click Next.

And we can just click Finish. All right, so once we've finished installing everything, now what we need to do is we're going to actually create a database that we can use.

So, I’m going to come over here and we're just going to come over here into the Start Menu. And you’ll see there’s this option here, MySQL 5,700 command line client. So, we're going to actually go ahead and use that.

And what this will do when I open it up, you’ll see it says Enter Password. So, I’m just going to enter in that password that I created. In my case, it was just password.

And you'll see here we're actually logged in. So, now we're connected to that MySQL server that is running on our computer. So, from in here, what we can actually do is create a database.

So, I’m just going to say create database. And I’m going to give it a name. So, I’m going to call it giraffe.

And then I’m going to type a semicolon. So, what this will do, is it’ll create a database for us called giraffe. Now remember, MySQL is a database management system

which means it’s an application or a software program that’s designed to help us manage various databases. So, in order to start building a database, we need to create one. So, I’m just going to say create database giraffe.

I’ll hit enter. And this is going to go ahead and create that database for us. So, now we've created this database, we're kind of ready to go.

So, for the rest of the course, what I’m going to be doing is teaching you guys like SQL commands. So, we're going to be learning all about this SQL language which is used to interact with MySQL and other database management systems.

Now, if you want, you can do everything that I’m doing for the rest of the course here inside of this terminal. So, inside of this little terminal window you can basically do everything that I’m doing.

You can type in SQL like this up here. Create database. That’s all valid SQL.

But I’m actually going to be using a different program which is called PopSQL. And that program just makes it a lot easier for us to visualize what’s going on. It’s not like some kind of boring terminal window.

So, that’s basically a text editor which will connect to our database server and we'll be able to write our SQL from inside of there. So, I’m going to show you guys real quick how to download that.

You’re just going to come up here. I’ll make a new tab. And we're going to come up to the search bar and just type in P-O-P S-Q-L, PopSQL.

And you'll see this link here, PopSQL.io. So, we're just going to click that. So, here there should be an option to download for Windows

so we can just click on that and that’ll start downloading it for us. So, PopSQL is cool. Actually, it’s kind of like Google Docs but for writing SQL queries.

Now, we're not going to be using that functionality. We're just going to be using it as a text editor. But it’s a great way for us to visualize like the SQL queries that we're writing

and then also like what gets returned from those queries and stuff like that. So, once that’s done downloading, we can just click on it and we're going to go ahead and run the installer. When that’s done installing, it should open up right away.

And you’ll see we have to sign in. So, you can either create an account just with Google or you can create an account with your email, but you have to sign in in order to use it.

So, I’m going to go ahead and sign in. And you’ll see it says, “Welcome, Mike.” So, we're able to sign in.

And I’ll open the app. So, now that we have the app open, what it’s asking us to do is connect to a database. So, remember, we set up our MySQL database server and we created a database.

So, what we can do now is we can connect to that database from inside of PopSQL. So, over here, it’s just asking us to type in our nickname, so I’m just going to type in Giraffe. And the type, if you click down, you’ll see we have MySQL right there.

Host name – so the host name is actually going to be localhost. And that just refers to like the local address of the computer that you're currently on. Port is going to be 3306.

Assuming that you used all the defaults when you're installing MySQL, then that should be the port number. And then finally down here, the database we want to connect to was called giraffe.

So, that’s what I named it. If you named it different from giraffe, but in the name there. And then username is going to be root and the password is going to be the password that you set up.

So, in my case it was just password. So, once all that information is in, we can just click connect, and you'll see it’s connected us to our database.

So, now we basically have a text editor that we can use, which is hooked up to our database so we can write all of our SQL code, all of our SQL queries in here. And it’ll actually get run on our MySQL database server.

So, now everything is set up. And we're ready to go off and learn some awesome SQL commands. Now, I realize that this was kind of like a little bit of a complex setup.

Unfortunately, that’s just unavoidable because database management systems are, by nature, you know, complex programs. I mean this isn’t – they’re not designed to be like extremely user friendly, so.

But now we're ready to go and everything is set up. [MySQL Mac Installation] In this tutorial I’m going to show you guys how to download and install two things

that we're going to need going forward in this course. Now, the first thing we're going to download and install and the most important is MySQL. And MySQL is a relational database management system.

And it’s actually one of the most popular relational database management systems around. And a lot of people use MySQL as they're first sort of dip or they're first dive into a database management system.

So, for that reason, I’ve picked MySQL as the relational database management system that we're going to use in order to learn SQL. Now it’s important to note that the focus of this course isn’t so much on MySQL as it is on SQL.

So, SQL is Structured Query Language. It’s an actual language. And MySQL is a relationship database management system.

So, we're going to be using MySQL in order to kind of learn about and use and learn all the most common stuff with SQL. The second thing we're going to install in addition to MySQL is actually going to be optional.

You don’t have to install it, but it’s going to be a text editor that we can use in order to write our SQL commands. So, it’s called PopSQL.

And it’s an awesome program which basically allows you to write out SQL statements. And then you can execute them from inside the program and it’ll show you all the results that you get back.

So, I’m going to be using PopSQL in order to kind of visualize everything that we're doing and just make it a lot easier for you guys to follow along. So, I’m going to show you guys how to download both those things so you can follow along with me.

First thing we're going to do is head over to our browser and we're going to install MySQL. So, you just want to type in MySQL community server. And we're just going to type that in to Google.

And then down here, this should pop up. It’s just download MySQL Community Server from dev.MySQL.com. So, MySQL Community Server is a free and opensource version of MySQL that anybody can use.

And if you’re just starting off and learning about SQL and learning about databases, then this is a great starting point. And then all we have to do down here is scroll down

and you’re going to see that there’s a couple different options here. Basically, what we want to download is the DMG Archive. So, it’s just this first one right here.

So, I'm just going to go ahead and click Download. And that’s going to start downloading for us. Now, it brings you to this page.

It’s kind of asking you to log in or sign up, but you don’t have to. You can just click No thanks, just start my download. And then it’ll start the download for you.

So, this is – it’s probably around like 400 megabytes. It’s not too too big of a file, but it’s also not like a super small file. You’re probably going to need at least over a gigabyte of storage on your computer

to hold everything that SQL is going to need. All right, once that is finished downloading, then we can just click on it and we're going to go ahead and install MySQL onto the computer.

All right, so here we just have this like PKG. So, I'm just going to double click on this. And it’ll open up this installer.

So, we're just going to click through. And you can basically just leave everything as the default. Just keep it in the default install location and it’s asking me for my password.

All right. So once that is done running the installer, this window right here is going to pop up. It says, MySQL Installer.

It’s giving me this date. And it says “A temporary password is generated for root@localhost.” Now, when we're using MySQL

we’re actually going to have to log into the SQL server using a username and password. And in order to log in, you’re actually going to need this temporary password

that they provide for you right now. Now, it’s root@localhost. So, root is the actual username that we're going to use to login.

Localhost is going to be address of the SQL server that’s running on our computer. And then this is going to be like a temporary password. So, what you want to do is just copy this.

You want to make sure that you keep this. So, what you could do, is you could just click Command+Shift+4. And you'll see this little like thing comes up.

If you just drag around here, that’s going to screenshot this for you. And so, now we'll have a picture of this on your desktop. Point is, is you don’t want to lose this.

So you want to make sure that you have this temporary password accessible. If you don’t, then it’s going to be a problem because you're not going to be able to get into the account. So, make sure that you record that.

And then I’m just going to click Okay and then we're done here, so we can close it. And I’ll move it to trash. All right, so once we've installed MySQL, now what I want to do is set up our MySQL server

which basically means that we're going to be running MySQL on our computer. Now the way this works is MySQL is a relational database management system. And it’s basically going to act as like a little database server for us.

And so, we can connect to MySQL. We can login and then we can manage the database from there. So, we need to make sure that the MySQL server is started on our computer.

So, I’m just going to come up here to the search bar. And I’m just going to search for System Preferences. And then down here there should be this little icon for MySQL.

So, we're just going to click on that. And you’ll see it’s going to open up this window over here. So, it says, “MySQL Server Status.

The MySQL Database Server is currently stopped.” So, what we want to do is start it. And I’m going to put my password in.

And then if you want, you can have it automatically start when you open your computer. It says, “Automatically start MySQL Server on Startup.” If you want to do that, you can.

You don’t have to. So, basically, now a MySQL database server is running on our computer. And what we can do now is we can connect to that database server

and we can log in and then we can start creating our databases and creating all of our tables and stuff like that. So, now that this is set up, there’s actually a couple things that we have to do.

The first thing I want to do is update our password. So, we were given that temporary password. So, I'm going to open up the terminal.

And you can just go over here to the search bar and type in terminal. And that’ll open this guy up right here. So, from in here, we need to start configuring a couple different things.

First thing we need to do is get this terminal to recognize the MySQL command. So, if I come over here and I try to type in MySQL, you’ll notice that it says command not found. Basically, our Mac doesn’t know about this MySQL command because we haven’t told it where it is.

So, there’s a couple things that we have to do in order to get this MySQL command to work. So, all we have to do is basically just tell our computer where we installed MySQL. So, what I’m going to do, is I’m just going to type this over here

and you guys can type exactly what I’m typing. Echo single-quote, export PATH in all caps is equal to /usr/local/mysql/bin. And then you’re going to type a colon$PATH in all caps.

Just like that. And then you're going to end off the quotation mark. So, you’re going to do single-quote.

And then two greater than signs. And we're just going to say ~ which is this little tilde character. /.bash_profile.

So, what this is doing is it’s actually adding this line inside of this file called .bash_profile. Basically, what this will do is it’ll make it so we can use that MySQL whenever we want inside the terminal. So, I'm just going to hit Enter.

And then I’m just going to .tilde/.bash_profile. This will kind of reload that file. And now what you want to do is type in MySQL.

And we'll see if this works. So, you notice now when I type in MySQL, instead of saying the command wasn’t found, we got this error message that says,

“Access denied for user ‘giraffeacademy’@’localhost’ (using password: NO). What this means is that the MySQL command is now working. So, basically we're able to give commands to MySQL.

We're able to do stuff like that. So, I’m just going to clear this out. And now what we want to do is we want to actually connect to the MySQL server

that’s running and we want to log in. So, I can just type MySQL just like that. And you're going to type hyphen u.

And you're just going to type root. And then you’re going to type hyphen p and your going to hit enter. And this is going to ask you to enter a password.

And so, now is the time where we want to enter in that temporary password. So, in my case, I took a screenshot of it. So, that screenshot is now going to be stored on my desktop.

It’s over here. And so, what I can do is I can just look at this screenshot now and I can type in the password. So, I'm going to go ahead and type in that password.

All right, so now that I’ve typed in that password, I was able to log in. So, basically now we are logged into the SQL server, or to the MySQL server that is running on our local computer.

So, what we can do now is we can actually change that temporary password. So, I’m just going to type in the following command. ALTER. So ALTER USER.

And then I’m just going to type in ‘root’@’localhost’. And you'll notice that I have single quotes around root and single quotes around local host. So, then I’m going to type in identified by – so I’m just going to type in a new password.

I’m just going to set it as password just so it’s easy to remember. And then you're going to type a semicolon. And now you want to hit enter.

And so, what that’s going to do, is its going to update your user information. So, now you can actually login using something other than that temporary password. So, you'll be able to login using the password that we set over here.

So, now what we want to do is just make sure that everything works. So, I’m going to type in exit. Just like that.

And I’m going to clear this out. And now what we want to do is try to log in again. So, I’m going to say MySQL-the root -p.

And this time I'm going to enter in the new password which was password. And you’ll see that I’m able to login. So, as long as that worked and you’re able to log in,

then you updated your password, so now you're going to be able to get in, no problem. So, the next thing I want to do really quick is we're actually going to create a database. So, MySQL is a database management system.

So, what it allows you to do is manage and keep track of different databases and the information that are in those databases. So, what we want to do before we can actually get started is we want to create an actual database, right?

So, what we can do is we can just type out create database. And then I’m just going to name this database giraffe, just like that. So, I’m creating a database named giraffe.

And you can name your database whatever you want. Generally, it’s going to correspond to the type of data that you're storing in it. I’m just going to be using this as a general purpose database to go throughout this course.

But we can just say create database giraffe. And this will create a database for us called giraffe. And then you’re going to type a semicolon after that.

And then you're going to click Enter. So, now we actually have a database called giraffe that we can use. All right, so once you’ve done all that, we're actually done here inside of the command line.

Now, here’s the thing. If you want, you can actually write out all of your SQL commands. You can do everything that I’m going to be doing in this course here inside of this terminal.

So, you can interact with the MySQL database server just from here inside the terminal. You can write SQL. You can give it SQL commands.

You can do all sorts of stuff like that. In our case though, I’m actually going to be using another separate program in order to do that. And this environment, this terminal environment is not a very good environment

for like visualizing things and seeing things. So, there’s actually another program called PopSQL which I’m going to use. I’m going to show you guys how to download that right now.

But just know that this part is optional. So, if you want, you can do everything from inside of the terminal. So, I’m going to open up my web browser again.

We're going to come back up here. And I’m just going to do a Google search for PopSQL. And this page should pop up It’s PopSQL.io.

So, basically what this is, is it’s a program that we can use to write SQL queries and write SQL code in order to do different things. And PopSQL actually has some really cool features.

It’s kind of like Google Docs but for writing SQL queries. In our case though, we're just going to use it to write out our SQL. So, over here there should be a button to download it for MAC.

So, I’m just going to click that. And then it’s going to go ahead and start downloading for us. Once that’s downloaded, then I’m just going to open it up And we'll be able to run the installer.

See, over here all we have to do is just drag this over here to the applications folder and then it’s going to be on our computer. So, now what we want to do is we're going to open that up.

So, I’m just going to go over here to applications. And we'll scroll down to PopSQL. And we're going to open this program up.

So, in order to use PopSQL you're actually going to have sign in. So, you can just sign in with a Google account or with an email address. I’m going to go ahead and sign in.

So, you can see it logged me in. It says, “Welcome, Mike!” So, now we'll go ahead and return to the app. So, now that we're logged in, what it’s going to ask us to do right away is connect to a database.

So, basically what this application is going to do is it’s going to connect to that MySQL database server that we have set up. So, what we can do over here is just give this a nickname.

So, I’m just going to call this Draft. And it’s asking us for the type. So, over here if you scroll down you'll see the first option is my MySQL.

So, we'll just click MySQL. Now, it’s asking us for a host name. And the host name is basically the address where this database server is located.

So, in our case it’s just at localhost. Just like that. So, just type in localhost.

Leave the port number the same. 3306 is going to be the default port number. And then the database we're going to connect to is named giraffe.

So, if you remember, like a minute ago we created that giraffe database. That’s what you want to put in here. So, if you named it something other than giraffe, put that name in here.

And then we need a username. So, the username is going to be root. And then the password which they're asking for down here is going to be the password that you set up.

So, in my case, it was just password like that. And then I’m just going to click Connect. And this is going to go ahead and connect us to the database.

So, at this point we installed MySQL. We set up our first database. And we connected everything with PopSQL.

And so, now we are able to start using PopSQL. We can start learning all sorts of SQL stuff. And it’s going to be awesome.

But keep in mind, if you don’t want to use PopSQL, which I honestly would recommend using it. You can do everything from inside the terminal here. [Creating Tables]

In this tutorial I’m going to talk to you guys about datatypes. We're going to talk about creating tables. We're also going to talk about altering and deleting tables.

So, in the last tutorial we set everything up. So, we got MySQL set up. We also downloaded this program PopSQL

which is basically just a text editor that we can use to write all of our SQL code and all that stuff. So, what I want to do in this tutorial is show you guys how to create tables, right? So, we created a database.

In our case, we created a database named giraffe in the last tutorial. And now what I want to do is start populating that database with different tables. In other words, I want to start defining our database schema.

Now whenever you're working with a relational database management system, your first step is always to create tables. So, before we can start inserting any information, before we can start querying for information,

we actually have to create the physical tables inside of our database. And we can do that by defining the tables layout. And so, what we want to do is we basically want to use a SQL command which is called Create Table.

And then inside of that command we can pass in all the different attributes or all the different columns that we want our table to have. And so, the first thing I want to show you guys is the different types of data

that we can store inside of our database. So, over here I actually just have a little list. And I’m just going to paste it in here.

So, these are all of the basic data types that we're going to be using in SQL. Now, these aren’t all of the datatypes. There are actually tons of datatypes.

And depending on the relational database management system that you're using, a lot of them have different datatypes to do different things. I would say that these six datatypes right here make up like the core SQL datatypes.

Like these are probably the most common datatypes that you're going to see. But just keep in mind that there are a few others. Now, we're using the MySQL database.

And all of these datatypes are going to be able to be used in the MySQL database management system. And all of these are going to be used, for the most part, in any relational database management system. But like I said, specific database management systems will allow you to use different datatypes

depending on how they want to implement things. So, let’s go through these different datatypes and I’ll kind of talk you guys through how to use them. So, INT.

This is actually going to refer to an integer. So, anytime you see INT just like that, I-N-T, that basically means any whole number, right? So, this is any sort of whole number.

But it can’t have decimal places after it. If you want to be able to store decimals, you can use this decimal datatype. And the decimal datatype will allow you to store decimals.

And you'll see, we have this parenthesis here after decimal. And I have M and N in here. Now these are actually both going to end up being numbers.

So, M is going to be the total number of digits that you want to store for this number. And N is going to be the number of digits that you want to store after the decimal point. So, when we're working with databases you have to be very specific about the different information.

And specifically, the amount of digits that you want to store for a number. So, what we could do is I could say like 10 here and I could put a 4 over here. And what this means is we want to store a decimal number with 10 total digits

and 4 of those digits coming after the decimal place. So, you can specify, you know, depending on how accurate you want these numbers to be stored, you can modify those numbers.

Down here we have VARCHAR. And this actually stands for Variable Char or Variable Character. This is basically a way that we can store a string of text.

So, VARCHAR, we have these parenthesis after here. And you can put a number in here. So, if I put a 1 in here, then this is going to store a string of text, length 1.

If I put (100) in here, this is going to store a string of text with 100 characters. So, that means that the maximum amount of characters that you can store inside of a VARCHAR(100) would be 100.

Down here we have BLOB which stands for Binary Large Object. And this is basically just a structure that can store large amounts of binary data. So, if you have – a lot of people will use these for like images or files.

Like you can store those inside of a BLOB. And they'll be able to be stored in the database. We also have DATE.

So, DATE will be like a specific date and time. And you can see we format a date like YYYY-MM-DD. So, this would be the year, the 2-digit month, and then the 2-digit day.

You can also have a TIMESTAMP, which is similar to date, but it’s generally used for recording like when things happen. So, you can record like when an item got inserted into the database or something like that.

So, over here it’s just YYYY-MM-DD and then we have the hours, minutes, and the seconds. So, those are all, like I said, the main datatypes that you're going to be using. But these aren’t all the datatypes.

So, depending on the database management system that you're using, you want to check to see what specific datatypes they offer. But like I said, for the most part, these should work in just about any system.

So, now I want to talk to you guys about creating database tables. So, what we can actually do is we can create tables and we're going to have use those datatypes to tell the relational database management system

what types of data we want to store in each column in the table. So, in order to create a table, we're actually going to type out some SQL. Now, I’m just going to type out CREATE TABLE.

And these are two SQL reserve words. CREATE and TABLE. And you'll notice that I typed them in all capital letters.

Now, this is actually a pretty common convention when writing SQL queries. A lot of people will write these reserve words in SQL in all capitals. But you don’t have to.

If I wanted, I could write create table just like that. And you’ll see it’s getting highlighted the same way. The reason that people write these in all caps is

because then it’s easy to distinguish the SQL from any other text that we might be writing. So, I would say for the most part, just stick with making these all uppercase and you should be fine. So, I’m going to say CREATE TABLE.

And then I want to type in the name of the table that I want to create. So, what we're going to do is we're going to actually create a database table. So, I actually have a database table set up over here.

You'll see it’s a student table. And it’s storing just a list of students for like maybe in a college or university. So, there’s three things that we're storing about this student.

The student’s ID, the student’s name, and the student’s major. So, we're storing all of this information about this student. So, what I can do now is I can actually create this exact table inside of my database.

So, I can say CREATE TABLE. We'll call it student. And you'll notice that I made this lowercase.

So, this isn’t an SQL reserve word, so I’m not going to make it uppercase. And then what I can do is I can just make an open and closes parenthesis and a semicolon. Now, any command that you write in SQL is always going to end with a semicolon.

And if you're just starting out, you might forget to put that semicolon in there, but you want to make sure that you always put it in there otherwise it’s not valid SQL. So, I’m just going to click Enter.

And I’m going to down here in-between these parenthesis. And what I’m going to is I’m going to start defining the different columns or the different attributes on this table.

So, what we can do is we can define a column name and then a column datatype. So, I can come over here and I can say the first attribute. So, in my case, the first attribute for this table is going to be student ID.

And you'll also notice that student ID is a primary key. So, on this table, student ID is the primary key. Meaning it’s the column on the table which will uniquely identify the specific row.

So, I'm just going to call this student_id. And now I need to give this a datatype. So, I’m going to give this a datatype of INT because all of these ID’s as you can see, are integers, right?

They’re just integer numbers. Now because this is going to be the primary key for this table, I need to denote that. So, over here I can just say PRIMARY KEY, just like that.

And what that’s going to do is it’s going to tell MySQL that this is going to be the primary key for the table. The next thing we want to store is the student’s name. So, I’m just going to say name.

And the name is actually going to be a string. So, this could be like Jack or Tommy or Kara, right? It’s a name that we're storing about this table.

So, over here I’m going to make this a VARCHAR. And then I’m going to make an open and closed parenthesis. Now, remember, with the VARCHAR datatype we have to tell MySQL

how many characters we want this to be able to store. So, with someone’s name, what you want to do is you basically just want to think like how many characters do we really want to allocate

to storing someone’s name? Because here’s the thing, if I allocated like 1000 characters for someone’s name, will in reality, normal people don’t have a name with 1000 characters, right?

I mean maybe your name would be 20 characters or 30 if you're really pushing it, but 1000 is just totally out of the bounds of reality. And you have to think if you're storing like millions of names,

potentially, you know, allocating 1000 characters to each name when you only need 20 is going to take up a lot of extra space in your database. So, I think what we should do is maybe say like 20.

So, let’s say that the maximum length of someone’s name we want to store is going to be 20 characters. And really, you know, depending on the domain of the application that you're building, that’s going to be different.

But in my case, let’s just say it’s 20. And then finally we wanted to store the student’s major. So, I can say over here, major.

And this is also going to be a VARCHAR. So, why don’t we also allocate 20 characters to their major. And that should be enough.

So, you'll notice that I’m defining each of the attributes, each of the columns on the table. And then I’m putting a comma. And then I’m defining the next column.

And then I’m putting a comma. And then the final column. So, this right here, this CREATE TABLE statement is

basically going to create this table over here for us, right? We have the primary key, which is an INT. And we have a name and a major which are strings.

So, that’s going to go ahead and do that for us. Now, from inside PopSQL, what’s really cool is we can just click on this query right here. Click on this SQL statement.

And I can come over here and click Run. And what this will do is it’ll automatically run this SQL on our MySQL server. So, I'm going to click Run.

And you'll see over here we get a success message that says Rows affected: 0. So, that actually was able to create that new table in our database. So, that is basically how we could create a table.

And now we have this table stored in our database. I want to show you guys another thing we can do. So, over here I’m defining the primary key.

So, student_id was the primary key, right? It’s the one column that’s going to uniquely identify each row. And if you want, you can put primary key over here after like student_id.

Or what you can do is you can get rid of this and you can define this down below. So, instead of defining the primary key up here next to student_id, I can come down here and I could change it.

So, I could say now PRIMARY KEY, and then I can make an open and closed parenthesis. And in here, I could put the name of the column that I want to be the primary key. So, in my case, I’ll just say student_id.

So, now what this is system is that the primary key is going to be student_id. So, this is a equivalent to what I was doing up here. It’s just another way that you can do it.

And so, now we already have our table created, so I’m not going to go ahead and create it again. All right, so now that we have our student table created, let’s go ahead and make sure they created correctly.

So, what I’m going to do is I’m going to come down here and I’m going to type out another MySQL command. So, I’m just going to type out DESCRIBE.

And then I’m going to type in the name of the table. So, in our case it’s going to be student. And you'll notice with PopSQL, if I click over here on this query, it gets highlighted in that blue color.

And if I click down here on this query, it gets highlighted. So, what you can do with PopSQL is you can have like a query up here. And you can run that query or you can click on this query down here and run it.

So, I’m going to click on DESCRIBE student. And then I’m going click Run. So, you'll see that it’s actually describing the table that we just created.

So, there’s a list of all these different fields. We have student_id, name, and major. It’s telling us the datatypes that we're storing.

So, like varchar(20). And then it’s telling us a bunch of other information which we're going to get into later. So, what this is, it basically described everything about this table.

So, now what I want to show you guys how to do is delete and modify a table. So, now that we've created a table and we saw that it got created correctly, if you wanted, you could delete the table.

So, what I could do is I can come down here and type out DROP TABLE and I can just type the name of the table. So, in our case, it’s the student table, and a semicolon.

And now I’m going to click on here and I’ll click run. And what this is going to do is it’s going to drop that table. So, now if I came up here and I click on describe student,

in other words, I’m saying I want to execute this command. And I click run, you'll see that it says “NO_SUCH_TABLE”. Table ‘giraffe.student’ doesn’t exist because remember, we just dropped it.

So, what we can do, is we can actually create again. So, I’m going to click on this CREATE TABLE query and I’ll click Run. And that will go ahead and create it.

So, now if we click DESCRIBE student, you’ll see we get the table back because we created it again. So, you can drop the table. You can also modify the table.

So, let’s say that after the table was created, you wanted to give it another column. What I could do is I could add another column. So, I could say ALTER TABLE.

And then say student, because that’s the name of the table. And then I could say ADD. So, what this is going to do is it’s going to add an extra column onto the table.

So, why don’t we add a column for GPA. So we could store a student’s GPA and that’s going to be a decimal. So, I’m just going to click a semicolon.

And this is going to go ahead and end off this command. Now, with decimal, remember I showed you that you could put numbers after here, like 1 and 2. So, what we're going to do is generally a GPA would be like 3. something something.

Or 4. something something. So, we're going to have this be three total digits with two of the digits occurring after the decimal point. So, now I can go ahead and run this command and you'll see it says success down here.

So, if we were to describe the student table again, if I just click on this and click Run, now you'll see we have that extra column in here, GPA. And it’s a decimal (3,2).

So, that is how we can drop a table and that’s also how we can alter a specific column. If you wanted, you could also drop a specific column. So, I could say ALTER TABLE student.

And over here, I can just say DROP COLUMN and just type in the name of the column. So, what this will do is it’ll drop that GPA column from the table. So, let’s go ahead and run.

We get this success message. Now, if I describe the table and I clicked run, you'll see that the GPA field went away. So, I actually dropped that field.

So, you can create tables. We can add columns onto tables. We can remove columns from tables.

And then we can just remove the table altogether. So, hopefully that show you guys some of the basics of doing those operations on tables. Now, obviously whenever you're creating your database,

the first thing that you want to do is define your database schema. In other words, you want to create all the different tables and then you can start inserting data into the tables as such.

So, in the next tutorial I'm going to talk to you guys about inserting data into tables, so we'll actually insert some student information into our student table. [Inserting Data]

In this tutorial, I’m going to talk to you guys about inserting data into a database tables. So, in the last tutorial we set up our first table which is this student table. And you can see it up here.

And then down here we described the student table. So, it has student_id, name, major, etc. So, now that we have our table set up, we want to actually start inputting information, right?

We want to start storing some different data. So, I’m going to show you guys how to do that. I’m going to leave this CREATE TABLE statement up here

because we're actually going to come back to that in a sec. But down here, I’m going to show you guys how to insert values. So, in order to insert a piece of information into a table, we just want to type out INSERT INTO.

And now I want to type out the name of the table. So, in our case, we have this student table. And then I just want to type out VALUES.

So, basically, we're saying insert into the student table the values. And then over here I’m going to make an open and closed parenthesis and a semicolon. So, now this is actually a full statement.

So, remember, we need to end this off with a semicolon. Inside of these parenthesis I can actually put the information that I want to store. So, the way that the table is set up, we have the student_id first, then the name, then the major.

And that's the order that we want to put this information into the table. So, I need to put this student_id first, the name, and then the major. So, over here I’m just going to type in the student ID.

So, we're going to be actually entering in the same information that we have over here. So, this is kind of like our little template. So, we have the first student, Jack, who’s a biology major and his student ID is 1.

And you can see we just keep going from there. So, I’m going to insert, you know, roughly the same information. So, the first thing we want to do is put the student ID, so that’s going to be 1.

And you'll notice that with an integer I can just type out the number 1. And then over here we're going to put in a VARCHAR(20). So, basically this is going to be a string.

Anytime we're typing out strings in SQL, we're going to make these double quotation marks. And then in here we can type out the string. So, the student’s name is going to be Jack.

And then another comma and we're going to put in his major which is biology. So, we have Jack with the student ID of 1. And his major is Biology.

So, insert into student values 1, Jack, Biology, this is going to go ahead and insert this value into the database table. So, now when I click Run –

and you'll notice all I have to do is just click on this query and then click Run. It says down here Success. Rows affected 1.

So, we affected one row in the student table. So, now what we can do is we can actually see how this got inserted into the table. So, what I’m going to do is actually below here, I’m just going to type out select asterisk from student.

And this is actually a very simple SQL command. What this is going to do is it’s going to grab all the information from the student table. And we're actually going to spend a whole tutorial just talking about using SELECT.

But for now, just know you can type SELECT * FROM student. And this is going to go ahead and give us all the information from the student table. So, I’m just going to click on this query here and I’ll click Run.

And you'll see down here we get this table. So, it says student_id 1. Name, Jack.

Major, Biology. So, so far in the student table, we have 1 student which is Jack and he’s a biology major. So, why don’t we actually insert another student.

So, I’m going to keep this same line of code except now I’m going to insert a student with ID 2. And this student’s name is going to be Kate. And she is going to be a sociology major.

So, we have Kate who’s ID is 2, sociology. So, if I click on this command here and I click Run, this is going to go ahead and insert Kate into the database.

So, now I’m going to click on Select All from students, or Select * from students. And we'll click Run. And now you can see we're getting all the information from the student table.