SQL Tutorial – Full Database Course for Beginners

SQL Tutorial – Full Database Course for Beginners
Spread the love

SQL Tutorial – Full Database Course for Beginners  In this course I’m going to teach you everything you  need to know to get started using SQL.  Now SQL is a language which is used to interact  with relational database management systems.  And a relational database management system   is basically just a software application which we can  use to create and manage different databases.  And so, we’re going to talk about  all of this stuff in this course.  We’re going to start off with the basics.  So, we’ll just look at what is a database.  We’ll talk about the different types of databases.  We’ll talk about what SQL is and what it actually  means and how you can use it to create databases.  And then we’re going to go ahead  and we’re going to install something   called a relational database management system.  Which like I said, is just software  that we can use to manage a database.  We’re going to install a relational database  management system called MySQL.  And MySQL is one of the most popular database  management systems for beginners.  And also, just in general.  So, MySQL is a great first system to learn.  And so, once we have that all install,  then we’ll start writing SQL.  So, we can write out little SQL code,  little queries   in order to create databases  and create database tables  and, you know, input information,  retrieve information.  And then we’re going to get into writing SQL queries.  And queries are used to query a database.  So, we’ll create a database.  We’ll populate it with information.  And I’ll show you guys how you can write these little  SQL queries to get specific pieces of information.  So, we’ll start off with the basics  and we’ll just learn all of the fundamentals.  And then I’m going to show you guys  some more advanced techniques   to getting information out of a database.  And finally, I’m going to show you guys  how you can actually design database schemas.  So, a database schema is basically just like all of the  different tables and all the different relations   that the database is going to store.  And if you don’t understand what any of that means,  don’t worry, we’re going to cover all of it.  But this course will cover basically everything  about SQL, all of the fundamentals,   all of the things that you need to get started.  And we’ll also look at database  design and schema design.  So, it’s going to be a pretty awesome course.  I’m excited to be bringing you guys this stuff.  And just know that all of the code,  all the stuff that’s in this course   is going to be available  on the Giraffe Academy website.  GiraffeAcademy.com.  And there’s also going to be some  additional things on the website   that isn’t going to be in course just because  I didn’t have time to cover all of it.  So, check out the website for more information.  But without further ado, lets get into it.  Let’s learn SQL which is one of the most popular  languages for not only jobs,   but just for developers in general.  [What is a database?]  In this video I’m going to give you guys a full  introduction into databases.  So, we’re not going to get too specific  on any one point.  Basically, I want to show you guys  just what databases are in general.  We’re going to talk about how databases are used,  why we use databases.  And the different types of databases  that you’ll find out in the world.  So, this should basically just give  you an introduction to databases in general.  If you don’t know anything about databases,  then this video will kind of walk you through   and get you up to speed  with what you need to know   so you can then go off and start learning  how to use an actual database.  So, I just have a little PowerPoint slide here  that I want to kind of walk you guys through.  And we’ll look at some different  features of databases.  So, the first question is,  “What is a database?”  You’ll often see databases  abbreviated as DB.  So DB would stand for database.  And, a database is any collection  of related information.  What I’m sharing with you guys here is the absolute  most general definition of this word.  A database really, if you want to get down to it,  is just any collection of related information.  So, that could be something like a phonebook,   a shopping lists, a to-do list,  your five best friends, Facebook’s userbase.  All of these are examples of databases.  All of these are collections of information  that store related stuff, right?  So, the phone book, right?  This stores people’s phone numbers.  Their names and their phone numbers.  It’s a collection of related information.  A to-do list – this stores a list of things  that you want to do during the day, right?  It’s related information.  Your five best friends.  That’s information.  It’s information that you’re storing somewhere.  You have – you know, if I came up to you  and asked you who your five best friends were,   I’m sure you could list them off.  That, in essence, is a database.  It’s a collection of related information.  And then Facebook’s userbase, right?  So, all of the users that Facebook  keeps track of and stores.  That’s another example of a database.  So, databases can be stored in different ways.  So, you could store a database on paper.  If I had a shopping list or a to-do list, I might  just scribble that down on a piece of paper.  You could store a database in your mind.  So, your five best friends, for example.  If I came up to you and I asked you,  “List off your five best friends.”  And you probably don’t have it  written down somewhere.  You don’t have an app on your phone  that tells you who all your friends are, right?  You just know that information in your mind naturally.  And so, that’s another way  that you can store a database.  You can just store the information in your mind.  You can store information on a computer.  And this is probably the most common use case  is people will create a database   and they’ll store all the information  on their computer.  This PowerPoint, the PowerPoint  that we’re looking at right now.  This is an example of a database.  It has related information on it.  And I’m using it to teach this lesson.  And then finally like a comment section.  So, if you go down to the comment section  of this video, that’s a database, right?  It’s a general database that’s storing  comments for the video.  So, that really, in essence, is everything you need  to know about databases to get started.  It’s a collection of related information  that can be stored in different ways.  So, now that we understand  the very general definition,   I want to kind of walk you guys through more  specifically what we can do with databases.  So, over here I have another slide.  It’s Computers + Databases equals heart.  Now, the point I’m trying to drive home  with this slide is that storing a database,   storing a collection of related information  on a computer is extremely useful.  And computers are actually great  for storing databases.  So, I have here two things.  We have over here on the left  Amazon vs a Shopping List.  So, these are two examples of situations  where we have a database.  We kind of talked about how  a shopping list is a database.  It’s a collection of related information, right?  A collection of products that you  want to buy from the store.  Amazon.com is also a database.  Amazon is storing all this product information,  all this user information.  They’re storing reviews on the products,  the prices of the products.  They’re storing all of this information.  So, let’s take a look and we’ll compare  and contrast these two databases   and we’ll see why computers  are the preferred medium for storing data.  So, Amazon.com keeps track of products, reviews,  purchase orders, credit cards, users, media, etc.  So, Amazon is keeping track of so much information.  Trillions of pieces of information  need to be stored and readily available.  Over here we have a shopping list.  A shopping list keeps track of consumer products  that need to be purchased, right?  We’re talking 10 to 20 pieces of information  need to be stored and readily available.  So, Amazon, we need to store  trillions of pieces of information.  With the shopping list,  we need to store 10 or 20.  Both of these are databases,  but the one we have trillions of stuff.  The other one we have 10 to 20 things.  And so, over here on Amazon, the information  on Amazon is extremely valuable   and it’s critical to Amazon.com’s functioning, right?  So, the information that Amazon is storing  it’s database is absolutely essential.  And another thing is that security is essential, right?  Amazon stores people’s personal information, like  social security number or credit card, address, phone.  Like that information needs to be  locked down and secure.  A shopping list on the other hand, the information  is for convenience’s sake only, right?  It’s not absolutely necessary for shopping.  Like if you didn’t have a shopping list, you could still  go to the store and, for the most part,   you’d be able to find everything that you need.  Also, with the shopping list,  security is just not important at all, right?  If you drop your shopping list on the ground –  I mean if somebody else was to pick it up   and look at it, it’s like not a big deal.  It’s a shopping list.  Finally, over here on Amazon,  the information is stored on a computer.  Whereas with the shopping list, the information  is stored maybe on a piece of paper,   or it could even just be stored  in someone’s memory, right?  So, maybe you just memorized your shopping list   and that’s where you’re storing  your shopping list database.  So, what I’m trying to show you guys is that  a database doesn’t just have to be on a computer   and it doesn’t just have to be like in your memory  or on a piece of paper, right?  Databases are in all of these different environments.  But here’s the thing, for an application like Amazon,  storing information on something like a computer   makes it really easy to store  trillions of pieces of information.  And really easy to secure the data  and make sure that security is taken care of.  And it also makes it really easy  to back up that information   and duplicate that information and store  that information on different computers.  So, the main point of this slide is that computers are  great at keeping track of large amounts of information.  And so, going forward, we’re going to be talking about  how can we create databases on the computers   because computers are so good  at storing that information.  So, let’s talk about how we can go about  creating databases on a computer.  Now, a database could be as simple  as like a text file where you store information.  Or it could be like an Excel file, right?  Microsoft Excel file.  But generally, if you’re going to be using  a database with an application   or you’re going to be using a database  to store huge amounts of information,   a lot of times what people will do is  they’ll use special software   which is designed to help you create  and maintain the database.  This is called database management systems.  So a database management system  is a special software program   that helps users create and maintain a database  on a computer.  So, it makes it really easy for us  to manage large amounts of information.  So, if you’re a company like Amazon  and you have trillions of pieces of information   that you need to keep track of, well,  the database management system   can make it pretty easy for you to store  trillions of pieces of information, right?  It’s not like all that information  is just in like some single text file.  The database management system  will make it really easy for you to store that.  Database management systems  can also handle security.  So, they can make it so only certain people with the  usernames and passwords can access the data.  It’ll also help you to backup your data and import  and export data from other sources.  So, if you have a bunch of information  and you want to back it up,   a database management system can help you do that.  Database management systems  can also interact with software applications.  So, you know, Amazon.com is a website.  And it’s interacting with the Amazon database   which is stored mostly likely  using a database management system.  So, you could write a program that could interact  with the database management system.  All right, so let’s take a look at  this quick little diagram that I have here.  So, we have Amazon over here.  And this would be like Amazon.com.  And Amazon is communicating  with a database management system.  And I don’t work for Amazon so I’m not 100% sure  exactly how they’re doing this.  But for the most part, this is the general use case.  So, we have our database management system  which is this little box here.  And the database management system is creating  and storing and keeping track of a database.  So, the database management system  isn’t the actual database.  The database management system  is the software application   that is creating, maintaining, updating, deleting  information from the actual database.  So, Amazon.com will interact with  the database management system   in order to create, read, update,  and delete information.  So, Amazon isn’t creating or reading  or updating this information directly.  Amazon is telling the database management system  to do that for it.  And by going through  the database management system,   we can be sure that all the data  is getting stored correctly   and there’s going to be no problems with the data.  So, let’s talk about C.R.U.D.  And this is an acronym that you’re going to hear a lot.  C-R-U-D.  It stands for Create, Read, Update, and Delete.  You’ll also hear people call this  Create, Retrieve, Update, and Delete.  Now, CRUD represents the four main operations  that we’re going to be doing with the database.  So, you’re going to be creating information  in the database.  So, creating new database entries.  You’re going to be reading information  from the database.  So, you know, retrieving or getting  the information that you already stored in there.  You’re going to be updating the existing information.  And you’re going to be deleting the information  that’s already in there.  And these are the core four operations that we want  the database management system to perform for us.  So, any good database management system  is going to be able to do all four of these things.  So, now that we’ve talked a little bit about  database management systems,   I want to talk to you guys about the two main types  of databases that you’ll find in computing.  So, the first is called a relational database.  You’ll hear people refer to these as SQL[Sequel]  databases or S-Q-L databases.  And then we also have what are called  non-relational databases.  And you’ll hear people refer to non-relational  databases as no-SQL or not just SQL databases.  We’re going to talk about what SQL is in a second.  But I want to show you guys these two types.  So, a relational database which we have over here  on the left, organizes data into one or more tables.  So, each table has columns and rows  and a unique key identifies each row.  Now, relational databases are by far  the most popular types of databases.  And relational databases are going to store  everything inside of these tables.  So, we’ll organize all the data that we want  to store inside of these predefined tables   and then we can just insert information into there.  A relational database is a lot like an Excel spreadsheet.  So, if you’ve ever used a spreadsheet  where we have columns and rows   and we’re storing information,  that’s essentially what a relational database is.  And then over here on the right  we have non-relational databases.  And a non-relational database is basically just any  type of database that’s not a relational database.  So, like I said, relational databases  are by far the most popular type.  And so, because they’re so popular,  any other type of database   that’s not technically relational is just referred  to as non-relational.  And non-relational databases, you know,  it’s very general.  Because any database that’s not relational  is getting put into this category.  So, non-relational database is organized database  in anything but a traditional table.  So, you’ll have things like key-value stores documents   which would be kind of like  JavaScript Object Notation.  Or like XMl.  They’ll store data in graphs.  Or even flexible tables.  So, really non-relational database  is a very general category.  It’s just anything that’s not relational.  So, let’s take a look real quick at a relational database.  Or you’ll hear people, like I said,  refer to this as a SQL database.  So, over here, we have two examples of tables.  So, this would be basically how we would store  information in a relational database.  So, over here I have a student table.  And this student table might store individual students.  So, you’ll see over here we have  an ID, a name, and a major.  So, for every single one of my students,  I have an entry just like this.  And you’ll notice over here,  I’m giving each of these entries an ID.  And that ID will uniquely identify that row in the table.  And over here, we have a users table.  So, maybe you’re creating users for your application.  So, they have a username,  a password, and then an email, right?  So, the username is something  that’s going to be unique.  It’s something that uniquely identifies  each row in the table.  And then we’re also storing like password and email.  So, we kind of talked about how  relational databases store data in tables.  And when we want to create a relational database,   we can use a  relational database management system.  And a relational database management system,  or an RDBMS   is just a database management system that helps you  create and maintain a relational database.  And some of the most popular are my MySQL,  Oracle, PostgreSQL, and MariaDB.  There’s a bunch of these that are,  you know, extremely popular.  And relational database management systems use  something called Structured Query Language, or SQL.  And SQL is a standardized language for interacting  with relational database management systems.  So, remember,  a relational database management system   is just a software application that we can use  in order to create, maintain   and do different things to our relational database.  And SQL or Structured Query Language  is the language that we can use   to interact with those relational  database management systems.  So, we can use SQL to perform CRUD operations  as well as other administrative tasks   like user management, security, backup, etc.  And we can use SQL to define tables and structures.  So, a relational database uses tables  in order to organize its information.  And we can use SQL to define those tables.  And then insert information into those tables.  And SQL is a standardized language  which means it’s pretty much used   on every relational database management system.  However, different relational database management  systems will implement SQL just a little bit differently.  So, not all SQL code that you use on one  relational database management system   will port over to another one  without slight modification.  So, now let’s talk a little bit  about non-relational databases.  And in non-relational databases  is just anything that’s not relational.  So, a non-relational database stores data  in anything but a, you know, a static table.  So, I’m showing you guys a couple different  examples of non-relational databases   and how they would store data.  So, over here we have a document database.  And this would store information inside of like  little objects or documents.  So, we’re talking about things like JSON or XML.  Basically, I think JSON is kind of a very popular format  and that’s kind of what I have up here.  So, JSON is JavaScript Object Notation.  So, there’s a lot of non-relational databases  that will store data in documents just like this.  They’ll also store data in graphs.  So, over here we have graphs.  And there’s like these little nodes.  And then there’s, you know, connecting lines between  the nodes which would represent like relationships.  And then over here we have a key value hash.  So, we would have a key  and it gets mapped to a value.  And that could really be anything.  It could be a string.  It could be JSON.  It could be a graph.  It could be anything like that.  So, these are just three common examples   of what SQL or non-relational databases  might look like or how they might store their data.  And just like with relational databases we can have  non-relational database management systems.  And these help users to create  and maintain non-relational databases.  So, some examples would be mongoDB,  DynamoDB, apache cassandra, firebase.  There’s a bunch of these popular noSQL databases   that have been sprouting up  over the last decade or so.  And these are all implementation specific.  So, unlike relational database management systems  where we have SQL   which is just a standard language  that can interact with all of them,   there isn’t a standard language for interacting with  non-relational database management system.  So, generally, the non-relational  database management system   will implement their own language for performing  CRUD and administrative operations on the database.  So, now that we’ve looked at the different types  of databases,   I want to talk to you guys about  some core concepts within databases.  So, one of the most common things  that you’re going to be doing are queries.  So, a database query is a request that’s made  to the database management system   for specific information.  So, you’ll hear the word query thrown around a lot.  And generally, a query is you’re just asking  the database management system   to give you some information.  So, as a databases structure becomes  more and more complex,   it becomes more difficult to get  the specific pieces of information that we want.  And if you have a very complex  database layout or schema,   then getting a specific piece of information  can be a little bit tricky.  And that’s why we write database queries.  So, can write a very complex database query  which in a lot of ways is like a program,   if you’ve ever used a programming language before.  And that query will then instruct the  relational database management system   to grab a specific piece or specific pieces  of information from the database.  So, a good way to think of a query,  is it’s kind of like a Google search, right?  So, if I go on Google and I would generally  type in the specific information that I want.  And then Google would give me the information  that matches that search query.  That’s a lot like a database query.  Except with a database query we’re  not going to be writing the query in English   like we would with Google.  So, I can, you know, go up to my Google search bar   and I can type something in in English or Spanish  or whatever language you speak.  But with a relational database management system  we have to write our queries using either SQL   or a specific language that’s meant for that  database management system.  So, let’s wrap this up real quick.  We kind of covered a lot of  the main points with databases.  So, a database is any collection  of related information.  It’s a very general term.  And computers are great for storing databases  because computers are really fast.  They can store lots of pieces of information.  And they can handle things like security really easily.  And database management system  make it easy to create, maintain,   and secure a database on a computer.  Database management systems will allow you  to perform the CRUD operations   and other administrative tasks on the database.  There’s two main types of databases, relational  and non-relational databases.  Relational databases use SQL and they store data  in tables with rows and columns.  Non-relational databases store data  using other data structures.  So, things that aren’t relational database tables.  And then we talked a little bit about querying.  A query is just a require that you would make  to the database management system   for a specific piece of information.  So, that is kind of the basics,  the overall high-level basics of databases.  And really, if you understand everything  in this PowerPoint,   then you have a good grasp of database fundamentals.  And as we go forward, we’re going to learn more and  more things about databases like how to create them,   how to store data, and how to organize our data  so that it’s easy to retrieve when we want it.  [Tables and Keys]  In this tutorial, we’re going to look at some  of the core concepts in relational databases.  So, we’re going to talk about how we store  information in relational databases.  Namely, in tables.  Let’s talk about the different things  about those tables.  We’re going to talk about things called keys.  We’re also going to look at rows and columns.  We’ll talk about different tables can be related  to each other   and how we can define relationships between tables.  So, this will really just give you guys a full introduction  into some of the core concepts of relational databases   which you can then apply when  creating your own relational database   using a relational database management system.  So, let’s get started.  Over here I have an example of a table.  And this is a student table.  And so, the student table defines  specific information about a student.  So, up here we have the column names.  So, the student ID, the same of the student,  and then the major.  So, I’m storing three pieces of information  about each student, right?  They have their ID, their name, and their major.  So, over here we have Jack.  His student ID is 1.  And he’s majoring in biology.  Down here, we have Claire.  Her student ID is 3 and she is majoring in English.  So, all tables in relational databases  are going to have two things.  They’re going to have columns  and they’re going to have rows.  Now, columns are these vertical sections right here.  So, a column would define a single attribute, right?  So, we have our name column.  And inside the name column  we have the names of all the students.  We have the major column.  Inside the major column we have  the majors of all the students, right?  Makes sense.  And then we have rows.  And a row is an individual entry  in the student table.  So, a row or a horizontal entry  would represent a single student, right?  So, in a single row we’re storing  the student ID, the name, and the major.  So, the columns represent a single attribute and  the row represents an entry or an actual student.  Whenever we’re creating a table  in a relational database   we always want to have one very special column  which is called the primary key.  And a primary key is basically an attribute which  uniquely defines the row in the database.  So, it’s an attribute about a specific entry  which will uniquely define it.  So, over here, we have student ID.  And notice that I colored this in red  and I also underlined it.  And this underline is basically going to specify  that this column or this attribute of the student   is going to be the primary key.  In other words, we can use this attribute  to uniquely identify a specific row.  So, over here we have Kate  and she’s a sociology major and her ID is 1.  So, we would say that Kate’s primary key is 1.  Now, let me show you guys why primary keys  can come in handy.  So, down here, inside of these gold squares  I have two entries in this database.  So, we have two students, both of which  are named Jack.  And both of which are biology majors.  Now, this isn’t that, you know, special of a case, right?  You can imagine that in a school you might have  two students with the same name   who are the same major.  But in this case, how can we differentiate  between this guy and this guy?  Well, we can use this primary key.  So, even though the name and the major  of this row in the table   are the same as the name and the major of this row,  the student ID is different.  The primary key is different.  And that’s the whole point of a primary key,   is that the primary key is always  going to be unique for each row in the table.  So, even if all of the attributes of the row  are the same, the primary key won’t be.  And therefore we can differentiate  between the two rows.  So, I can differentiate between this Jack and this Jack  by referring to their student IDs.  And so, generally, whenever we have a table  in a relational database,   you always want to define a primary key.  And a primary key can be anything.  It can be a number.  It could be a string of text.  It could really be anything that you want.  But it has to uniquely identify the specific row.  So, in other words, another row in this student table  couldn’t have a primary key of 2.  So, down here I have another example  of a database table.  This is a user table.  So, just like that student table,  except it’s storing information about users.  So, over here we have email, password,  date created and type.  So, email is going to be our primary key.  You can see I put that in red and I also underlined it.  And these emails are unique to each entry  in the table, right?  So, 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   and then you’ll just insert specific pieces  of information into that table.  And so, over here we have one more example  of a database table.  And we’re actually going to be looking  at this example as we go forward.  So, this is an example of an employee.  So, imagine that we have like a company database  and we were storing information about employees.  So, we have the employee ID which is the primary key.  We’re storing first name,  last name, birthdate, sex, and salary.  And we’re storing all of this specific information  about an employee.  Now, I want you guys to notice  the primary key over here.  So, employee ID, this is just a, you know,  some sort of random number   that we’ve assigned to each employee, right?  This employee’s ID is 100.  This employee ID is 101, etc.  And this over here, this employee ID,  this is what we would call a surrogate key.  And a surrogate key is basically a key that has no  mapping to anything in the real world, right?  It’s essentially just, you know, like in this case a  random number that we assign to an employee, right?  So this, employee Jan has an employee ID of 100.  That doesn’t necessarily mean anything.  100 is just a value that we’re using to represent  Jan inside of the database.  And so, we would refer to that as a surrogate key,  which is just a type of primary key.  Surrogate key is a key that has no mapping to  anything in the real world.  We can also have something called a natural key.  And over here you’ll see that  I have the same exact table   except instead of having employee ID,  I have employee SSN.  And SSN stands for Social Security Number.  So, Social Security Number is a number  that we use here in the United States   in order to uniquely identify each citizen.  So, in this case we’re using the Social Security Number  in order to uniquely identify each row in the table.  In other words, we’re using the Social Security Number  as the primary key of the table.  And this is an example of  what we would call a natural key.  And this is a key that has a mapping or has a purpose  in the real world, not just in the database.  So, a lot of times you’ll hear people refer to surrogate  keys or natural keys and that’s the difference.  A surrogate key is a primary key  that has no mapping to the real world.  And a natural key is a key that has a mapping to the  real world, just like Social Security Number.  So, those are kind of the two different types of  primary keys that you might see being stored.  Another thing I want to talk to you guys  about are what are called foreign keys.  And a foreign key is basically an attribute  that we can store on a database table   that will link us to another database table.  So, over here I have this same exact employee table.  And then I also have this   other attribute over here, branch_id.  And you notice that I colored this in green.  And this is what we would call a foreign key.  And a foreign key stores the primary key  of a row in another database table.  So, here we have our employee table  and I’m defining information about the employee.  But let’s say that an employee belongs  to a specific branch in our company, right?  So, a company might have different branches.  And we can store the information about what branch  the employee belongs to inside of a foreign key.  So, the foreign key over here is actually  a primary key inside of another table.  In our case, a branch table.  So, let me show you guys that.  So, down here, we have this branch table  in our database, right?  So, the branch is it’s own separate table.  And we have – the branch has a primary key  over here which is 2, 3, 1.  And the branch names are Scranton, Stamford,  and Corporate.  So this in itself is its own table, right?  It’s the branch table.  And over here we can define which branch  a specific employee belongs to,   by referring to the ID, the primary key of the branch.  So, here, Jan Levinson, her branch ID is 1, which  means she is in the corporate branch, right?  Because branch ID number 1 is corporate.  Michael Scott, his branch ID is 2,  which means he’s in the Scranton branch.  So this number is mapping this row over here  into this other table.  And that’s what a foreign key does.  A foreign key is essentially just a way that we can  define relationships between the two tables.  So, a foreign key is just a primary key of another table.  So, Andy Bernard, right?  His branch ID is 3 which means he belongs  to the Stamford branch.  And so, that’s what a foreign key does.  A foreign key allows us to link up or define  relationships between tables.  I can say that Andy belongs to the Stamford branch  and I can define that using the foreign key.  And over here you’ll see on the branch table, I also  defined another foreign key which is manager ID.  mgr_id, that would stand for manager ID.  And now this is actually going to be a foreign key  which connects branch to the employee table.  So, manager ID is going to be the ID of a particular  employee who is the manager of the branch.  So, let’s take a look at the Scranton branch.  So, the branch ID is 2.  The name of the branch is Scranton,  and the manager ID is 101.  So, let’s go check it out.  So, over here in the employee table,  employee 101 is Michael Scott.  What that means is that Michael Scott  is the manager of the Scranton branch, right?  We were able to define that relational  by using these foreign keys.  So, down here, the Stamford branch,  the manager is 102.  If we come up here, we can see  employee 102 is Josh Porter.  So, Josh Porter is the manager  of the Stamford branch.  Corporate over here, the manager ID is 108.  So, over here we didn’t actually include  108 over there.  So, obviously, this isn’t like, you know,  a huge table of employees.  But the manager for the corporate branch  would be the employee with the ID of 108,   whatever that would be inside of this table.  So, that’s what a foreign key does.  A foreign key is able to help us to define relationships  between the tables, right?  I can define which branch  a specific employee belongs to   by including the branch’s ID as a foreign key  in the employee table.  Similarly, I can define which employee  is the manager of a branch   by including that employee’s ID as a foreign key  in the branch table.  And so, that’s really why foreign keys  are going to come in handy.  And it’s also important to note that a particular table  can have more than one foreign key on it.  So, ever here I have my employee table, right?  And it’s the same exact thing as we have before.  But I also added another attribute  or other column over here which is super_id.  And that stands for supervisor ID.  And so, what this is going to do is it’s going to define  who is the supervisor of a particular employee.  And what’s cool about this foreign key  is it’s actually relating back to the same table.  So, an employee can be a supervisor  of another employee.  So, over here we have Angela Martin, right?  Her employee ID is 103.  And her supervisor ID is 101.  And that means her supervisor  is the employee with the ID 101.  So, if we come over here we can look.  Angela Martin’s supervisor is Michael Scott because  Michael Scott has an employee ID of 101.  So, Michael Scott.  Michael Scott’s super ID is 100.  That means Michael Scott’s supervisor is the  employee with ID 100, which is Jan Levison.  So, we can these this foreign key over here  on the employee table   to define relationships between employees.  So, before we define the relationship between  the employee table and the branch table,   and now we’re defining a relationship between the  employee table and the employee table, right?  And so, what’s cool about this is  then basically inside of this table,   we can tell what the different supervisor  or supervisee relationships are, right?  I can tell that Josh Porter’s supervisor is Jan Levinson.  And I can tell that Angela Martin’s supervisor  is Michael Scott.  Defining that information by using a foreign key.  All right, so let’s take a look at another table.  I actually added in another table down here.  So, over here we have employee,  just like we had before.  We have branch.  And then we also have branch supplier.  And branch supplier is another table  that we can look at.  So, a branch supplier would basically define  who the suppliers are for specific branches.  So, we have these branches.  Maybe they’re selling a certain product.  Maybe they’re selling like paper products.  And the branch suppliers would define  who are the suppliers for that branch.  And you’ll notice up here, the primary key actually  consists of two columns.  And this is what we would call  a composite key or a composite key.  And a composite key is basically a key  that needs two attributes.  So, this key is made up of two columns, right?  It’s made up of branch_id and supplier_name.  So, branch_id is actually going to refer  to the specific branch.  And supplier_name is going to refer  to the specific supplier, right?  So, we can say over here that Hammer Mill  supplies paper to branch number 2.  So Hammer Mill supplies paper to Scranton.  Uni-ball supplies writing utensils to  branch ID number 2, which is Scranton.  Patriot Paper supplies paper to  branch ID number 3 which is Stamford.  So, inside of this branch supplier table,   I’m able to define which different suppliers are  supplying what to which different branches.  Now, the reason that I need this composite key  or this composite key,   is because the supplier name doesn’t  uniquely identify each row.  And the branch_id doesn’t uniquely identify each row.  Only together can they  uniquely identify each row, right?  So, for example, supplier name, Hammer Mill.  You’ll notice that Hammer Mill shows up here  and it also shows up here.  So, Hammer Mill supplies to branch number 2.  And they also supply to branch number 3.  Uni-ball supplies to branch number 3  and Uni-ball supplies to branch number 2.  So, this column has repeated values, right?  This column has Hammer Mill repeated.  It has Uni-ball repeated.  branch_id also has those things repeated, right?  So, branch_id 2 shows up here  a bunch of times.  branch_id 3 shows up here a bunch of times, right?  And so, the branch_id column  can’t uniquely identify the table.  And the supplier name column  can’t uniquely identify the table.  Only together can they uniquely identify it, right?  So, we would say Hammer Mill supplies branch 2.  That combination only shows up once.  We wouldn’t put that combination again  because we’re already defining it.  So, Hammer Mill supplies branch 2.  Hammer Mill supplies branch 3.  These two rows are uniquely identified by the  identified by the supplier name and the branch ID.  And so, over here in these employee and the branch  table, we only find one column as the primary key.  But over here, we define two columns as  the primary key, which would be a composite key.  And that’s actually pretty common, where we’re going  to have two columns uniquely identifying each row.  All right, so down here I want  to show you guys one more example.  And this is going to show you one other way that we  can define like different relationships with these tables.  So, I actually added in two other tables.  We have a client table  and we have this Works With table.  So, let me kind of walk you guys  through this a little bit.  We still have employee. We still have branch.  But over here, we’re defining clients.  And so, a client might be like a customer, right?  So, a client would buy paper products or, you know,  whatever products from the branch and employee.  So, the client has a client ID.  They have a client name.  And then they also have a foreign key  which is a branch ID.  So, a client is going to be associated  with a specific branch, right?  So, we would say Lackawana County  is associated with branch 2.  Which means the Lackawana County  is going to buy products from the Scranton branch.  The John Daly Law, LLC is going to  buy products from branch ID number 3.  So, they’re going to buy products  from the Stamford branch, right?  So, this is a client table.  And down here, we have this Works_With table.  And what the Works_With table is doing  is it’s defining the relationships   between the employees and the clients.  Namely, how much paper  an employee sells to a specific client.  So over here we have employee ID.  We have client ID.  And we have total sales.  So, the employee ID is going to refer  to an employee in the employee table.  The client ID is going to refer  to a client in the client table.  And then we’re going to define how much in product  the client has bought from the employee.  So, employee 101, for example, that is Michael Scott.  Michael Scott has sold client ID 401.  Michael Scott has sold Lackawana County  $267,000 worth of product, right?  How about this one, 104.  So, Andy Bernard has sold client number 403 –  has sold John Daly Law $5000 in paper products.  And so, this Works_With table is able to define how  much an employee has sold to a client.  And you’ll notice over here  we have a composite key.  And the composite key  is employee ID and client ID.  And this is actually a special type of composite key   because both of these columns  are actually foreign keys.  So, employee ID is a foreign key, right?  It relates to the employee table.  Client ID is also a foreign key.  It relates to the client table.  And both of those foreign keys together  makes up the primary key of the table.  And that is actually a very useful way  to define a primary key.  Because when we do something like this,  we can define a relationship, right?  So, I can define how much product  the client has bought from the employee.  And that is something that can  because were useful to keep track of.  So, as you can see, like these tables can either  be very simple or they can be very complex.  And the more complex your database design  or your database schema is,   the more complex these tables  and these keys are going to have to be, right?  So, this employee table has a primary key  and two foreign keys.  The client table has a primary key and a foreign key.  This Works_With table has a composite key.  You know what I mean?  Both of which are foreign keys.  Like it can get very complex, but we can use  the primary keys and the foreign keys   in order to define different relationships.  But really, that is just a, you know, I think a pretty  good introduction into kind of how tables work.  We talked about rows and columns.  We talked about primary keys.  We talked about surrogate keys and natural keys.  You know, the different types of keys  that might map to the real world or not.  We talked about foreign keys.  We talked about composite keys.  And I kind of showed you guys a few different  examples of ways that things might work.  So, this would be like our company database.  And obviously, we would have  a lot more information in here.  But this is kind of a good example of  how we might define different tables   and then define the relationships between  those tables in a relational database.  [SQL Basics]  In this tutorial I’m going to walk you guys through SQL  which stands for Structured Query Language.  So, SQL is a term that you’re going to hear  thrown around all the time   when you’re talking about databases.  And SQL actually is a language.  And it’s a language that’s used for interacting with  relational database management systems.  So, SQL is – it’s similar to –  kind of like a programming language.  A lot of times you’ll hear people refer to SQL  as a programming language.  It’s not technically a programming language  in the tradition sense.  Although, you can use SQL to provide instructions  to a relational database management system.  So, I guess if you want, you could call it  a programming language.  So, like I said, SQL, it’s a language that’s used   for interacting with relational database  management systems.  A relational database management system  is a piece of software.  It’s a software application that you can use to create  and maintain a relational database.  You might use a relational database  management system   to create a database for an application  that you’re building.  And then that relational database  management system   can make sure that the database  is structured correctly   and store everything the way  that it needs to be stored.  So, in order to interact with a relational  database management system,   we need to use a special language.  So, I can’t just talk to it in English.  I can’t just be like, “Hey, relational database  management system,   get me this piece of information.”  Relational database management systems  don’t speak English.  But what they do speak is a language called SQL.  They speak Structured Query Language.  And so, if we want to ask a relational database  management system to do something for us,   for example, like store a piece of information  or create a table, update a piece of information.  We can ask the relational database management  system to do that using SQL.  So, SQL is the language that we can use to  communicate with the database management system.  And, you know, we can use SQL to get the relational  database management system to do stuff for us,   like create, retrieve, update, and delete data.  Create and manage different databases.  Design and create database tables.  So, we can define like a database schema  which would just be like the overall like table design.  And perform administrative tasks.  So, things like security.  We could do like user management, importing,  exporting, backup, all that stuff.  So, SQL can be used to tell the relational database  management system to do all of that stuff for us.  Now, here’s the thing, SQL implementation  can vary between the systems.  So, SQL, the actual language actually does  have a formal specification.  So, there is like an overall formal specification  which defines, you know, how SQL needs to be used   and all the different commands that can be used.  But the problem is that there’s a bunch of these  relational database management system.  Some of the popular ones we hear about, like  Postegres, MySQL, Oracle, Microsoft SQL Server.  Like all of these relational database  management systems   are going implement SQL just a little bit differently.  So, you could write SQL code that would work on  one relational database management system,   but then if you tried to use it on another one,  it might not work 100%.  Now, for the most part, everything  should be the same,   but you might just need to tweak  a couple little things.  So, one thing you want to keep in mind with SQL   is that, you know, SQL is used on all of the major  relationship database management systems,   but it’s used slightly differently.  So, certain instructions might work  on one database management system   and they might not work on another, or vice versa.  Or you might do things just  a little bit differently, depending.  But for the most part, they’re all implementing SQL  which means it’s all basically the same.  So, the concepts are the same  but the implementation may vary just a little bit.  So, let’s talk a little bit more about SQL.  It’s actually a hybrid language.  So, it’s basically four types of languages in one.  And you know, you don’t need to  worry too much about all this stuff,   but you are going to hear people talking  about these different aspects of SQL.  And so, I want to introduce them to you  so you understand what they are.  So, SQL, you know like I said, it’s four types of  languages all mashed into one single language.  So, SQL is a data query language, which means it can  be used to query the database for information.  So, you can write queries in SQL which tell the  relational database management system   what pieces of information  that you want to get from the database.  And so, a data query language is used to get data  that’s already stored in the database.  SQL is also a data definition language.  And what that means is you can use  SQL to define database schemas.  Now, a schema is basically just like the  overall layout of the database.  Like what tables are going to be in the database,  what columns those tables are going to have,   and the data types that those columns  are going to be able to store.  So, we can use SQL to define data in the different,  like I said, database schemas.  SQL is also a data control language which means   it’s used for controlling access  to the data in the database.  Basically, you can use it to configure  like users and permissions.  So, I could define a bunch of different users  for the database.  And I can say like, okay,  this user can write to this table.  Or this user can read information from this table.  Or this user can update  and delete information from this table.  So, you can use SQL to control the access to the data.  And SQL is also a data manipulation language.  So, it’s used for inserting, updating,  and deleting data from the database.  So, these are the four different types  of things that you can do with SQL.  And you’ll hear people using these terms kind of a lot.  And even like database management systems  might throw error messages or certain things.  And they’ll say like data query language  or data definition language.  So, it is good to just understand these  different types of things that SQL can do   and how they’re broken up into these four  like broad types of languages.  But basically, the whole point of this slide  is that SQL can do a bunch of stuff.  And it’s super powerful.  And that’s one of the reasons why it’s used in  all these relational database management systems.  So, now let’s talk about queries.  And one of the things that we’re going to be  doing a done of in this course,   and you know, if you’re working with databases,  you’re going to be doing this all the time,   which is querying.  So, query is a set of instructions given to the  relational database management system   generally written in SQL, that tell the RDBMS  what information you want it to retrieve for you.  So, if you have a database that has like millions  or billions of records in it, like there’s tons of data.  And a lot of times that data is, you know,  spread out across different tables, right?  It’s sort of hidden in this complex schema.  You know what I mean?  Like you have one piece of information  stored over here   and then another piece over here,  another piece over here in different tables.  And you want to kind of grab all that information  and organize it in a specific way.  We can use queries in order to tell  the RDBMS to do that for us.  So, the goal of writing a query is that we only want  to get the information that we need, right?  Imagine that if you, you know,  every time you wanted a piece of information   from the database you had to grab all of the  information in the database, parse through it,   and then find the information that you want.  We don’t have to do that.  Instead, you can write a query which will tell the  RDBMS exactly what piece of information you want   and it’ll be able to grab just that information  and send it back to you.  So, over here I just have a little example of a query.  So, this would be like maybe we’re trying to get –  this would be SELECT employee.name, employee.age.  So, select the employee’s name and the age  from the employee table   where the employee’s salary is greater than $30,000.  So, what this will do is it’ll give us every employee  in an employee table   who makes more than $30,000, right?  That’s kind of like a general query.  Don’t worry too much about specifically  what all this stuff means.  We’re going to get into that in this course.  But that’s kind of like what a query would look like.  We’re telling the RDBMS what information we want  and then it only gives us back that information.  Not like all the information in the database.  All right, so that is kind of an overview of SQL.  I mean obviously I didn’t get into everything.  We’re going to look at all of these, you know,  little specific things   that we can do with SQL as we go forward.  But for now, that should kind of give you  an idea of what SQL is, why it’s useful,   and you know, sort of what it’s doing for us  and how we can use it to work with our databases.  [MySQL Windows Installation]  In this tutorial I’m going to show you guys  how to download   and install two things that we’re going  to need going forward in this course.  The first thing and the most important  is called MySQL.  And MySQL is a RDBMS.  Which basically means it’s a software application   which we can use to maintain and create  and do all that sort of stuff with databases.  And so, when we have MySQL  installed on our computer,   we can actually set up what’s called  a MySQL database server.  And that would basically be a server  where MySQL is running.  And then we can write SQL in  all sorts of queries and instructions   in order to create  and do all sorts of stuff with databases.  So, the first thing we’ll do is download  and install MySQL.  And the second thing we’re going to do  is we’re going to install another program   which is called PopSQL, or PopSQL.  And it’s basically a program  that I’m going to be using in this course   in order to kind of like write all of my queries.  It’s essentially a text editor.  But it’s a text editor that can hook up  with our MySQL database.  And it’ll just be a really easy interface  and a really easy way for us to write queries   and get the information back.  So, I’m going to show you guys how to download  and install both of those things.  The first thing we want to do is open our browser  and we’re going to install MySQL.  So, you just want to come up here   and you’re just going to search in Google  for MySQL community server.  So MySQL community server is this first link  right here.  It’s just dev.MySQL.com.  And this is actually a free  and opensource piece of software.  It’s basically like the most basic version of MySQL.  And it’s the version that we’re going to be using.  So, if you’re a beginner,  this is probably what you want to go for.  It’s kind of just like a great environment  for you to learn in.  It’s pretty simple.  So, down here you have a couple different options.  You can download just like a zip  or you can download the actual installer.  So, you’ll see over here there’s  this option for the MySQL installer.  So, you’re going to go ahead and click on that.  And then that’ll bring you to this menu over here.  And you can see here’s the MSI installer.  So, we’re just going to go ahead and download that.  And it brings you to this page.  It’s asking you to like log in or sign up,  but you can just click No Thanks down here.  And it’ll go ahead and start the download.  So, this installer is really great  because it will kind of configure everything for us   and it’ll allow us to pick and choose what MySQL  products that we want to actually download.  So, let’s go ahead and accept the license  and I’m going to click Next now.  Here we have a couple options  for how we want to set this up.  You can see you can just do like Developer,  Default, Server only, Client only.  What we want to do is –  actually, we’re going to do a custom install.  Now, here’s the thing, if you want,  you can just do the developer default.  But that’s going to install a bunch of stuff  that we’re not actually going to be using.  Or at least that I’m not going to be using  in this course.  So, I’m going to ahead and click custom  because I only need to install a couple things.  So, the first thing we’ll do is come down here  to MySQL Servers   and I’m just going to click MySQL server 5,700 right here.  And I’m going to go ahead and add that over here  to the right side.  And then I’m going to come down here  to applications.  And I’m going to come down here to MySQL Shell.  And I’m going to do the same thing.  So, I’m just going to click down here to MySQL shell.  I’m doing the 64 bit version.  I’m going to add that over here.  So, these are the only two things  that I’m really going to be using for this course.  But if you want, you can download everything.  That way, you just have it.  So, I’m going to click Next.  I’m going to click Execute.  And this is going to go ahead  and install both of those things for us.  So, now you can see they’re downloading.  And this shouldn’t take too long.  They should be downloaded pretty quickly.  And then we’ll be able to move on.  All right, once those two things are finished  downloading, then we can just click next.  And we can also just click next through here.  And you’ll see it’s asking us what we want.  I’m just going to click standalone,  MySQL Server, Classic, MySQL Replication.  That’s all we need.  And you can just leave all of this stuff,  all this Type and Networking stuff as the default.  And then over here, it’s asking us to create  a root password.  So, whenever we’re using this MySQL database server,  we’re going to have to log into it using an account.  So, there’s already a default admin account  set up for us which is called Root.  But we’re going to have to give this a password.  So, I’m just going to type in password  as the password.  And again, down here.  So, make sure that you remember what this password  is because we’re actually going to need it later.  And if you want, you can add additional users.  I’m not going to do that.  So, then we can just click Next.  And you’ll see there’s an option here.  It says Start the MySQL Server at system start-up.  And if you want, MySQL to start whenever  you start-up the system   you can go ahead and keep that checked.  And then I’m just going to click Next.  And click Next.  And just click Execute.  So, this is going to go through and do a bunch of stuff  that we need to do to get this set up.  So, you’ll notice that it’s actually, you know,  not super trivial for us to set this up.  I mean obviously the installer makes it really easy.  But MySQL is a complex program.  And that’s why there’s all these little things  that we have to keep configuring.  But we’re almost done  and then we’ll be able to move on.  So, now that everything is finished configuring,  we can just click finish and I’m going to click Next.  And we can just click Finish.  All right, so once we’ve finished installing everything,   now what we need to do is we’re going to actually  create a database that we can use.  So, I’m going to come over here and we’re just going  to come over here into the Start Menu.  And you’ll see there’s this option here,  MySQL 5,700 command line client.  So, we’re going to actually go ahead and use that.  And what this will do when I open it up,  you’ll see it says Enter Password.  So, I’m just going to enter in that password  that I created.  In my case, it was just password.  And you’ll see here we’re actually logged in.  So, now we’re connected to that MySQL server  that is running on our computer.  So, from in here, what we can actually do  is create a database.  So, I’m just going to say create database.  And I’m going to give it a name.  So, I’m going to call it giraffe.  And then I’m going to type a semicolon.  So, what this will do, is it’ll create a database  for us called giraffe.  Now remember, MySQL is a database  management system   which means it’s an application or a software program  that’s designed to help us manage various databases.  So, in order to start building a database,  we need to create one.  So, I’m just going to say create database giraffe.  I’ll hit enter.  And this is going to go ahead and create  that database for us.  So, now we’ve created this database,  we’re kind of ready to go.  So, for the rest of the course, what I’m going to be  doing is teaching you guys like SQL commands.  So, we’re going to be learning  all about this SQL language   which is used to interact with MySQL  and other database management systems.  Now, if you want, you can do everything  that I’m doing for the rest of the course   here inside of this terminal.  So, inside of this little terminal window you can  basically do everything that I’m doing.  You can type in SQL like this up here.  Create database.  That’s all valid SQL.  But I’m actually going to be using a different program  which is called PopSQL.  And that program just makes it a lot easier  for us to visualize what’s going on.  It’s not like some kind of boring terminal window.  So, that’s basically a text editor  which will connect to our database server   and we’ll be able to write our SQL  from inside of there.  So, I’m going to show you guys real quick  how to download that.  You’re just going to come up here.  I’ll make a new tab.  And we’re going to come up to the search bar  and just type in P-O-P S-Q-L, PopSQL.  And you’ll see this link here, PopSQL.io.  So, we’re just going to click that.  So, here there should be an option  to download for Windows   so we can just click on that  and that’ll start downloading it for us.  So, PopSQL is cool.  Actually, it’s kind of like Google Docs  but for writing SQL queries.  Now, we’re not going to be using that functionality.  We’re just going to be using it as a text editor.  But it’s a great way for us to visualize  like the SQL queries that we’re writing   and then also like what gets returned  from those queries and stuff like that.  So, once that’s done downloading, we can just click on  it and we’re going to go ahead and run the installer.  When that’s done installing,  it should open up right away.  And you’ll see we have to sign in.  So, you can either create  an account just with Google   or you can create an account with your email,  but you have to sign in in order to use it.  So, I’m going to go ahead and sign in.  And you’ll see it says, “Welcome, Mike.”  So, we’re able to sign in.  And I’ll open the app.  So, now that we have the app open,  what it’s asking us to do is connect to a database.  So, remember, we set up our MySQL database server  and we created a database.  So, what we can do now is we can connect  to that database from inside of PopSQL.  So, over here, it’s just asking us to type in our  nickname, so I’m just going to type in Giraffe.  And the type, if you click down,  you’ll see we have MySQL right there.  Host name – so the host name  is actually going to be localhost.  And that just refers to like the local address  of the computer that you’re currently on.  Port is going to be 3306.  Assuming that you used all the defaults  when you’re installing MySQL,   then that should be the port number.  And then finally down here, the database  we want to connect to was called giraffe.  So, that’s what I named it.  If you named it different from giraffe,  but in the name there.  And then username is going to be root and the  password is going to be the password that you set up.  So, in my case it was just password.  So, once all that information is in,  we can just click connect,   and you’ll see it’s connected us to our database.  So, now we basically have a text editor  that we can use,   which is hooked up to our database so we can write  all of our SQL code, all of our SQL queries in here.  And it’ll actually get run  on our MySQL database server.  So, now everything is set up.  And we’re ready to go off  and learn some awesome SQL commands.  Now, I realize that this was kind of  like a little bit of a complex setup.  Unfortunately, that’s just unavoidable   because database management systems are,  by nature, you know, complex programs.  I mean this isn’t – they’re not designed to be like  extremely user friendly, so.  But now we’re ready to go and everything is set up.  [MySQL Mac Installation]  In this tutorial I’m going to show you guys  how to download and install two things   that we’re going to need going forward in this course.  Now, the first thing we’re going to download  and install and the most important is MySQL.  And MySQL is a relational database  management system.  And it’s actually one of the most popular relational  database management systems around.  And a lot of people use MySQL  as they’re first sort of dip   or they’re first dive into  a database management system.  So, for that reason, I’ve picked MySQL as the  relational database management system   that we’re going to use in order to learn SQL.  Now it’s important to note that the focus of this  course isn’t so much on MySQL as it is on SQL.  So, SQL is Structured Query Language.  It’s an actual language.  And MySQL is a relationship database  management system.  So, we’re going to be using MySQL  in order to kind of learn about   and use and learn all the most  common stuff with SQL.  The second thing we’re going to install in addition  to MySQL is actually going to be optional.  You don’t have to install it,  but it’s going to be a text editor   that we can use in order to  write our SQL commands.  So, it’s called PopSQL.  And it’s an awesome program which basically allows  you to write out SQL statements.  And then you can execute them  from inside the program   and it’ll show you all the results that you get back.  So, I’m going to be using PopSQL in order to kind of  visualize everything that we’re doing   and just make it a lot easier for you guys  to follow along.  So, I’m going to show you guys how to download  both those things so you can follow along with me.  First thing we’re going to do is head over to our  browser and we’re going to install MySQL.  So, you just want to type in MySQL community server.  And we’re just going to type that in to Google.  And then down here, this should pop up.  It’s just download MySQL Community Server  from dev.MySQL.com.  So, MySQL Community Server is a free and  opensource version of MySQL that anybody can use.  And if you’re just starting off and learning about  SQL and learning about databases,   then this is a great starting point.  And then all we have to do down here is scroll down   and you’re going to see that  there’s a couple different options here.  Basically, what we want to download  is the DMG Archive.  So, it’s just this first one right here.  So, I’m just going to go ahead and click Download.  And that’s going to start downloading for us.  Now, it brings you to this page.  It’s kind of asking you to log in or sign up,  but you don’t have to.  You can just click No thanks, just start my download.  And then it’ll start the download for you.  So, this is – it’s probably around like 400 megabytes.  It’s not too too big of a file,  but it’s also not like a super small file.  You’re probably going to need at least over  a gigabyte of storage on your computer   to hold everything that SQL is going to need.  All right, once that is finished downloading,  then we can just click on it   and we’re going to go ahead and install MySQL  onto the computer.  All right, so here we just have this like PKG.  So, I’m just going to double click on this.  And it’ll open up this installer.  So, we’re just going to click through.  And you can basically just leave everything  as the default.  Just keep it in the default install location  and it’s asking me for my password.  All right.  So once that is done running the installer,  this window right here is going to pop up.  It says, MySQL Installer.  It’s giving me this date.  And it says “A temporary password  is generated for root@localhost.”  Now, when we’re using MySQL   we’re actually going to have to  log into the SQL server  using a username and password.  And in order to log in, you’re actually going  to need this temporary password   that they provide for you right now.  Now, it’s root@localhost.  So, root is the actual username  that we’re going to use to login.  Localhost is going to be address of the SQL server  that’s running on our computer.  And then this is going to be  like a temporary password.  So, what you want to do is just copy this.  You want to make sure that you keep this.  So, what you could do, is you could  just click Command+Shift+4.  And you’ll see this little like thing comes up.  If you just drag around here,  that’s going to screenshot this for you.  And so, now we’ll have a picture of this  on your desktop.  Point is, is you don’t want to lose this.  So you want to make sure that you have this  temporary password accessible.  If you don’t, then it’s going to be a problem because  you’re not going to be able to get into the account.  So, make sure that you record that.  And then I’m just going to click Okay  and then we’re done here, so we can close it.  And I’ll move it to trash.  All right, so once we’ve installed MySQL,  now what I want to do is set up our MySQL server   which basically means that we’re going to be running  MySQL on our computer.  Now the way this works is MySQL is a relational  database management system.  And it’s basically going to act as like  a little database server for us.  And so, we can connect to MySQL.  We can login and then we can  manage the database from there.  So, we need to make sure that the MySQL server  is started on our computer.  So, I’m just going to come up here to the search bar.  And I’m just going to search for System Preferences.  And then down here there should be  this little icon for MySQL.  So, we’re just going to click on that.  And you’ll see it’s going to open up  this window over here.  So, it says, “MySQL Server Status.  The MySQL Database Server is currently stopped.”  So, what we want to do is start it.  And I’m going to put my password in.  And then if you want, you can have it automatically  start when you open your computer.  It says, “Automatically start MySQL Server  on Startup.”  If you want to do that, you can.  You don’t have to.  So, basically, now a MySQL database server  is running on our computer.  And what we can do now is we can connect  to that database server   and we can log in and then we can  start creating our databases   and creating all of our tables and stuff like that.  So, now that this is set up,  there’s actually a couple things that we have to do.  The first thing I want to do is update our password.  So, we were given that temporary password.  So, I’m going to open up the terminal.  And you can just go over here to the search bar  and type in terminal.  And that’ll open this guy up right here.  So, from in here, we need to start configuring  a couple different things.  First thing we need to do is get this terminal  to recognize the MySQL command.  So, if I come over here and I try to type in MySQL,  you’ll notice that it says command not found.  Basically, our Mac doesn’t know about this MySQL  command because we haven’t told it where it is.  So, there’s a couple things that we have to do  in order to get this MySQL command to work.  So, all we have to do is basically just tell  our computer where we installed MySQL.  So, what I’m going to do,  is I’m just going to type this over here   and you guys can type exactly what I’m typing.  Echo single-quote, export PATH in all caps  is equal to /usr/local/mysql/bin.  And then you’re going to type a colon$PATH  in all caps.  Just like that.  And then you’re going to end off the quotation mark.  So, you’re going to do single-quote.  And then two greater than signs.  And we’re just going to say ~  which is this little tilde character.  /.bash_profile.  So, what this is doing is it’s actually adding this line  inside of this file called .bash_profile.  Basically, what this will do is it’ll make it so we can use  that MySQL whenever we want inside the terminal.  So, I’m just going to hit Enter.  And then I’m just going to .tilde/.bash_profile.  This will kind of reload that file.  And now what you want to do is type in MySQL.  And we’ll see if this works.  So, you notice now when I type in MySQL,  instead of saying the command wasn’t found,   we got this error message that says,   “Access denied for user ‘giraffeacademy’@’localhost’  (using password:  NO).  What this means is that  the MySQL command is now working.  So, basically we’re able to give commands to MySQL.  We’re able to do stuff like that.  So, I’m just going to clear this out.  And now what we want to do is we want to  actually connect to the MySQL server   that’s running and we want to log in.  So, I can just type MySQL just like that.  And you’re going to type hyphen u.  And you’re just going to type root.  And then you’re going to type hyphen p  and your going to hit enter.  And this is going to ask you to enter a password.  And so, now is the time where we want to  enter in that temporary password.  So, in my case, I took a screenshot of it.  So, that screenshot is now going to be stored  on my desktop.  It’s over here.  And so, what I can do is I can just look at this  screenshot now and I can type in the password.  So, I’m going to go ahead and type in that password.  All right, so now that I’ve typed in that password,  I was able to log in.  So, basically now we are logged into the SQL server,   or to the MySQL server that is running  on our local computer.  So, what we can do now is we can actually  change that temporary password.  So, I’m just going to type in the following command.  ALTER. So ALTER USER.  And then I’m just going to type in ‘root’@’localhost’.  And you’ll notice that I have single quotes around  root and single quotes around local host.  So, then I’m going to type in identified by –  so I’m just going to type in a new password.  I’m just going to set it as password just  so it’s easy to remember.  And then you’re going to type a semicolon.  And now you want to hit enter.  And so, what that’s going to do,  is its going to update your user information.  So, now you can actually login using something  other than that temporary password.  So, you’ll be able to login using the password  that we set over here.  So, now what we want to do is just make sure  that everything works.  So, I’m going to type in exit.  Just like that.  And I’m going to clear this out.  And now what we want to do is try to log in again.  So, I’m going to say MySQL-the root -p.  And this time I’m going to enter in the new password  which was password.  And you’ll see that I’m able to login.  So, as long as that worked and you’re able to log in,   then you updated your password,  so now you’re going to be able to get in, no problem.  So, the next thing I want to do really quick  is we’re actually going to create a database.  So, MySQL is a database management system.  So, what it allows you to do is manage  and keep track of different databases   and the information that are in those databases.  So, what we want to do before we can actually get  started is we want to create an actual database, right?  So, what we can do is we can just type  out create database.  And then I’m just going to  name this database giraffe, just like that.  So, I’m creating a database named giraffe.  And you can name your database whatever you want.  Generally, it’s going to correspond  to the type of data that you’re storing in it.  I’m just going to be using this as a general purpose  database to go throughout this course.  But we can just say create database giraffe.  And this will create a database for us called giraffe.  And then you’re going to type a semicolon after that.  And then you’re going to click Enter.  So, now we actually have a database  called giraffe that we can use.  All right, so once you’ve done all that,  we’re actually done here inside of the command line.  Now, here’s the thing.  If you want, you can actually write out  all of your SQL commands.  You can do everything that I’m going to be doing  in this course here inside of this terminal.  So, you can interact with the MySQL database server  just from here inside the terminal.  You can write SQL.  You can give it SQL commands.  You can do all sorts of stuff like that.  In our case though, I’m actually going to be using  another separate program in order to do that.  And this environment, this terminal environment  is not a very good environment   for like visualizing things and seeing things.  So, there’s actually another program called PopSQL  which I’m going to use.  I’m going to show you guys how to download  that right now.  But just know that this part is optional.  So, if you want, you can do everything  from inside of the terminal.  So, I’m going to open up my web browser again.  We’re going to come back up here.  And I’m just going to do a Google search for PopSQL.  And this page should pop up It’s PopSQL.io.  So, basically what this is, is it’s a program that  we can use to write SQL queries and write SQL code   in order to do different things.  And PopSQL actually has some really cool features.  It’s kind of like Google Docs  but for writing SQL queries.  In our case though, we’re just going to use it  to write out our SQL.  So, over here there should be a button  to download it for MAC.  So, I’m just going to click that.  And then it’s going to go ahead  and start downloading for us.  Once that’s downloaded, then I’m just going to  open it up And we’ll be able to run the installer.  See, over here all we have to do is just drag  this over here to the applications folder   and then it’s going to be on our computer.  So, now what we want to do  is we’re going to open that up.  So, I’m just going to go over here to applications.  And we’ll scroll down to PopSQL.  And we’re going to open this program up.  So, in order to use PopSQL  you’re actually going to have sign in.  So, you can just sign in with a Google account  or with an email address.  I’m going to go ahead and sign in.  So, you can see it logged me in.  It says, “Welcome, Mike!”  So, now we’ll go ahead and return to the app.  So, now that we’re logged in, what it’s going to ask us  to do right away is connect to a database.  So, basically what this application is going to do   is it’s going to connect to that  MySQL database server that we have set up.  So, what we can do over here  is just give this a nickname.  So, I’m just going to call this Draft.  And it’s asking us for the type.  So, over here if you scroll down  you’ll see the first option is my MySQL.  So, we’ll just click MySQL.  Now, it’s asking us for a host name.  And the host name is basically the address  where this database server is located.  So, in our case it’s just at localhost.  Just like that.  So, just type in localhost.  Leave the port number the same.  3306 is going to be the default port number.  And then the database we’re going to connect to  is named giraffe.  So, if you remember, like a minute ago  we created that giraffe database.  That’s what you want to put in here.  So, if you named it something other than giraffe,  put that name in here.  And then we need a username.  So, the username is going to be root.  And then the password which they’re asking for down  here is going to be the password that you set up.  So, in my case, it was just password like that.  And then I’m just going to click Connect.  And this is going to go ahead  and connect us to the database.  So, at this point we installed MySQL.  We set up our first database.  And we connected everything with PopSQL.  And so, now we are able to start using PopSQL.  We can start learning all sorts of SQL stuff.  And it’s going to be awesome.  But keep in mind, if you don’t want to use PopSQL,  which I honestly would recommend using it.  You can do everything from inside the terminal here.  [Creating Tables]  In this tutorial I’m going to  talk to you guys about datatypes.  We’re going to talk about creating tables.  We’re also going to talk about  altering and deleting tables.  So, in the last tutorial we set everything up.  So, we got MySQL set up.  We also downloaded this program PopSQL   which is basically just a text editor that we can use  to write all of our SQL code and all that stuff.  So, what I want to do in this tutorial  is show you guys how to create tables, right?  So, we created a database.  In our case, we created a database  named giraffe in the last tutorial.  And now what I want to do is start populating  that database with different tables.  In other words, I want to start defining  our database schema.  Now whenever you’re working with  a relational database management system,   your first step is always to create tables.  So, before we can start inserting any information,  before we can start querying for information,   we actually have to create the physical tables  inside of our database.  And we can do that by defining the tables layout.  And so, what we want to do is we basically want to  use a SQL command which is called Create Table.  And then inside of that command  we can pass in all the different attributes   or all the different columns  that we want our table to have.  And so, the first thing I want to show you guys  is the different types of data   that we can store inside of our database.  So, over here I actually just have a little list.  And I’m just going to paste it in here.  So, these are all of the basic data types  that we’re going to be using in SQL.  Now, these aren’t all of the datatypes.  There are actually tons of datatypes.  And depending on the relational database  management system that you’re using,   a lot of them have different datatypes  to do different things.  I would say that these six datatypes  right here make up like the core SQL datatypes.  Like these are probably the most  common datatypes that you’re going to see.  But just keep in mind that there are a few others.  Now, we’re using the MySQL database.  And all of these datatypes are going to be able to be  used in the MySQL database management system.  And all of these are going to be used, for the most  part, in any relational database management system.  But like I said, specific database management systems  will allow you to use different datatypes   depending on how they want to implement things.  So, let’s go through these different datatypes  and I’ll kind of talk you guys through how to use them.  So, INT.  This is actually going to refer to an integer.  So, anytime you see INT just like that, I-N-T,  that basically means any whole number, right?  So, this is any sort of whole number.  But it can’t have decimal places after it.  If you want to be able to store decimals,  you can use this decimal datatype.  And the decimal datatype  will allow you to store decimals.  And you’ll see, we have  this parenthesis here after decimal.  And I have M and N in here.  Now these are actually both going to  end up being numbers.  So, M is going to be the total number of digits  that you want to store for this number.  And N is going to be the number of digits  that you want to store after the decimal point.  So, when we’re working with databases you have to  be very specific about the different information.  And specifically, the amount of digits  that you want to store for a number.  So, what we could do is I could say like 10 here  and I could put a 4 over here.  And what this means is we want to store  a decimal number with 10 total digits   and 4 of those digits coming after the decimal place.  So, you can specify, you know, depending on how  accurate you want these numbers to be stored,   you can modify those numbers.  Down here we have VARCHAR.  And this actually stands for Variable Char  or Variable Character.  This is basically a way that we can store  a string of text.  So, VARCHAR, we have these parenthesis after here.  And you can put a number in here.  So, if I put a 1 in here, then this is going to  store a string of text, length 1.  If I put (100) in here, this is going to  store a string of text with 100 characters.  So, that means that the maximum amount  of characters   that you can store inside of a VARCHAR(100)  would be 100.  Down here we have BLOB  which stands for Binary Large Object.  And this is basically just a structure  that can store large amounts of binary data.  So, if you have – a lot of people will use these  for like images or files.  Like you can store those inside of a BLOB.  And they’ll be able to be stored in the database.  We also have DATE.  So, DATE will be like a specific date and time.  And you can see we format a date like YYYY-MM-DD.  So, this would be the year, the 2-digit month,  and then the 2-digit day.  You can also have a TIMESTAMP,  which is similar to date,   but it’s generally used for recording  like when things happen.  So, you can record like when an item got inserted  into the database or something like that.  So, over here it’s just YYYY-MM-DD  and then we have the hours, minutes, and the seconds.  So, those are all, like I said, the main datatypes  that you’re going to be using.  But these aren’t all the datatypes.  So, depending on the database management system  that you’re using,   you want to check to see  what specific datatypes they offer.  But like I said, for the most part,  these should work in just about any system.  So, now I want to talk to you guys  about creating database tables.  So, what we can actually do is we can create tables   and we’re going to have use those datatypes to tell  the relational database management system   what types of data we want to store  in each column in the table.  So, in order to create a table,  we’re actually going to type out some SQL.  Now, I’m just going to type out CREATE TABLE.  And these are two SQL reserve words.  CREATE and TABLE.  And you’ll notice that I typed them  in all capital letters.  Now, this is actually a pretty common convention  when writing SQL queries.  A lot of people will write these reserve words  in SQL in all capitals.  But you don’t have to.  If I wanted, I could write create table just like that.  And you’ll see it’s getting highlighted the same way.  The reason that people write these in all caps is   because then it’s easy to distinguish the SQL  from any other text that we might be writing.  So, I would say for the most part, just stick with  making these all uppercase and you should be fine.  So, I’m going to say CREATE TABLE.  And then I want to type in the name of the table  that I want to create.  So, what we’re going to do is we’re going to  actually create a database table.  So, I actually have a database table set up over here.  You’ll see it’s a student table.  And it’s storing just a list of students  for like maybe in a college or university.  So, there’s three things that we’re storing  about this student.  The student’s ID, the student’s name,  and the student’s major.  So, we’re storing all of this information  about this student.  So, what I can do now is I can actually create  this exact table inside of my database.  So, I can say CREATE TABLE.  We’ll call it student.  And you’ll notice that I made this lowercase.  So, this isn’t an SQL reserve word,  so I’m not going to make it uppercase.  And then what I can do is I can just make an open  and closes parenthesis and a semicolon.  Now, any command that you write in SQL  is always going to end with a semicolon.  And if you’re just starting out,  you might forget to put that semicolon in there,   but you want to make sure that you always  put it in there otherwise it’s not valid SQL.  So, I’m just going to click Enter.  And I’m going to down here  in-between these parenthesis.  And what I’m going to is I’m going to start defining  the different columns   or the different attributes on this table.  So, what we can do is we can define a column name  and then a column datatype.  So, I can come over here  and I can say the first attribute.  So, in my case, the first attribute for this table  is going to be student ID.  And you’ll also notice that student ID is a primary key.  So, on this table, student ID is the primary key.  Meaning it’s the column on the table  which will uniquely identify the specific row.  So, I’m just going to call this student_id.  And now I need to give this a datatype.  So, I’m going to give this a datatype of INT because all  of these ID’s as you can see, are integers, right?  They’re just integer numbers.  Now because this is going to be the primary key  for this table, I need to denote that.  So, over here I can just say PRIMARY KEY,  just like that.  And what that’s going to do is it’s going to tell MySQL  that this is going to be the primary key for the table.  The next thing we want to store is the student’s name.  So, I’m just going to say name.  And the name is actually going to be a string.  So, this could be like Jack or Tommy or Kara, right?  It’s a name that we’re storing about this table.  So, over here I’m going to make this a VARCHAR.  And then I’m going to make an open  and closed parenthesis.  Now, remember, with the VARCHAR datatype  we have to tell MySQL   how many characters we want this to be able to store.  So, with someone’s name, what you want to do   is you basically just want to think like how many  characters do we really want to allocate   to storing someone’s name?  Because here’s the thing, if I allocated like 1000  characters for someone’s name,   will in reality, normal people don’t have a name  with 1000 characters, right?  I mean maybe your name would be 20 characters  or 30 if you’re really pushing it,   but 1000 is just totally out of the bounds of reality.  And you have to think if you’re storing  like millions of names,   potentially, you know, allocating 1000 characters  to each name when you only need 20   is going to take up a lot of extra space  in your database.  So, I think what we should do is maybe say like 20.  So, let’s say that the maximum length of someone’s  name we want to store is going to be 20 characters.  And really, you know, depending on the domain  of the application that you’re building,   that’s going to be different.  But in my case, let’s just say it’s 20.  And then finally we wanted to store  the student’s major.  So, I can say over here, major.  And this is also going to be a VARCHAR.  So, why don’t we also allocate 20 characters  to their major.  And that should be enough.  So, you’ll notice that I’m defining each of the  attributes, each of the columns on the table.  And then I’m putting a comma.  And then I’m defining the next column.  And then I’m putting a comma.  And then the final column.  So, this right here, this CREATE TABLE statement is   basically going to create  this table over here for us, right?  We have the primary key, which is an INT.  And we have a name and a major which are strings.  So, that’s going to go ahead and do that for us.  Now, from inside PopSQL, what’s really cool  is we can just click on this query right here.  Click on this SQL statement.  And I can come over here and click Run.  And what this will do is it’ll automatically  run this SQL on our MySQL server.  So, I’m going to click Run.  And you’ll see over here we get a success message  that says Rows affected:  0.  So, that actually was able to create  that new table in our database.  So, that is basically how we could create a table.  And now we have this table stored in our database.  I want to show you guys another thing we can do.  So, over here I’m defining the primary key.  So, student_id was the primary key, right?  It’s the one column that’s going to uniquely  identify each row.  And if you want, you can put primary key over here  after like student_id.  Or what you can do is you can get rid of this  and you can define this down below.  So, instead of defining the primary key up here  next to student_id,   I can come down here and I could change it.  So, I could say now PRIMARY KEY,  and then I can make an open and closed parenthesis.  And in here, I could put the name of the column  that I want to be the primary key.  So, in my case, I’ll just say student_id.  So, now what this is system is that the primary key  is going to be student_id.  So, this is a equivalent to what I was doing up here.  It’s just another way that you can do it.  And so, now we already have our table created,  so I’m not going to go ahead and create it again.  All right, so now that we have  our student table created,   let’s go ahead and make sure they created correctly.  So, what I’m going to do  is I’m going to come down here   and I’m going to type out another MySQL command.  So, I’m just going to type out DESCRIBE.  And then I’m going to type in the name of the table.  So, in our case it’s going to be student.  And you’ll notice with PopSQL, if I click over here on  this query, it gets highlighted in that blue color.  And if I click down here on this query,  it gets highlighted.  So, what you can do with PopSQL  is you can have like a query up here.  And you can run that query or you can click  on this query down here and run it.  So, I’m going to click on DESCRIBE student.  And then I’m going click Run.  So, you’ll see that it’s actually describing  the table that we just created.  So, there’s a list of all these different fields.  We have student_id, name, and major.  It’s telling us the datatypes that we’re storing.  So, like varchar(20).  And then it’s telling us a bunch of other information which we’re going to get into later.  So, what this is, it basically described  everything about this table.  So, now what I want to show you guys  how to do is delete and modify a table.  So, now that we’ve created a table  and we saw that it got created correctly,   if you wanted, you could delete the table.  So, what I could do is I can come down here  and type out DROP TABLE   and I can just type the name of the table.  So, in our case, it’s the student table, and a semicolon.  And now I’m going to click on here and I’ll click run.  And what this is going to do  is it’s going to drop that table.  So, now if I came up here  and I click on describe student,   in other words, I’m saying  I want to execute this command.  And I click run, you’ll see that it says  “NO_SUCH_TABLE”.  Table ‘giraffe.student’ doesn’t exist  because remember, we just dropped it.  So, what we can do, is we can actually create again.  So, I’m going to click on this CREATE TABLE query  and I’ll click Run.  And that will go ahead and create it.  So, now if we click DESCRIBE student, you’ll see  we get the table back because we created it again.  So, you can drop the table.  You can also modify the table.  So, let’s say that after the table was created,  you wanted to give it another column.  What I could do is I could add another column.  So, I could say ALTER TABLE.  And then say student,  because that’s the name of the table.  And then I could say ADD.  So, what this is going to do  is it’s going to add an extra column onto the table.  So, why don’t we add a column for GPA.  So we could store a student’s GPA  and that’s going to be a decimal.  So, I’m just going to click a semicolon.  And this is going to go ahead  and end off this command.  Now, with decimal, remember I showed you  that you could put numbers after here, like 1 and 2.  So, what we’re going to do is generally a GPA  would be like 3. something something.  Or 4. something something.  So, we’re going to have this be three total digits with  two of the digits occurring after the decimal point.  So, now I can go ahead and run this command  and you’ll see it says success down here.  So, if we were to describe the student table again,   if I just click on this and click Run, now you’ll see  we have that extra column in here, GPA.  And it’s a decimal (3,2).  So, that is how we can drop a table  and that’s also how we can alter a specific column.  If you wanted, you could also drop a specific column.  So, I could say ALTER TABLE student.  And over here, I can just say DROP COLUMN  and just type in the name of the column.  So, what this will do is it’ll drop that GPA column  from the table.  So, let’s go ahead and run.  We get this success message.  Now, if I describe the table and I clicked run,  you’ll see that the GPA field went away.  So, I actually dropped that field.  So, you can create tables.  We can add columns onto tables.  We can remove columns from tables.  And then we can just remove the table altogether.  So, hopefully that show you guys some of the basics  of doing those operations on tables.  Now, obviously whenever  you’re creating your database,   the first thing that you want to do  is define your database schema.  In other words, you want to create  all the different tables   and then you can start inserting data  into the tables as such.  So, in the next tutorial I’m going to talk to you guys  about inserting data into tables,   so we’ll actually insert some student information  into our student table.  [Inserting Data]  In this tutorial, I’m going to talk to you guys  about inserting data into a database tables.  So, in the last tutorial we set up our first table  which is this student table.  And you can see it up here.  And then down here we described the student table.  So, it has student_id, name, major, etc.  So, now that we have our table set up, we want to  actually start inputting information, right?  We want to start storing some different data.  So, I’m going to show you guys how to do that.  I’m going to leave this  CREATE TABLE statement up here   because we’re actually going to  come back to that in a sec.  But down here, I’m going to  show you guys how to insert values.  So, in order to insert a piece of information into  a table, we just want to type out INSERT INTO.  And now I want to type out the name of the table.  So, in our case, we have this student table.  And then I just want to type out VALUES.  So, basically, we’re saying insert  into the student table the values.  And then over here I’m going to make an open  and closed parenthesis and a semicolon.  So, now this is actually a full statement.  So, remember, we need to  end this off with a semicolon.  Inside of these parenthesis I can actually  put the information that I want to store.  So, the way that the table is set up, we have the  student_id first, then the name, then the major.  And that’s the order that we want to  put this information into the table.  So, I need to put this student_id first,  the name, and then the major.  So, over here I’m just going to type in the student ID.  So, we’re going to be actually entering in the same  information that we have over here.  So, this is kind of like our little template.  So, we have the first student, Jack,  who’s a biology major and his student ID is 1.  And you can see we just keep going from there.  So, I’m going to insert, you know,  roughly the same information.  So, the first thing we want to do is put the student ID,  so that’s going to be 1.  And you’ll notice that with an integer  I can just type out the number 1.  And then over here we’re going  to put in a VARCHAR(20).  So, basically this is going to be a string.  Anytime we’re typing out strings in SQL,  we’re going to make these double quotation marks.  And then in here we can type out the string.  So, the student’s name is going to be Jack.  And then another comma  and we’re going to put in his major which is biology.  So, we have Jack with the student ID of 1.  And his major is Biology.  So, insert into student values 1, Jack, Biology,   this is going to go ahead  and insert this value into the database table.  So, now when I click Run –   and you’ll notice all I have to do is  just click on this query and then click Run.  It says down here Success.  Rows affected 1.  So, we affected one row in the student table.  So, now what we can do is we can actually see  how this got inserted into the table.  So, what I’m going to do is actually below here,  I’m just going to type out select asterisk from student.  And this is actually a very simple SQL command.  What this is going to do is it’s going to  grab all the information from the student table.  And we’re actually going to spend  a whole tutorial just talking about using SELECT.  But for now, just know  you can type SELECT * FROM student.  And this is going to go ahead and give us  all the information from the student table.  So, I’m just going to click on this query here  and I’ll click Run.  And you’ll see down here we get this table.  So, it says student_id 1.  Name, Jack.  Major, Biology.  So, so far in the student table, we have 1 student  which is Jack and he’s a biology major.  So, why don’t we actually insert another student.  So, I’m  going to keep this same line of code except  now I’m going to insert a student with ID 2.  And this student’s name is going to be Kate.  And she is going to be a sociology major.  So, we have Kate who’s ID is 2, sociology.  So, if I click on this command here and I click Run,   this is going to go ahead and insert Kate  into the database.  So, now I’m going to click on Select All  from students, or Select * from students.  And we’ll click Run.  And now you can see we’re getting  all the information from the student table.  So, we have Jack and then we also have Kate.  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   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.  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.  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.  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.  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.  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.  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.  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.  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.  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   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.  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,   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.  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.  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.  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.  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.”  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?  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 –   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.  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.  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   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,   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?  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.  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?  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.  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,   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   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,   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.  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.  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.  And over here I can say UPDATE student  I want to set major equal to ‘Bio’, right?  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.  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,   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.  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.  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   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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,   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.  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.  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,   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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,   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.  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.  [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?  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.  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.  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.  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?  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.  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.  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.  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.  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.  So, Michael Scott’s supervisor  is going to be David Wallace, right?  Because David Wallace has an employee ID of 100.  Kelly Kapoor has a supervisor ID of 102.  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.  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.  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.  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.  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   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.  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,   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.  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.  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.  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.  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.  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.  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.  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.  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   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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?  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.  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.  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.  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]  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.  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.  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.  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.  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.  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.  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.  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.  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.  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,   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,   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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?  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.  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.  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.  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]  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.  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.  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.  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.  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.  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.  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.  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.  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.  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 %.  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.  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.  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.  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?  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.  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.  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.  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.  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   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.  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.  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   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.  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   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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?  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.  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.  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,   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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   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.  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.  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   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.  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.  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.  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.  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   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.  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.  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?  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.  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?  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.  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.  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.  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.  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.  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,   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]  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.  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.  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.  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.  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.  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.  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.  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,   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.  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.  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.  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   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.  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.  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,   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,   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   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.  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.  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.  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.  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.  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,   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   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,   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’.  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.  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,   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,   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.  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.  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,   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   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.  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.  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.  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?  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.  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.  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.  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.  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.  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.  So, I can say like over here in the terminal  I can just say DROP TRIGGER.  And it would be like my_trigger.  So, this will drop my_trigger.  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,   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.  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   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   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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,   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.  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.  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?  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.  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?  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.  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.  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.  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.  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.  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.  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.  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.  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.  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,   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.  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]  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,   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.  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   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.  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.  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.  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.  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?  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?  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.  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.  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.  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.  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.  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.  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?  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.  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.  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.  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.  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.  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.  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.  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?  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.  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,   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?  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   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.  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   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,   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.  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   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.  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.  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.  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.  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.  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.  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.  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   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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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.  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,   you can basically take any ER diagram  and convert into something like this.  

admin

Leave a Reply

Your email address will not be published. Required fields are marked *