All language subtitles for English_en_0_SQL Tutorial - Full Database Course for Beginners(720P_HD)

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.