Skip to main content
Loading Explore guides…
SQL Database Course · Acedra
Home
Explore
Computer Science
SQL Database Course
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
587 segments
Copy
Hide video
0:00
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.
0:24
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.
0:48
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.
1:18
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.
1:41
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.
2:06
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.
2:28
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.
2:59
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.
3:21
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,
3:46
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.
4:18
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.
4:42
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.
5:03
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.
5:27
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,
5:50
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.
6:18
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.
6:40
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?
7:09
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?
7:36
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.
8:00
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.
8:23
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.
8:48
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.
9:11
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.
9:44
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.
10:11
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.
10:33
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.
11:00
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.
11:26
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
11:56
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.
12:26
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.
12:50
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.
13:17
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.
13:48
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
14:17
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.
14:43
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.
15:11
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.
15:35
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.
16:03
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.
16:34
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.
17:02
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.
17:34
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.
18:01
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.
18:29
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.
18:56
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.
19:24
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.
19:52
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.
20:19
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.
20:48
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.
21:16
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
21:40
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.
22:05
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.
22:33
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.
22:58
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.
23:26
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.
23:50
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.
24:20
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.
24:45
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.
25:17
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.
25:47
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
26:16
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.
26:45
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.
27:13
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.
27:39
And these emails are unique to each entry in the table, right? So fakemail@fake.co. 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
28:07
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.
28:33
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?
29:01
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.
29:26
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.
29:57
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.
30:25
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.
30:54
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.
31:23
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.
31:49
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.
32:24
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.
32:56
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.
33:25
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.
33:57
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.
34:26
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.
34:55
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.
35:28
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,
35:54
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.
36:21
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.
36:48
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?
37:20
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,
37:51
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.
38:19
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.
38:50
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.
39:18
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.
39:42
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.
40:07
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.
40:38
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?
41:12
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?
41:47
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.
42:17
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.
42:44
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.
43:11
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.
43:41
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.
44:07
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
44:35
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.”
45:03
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.
45:28
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.
45:58
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.
46:27
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%.
46:56
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.
47:21
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.
47:48
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.
48:14
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.
48:39
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.
49:07
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.
49:39
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.
50:05
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.
50:31
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.
50:55
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
51:19
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?
51:45
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.
52:08
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.
52:34
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
53:05
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.
53:30
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.
53:54
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.
54:16
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.
54:38
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.
55:02
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.
55:23
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.
55:46
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.
56:05
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.
56:30
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.
56:53
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.
57:16
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.
57:40
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.
58:07
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.
58:30
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.
58:48
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
59:15
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.
59:42
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.
1:00:04
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.
1:00:25
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.
1:00:54
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.
1:01:16
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.
1:01:41
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.
1:02:07
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.
1:02:35
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.
1:03:02
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.
1:03:28
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.
1:03:54
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.
1:04:18
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.
1:04:38
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.
1:05:00
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.
1:05:30
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.
1:05:52
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.
1:06:11
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.
1:06:40
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. It says, “Automatically start MySQL Server on Startup.”
1:07:08
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.
1:07:35
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.
1:07:54
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.
1:08:27
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.
1:08:59
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.
1:09:26
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.
1:09:51
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,
1:10:15
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.
1:10:44
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.
1:11:06
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.
1:11:29
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.
1:11:54
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.
1:12:16
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.
1:12:40
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.
1:13:03
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.
1:13:32
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.
1:13:53
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.
1:14:14
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.
1:14:41
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.
1:15:09
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.
1:15:36
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.
1:16:04
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.
1:16:30
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.
1:16:55
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.
1:17:22
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.
1:17:45
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.
1:18:09
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.
1:18:32
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.
1:18:58
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.
1:19:26
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.
1:19:55
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.
1:20:23
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.
1:20:47
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.
1:21:13
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
1:21:40
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.
1:22:04
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.
1:22:30
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.
1:22:52
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.
1:23:18
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.
1:23:46
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.
1:24:10
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,
1:24:33
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.
1:25:00
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.
1:25:24
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.
1:25:49
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.
1:26:18
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,
1:26:45
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.
1:27:08
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.
1:27:27
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.
1:27:49
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,
1:28:13
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.
1:28:33
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.
1:28:57
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.
1:29:24
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.
1:29:51
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.
1:30:20
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.
1:30:46
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]
1:31:09
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.
1:31:33
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.
1:31:58
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.
1:32:24
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.
1:32:47
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.
1:33:10
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.
1:33:41
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.
1:34:08
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.
1:34:35
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. So, we have Jack and then we also have Kate.
1:35:04
So, we added in – so far, we've inserted in 2 people into there. So, what you can do is you can basically use this same format in order to keep inserting students into the student table, right? As long as you have their ID and you have their name and their major, then everything is good. But I want to show you guys another thing we can do. So, let’s say there’s a situation where we had a student who didn’t have a major. Maybe they just had no major or we didn’t know what their major was
1:35:31
so we couldn't insert it into the database. Well, in a situation like that we could actually modify this statement a little bit. So, I could say INSERT INTO student and then after student here I can make an open and closed parenthesis. And I can type out the names of the columns or the names of the attributes that I have or that I want to insert. So, I can say like student_id and then name. So, you'll notice that I’m not including major in here.
1:35:57
And what that means is that over here in this value section, we can include the student ID and the name. But we don’t have to include the major. So, like if I don’t know what the major is, like I can’t obviously put anything there. So, by saying student open and closed parenthesis and then specifying the 2 attributes that I do have, I can then just put them over here and I won’t get an error by not including it. So, now we can change these values. So, we'll change the primary key to three.
1:36:24
And then let’s see who the next student in our database is. So, it looks like it’s Claire. So, we'll have Claire. And then we can go ahead and run this query. And you'll see rows affected, 1. So, we added in another row. But if I was to select all this data – so I’m going to click Select again and I’m just going to click Run. Now you'll see that Claire’s student ID is 3. Her name is Claire, but here major is NULL. So, we didn’t actually enter in a major for Claire.
1:36:50
And therefore, inside the major field, she’s getting a value of NULL. And that’s actually pretty common. So, that’s basically going to be what happens when we don’t enter in something. And so, what you can do is you can specify what pieces of information you want to insert into the table by specifying them over here. And then here in the value section, you just have to add those pieces of information. So, that’s two ways that you can insert something into a database table.
1:37:17
And it’s important to notice that you can’t insert duplicate entries. So, if I was to try to insert this again, you'll see that the primary key is still 3 which means I’m not going to be able to enter this record in because a record with that primary key already exists inside the table. So, if I was to click Run again, you'll see I'm getting this error. It says Duplicate Entry ‘3’ for key ‘PRIMARY’. Basically, what that means is we have a duplicate key and it’s not going to work.
1:37:45
But if I was to change this to 4. So, instead of having an ID of 3, it’s 4. Now, this is going to work. And we'll be able to see we have two entries with Claire, but they have different student ID’s so we're all good to go. So, that is the basics of inserting stuff into a table. And really, those two techniques are really useful. So, that’s kind of the basics of inserting into a table. Now, there’s a lot of more things we can get into with this.
1:38:10
And in the next tutorial, I’m going to show you guys some other stuff that we can do in order to make inserting into the tables a lot easier. [C] In this tutorial I’m going to talk to you guys some more about inserting information into a database. So, in the last tutorial we kind of looked at the basics of inserting information. So, we have our table here. It’s the student table. And down here we kind of looked at how we can insert information.
1:38:35
So, we can insert into the name of the table, and then values. And we can pass in the values. We can also say insert into the name of the table. And then over here we could specify what information we want to insert. And then here in the values we just insert that information. So, we've kind of been using this photo over here of this like, you know, student database. And I actually just went ahead and wrote out all the code for inserting all of these different students into the database.
1:39:02
So, you'll see we're inserting Jack who’s a biology major. Kate sociology. Claire, who doesn’t have a major. There’s another one name Jack who’s also a biology major. But you'll notice that it has a different primary key than this guy up here. And then there’s Mike who’s computer science. So, these instructions will actually insert each of those students into our database. And you'll see over here when I run this Select * FROM command.
1:39:30
So, with PopSQL, if you just click on the SQL statement and then you click Run, it’ll go ahead and run it for you. So, you'll see down here we get all that information. So, there database is set up. And that’s sort of like the general use case, right? That’s like we set up our database table and we're just straight-up inserting information into it. But there’s some other stuff we can do. Namely, we can actually set up our database table
1:39:51
in order to make it easier for us to insert elements or to control the type of information or the type of rows that we can insert into the database table. So, what I’m actually going to do is I’m actually just going to drop this table. So, I’m just going to say DROP TABLE student. And we're actually just going to start over. So, again, with PopSQL all you have to do is click on the SQL command. Like I can click on DROP TABLE right here. And then when I click Run, it’ll go ahead and drop it.
1:40:20
So, now if I tried to SELECT ALL from the student table, you'll see it says there’s no such table. So, let’s go ahead and I’m going to show you guys how we can create this student table in order to make it a little bit easier for us to insert stuff. So, the first thing I’m going to show you guys is something called NOT NULL. And NOT NULL will basically allow us to define that a particular column in the table cannot be NULL. So, let’s say that when we're storing our students,
1:40:47
we don’t want the student to be able to have a NULL name. And NULL is basically just a value that represents no value. So, if something is NULL it means that it doesn’t have a value. So, let’s say that we always want the student to have a name no matter what. Well, after I same name VARCHAR(20) what I could do is I can say NOT NULL. And I'm just putting that right after here. What that means is this column can’t be null.
1:41:10
So, if you insert a row into the table, you can’t insert NULL for a name. I could also do something else which would be UNIQUE. So, UNIQUE is another keyword which we can use. And basically, what this is going to mean is that the major field has to be unique for each row in this table. So, if somebody else or another row in the table has the same major as another entry, then that will get rejected. So, NOT NULL and UNIQUE are actually pretty useful.
1:41:39
So, now what we can do is we can actually create this table. So, I’m just going to click on this query. And I’m going to click Run. And you'll see that we get a success message. So, we were able to create the table. And so, now why don’t we start populating this table with information? So, I’ll come over here and I’ll click insert into student – Jack, biology. So, we're going to insert Jack. And then we'll select the table and you'll see that Jack got inserted just fine.
1:42:00
Let’s click on this second one. Kate sociology. So, we'll run this. And again, we'll just select all from the table. You'll see both of those got inserted. So, now we get down here into this next one. So, insert into student. So, for Claire, we're inserting in student_id and name. But let’s actually switch this up. So, why don’t we get rid of this and instead of saying this, we'll give her a major. So, we're going to say that Claire’s a chemistry major.
1:42:26
But we're actually going to get rid of this name. So, instead of Claire, I’m just going to say NULL here. And you'll notice up here, I said that the name cannot be NULL. So, when I created this table, I specified that the name field cannot be NULL. So, if I come down here and I try to insert a student with no name, with a name that’s set to NULL and click Run, you'll see that we get this error. And it says, “You have an error in your SQL.”
1:42:51
And basically, what that means is we can’t insert in a value here that’s NULL because we specified that it can’t be NULL up here. So, you’re actually not going to be able to do that. Now, we also, over here on this major field, we defined that it has to be unique. So, if I come down here and I try to execute this line, so you’ll see, we're trying to insert this person’s name as Jack and they're a biology major. But we already defined one person that was a biology major up here, right?
1:43:19
It was this first one. So, if I try to enter in another biology major, it’s going to give me another error. So, I’m going to click run. And you'll see down here it says Duplicate entry ‘Biology’ for key ‘major’. So, it yelled at us because we entered in a duplicate entry. So, NOT NULL and UNIQUE are really good ways for you to kind of control the data that gets stored on the table. And actually, funny enough, if you have a primary key –
1:43:47
a primary key is actually just an attribute or a column on the table that is both NOT NULL and UNIQUE. So, a primary key is basically just something that’s NOT NULL and UNIQUE. So, there’s a couple other ones. We're actually going to drop this table again. So, I’m just going to click DROP TABLE and we'll go ahead and drop the table. And over here, I’m going to create the table again, but we're going to give this some other stuff. So, these are actually what we would call constraints.
1:44:10
So, I would say like a constraint would be that it’s NOT NULL or that it’s UNIQUE. But there’s a couple other constraints that we can add that I want to show you guys. So, let’s say that we wanted to set a default value. So, let’s say that if somebody didn’t enter in a major, we wanted to be able to give them a default major. Well, I can actually say that something has a default. So, I can come down here and I can say after major, DEFAULT.
1:44:34
And then inside of single quotes I can just type in what I want the default to be. So, in our case, if somebody doesn’t provide us with a major, why don’t we just say that they’re ‘undecided’. So, basically if the major field is left blank, we'll say that the person is undecided. So, then down here, why don’t we go ahead and insert a student into the table that doesn’t have a major. So, here we can just say INSERT INTO student
1:44:58
and we'll go ahead and specify that we're going to enter in the student_id. And also the name. But you'll notice that I’m not putting major in here. Which means that we don’t have to give this a major. So, now we'll get rid of Jack’s major. And when I go ahead and run this, you'll see that we got a success message. And if I was to select all the entries from the student, it says student_id is 1. His name is Jack and his major is undecided. So, because I didn’t insert a major,
1:45:24
it’s going ahead and populating that major for us with the value undecided because that was the default value that I placed over here. So, that’s another really useful constraint that we can put on this. All right, so there’s one more of these little constraints that I want to show you guys. And it’s actually really useful for working with primary keys. So, you'll notice that the primary keys for all of these guys were like 1, 2, 3, 4, 5, etc., right?
1:45:50
I like manually went in and incremented all those primary keys. But what you can actually do you can have the database do that for you. So, I can come over here right here after students, so remember, student_id is the primary key, right? We defined that down here. I can come ever here and say AUTO_INCREMENT. And AUTO_INCREMENT is basically going to specify that the data that gets inserted into here is going to automatically incremented every time we add one in.
1:46:18
So, I could actually come down here and instead of inserting a student ID, I can just leave it out. So, I can just insert the name and the major. And then I can add that information in here. So, Jack is going to go ahead and study biology. And actually, let me get rid of these. So, we can kind of see how this works. So, I can copy this for another one. And we'll make this Kate and she’s studying sociology. So, notice that I’m not actually inserting the student_id, right?
1:46:51
All I’m inserting is the name and the major. But because we said that this table was going to be auto incremented. In other words because we said that the student_id was going to be auto incremented we don’t actually have to do that. So, I can come down here and I can run this. And actually, whoops. I have to create the table again. My bad. So, we're going to create the table. And I can insert in this first entry, Jack, who’s a biology major. And I’ll run this.
1:47:13
And you'll see it says 1 row affected. And then I’m also going to insert this next one which is Kate who is studying sociology, so I’m just going to click Run. And so, now I’ve inserted both of these students into the table. And if I select all from the student table, you'll see down here that we get both of these entries. But Jack has an ID of 1 and Kate as an ID of 2 even though I didn’t rigorously specify that, right? So, I didn’t actually add in the ID’s for either of these guys,
1:47:37
and yet they still showed up down here in the table which was pretty useful. So, that’s a great way for you to just like automatically increment like something like a primary key or really any other row in the table by using AUTO_INCREMENT. So, those are just a couple little things that you can do when you’re creating the table. We would call those constraints. So, you can add constraints onto the specific columns of the table and that can control
1:48:02
like what types of information can be put inside of there. So, that’s – there’s a little bit more of an advanced way for you guys to insert information into the database. [Update & Delete] In this tutorial I’m going to talk to you guys about updating and deleting rows inside of a database table. So, in the last few tutorials we've looked at creating database tables and inserting information into them. But now once we have that information inside those tables,
1:48:28
I’m going to show you guys how to update it and delete it whenever you want. So, we have here our student database. And this is – or the student table and it’s the table that we've been using throughout the course. And so, down here you can see it’s just a bunch of students. And they have names, majors, and ID's. So, right now, these are all the students that are stored inside of our database. So, these are the database entries that we can work with.
1:48:51
The first thing I’ll show you guys show to do is update some of these entries. And so, you know, let’s say that we wanted, you know, update something. So, let’s say that we wanted to change the name of one of the majors in our school, right? So, let’s say I am the database administrator for my school. And we decided that instead of calling the major biology, we wanted to call it bio. So, the official name for the major is no longer biology. It’s just going to be bio.
1:49:17
Well, we could actually make that change inside the database. So, I can use the special keyword called UPDATE. And I can just type out the name of the table where I want to do the update. So, in our case, it’s student. But here’s the thing. I don’t want to set the major equal to bio for all the students. I only want to set the major equal to bio for the students who have their major as biology. So, over here we can specify that. I can say WHERE. And here I can put a condition.
1:49:51
So, I can say major is equal to ‘Biology’. And I’m going to be semicolon. So, this whole thing now is one SQL statement or query. And so, what I'm doing is I’m updating the student table and I’m setting the major column equal to ‘Bio’ where the major column is already equal to ‘Biology’. So, now if I was to go ahead and run this, you'll see it says Rows affected: 2. So, we updated to of the rows in this database. And if I was to select all of them and I’m going to click Run,
1:50:20
you'll see down here now for the two students who are biology majors, it’s now saying that they’re bio majors, right? So, it updated the database only in those places where the student’s major was equal to ‘Biology’. And that’s what we can do here. So, this is just a very basic update statement, but we can use this in order to all sorts of stuff. So, let’s do another one. We can do the same for computer science. So, I could say UPDATE student.
1:50:45
SET major equal to ‘Comp sci’ WHERE major = ‘Computer Science’. So, I’m basically abbreviating this major. So, let’s go ahead and run this. And again, I’m just going to SELECT ALL from the table. You'll see down here, Mike, who used to be a ‘Computer Science’ major is now a ‘Comp Sci’ major. So, I was able to update that. Another thing you can do is you can update specific things. So, I can say like UPDATE student. SET major equal to ‘Comp Sci’, WHERE.
1:51:13
And then over here I can do a different condition. So, I can say like student_id is equal to 4. So, now I’m updating the student’s major who has an ID of 4. So, this is like a different type of query. So, down here I’m running that. And then I’m going to SELECT ALL. And so, now you can see that Jack who used to be a biology major is now a comp sci major because his student ID was 4. So, you can get kind of creative, you know, playing around with setting different things
1:51:41
and then checking different conditions. You can also use more complex conditions down here. So, down here we have bio majors and we have chemistry majors, right? Well, let’s say that any student who is either a biology or a chemistry major is now going to be a biochemistry major. So, we're combining those two majors together. Well, I could update that here. So, I could say UPDATE student. SET major equal to ‘Biochemistry’. WHERE major is equal to ‘Bio’. And over here we could say OR.
1:52:14
And then we could also say major is equal to ‘Chemistry’. And this is going to give us some OR logic. So, now if the student has a major of bio or if they have a major of chemistry, we're going to set it equal to biochemistry. So, we're basically combining those two majors together. So, now I’ll click Run and you can see rows affected is 2. And I’ll SELECT ALL from students. And you can see now Jack and Claire – Jack used to be a bio major. Claire used to be a chem major.
1:52:41
Are both now biochemistry majors. So, you can use OR in order to check multiple things like that. You can also set multiple things. So, over here I’m actually going to get rid of this condition. And I can say SET name equal to ‘Tom’. And major equal to ‘undecided’. WHERE student_id is equal to 1. So, whoever has the student equal to 1 is going to have their name set to Tom and their major set to undecided. So, let’s go ahead and run this. And you'll see it says 1 row affected.
1:53:22
So, let’s SELECT ALL from the student’s table. And you'll see down here, the student with the ID of 1 is now named Tom and their major is undecided. So, you can change multiple columns within the same query like I did up here. All right, so there’s one more thing we can do, which we can actually get rid of this WHERE. So, this is actually optional. So, if I got rid of WHERE, I can then set something. So, why don’t we set major equal to undecided.
1:53:47
So, if I get rid of that WHERE statement, this is just going to apply to every single row in the table. So, now when we run this, you'll see rows affected was 5. And now when we SELECT ALL from the students table, all of their majors are now undecided. So, you can either select specific rows or groups of rows with that WHERE or you can just do it to all of them by dropping the WHERE. So, now I’m going to show you guys how we can delete rows from the table.
1:54:10
And it’s actually pretty similar to how we would update rows. So, instead of updating a specific row or a group of rows, I can actually delete a specific row or a group of rows. So, it’s like I said, pretty similar to UPDATE. I’m just going to type in DELETE FROM and then the name of the table. So in our case it’s going to be student. And you can actually just end this off right here. I can put a semicolon here. And this will go ahead and delete all of the rows inside of the table.
1:54:35
But why don’t we try to get more specific? So, I’m going to try to delete a specific row. I can say DELETE FROM student. WHERE – and now I can specify a condition. So, I can say like WHERE student_id is equal to 5. So, what this will do is it’ll delete any students or any rows from the table that have a student ID of 5. So, down here we just have one like that. So, Mike, who is an undecided major has an ID of 5. So, if I was to run this. Now you'll see it says rows affected, 1.
1:55:06
And we'll select all from students and Mike is no longer inside the table. So, we actually deleted a specific row. And just like update, you can get more fancy with these queries. So, I could say like WHERE name is equal to Tom. And you can also do an AND. So, instead doing OR, we're doing an AND here. And I can say like major is equal to ‘undecided’. So, this will delete any students from the table who have the name of Tom and who are an undecided major.
1:55:37
So, now when I run this query, you'll see – and I select all the students again – that Tom got deleted from the table. So, he’s no longer there. So, just like we used WHERE in the UPDATE statements, we can use WHERE with the DELETE statements to do all of that. But if you wanted, you can just delete everything from the table. So, now I’m just going to run this command. And if I query the table, you'll see that nothing is there. So, we deleted everything.
1:56:01
So, updating and deleting is extremely useful. And you can use that WHERE statement in order to specify what specific row you want to update and what specific row you want to delete. [Basic Queries] In this tutorial I’m going to talk to you guys about getting information from the database. More specifically we're going to look at the SELECT keyword and we're going to look at the different ways that we can ask the database management system to give us some information back.
1:56:28
So, one of the core tenets of interacting with the database management system and using SQL is writing these little queries. And a query is essentially just a block of SQL that’s designed to ask the database management system for a particular piece of information. And so, one of the things that we need to be aware of is that when you’re keeping track of, you know, huge amounts of information in a database, you want to be able to grab specific information easily.
1:56:53
So, let’s say that I’m trying to, you know, grab a bunch of students from our little student table right here. Well, imagine that we had like a million students stored inside of that table, right? I might not want to just grab every single student. I might want to just grab students who meet a certain condition or students who have a certain major or students with a certain name. And we can use SQL queries in order to specify those things.
1:57:18
So, instead of the relational database management system giving us back all the entries in a specific table, instead it can just give us back very specific entries which meet a certain condition. So, we're going to talk a little bit about the basics of doing that stuff. Now, this is actually a huge topic and it’s the topic that we're going to be talking about for most of the rest of course.
1:57:36
So, this is going to kind of give you guys an introduction into writing all of these little queries. So, over here, you’ll see that I have this query here. And it’s just SELECT * FROM student. And actually, if you want, you can put this on two different lines. A lot of people will do that. So, this SELECT keyword is a very special word. And this SELECT keyword is basically going to tell the relational database management system that we want to get some information from it.
1:58:02
So, I can say SELECT. And then right next to it I can specify what information I want to get. And so far in this course we've been using this star or this asterisk. And basically, the asterisk means that we want to grab all of the information. But if we wanted, we could specify a specific column. So, we can select specific columns of information that we want to get back from the database management system and then we can say FROM whichever table.
1:58:29
So, SELECT * FROM table could also be read as select every column from the student table. So, over here you'll see when I run this, we get this information down here. So, we're getting all of the students in the table. We're getting their student ID’s and their names and their majors. If I wanted, I could change the columns up here. So, I could say like name. And now what this will do is it’ll select all of the names from the student table.
1:58:56
So, if I was to run this query, you'll see down here we get just the names. So, we have the name and then it’s Jake, Kate, Claire, Jack, and Mike. So, these are all of the names that were stored inside of the database. I could also include something else. So, I could say name, major FROM student. And then down here when I run this query, you'll see we're getting the students, names, and the majors. But we're not getting the student’s ID.
1:59:20
So, we're able to specify which specific columns we want to get back. You can also pre-pen these with the name of the table. So, I could say like student.name. And student.major. And sometimes people will do this just because student.name, it’s clear which table the name is coming from. And as we write more and more complex queries, that can come in handy more. So, for the most part, I’m probably going to be writing them both ways, just depending on the situation.
1:59:46
But you could do something like this. We can also order the information that we get back. So, here I’m getting the student’s name and the student’s major from student. And if I wanted, I could order that information. So, I could use another command which is ORDER BY. And then over here I can just put the name of the column that I want to order these by. So, I can say ORDER BY name. And now when I run this, you'll see we get the same results,
2:00:12
but they're in alphabetical order based off the name. So, we get Claire, Jack, Jack, Kate, and Mike. So, these are now ordered in alphabetical order. And by default, these are going to be in ascending order. But if you wanted, you could put them in descending order. So, you could say DESC and this stands for descending. So, now if I run this, you'll see that all the names get returned in the opposite order. So, Claire is all the way at the bottom. And then we go all the way back up to Mike.
2:00:39
So, you can order by anything. So, I can even order by like student_id. So, I’m not returning the student_id. In other words, I’m not getting the student ID up here, but I can still order by it. And so, now these are going to be in descending order of student ID’s. So, actually why don’t we just get all of these now. So, I’ll just say SELECT ALL from student. And you'll see now it’s ordered in descending order by student ID. It’s a little bit clearer, 5, 4, 3, 2, 1.
2:01:03
But I can also get rid of it or I can just say ASC which stands for ascending. And now it will order them in ascending order. So, you can order by a bunch of different stuff. You can also order by different sub columns. So, I could say like ORDER BY – and we'll start with major. And then after that we'll do student_id. So, SELECT ALL from student. ORDER BY major. And then student_id. So, it’s going to order them by major first. And then if there’s any of them that have the same major,
2:01:33
it’ll order them by student ID further. So, I’m going to run this. And you'll see down here, it’s ordering everybody by major. So, we're getting biology, biology, chemistry, computer science, sociology. In this case, these two students have the same major. They also have the same name too. But their student ID’s are different. So, the student ID’s are now order in ascending order. But I said descending right here. And I run this query.
2:01:59
Now you'll notice that the biology major with student ID 4 came first and then 1. So, it ordered it first by major and then within that, if they have the same major, it ordered them by student ID. And you can specify as many of those as you want. You can also limit the amount and results you're getting. So, I can say like SELECT ALL from student. And then I can say LIMIT. And here I can put like 2. And now what this will do, is it’ll limit the results I get back to 2.
2:02:25
So, now instead of getting all the students back, we only got 2. So, if you only want like a specify number of rows back from the table, then you’ll only get that. And you can also combine these. So, I could also like order them. So, I could say ORDER BY student_id, descending. So, now this is going to select all the students. Order them by student ID only give us 2 of them back. So, now when I run this you'll see we're getting 2 back.
2:02:50
And it’s ordering them in descending order by student ID. So, that can be pretty useful. And that’s just another way that you can make these more complex. The final thing that we can do that I’m going to show you guys in this tutorial is filtering. So, I can say WHERE. So, if you remember in a previous tutorial we were updating and deleting students. And we wanted to only update or delete specific students where certain conditions were true. And we used this WHERE condition.
2:03:14
You can do the same thing for SELECT. So, I could say like SELECT ALL FROM students WHERE major is equal to ‘Biology’. And so, now this is only going to give us the students who are biology majors. And you can see down here we get back the two Jacks who are both biology majors. You can do the same thing for chemistry. So, let’s see if we have any chemistry majors. We do. Looks like Claire is a chemistry major. And if you wanted, we can only return specific columns, like I said.
2:03:42
So, we can say SELECT, you know, the name and major from student. WHERE major is ‘Chemistry’. And now we're only getting the name and the major back. And you can make these more complex. So, I could say like WHERE major is equal to ‘Chemistry’ OR major is equal to ‘Biology’. And so, now this will give us all of the chemistry and the biology majors so we get the two Jacks and Claire. And we can also do different things.
2:04:08
So, like I said, like WHERE major is equal to ‘Chemistry’ OR name is equal to ‘Kate’. And so, now we'll get back any of those students. So, we get back Kate and Claire and they have different majors. So, you can play around with those WHERE statements to get specific entries from the individual table. So, I want to talk to you guys about how we can make these where’s a little bit more complex. So, obviously over here we're using equals, but there’s a bunch of other stuff you can use too.
2:04:35
So, this is actually comment in SQL. So, if you put two dashes, then anything after it is going to be a comment. But here I have all the different comparison operators. So, we have equals. We also have less than, greater than, less than or equal to, greater than equal to, equal to, not equal to – which is these less than/greater than signs. And then AND and OR. So, you guys have seen some of these, but we can use these. So, I can say like major not equal to chemistry.
2:05:02
So, this is going to select all the students where the major is not equal to chemistry. So, if I run this, now we get all the students except Claire because Claire is a chemistry major. Or we can do the same thing for numbers. So, I can say like student_id. So SELECT ALL the students where student_id is less than 3. And we need to get rid of this. And so, now we'll get all the students who have student ID’s. Let me get all of these. So, we're only getting students 1 and 2.
2:05:32
We're not getting anything – or we can do less than equal to 3. And now this will also give us that student ID equal to 3. And you can combine these. So, like where student_id is less than 3 and name is not equal to Jack. So, if we run this now, you'll see we get Kate and Claire, but we don’t get Jack who is student ID number 1 because it didn’t fit the condition. So, you can these all these comparison operators to compare, you know, different things.
2:05:58
And by using them inside of this WHERE statement over here you can seriously filter the results down to only like the few that you need. All right, so I want to show you guys one more cool thing we can do which is using the IN keyword. So, instead of just like putting a little condition like this, we could say WHERE. And then we could say like the name of one of the columns. So, like name. And then I can say IN.
2:06:21
And then over here I can put some parenthesis and I can put some values in here. So, if the name is Claire, Kate, or Mike. So, basically what this is saying is SELECT ALL from student WHERE the name is IN these like this group of values, right? So, if the name is Claire, Kate, or Mike, then it’s going to select that. So, now I can click Run and you'll see down here, we get all the entries, Kate, Claire, and Mike.
2:06:48
So, this is a really easy way to compare like one column to a bunch of different values. So, like we could check to see if the name is in there. We could also do the same thing for like major. So, like WHERE major IN ‘Biology’ or ‘Chemistry’. And now if we run this, you'll see that we get Jack and Claire because they’re both biology. So, we can use IN. And you can also combine all these things together.
2:07:13
So, you could say like WHERE major IN ‘Biology’ and ‘Chemistry’ AND student_id is greater than 2, right? And now I'll click Run and you'll see that we get these two students, right? So, you can combine the SELECTs and the WHEREs and even like the LIMITs and all that other stuff to make these kind of complex queries. But that’s sort of the basics of doing these queries. I mean obviously these are very simple queries. And as the database schemas get more complex,
2:07:47
the queries that you need to select specific pieces of information are also going to get more complex. And we're just using one table here, right? We're just getting information from the student table. But if we had multiple different tables, you know, maybe certain tables have like foreign keys to other tables, like getting information can get pretty complex. And as we go forward in the course, we're actually going to design a more complex database schema.
2:08:10
And using that database schema, we're going to learn more about using these select commands. But for now, that kind of shows you guys the basics. And so, what you want to do is just kind of play around with these, right? I mean we have our student table. It’s very simple. It has three columns. And you know, just play around with getting specific pieces of information using these WHEREs and INs and all of these different keywords in order to select the information that you want.
2:08:36
[Company Database Into] In this tutorial I’m going to introduce you guys to a more complex database query that we're going to be using for the rest of the course. And so, up to this point in the course we've learned a lot of stuff with SQL. We learned how to create tables, insert data into those tables. We learned about the different datatypes. We also learned how to update and delete data. And we learned how to query and select data from the database, right?
2:09:04
So, we learned a lot of the core concepts in SQL. And up to this point, if you guys have been following along, then you have a pretty good fundamental understanding of the different things that we can do in SQL. Now, obviously we didn’t cover everything, but we covered a lot of the basics, right? And we've been using this student table which just has three columns. One primary key. And, you know, we learned how to create this table. We learned how to insert all this information.
2:09:27
We learned how to update and delete. And then also just, you know, query for the specific language. Here’s a problem though, is this is a very simple example, right? I mean it’s one database table and it only has three different columns. But in reality database schemas are going to be a lot more complex than this, right? The chances sort of your database schema just having one table is going to be pretty slim.
2:09:52
And so, you know, to really master SQL and to really learn about all the different features. And there are certain features that we haven’t covered yet that I want to cover. We're going to need a more complex database schema. There’s certain things that I just can’t show you guys on this student table because it’s just not complex enough, right? So, what I actually did is I went ahead and designed another database schema.
2:10:17
So, I actually designed a database that could be used for a company. So, I’m going to go ahead and pull that up. And right here we have our company database. And you can find this .pdf in the description below. I’ll put a link to it. But basically this is the database schema that we're going to be using for the remainder of the course. So, this is a more complex database schema. But because it’s complex, it’s going to give us some awesome opportunities to query it, right?
2:10:44
In other words, the more complex the database schema, the more complex the queries we're going to be able to write and play around with. Because there’s only so many types of queries that we can write for that student table. But this is going to be a better example for us to learn about different types of queries and stuff like that. So, I’m going to go ahead and walk you guys through this schema in this tutorial. That way you guys understand it.
2:11:07
Because if you don’t – if you can’t wrap your head around it, then you’re not going to be able to follow along with the rest of the course. So, I’m just going to give you guys an explanation of this. And that way we have it going forward. So, this database schema maps out the database for a company, right? So, this would be like the information that we might want to store about a company. So, up here we have our first table which is the Employee table.
2:11:29
And so, this is just going to store information about employees. So, we're storing like the employee ID, the first name, the last name, birth date. So, you'll see here, this is actually a date. Sex which is going to be male or female. Salary, which will be like how much they make. And then over here we have two foreign keys. So, the primary key of this table is the employee ID over here which we have in red. The foreign keys are going to be these keys over here in green.
2:11:55
And basically, a foreign key is just – it’s going to store the primary key of an entry in a different table, right? So, we have two foreign keys. The first here is super_id. That stands for supervisor ID. So, an employee in our company is going to be able to have a supervisor. And a supervisor is actually going to be another employee. So, super_id is going to point to another employee in the employee table. We also have branch_id.
2:12:23
So, different employees in the company are going to be able to work for different branches. And you'll see down here we have this branch table. And it just has a branch ID, a name, and then also some other stuff which we'll talk about. So, an employee can work at a branch, right? And that’s kind of what we're storing over here. So, let’s take a look at this. We would say that the employee Michael Scott, his super_id is 100. That means Michael Scott’s supervisor has an ID of 100.
2:12:52
So, Michael Scott’s supervisor is going to be David Wallace, right? Because David Wallace has an employee ID of 100. That means Kelly Kapoor’s supervisor is going to be employee 102. So, it’s going to be Michael Scott, right? So, hopefully that makes sense, right? An employee can have a supervisor. And super_id is a foreign key which points to the employee ID of another employee. And then we also have branch_id over here again. And this will point to the branch. So branch_id.
2:13:25
Angela Martin has a branch ID of 2. That means Angela Martin works at the Scranton branch. Andy Bernard has a branch ID of 3. That means Andy Bernard works at the Stamford branch. So, hopefully that kind of makes sense. And then down here, like I said, we have the Branch table. And the Branch table just has an ID, a name. And also has a manager ID. So, on the Branch table we're actually storing the ID of an employee who is the manager. So, this is actually a foreign key.
2:13:56
So, manager ID is going to point to one of the employees up here. So, we would say that the manager of the Scranton branch has an ID of 102. So, the manager of the Scranton branch is going to be Michael Scott because he has a 102. The manager of the Stamford branch has an ID of 106. So, the manager of the Stamford branch is Josh Porter because he has an ID of 106. Hopefully that makes sense. And then we're storing the date that the manager started being the manager.
2:14:24
So, now we'll check out the client table. So, the client table has a client ID, client name, and also has a foreign key branch ID. So, we would say that the client Dunmore High School, you know, works with branch number 2. Or the client Times Newspaper works with branch number 3. So, Dunmore Highschool would be a client of branch number 2 which is the Scranton branch over here, right? Hopefully that makes sense.
2:14:53
The Times Newspaper is a client of branch number 3 which is the Stamford branch. So, that’s kind of how those are connected. And then over here we have another one which is branch supplier. So, this has a compound primary key, or a composite key. And the first part of it is the branch_id. So, a Branch Supplier is obviously going to store the branch_id. And it’s also going to store a supplier name. So, it’s important to notice that we need a composite key here
2:15:23
because the branch_id doesn’t uniquely identify each row. And the supplier_name doesn’t uniquely identify each row. Only together can they uniquely identify each row. And then the last table down here is the Works_With table. So, this basically defines the relationship between employees and clients. So, we're going to go ahead and assume that an employee can work with a client and sell the client different products. So, employee 105 sold $55,000 worth of paper to client 400.
2:15:54
Employee number 108 sold $22,500 worth of product to client_id 402, etc. So, this is kind of mapping the relationships between employees and clients so telling us how much a certain employee sold to a certain client. And you'll see this is also a composite key. So, this is the database schema that we're going to be working with for the rest of the course. And like I said, in order to kind of show you guys some more advanced SQL queries,
2:16:23
we're going to need a database schema that is, you know, complex just like this one. If this isn’t super clear to you, what you might want to do is just kind of look over the pdf that I'm going to include in the description below. And really, what you want to do is just trace the relationships. So, you know, like really make sure that you understand how this database schema is put together, how everything relates to each other.
2:16:45
And then going forward in the course, we're going to be using this database schema. So, in the next tutorial I’m going to show you guys how to create this. So, I’m going to actually give you all the code for creating this entire database. And then from there we'll go ahead and we'll start learning some more advanced queries and some more advanced stuff that we can do. [Creating Company Database] In this tutorial I’m going to show you guys how we can create a complex database schema in SQL.
2:17:14
So, in the last tutorial I showed you guys this database over here. It’s this company database. This is basically just an example database that I created. And it’s a database that we're going to be using for the remainder of the course in order to learn a little bit more advanced SQL querying and stuff like that. So, this is a company database. And we have a bunch of tables like this employee table, branch table, works with table, etc.
2:17:35
In the last video I kind of talked about what each of those did. We looked at some of the different relationships between the tables. So, in this video I’m going to show you guys how we can actually implement this database. So, how can we take this database and actually create it in MySQL? So, not only are we going to create all of these tables and we're going to define all of these relationships, like the foreign keys and all that stuff.
2:17:58
We're also going to populate all of that information. So, I’m going to populate these database tables with all this information. That way we can use that information going forward for the examples. So, this tutorial is going to be pretty cool because I’m going to show you guys how to build an advanced database schema just like this. It’s a little bit more complex than the student table we had looked at before.
2:18:19
And before we get started, I just want to say all of the code that I'm going to be using in this tutorial is going to be available in the description below. So, there’ll be a link that you can click on and I’ll have all of this. So, you know, you don’t have to type out everything that I'm typing out. And in fact, I’m just going to be pasting in a bunch of stuff so that way you don’t have to type anything. So, the first thing we want to do is we're going to drop that student table.
2:18:42
So, I’m just going to go ahead and DROP TABLE student. And we'll go ahead and drop that table. That way we don’t have to worry about it. And so, once we've gone ahead and dropped the student table, now we can start creating all of these tables for our company database. So, I’m just basically going to paste in the code for creating each of these tables. I already have it all written out. And I’ll kind of walk you guys through what it is and what we're doing.
2:19:06
So, here we have CREATE TABLE employee. So, we're going to create this Employee table. We have our employee ID which is an integer. And this is going to be the primary key of the table. And then we also have first_name, last_name, birth_day. So, birthday is actually a DATE as you can see over here. We haven’t used the DATE datatype yet, but we're using it now. DATE will allow us to store a date just like this, with a 4-digit year, 2-digit month, and a 2-digit day.
2:19:28
We’re also storing their sex. So, like male or female. And that’s just a VARCHAR(1). We're storing the salary. And then we're also storing these supervisor ID and the branch ID. And if you guys remember from the last video, the supervisor ID is a foreign key which points to another employee. And the branch ID is also a foreign key which points to the branch table. Now, here’s the thing. We can’t actually make these foreign keys just yet
2:19:53
because the employee table doesn’t technically exist yet. And the branch table doesn’t technically exist yet because I haven’t created them. And so, I can’t define these guys as foreign keys just yet. We're going to do that later and we'll add that in afterwards. So, I’m going to go ahead and run this and we'll create the employee table just like that. So, now we're going to go ahead and create the branch table. So, I have the code for the branch table right here.
2:20:17
So, we're going to CREATE TABLE branch. And this is just this guy down here. So, it has a branch_id which is the PRIMARY KEY. Branch name. And the manager ID. So, remember, the manager ID is down here in green. The manager ID is also a foreign key. So, the manager ID, we're actually going to be defining as a foreign key which points to the employee table. And then we have the manager start date, which is DATE. So, down here, I’m defining a foreign key.
2:20:44
So, in order to create a foreign key I can just say FOREIGN KEY and then inside of parenthesis put the name of the column that I want to be the foreign key. In our case, it’s mgr_id. And then I can say that it references employee. And then inside parenthesis just the name of the column in the employee table, which is going to be emp_id. And then finally, I'm going to do one more thing which is over here. I’m going to say ON DELETE SET NULL.
2:21:10
And we’re going to talk more about what ON DELETE SET NULL does in a future video. But for now, just know that whenever we're creating a foreign key, we're going to put ON DELETE SET NULL. Or we can also put something called ON DELETE CASCADE. Which again, I’m going to talk about in a future video. But just put that in there and that’ll make it a lot easier for us to manage this foreign key. So, now I'm going to go ahead and run this and we'll create the Branch table.
2:21:34
So, looks like that went well. All right, so next thing we need to do is we need to set the super_id and branch_id of the Employee table as foreign keys. So, remember, down here in the Branch table we set the manager ID as a foreign key. But we weren’t able to do that with the supervisor ID or the branch ID in the employee table because the Branch table and the Employee table haven’t been created yet. So, I’m going to show you guys how we can do that.
2:21:59
So, down here I have two little blocks of SQL code. The first one is altering the table employee. And I’m just saying ADD FOREIGN KEY. And then inside of parenthesis we're putting branch_id. So, that’s going to be the foreign key. REFERENCES branch, and then branch_id. And ON DELETE we're going to SET NULL. So, what this is going to do is it’s going to add branch_id as foreign key to the employee table. So, I’m going to run this. And now this is going to be a foreign key.
2:22:29
And then down here we can do the same thing, but for supervisor_id. So, you see supervisor_id right there. I’m going to go ahead and run this. And this will add the supervisor ID as a foreign key just like we did with the branch ID. So, we needed to do that because when we created the Employee table, the Branch table and the Employee table hadn’t been created yet so we couldn't add them as foreign key relationships. All right, so now we're going to add the Client table.
2:22:52
So, you'll see over here, CREATE TABLE client. And we're just storing the client_id as a primary key, client_name, branch_id. And then we're going to make the branch_id a foreign key. So, over here on the Client table you'll see that the branch_id is a foreign key. It points over to branch. So, we're going to define that relationship here. I’m just saying FOREIGN KEY (branch_id) REFERENCES branch (branch_id). And then once again we're just going to say ON DELETE SET NULL.
2:23:19
So, let’s go ahead and create the Client table. I’m just going to run this. And that’ll create the Client table. So, next we have the Works With table. So, the Works With table is actually pretty unique because it has a composite primary key. So, the primary key has the employee ID and the client ID. And actually, what’s unique is that each component of the primary key is a foreign key. So, employee ID is a foreign key. And the client ID is a foreign key.
2:23:44
And so, over here, we can create this table. I have employee ID, client_id, total_sales. The primary key is employee ID and client ID. And then the foreign keys are employee ID and client ID. And you'll notice over here, instead of saying ON DELETE SET NULL, I’m saying ON DELETE CASCADE. And again, I’m going to talk more about ON DELETE SET NULL and ON DELETE CASCADE in a future video.
2:24:07
But for now, just know that you need to have this here in order for everything to kind of be set up correctly. So, I’m going to go ahead and run this and we'll be able to insert or create the Works With table. And then finally, we're going to create our last table which is the Branch Supplier table. And this is actually kind of similar to the Works With table. So, down here we have the Branch Supplier table. It also has a composite key. So, its key is made up of multiple columns.
2:24:33
And the branch_id column is a foreign key, but the supplier_name column isn’t a foreign key. So, this one is actually pretty interesting as well. So, we have branch_id, supplier_name, supply_type. And then the PRIMARY KEY is branch_id and supplier_name. And the foreign key is just branch_id. And again, with this one, on the foreign key, that’s also part of the primary key. I’m just saying ON DELETE CASCADE. So, that’s going to be what we're going to need there.
2:25:00
So, I’m to go ahead and run this. And you'll see over here that everything got entered in correctly. So, now we have all of these tables created, right? We created all the tables for our database schema. And so, what we're going to do now is we're going to actually insert information into those tables. Now, when we're inserting information into these tables, because we have all of these like foreign key relationships, we're actually going to have to do it a specific way.
2:25:23
And so, I’m going to walk you guys through how we might do something like this. And it’ll give you an idea of how you can do it. So, I’m actually going to make the text a little bit smaller. So, over here I’m going to show you guys how we could insert all of information for the corporate branch. So, inserting the employee and the branch entries for the corporate branch. Now, you'll notice over here that the Employee table and the Branch table have foreign keys that point to each other.
2:25:47
So, the employee table has an entry over here, branch_id, which points to the branch or points to a specific branch. And each branch has a column here, manager ID, which points to a specific employee. So, there’s like this circular relationship. So, when we're inserting these elements, we're going to have to do it in a specific order. So, over here I’m just starting with, like I said, the corporate branch. So, I’m inserting into the employee table VALUES 100, David Wallace.
2:26:15
So, I’m inserting in this David Wallace row over here. And you'll notice that I put all this stuff in here. But when I get to branch_id, which is this last element over here, it should be 1, right? So, David Wallace should belong to the corporate branch. But the problem is that the corporate branch hasn’t been created yet. So, I’m just going to set this equal to NULL because that branch hasn’t been created yet. And I’m going to go ahead and insert David Wallace in there.
2:26:42
So, I’m just going to run this. And then the what I'm going to do is I'm going to insert the branch into the Branch table. So, I’m inserting into the Branch table VALUES 1, Corporate, 100. So, now that I – since I already inserted the David Wallace employee, I can set David Wallace’s employee ID as the manager ID on the branch row. And so now I’m inserting in the corporate branch. So, I’m going to go ahead and do that.
2:27:07
And then what I need to do now is I need to update that David Wallace entry to say that he works for the corporate branch, right? So, down here I’m saying UPDATE employee. SET branch_id equal to 1. WHERE employee ID is equal to 100. So, that will go ahead and update David Wallace. So, the last thing we're going to do now is just insert the last employee into the corporate branch. So you'll see Jan Levinson is actually getting inserted into there. So, I'm going to go ahead and run this.
2:27:33
And so, now we have all of our employees inserted into the corporate branch. I’m going to do the same thing for the other branches. So, I’ll do the same thing for the Scranton branch. And here I have the code to do that. So, again, I’m inserting in the manager of the Scranton branch. So, I’m doing that right now, which is Michael Scott. And then I’m inserting the actual Scranton branch. And then I’m updating Michael Scott to say that he works at the Scranton branch.
2:27:56
And then finally, I’m adding in Angela, Kelly. And I’m also adding in Stanley. So, now I have all of the employees in the Scranton branch. And again, we have to do it that way because we have this like circular relationship with the foreign keys between the Employee and the Branch table. So, then finally we'll do the same thing for the Stamford branch. I’m inserting the manager of the Stamford branch. And then I’m inserting the actually Stamford branch.
2:28:25
And them I’m updating the manager of the Stamford branch to say that he works at the Stamford branch. And then I’m adding in these other employees. So, Andy Bernard and Jim Halpert. All right, so now that we've done all stuff with the employee table and the branch table, we can kind of move onto doing the other ones. And hopefully, that shows you how you might insert information or how you might have to insert information into a more complex database schema, right?
2:28:48
When we're just inserting into the student table, it’s really easy. But when we have foreign keys linking all over the place, it can get a little bit complicated. So, now though that is the most complex inserting we're going to have to do. So, now we can just insert normally. So, we can INSERT INTO branch_supplier. And I’m just going to go ahead and click through all of these and insert them in turn just like this. All right, so I’ve gone ahead and ran each one of these INSERT statements.
2:29:13
So, we inserted everything into the Branch Supplier table. Now, I’m going to insert everything into the Client table. And again, this information is just the information that you see over here that’s just written out into, you know, database or SQL commands. So, I’m going to go ahead and insert each one of these. And then finally, we'll insert into the Works With table. So, again, this is just a bunch of numbers and stuff like that.
2:29:35
All right, so now we've gone ahead and populated all of these database tables with all the information. So, why don’t we check it out? I’m going to say like SELECT ALL from employee. So lets see all the different employees that we have, make sure everything worked. So, down here you'll see that we have all of these different employees. We can do the same thing for like Works With. So, let’s see if we got all that data. And you can see that we do.
2:29:59
So, now our database is actually populated with all of the information that I have over here. So, again, you can get all that code from the description below. But hopefully this kind of shows you guys how, you know, we can go about designing a database schema or, you know, go about creating a database schema like this inside of MySQL. You can see it’s not as straight forward as it was with the student table. But we can do different things to make it happen. [More Basic Queries]
2:30:30
In this tutorial I’m going to show you guys some awesome SELECT statements which will allow us to query the company database that we've been setting up for the last couple tutorials. So, basically, I’m going to give us some little prompts and we'll see if we can figure out how to get the data. So, first thing we're going to do is try to find all employees. So, our first task is to get all the employees from the employee table.
2:30:54
And you'll see over here, this is essentially all of the data that we have stored in our database. So, let’s see if we can find all employees. I’m going to say SELECT and I’m just going to say *. So, SELECT * would mean SELECT ALL COLUMNS from the Employee table. And this should actually do it. So, just by saying this, I’m going to run this and we'll see what we get. So, down here in the results we have returned all the employees. Cool. So, we were able to get all of the employees.
2:31:22
Let’s try another one. How about we'll try to find all clients. So, now instead of finding all employees, we'll see if we can find all the clients. So, basically it’s the same thing. But all I have to do is change employee to client. So, now we're grabbing information from the Client table. So, I’m going to run this and you'll see down here we’re getting all of the clients. Cool. So, that’s, you know, how we can just get all the stuff from a single table. Let’s try another one.
2:31:46
Find all employees ordered by salary. So, this one is a little bit different. Not only do we want to find all the employees, but we want to order them by how much they make. So, we'll say SELECT ALL from employee. And now we want to order. So, we can just say ORDER BY. And we want to specify what column we want to order these by. So, we could say salary. So, now I’m going to run this. And you'll see down here, now we get all the employees but they’re ordered by how much they make.
2:32:14
So, down here, this employee makes 250k a year. And it goes all the way down to 55k a year. Let’s see if we can do this though. So, the richest employee, or the employee that makes the most starts. So, we can say DESC for descending. And now this will list them all out in descending order. So, the 250,000 guy starts up at the top. And then we go down to 55,000. All right. So, that’s pretty cool. And that shows how we could execute that query. So, let’s try another.
2:32:40
It says Find all employees ordered by sex then name. So, what we want to do is order all the employees by what sex they are. And then within those orderings, we want to order them by their name, alphabetically. So, I can say SELECT ALL FROM employee. It’s the same. ORDER BY – this time, we're going to do sex. So, why don’t we do first name. And then we'll do last name. So, now when we run this, you'll see here all of these ordered first by sex. So, we get female and then here’s all the males.
2:33:10
And then within that, they’re actually ordered alphabetically. So, we'll start with the first name. So, Angela is first, then Jan, Kelly. And then these are all the girls. And then down here we have the guys. So Andy David Jim Josh etc. And then if there were any duplicates with the first names or there were, you know, two that were the same, then we would defer to the last name as we specified up there. All right, let’s try another query prompt.
2:33:34
So, we'll ask for a different piece of information. So, it says Find the first 5 employees in the table. So, this is a situation we could say FROM employee. SELECT ALL FROM employee. And here we can limit it to 5 employees. So, SELECT ALL FROM employee. LIMIT 5. That will give us the first five employees. So, you'll see down here, we just get the first five like that. All right, let’s do a few more and we'll see if we can mix it up a little bit.
2:33:59
So, let’s try to find the first and last names of all employees. So, this time we're actually just trying to get the first and the last names. So, up here we could say – instead of SELECT * we can just SELECT first_name and last_name. Just like that. So, over here I’m going to click Run. And you'll see now instead of getting all that stuff, we're just getting first and last name. So, I’ll actually show you guys another thing we can do. So, here’s the prompt.
2:34:24
It says find the forename and the surnames of all employees. So, this is actually similar, but you'll see down here when we return to the first and last names, the columns were named first_name and last_name. But there’s another keyword that we can use in SQL which is called AS. So, I can say SELECT first_name. And I can say AS forename. And we can say last_name AS surname. And what this is going to is it’s going to return the first names and last names,
2:34:53
but instead of naming the columns first_name and last_name, it’s going to name them forename and surname. So we're going to go ahead and run this. And you'll see we get exactly the same thing except now it’s calling the columns forename and surname. So, that’s kind of a useful thing you can do. And there’ll be certain circumstances where you want to name the columns differently depending on what you need. All right, so there’s one more I want to show you guys,
2:35:14
and I’m actually going to introduce you to a new SQL keyword. So, the prompt is find out all the different genders. So, what we want to do is figure out what are all the different genders that employees are stored as inside of the table. So, what we can do is we can use this special keyword called DISTINCT. So, I can say SELECT DISTINCT. And then the name of the column that I want to select DISTINCT. And what this is going to do is when I run this, you'll see down here we're getting M, F.
2:35:40
So, we're getting male and female. So, those are all of the different sexes that are stored inside of the table. I could do the same thing for something else. So, we could do like SELECT DISTINCT branch_id. And this will tell me all the different branch ID’s that are stored on the employee. So, you'll see down here we have different branch ID’s 1, 2, and 3. So, those are all of the distinct branch ID’s that employees have.
2:36:03
And so, that’s this DISTINCT keyword is pretty useful if you want to find out like, you know, what are the different values that are stored in a particular column. All right, so that should kind of show you guys. There’s a little bit more about how we can use these select queries. And it kind of gave you guys a chance to see how we might query data from the company database schema that we set up. [Functions] In this tutorial I’m going to show you guys some SQL functions.
2:36:32
Now, an SQL function is basically just a special little, kind of like block of code that we can call, which will do something for us. So, these will like count things or they'll give us averages or they'll add things together. And they can be really useful for getting information about the data in our tables. So, I’m going to give you guys a couple different prompts and then we'll figure out how we could solve them. So, over here, I have a prompt that says Find the number of employees.
2:36:56
So, this is kind of a, you know, maybe something that you’d want to do. We want to figure out how many employees are in the database. So, what I could do is I could say SELECT. And I can use a special SQL function called COUNT. So, I can just type COUNT like that. And then open and close parenthesis. And in here I want to put whatever I want to count. So, basically we could just put in like emp_id. And this will basically tell us how many employee ID’s are inside of the table.
2:37:22
And since the employee ID is the primary key, that’ll tell us how many employees we have. And then I can just say FROM employee. So, basically what I’m doing here is I’m asking SQL to select how many employees are inside of the Employee table. So, when I run this, you’ll see down here we get 9 because there’s 9 employees inside of the table. And you can see over here we have 100 through 108 which is 9. Another thing we could do would be to count how many employees have supervisors.
2:37:51
So, this is going to be a different number. You’ll see over here that 1 employee, David Wallace doesn’t actually have a supervisor. So, we could check that out. We could say SELECT. And I can say super_id. And now when I run this, we should get 8 instead of 9 which we do down there. So, this will count how many entries in the database table actually have values. Another thing we can do – and here’s another prompt, which should be kind of interesting to see if we can figure it out.
2:38:19
It says Find the number of female employees born after 1970. So, this is a little bit more specific, but nonetheless it’s kind of the same. So, first thing we want to do is count how many employees we have. So, I’m going to select COUNT employee ID FROM employee. But I want to limit the results that we get. So, I’m going to say WHERE. And here, I’m going to check these conditions. So, I’m going to say sex is equal to female. And birth_date is greater than.
2:38:49
And over here, I’m actually going to put in a date. And so, when I put in I date, I’m just going to put the 4-digit year. So, I can say, 1970. And then a hyphen. And then put in the month. So, we'll just say 01 and then 01. So, this would be like January 1st, 1970. So, this will tell us the number of female employees born after 1970. Or I guess we would put this as 1971. So, let’s run this. And now you'll see that we get a count of 2.
2:39:17
So, there are 2 female employees in the database table that were born after 1970. And you can see over here, looks like we have one born in 1971. Angela Martin. And then one born in 1980. So, that actually makes sense. All right, lets try another one. Here’s another prompt. It says Find the average of all employee’s salaries. So, this is a little bit different. In this case, we want to find out the average of all of the employee’s salaries. So, instead of COUNT we can say AVG.
2:39:46
And then inside of parenthesis we can just put again the column. So, I can say salary. And then over here we can say FROM employee. And what this will do is it’ll tell us the average of all the employee salaries. So, I’m going to go ahead and run this. And you'll see down here the average looks like $92,888. And so, if we wanted, we could filter this further. So, let’s say I wanted to find the average from all the employees who are male.
2:40:12
So, I could say WHERE sex is equal to male, just like that. And now this will give us the average of all the male salaries. So, here it looks like it’s a little higher, 101,333. That’s probably because David Wallace makes 250 grand a year. All right, so that’s kind of interesting. Let’s try another one. So, how about this. Why don’t we try to find the sum of all employee salaries? So, instead of finding the average, we're going to find the sum. So, over here, instead of AVG I can say SUM.
2:40:43
And that stands for SUM. And what this will do is it’ll add up all the entries for the salaries. So, this would basically tell us like how much the company is spending on payroll to pay the employees. So, over here I’m going to click Run. And you'll see the company is spending a total of $836,000 on payroll. So, that’s how much the company is paying its employees. I want to show you guys one more thing we can do which is called aggregation.
2:41:06
And aggregation is basically where we can use these functions and we can display the data that we get back in a more helpful way. So, I have a prompt up here that will kind of get us started with this. So, the prompt is find out how many males and how many females there are. So, this is actually an interesting point. Let’s say we want to figure out how many males or females were in the company. Well, I can say over here SELECT COUNT. And instead of saying salary, I’m going to say sex.
2:41:33
And so, let’s say that we just did this, right? This is going to tell us how many employees there are that have an entry in the sex field, right? So, we get 9. But if we wanted to also display how many males and how many females there are, we're going to have to do a couple things. So, over here I can say COUNT comma. And then I can just say sex. So, what this is going to do is it’s going to return not only the count, but also it’s going to return which sex it is.
2:41:59
So, I’m going to click run. And you'll see over here there are 9. And it says male. But this still isn’t exactly what we want. So, what we can do is we can come down here and we can say GROUP BY. And what this is going to do, is it’s going to group this information by the column that I put over here. So, now when I run this, you'll see it’s going to tell us exactly what we want. So, it’s going to say 3 Female and 6 male.
2:42:22
And that’s because I’m telling SQL – I’m telling MySQL to group the information that it gets by sex. So, it’s counting how many entries or how many employees have an entry in the sex column. And then what it’s doing is it’s printing this data out alongside of whether they're male or female. And it’s giving us that information. So, that is what we would call like aggregation. And its really awesome. And you can use this GROUP BY keyword to do that. So, let’s do that again.
2:42:54
Why don’t we try another one? Here’s another prompt that we can look at. It says Find the total sales of each salesman. So, this is kind of interesting, right? Down here we have this Works With table. And this kind of gives the information about which employees sell what. And you'll notice I have like employee 105, right? Right here, sold 55,000. Employee 105 also sold 33,000 and also sold like 130,000. So, what if we want to figure out the total that each employee actually sold?
2:43:23
Well, I can do something similar. So, I can come over here and I can say SUM. And here we're going to say total_sales. And then over here we'll print out the employee ID. And instead of grabbing this from the employee table, we're going to grab this from the Works With table. And then we want to GROUP this BY employee ID. So, basically what this is going to do is it’s going to tell us how much each employee has sold. So, you'll see over here, employee 102 sold $282,000.
2:43:53
Employee 105 sold 218,000. 107 sold 31,000, etc. So, we're able to get all of that information given to us. Now let’s say if we wanted something slightly different. So, let’s say that instead of finding the sales of each salesman we wanted to figure out how much money each client actually spent with the branch. Well, instead of using employee ID over here, we can just say client ID. And we'll change this to client ID as well.
2:44:21
And now what this will do is it’ll tell us how much each client spent. So, I’m going to run this and you'll see down here we get all this information. So, client 400 spent $55,000. Client 401 spent $267,000, right? And so, we can use aggregation in order to organize the data that we get from using these functions. So, I can add up the total sales of each client. And I can group them by client ID. And so, that’s kind of how that works. And so, aggregation can be extremely useful.
2:44:53
And it’s definitely something you want to play around with. So, we have this entire database schema over here. And so, what you want to do is just kind of, you know, try to give yourself little prompts like I’m giving up here, right? So, I kind of wrote out these little prompts. And they're kind of like little problems that we need to solve. So, we want to, you know, be able to figure out all the different information from the table. [Wildcards]
2:45:16
In this tutorial I’m going to talk to you guys about wildcards and the LIKE keyword in SQL. Now wildcards are basically a way of defining different patterns that we want to match specific pieces of data to. So, this would be a way to kind of like grab data that matches a specific pattern. And I’m going to show you guys exactly how this works. So, I have over here a prompt. And it just says Find any clients who are an LLC. So, this is kind of an interesting prompt.
2:45:45
Let’s say that we want to query our database and find any clients who, you know, were like an LLC. And you'll see over here in the Client’s table, we actually have one, John Daly Law, LLC, right? So, this is a limited liability company. And this is kind of what we're looking to find with our search. So, I’m going to show you guys how we can use wildcards in order to find something like this. So, I could say SELECT ALL FROM client because I want to grab a client.
2:46:11
And now I can use the WHERE keyword. So, I want to filter the results, right? WHERE – I’m going to say client_name. And I’m going to use this keyword called LIKE. And LIKE is a special SQL keyword which we're going to use with wildcards. Now, we can say LIKE. And then over here we want to write out a little expression or a little statement. So, I’m going to make an open and closed quotation mark. And we'll put a semicolon over here.
2:46:36
And now, inside of here, I can uses a couple of different special characters. So, basically what I can do here is define a pattern, okay? And if the client’s name – if the specific client’s name matches the pattern that I define over here, then this condition or this statement here will be true and we'll end up returning that client. And so, we can basically use wildcards to do a bunch of different stuff. So, inside of these quotation marks we can use two special characters.
2:47:07
There is this percent sign. And this stands for any number of characters. And then there’s this underscore which stands for one character. And I can use these in order to define certain patterns that can be used by the database in order to find what we need. So, over here I could say LIKE. And I could say %LLC. Basically, what this pattern is saying is if the client’s name is LIKE this pattern, then we want to return it.
2:47:36
So, in other words, if it’s any number of characters and then an LLC at the end, then we want to return it. So, this percent sign is going to stand for any number of characters. So, that means any characters. Anything can come before that. But that would mean that the name has to end in LLC. So, you'll see over here John Daly Law, LLC has a bunch of characters here. And then the last three characters are LLC. And that’s kind of standard.
2:48:01
Most limited liability companies will be set up like that. It’ll be like company name, LLC or John Daly Law, LLC. So, this wildcard, if there’s any company names like that that end with LLC is going to catch them because it’s using this percent sign because basically means any number of characters can come before this. And then LLC. If you’re familiar with regular expressions, this is very similar to regular expressions. It’s just a more simplified version.
2:48:28
So, I’m just going to click Run. And you'll see down here we returned John Daly, LLC, just like that. So, that’s how we could use these wildcards and this LIKE keyword in order to find something like that. So, why don’t we try another one? So, I have another little prompt here that we can see if we can figure it out. So, it says Find any branch suppliers who are in the label business. Okay, so this is kind of interesting. Find any branch suppliers that are in the label business.
2:48:56
Well, over here we're going to change this. So, instead of looking in client we're going to look in branch_supplier. And then down here we're going to do the same thing. Except here, we're just going to say supplier_name. So, over here in the Branch Supplier table we have a bunch of different branch suppliers, right? These would be like companies that supply products to our company. And you'll see there’s actually a couple of these that have the word Label in them.
2:49:24
And so, if the word Label is in the company’s name, then we know they probably sell labels. And so, what I could do is I could say use a wildcard to see if the word label shows up anywhere inside of their names. So, what I could do is I’m actually going to come over here and I’m going to change this up. So, basically we're going to have any number of characters. We're going to use this %. And then I'll just say Label. And then we'll make another one with another %.
2:49:53
So, actually, why don’t we check to see if the word Label is actually in there. So, it’s going to be % and then Label, just like that. So, this is going to be our wildcard. So, this will basically match if the supplier name has the word Label in it somewhere. So, now I’m going to run this. And you'll see down here we get this company, J.T. Forms & Labels. And so, actually I thought there was two of them. Let me see. Okay yeah. So, it looks like I have a typo here.
2:50:19
So, it looks like when I was inserting the data I spelled Labels wrong on this column. So, that’s going to be why that one is not showing up. But if we had – if I had spelled this correctly, then Stamford Labels would also have shown up. So, that kind of shows you guys how we can do something like that. So, why don’t we find another one. All right, so this one says Find any employee born in October. So, this is kind of interesting.
2:50:42
You'll see over here on the employee table we're actually storing the birthdates for all the employees. And they're all structured the same exact way. We have a 4-digit year, a hyphen, and then the 2-digit month. Now, October is the 10th month. So, let’s see if we have any employees. So, we have one employee down here, Jim Halpert was born in October. So, what we can do is let’s see if we can design a wildcard that will figure that one out. So, over here we can change this to employee.
2:51:10
So FROM employee. And we want to check to see if the birthdate is LIKE the wildcard. So, what we can do is we can actually use this character right here. It’s this underscore. So, the underscore represents any single character. So, the percent sign represented just like any number of random characters. Any number of characters would match it. And the underscore represents one character. And we can use that to our advantage. So, we know the way that these dates are formatted, right?
2:51:42
It’s a 4-digit date, a hyphen. Or it’s a 4-digit year, a hyphen, and then the 2-digit month. So, we could do 1, 2, 3, 4 underscores, a hyphen, and then a 10. And then we can do this percent sign. So, what this is going to match, it’s going to match with any four characters, a hyphen, and then 10. So, this should give us all of the birthdays that are in October. So, I'm going to run this. And you'll see over here we get Jim Halpert. So, it says Jim Halpert and he is indeed born in October.
2:52:15
So, you can do this for any month. We can see if there’s any employees born in February. And you'll see down here, it looks like we have two employees born in February, also born in different years. So, that kind of gives you an idea of how we could do something like that. Again, we're using these wildcards. All right, so let’s try one more. It says Find any clients who are schools. So, this one is kind of interesting. Basically, we're looking for any clients that might be schools.
2:52:44
So, what we could do is we could say SELECT ALL from client WHERE client_name. So, why don’t we search the client name – is LIKE. And then over here we'll define a wildcard. Basically, let’s just look for anything that has school in it. So, we could say %school and then %. So, this is obviously like a little bit general and broad, but hopefully it should at least give us all of the schools. So, I’m going to go ahead and run this.
2:53:09
And you'll see down here, it looks like we have 1 client that’s a school. And it’s Dunmore Highschool. And the branch ID is 2. So, that is really the basics of using these wildcards. So, again, we have the percent sign which is going to represent any number of characters. And then we have the underscore. And the whole idea is we want to build these little expressions here which we'll be able to match. So, like the client name should be able to match this expression.
2:53:35
And if it does, then we're going to go ahead and return it. And those can actually be really useful when you’re just trying to, you know, kind of query something from a database. So, imagine that you're building like a searching application and the user entered a bunch of search terms. You could use something like this in order to search the database for you. [Union] In this tutorial I’m going to talk to you guys about unions in SQL. Now, a union is basically a special SQL operator
2:54:03
which we can use to combine the results of multiple select statements into one. So, I might have, you know, two or three different select statements that I’m using. And if I want it, I can combine all of them into the same result and get just a big list or a big table back from the database. So, I’m going to show you guys how we can do this. And I’m actually going to give us some prompts so we can kind of practice.
2:54:27
So, over here, I have prompt that says Find a list of employee and branch names. So, this is kind of interesting. We can actually use the UNION operator in order to do this. So, first thing, why don we see how we could just grab just the employee names and just the branch names. So, if I wanted to just grab the employee names I could just say SELECT first_name. And why don’t we just grab the first name FROM employee, right? So, that’s pretty easy. I mean that’s as easy as it gets.
2:54:55
We're just getting all the first names from the employees. Let’s do the same thing for branch name. So, I could say SELECT branch_name FROM branch, right? Again, pretty simple. So, this over here will give us all the names of the employees. So, if I was to just run this, you'll see we get all the names of all the employees. And if I was to run this, we get the names of the all the branches. But how can we combine those together into one single statement
2:55:21
and then just get a list with all that information in it? Well, I can use the UNION keyword. So, over here I can just say UNION. I’m going to actually going to get rid of this semicolon. So, now we have one single SQL query which is going to ask the relational database management system to return not only the employee first names, but also the branch names in a single column. So, I’m going to go ahead and run this. And you'll see down here that we get this big column right here.
2:55:46
Just says first_name. And then we have all of the names of the employees, but down here we also have the names of the branches. So, Corporate, Scranton and Stamford. We have all that information. And it’s all combined with the names of the employees. So, this is actually pretty cool. And when we're using unions, you can do unions on a bunch of different things. There are a couple rules though. So, the first rule is that you have to have the name number of columns
2:56:12
that you're getting in each SELECT statement. So, in this first SELECT statement, I’m grabbing one column. In the second SELECT statement, I’m grabbing one column. If I was to come up here and also try to grab like last_name, now when I run this, we're going to get an error because up here we have two columns and down here we only have one. So, that’s the first rule. You can do that. They have to have the same number of columns. They also have to have a similar datatype.
2:56:36
So, first_name and branch_name, they’re both strings, right? So, they're both of a similar datatype, so we're able to return them in the same result. But if you had two things that were very different datatypes then it might not necessarily work out as well. So, that is basically how we can do these. And that kind of just show you guys how we can use the UNION operator in order to combine the results from multiple statements. If we wanted, we could add in another one too.
2:57:02
So, I could say like UNION again. And now we can UNION all of that with like SELECT client_name FROM client. So, now I'm grabbing not just the names of the employees and the branches, but also the clients. So, when we run this now, you can see we're getting this really long list that has all these names. Now one thing you might notice is that the column name up here is first_name. So, this is saying that it’s first_name when in reality, you know, that’s not necessarily what this is.
2:57:33
And so, over here you'll see that it’s first_name because the first SELECT statement, the column that we were grabbing was called first_name. So, that’s why that’s showing up as first_name. But if you wanted, you could change that. So, I could say first_name AS and then I can change the name. So, I can just say like Company_Names. And so, now the column name is going to be Company_Names instead of first_name.
2:57:57
So, now this is just like all the different names that we have stored in the company database. So, hopefully that kind of makes sense and that’s kind of how we can use UNIONs. I’m going to show you guys a couple other cool ones that we can do. So, I’m going to pull up another prompt here. Why don’t we take a look? It says find a list of all clients & branch suppliers’ names. So, this is actually pretty similar. So, we can SELECT client_name FROM client.
2:58:23
And then we can also UNION this with SELECT supplier_name FROM branch_supplier. And so, now we're going to get a table with all the client names and all of the supplier names. So, let’s run this. And you'll see over here we get all that. So, we have all of the clients and then we have all of the suppliers. And you'll notice over here on the Client table and the Branch Supplier table, both of these have a branch_id column. So, the Branch Supplier has a branch_id column.
2:58:55
And the client has a branch_id column. So, what we could do is we could use that. So, I could say like SELECT client_name. And we could also SELECT branch_id FROM client. And then we can SELECT supplier_name and the branch_id from the Branch Supplier. And that will give us now not only the client names and the supplier names, but also the branches that they're associated with. So, you can see down here we get all of that information.
2:59:22
Now, one thing I do want to point to is you'll notice over here we have branch_id up here and branch_id down here. So, the Branch Supplier table and the Client table both have a column called branch_id. And sometimes in a situation like this it can get a little bit confusing because we have the same column name. But associated with different tables. And so, what a lot of people will do is they'll prefix these column names with the table name. So, let’s say like client.branch_id.
2:59:52
Or they'll say branch_supplier.branch_id. And what that does is it basically just makes it a little bit more readable. So, now I know that this branch ID comes from the Client table. And this branch ID comes from the Branch Supplier table. You don’t have to do that. But in a lot of circumstances, it can be extremely useful. So, I want to show you guys one more thing we can do with UNION, so I’m going to give us another prompt here.
3:00:17
And it basically just says find a list of all money spent or earned by the company. So, this one is kind of interesting. All the money that the company either spends or earns, we want to combine into a single list. So, basically, the company earns money through the total sales down here. And the company spends money by paying its employees in the salary field up here. So, we can combine those two. So, I can just say like SELECT salary FROM employee.
3:00:46
And again, we can UNION that with SELECT total_sales FROM works_with. And so, now this will give us that column. So, you'll see over here it’s all of this money that’s either going in or out of the branch combined together. Hopefully, that gives you a good idea of how UNIONs work. Now, obviously, these are very simple examples. But you can take this and kind of extrapolate it out to more complex examples. Union basically just combines the results from two SELECT statements.
3:01:20
That’s essentially all it does. But again, there are certain rules like you have to have the same number of columns in both statements. And they have to be like similar datatype and stuff like that. But UNIONs can be very useful for sort of combining all this data into a single place. [Joins] In this tutorial I’m going to talk to you guys about JOINs in SQL. Well, JOIN is basically used to combine rows from two or more tables based on a related column between them.
3:01:50
So, JOINs can be really useful for combining information from different tables into a single result which we can then use to, you know, obviously find out specific information that’s stored in our database. So, in order to teach JOINs, I’m actually going to have us do one thing. We're going to insert another branch into the Branch table. So, over here I just have the code to do that. I’m inserting INTO branch the VALUES. So, the branch is going to have an ID of 4.
3:02:17
The branch is going to be called Buffalo. And it’s not going to have a manager ID and it’s not going to have a manager start date. So, down here in this table that we've been using so far, we have branches 1, 2, and 3. Corporate, Scranton, and Stamford. Now we're adding a Buffalo branch. But the Buffalo branch doesn’t have a manager ID and it doesn’t have a manager start date. Both of those are NULL. So, I went ahead and inserted this into my database.
3:02:42
And you'll see down here this is now our branch database. So, we have 1, 2, 3, 4. All of these pieces of information. And then the manager ID for Buffalo is NULL and the manager start date is also NULL. So, that’s actually going to come in handy for what I’m going to be showing you guys with these joins. So, if I want to follow along, go ahead and do that. But if you're not going to be following along, just know that Buffalo doesn’t have a manager ID or a manager start date.
3:03:06
So, now that we've inserted that into the database, let’s get started. So, I want to kind of show you guys what a JOIN is before I actually talk to you about what it does. So, I’m just going to go ahead and show you guys an example. I’m just going to paste in here. And then we'll talk about the example and you'll be able to see exactly what the JOIN is doing. So, over here I have this. It says Find all branches and the names of their managers.
3:03:32
So, we want to find all the branches in the database. And for each of those branches we want to find the name of the manager. So, down here you'll notice that I have this branch table. And the branch has this column here mgr_id, right? And inside the mgr_id we have ID’s of different employees. So, these ID’s relate to employee ID’s which are stored up here. Now, I want you guys to notice that the employee ID column and the manager ID column that we have down here are similar, right?
3:04:07
They’re both storing employee IDs. And so, this is basically a column that is shared between the Employee table and the Branch table, right? Both of those tables have a column which stores employee ID’s. And anytime you have a situation like that, you can use something called a JOIN. And like I said, a JOIN is used to combine rows from two or more tables based on the related column. So, in our case, the related column is the ID’s of the employees.
3:04:37
So, over here, in order to find all the branches and the names of their managers we can use a JOIN. So, here I’m saying SELECT. And I’m selecting employee.emp_id. employee.first_name and branch.branch_name. Now, up to this point in the course, we’ve never used multiple table’s columns up here in the SELECT statement. So, normally we just say like employee.emp_id, employee.first_name. We wouldn't use – or we wouldn't specify anything from the Branch table.
3:05:09
But because we're joining the tables, we can actually specify that we want to grab a column from the Branch table as well. So, just keep that in mind. So, when we get this table back, we're going to be getting the employee ID, the employee’s first name, the branch’s name. So, that will basically give us the branch and the name of the branch manager. And I want to grab that information from employee. And then I’m going to say JOIN branch. And so, when I say JOIN branch,
3:05:38
what this is going to do is it’s going to join the Employee table and the Branch table together. So, it’s going to join them together into one table. And it’s going to join them together on a specific column. Now, this is the column that both of these are going to have in common. So, I’m going to say Join branch ON. And then over here, I can basically just say employee.emp_id is equal to branch.mgr_id. So, basically I want to combine these two tables.
3:06:08
Or I want to combine all of the rows from the Employee table and all of the rows from the Branch table, as long as the manager ID of the branch row is equal to the employee ID of the employee row. So, I’m going to go ahead and run this and you guys will see what happens. And this should kind of clear up exactly what’s happening. So, down here we get three columns. We get emp_id, first_name and branch_name. And remember, we specified that up here.
3:06:35
I said employee – I want the ID, the first name, and then I want the name of the branch. So, down here, we're getting the employee ID. So, it’s 100. We’re getting the first name, which is David. And we're getting the branch name Corporate. So, this tells me that the manager of the Corporate branch is named David. The manager of the Scranton branch is named Michael. And the manager of the Stamford branch is named Josh. And if you look over there in our database table, that’s exactly right.
3:07:04
And so, essentially, we combined a row from the Branch table, this branch name, with the rows from the Employee table, emp_id and first_name into one single table, right? But we only combined them when the employee ID was equal to the branch’s manager ID, right? We have a bunch of employees over here. Michael Angela Kelly Stanley Josh Andy Jim. But not all of these employees have their ID down here in the mgr_id column.
3:07:33
So, only employees whose ID’s match the value here in the this mgr_id column were joined together into this combined table that we got down here. So, that is basically what a JOIN is. We can take rows from one table, combine them with rows from another table. And it gives us information. So, this is pretty useful information, right? We got the name of the manager for a specific branch even though that information was stored in different tables.
3:08:00
So, this is kind of the most basic version of a JOIN. And this is like just the normal join. You can see I’m just using this normal JOIN keyword up here. But there’s actually a couple other types of JOINs. So, there’s actually four basic types of JOINs that we can use. The first is just this general JOIN. And this is what’s referred to as inner JOIN. And the inner JOIN is going to combine rows from the Employee table and the Branch table whenever they have the shared column in common.
3:08:29
So, whenever the employee ID is equal to the manager ID, then that is going to get included in the table that gets returned back to us. But there’s a couple of other types of joins. And I want to show you guys what they do. So, there’s another which is called a LEFT JOIN. And you can just say LEFT JOIN just like that. And so, now instead of doing a normal JOIN we're going to do what’s called a LEFT JOIN. And I want to show you guys what happens when I run this.
3:08:53
So, I’m going to go ahead and run this. And you'll see over here that instead of just getting David, Michael, and Josh, we also got all of the other employees from the Employee table. So, we got Jan, Angela, Kelly, Stanley, Andy, and Jim. All of the employees got included in the results, right? Not just the employees who are branch managers. So, when we just use an inner JOIN, only the employees who are branch managers got included.
3:09:20
But when we used the LEFT JOIN, all of the employees got included. And here’s the reason why. With the LEFT JOIN, we include all of the rows from the left table. So, in our case, the left table is the table over here, right? It’s the employee table. And the left table is basically the one that is included in the FROM statement. So, whenever we use this LEFT JOIN, that means all of the rows in the Employee table are going to get included in the results.
3:09:46
But only the rows in the Branch table that matched are going to get included because the Branch table is like the right table. There’s another type of JOIN we can use which is called a RIGHT JOIN. And so, I can just say RIGHT. Now, this is going to do the opposite. So, instead of including all of the rows from the Employee table, no matter what, now it’s going to include all of the rows from the Branch table, no matter what. So, I’m going to go ahead and run this.
3:10:11
And down here you'll see we have all of the rows from the Branch table. So, not only did we get the rows with had managers, but we also got the rows which didn’t. So, down here, remember, the Buffalo branch which we just added didn’t have a manager. So, it didn’t actually get linked to an employee. So, therefore, both of these things were NULL. So, in the LEFT JOIN, we got all of the employees. In the RIGHT JOIN, we got all of the branches.
3:10:36
In other words, in the LEFT JOIN we got all of the rows from the LEFT table. In the RIGHT JOIN we got all the rows from the RIGHT table. So, in certain circumstances you're going to want to do either one of those. So, those are the three basic types of JOINs that we can use in MySQL. There’s actually a fourth type of JOIN that I want to talk to you guys about. Unfortunately, we can’t do it in MySQL but it’s called a FULL OUTER JOIN.
3:11:01
And basically, in a FULL OUTER JOIN, it’s basically a LEFT JOIN and a RIGHT JOIN combined. So, remember, in the LEFT JOIN, we grabbed all the employees and the RIGHT JOIN we grabbed all the branches. In a FULL OUTER JOIN you would grab all of the employees and all of the branches no matter if they met this condition or not. So, like I said, there’s not like – we can’t just come over here and say like FULL JOIN in MySQL. We're not going to be able to do that.
3:11:29
But I just wanted to let you guys know about it so that you kind of have context for it. So, these JOINs are really simple and they're really easy. And they can be really useful if you want to combine information. So, something as simple as getting the names of all the branch managers and what branches they manage is really, really easy if we just use JOINs. [Nested Queries] In this tutorial I'm going to talk to you guys about nested queries in SQL.
3:11:56
Now, nested query is basically a query where we're going to be using multiple select statements in order to get a specific piece of information. So, a lot of times we're going to want to get very specific information and we're going to need to use the results of one SELECT statement to inform the results of another SELECT statement. So, this is a little bit more advanced and this is kind of when we're getting into more advanced query writing. But I want to show you guys how this works
3:12:23
because a lot of information that you’re going to want to get is going to involve using nested queries. So, let’s go ahead and put a prompt up on the screen. It says Find names of all employees who have sold over $30,000 to a single client. So, we want to get the names of the employees, if they've sold more than 50k to a client. So, the first thing I would do if we were trying to figure this out, figure out how to write this query is let’s just look at the information that we have.
3:12:51
So, down here we have this Works With table. And the Works With table has total sales, right? And each one of these rows defines how much a particular employee has sold to a particular client, right? So, employee 105 sold $55,000 to client 400, etc, right? So, over here we have part of the information, right? In other words, here we have the total sales. But what we don’t have is the employee’s first name and they're last name, right? We don’t have the actual employee’s name.
3:13:22
What we do have though is the ID of the employee who did it, right? So, we have the employee’s ID. And we can use the employee’s ID in order to get their first name and their last name. So, in this case we had part of the data here on the Works With table. And we have part of the data up here on the Employee table. And this is a situation where we can use a nested query. So, the first thing I’m going to do is I’m going to write a query
3:13:47
which is going to get me all of the employee ID’s that have sold more than $30,000 to a single client. All right, so we're going to start with Step 1, which means we're getting all of the employee ID’s from here if they’ve sold more than 30k. So, let’s go ahead and write that query. Shouldn't be too hard considering all the stuff that we know. So, I’m going to SELECT emp_id FROM works_with. And I’m going to select it WHERE total_sales is greater than 30,000. And we'll go ahead and end this.
3:14:25
And actually, up here, I’m just going to prefix this with the table name. So, I’m going to say works_with.emp_id. And then down here we'll say works_with.total_sales just so it’s more clear. Especially when we get into nested queries it’s usually useful to prefix everything with the table name just in case we have repeated column names. So, over here I’m going to run this. And this should give us all the ID’s of the employees who have sold more than 30,000.
3:14:49
So, you’ll see we get 102 and 105 shows up three times. So, it looks like 105 has sold a lot of paper. And so, now we have all of the ID’s of the employees who have sold more than $30,000 worth of products. And so, what we can do now is we can figure out from this information, we want to get those employee’s first names and last name. And so, I’m going to go ahead and write another query up here. I’m going to say SELECT. And I’m going to say employee.first_name.
3:15:19
And why don’t we do employee.last_name. And we're going to SELECT this FROM employee. And then over here we're going to say WHERE. And this is where we're going to go ahead and use a nested query. So, basically I want to select all of the employees whose ID’s we got from this query right here. So, what I could do is I could say employee.emp_id IN – and remember, the IN keyword is going to give us a result if the employee ID is in values that we specify inside of these parenthesis.
3:15:53
So, what I could do is I can actually nest this query inside of there. So, I can go ahead and take this and I can just paste it right in here in between these parenthesis. And one thing you want to keep in mind is just how this is formatted. So, you’ll see I formatted this and it’s indented from this query over here. And then I’m going to get rid of this semicolon and we'll put a semicolon over here. So, basically what this is saying is I want to get
3:16:17
the first name and the last name FROM the Employee table where the employee ID is IN the result of this query. So, if the employee ID got returned from this query, which gave us the ID’s of all of the employees who have sold over 30,000, then we're going to return their first name and last name. So, let’s go ahead and run this. And you'll see over here, now we're getting the names of the two employees. So, Michael Scott sold over 30,000 and Stanley Hudson also sold over 30,000.
3:16:48
So, that is actually a really cool way where we can find out that information. So, that’s how we can use a nested query, right? A lot of times you'll use this IN keyword. I’ll also show you some other examples where we use other things. But in that case, we're basically checking to see if the employee ID is IN this result. All right, so now I have another prompt here. It says Find all clients who are handled by the branch that Michael Scott manages. So, this is another interesting one.
3:17:15
It says assume you know Michael’s ID. So, we're going to assume that we know what Michael Scott’s ID is. This is another one where again, we're going to need to grab data from one table in order to inform the data from another table. So, the first thing that we want to be able to do is figure out the branch ID of the branch that Michael Scott manages, right? So, over here we have our branches and each one has a manager ID, right?
3:17:39
And so, what we need to do is able to figure out which of these branches Michael Scott manages. Then once we have that information we can figure out all of the clients that use that branch ID, right? So, over here, the manager ID will map us to Michael Scott. And the branch ID will actually map us to the Client table over here because it has the branch ID as a foreign key. So, the first thing we'll do is we'll figure out what branch Michael Scott manages. So, that should be easy enough.
3:18:07
We can just say SELECT. And actually we'll just do the branch.branch_id FROM branch WHERE. And remember, we’re going to assume that we know Michael Scott’s ID. So, I can just say branch.branch_id is equal to – and Michael Scott’s ID is 102. So, I can just say is equal to 102. And so, what this should do, is it should give us the branch ID of the branch that he manages. In this case – actually, whoops. Instead of branch_id this needs to be mgr_id. And this is going to give us 2, right?
3:18:44
Because 2 is the Scranton branch which is the branch that Michael Scott manages. So, now that we have this piece of information, all we want to do is just get all of the clients that are handled by that branch. So, we can just say SELECT. And why don’t we just get the client name? So, it’ll say client.client_name FROM client WHERE. And over here, we're basically just going to say WHERE client.branch_id is equal to. And over here we're going to set an equal to the result of this query.
3:19:23
So, we're going to set it equal to the result of getting the ID of the branch that Michael Scott manages. So, down here, we'll put this statement and you'll see, again, I’m just embedding this in here. So, what’s going to happen is when the relational database management system sees an embedded SQL statement like this, it’s going to execute this first. And then it’s going to execute the outer one. So, it starts inner and then it slowly goes outer.
3:19:47
So, we'll be able to get the branch ID where Michael Scott is the manager. And then we can use that information to find all of the clients. So, over here, I’m just going to click Run. And I’m going to go ahead and get rid of this semicolon right here. And now we can go ahead and run this. And you'll see we get all of these clients. So, we get like Dunmore Highschool, Lackawana County, Scranton White pages, and FedEx. So, those are all the clients that are managed by the Scranton branch.
3:20:11
Now, there is one more thing I want to point out which is you’ll notice down here we're setting client.branch_id equal to. We're checking to see if it’s equal to the result of this statement. But here’s the problem, is this statement isn’t necessarily guaranteed to only return one value. So, if this – so, let’s say that Michael Scott was the manager at like multiple branches, it’s possible that this would return multiple values.
3:20:35
So, what we want to do is just come down here and say, LIMIT 1. And that’ll make sure that we only get 1 of these. So, now if I click Run, you'll see it does the same thing. Although, now we're just making sure that we only have 1. So, anytime you're using something like a quality, it’s always a good idea to limit it down to 1 unless you’re looking for a situation where Michael Scott is going to be managing multiple branches. In which case, we can use IN instead.
3:21:01
All right, so that’s kind of a little dip into nested queries. Now, obviously these can get very complex. And really, you know, what’s important is that you have a solid understanding of the fundamentals. If you don’t understand the fundamentals that we've kind of talked about up to this point in this video, then using nested queries is really going to confuse the crap out of you. All nested queries are is it’s just kind of like one query informing another query,
3:21:25
maybe informing another query, right? We just use the results from one query to get results from another query, etc. And as long as you can break the nested query up into its individual parts, you should have absolutely no problem writing these. And really, the best way to get good at writing more complex queries like this is just to practice. So, the more you practice writing nested queries and using all these things in combination, the better you’re going to get at it. [On Delete]
3:21:56
In this tutorial, I’m going to talk to you guys about deleting entries in the database when they have foreign keys associated to them. So, this is actually a pretty interesting topic. And over here in our company database we have a pretty complex database schema, right? We have all sorts of foreign keys that are linking between all sorts of places. And I want to pose to you guys a scenario. So, imagine over here I have my Branch table and I have my Employee table.
3:22:24
So, imagine that I came over here in my Employee table and I deleted one of the employees, right? So, let’s say that I deleted Michael Scott. So, over here we have this employee, Michael Scott. And his branch ID is 2. So, Michael Scott has a foreign key here defined, which is branch_id, right? So, branch_id, Michael Scott’s branch ID is 2 which means that Michael Scott belongs to the Scranton branch which is right down there, right? But let’s pose something.
3:22:54
Like imagine that we were to delete Michael Scott from the database, right? Well, what’s going to happen this manager ID down here? So, if we delete Michael Scott, we delete the employee with ID 102. What’s going to happen to the manager ID? The manager ID is supposed to be linking us to an actual row in the Employee table. But if we delete Michael Scott, then all of a sudden 102, that doesn’t mean anything, right? Because Michael Scott is gone.
3:23:24
His employee ID is no longer inside of our Employee table. And this is what I’m going to talk to you guys about today, which is different things that we can do in order to handle this situation. So, specifically, I’m going to talk to you guys about two things. One is called ON DELETE SET NULL. And the other is called ON DELETE CASCADE. So, there’s really two things that we can do when this situation occurs. And so, the first thing would be ON DELETE SET NULL.
3:23:52
And ON DELETE SET NULL is basically where if we delete one of these employees, that means that the manager ID that was associated to that employee is going to get set to NULL. ON DELETE CASCADE is essentially where if we delete the employee whose ID is stored in the manager ID column, then we're just going to delete this entire row in the database. So, I’m going to go ahead and show you guys basically how this works. So, over here I actually have the code for creating this branch table.
3:24:25
And this is the code that we used in one of the previous videos when I was showing you guys how to create this database. You'll notice over here on the Branch table it says FOREIGN KEY (mgr_id) REFERENCES employee(emp_id). And over here I said ON DELETE SET NULL. Basically, what I’m saying here is that if the employee ID in the Employee table gets deleted, I want to set the manager ID equal to NULL. And so, let me show you guys how this is going to work.
3:24:56
So, over here I’m going to go ahead and delete Michael Scott from the database and we'll see what happens. And so, I’m just going to go ahead and type out DELETE FROM employee WHERE emp_id is equal to – and we're just going to put Michael Scott’s employee ID which is 102. And I’m going to go ahead and run this. And you'll see over here it says 1 rows affected. But I want to show you guys what happened inside of the branch. So, I’m just going to SELECT ALL from branch.
3:25:26
And let’s go ahead and run this. You'll see down here the manager ID is now set to NULL. And that’s because over here – because we deleted Michael Scott, right? So, we deleted the Michael Scott, the 102 entry in the employee table. And so, now the manager ID which was storing that as a foreign key is just going to be set equal to NULL. And that’s because that’s what we defined up here. So, in a situation like that, because we said ON DELETE SET NULL,
3:25:56
now that entry is just going to be equal to NULL. And the same thing actually happened inside of the Employee table. So, if I was to SELECT ALL from the Employee table and I ran this, you'll see now that a lot of these supervisor ID’s are also set equal to NULL. And if you remember back to when we created the company database, when we created the employee table, the super_id also had ON DELETE SET NULL associated to it.
3:26:26
And so, that’s why when we deleted Michael Scott, all of the employees, namely these three employees right here who had Michael Scott as their supervisor, you can see all these employees were at branch number 2. All of their super ID’s ended up getting set equal to NULL because we had ON DELETE SET NULL there. So, that is ON DELETE SET NULL. And that’s basically how that works. I want to show you guys also how ON DELETE CASCADE works. So, we have this Branch Supplier table.
3:26:51
And the Branch Supplier table also had a foreign key like this, but instead of saying ON DELETE SET NULL, we said ON DELETE CASCADE. And when we use ON DELETE CASCADE, what that means is that if the branch ID that’s stored as the foreign key in the Branch Supplier table gets deleted, then we're just going to delete the entire row in the database. So, down here in Branch Supplier, you'll see that I have all of this stuff, right? So, I have like Hammer Mill supplies paper to branch_id 2.
3:27:24
Or Uni-ball supplies writing utensils to branch_id number 2. If I was to delete Branch 2, in other words, if I was to delete the branch that had an ID of 2, then all of the rows that had branch_id 2 here would just get deleted. So, I’m going to show you guys how that works. So, over here, we're going to go ahead and do that. So, we're just going to DELETE FROM branch WHERE branch_id is equal to 2. And so, when I go ahead and delete this, what’s going to happen
3:27:54
is all of those branch supplier rows are going to get deleted that had 2 as their foreign key. So, now I’ll just SELECT ALL from branch_supplier. And when I run this query, you'll notice that there’s no longer any branch ID’s 2 in here. In other words, we got rid of all of the suppliers that supplied Branch 2 when we deleted Branch 2. And that what’s ON DELETE CASCADE is going to do for us. Instead of just setting those equal to NULL, it’s going to go ahead and just delete them entirely.
3:28:23
So, now that we kind of understand the difference between those two, I want to talk to you guys about the different situations where we might use them. And actually, the Branch Supplier table and the Branch table are actually really good examples. So, in the Branch table we used ON DELETE SET NULL. And it was okay for us to use ON DELETE SET NULL because the manager ID on the Branch table is just a foreign key. It’s not actually a primary key.
3:28:51
And so, the manager ID isn’t like absolutely essential for the Branch Table. However, if we look down here in the Branch Supplier table, you'll notice that the branch_id, in other words, the foreign key here is also part of the primary key. Which means the branch ID on the Branch Supplier table is absolutely crucial for this row in the database, right? And so, if the branch ID here, if this branch disappears,
3:29:20
we can’t set this to NULL because a primary key can’t have a NULL value, right? And so, this can’t be NULL. You have to just delete the entire thing. And so, that’s why we use ON DELETE CASCADE as opposed to ON DELETE SET NULL. And honestly, you know, it’s really up to you which one you want to use. But just know that if you have a situation like Branch Supplier, where a foreign key is also a primary key or also a component of a primary key,
3:29:47
then it always has to be ON DELETE CASCADE otherwise you're going to run into trouble. So, that’s the basics of those different ON DELETEs. So, ON DELETE SET NULL and ON DELETE CASCADE. And both of those are extremely useful when we're defining foreign key relationships between tables. [Triggers] In this tutorial I’m going to talk to you guys about using triggers in SQL and MySQL. Well, a trigger is basically a block of SQL code which we can write
3:30:18
which will define a certain action that should happen when a certain operation gets performed on the database. So, I could write a trigger which would basically tell MySQL to do something when like an entry was added into a particular table in the database. Or when something was deleted from a database table. And basically I can say like Hey, anytime, you know, a row gets deleted from this table, I want you to like insert something into something else.
3:30:46
So, triggers can be extremely powerful and extremely useful. So, I'm going to show you guys basically how they work and we'll just talk about like setting them up and everything. So, the first thing we have to do, at least to follow along with this tutorial is we're going to create a table. And you don’t have to create this table. I’m just doing this so we can illustrate what’s happening. But this is not necessary for triggers. But I’m creating a table called trigger_test.
3:31:09
And it’s just going to have one column which is just going to be a message. And I’m just going to go ahead and create this. So, we're creating this table trigger test. And now what we can do is we can start writing out some triggers. Now, when we're using MySQL – and up to this point in this course we've been using this program PopSQL which is actually an awesome program for, you know, writing out different SQL commands.
3:31:33
And it’s been really great because it’s, you know, a bit easy for us to visual stuff. But when we're going to write triggers, we're going to have to define the triggers over here in the command line. And that’s just because there’s one special thing that we have to do which is change the SQL delimiter that we're going to use. And I’ll talk to you guys about that in a second. But in order to do that, we're going to have to do it inside of the terminal.
3:31:56
So, if you're on Windows you can just go down and open up the MySQL Command Line Client. So, it’s this guy right here. That’s what I have open. It might ask you to log in. If you're on the OSX and you're using Terminal, you can just type in – if you just type in MySQL -u root -p. And then hit Enter, it should prompt you for your password and then you should be logged in. And so, that’s how you can get to this screen over here.
3:32:25
And then once we're in here we're going to want to use the database. So, I’m just going to say use giraffe. And giraffe is the name of the database that I created like in the first tutorial. So, whatever the database you created was, you can just use that. And then over here, so once we have all that set up, now we're ready to go and start creating these triggers. So, I need to actually execute the trigger code over here inside of the command line,
3:32:50
but we can actually just write it out over here inside PopSQL so it’s a little bit easier to see. I’m actually just going to show you guys some different triggers and then I’ll kind of talk to you about it. So, I’m going to go ahead and paste one over here. And this is actually a very simple trigger. So, the trigger is actually right here, what I have highlighted. And then you'll see over here, I’m saying DELIMITER. So, I'm going to talk to you guys about the trigger first
3:33:15
and then I’ll talk to you guys about what that delimiter is doing. So, we can basically create a trigger by saying CREATE and then I can say TRIGGER. We're going to give this a name. I’m just going to call it my_trigger. And I can say BEFORE INSERT ON employee, FOR EACH ROW BEGIN INSERT INTO trigger_test. So, what does all this mean? Basically, I’m defining my trigger. I’m giving it a name. And I’m saying that before something gets inserted on the Employee table,
3:33:40
so before anything, you know, any new items gets inserted on the Employee table, for each of the new items that are getting inserted, I want to insert into the trigger test table the values ‘added new employee’. So, basically what happens is when I define this trigger, that means that before anything gets inserted on the employee table now, I’m going to go ahead and preform whatever is down here. And in our case, I’m just inserting into trigger tests, the values, ‘added new employee’.
3:34:10
So, that’s basically all it is. We're basically configuring MySQL to insert a value into the Trigger Test table whenever a value gets inserted into the Employee table. And this can be really useful because it automates things, right? I can automate something that happens every time a record gets inserted into the employee table. Now, over here we have these little DELIMITERs. And this DELIMITER is actually a special keyword in MySQL. What this will do is it’ll change the MySQL delimiter.
3:34:39
So, normally, the MySQL delimiter is a semicolon, right? So, if I said like SELECT ALL from employee. I would end this off with a semicolon. That’s the delimiter, right? That delimits the different SQL commands. But when we're writing these triggers out, you'll notice that over here inside of these FOR EACH and this END I have to use this semicolon over here. And so, because I’m using the semicolon to end off this SQL command right here,
3:35:08
I can’t actually use that same delimiter in order to end off the trigger creation. So, you have to put the semicolon here in order for this to work. But if I don’t change the delimiter, then this semicolon is basically going to tell SQL that we're done creating the trigger, even though we're clearly not. And so, what I’m doing up here is I’m changing the delimiter to two dollar signs. So, basically now instead of the delimiter being a semicolon,
3:35:35
the delimiter is going to be two dollar signs. And you'll see, I create the trigger and then I'm using the two dollar signs to delineate that the trigger is done being created. And then I can just delinear back to a semicolon. Now, the reason that I have to do this over here in the terminal is because in PopSQL you can’t actually configure the delimiter. So, the delimiter is actually something that’s defined not on the like text editor level. It’s defined like over here.
3:36:01
So, basically we have to execute this over there. So, what I’m going to do now is I’m actually going to execute all of these pieces of SQL code over here. So, I’m just going to change the delimiter. So, I'm going to paste this in. I'll hit Enter. And now I’m going to paste in the actually part where I’m creating the trigger. So, over here we'll paste this. And I’m just going to hit Enter. And then finally, we're going to change the DELIMITER back.
3:36:25
So, I’m going to change this back to a semicolon. So, hopefully now this trigger is all set up inside of MySQL. So, one thing we can do to test it is just to add in another employee. So, I'm going to go ahead and add another employee into the Employees table. So, we're going to add in Oscar Martinez. And let’s go ahead and do that. And so, we added in Oscar. And now what I’m going to do is SELECT from the Trigger Test table. So, assuming our trigger got set up correctly,
3:36:55
when we inserted an employee into the Employee table, it should have also inserted something into Trigger Test that said Added New Employee. So, let’s go ahead and run this SELECT statement and we'll see what happens. So, you'll see down here we get a message that says added new employee. So, it looks like it worked, right? The trigger got set up correctly and therefore when we inserted something into the Employee table
3:37:17
we actually ended up updating the Trigger Test table with a new entry as well. And so, that is basically how we can use triggers to do something like that. So, I want to show you guys a couple other things we can do with triggers. I’ll show you guys another one right now. I’m actually going to, again, paste it and then we'll kind of talk about it. So, this one is actually very similar to the one which just made. But instead of over here, saying like added new employee.
3:37:45
Instead, I'm saying NEW.first_name. And so, what this is allowing me to do is it’s actually allowing me to access a particular attribute about the thing that we just inserted. So, again, we're inserting something on the employee table. NEW is going to refer to the row that’s getting inserted. And then I can access specific columns from that row. So, NEW.first_name will give me the first name of the employee that’s getting inserted.
3:38:11
So, now if I was to come down here and I’m actually just going to insert another employee. So, we're going to insert Kevin Malone. And let’s go ahead and do that. And actually, whoops. I have to update the trigger over here. So, once again, I’m going to do the same thing. I’m just going to paste in all of this code over here on the command line. So, we'll paste in the trigger. And actually need to change the name on this real quick. So, we'll say my_trigger1 is what we're going to call that.
3:38:38
And that’s going to go ahead. And then we'll change the delimiter back to a semicolon. All right, so now let’s go ahead and add in our Kevin Malone employee. So, I’m going to run this. So, we added Kevin. Now if we SELECT ALL from Trigger Test, you'll see down here not only did we add a new employee, it says added new employee. That was that first trigger that we set up. But we also added the employees name which was Kevin, right?
3:39:02
So, we were able to grab a specific piece of information from the new row that got inserted and that’s going to show up down there. All right, so there’s one more thing I want to show you with these triggers. And it’s actually going to be a more complex trigger. So, this is how we can use conditionals. So, I can use something like IF, ELSEIF, and ELSE. So, over here we have this trigger. So, it’s basically the same exact thing as we did before. TRIGGER my_trigger BEFORE INSERT ON employee.
3:39:32
And then for each row. This time we're using an IF statement. So, I’m saying IF NEW.sex is equal to male, THEN INSERT INTO trigger_test VALUES added male employee. ELSEIF NEW.sex is equal to F, INSERT INTO trigger_test added female. ELSE INSERT INTO trigger_test, added other employee. So, we're using IF ELSE logic. And basically, it’s just if this condition up here is true, then we do this. Otherwise, we check this condition. If that’s true, we do this. Otherwise, we do that.
3:40:02
So, if you’ve ever programmed before, then you're probably familiar with an IF statement. So, this is a very special type of trigger because we're using conditionals. So, I’m going to go ahead and we'll put this one over here on the terminal. So, change the DELIMITER. And then we're going to put this guy over here. And whoops. Again, I forgot to change the name. So, this will be called trigger2. And put this over here. And then finally, we're just going to change the DEMILITER back.
3:40:33
All right, so now lets – again, we're going to insert an employee. So, I’m going to go ahead and insert a female employee. So, why don’t we insert Pam Beesly. So, Pam Beesly is going to be a female. Which means when we insert Pam Beesly, hopefully it should say added female into the Trigger Test table. So, I’m going to run this. And we added the employee. Now, let’s SELECT ALL FROM trigger_test. And so all of these triggers are actually going to compound on each other.
3:40:59
So, we should have quite a few entries in here. So, we'll see when we added Pam, it said added new employee, Pam. Added female. So, that third trigger that we just created actually ended up working. So, you'll notice over here we've been creating triggers for INSERT. But you can also create triggers for UPDATE and you can also make one for DELETE. So, anytime they're trying to INSERT, UPDATE, or DELETE, you can create a trigger.
3:41:21
So, you can also do – in addition to BEFORE you could also do AFTER. So, in certain circumstances you won’t want to insert into trigger_test before. You’d want to INSERT AFTER. And you can go ahead and control it just like that. So, but basically, that’s all the, you know, the main stuff that we can do with triggers. These are very, very useful. And they'll allow you to do a bunch of cool stuff. We can also drop a trigger. And it would be like my_trigger. So, this will drop my_trigger.
3:41:58
And now, my_trigger is no longer going to be active. So, triggers are, like I said, very useful. And it’s a really great way to kind of control what happens when other stuff happens. And you can automate a lot of the stuff on the backend of your database. [ER Diagrams Intro] In this tutorial I’m going to talk to you guys about ER diagrams. More specifically I’m just going to give you guys an introduction to ER diagrams. And we'll talk about how ER diagrams are put together,
3:42:26
all the different symbols in the ER diagrams and what they represent. Now, when you're designing a database, one of the most important things is designing a database schema. And a database schema is basically just all the different tables and the different attributes that are going to be on those tables. So, maybe you have some requirements for the different data that needs to get stored inside of your database and the different relationships that that data is going to have.
3:42:51
Well, you can use an ER diagrams to act as a middleman between database or storage requirements and the actual database schema that’s going to get implemented in the database management system. So, an ER diagram is a great way to kind of take, you know, data storage requirements like business requirements. And sort of convert them into an actual database schema. So, we can use the ER diagram to map out the different relationships
3:43:18
and the different entities and the different attributes for those entities. And it can just be a really great way to organize our data into a database schema. So, an ER diagram is basically just a little diagram that consists of different shapes and symbols and text. And it all kind of gets combined together to end up defining, you know, a relationship model. So, without further ado, let’s get started. I’m going to show you guys all the different basic parts of an ER diagram
3:43:48
and we'll kind of construct our own ER diagram. And it’ll kind of give you guys an idea of all the different symbols and stuff that we're going to use. So, in this example I'm going to be using the example of like a school. So, let’s say that I’m working for a school and my boss comes to me and he’s like, “Hey Mike, I need you to design a database schema or I need you to design an ER diagram for our database.” So, maybe this database is going to store information about different students.
3:44:13
And then maybe information about like the classes that those students take. So, let’s start looking at the different parts of the ER diagram. So, the first thing I want to talk to you guys about are entities. And an entity is just an object that we want to model and store information about. So, for our school database we might want to store information about a particular student. So, inside of our ER diagram we can put an entity which is just going to be a square, just like this.
3:44:37
And then we're going to have the name of the entity that we're storing. So, it’s going to be student. Next we can define attributes. So, attributes are specific pieces of information about an entity. So, over here we have our student entity. And then we might want to store like the student’s name, the student’s grade number. So, like, what grade are they in. And then their GPA. So, we can store all different types of attributes.
3:44:59
And we're going to make these little ovals and we're going to connect them to the entity just like that. So, the attribute is going to have the name of the attribute inside of an oval, connected to our square entity. We can also define a primary key. A primary key is going to be an attribute that’s going to uniquely identify an entry in the database table. So, you'll see over here I actually colored the primary key different.
3:45:20
Now, generally, for an ER diagram, you’re not going to be using colors. I just did that so it’s kind of easier for us to wrap our minds around. But whenever we're defining a primary key, we're always going to underline. So, a primary key is just like a normal attribute, but we're going to underline. So, here, our primary key is a student ID. And then I just have the GPA. So, you know, obviously I could put all of those attributes here. But I’m just using two for now just to keep it simple.
3:45:42
So, we have our primary key, student ID, which is underlined, and then we have our GPA. And they're both connected to our entity. Next, we can define composite attributes. So, these would be attributes that could be broken up into sub attributes. So, for example, if we wanted to store the student’s name, but we can also store their first name and they're last name. So, name could be broken up further into first name and last name.
3:46:05
And so, in the case of a composite attribute you'll notice that we have the main attribute here. It’s connected to the entity. And then off of that main attribute we have two other attributes fname and lname for first name and last name. We can also define a multi-valued attribute. So, if there’s any attributes in your data model that could have more than one value, then you can put them in a multi-valued attribute which looks just like an attribute except we have an extra circle.
3:46:32
So, it’s just two circles. And then inside, the name of the attribute. So, clubs, for example. Like a student might be involved in a bunch of different clubs. And so, clubs would be a multi-valued attribute. In other words, it could have more than one value. Like a student is not going to have more than one GPA. They're not going to have more than one name. They're not going to have more than one student ID. But they might have more than one club that they belong to.
3:46:54
So, next step is a derived attribute. And a derived attribute is an attribute that can be derived from the other attributes that we're keeping track of. So, we're not going to actually keep track of the derived attribute. But it’s just a way that we can sort of notate attributes that could be derived from the attributes that we're storing. So, down here I have my derived attribute. And you'll notice that it’s just an oval with these dashed lines. It’s called has_honors.
3:47:18
So, has_honors is an attribute that we could derive from this GPA. So, maybe the school is going to say that anybody with a GPA of 3,500 or above is going to have honors. Well, we could derive that just from the GPA. So, we're not actually going to be keeping track of this attribute, but it’s an attribute that we can derive from the GPA that we are keeping track of. So, we can just denote it like that. And sometimes it’s useful to denote our derived attributes.
3:47:46
So, we can also have multiple entities. So, over here you'll see I have my student entity. But I can also define another entity which would be like class. And so, a class would be like a particular class that a student is taking, right? So, if I was in school, I might take like biology or chemistry, right? That would be what this class is over here. And then you'll see over here we have our primary key which is just going to be class ID. And so, when we have multiple entities,
3:48:14
we’re going to want to define relationships between those entities. So, what I can do is I can define a relationship. And a relationship is basically just this diamond over here. And the relationship would basically denote a student is going to take a class. So, a relationship is kind of like a verb, right? It’s the student is related to the class in some way, right? So, the student is going to take a class. And a class can be taken by students. So, you can read this both ways.
3:48:41
You can say the student takes a class or you can say that the class is taken by a student. And we can also define participation. So, you'll notice that the relationship I’m connecting the two entities using these lines. So, the student is connected to the relationship using a single line. And the classes connected to the relationship using a double line. So, when you're defining relationships you can define the participation of the particular entities to that relationship.
3:49:06
So, when I use a single line, this indications partial participation. What this means is that not all students need to take a class. So, when I use the single line. I’m basically saying that only some of the students have to take a class, right? Not all students necessarily have to be taking a class. When I use this double line, it indicates total participation. Which means that all of the classes need to be taken by at least a single student, right?
3:49:34
So, that means all classes must participate in this Takes relationship. So, all classes need to have students that are taking them. So, you couldn't have a class that has no students taking it. All classes have to have students that are taking it. And, you know, maybe that’s not what you’d want in your database. But in this case, that’s how we can denote something like that. So, I could use total participation to denote that all classes need to participate in this relationship.
3:50:03
In other words, all classes need to have a student taking the class. So, that’s basically how we can define relationships. And then obviously, partial participation and total participation. And so, over here, we can also define attributes about a particular relationship. So, we have our Takes relationship. And you'll notice that I’m defining at attribute about this relationship which is grade. So, a student will take a class and the student will get a particular grade for that class, right?
3:50:33
So, I might take biology and maybe I get like a B+ in biology. Well, that grade isn’t necessarily stored on the student entity and it isn’t necessarily stored on the class entity. It’s stored on the relationship, right? So, the only way I can get a grade from a class is if I take it, right? And so, that’s why the relationship attribute is stored on the relationship. And sometimes that'll come in handy. And so, we can also define relationship cardinality.
3:50:59
And relationship cardinality is the number of instances of an entity from a relationship that can be associated with the relation. Now, I understand that’s a very confusing definition. And I think relationship cardinality is something that trips a lot of people up, so I’m going to try to give you guys a good explanation of it. So, over here we have a student and a student can take a class. But we can define relationship cardinalities on that.
3:51:21
Basically, what this means is that a student can take any number of classes. So, when we say M, that refers to any number. So, a student could take basically multiple classes, right? A student could take 2, or 3, or 4 classes. And we can define the same thing for the class. So, we could say a class is taken by any number of students, right? So, a class can be taken by 5 or 10 or 30 students. That’s basically what that would define. So, this would be an NM cardinality relationship.
3:51:51
But we can also define other cardinality relationships. So, we could say like 1 to 1. So, in a 1 to 1 cardinality relationship we would say that a student can take one class and a class can be taken by one student. We can also say like 1 to N, the cardinality relationship, which would be a student could take one class and a class could be taken by many students. Or, you could reverse it and say a class can be taken by one student, but a student can take any number of classes.
3:52:23
And then again, you guys saw NM which would be a student can take any number of classes and a class can be taken by any number of students. So, it’s useful to define that relationship cardinality in an ER diagram because that’s actually going to relate to how we want to design our database schema when it’s eventually time to do that. And also, like this is something that could be defined in data modeling requirements.
3:52:48
So, if the requirements comes to you and says a student can only take one class at a time, well, that’s something that you want to be able to represent inside of the ER diagram. So, that’s kind of how we can represent relationship cardinality. And then finally, the last thing I want to show you guys are weak entity types and identifying relationships. So, actually, I think I’m in the way here. But where my head is, it just says class. So, you guys kind of saw it before.
3:53:12
So, a weak entity as entity that cannot be uniquely identified by it’s attributes alone. Basically, a weak entity is an entity that’s going to rely on or depend on another entity. So, over here I have an example of a weak entity which would be an exam. So, a class can have an exam, right? So, an exam is something – it’s sort of like an entity, right? You know, a test or whatever that someone might be taking. An exam might have an exam ID.
3:53:39
But in this case, an exam can’t exist without a class, right? In other words, for an exam to exist, it has to be associated with a class, right? An exam, you're not just going to like have an exam stored, right? We're only going to have an exam that’s going to be associated with a class. So, this is what would we call a weak entity type. It’s an entity that cannot be uniquely identified by its attributes alone. And we can also define an identifying relationship.
3:54:08
And an identifying relationship is a relationship that serves to uniquely identify the weak entity. So, an exam can be uniquely identified when it’s paired with a class. Which I realize, my head is in the way of, but you guys know it’s there. So, I can say that a class has an exam. And an exam is had by a class. The exam doesn’t exist on its own. It only exists in the context of a class. And this is kind of more of an abstract idea.
3:54:35
And actually, in the next video we're going to look more at weak entity types. But this should be at least a little bit of an example and an introduction into weak entity types and identifying the relationships which we're just notating by these double lines. So, the exam has double square and the identifying relationship has a double triangle. And also, one more thing to note. Then whenever we have a weak entity and identifying relationship,
3:54:59
the weak entity always has to have total participation in the identifying relationship. In other words, all exams must have a class, but not all classes need to have an exam. All right, so that is kind of all of the sort of basic things that you're going to encounter in an ER diagram. And really, everything that you see right here is kind of like all of the stuff that you might seen an ER diagram. And really, you can use all of these different symbols in order to represent a data model.
3:55:30
And what you'll see is we can take this ER diagram and we can actually convert into an actual database schema. And that’s why these are really useful sort of middle-man between requirements and the actual database schema. So, hopefully that makes sense. In the next video we're going to actually walk through an example of constructing our own ER diagram, so that should be kind of cool. And that should kind of give you guys more of an idea of how these work. [Designing an ER Diagram]
3:55:57
In this tutorial I’m going to walk you guys through creating an ER diagram based off of some data requirements. So, over here I have this document. It’s called Company Data Requirements. And basically, what this document does is it describes all of the different data, all the pieces of information and the relationships between those pieces of information. And this is a good example of a document that, you know,
3:56:20
you might get if you’re working for a company and they want you to design a database. Let’s say that they want you to design a database to store information about a company. Well, they might give you this document. And then your job would be to take this document and convert it into a database schema which you can then, you know, store information in and all that. So, this document will describe all the data and it will describe the relationships between the data.
3:56:44
But it will do it in, you know, in English, right? It’ll do it in a very high-level manner. You know, it’s not going to get into database specifics or anything like that. So, your job would be to take this information and then, you know, design database schema from it. And so, what you can do is you can take this. You can convert it into an ER diagram and then you can take that ER diagram and convert it into a database schema. So, I’m going to show you guys the first step of that
3:57:12
which would be to take a document like this and convert it into an ER diagram. Which in the last video, I kind of walked you guys through what an ER diagram was and all that stuff. So, let’s go ahead and take a look at this document. I’m going to read through it. And then what we're going to do is we're going to design an ER diagram based off of it. So, over here it’s just Company Data Requirements. So, we're defining the data and all that stuff in a company.
3:57:35
So, it says the company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it. The company makes its money by selling to clients. Each client has a name and a unique number to identify it. The foundation of the company is its employees. Each employee has a name, birthday, sex, salary, and a unique number. An employee can work for one branch at a time and each branch will be managed by one of the employees that work there.
3:58:01
We’ll also want to keep track of when the current manager started as manager. An employee can act as a supervisor for other employees at the branch. An employee may also act as a supervisor for employees at other branches. An employee can have at most one supervisor. A branch may handle a number of clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.
3:58:26
Employees can work with clients controlled by their branch to sell them stuff. If necessarily, multiple employees can work with the same client. We'll want to keep track of how many dollars worth of stuff each employee sells to each client they work with. Many branches will need to work with suppliers to buy inventory. For each supplier we'll keep track of their name and the type of product they're selling the branch. A single supplier may supply products to multiple branches.
3:58:49
So, this is our Company Data Requirements document. And there’s a lot here, right? It kind of took me like over a minute to go through and read all of this. And so, if you're given a document like this, how do you go about converting this into a database schema? So, the first thing we want to do is create an ER diagram. So, what I’m going to do is I’m going to walk through and show you guys how we can create an ER diagram for these data requirements, okay?
3:59:16
And then in the next video I’ll show you guys how you can convert that ER diagram into an actual database schema. So, let’s go ahead and take a look. I’m going to walk you guys through each line in that requirements document and we'll convert it into our ER diagram. So, over here it says the company is organized into branches. Each branch has a unique number and a name. So, you'll notice that I’ve mode bold branches. So, branch is going to be our entity, right?
3:59:41
We're defining an entity branch. And it’s going to have two attributes, a branch ID which is going to be our primary key, right? The branch has a unique number. So, that, to me, tells me that it’s going to be the primary key. And then obviously, the branch name. Next we have the company makes its money by selling to clients. So, right away there we have another entity. Each client has a name and a unique number to identify it.
4:00:03
So, here we have our client which has their client ID which identifies it. And then their client name which is just going to be the name. And then over here it says the foundation of the company is its employees. Each employee has a name, birthday, sex, salary and a unique number to identify it. So, over here we have our employee and we have the employee ID which is the primary key. Birthday, name, so we get first and last name. And salary and then sex.
4:00:30
And then so over here we also have a derived attribute which is going to be age. So, from the employees birth date we could derive how old they are at any given point. So, here we have our three entities that we got from this requirements document. So, now over here it says the employee can work for one branch at a time. So, over here we have a relationship, Work For, right? This is like a verb.
4:00:56
So, an employee over here can work for a branch and a branch can have an employee working for it, right? So, that’s our relationship. And you'll notice over here I defined these as total participation. So, I’m saying that all branches must have employees working for them. That’s this double line right here. And I’m also saying all employees must work for a branch. So, both of those entities have a total participation in the Works For relationship.
4:01:26
And that wasn’t rigorously defined inside of the document. But that’s just something that I kind of, you know set there as the person designing the ER diagram. And then over here we have our cardinality relationship. So, I'm saying that – basically what this says is that a branch can have any number of employees working for it. And an employee can work for one branch. So, I’m going to say that one more time. A branch can have any number of employees working for it.
4:01:53
And an employee can work for one branch. That’s what that cardinality relationship is defining right there. So, next we have another relationship. It says each branch will be managed by one of the employees that work there. We’ll also want to keep track of when the current manager started as manager. So, over here we have another relationship which is manage, right? An employee can manage a branch. So, employee can manage a branch and a branch can be managed by an employee.
4:02:22
And then you'll also notice over here that on this relationship we've defined an attribute. So, we want to keep track of when the employee started as the manager, right? So, when does the employee start as the manager and that’s what we're defining over here. So, we're defining this attribute on the actual relationship. And now let’s take a look at the participation. So, all branches must have someone managing them. So, you'll notice that we have this full participation here, right?
4:02:49
Every branch is going to participate in that Manages relationship. All branches need to have a manager. But over here on the employee it’s partial participation, right? Not all employees need to be managers of a branch. In fact, by a large majority most employees will not be the manager of a branch. And so, that’s why we define this as single participation or partial participation. So, not all employees are going to manage a branch, but all branches will be managed by employees.
4:03:16
And then over here we have our cardinality relationships. So, we're saying that an employee can manage one branch and a branch can be managed by one employee. So, that kind of makes sense. So, down here we also have another relationship. It says an employee can act as a supervisor for other employees at the branch. An employee may also act as a supervisor for employees at other branches. That employee can have at most one supervisor. So, over here we get this supervision relationship.
4:03:45
Now you'll notice that the supervision relationship is actually a relationship that an employee has to itself. So, this is a relationship between employees. So, over here we have an employee can be supervised by another employee and an employee can be the supervisor of another employee, right? So, over here, basically we're saying that an employee can be the supervisee of only one supervisor. So you can only have one supervisor. But an employee can supervise any number of employees.
4:04:16
So, one more time, I’ll just say that an employee can be supervised by one other employee, one supervisor. And a supervisor can be the supervisor of any number of employees. All right, so over here we have another relationship. It says a branch may handle a number of clients, however a single client may only be handled by one branch at a time. So, over here we have the new relationship between the branch and the client.
4:04:43
So, I’m saying that a branch can handle a client and a client can be handled by a branch, right? So, maybe a branch might have a bunch of clients that it works with, or whatever. And so, the client has a total participation in this relationship. That means that every client must be handled by a branch. But if the branch has a partial participation, which means that not all branches need to have clients, right? Maybe you’d have like a corporate branch that doesn’t work with any clients.
4:05:09
Or maybe you’d have like an RND branch that doesn’t work with clients. But you'd have other branches that do. And so, that’s why we would define that relationship. And then also we have our cardinality relationship which would be a branch can handle any number of clients, right? So, the branch can handle N clients. And a client can be handled by one branch. So, if you're a client, you can only work with one branch. But if you’re branch, you can work with multiple clients.
4:05:32
And that’s what we're defining over here. All right, and then over here we have another relationship. And I realize this is getting a little cluttered and I’m actually in the way. But it says employees can work with clients controlled by their branch to sell them stuff. If necessary, multiple employees can work with the same client. So, now we have a relationship between employees and clients. So, we have an employee works with a client. And then a client can work with an employee.
4:06:01
Now, notice the participation. So, all clients must work with an employee. But not all employees must work with clients, right? So, all clients need to interact with the branch through an employee, but not all employees need to interact with clients. You'll also see down here it says we'll want to keep track of how many dollars worth of stuff each employee sells to each client they work with. So, on this Works With relationship, we're defining this attribute, right?
4:06:30
So, the employee can sell to the client and the client can buy from the employee and that’s where we're getting this from. And finally, we'll look at the cardinality. So, a client can work with any number of employees. And an employee can work with any number of clients. And so, that’s basically the relationship that we get from this line up here. All right, so over here we have our final little section of this requirements document.
4:06:57
It says many branches will need to work with suppliers to buy inventory. For each supplier we'll keep track of their name and the type of product they're selling the branch. A single supplier may supply products to multiple branches. So, this is an example where we would need to use a weak entity and an identifying relationship. So, over here we have this weak entity branch supplier. And it has a supplier name and a supply type,
4:07:21
but the branch supplier is going to supply a specific branch. Now, we want to keep track of which branch suppliers are supplying which branches. And in order to do that, we're going to have to use this identifying relationship. So, we can say the branch supplier supplies a branch and a branch gets supplied by a supplier. And you can see that we have these cardinality ratios over here. So, this is basically our entire ER diagram, right?
4:07:48
We have from that requirements document we've been able to map out all of the different entities, all the different attributes on the entities and all the different relationships. And basically, we get this diagram. And this diagram is – it’s just linking all that information together, right? It’s visually representing all of that information in a way that is defining it. So, what we can do now is we can take this ER diagram
4:08:14
and depending on the different relationships, the different cardinality ratios, the different participations, we can actually go ahead and convert this into a database schema which I'm going to show you guys how to do in the next video. So, stick around for that and we'll go ahead and design our database based off this ER diagram. [Converting ER Diagrams to Schemas] In this tutorial I’m going to show you guys how to convert an ER diagram into a database schema.
4:08:43
So, basically we're going to take all of this information inside this ER diagram which we created in the last tutorial. And we're actually going to take this and use it to create an actually database schema. So, from this diagram right here we'll be able to, you know, create and define actually database tables that we can use in our relational database. So, let’s go ahead and get started. I’m going to show you guys basically step by step
4:09:09
how we can start converting this into database tables and database relations. So, the first step, Step 1, is the mapping of regular entity types. So, for each regular entity type, we want to create a relation which is just a table, that includes all of the simple attributes of that entity. So, here we have all of our regular entities. We have branch, client, and employee. And so, what we want to do is just create relations or, you know,
4:09:36
basically database tables for each one of those regular entities. And then the columns of those relations are going to be all of these attributes that we defined. So, from that, from everything that’s inside of the green squares we're going to get something like this. So, we have our employee relation. We have the employee ID which is the primary key. First name, last name, birthdate, sex, and salary. And we have the Branch with that information. And then we have the Client.
4:10:02
So, I just want to show you guys when we had a composite attribute, so this name attribute over here, we're just storing the sub-attribute. So, we're just storing first name and last name. All right, so here we have three relations and lets see if we can start adding to those. So, Step 2 is the mapping of weak entity types. So, over here we have one weak entity type which is inside of this green square. For weak entity type we want to create a relation or a table
4:10:28
that includes all of the simple attributes of the weak entity. And the primary key for the new relation should be the partial key of the weak entity plus the primary key of its owner. In this case, the primary key of the owner is going to be branch ID because the branch is the owner. In other words, the branch is the entity that’s participating in the identifying relationship with branch supplier. So, over here we'll see what happens. So, you'll see we get this new table, Branch Supplier.
4:10:59
And the primary key is branch_id supplier_name, and supply_type. So, for this table, we included the supplier’s name and the branch ID. Both of those come together to make our composite key. It’s a compound key. And then we have supply type and then we end up with this. So, now we have four tables, Employee, Branch, Client, and Branch Supplier. And they all have their associated attributes. So, Step 3 is the mapping of binary 1 to 1 relationship types.
4:11:28
Now, a binary relationship is a relationship that has two entities participating in it. For example, all of the actual relationships up here are binary. In other words, there’s two parties that are participating. And what we want to do is map 1 to 1 relationships. So, we only have a single 1 to 1 relationship here. It’s this manages relationship. So, a branch can be managed by one employee and an employee can manage 1 branch.
4:11:52
So, for each 1 to 1 binary relationship we want to include one side of the relationship as a foreign key in the other. And we want to favor total participation. So, in this case, we want to basically include the primary key of one of these entities as a foreign key in the other entity’s relation. And we always want to favor the total participation. So, if a particular entity has total participation in their relationship, then you want to add the foreign key onto that entity.
4:12:25
So, in this case, branch has a total participation, so we're going to add the employee’s ID as a foreign key in the branch relation. If both of them are partial participation or both of them are total participation, then you can just use your own discretion. But in this case, it’s pretty clear that we're going to use branch. So, over here on the branch relation, I added in a foreign key which is just manager ID. And this is a foreign key which points to this employee’s ID up here.
4:12:54
So, that’s how we're going to link those two together. Step 4 is the mapping of binary 1 to N relationship types. So, unlike a 1 to 1 relation, now we're looking for 1 to N. And you'll see that we have three of them here. So, branch handles a client. An employee supervises or is supervised by another employee. And a branch has employees working for it. So, basically what we want to do is we want to include the one side’s primary key as a foreign key on the inside relation or table.
4:13:25
So, basically, what this means is that – okay, so for example, in this case we have a branch and an employee. I want to include the one side’s primary key, right? In other words, I want to include the branch’s primary key because that’s on the one side as a foreign key on the employee relation. So, basically on the employee relation, now we're going to have a branch ID column that will store a foreign key to the branch. Same thing down here for a client and branch.
4:13:53
So, the branch over here is on the 1 side. And basically, what that means is that we're going to store in the Client table a foreign key to the branch. And then same goes for this supervisor relationship. So, on the Employee table we want to store a foreign key to the supervisor. And so, over here let’s take a look. So, employee we have a super_id and we have branch_id. And that’s because the branch was on the one side of the relationship
4:14:23
and the employee was on the N side of the relationship. Which means that we're going to go ahead and store the branch’s ID as a foreign key on the Employee table. And then the same goes for the supervisor ID. So, obviously, with the supervisor ID, it’s pointing to the Employee table. So, we have to store it on the Employee table. And then down here in the Client table we stored as a foreign key the branch ID.
4:14:44
And again, that’s because the branch was on the 1 side of that cardinality relationship. You see, branch is on the one side and the client was on the N side. And so, that’s why we stored branch_id as a foreign key on the Client table. All right, and then Step 5 is the mapping of binary M to N relationship types. So, in this case, we only have one instance of this. A client can work with an employee and an employee can work with a client.
4:15:12
So, what we want to do in this case is we want to create a new relation. So, we're actually going to create a new table whose primary key is a combination of both entity’s primary keys. And we’re also going to include any relationship attributes. So, over here, the client’s primary key is client_id. The employee’s primary key is emp_id. So, what we're going to do is create a new table where we have a composite key which is the employee ID and the client ID.
4:15:40
And actually, this would be what we would call a compound key because both of it’s keys are actually foreign keys. And we want to store in this relationship the attribute on the relationship, or any attributes on the relationship which in this case is just sales. So, down here we created a new table or a new relation which is Works On. And you'll notice the key is employee ID and client ID. So, both of these individual parts of the primary key are actually foreign keys themselves.
4:16:10
So, this is a special situation. And then over here we have total sales which was the attribute that we stored on the relationship like that. And so, basically that, in essence, is going to allow us to take this ER diagram and convert it into relations. Now if you have more complex relationships, like if you have nonbinary relationships then it gets a little bit more complex when we're mapping them. In this case, I’m just kind of looking at basic ER diagrams.
4:16:42
I don’t want to get too complex. So, in this case, in five steps, we're able to basically convert the ER diagram into a set of relations. But if you do have more advanced types of ER diagrams, then there are going to be more steps. But for our cases, there’s only going to be five steps that we need to basically convert this into relations. And so, now basically what we have here is we have our database tables, right? Each of these relations is itself a database table.
4:17:10
So, when I'm designing my database now, I know I have to have an Employee table with all this stuff, a Branch table with all this stuff. Client table, etc. And so, what we can do also and what you'll see a lot of times is people will draw little arrows to define the relationships. So, this can get a little bit messy which is why I kind of saved it for the end. But you can see over here, for employee, on the employee’s foreign keys I’m drawing arrows to what they relate to.
4:17:38
So, for example, super_id, I have an arrow going back to emp_id. branch_id I have a little line here going over to branch_id. Mgr_id over here, we have a line going up to emp_id, etc. So, this is basically just like mapping out all the different relationships. This, like I said, it gets a little messy. And it’s pretty difficult to read if you have more than a couple tables. But you also – you'll see people doing this a lot, so I just wanted to show you guys how that works.
4:18:06
So, now that we have our, you know, essentially our relations, our database tables, we can actually create a database. So, over here I have an example of what a database might look like from these relations. So, we have our actually database. So, up here we have all our employees. And you'll notice we have our employee ID so we can define like the supervisor of each employee. So, like Angela Martin’s supervisor is employee number 101.
4:18:30
So, Angela Martin’s supervisor is Michael Scott, right. You'll see how it easy it is now for us to define all this stuff. Angela Martin also works at branch_id number 2. So, that links over here to the branch. So, branch 2 is Scranton, etc. And then we have our Client table over here. And we have our Works With table. So, the Works With table has the employee ID and the client ID. And then we have our Branch Supplier table.
4:18:53
So, all of these got basically put into our database and then we started putting information in there. And so, really what this is, is it’s a way for us to go from just a set of requirements like we saw in the last video, to our actual finished database table, what you see here. In designing relational database schemas, and you know, the schema is just like this whole thing, right? It’s not super easy.
4:19:19
If you have a very simple database, you know, if you have a very simple set of database storage or requirements, then obviously the schema is going to be very simple and you might not need something like an ER diagram. But with something like this, an ER diagram is hugely useful. So, here’s the thing, you don’t necessarily need the ER diagram, right? You don’t need it. But it’s a really, really great way to convert requirements into an actual database schema or a set of relations.
4:19:48
And so, that kind of show you guys how you can do that. Now, here’s the thing with ER diagram. I only showed you guys one example. And I think this is actually a pretty good example because it covers all of the main use cases. But, you know, the only way that your going to get good at using the ER diagrams and building them and, you know, converting them into database schemas is just by doing it a bunch. And so, just by practicing.
4:20:11
So, obviously, I’m not going to spend, you know, dozens of videos doing dozens of these examples, but hopefully this example kind of shows you guys the basics so now you can go on and, you know, design your own ER diagrams and then convert them into database schemas following those rules. And all of the rules that I showed you guys for converting ER diagram into relations that’s going to apply to any ER diagram. So, what we talked about in this video will, you know,
4:20:35
you can basically take any ER diagram and convert into something like this.
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat