SQL Tutorial - Full Database Course for Beginners

0:00 SQL Tutorial- Full Database Course for Beginners In this course I’m going

0:02 to teach you everything you need to know to get started using SQL.

0:04 Now SQL is a language which is

0:07 used to interact with relational database management systems.

0:10 And a relational database management system is basically just a software

0:15 application which we can use to create and manage different databases.

0:19 And so, we're going to talk about all of this stuff in this course.

0:22 We’re going to start off with the basics.

0:24 So, we'll just look at what is a database.

0:25 We’ll talk about the different types of databases.

0:27 We'll talk about what SQL is and what it actually

0:30 means and how you can use it to create databases.

0:34 And then we're going to go ahead and we're

0:36 going to install something called a relational database management system.

0:40 Which like I said, is just software that we can use to manage a database.

0:44 We're going to install a relational database management system called MySQL.

0:48 And MySQL is one of the most popular database management systems for beginners.

0:53 And also, just in general.

0:55 So, MySQL is a great first system to learn.

0:58 And so, once we have that all install, then we'll start writing SQL.

1:02 So, we can write out little SQL code,

1:04 little queries in order to create databases

1:07 and create database tables and, you know,

1:11 input information, retrieve information.

1:13 And then we're going to get into writing SQL queries.

1:15 And queries are used to query a database.

1:18 So, we'll create a database.

1:20 We’ll populate it with information.

1:21 And I’ll show you guys how you can write

1:23 these little SQL queries to get specific pieces of information.

1:26 So, we'll start off with the basics

1:28 and we'll just learn all of the fundamentals.

1:30 And then I’m going to show you guys some

1:32 more advanced techniques to getting information out of a database.

1:36 And finally, I’m going to show you

1:38 guys how you can actually design database schemas.

1:41 So, a database schema is basically just like all of the different

1:44 tables and all the different relations that the database is going to store.

1:48 And if you don’t understand what any of that means,

1:50 don’t worry, we're going to cover all of it.

1:52 But this course will cover basically everything about SQL,

1:56 all of the fundamentals, all of the things that you need to get started.

2:00 And we'll also look at database design and schema design.

2:04 So, it’s going to be a pretty awesome course.

2:06 I’m excited to be bringing you guys this stuff.

2:09 And just know that all of the code, all the stuff that’s in this course is

2:12 going to be available on the Giraffe Academy website.

2:15 GiraffeAcademy.com.

2:16 And there’s also going to be some

2:18 additional things on the website that isn’t going

2:20 to be in course just because I didn’t have time to cover all of it.

2:23 So, check out the website for more information.

2:26 But without further ado, lets get into it.

2:28 Let’s learn SQL which is one of the most popular languages for not only jobs,

2:34 but just for developers in general.

2:36 [What is a database?] In this video I’m going

2:41 to give you guys a full introduction into databases.

2:43 So, we're not going to get too specific on any one point.

2:46 Basically, I want to show you guys just what databases are in general.

2:50 We're going to talk about how databases are used, why we use databases.

2:55 And the different types of databases that you’ll find out in the world.

2:59 So, this should basically just give you an introduction to databases in general.

3:03 If you don’t know anything about databases,

3:05 then this video will kind of walk you through

3:07 and get you up to speed with what you need to know so you can then go off

3:11 and start learning how to use an actual database.

3:15 So, I just have a little PowerPoint slide here

3:17 that I want to kind of walk you guys through.

3:19 And we'll look at some different features of databases.

3:21 So, the first question is,

3:23 “What is a database?” You’ll often see databases abbreviated as DB.

3:28 So DB would stand for database.

3:29 And, a database is any collection of related information.

3:34 What I’m sharing with you guys here is

3:37 the absolute most general definition of this word.

3:39 A database really, if you want to get down to it,

3:42 is just any collection of related information.

3:44 So, that could be something like a phonebook, a shopping lists,

3:48 a to-do list, your five best friends, Facebook’s userbase.

3:52 All of these are examples of databases.

3:54 All of these are collections of information that store related stuff, right?

4:00 So, the phone book, right?

4:02 This stores people’s phone numbers.

4:04 Their names and their phone numbers.

4:06 It’s a collection of related information.

4:08 A to-do list– this stores a list of things

4:11 that you want to do during the day, right?

4:14 It’s related information.

4:15 Your five best friends.

4:16 That’s information.

4:18 It’s information that you’re storing somewhere.

4:20 You have– you know, if I came up to you

4:22 and asked you who your five best friends were, I’m sure you could list them off.

4:25 That, in essence, is a database.

4:26 It’s a collection of related information.

4:28 And then Facebook’s userbase, right?

4:31 So, all of the users that Facebook keeps track of and stores.

4:34 That’s another example of a database.

4:36 So, databases can be stored in different ways.

4:40 So, you could store a database on paper.

4:42 If I had a shopping list or a to-do list,

4:44 I might just scribble that down on a piece of paper.

4:47 You could store a database in your mind.

4:49 So, your five best friends, for example.

4:52 If I came up to you and I asked you, “List off your five best friends.” And you

4:55 probably don’t have it written down somewhere.

4:57 You don’t have an app on your phone

4:59 that tells you who all your friends are, right?

5:01 You just know that information in your mind naturally.

5:03 And so, that’s another way that you can store a database.

5:06 You can just store the information in your mind.

5:08 You can store information on a computer.

5:10 And this is probably the most common use case is people will

5:14 create a database and they’ll store all the information on their computer.

5:17 This PowerPoint, the PowerPoint that we're looking at right now.

5:20 This is an example of a database.

5:22 It has related information on it.

5:24 And I’m using it to teach this lesson.

5:27 And then finally like a comment section.

5:29 So, if you go down to the comment section of this video,

5:32 that’s a database, right?

5:33 It’s a general database that’s storing comments for the video.

5:37 So, that really, in essence,

5:39 is everything you need to know about databases to get started.

5:43 It’s a collection of related information that can be stored in different ways.

5:47 So, now that we understand the very general definition,

5:50 I want to kind of walk you guys

5:52 through more specifically what we can do with databases.

5:54 So, over here I have another slide.

5:56 It’s Computers+ Databases equals heart.

5:59 Now, the point I’m trying to drive home

6:01 with this slide is that storing a database,

6:03 storing a collection of related information on a computer is extremely useful.

6:09 And computers are actually great for storing databases.

6:12 So, I have here two things.

6:14 We have over here on the left Amazon vs a Shopping List.

6:18 So, these are two examples of situations where we have a database.

6:21 We kind of talked about how a shopping list is a database.

6:24 It’s a collection of related information, right?

6:27 A collection of products that you want to buy from the store.

6:29 Amazon.com is also a database.

6:32 Amazon is storing all this product information, all this user information.

6:35 They’re storing reviews on the products, the prices of the products.

6:38 They’re storing all of this information.

6:40 So, let’s take a look and we'll compare and contrast these two

6:45 databases and we'll see why computers are the preferred medium for storing data.

6:49 So, Amazon.com keeps track of products, reviews,

6:52 purchase orders, credit cards, users, media, etc.

6:55 So, Amazon is keeping track of so much information.

6:59 Trillions of pieces of information need to be stored and readily available.

7:04 Over here we have a shopping list.

7:05 A shopping list keeps track of consumer

7:07 products that need to be purchased, right?

7:09 We're talking 10 to 20 pieces of information

7:12 need to be stored and readily available.

7:14 So, Amazon, we need to store trillions of pieces of information.

7:18 With the shopping list, we need to store 10 or 20.

7:21 Both of these are databases, but the one we have trillions of stuff.

7:25 The other one we have 10 to 20 things.

7:28 And so, over here on Amazon, the information on Amazon is extremely valuable

7:33 and it’s critical to Amazon.com’s functioning, right?

7:36 So, the information that Amazon is

7:38 storing it’s database is absolutely essential.

7:40 And another thing is that security is essential, right?

7:44 Amazon stores people’s personal information,

7:46 like social security number or credit card, address, phone.

7:49 Like that information needs to be locked down and secure.

7:53 A shopping list on the other hand,

7:55 the information is for convenience’s sake only, right?

7:58 It’s not absolutely necessary for shopping.

8:00 Like if you didn’t have a shopping list,

8:02 you could still go to the store and, for the most part,

8:05 you’d be able to find everything that you need.

8:07 Also, with the shopping list, security is just not important at all, right?

8:11 If you drop your shopping list on the ground– I mean

8:14 if somebody else was to pick it up and look at it, it’s like not a big deal.

8:17 It’s a shopping list.

8:17 Finally, over here on Amazon, the information is stored on a computer.

8:23 Whereas with the shopping list,

8:25 the information is stored maybe on a piece of paper,

8:28 or it could even just be stored in someone’s memory, right?

8:31 So, maybe you just memorized your shopping list

8:33 and that’s where you’re storing your shopping list database.

8:36 So, what I’m trying to show you guys is

8:38 that a database doesn’t just have to be on a computer

8:41 and it doesn’t just have to be like

8:42 in your memory or on a piece of paper, right?

8:44 Databases are in all of these different environments.

8:48 But here’s the thing, for an application like Amazon,

8:52 storing information on something like a computer makes it

8:55 really easy to store trillions of pieces of information.

8:58 And really easy to secure the data and make sure that security is taken care of.

9:04 And it also makes it really easy to back up that information

9:06 and duplicate that information and store

9:09 that information on different computers.

9:11 So, the main point of this slide is that computers

9:15 are great at keeping track of large amounts of information.

9:18 And so, going forward,

9:19 we're going to be talking about how can we create databases

9:23 on the computers because computers are so good at storing that information.

9:28 So, let’s talk about how we can go about creating databases on a computer.

9:34 Now, a database could be as simple

9:36 as like a text file where you store information.

9:40 Or it could be like an Excel file, right?

9:42 Microsoft Excel file.

9:44 But generally, if you’re going to be using a database with an application

9:48 or you’re going to be using a database to store huge amounts of information,

9:52 a lot of times what people will do is they’ll use special

9:55 software which is designed to help you create and maintain the database.

9:59 This is called database management systems.

10:02 So a database management system is a special software program

10:06 that helps users create and maintain a database on a computer.

10:11 So, it makes it really easy for us to manage large amounts of information.

10:15 So, if you’re a company like Amazon and you have trillions

10:18 of pieces of information that you need to keep track of, well,

10:21 the database management system can make it pretty easy

10:23 for you to store trillions of pieces of information, right?

10:25 It’s not like all that information is just in like some single text file.

10:29 The database management system will make it really easy for you to store that.

10:33 Database management systems can also handle security.

10:36 So, they can make it so only certain people

10:38 with the usernames and passwords can access the data.

10:41 It’ll also help you to backup your data

10:44 and import and export data from other sources.

10:47 So, if you have a bunch of information and you want to back it up,

10:50 a database management system can help you do that.

10:53 Database management systems can also interact with software applications.

10:57 So, you know, Amazon.com is a website.

11:00 And it’s interacting with the Amazon database which

11:03 is stored mostly likely using a database management system.

11:07 So, you could write a program

11:09 that could interact with the database management system.

11:11 All right, so let’s take a look at this quick little diagram that I have here.

11:15 So, we have Amazon over here.

11:17 And this would be like Amazon.com.

11:19 And Amazon is communicating with a database management system.

11:22 And I don’t work for Amazon so I’m not 100% sure exactly how they're doing this.

11:26 But for the most part, this is the general use case.

11:29 So, we have our database management system which is this little box here.

11:33 And the database management system is creating

11:36 and storing and keeping track of a database.

11:39 So, the database management system isn’t the actual database.

11:42 The database management system is the software application that is creating,

11:47 maintaining, updating, deleting information from the actual database.

11:52 So, Amazon.com will interact with the database

11:55 management system in order to create, read, update, and delete information.

12:00 So, Amazon isn’t creating or reading or updating this information directly.

12:05 Amazon is telling the database management system to do that for it.

12:10 And by going through the database management system,

12:13 we can be sure that all the data is getting stored

12:16 correctly and there’s going to be no problems with the data.

12:20 So, let’s talk about C.R.U.D.

12:22 And this is an acronym that you’re going to hear a lot.

12:24 C-R-U-D.

12:26 It stands for Create, Read, Update, and Delete.

12:29 You’ll also hear people call this Create, Retrieve, Update, and Delete.

12:33 Now, CRUD represents the four main operations

12:36 that we're going to be doing with the database.

12:39 So, you’re going to be creating information in the database.

12:42 So, creating new database entries.

12:44 You’re going to be reading information from the database.

12:46 So, you know, retrieving or getting

12:48 the information that you already stored in there.

12:50 You’re going to be updating the existing information.

12:53 And you're going to be deleting the information that’s already in there.

12:56 And these are the core four operations that we

12:59 want the database management system to perform for us.

13:02 So, any good database management system is going

13:04 to be able to do all four of these things.

13:07 So, now that we've talked a little bit about database management systems,

13:11 I want to talk to you guys about the two

13:14 main types of databases that you'll find in computing.

13:17 So, the first is called a relational database.

13:20 You’ll hear people refer to these as SQL[Sequel] databases or S-Q-L databases.

13:26 And then we also have what are called non-relational databases.

13:29 And you’ll hear people refer to non-relational

13:32 databases as no-SQL or not just SQL databases.

13:36 We're going to talk about what SQL is in a second.

13:40 But I want to show you guys these two types.

13:42 So, a relational database which we have over here on the left,

13:46 organizes data into one or more tables.

13:48 So, each table has columns and rows and a unique key identifies each row.

13:54 Now, relational databases are by far the most popular types of databases.

13:59 And relational databases are going to store everything inside of these tables.

14:03 So, we'll organize all the data that we want to store inside

14:06 of these predefined tables and then we can just insert information into there.

14:10 A relational database is a lot like an Excel spreadsheet.

14:13 So, if you’ve ever used a spreadsheet where

14:15 we have columns and rows and we're storing information,

14:18 that’s essentially what a relational database is.

14:20 And then over here on the right we have non-relational databases.

14:23 And a non-relational database is basically just any

14:26 type of database that’s not a relational database.

14:30 So, like I said, relational databases are by far the most popular type.

14:34 And so, because they’re so popular, any other type of database that’s not

14:39 technically relational is just referred to as non-relational.

14:43 And non-relational databases, you know, it’s very general.

14:45 Because any database that’s not relational is getting put into this category.

14:51 So, non-relational database is organized database

14:53 in anything but a traditional table.

14:56 So, you’ll have things like key-value stores documents

14:59 which would be kind of like JavaScript Object Notation.

15:02 Or like XMl.

15:03 They’ll store data in graphs.

15:05 Or even flexible tables.

15:07 So, really non-relational database is a very general category.

15:11 It’s just anything that’s not relational.

15:13 So, let’s take a look real quick at a relational database.

15:16 Or you’ll hear people, like I said, refer to this as a SQL database.

15:20 So, over here, we have two examples of tables.

15:23 So, this would be basically how we

15:25 would store information in a relational database.

15:26 So, over here I have a student table.

15:29 And this student table might store individual students.

15:32 So, you’ll see over here we have an ID, a name, and a major.

15:35 So, for every single one of my students, I have an entry just like this.

15:39 And you'll notice over here, I’m giving each of these entries an ID.

15:43 And that ID will uniquely identify that row in the table.

15:48 And over here, we have a users table.

15:50 So, maybe you're creating users for your application.

15:53 So, they have a username, a password, and then an email, right?

15:57 So, the username is something that’s going to be unique.

16:00 It’s something that uniquely identifies each row in the table.

16:03 And then we're also storing like password and email.

16:06 So, we kind of talked about how relational databases store data in tables.

16:10 And when we want to create a relational database,

16:14 we can use a relational database management system.

16:18 And a relational database management system,

16:21 or an RDBMS is just a database management system

16:24 that helps you create and maintain a relational database.

16:27 And some of the most popular are my MySQL, Oracle, PostgreSQL, and MariaDB.

16:34 There’s a bunch of these that are, you know, extremely popular.

16:36 And relational database management systems use

16:39 something called Structured Query Language, or SQL.

16:42 And SQL is a standardized language

16:46 for interacting with relational database management systems.

16:50 So, remember, a relational database management system is just

16:53 a software application that we can use in order to create,

16:58 maintain and do different things to our relational database.

17:02 And SQL or Structured Query Language is the language that we

17:07 can use to interact with those relational database management systems.

17:11 So, we can use SQL to perform CRUD operations

17:15 as well as other administrative tasks like user management,

17:19 security, backup, etc.

17:20 And we can use SQL to define tables and structures.

17:25 So, a relational database uses tables in order to organize its information.

17:29 And we can use SQL to define those tables.

17:32 And then insert information into those tables.

17:34 And SQL is a standardized language which means it’s

17:38 pretty much used on every relational database management system.

17:41 However, different relational database management systems will

17:45 implement SQL just a little bit differently.

17:48 So, not all SQL code that you use on one relational

17:53 database management system will port over

17:55 to another one without slight modification.

17:58 So, now let’s talk a little bit about non-relational databases.

18:01 And in non-relational databases is just anything that’s not relational.

18:06 So, a non-relational database stores data in anything

18:09 but a, you know, a static table.

18:12 So, I’m showing you guys a couple different examples

18:15 of non-relational databases and how they would store data.

18:18 So, over here we have a document database.

18:20 And this would store information inside of like little objects or documents.

18:25 So, we're talking about things like JSON or XML.

18:29 Basically, I think JSON is kind of a very popular

18:31 format and that’s kind of what I have up here.

18:34 So, JSON is JavaScript Object Notation.

18:37 So, there’s a lot of non-relational databases

18:39 that will store data in documents just like this.

18:43 They’ll also store data in graphs.

18:45 So, over here we have graphs.

18:47 And there’s like these little nodes.

18:49 And then there’s, you know,

18:50 connecting lines between the nodes which would represent like relationships.

18:53 And then over here we have a key value hash.

18:56 So, we would have a key and it gets mapped to a value.

18:58 And that could really be anything.

19:00 It could be a string.

19:00 It could be JSON.

19:01 It could be a graph.

19:03 It could be anything like that.

19:04 So, these are just three common examples of what SQL or non-relational

19:09 databases might look like or how they might store their data.

19:12 And just like with relational databases

19:15 we can have non-relational database management systems.

19:19 And these help users to create and maintain non-relational databases.

19:24 So, some examples would be mongoDB, DynamoDB, apache cassandra, firebase.

19:30 There’s a bunch of these popular noSQL databases that have

19:33 been sprouting up over the last decade or so.

19:36 And these are all implementation specific.

19:38 So, unlike relational database management systems where we have SQL which

19:43 is just a standard language that can interact with all of them,

19:46 there isn’t a standard language

19:49 for interacting with non-relational database management system.

19:52 So, generally, the non-relational database

19:54 management system will implement their own

19:57 language for performing CRUD and administrative operations on the database.

20:02 So, now that we've looked at the different types of databases,

20:04 I want to talk to you guys about some core concepts within databases.

20:07 So, one of the most common things that you’re going to be doing are queries.

20:12 So, a database query is a request that’s

20:15 made to the database management system for specific information.

20:19 So, you’ll hear the word query thrown around a lot.

20:21 And generally, a query is you’re just asking

20:24 the database management system to give you some information.

20:27 So, as a databases structure becomes more and more complex,

20:30 it becomes more difficult to get

20:32 the specific pieces of information that we want.

20:36 And if you have a very complex database layout or schema,

20:40 then getting a specific piece of information can be a little bit tricky.

20:45 And that’s why we write database queries.

20:48 So, can write a very complex database query which

20:51 in a lot of ways is like a program,

20:53 if you’ve ever used a programming language before.

20:56 And that query will then instruct the relational database management system

21:00 to grab a specific piece or specific pieces of information from the database.

21:06 So, a good way to think of a query, is it’s kind of like a Google search, right?

21:11 So, if I go on Google and I would

21:13 generally type in the specific information that I want.

21:16 And then Google would give me the information that matches that search query.

21:20 That’s a lot like a database query.

21:22 Except with a database query we're not going to be

21:25 writing the query in English like we would with Google.

21:28 So, I can, you know, go up to my Google search bar and I can type

21:31 something in in English or Spanish or whatever language you speak.

21:34 But with a relational database management system we have to write our queries

21:39 using either SQL or a specific language

21:41 that’s meant for that database management system.

21:45 So, let’s wrap this up real quick.

21:46 We kind of covered a lot of the main points with databases.

21:50 So, a database is any collection of related information.

21:52 It’s a very general term.

21:54 And computers are great for storing databases because computers are really fast.

21:59 They can store lots of pieces of information.

22:01 And they can handle things like security really easily.

22:05 And database management system make it easy to create,

22:09 maintain, and secure a database on a computer.

22:12 Database management systems will allow you to perform

22:15 the CRUD operations and other administrative tasks on the database.

22:18 There’s two main types of databases, relational and non-relational databases.

22:23 Relational databases use SQL and they store

22:26 data in tables with rows and columns.

22:28 Non-relational databases store data using other data structures.

22:33 So, things that aren’t relational database tables.

22:37 And then we talked a little bit about querying.

22:39 A query is just a require that you would make

22:42 to the database management system for a specific piece of information.

22:47 So, that is kind of the basics, the overall high-level basics of databases.

22:52 And really, if you understand everything in this PowerPoint,

22:55 then you have a good grasp of database fundamentals.

22:58 And as we go forward, we're going to learn more and more

23:01 things about databases like how to create them,

23:04 how to store data, and how to organize our data

23:06 so that it’s easy to retrieve when we want it.

23:09 [Tables and Keys] In this tutorial,

23:14 we're going to look at some of the core concepts in relational databases.

23:18 So, we're going to talk about how we store information in relational databases.

23:22 Namely, in tables.

23:23 Let’s talk about the different things about those tables.

23:26 We're going to talk about things called keys.

23:28 We're also going to look at rows and columns.

23:30 We'll talk about different tables can be related to each

23:33 other and how we can define relationships between tables.

23:36 So, this will really just give you guys a full

23:39 introduction into some of the core concepts of relational

23:41 databases which you can then apply when creating

23:45 your own relational database using a relational database management system.

23:49 So, let’s get started.

23:50 Over here I have an example of a table.

23:53 And this is a student table.

23:55 And so, the student table defines specific information about a student.

24:00 So, up here we have the column names.

24:03 So, the student ID, the same of the student, and then the major.

24:07 So, I’m storing three pieces of information about each student, right?

24:11 They have their ID, their name, and their major.

24:14 So, over here we have Jack.

24:16 His student ID is 1.

24:17 And he’s majoring in biology.

24:19 Down here, we have Claire.

24:20 Her student ID is 3 and she is majoring in English.

24:24 So, all tables in relational databases are going to have two things.

24:27 They’re going to have columns and they're going to have rows.

24:30 Now, columns are these vertical sections right here.

24:33 So, a column would define a single attribute, right?

24:36 So, we have our name column.

24:37 And inside the name column we have the names of all the students.

24:40 We have the major column.

24:42 Inside the major column we have the majors of all the students, right?

24:44 Makes sense.

24:45 And then we have rows.

24:47 And a row is an individual entry in the student table.

24:51 So, a row or a horizontal entry would represent a single student, right?

24:56 So, in a single row we're storing the student ID, the name, and the major.

25:01 So, the columns represent a single attribute

25:04 and the row represents an entry or an actual student.

25:08 Whenever we're creating a table in a relational database we always want

25:13 to have one very special column which is called the primary key.

25:17 And a primary key is basically an attribute

25:20 which uniquely defines the row in the database.

25:24 So, it’s an attribute about a specific entry which will uniquely define it.

25:28 So, over here, we have student ID.

25:31 And notice that I colored this in red and I also underlined it.

25:34 And this underline is basically going to specify that this column

25:38 or this attribute of the student is going to be the primary key.

25:43 In other words, we can use this attribute to uniquely identify a specific row.

25:47 So, over here we have Kate and she’s a sociology major and her ID is 1.

25:53 So, we would say that Kate’s primary key is 1.

25:57 Now, let me show you guys why primary keys can come in handy.

26:00 So, down here, inside of these gold squares I have two entries in this database.

26:04 So, we have two students, both of which are named Jack.

26:07 And both of which are biology majors.

26:10 Now, this isn’t that, you know, special of a case, right?

26:13 You can imagine that in a school you might have

26:15 two students with the same name who are the same major.

26:18 But in this case, how can we differentiate between this guy and this guy?

26:24 Well, we can use this primary key.

26:27 So, even though the name and the major of this row in the table

26:32 are the same as the name and the major of this row, the student ID is different.

26:36 The primary key is different.

26:39 And that’s the whole point of a primary key,

26:41 is that the primary key is always going to be unique for each row in the table.

26:45 So, even if all of the attributes of the row are the same,

26:49 the primary key won’t be.

26:50 And therefore we can differentiate between the two rows.

26:54 So, I can differentiate between this Jack

26:57 and this Jack by referring to their student IDs.

27:00 And so, generally, whenever we have a table in a relational database,

27:03 you always want to define a primary key.

27:06 And a primary key can be anything.

27:08 It can be a number.

27:09 It could be a string of text.

27:10 It could really be anything that you want.

27:13 But it has to uniquely identify the specific row.

27:16 So, in other words, another row in this student

27:19 table couldn't have a primary key of 2.

27:21 So, down here I have another example of a database table.

27:24 This is a user table.

27:26 So, just like that student table, except it’s storing information about users.

27:30 So, over here we have email, password, date created and type.

27:34 So, email is going to be our primary key.

27:36 You can see I put that in red and I also underlined it.

27:39 And these emails are unique to each entry in the table, right?

27:44 So, fakemail@fake.co.

27:46 No, other rows in this table could have that exact email.

27:49 And then over here we're storing the password.

27:51 We're also storing a date.

27:53 So, a lot of times in databases you can store date values or time values.

27:57 And then over here we're storing a type.

27:59 And so, basically whenever you want to store data,

28:01 what you’re going to do is define a table.

28:03 So, you’re going to define, you know,

28:06 all this information up here and then you'll

28:08 just insert specific pieces of information into that table.

28:12 And so, over here we have one more example of a database table.

28:16 And we're actually going to be looking at this example as we go forward.

28:19 So, this is an example of an employee.

28:22 So, imagine that we have like a company

28:25 database and we were storing information about employees.

28:27 So, we have the employee ID which is the primary key.

28:29 We're storing first name, last name, birthdate, sex, and salary.

28:33 And we're storing all of this specific information about an employee.

28:37 Now, I want you guys to notice the primary key over here.

28:39 So, employee ID, this is just a, you know,

28:42 some sort of random number that we’ve assigned to each employee, right?

28:46 This employee’s ID is 100.

28:48 This employee ID is 101, etc.

28:50 And this over here, this employee ID,

28:53 this is what we would call a surrogate key.

28:56 And a surrogate key is basically a key that has

28:59 no mapping to anything in the real world, right?

29:01 It’s essentially just, you know,

29:02 like in this case a random number that we assign to an employee, right?

29:05 So this, employee Jan has an employee ID of 100.

29:09 That doesn’t necessarily mean anything.

29:10 100 is just a value that we're using to represent Jan inside of the database.

29:16 And so, we would refer to that as a surrogate key,

29:19 which is just a type of primary key.

29:21 Surrogate key is a key that has no mapping to anything in the real world.

29:24 We can also have something called a natural key.

29:26 And over here you'll see that I have

29:29 the same exact table except instead of having employee ID, I have employee SSN.

29:33 And SSN stands for Social Security Number.

29:36 So, Social Security Number is a number that we use here

29:39 in the United States in order to uniquely identify each citizen.

29:43 So, in this case we're using the Social Security Number

29:47 in order to uniquely identify each row in the table.

29:51 In other words, we're using the Social Security

29:54 Number as the primary key of the table.

29:57 And this is an example of what we would call a natural key.

30:00 And this is a key that has a mapping or has a purpose in the real world,

30:06 not just in the database.

30:08 So, a lot of times you'll hear people refer

30:11 to surrogate keys or natural keys and that’s the difference.

30:14 A surrogate key is a primary key that has no mapping to the real world.

30:17 And a natural key is a key that has a mapping to the real world,

30:23 just like Social Security Number.

30:25 So, those are kind of the two different types

30:27 of primary keys that you might see being stored.

30:29 Another thing I want to talk to you guys about are what are called foreign keys.

30:33 And a foreign key is basically an attribute that we can store

30:37 on a database table that will link us to another database table.

30:43 So, over here I have this same exact employee table.

30:46 And then I also have this other attribute over here, branch_id.

30:51 And you notice that I colored this in green.

30:54 And this is what we would call a foreign key.

30:57 And a foreign key stores the primary key of a row in another database table.

31:04 So, here we have our employee table

31:07 and I’m defining information about the employee.

31:09 But let’s say that an employee belongs

31:11 to a specific branch in our company, right?

31:14 So, a company might have different branches.

31:16 And we can store the information about what branch

31:19 the employee belongs to inside of a foreign key.

31:23 So, the foreign key over here is actually a primary key inside of another table.

31:29 In our case, a branch table.

31:30 So, let me show you guys that.

31:32 So, down here, we have this branch table in our database, right?

31:35 So, the branch is it’s own separate table.

31:38 And we have– the branch has a primary key over here which is 2, 3, 1.

31:42 And the branch names are Scranton, Stamford, and Corporate.

31:45 So this in itself is its own table, right?

31:49 It’s the branch table.

31:49 And over here we can define which branch

31:52 a specific employee belongs to, by referring to the ID,

31:57 the primary key of the branch.

32:00 So, here, Jan Levinson, her branch ID is 1,

32:03 which means she is in the corporate branch, right?

32:07 Because branch ID number 1 is corporate.

32:10 Michael Scott, his branch ID is 2, which means he’s in the Scranton branch.

32:16 So this number is mapping this row over here into this other table.

32:23 And that’s what a foreign key does.

32:24 A foreign key is essentially just a way

32:27 that we can define relationships between the two tables.

32:31 So, a foreign key is just a primary key of another table.

32:35 So, Andy Bernard, right?

32:37 His branch ID is 3 which means he belongs to the Stamford branch.

32:41 And so, that’s what a foreign key does.

32:44 A foreign key allows us to link up or define relationships between tables.

32:49 I can say that Andy belongs to the Stamford

32:52 branch and I can define that using the foreign key.

32:56 And over here you'll see on the branch table,

32:58 I also defined another foreign key which is manager ID.

33:02 mgr_id, that would stand for manager ID.

33:04 And now this is actually going to be

33:07 a foreign key which connects branch to the employee table.

33:10 So, manager ID is going to be the ID

33:13 of a particular employee who is the manager of the branch.

33:16 So, let’s take a look at the Scranton branch.

33:19 So, the branch ID is 2.

33:20 The name of the branch is Scranton, and the manager ID is 101.

33:24 So, let’s go check it out.

33:25 So, over here in the employee table, employee 101 is Michael Scott.

33:29 What that means is that Michael Scott

33:32 is the manager of the Scranton branch, right?

33:36 We were able to define that relational by using these foreign keys.

33:40 So, down here, the Stamford branch, the manager is 102.

33:44 If we come up here, we can see employee 102 is Josh Porter.

33:48 So, Josh Porter is the manager of the Stamford branch.

33:51 Corporate over here, the manager ID is 108.

33:54 So, over here we didn’t actually include 108 over there.

33:57 So, obviously, this isn’t like, you know, a huge table of employees.

34:01 But the manager for the corporate branch would

34:04 be the employee with the ID of 108, whatever that would be inside of this table.

34:09 So, that’s what a foreign key does.

34:11 A foreign key is able to help

34:13 us to define relationships between the tables, right?

34:16 I can define which branch a specific employee belongs to by including

34:21 the branch’s ID as a foreign key in the employee table.

34:26 Similarly, I can define which employee is the manager of a branch

34:30 by including that employee’s ID as a foreign key in the branch table.

34:36 And so, that’s really why foreign keys are going to come in handy.

34:40 And it’s also important to note that a particular

34:42 table can have more than one foreign key on it.

34:45 So, ever here I have my employee table, right?

34:47 And it’s the same exact thing as we have before.

34:50 But I also added another attribute or other column over here which is super_id.

34:55 And that stands for supervisor ID.

34:58 And so, what this is going to do is it’s

35:01 going to define who is the supervisor of a particular employee.

35:04 And what’s cool about this foreign key is

35:06 it’s actually relating back to the same table.

35:09 So, an employee can be a supervisor of another employee.

35:13 So, over here we have Angela Martin, right?

35:15 Her employee ID is 103.

35:17 And her supervisor ID is 101.

35:20 And that means her supervisor is the employee with the ID 101.

35:26 So, if we come over here we can look.

35:28 Angela Martin’s supervisor is Michael Scott because

35:30 Michael Scott has an employee ID of 101.

35:32 So, Michael Scott.

35:34 Michael Scott’s super ID is 100.

35:36 That means Michael Scott’s supervisor is the employee with ID 100,

35:41 which is Jan Levison.

35:43 So, we can these this foreign key over here

35:46 on the employee table to define relationships between employees.

35:50 So, before we define the relationship between

35:52 the employee table and the branch table,

35:54 and now we're defining a relationship between

35:57 the employee table and the employee table, right?

36:00 And so, what’s cool about this is then basically inside of this table,

36:03 we can tell what the different

36:06 supervisor or supervisee relationships are, right?

36:08 I can tell that Josh Porter’s supervisor is Jan Levinson.

36:12 And I can tell that Angela Martin’s supervisor is Michael Scott.

36:15 Defining that information by using a foreign key.

36:19 All right, so let’s take a look at another table.

36:21 I actually added in another table down here.

36:23 So, over here we have employee, just like we had before.

36:25 We have branch.

36:26 And then we also have branch supplier.

36:28 And branch supplier is another table that we can look at.

36:33 So, a branch supplier would basically define

36:36 who the suppliers are for specific branches.

36:39 So, we have these branches.

36:41 Maybe they're selling a certain product.

36:43 Maybe they're selling like paper products.

36:44 And the branch suppliers would define who are the suppliers for that branch.

36:48 And you'll notice up here, the primary key actually consists of two columns.

36:54 And this is what we would call a composite key or a composite key.

36:58 And a composite key is basically a key that needs two attributes.

37:03 So, this key is made up of two columns, right?

37:07 It’s made up of branch_id and supplier_name.

37:10 So, branch_id is actually going to refer to the specific branch.

37:15 And supplier_name is going to refer to the specific supplier, right?

37:20 So, we can say over here that Hammer Mill supplies paper to branch number 2.

37:24 So Hammer Mill supplies paper to Scranton.

37:28 Uni-ball supplies writing utensils to branch ID number 2, which is Scranton.

37:33 Patriot Paper supplies paper to branch ID number 3 which is Stamford.

37:38 So, inside of this branch supplier table,

37:40 I’m able to define which different suppliers

37:43 are supplying what to which different branches.

37:46 Now, the reason that I need this composite key or this composite key,

37:51 is because the supplier name doesn’t uniquely identify each row.

37:56 And the branch_id doesn’t uniquely identify each row.

38:00 Only together can they uniquely identify each row, right?

38:04 So, for example, supplier name, Hammer Mill.

38:07 You’ll notice that Hammer Mill shows up here and it also shows up here.

38:10 So, Hammer Mill supplies to branch number 2.

38:12 And they also supply to branch number 3.

38:14 Uni-ball supplies to branch number 3 and Uni-ball supplies to branch number 2.

38:19 So, this column has repeated values, right?

38:22 This column has Hammer Mill repeated.

38:25 It has Uni-ball repeated.

38:26 branch_id also has those things repeated, right?

38:30 So, branch_id 2 shows up here a bunch of times.

38:32 branch_id 3 shows up here a bunch of times, right?

38:35 And so, the branch_id column can’t uniquely identify the table.

38:40 And the supplier name column can’t uniquely identify the table.

38:44 Only together can they uniquely identify it, right?

38:47 So, we would say Hammer Mill supplies branch 2.

38:50 That combination only shows up once.

38:53 We wouldn't put that combination again because we’re already defining it.

38:57 So, Hammer Mill supplies branch 2.

38:59 Hammer Mill supplies branch 3.

39:01 These two rows are uniquely identified by the identified

39:04 by the supplier name and the branch ID.

39:07 And so, over here in these employee and the branch table,

39:10 we only find one column as the primary key.

39:13 But over here, we define two columns as the primary key,

39:16 which would be a composite key.

39:18 And that’s actually pretty common,

39:20 where we're going to have two columns uniquely identifying each row.

39:23 All right, so down here I want to show you guys one more example.

39:26 And this is going to show you one other way

39:29 that we can define like different relationships with these tables.

39:32 So, I actually added in two other tables.

39:34 We have a client table and we have this Works With table.

39:38 So, let me kind of walk you guys through this a little bit.

39:40 We still have employee.

39:41 We still have branch.

39:42 But over here, we're defining clients.

39:43 And so, a client might be like a customer, right?

39:46 So, a client would buy paper products or, you know,

39:50 whatever products from the branch and employee.

39:53 So, the client has a client ID.

39:54 They have a client name.

39:56 And then they also have a foreign key which is a branch ID.

39:59 So, a client is going to be associated with a specific branch, right?

40:03 So, we would say Lackawana County is associated with branch 2.

40:07 Which means the Lackawana County is going

40:10 to buy products from the Scranton branch.

40:12 The John Daly Law, LLC is going to buy products from branch ID number 3.

40:18 So, they're going to buy products from the Stamford branch, right?

40:21 So, this is a client table.

40:23 And down here, we have this Works_With table.

40:27 And what the Works_With table is doing is it’s

40:30 defining the relationships between the employees and the clients.

40:33 Namely, how much paper an employee sells to a specific client.

40:38 So over here we have employee ID.

40:41 We have client ID.

40:42 And we have total sales.

40:43 So, the employee ID is going to refer to an employee in the employee table.

40:48 The client ID is going to refer to a client in the client table.

40:51 And then we're going to define how much

40:54 in product the client has bought from the employee.

40:58 So, employee 101, for example, that is Michael Scott.

41:01 Michael Scott has sold client ID 401.

41:05 Michael Scott has sold Lackawana County $267,000 worth of product, right?

41:12 How about this one, 104.

41:13 So, Andy Bernard has sold client number 403–

41:18 has sold John Daly Law $5000 in paper products.

41:23 And so, this Works_With table is able to define

41:26 how much an employee has sold to a client.

41:30 And you'll notice over here we have a composite key.

41:33 And the composite key is employee ID and client ID.

41:36 And this is actually a special type of composite

41:40 key because both of these columns are actually foreign keys.

41:45 So, employee ID is a foreign key, right?

41:47 It relates to the employee table.

41:49 Client ID is also a foreign key.

41:51 It relates to the client table.

41:53 And both of those foreign keys together makes up the primary key of the table.

42:00 And that is actually a very useful way to define a primary key.

42:05 Because when we do something like this, we can define a relationship, right?

42:09 So, I can define how much product the client has bought from the employee.

42:14 And that is something that can because were useful to keep track of.

42:17 So, as you can see, like these tables can

42:20 either be very simple or they can be very complex.

42:22 And the more complex your database design or your database schema is,

42:28 the more complex these tables and these keys are going to have to be, right?

42:32 So, this employee table has a primary key and two foreign keys.

42:35 The client table has a primary key and a foreign key.

42:38 This Works_With table has a composite key.

42:42 You know what I mean?

42:43 Both of which are foreign keys.

42:44 Like it can get very complex, but we can use the primary keys

42:48 and the foreign keys in order to define different relationships.

42:52 But really, that is just a, you know,

42:54 I think a pretty good introduction into kind of how tables work.

42:58 We talked about rows and columns.

42:59 We talked about primary keys.

43:01 We talked about surrogate keys and natural keys.

43:04 You know, the different types of keys that might map to the real world or not.

43:08 We talked about foreign keys.

43:10 We talked about composite keys.

43:11 And I kind of showed you guys a few

43:14 different examples of ways that things might work.

43:16 So, this would be like our company database.

43:19 And obviously, we would have a lot more information in here.

43:22 But this is kind of a good example of how we might define

43:26 different tables and then define the relationships

43:28 between those tables in a relational database.

43:30 [SQL Basics] In this tutorial I’m going to walk

43:37 you guys through SQL which stands for Structured Query Language.

43:41 So, SQL is a term that you're going to hear

43:43 thrown around all the time when you're talking about databases.

43:46 And SQL actually is a language.

43:49 And it’s a language that’s used

43:52 for interacting with relational database management systems.

43:55 So, SQL is– it’s similar to– kind of like a programming language.

44:00 A lot of times you’ll hear people refer to SQL as a programming language.

44:03 It’s not technically a programming language in the tradition sense.

44:07 Although, you can use SQL to provide

44:10 instructions to a relational database management system.

44:13 So, I guess if you want, you could call it a programming language.

44:16 So, like I said, SQL, it’s a language that’s used

44:19 for interacting with relational database management systems.

44:22 A relational database management system is a piece of software.

44:25 It’s a software application that you can

44:28 use to create and maintain a relational database.

44:32 You might use a relational database management system

44:35 to create a database for an application that you're building.

44:39 And then that relational database management

44:41 system can make sure that the database

44:44 is structured correctly and store everything the way that it needs to be stored.

44:48 So, in order to interact with a relational database management system,

44:53 we need to use a special language.

44:55 So, I can’t just talk to it in English.

44:58 I can’t just be like, “Hey, relational database management system,

45:01 get me this piece of information.”

45:03 Relational database management systems don’t speak English.

45:06 But what they do speak is a language called SQL.

45:09 They speak Structured Query Language.

45:12 And so, if we want to ask a relational

45:14 database management system to do something for us,

45:17 for example, like store a piece of information or create a table,

45:21 update a piece of information.

45:23 We can ask the relational database management system to do that using SQL.

45:28 So, SQL is the language that we can

45:32 use to communicate with the database management system.

45:35 And, you know, we can use SQL to get

45:38 the relational database management system to do stuff for us,

45:41 like create, retrieve, update, and delete data.

45:43 Create and manage different databases.

45:45 Design and create database tables.

45:49 So, we can define like a database schema which

45:51 would just be like the overall like table design.

45:54 And perform administrative tasks.

45:56 So, things like security.

45:58 We could do like user management, importing, exporting, backup, all that stuff.

46:02 So, SQL can be used to tell the relational database

46:05 management system to do all of that stuff for us.

46:09 Now, here’s the thing, SQL implementation can vary between the systems.

46:13 So, SQL, the actual language actually does have a formal specification.

46:18 So, there is like an overall formal specification which defines, you know,

46:23 how SQL needs to be used and all the different commands that can be used.

46:27 But the problem is that there’s

46:29 a bunch of these relational database management system.

46:32 Some of the popular ones we hear about,

46:35 like Postegres, MySQL, Oracle, Microsoft SQL Server.

46:39 Like all of these relational database management systems

46:42 are going implement SQL just a little bit differently.

46:46 So, you could write SQL code that would

46:49 work on one relational database management system,

46:52 but then if you tried to use it on another one, it might not work 100%.

46:56 Now, for the most part, everything should be the same,

46:59 but you might just need to tweak a couple little things.

47:01 So, one thing you want to keep in mind with SQL is that, you know,

47:05 SQL is used on all of the major relationship database management systems,

47:09 but it’s used slightly differently.

47:12 So, certain instructions might work on one database management system

47:16 and they might not work on another, or vice versa.

47:18 Or you might do things just a little bit differently, depending.

47:21 But for the most part,

47:23 they’re all implementing SQL which means it’s all basically the same.

47:26 So, the concepts are the same but the implementation may vary just a little bit.

47:31 So, let’s talk a little bit more about SQL.

47:33 It’s actually a hybrid language.

47:35 So, it’s basically four types of languages in one.

47:39 And you know, you don’t need to worry too much about all this stuff,

47:43 but you are going to hear people talking about these different aspects of SQL.

47:48 And so, I want to introduce them to you so you understand what they are.

47:50 So, SQL, you know like I said,

47:53 it’s four types of languages all mashed into one single language.

47:56 So, SQL is a data query language,

47:59 which means it can be used to query the database for information.

48:04 So, you can write queries in SQL which tell the relational database management

48:09 system what pieces of information that you want to get from the database.

48:14 And so, a data query language is used

48:16 to get data that’s already stored in the database.

48:19 SQL is also a data definition language.

48:22 And what that means is you can use SQL to define database schemas.

48:27 Now, a schema is basically just like the overall layout of the database.

48:31 Like what tables are going to be in the database,

48:34 what columns those tables are going to have,

48:37 and the data types that those columns are going to be able to store.

48:39 So, we can use SQL to define data in the different,

48:44 like I said, database schemas.

48:47 SQL is also a data control language which means it’s

48:50 used for controlling access to the data in the database.

48:53 Basically, you can use it to configure like users and permissions.

48:57 So, I could define a bunch of different users for the database.

49:01 And I can say like, okay, this user can write to this table.

49:04 Or this user can read information from this table.

49:07 Or this user can update and delete information from this table.

49:11 So, you can use SQL to control the access to the data.

49:16 And SQL is also a data manipulation language.

49:20 So, it’s used for inserting, updating, and deleting data from the database.

49:26 So, these are the four different types of things that you can do with SQL.

49:30 And you'll hear people using these terms kind of a lot.

49:34 And even like database management systems

49:36 might throw error messages or certain things.

49:39 And they'll say like data query language or data definition language.

49:42 So, it is good to just understand these different types of things that SQL

49:47 can do and how they're broken up into these four like broad types of languages.

49:52 But basically, the whole point of this slide

49:54 is that SQL can do a bunch of stuff.

49:56 And it’s super powerful.

49:58 And that’s one of the reasons why it’s

50:00 used in all these relational database management systems.

50:03 So, now let’s talk about queries.

50:05 And one of the things that we're going to be doing a done of in this course,

50:09 and you know, if you’re working with databases,

50:10 you’re going to be doing this all the time, which is querying.

50:13 So, query is a set of instructions given

50:15 to the relational database management system generally written in SQL,

50:19 that tell the RDBMS what information you want it to retrieve for you.

50:24 So, if you have a database that has like millions or billions of records in it,

50:29 like there’s tons of data.

50:31 And a lot of times that data is,

50:33 you know, spread out across different tables, right?

50:36 It’s sort of hidden in this complex schema.

50:39 You know what I mean?

50:40 Like you have one piece of information stored

50:42 over here and then another piece over here,

50:44 another piece over here in different tables.

50:45 And you want to kind of grab all

50:47 that information and organize it in a specific way.

50:49 We can use queries in order to tell the RDBMS to do that for us.

50:55 So, the goal of writing a query is that we

50:58 only want to get the information that we need, right?

51:01 Imagine that if you, you know,

51:03 every time you wanted a piece of information from the database

51:05 you had to grab all of the information in the database,

51:08 parse through it, and then find the information that you want.

51:11 We don’t have to do that.

51:12 Instead, you can write a query which will

51:15 tell the RDBMS exactly what piece of information

51:18 you want and it’ll be able to grab

51:21 just that information and send it back to you.

51:23 So, over here I just have a little example of a query.

51:26 So, this would be like maybe we're trying

51:28 to get– this would be SELECT employee.name, employee.age.

51:32 So, select the employee’s name and the age from the employee

51:35 table where the employee’s salary is greater than $30,000.

51:39 So, what this will do is it’ll give us every

51:41 employee in an employee table who makes more than $30,000, right?

51:45 That’s kind of like a general query.

51:46 Don’t worry too much about specifically what all this stuff means.

51:50 We're going to get into that in this course.

51:52 But that’s kind of like what a query would look like.

51:54 We're telling the RDBMS what information we want

51:57 and then it only gives us back that information.

52:01 Not like all the information in the database.

52:03 All right, so that is kind of an overview of SQL.

52:06 I mean obviously I didn’t get into everything.

52:08 We're going to look at all of these, you know,

52:11 little specific things that we can do with SQL as we go forward.

52:14 But for now, that should kind of give you

52:16 an idea of what SQL is, why it’s useful,

52:19 and you know, sort of what it’s doing for us

52:21 and how we can use it to work with our databases.

52:25 [MySQL Windows Installation] In this tutorial I’m

52:30 going to show you guys how to download

52:31 and install two things that we're going to need going forward in this course.

52:34 The first thing and the most important is called MySQL.

52:38 And MySQL is a RDBMS.

52:41 Which basically means it’s a software application which we can use

52:46 to maintain and create and do all that sort of stuff with databases.

52:50 And so, when we have MySQL installed on our computer,

52:54 we can actually set up what’s called a MySQL database server.

52:58 And that would basically be a server where MySQL is running.

53:02 And then we can write SQL in all sorts of queries and instructions

53:05 in order to create and do all sorts of stuff with databases.

53:09 So, the first thing we'll do is download and install MySQL.

53:12 And the second thing we're going to do is we're

53:14 going to install another program which is called PopSQL, or PopSQL.

53:19 And it’s basically a program that I’m going to be using

53:21 in this course in order to kind of like write all of my queries.

53:24 It’s essentially a text editor.

53:26 But it’s a text editor that can hook up with our MySQL database.

53:30 And it’ll just be a really easy interface and a really

53:33 easy way for us to write queries and get the information back.

53:36 So, I’m going to show you guys how to download and install both of those things.

53:39 The first thing we want to do is

53:41 open our browser and we're going to install MySQL.

53:42 So, you just want to come up here and you're

53:45 just going to search in Google for MySQL community server.

53:49 So MySQL community server is this first link right here.

53:52 It’s just dev.MySQL.com.

53:54 And this is actually a free and opensource piece of software.

53:59 It’s basically like the most basic version of MySQL.

54:01 And it’s the version that we're going to be using.

54:03 So, if you’re a beginner, this is probably what you want to go for.

54:07 It’s kind of just like a great environment for you to learn in.

54:09 It’s pretty simple.

54:10 So, down here you have a couple different options.

54:12 You can download just like a zip or you can download the actual installer.

54:16 So, you’ll see over here there’s this option for the MySQL installer.

54:20 So, you’re going to go ahead and click on that.

54:22 And then that’ll bring you to this menu over here.

54:24 And you can see here’s the MSI installer.

54:29 So, we're just going to go ahead and download that.

54:31 And it brings you to this page.

54:32 It’s asking you to like log in or sign up,

54:34 but you can just click No Thanks down here.

54:36 And it’ll go ahead and start the download.

54:38 So, this installer is really great because

54:39 it will kind of configure everything for us

54:41 and it’ll allow us to pick and choose

54:44 what MySQL products that we want to actually download.

54:46 So, let’s go ahead and accept the license and I’m going to click Next now.

54:52 Here we have a couple options for how we want to set this up.

54:54 You can see you can just do like Developer, Default, Server only, Client only.

54:59 What we want to do is– actually, we're going to do a custom install.

55:02 Now, here’s the thing, if you want, you can just do the developer default.

55:05 But that’s going to install a bunch of stuff

55:07 that we're not actually going to be using.

55:09 Or at least that I’m not going to be using in this course.

55:12 So, I’m going to ahead and click custom

55:13 because I only need to install a couple things.

55:15 So, the first thing we’ll do is come down here to MySQL

55:19 Servers and I’m just going to click MySQL server 5,700 right here.

55:23 And I’m going to go ahead and add that over here to the right side.

55:27 And then I’m going to come down here to applications.

55:30 And I’m going to come down here to MySQL Shell.

55:34 And I’m going to do the same thing.

55:35 So, I’m just going to click down here to MySQL shell.

55:36 I’m doing the 64 bit version.

55:38 I’m going to add that over here.

55:39 So, these are the only two things that I’m

55:41 really going to be using for this course.

55:42 But if you want, you can download everything.

55:45 That way, you just have it.

55:46 So, I’m going to click Next.

55:47 I’m going to click Execute.

55:48 And this is going to go ahead and install both of those things for us.

55:51 So, now you can see they’re downloading.

55:52 And this shouldn't take too long.

55:54 They should be downloaded pretty quickly.

55:55 And then we'll be able to move on.

55:57 All right, once those two things are finished downloading,

55:59 then we can just click next.

56:00 And we can also just click next through here.

56:03 And you’ll see it’s asking us what we want.

56:05 I’m just going to click standalone, MySQL Server, Classic, MySQL Replication.

56:09 That’s all we need.

56:10 And you can just leave all of this stuff,

56:12 all this Type and Networking stuff as the default.

56:14 And then over here, it’s asking us to create a root password.

56:18 So, whenever we're using this MySQL database server,

56:20 we're going to have to log into it using an account.

56:24 So, there’s already a default admin account set up for us which is called Root.

56:29 But we're going to have to give this a password.

56:30 So, I’m just going to type in password as the password.

56:32 And again, down here.

56:34 So, make sure that you remember what this password

56:36 is because we're actually going to need it later.

56:39 And if you want, you can add additional users.

56:42 I’m not going to do that.

56:43 So, then we can just click Next.

56:43 And you'll see there’s an option here.

56:45 It says Start the MySQL Server at system start-up.

56:48 And if you want, MySQL to start whenever you start-up

56:50 the system you can go ahead and keep that checked.

56:53 And then I’m just going to click Next.

56:54 And click Next.

56:55 And just click Execute.

56:57 So, this is going to go through and do a bunch

56:59 of stuff that we need to do to get this set up.

57:01 So, you'll notice that it’s actually, you know,

57:03 not super trivial for us to set this up.

57:05 I mean obviously the installer makes it really easy.

57:07 But MySQL is a complex program.

57:09 And that’s why there’s all these little things that we have to keep configuring.

57:13 But we're almost done and then we'll be able to move on.

57:16 So, now that everything is finished configuring,

57:18 we can just click finish and I’m going to click Next.

57:21 And we can just click Finish.

57:23 All right, so once we've finished installing everything,

57:27 now what we need to do is we're going

57:28 to actually create a database that we can use.

57:30 So, I’m going to come over here and we're

57:32 just going to come over here into the Start Menu.

57:34 And you’ll see there’s this option here, MySQL 5,700 command line client.

57:38 So, we're going to actually go ahead and use that.

57:40 And what this will do when I open it up, you’ll see it says Enter Password.

57:44 So, I’m just going to enter in that password that I created.

57:47 In my case, it was just password.

57:48 And you'll see here we're actually logged in.

57:51 So, now we're connected to that MySQL server that is running on our computer.

57:56 So, from in here, what we can actually do is create a database.

58:00 So, I’m just going to say create database.

58:03 And I’m going to give it a name.

58:06 So, I’m going to call it giraffe.

58:07 And then I’m going to type a semicolon.

58:09 So, what this will do, is it’ll create a database for us called giraffe.

58:12 Now remember, MySQL is a database

58:14 management system which means it’s an application

58:16 or a software program that’s designed to help us manage various databases.

58:20 So, in order to start building a database, we need to create one.

58:24 So, I’m just going to say create database giraffe.

58:26 I’ll hit enter.

58:27 And this is going to go ahead and create that database for us.

58:30 So, now we've created this database, we're kind of ready to go.

58:33 So, for the rest of the course,

58:34 what I’m going to be doing is teaching you guys like SQL commands.

58:37 So, we're going to be learning all about this SQL language

58:40 which is used to interact with MySQL and other database management systems.

58:43 Now, if you want, you can do everything that I’m doing

58:45 for the rest of the course here inside of this terminal.

58:48 So, inside of this little terminal window

58:50 you can basically do everything that I’m doing.

58:53 You can type in SQL like this up here.

58:55 Create database.

58:56 That’s all valid SQL.

58:58 But I’m actually going to be using a different program which is called PopSQL.

59:03 And that program just makes it a lot easier for us to visualize what’s going on.

59:07 It’s not like some kind of boring terminal window.

59:10 So, that’s basically a text editor which will connect to our database

59:15 server and we'll be able to write our SQL from inside of there.

59:18 So, I’m going to show you guys real quick how to download that.

59:20 You’re just going to come up here.

59:22 I’ll make a new tab.

59:23 And we're going to come up to the search

59:26 bar and just type in P-O-P S-Q-L, PopSQL.

59:28 And you'll see this link here, PopSQL.io.

59:33 So, we're just going to click that.

59:34 So, here there should be an option to download for Windows so

59:37 we can just click on that and that’ll start downloading it for us.

59:40 So, PopSQL is cool.

59:42 Actually, it’s kind of like Google Docs but for writing SQL queries.

59:46 Now, we're not going to be using that functionality.

59:49 We're just going to be using it as a text editor.

59:51 But it’s a great way for us to visualize like the SQL queries that we're

59:56 writing and then also like what gets

59:57 returned from those queries and stuff like that.

59:59 So, once that’s done downloading,

1:00:00 we can just click on it and we're going to go ahead and run the installer.

1:00:04 When that’s done installing, it should open up right away.

1:00:07 And you’ll see we have to sign in.

1:00:09 So, you can either create an account just with Google

1:00:11 or you can create an account with your email,

1:00:13 but you have to sign in in order to use it.

1:00:15 So, I’m going to go ahead and sign in.

1:00:16 And you’ll see it says, “Welcome, Mike.” So, we're able to sign in.

1:00:19 And I’ll open the app.

1:00:20 So, now that we have the app open,

1:00:22 what it’s asking us to do is connect to a database.

1:00:25 So, remember, we set up our MySQL database server and we created a database.

1:00:31 So, what we can do now is we can connect to that database from inside of PopSQL.

1:00:36 So, over here, it’s just asking us to type in our nickname,

1:00:39 so I’m just going to type in Giraffe.

1:00:41 And the type, if you click down, you’ll see we have MySQL right there.

1:00:44 Host name– so the host name is actually going to be localhost.

1:00:49 And that just refers to like the local

1:00:51 address of the computer that you're currently on.

1:00:54 Port is going to be 3306.

1:00:56 Assuming that you used all the defaults when you're installing MySQL,

1:01:00 then that should be the port number.

1:01:01 And then finally down here,

1:01:03 the database we want to connect to was called giraffe.

1:01:06 So, that’s what I named it.

1:01:07 If you named it different from giraffe, but in the name there.

1:01:09 And then username is going to be root and the password

1:01:12 is going to be the password that you set up.

1:01:14 So, in my case it was just password.

1:01:16 So, once all that information is in, we can just click connect,

1:01:18 and you'll see it’s connected us to our database.

1:01:21 So, now we basically have a text editor that we can use,

1:01:24 which is hooked up to our database so we can write all of our SQL code,

1:01:28 all of our SQL queries in here.

1:01:31 And it’ll actually get run on our MySQL database server.

1:01:37 So, now everything is set up.

1:01:39 And we're ready to go off and learn some awesome SQL commands.

1:01:41 Now, I realize that this was kind of like a little bit of a complex setup.

1:01:44 Unfortunately, that’s just unavoidable because database management systems are,

1:01:48 by nature, you know, complex programs.

1:01:51 I mean this isn’t– they’re not designed to be like extremely user friendly, so.

1:01:55 But now we're ready to go and everything is set up.

1:01:58 [MySQL Mac Installation] In this tutorial I’m

1:02:03 going to show you guys how to download

1:02:04 and install two things that we're going to need going forward in this course.

1:02:07 Now, the first thing we're going to download

1:02:09 and install and the most important is MySQL.

1:02:12 And MySQL is a relational database management system.

1:02:15 And it’s actually one of the most

1:02:17 popular relational database management systems around.

1:02:19 And a lot of people use MySQL as they're first sort

1:02:24 of dip or they're first dive into a database management system.

1:02:27 So, for that reason,

1:02:29 I’ve picked MySQL as the relational database management system

1:02:32 that we're going to use in order to learn SQL.

1:02:35 Now it’s important to note that the focus of this course

1:02:38 isn’t so much on MySQL as it is on SQL.

1:02:41 So, SQL is Structured Query Language.

1:02:44 It’s an actual language.

1:02:45 And MySQL is a relationship database management system.

1:02:50 So, we're going to be using MySQL in order to kind of learn

1:02:54 about and use and learn all the most common stuff with SQL.

1:02:57 The second thing we're going to install in addition

1:03:00 to MySQL is actually going to be optional.

1:03:02 You don’t have to install it, but it’s going to be a text editor that we

1:03:06 can use in order to write our SQL commands.

1:03:09 So, it’s called PopSQL.

1:03:13 And it’s an awesome program which basically

1:03:15 allows you to write out SQL statements.

1:03:16 And then you can execute them from inside the program

1:03:20 and it’ll show you all the results that you get back.

1:03:22 So, I’m going to be using PopSQL in order to kind of visualize everything

1:03:25 that we're doing and just make it a lot easier for you guys to follow along.

1:03:28 So, I’m going to show you guys how to download

1:03:30 both those things so you can follow along with me.

1:03:31 First thing we're going to do is head over

1:03:34 to our browser and we're going to install MySQL.

1:03:36 So, you just want to type in MySQL community server.

1:03:40 And we're just going to type that in to Google.

1:03:41 And then down here, this should pop up.

1:03:44 It’s just download MySQL Community Server from dev.MySQL.com.

1:03:47 So, MySQL Community Server is a free

1:03:50 and opensource version of MySQL that anybody can use.

1:03:54 And if you’re just starting off

1:03:56 and learning about SQL and learning about databases,

1:03:58 then this is a great starting point.

1:04:00 And then all we have to do down here is scroll down

1:04:03 and you’re going to see that there’s a couple different options here.

1:04:05 Basically, what we want to download is the DMG Archive.

1:04:09 So, it’s just this first one right here.

1:04:12 So, I'm just going to go ahead and click Download.

1:04:13 And that’s going to start downloading for us.

1:04:16 Now, it brings you to this page.

1:04:18 It’s kind of asking you to log in or sign up, but you don’t have to.

1:04:20 You can just click No thanks, just start my download.

1:04:23 And then it’ll start the download for you.

1:04:25 So, this is– it’s probably around like 400 megabytes.

1:04:28 It’s not too too big of a file, but it’s also not like a super small file.

1:04:32 You’re probably going to need at least over a gigabyte of storage

1:04:35 on your computer to hold everything that SQL is going to need.

1:04:38 All right, once that is finished downloading,

1:04:40 then we can just click on it and we're

1:04:42 going to go ahead and install MySQL onto the computer.

1:04:45 All right, so here we just have this like PKG.

1:04:48 So, I'm just going to double click on this.

1:04:49 And it’ll open up this installer.

1:04:51 So, we're just going to click through.

1:04:52 And you can basically just leave everything as the default.

1:04:55 Just keep it in the default install location and it’s asking me for my password.

1:04:59 All right.

1:05:00 So once that is done running the installer,

1:05:02 this window right here is going to pop up.

1:05:05 It says, MySQL Installer.

1:05:07 It’s giving me this date.

1:05:08 And it says “A temporary password is generated for root@localhost.” Now,

1:05:13 when we're using MySQL we’re actually going to have

1:05:17 to log into the SQL server using a username and password.

1:05:21 And in order to log in, you’re actually going to need

1:05:24 this temporary password that they provide for you right now.

1:05:27 Now, it’s root@localhost.

1:05:30 So, root is the actual username that we're going to use to login.

1:05:33 Localhost is going to be address

1:05:34 of the SQL server that’s running on our computer.

1:05:37 And then this is going to be like a temporary password.

1:05:40 So, what you want to do is just copy this.

1:05:42 You want to make sure that you keep this.

1:05:42 So, what you could do, is you could just click Command+Shift+4.

1:05:46 And you'll see this little like thing comes up.

1:05:49 If you just drag around here, that’s going to screenshot this for you.

1:05:52 And so, now we'll have a picture of this on your desktop.

1:05:55 Point is, is you don’t want to lose this.

1:05:57 So you want to make sure that you have this temporary password accessible.

1:05:59 If you don’t, then it’s going to be a problem because

1:06:01 you're not going to be able to get into the account.

1:06:04 So, make sure that you record that.

1:06:07 And then I’m just going to click Okay

1:06:08 and then we're done here, so we can close it.

1:06:09 And I’ll move it to trash.

1:06:11 All right, so once we've installed MySQL,

1:06:13 now what I want to do is set up our MySQL server

1:06:17 which basically means that we're going to be running MySQL on our computer.

1:06:21 Now the way this works is MySQL is a relational database management system.

1:06:25 And it’s basically going to act as like a little database server for us.

1:06:30 And so, we can connect to MySQL.

1:06:33 We can login and then we can manage the database from there.

1:06:36 So, we need to make sure that the MySQL server is started on our computer.

1:06:40 So, I’m just going to come up here to the search bar.

1:06:43 And I’m just going to search for System Preferences.

1:06:45 And then down here there should be this little icon for MySQL.

1:06:48 So, we're just going to click on that.

1:06:50 And you’ll see it’s going to open up this window over here.

1:06:53 So, it says, “MySQL Server Status.

1:06:54 The MySQL Database Server is currently stopped.” So,

1:06:57 what we want to do is start it.

1:06:59 And I’m going to put my password in.

0:00 And then if you want,

0:00 you can have it automatically start when you open your computer.

1:07:01 It says, “Automatically start MySQL Server on Startup.”

1:07:08 If you want to do that, you can.

1:07:10 You don’t have to.

1:07:11 So, basically, now a MySQL database server is running on our computer.

1:07:15 And what we can do now is we can connect to that database server and we can log

1:07:21 in and then we can start creating our databases

1:07:23 and creating all of our tables and stuff like that.

1:07:26 So, now that this is set up,

1:07:27 there’s actually a couple things that we have to do.

1:07:29 The first thing I want to do is update our password.

1:07:33 So, we were given that temporary password.

1:07:35 So, I'm going to open up the terminal.

1:07:36 And you can just go over here to the search bar and type in terminal.

1:07:39 And that’ll open this guy up right here.

1:07:40 So, from in here, we need to start configuring a couple different things.

1:07:43 First thing we need to do is get this terminal to recognize the MySQL command.

1:07:48 So, if I come over here and I try to type in MySQL,

1:07:52 you’ll notice that it says command not found.

1:07:54 Basically, our Mac doesn’t know about this MySQL

1:07:57 command because we haven’t told it where it is.

1:08:00 So, there’s a couple things that we have to do

1:08:01 in order to get this MySQL command to work.

1:08:03 So, all we have to do is basically

1:08:06 just tell our computer where we installed MySQL.

1:08:09 So, what I’m going to do, is I’m just going to type this over here

1:08:11 and you guys can type exactly what I’m typing.

1:08:13 Echo single-quote, export PATH in all caps is equal to /usr/local/mysql/bin.

1:08:27 And then you’re going to type a colon$PATH in all caps.

1:08:31 Just like that.

1:08:32 And then you're going to end off the quotation mark.

1:08:34 So, you’re going to do single-quote.

1:08:36 And then two greater than signs.

1:08:38 And we're just going to say~ which is this little tilde character.

1:08:42 /.bash_profile.

1:08:46 So, what this is doing is it’s actually

1:08:49 adding this line inside of this file called .bash_profile.

1:08:53 Basically, what this will do is it’ll make it so

1:08:55 we can use that MySQL whenever we want inside the terminal.

1:08:59 So, I'm just going to hit Enter.

1:09:01 And then I’m just going to .tilde/.bash_profile.

1:09:06 This will kind of reload that file.

1:09:08 And now what you want to do is type in MySQL.

1:09:11 And we'll see if this works.

1:09:13 So, you notice now when I type in MySQL,

1:09:15 instead of saying the command wasn’t found, we got this error message that says,

1:09:19 “Access denied for user ‘giraffeacademy’@’localhost’ (using password: NO).

1:09:23 What this means is that the MySQL command is now working.

1:09:26 So, basically we're able to give commands to MySQL.

1:09:31 We're able to do stuff like that.

1:09:32 So, I’m just going to clear this out.

1:09:34 And now what we want to do is we want to actually

1:09:36 connect to the MySQL server that’s running and we want to log in.

1:09:39 So, I can just type MySQL just like that.

1:09:41 And you're going to type hyphen u.

1:09:44 And you're just going to type root.

1:09:46 And then you’re going to type hyphen p and your going to hit enter.

1:09:49 And this is going to ask you to enter a password.

1:09:51 And so, now is the time where we want to enter in that temporary password.

1:09:54 So, in my case, I took a screenshot of it.

1:09:57 So, that screenshot is now going to be stored on my desktop.

1:10:00 It’s over here.

1:10:01 And so, what I can do is I can just look

1:10:04 at this screenshot now and I can type in the password.

1:10:06 So, I'm going to go ahead and type in that password.

1:10:08 All right, so now that I’ve typed in that password, I was able to log in.

1:10:11 So, basically now we are logged into the SQL server,

1:10:15 or to the MySQL server that is running on our local computer.

1:10:19 So, what we can do now is we can actually change that temporary password.

1:10:24 So, I’m just going to type in the following command.

1:10:26 ALTER.

1:10:27 So ALTER USER.

1:10:30 And then I’m just going to type in ‘root’@’localhost’.

1:10:35 And you'll notice that I have single quotes

1:10:37 around root and single quotes around local host.

1:10:39 So, then I’m going to type in identified by– so

1:10:42 I’m just going to type in a new password.

1:10:44 I’m just going to set it as password just so it’s easy to remember.

1:10:47 And then you're going to type a semicolon.

1:10:48 And now you want to hit enter.

1:10:50 And so, what that’s going to do, is its going to update your user information.

1:10:53 So, now you can actually login

1:10:55 using something other than that temporary password.

1:10:58 So, you'll be able to login using the password that we set over here.

1:11:01 So, now what we want to do is just make sure that everything works.

1:11:03 So, I’m going to type in exit.

1:11:04 Just like that.

1:11:06 And I’m going to clear this out.

1:11:07 And now what we want to do is try to log in again.

1:11:09 So, I’m going to say MySQL-the root -p.

1:11:12 And this time I'm going to enter in the new password which was password.

1:11:16 And you’ll see that I’m able to login.

1:11:18 So, as long as that worked and you’re

1:11:19 able to log in, then you updated your password,

1:11:21 so now you're going to be able to get in, no problem.

1:11:25 So, the next thing I want to do really

1:11:27 quick is we're actually going to create a database.

1:11:29 So, MySQL is a database management system.

1:11:32 So, what it allows you to do is manage and keep

1:11:34 track of different databases and the information that are in those databases.

1:11:37 So, what we want to do before we can actually

1:11:40 get started is we want to create an actual database, right?

1:11:43 So, what we can do is we can just type out create database.

1:11:48 And then I’m just going to name this database giraffe, just like that.

1:11:52 So, I’m creating a database named giraffe.

1:11:54 And you can name your database whatever you want.

1:11:56 Generally, it’s going to correspond to the type

1:11:58 of data that you're storing in it.

1:11:59 I’m just going to be using this as a general

1:12:02 purpose database to go throughout this course.

1:12:03 But we can just say create database giraffe.

1:12:06 And this will create a database for us called giraffe.

1:12:08 And then you’re going to type a semicolon after that.

1:12:11 And then you're going to click Enter.

1:12:12 So, now we actually have a database called giraffe that we can use.

1:12:16 All right, so once you’ve done all that, we're

1:12:18 actually done here inside of the command line.

1:12:20 Now, here’s the thing.

1:12:22 If you want, you can actually write out all of your SQL commands.

1:12:25 You can do everything that I’m going to be

1:12:28 doing in this course here inside of this terminal.

1:12:30 So, you can interact with the MySQL

1:12:33 database server just from here inside the terminal.

1:12:36 You can write SQL.

1:12:37 You can give it SQL commands.

1:12:38 You can do all sorts of stuff like that.

1:12:40 In our case though, I’m actually going to be

1:12:42 using another separate program in order to do that.

1:12:45 And this environment, this terminal environment is not a very good

1:12:49 environment for like visualizing things and seeing things.

1:12:52 So, there’s actually another program called PopSQL which I’m going to use.

1:12:56 I’m going to show you guys how to download that right now.

1:12:58 But just know that this part is optional.

1:13:00 So, if you want, you can do everything from inside of the terminal.

1:13:03 So, I’m going to open up my web browser again.

1:13:06 We're going to come back up here.

1:13:07 And I’m just going to do a Google search for PopSQL.

1:13:10 And this page should pop up It’s PopSQL.io.

1:13:14 So, basically what this is, is it’s a program that we can use to write SQL

1:13:20 queries and write SQL code in order to do different things.

1:13:24 And PopSQL actually has some really cool features.

1:13:26 It’s kind of like Google Docs but for writing SQL queries.

1:13:29 In our case though, we're just going to use it to write out our SQL.

1:13:32 So, over here there should be a button to download it for MAC.

1:13:35 So, I’m just going to click that.

1:13:36 And then it’s going to go ahead and start downloading for us.

1:13:39 Once that’s downloaded,

1:13:40 then I’m just going to open it up And we'll be able to run the installer.

1:13:44 See, over here all we have to do is just drag this over

1:13:47 here to the applications folder and then it’s going to be on our computer.

1:13:50 So, now what we want to do is we're going to open that up.

1:13:53 So, I’m just going to go over here to applications.

1:13:56 And we'll scroll down to PopSQL.

1:13:59 And we're going to open this program up.

1:14:01 So, in order to use PopSQL you're actually going to have sign in.

1:14:04 So, you can just sign in with a Google account or with an email address.

1:14:07 I’m going to go ahead and sign in.

1:14:09 So, you can see it logged me in.

1:14:11 It says, “Welcome, Mike!” So, now we'll go ahead and return to the app.

1:14:14 So, now that we're logged in, what it’s going to ask

1:14:17 us to do right away is connect to a database.

1:14:19 So, basically what this application is going to do is it’s going

1:14:23 to connect to that MySQL database server that we have set up.

1:14:28 So, what we can do over here is just give this a nickname.

1:14:30 So, I’m just going to call this Draft.

1:14:32 And it’s asking us for the type.

1:14:34 So, over here if you scroll down you'll see the first option is my MySQL.

1:14:38 So, we'll just click MySQL.

1:14:39 Now, it’s asking us for a host name.

1:14:41 And the host name is basically

1:14:43 the address where this database server is located.

1:14:47 So, in our case it’s just at localhost.

1:14:49 Just like that.

1:14:52 So, just type in localhost.

1:14:53 Leave the port number the same.

1:14:55 3306 is going to be the default port number.

1:14:57 And then the database we're going to connect to is named giraffe.

1:15:01 So, if you remember, like a minute ago we created that giraffe database.

1:15:05 That’s what you want to put in here.

1:15:06 So, if you named it something other than giraffe, put that name in here.

1:15:09 And then we need a username.

1:15:11 So, the username is going to be root.

1:15:12 And then the password which they're asking for down here

1:15:15 is going to be the password that you set up.

1:15:18 So, in my case, it was just password like that.

1:15:20 And then I’m just going to click Connect.

1:15:23 And this is going to go ahead and connect us to the database.

1:15:26 So, at this point we installed MySQL.

1:15:29 We set up our first database.

1:15:31 And we connected everything with PopSQL.

1:15:33 And so, now we are able to start using PopSQL.

1:15:36 We can start learning all sorts of SQL stuff.

1:15:38 And it’s going to be awesome.

1:15:40 But keep in mind, if you don’t want to use PopSQL,

1:15:42 which I honestly would recommend using it.

1:15:44 You can do everything from inside the terminal here.

1:15:48 [Creating Tables] In this tutorial I’m going

1:15:53 to talk to you guys about datatypes.

1:15:55 We're going to talk about creating tables.

1:15:57 We're also going to talk about altering and deleting tables.

1:16:00 So, in the last tutorial we set everything up.

1:16:02 So, we got MySQL set up.

1:16:04 We also downloaded this program PopSQL which is basically just a text editor

1:16:08 that we can use to write all of our SQL code and all that stuff.

1:16:12 So, what I want to do in this tutorial

1:16:15 is show you guys how to create tables, right?

1:16:17 So, we created a database.

1:16:19 In our case, we created a database named giraffe in the last tutorial.

1:16:22 And now what I want to do

1:16:24 is start populating that database with different tables.

1:16:27 In other words, I want to start defining our database schema.

1:16:30 Now whenever you're working with a relational database management system,

1:16:34 your first step is always to create tables.

1:16:37 So, before we can start inserting any information,

1:16:39 before we can start querying for information,

1:16:42 we actually have to create the physical tables inside of our database.

1:16:46 And we can do that by defining the tables layout.

1:16:49 And so, what we want to do is we basically

1:16:52 want to use a SQL command which is called Create Table.

1:16:55 And then inside of that command we can pass in all the different

1:16:59 attributes or all the different columns that we want our table to have.

1:17:03 And so, the first thing I want to show you guys is

1:17:06 the different types of data that we can store inside of our database.

1:17:10 So, over here I actually just have a little list.

1:17:13 And I’m just going to paste it in here.

1:17:15 So, these are all of the basic data types that we're going to be using in SQL.

1:17:19 Now, these aren’t all of the datatypes.

1:17:22 There are actually tons of datatypes.

1:17:24 And depending on the relational database management system that you're using,

1:17:27 a lot of them have different datatypes to do different things.

1:17:31 I would say that these six datatypes right

1:17:34 here make up like the core SQL datatypes.

1:17:38 Like these are probably the most common datatypes that you're going to see.

1:17:41 But just keep in mind that there are a few others.

1:17:43 Now, we're using the MySQL database.

1:17:45 And all of these datatypes are going to be

1:17:48 able to be used in the MySQL database management system.

1:17:50 And all of these are going to be used,

1:17:52 for the most part, in any relational database management system.

1:17:54 But like I said, specific database management systems will allow you

1:18:00 to use different datatypes depending on how they want to implement things.

1:18:04 So, let’s go through these different datatypes and I’ll

1:18:06 kind of talk you guys through how to use them.

1:18:08 So, INT.

1:18:09 This is actually going to refer to an integer.

1:18:11 So, anytime you see INT just like that, I-N-T,

1:18:14 that basically means any whole number, right?

1:18:16 So, this is any sort of whole number.

1:18:18 But it can’t have decimal places after it.

1:18:20 If you want to be able to store decimals, you can use this decimal datatype.

1:18:23 And the decimal datatype will allow you to store decimals.

1:18:26 And you'll see, we have this parenthesis here after decimal.

1:18:30 And I have M and N in here.

1:18:32 Now these are actually both going to end up being numbers.

1:18:35 So, M is going to be the total number

1:18:37 of digits that you want to store for this number.

1:18:39 And N is going to be the number of digits

1:18:42 that you want to store after the decimal point.

1:18:44 So, when we're working with databases you have

1:18:47 to be very specific about the different information.

1:18:50 And specifically, the amount of digits that you want to store for a number.

1:18:54 So, what we could do is I could say like 10 here and I could put a 4 over here.

1:18:58 And what this means is we want to store a decimal number

1:19:01 with 10 total digits and 4 of those digits coming after the decimal place.

1:19:06 So, you can specify, you know,

1:19:08 depending on how accurate you want these numbers to be stored,

1:19:12 you can modify those numbers.

1:19:13 Down here we have VARCHAR.

1:19:15 And this actually stands for Variable Char or Variable Character.

1:19:19 This is basically a way that we can store a string of text.

1:19:22 So, VARCHAR, we have these parenthesis after here.

1:19:25 And you can put a number in here.

1:19:26 So, if I put a 1 in here,

1:19:29 then this is going to store a string of text, length 1.

1:19:32 If I put (100) in here,

1:19:34 this is going to store a string of text with 100 characters.

1:19:38 So, that means that the maximum amount of characters

1:19:40 that you can store inside of a VARCHAR(100) would be 100.

1:19:45 Down here we have BLOB which stands for Binary Large Object.

1:19:49 And this is basically just a structure

1:19:52 that can store large amounts of binary data.

1:19:55 So, if you have– a lot of people will use these for like images or files.

1:19:58 Like you can store those inside of a BLOB.

1:20:01 And they'll be able to be stored in the database.

1:20:04 We also have DATE.

1:20:06 So, DATE will be like a specific date and time.

1:20:08 And you can see we format a date like YYYY-MM-DD.

1:20:13 So, this would be the year, the 2-digit month, and then the 2-digit day.

1:20:17 You can also have a TIMESTAMP, which is similar to date,

1:20:20 but it’s generally used for recording like when things happen.

1:20:23 So, you can record like when an item

1:20:26 got inserted into the database or something like that.

1:20:28 So, over here it’s just YYYY-MM-DD and then we have the hours,

1:20:34 minutes, and the seconds.

1:20:36 So, those are all, like I said,

1:20:37 the main datatypes that you're going to be using.

1:20:39 But these aren’t all the datatypes.

1:20:41 So, depending on the database management system that you're using,

1:20:43 you want to check to see what specific datatypes they offer.

1:20:47 But like I said, for the most part, these should work in just about any system.

1:20:50 So, now I want to talk to you guys about creating database tables.

1:20:54 So, what we can actually do is we can

1:20:56 create tables and we're going to have use those datatypes

1:20:59 to tell the relational database management system what types of data

1:21:03 we want to store in each column in the table.

1:21:05 So, in order to create a table, we're actually going to type out some SQL.

1:21:09 Now, I’m just going to type out CREATE TABLE.

1:21:13 And these are two SQL reserve words.

1:21:16 CREATE and TABLE.

1:21:18 And you'll notice that I typed them in all capital letters.

1:21:21 Now, this is actually a pretty common convention when writing SQL queries.

1:21:26 A lot of people will write these reserve words in SQL in all capitals.

1:21:31 But you don’t have to.

1:21:32 If I wanted, I could write create table just like that.

1:21:35 And you’ll see it’s getting highlighted the same way.

1:21:37 The reason that people write these in all caps is because then it’s

1:21:41 easy to distinguish the SQL from any other text that we might be writing.

1:21:46 So, I would say for the most part,

1:21:48 just stick with making these all uppercase and you should be fine.

1:21:51 So, I’m going to say CREATE TABLE.

1:21:53 And then I want to type in the name of the table that I want to create.

1:21:56 So, what we're going to do is we're going to actually create a database table.

1:21:59 So, I actually have a database table set up over here.

1:22:01 You'll see it’s a student table.

1:22:04 And it’s storing just a list of students

1:22:06 for like maybe in a college or university.

1:22:09 So, there’s three things that we're storing about this student.

1:22:12 The student’s ID, the student’s name, and the student’s major.

1:22:15 So, we're storing all of this information about this student.

1:22:19 So, what I can do now is I can

1:22:22 actually create this exact table inside of my database.

1:22:25 So, I can say CREATE TABLE.

1:22:26 We'll call it student.

1:22:28 And you'll notice that I made this lowercase.

1:22:30 So, this isn’t an SQL reserve word, so I’m not going to make it uppercase.

1:22:35 And then what I can do is I can

1:22:37 just make an open and closes parenthesis and a semicolon.

1:22:39 Now, any command that you write in SQL is always going to end with a semicolon.

1:22:44 And if you're just starting out,

1:22:45 you might forget to put that semicolon in there,

1:22:48 but you want to make sure that you always

1:22:49 put it in there otherwise it’s not valid SQL.

1:22:51 So, I’m just going to click Enter.

1:22:52 And I’m going to down here in-between these parenthesis.

1:22:54 And what I’m going to is I’m going to start

1:22:57 defining the different columns or the different attributes on this table.

1:23:01 So, what we can do is we can define a column name and then a column datatype.

1:23:06 So, I can come over here and I can say the first attribute.

1:23:10 So, in my case, the first attribute for this table is going to be student ID.

1:23:14 And you'll also notice that student ID is a primary key.

1:23:18 So, on this table, student ID is the primary key.

1:23:21 Meaning it’s the column on the table

1:23:24 which will uniquely identify the specific row.

1:23:26 So, I'm just going to call this student_id.

1:23:29 And now I need to give this a datatype.

1:23:32 So, I’m going to give this a datatype of INT

1:23:35 because all of these ID’s as you can see, are integers, right?

1:23:38 They’re just integer numbers.

1:23:40 Now because this is going to be the primary key for this table,

1:23:44 I need to denote that.

1:23:46 So, over here I can just say PRIMARY KEY, just like that.

1:23:50 And what that’s going to do is it’s going to tell MySQL

1:23:52 that this is going to be the primary key for the table.

1:23:54 The next thing we want to store is the student’s name.

1:23:57 So, I’m just going to say name.

1:23:59 And the name is actually going to be a string.

1:24:00 So, this could be like Jack or Tommy or Kara, right?

1:24:04 It’s a name that we're storing about this table.

1:24:07 So, over here I’m going to make this a VARCHAR.

1:24:10 And then I’m going to make an open and closed parenthesis.

1:24:12 Now, remember, with the VARCHAR datatype we have to tell MySQL

1:24:16 how many characters we want this to be able to store.

1:24:19 So, with someone’s name,

1:24:20 what you want to do is you basically just want to think like

1:24:23 how many characters do we really want to allocate to storing someone’s name?

1:24:28 Because here’s the thing,

1:24:29 if I allocated like 1000 characters for someone’s name,

1:24:33 will in reality, normal people don’t have a name with 1000 characters, right?

1:24:38 I mean maybe your name would be 20 characters or 30 if you're really pushing it,

1:24:42 but 1000 is just totally out of the bounds of reality.

1:24:46 And you have to think if you're storing like millions of names,

1:24:49 potentially, you know,

1:24:50 allocating 1000 characters to each name when you only need 20 is

1:24:55 going to take up a lot of extra space in your database.

1:24:57 So, I think what we should do is maybe say like 20.

1:25:00 So, let’s say that the maximum length of someone’s name

1:25:02 we want to store is going to be 20 characters.

1:25:05 And really, you know,

1:25:06 depending on the domain of the application that you're building,

1:25:09 that’s going to be different.

1:25:11 But in my case, let’s just say it’s 20.

1:25:13 And then finally we wanted to store the student’s major.

1:25:15 So, I can say over here, major.

1:25:17 And this is also going to be a VARCHAR.

1:25:20 So, why don’t we also allocate 20 characters to their major.

1:25:23 And that should be enough.

1:25:24 So, you'll notice that I’m defining each of the attributes,

1:25:27 each of the columns on the table.

1:25:29 And then I’m putting a comma.

1:25:30 And then I’m defining the next column.

1:25:33 And then I’m putting a comma.

1:25:35 And then the final column.

1:25:36 So, this right here, this CREATE TABLE statement is basically going

1:25:39 to create this table over here for us, right?

1:25:42 We have the primary key, which is an INT.

1:25:44 And we have a name and a major which are strings.

1:25:47 So, that’s going to go ahead and do that for us.

1:25:49 Now, from inside PopSQL,

1:25:51 what’s really cool is we can just click on this query right here.

1:25:54 Click on this SQL statement.

1:25:56 And I can come over here and click Run.

1:25:59 And what this will do is it’ll automatically run this SQL on our MySQL server.

1:26:03 So, I'm going to click Run.

1:26:05 And you'll see over here we get a success message that says Rows affected: 0.

1:26:09 So, that actually was able to create that new table in our database.

1:26:15 So, that is basically how we could create a table.

1:26:18 And now we have this table stored in our database.

1:26:21 I want to show you guys another thing we can do.

1:26:22 So, over here I’m defining the primary key.

1:26:25 So, student_id was the primary key, right?

1:26:28 It’s the one column that’s going to uniquely identify each row.

1:26:31 And if you want, you can put primary key over here after like student_id.

1:26:36 Or what you can do is you can get

1:26:38 rid of this and you can define this down below.

1:26:41 So, instead of defining the primary key up here next to student_id,

1:26:45 I can come down here and I could change it.

1:26:47 So, I could say now PRIMARY KEY,

1:26:49 and then I can make an open and closed parenthesis.

1:26:52 And in here, I could put the name

1:26:53 of the column that I want to be the primary key.

1:26:55 So, in my case, I’ll just say student_id.

1:26:57 So, now what this is system is that the primary key is going to be student_id.

1:27:02 So, this is a equivalent to what I was doing up here.

1:27:04 It’s just another way that you can do it.

1:27:08 And so, now we already have our table created,

1:27:10 so I’m not going to go ahead and create it again.

1:27:12 All right, so now that we have our student table created,

1:27:14 let’s go ahead and make sure they created correctly.

1:27:16 So, what I’m going to do is I’m going to come

1:27:18 down here and I’m going to type out another MySQL command.

1:27:21 So, I’m just going to type out DESCRIBE.

1:27:23 And then I’m going to type in the name of the table.

1:27:25 So, in our case it’s going to be student.

1:27:27 And you'll notice with PopSQL, if I click over here on this query,

1:27:31 it gets highlighted in that blue color.

1:27:33 And if I click down here on this query, it gets highlighted.

1:27:36 So, what you can do with PopSQL is you can have like a query up here.

1:27:39 And you can run that query or you can click on this query down here and run it.

1:27:43 So, I’m going to click on DESCRIBE student.

1:27:45 And then I’m going click Run.

1:27:46 So, you'll see that it’s actually describing the table that we just created.

1:27:49 So, there’s a list of all these different fields.

1:27:51 We have student_id, name, and major.

1:27:53 It’s telling us the datatypes that we're storing.

1:27:55 So, like varchar(20).

1:27:57 And then it’s telling us a bunch of other

1:27:59 information which we're going to get into later.

1:28:01 So, what this is, it basically described everything about this table.

1:28:05 So, now what I want to show you guys how to do is delete and modify a table.

1:28:09 So, now that we've created a table and we saw that it got created correctly,

1:28:13 if you wanted, you could delete the table.

1:28:15 So, what I could do is I can come down here and type

1:28:18 out DROP TABLE and I can just type the name of the table.

1:28:20 So, in our case, it’s the student table, and a semicolon.

1:28:23 And now I’m going to click on here and I’ll click run.

1:28:25 And what this is going to do is it’s going to drop that table.

1:28:28 So, now if I came up here and I click on describe student,

1:28:31 in other words, I’m saying I want to execute this command.

1:28:33 And I click run, you'll see that it says “NO_SUCH_TABLE”.

1:28:36 Table ‘giraffe.student’ doesn’t exist because remember, we just dropped it.

1:28:42 So, what we can do, is we can actually create again.

1:28:45 So, I’m going to click on this CREATE TABLE query and I’ll click Run.

1:28:48 And that will go ahead and create it.

1:28:50 So, now if we click DESCRIBE student,

1:28:52 you’ll see we get the table back because we created it again.

1:28:55 So, you can drop the table.

1:28:56 You can also modify the table.

1:28:57 So, let’s say that after the table was created,

1:29:00 you wanted to give it another column.

1:29:02 What I could do is I could add another column.

1:29:05 So, I could say ALTER TABLE.

1:29:07 And then say student, because that’s the name of the table.

1:29:11 And then I could say ADD.

1:29:13 So, what this is going to do is it’s

1:29:14 going to add an extra column onto the table.

1:29:16 So, why don’t we add a column for GPA.

1:29:17 So we could store a student’s GPA and that’s going to be a decimal.

1:29:22 So, I’m just going to click a semicolon.

1:29:24 And this is going to go ahead and end off this command.

1:29:28 Now, with decimal, remember I showed you that you

1:29:31 could put numbers after here, like 1 and 2.

1:29:34 So, what we're going to do is generally a GPA would be like 3.

1:29:39 something something.

1:29:39 Or 4.

1:29:40 something something.

1:29:41 So, we're going to have this be three total digits

1:29:44 with two of the digits occurring after the decimal point.

1:29:47 So, now I can go ahead and run

1:29:49 this command and you'll see it says success down here.

1:29:51 So, if we were to describe the student table again,

1:29:54 if I just click on this and click Run,

1:29:57 now you'll see we have that extra column in here, GPA.

1:30:00 And it’s a decimal (3,2).

1:30:02 So, that is how we can drop a table

1:30:05 and that’s also how we can alter a specific column.

1:30:08 If you wanted, you could also drop a specific column.

1:30:11 So, I could say ALTER TABLE student.

1:30:14 And over here, I can just say DROP COLUMN

1:30:17 and just type in the name of the column.

1:30:20 So, what this will do is it’ll drop that GPA column from the table.

1:30:23 So, let’s go ahead and run.

1:30:24 We get this success message.

1:30:25 Now, if I describe the table and I clicked run,

1:30:29 you'll see that the GPA field went away.

1:30:32 So, I actually dropped that field.

1:30:33 So, you can create tables.

1:30:34 We can add columns onto tables.

1:30:35 We can remove columns from tables.

1:30:38 And then we can just remove the table altogether.

1:30:40 So, hopefully that show you guys some

1:30:42 of the basics of doing those operations on tables.

1:30:46 Now, obviously whenever you're creating your database,

1:30:48 the first thing that you want to do is define your database schema.

1:30:51 In other words, you want to create all the different tables

1:30:54 and then you can start inserting data into the tables as such.

1:30:57 So, in the next tutorial I'm going to talk

1:30:59 to you guys about inserting data into tables,

1:31:01 so we'll actually insert some student information into our student table.

1:31:04 [Inserting Data] In this tutorial,

1:31:09 I’m going to talk to you guys about inserting data into a database tables.

1:31:13 So, in the last tutorial we set up our first table which is this student table.

1:31:17 And you can see it up here.

1:31:18 And then down here we described the student table.

1:31:21 So, it has student_id, name, major, etc.

1:31:24 So, now that we have our table set up,

1:31:27 we want to actually start inputting information, right?

1:31:30 We want to start storing some different data.

1:31:32 So, I’m going to show you guys how to do that.

1:31:33 I’m going to leave this CREATE TABLE statement up here because

1:31:36 we're actually going to come back to that in a sec.

1:31:37 But down here, I’m going to show you guys how to insert values.

1:31:40 So, in order to insert a piece of information into a table,

1:31:44 we just want to type out INSERT INTO.

1:31:46 And now I want to type out the name of the table.

1:31:48 So, in our case, we have this student table.

1:31:51 And then I just want to type out VALUES.

1:31:54 So, basically, we're saying insert into the student table the values.

1:31:58 And then over here I’m going to make

1:32:00 an open and closed parenthesis and a semicolon.

1:32:02 So, now this is actually a full statement.

1:32:05 So, remember, we need to end this off with a semicolon.

1:32:07 Inside of these parenthesis I can actually

1:32:10 put the information that I want to store.

1:32:12 So, the way that the table is set up,

1:32:16 we have the student_id first, then the name, then the major.

1:32:20 And that's the order that we want to put this information into the table.

1:32:24 So, I need to put this student_id first, the name, and then the major.

1:32:28 So, over here I’m just going to type in the student ID.

1:32:31 So, we're going to be actually entering

1:32:33 in the same information that we have over here.

1:32:35 So, this is kind of like our little template.

1:32:38 So, we have the first student, Jack,

1:32:40 who’s a biology major and his student ID is 1.

1:32:42 And you can see we just keep going from there.

1:32:44 So, I’m going to insert, you know, roughly the same information.

1:32:47 So, the first thing we want to do is put the student ID,

1:32:50 so that’s going to be 1.

1:32:51 And you'll notice that with an integer I can just type out the number 1.

1:32:55 And then over here we're going to put in a VARCHAR(20).

1:32:58 So, basically this is going to be a string.

1:33:00 Anytime we're typing out strings in SQL,

1:33:03 we're going to make these double quotation marks.

1:33:05 And then in here we can type out the string.

1:33:08 So, the student’s name is going to be Jack.

1:33:10 And then another comma and we're going to put in his major which is biology.

1:33:15 So, we have Jack with the student ID of 1.

1:33:18 And his major is Biology.

1:33:19 So, insert into student values 1, Jack, Biology,

1:33:24 this is going to go ahead and insert this value into the database table.

1:33:29 So, now when I click Run– and you'll notice all I have

1:33:31 to do is just click on this query and then click Run.

1:33:34 It says down here Success.

1:33:36 Rows affected 1.

1:33:38 So, we affected one row in the student table.

1:33:41 So, now what we can do is we can

1:33:44 actually see how this got inserted into the table.

1:33:46 So, what I’m going to do is actually below here,

1:33:50 I’m just going to type out select asterisk from student.

1:33:54 And this is actually a very simple SQL command.

1:33:57 What this is going to do is it’s going

1:33:59 to grab all the information from the student table.

1:34:01 And we're actually going to spend

1:34:02 a whole tutorial just talking about using SELECT.

1:34:05 But for now, just know you can type SELECT* FROM student.

1:34:08 And this is going to go ahead and give

1:34:10 us all the information from the student table.

1:34:12 So, I’m just going to click on this query here and I’ll click Run.

1:34:14 And you'll see down here we get this table.

1:34:18 So, it says student_id 1.

1:34:20 Name, Jack.

1:34:21 Major, Biology.

1:34:22 So, so far in the student table,

1:34:24 we have 1 student which is Jack and he’s a biology major.

1:34:29 So, why don’t we actually insert another student.

1:34:31 So, I’m going to keep this same line of code

1:34:33 except now I’m going to insert a student with ID 2.

1:34:35 And this student’s name is going to be Kate.

1:34:38 And she is going to be a sociology major.

1:34:42 So, we have Kate who’s ID is 2, sociology.

1:34:45 So, if I click on this command here and I click Run,

1:34:48 this is going to go ahead and insert Kate into the database.

1:34:52 So, now I’m going to click on Select All from students,

1:34:55 or Select* from students.

1:34:57 And we'll click Run.

1:34:58 And now you can see we're getting all the information from the student table.

1:35:01 So, we have Jack and then we also have Kate.

1:35:04 So, we added in– so far, we've inserted in 2 people into there.

1:35:09 So, what you can do is you can basically use this same

1:35:12 format in order to keep inserting students into the student table, right?

1:35:15 As long as you have their ID and you have their name and their major,

1:35:19 then everything is good.

1:35:20 But I want to show you guys another thing we can do.

1:35:23 So, let’s say there’s a situation where we

1:35:25 had a student who didn’t have a major.

1:35:27 Maybe they just had no major or we didn’t know what

1:35:30 their major was so we couldn't insert it into the database.

1:35:33 Well, in a situation like that we

1:35:35 could actually modify this statement a little bit.

1:35:37 So, I could say INSERT INTO student and then after

1:35:41 student here I can make an open and closed parenthesis.

1:35:44 And I can type out the names of the columns or the names

1:35:47 of the attributes that I have or that I want to insert.

1:35:50 So, I can say like student_id and then name.

1:35:54 So, you'll notice that I’m not including major in here.

1:35:57 And what that means is that over here in this value section,

1:36:00 we can include the student ID and the name.

1:36:04 But we don’t have to include the major.

1:36:06 So, like if I don’t know what the major is,

1:36:08 like I can’t obviously put anything there.

1:36:10 So, by saying student open and closed parenthesis

1:36:13 and then specifying the 2 attributes that I do have,

1:36:16 I can then just put them over here and I won’t get an error by not including it.

1:36:20 So, now we can change these values.

1:36:22 So, we'll change the primary key to three.

1:36:24 And then let’s see who the next student in our database is.

1:36:27 So, it looks like it’s Claire.

1:36:28 So, we'll have Claire.

1:36:31 And then we can go ahead and run this query.

1:36:33 And you'll see rows affected, 1.

1:36:35 So, we added in another row.

1:36:37 But if I was to select all this data– so I’m

1:36:39 going to click Select again and I’m just going to click Run.

1:36:41 Now you'll see that Claire’s student ID is 3.

1:36:44 Her name is Claire, but here major is NULL.

1:36:47 So, we didn’t actually enter in a major for Claire.

1:36:50 And therefore, inside the major field, she’s getting a value of NULL.

1:36:55 And that’s actually pretty common.

1:36:57 So, that’s basically going to be what happens when we don’t enter in something.

1:37:01 And so, what you can do is you can specify what pieces

1:37:04 of information you want to insert into the table by specifying them over here.

1:37:08 And then here in the value section,

1:37:10 you just have to add those pieces of information.

1:37:13 So, that’s two ways that you can insert something into a database table.

1:37:17 And it’s important to notice that you can’t insert duplicate entries.

1:37:21 So, if I was to try to insert this again,

1:37:23 you'll see that the primary key is still 3 which means I’m not going to be able

1:37:28 to enter this record in because a record

1:37:30 with that primary key already exists inside the table.

1:37:33 So, if I was to click Run again, you'll see I'm getting this error.

1:37:37 It says Duplicate Entry ‘3’ for key ‘PRIMARY’.

1:37:40 Basically, what that means is we have

1:37:42 a duplicate key and it’s not going to work.

1:37:45 But if I was to change this to 4.

1:37:47 So, instead of having an ID of 3, it’s 4.

1:37:50 Now, this is going to work.

1:37:52 And we'll be able to see we have two entries with Claire,

1:37:56 but they have different student ID’s so we're all good to go.

1:37:59 So, that is the basics of inserting stuff into a table.

1:38:02 And really, those two techniques are really useful.

1:38:05 So, that’s kind of the basics of inserting into a table.

1:38:08 Now, there’s a lot of more things we can get into with this.

1:38:10 And in the next tutorial,

1:38:11 I’m going to show you guys some other stuff that we can

1:38:13 do in order to make inserting into the tables a lot easier.

1:38:16 [C] In this tutorial I’m going to talk to you

1:38:22 guys some more about inserting information into a database.

1:38:25 So, in the last tutorial we kind

1:38:27 of looked at the basics of inserting information.

1:38:29 So, we have our table here.

1:38:30 It’s the student table.

1:38:31 And down here we kind of looked at how we can insert information.

1:38:35 So, we can insert into the name of the table, and then values.

1:38:38 And we can pass in the values.

1:38:40 We can also say insert into the name of the table.

1:38:43 And then over here we could specify what information we want to insert.

1:38:47 And then here in the values we just insert that information.

1:38:51 So, we've kind of been using this photo over here of this like,

1:38:55 you know, student database.

1:38:56 And I actually just went ahead and wrote out all

1:38:58 the code for inserting all of these different students into the database.

1:39:02 So, you'll see we're inserting Jack who’s a biology major.

1:39:06 Kate, sociology.

1:39:07 Claire, who doesn’t have a major.

1:39:09 There’s another one name Jack who’s also a biology major.

1:39:12 But you'll notice that it has a different primary key than this guy up here.

1:39:16 And then there’s Mike who’s computer science.

1:39:19 So, these instructions will actually insert

1:39:21 each of those students into our database.

1:39:25 And you'll see over here when I run this Select* FROM command.

1:39:30 So, with PopSQL, if you just click on the SQL statement and then you click Run,

1:39:34 it’ll go ahead and run it for you.

1:39:36 So, you'll see down here we get all that information.

1:39:38 So, there database is set up.

1:39:39 And that’s sort of like the general use case, right?

1:39:42 That’s like we set up our database table

1:39:44 and we're just straight-up inserting information into it.

1:39:46 But there’s some other stuff we can do.

1:39:48 Namely, we can actually set up our database table

1:39:51 in order to make it easier for us to insert elements

1:39:55 or to control the type of information or the type

1:39:59 of rows that we can insert into the database table.

1:40:02 So, what I’m actually going to do is I’m actually just going to drop this table.

1:40:06 So, I’m just going to say DROP TABLE student.

1:40:09 And we're actually just going to start over.

1:40:11 So, again, with PopSQL all you have to do is click on the SQL command.

1:40:15 Like I can click on DROP TABLE right here.

1:40:17 And then when I click Run, it’ll go ahead and drop it.

1:40:20 So, now if I tried to SELECT ALL from the student table,

1:40:24 you'll see it says there’s no such table.

1:40:26 So, let’s go ahead and I’m going to show you guys how we can create

1:40:30 this student table in order to make it

1:40:32 a little bit easier for us to insert stuff.

1:40:35 So, the first thing I’m going to show you guys is something called NOT NULL.

1:40:39 And NOT NULL will basically allow us to define

1:40:41 that a particular column in the table cannot be NULL.

1:40:44 So, let’s say that when we're storing our students,

1:40:47 we don’t want the student to be able to have a NULL name.

1:40:49 And NULL is basically just a value that represents no value.

1:40:54 So, if something is NULL it means that it doesn’t have a value.

1:40:57 So, let’s say that we always want the student to have a name no matter what.

1:41:00 Well, after I same name VARCHAR(20) what I could do is I can say NOT NULL.

1:41:06 And I'm just putting that right after here.

1:41:08 What that means is this column can’t be null.

1:41:10 So, if you insert a row into the table, you can’t insert NULL for a name.

1:41:14 I could also do something else which would be UNIQUE.

1:41:19 So, UNIQUE is another keyword which we can use.

1:41:21 And basically, what this is going to mean is that the major

1:41:25 field has to be unique for each row in this table.

1:41:27 So, if somebody else or another row

1:41:30 in the table has the same major as another entry, then that will get rejected.

1:41:36 So, NOT NULL and UNIQUE are actually pretty useful.

1:41:39 So, now what we can do is we can actually create this table.

1:41:41 So, I’m just going to click on this query.

1:41:42 And I’m going to click Run.

1:41:44 And you'll see that we get a success message.

1:41:46 So, we were able to create the table.

1:41:47 And so, now why don’t we start populating this table with information?

1:41:50 So, I’ll come over here and I’ll click insert into student– Jack, biology.

1:41:54 So, we're going to insert Jack.

1:41:56 And then we'll select the table and you'll see that Jack got inserted just fine.

1:42:00 Let’s click on this second one.

1:42:02 Kate, sociology.

1:42:03 So, we'll run this.

1:42:04 And again, we'll just select all from the table.

1:42:07 You'll see both of those got inserted.

1:42:09 So, now we get down here into this next one.

1:42:11 So, insert into student.

1:42:13 So, for Claire, we're inserting in student_id and name.

1:42:16 But let’s actually switch this up.

1:42:18 So, why don’t we get rid of this and instead

1:42:21 of saying this, we'll give her a major.

1:42:23 So, we're going to say that Claire’s a chemistry major.

1:42:26 But we're actually going to get rid of this name.

1:42:27 So, instead of Claire, I’m just going to say NULL here.

1:42:31 And you'll notice up here, I said that the name cannot be NULL.

1:42:35 So, when I created this table, I specified that the name field cannot be NULL.

1:42:40 So, if I come down here and I try to insert a student with no name,

1:42:44 with a name that’s set to NULL and click Run, you'll see that we get this error.

1:42:49 And it says, “You have an error in your SQL.” And basically,

1:42:52 what that means is we can’t insert in a value here

1:42:56 that’s NULL because we specified that it can’t be NULL up here.

1:42:59 So, you’re actually not going to be able to do that.

1:43:02 Now, we also, over here on this major field,

1:43:05 we defined that it has to be unique.

1:43:07 So, if I come down here and I try to execute this line, so you’ll see,

1:43:10 we're trying to insert this person’s name as Jack and they're a biology major.

1:43:15 But we already defined one person that was a biology major up here, right?

1:43:19 It was this first one.

1:43:20 So, if I try to enter in another biology major,

1:43:23 it’s going to give me another error.

1:43:25 So, I’m going to click run.

1:43:26 And you'll see down here it says Duplicate entry ‘Biology’ for key ‘major’.

1:43:31 So, it yelled at us because we entered in a duplicate entry.

1:43:36 So, NOT NULL and UNIQUE are really good ways for you

1:43:40 to kind of control the data that gets stored on the table.

1:43:44 And actually, funny enough,

1:43:45 if you have a primary key– a primary key is actually just an attribute

1:43:50 or a column on the table that is both NOT NULL and UNIQUE.

1:43:54 So, a primary key is basically just something that’s NOT NULL and UNIQUE.

1:43:58 So, there’s a couple other ones.

1:43:59 We're actually going to drop this table again.

1:44:01 So, I’m just going to click DROP TABLE and we'll go ahead and drop the table.

1:44:03 And over here, I’m going to create the table again,

1:44:06 but we're going to give this some other stuff.

1:44:08 So, these are actually what we would call constraints.

1:44:10 So, I would say like a constraint would

1:44:12 be that it’s NOT NULL or that it’s UNIQUE.

1:44:15 But there’s a couple other constraints that we

1:44:16 can add that I want to show you guys.

1:44:18 So, let’s say that we wanted to set a default value.

1:44:21 So, let’s say that if somebody didn’t enter in a major,

1:44:24 we wanted to be able to give them a default major.

1:44:27 Well, I can actually say that something has a default.

1:44:31 So, I can come down here and I can say after major, DEFAULT.

1:44:34 And then inside of single quotes I can just

1:44:37 type in what I want the default to be.

1:44:40 So, in our case, if somebody doesn’t provide us with a major,

1:44:43 why don’t we just say that they’re ‘undecided’.

1:44:45 So, basically if the major field is left blank,

1:44:48 we'll say that the person is undecided.

1:44:50 So, then down here, why don’t we go ahead and insert

1:44:53 a student into the table that doesn’t have a major.

1:44:56 So, here we can just say INSERT INTO student and we'll

1:44:58 go ahead and specify that we're going to enter in the student_id.

1:45:01 And also the name.

1:45:03 But you'll notice that I’m not putting major in here.

1:45:06 Which means that we don’t have to give this a major.

1:45:08 So, now we'll get rid of Jack’s major.

1:45:09 And when I go ahead and run this, you'll see that we got a success message.

1:45:15 And if I was to select all the entries from the student,

1:45:18 it says student_id is 1.

1:45:19 His name is Jack and his major is undecided.

1:45:22 So, because I didn’t insert a major,

1:45:24 it’s going ahead and populating that major for us with the value

1:45:28 undecided because that was the default value that I placed over here.

1:45:32 So, that’s another really useful constraint that we can put on this.

1:45:35 All right, so there’s one more of these little

1:45:37 constraints that I want to show you guys.

1:45:38 And it’s actually really useful for working with primary keys.

1:45:42 So, you'll notice that the primary keys for all of these guys were like 1,

1:45:48 2, 3, 4, 5, etc., right?

1:45:50 I like manually went in and incremented all those primary keys.

1:45:54 But what you can actually do you can have the database do that for you.

1:45:57 So, I can come over here right here after students,

1:46:01 so remember, student_id is the primary key, right?

1:46:04 We defined that down here.

1:46:05 I can come ever here and say AUTO_INCREMENT.

1:46:10 And AUTO_INCREMENT is basically going to specify

1:46:12 that the data that gets inserted

1:46:14 into here is going to automatically incremented every time we add one in.

1:46:18 So, I could actually come down here and instead of inserting a student ID,

1:46:21 I can just leave it out.

1:46:23 So, I can just insert the name and the major.

1:46:27 And then I can add that information in here.

1:46:31 So, Jack is going to go ahead and study biology.

1:46:35 And actually, let me get rid of these.

1:46:37 So, we can kind of see how this works.

1:46:39 So, I can copy this for another one.

1:46:43 And we'll make this Kate and she’s studying sociology.

1:46:47 So, notice that I’m not actually inserting the student_id, right?

1:46:51 All I’m inserting is the name and the major.

1:46:53 But because we said that this table was going to be auto incremented.

1:46:56 In other words because we said that the student_id was going

1:46:59 to be auto incremented we don’t actually have to do that.

1:47:02 So, I can come down here and I can run this.

1:47:04 And actually, whoops.

1:47:05 I have to create the table again.

1:47:06 My bad.

1:47:07 So, we're going to create the table.

1:47:08 And I can insert in this first entry, Jack, who’s a biology major.

1:47:11 And I’ll run this.

1:47:13 And you'll see it says 1 row affected.

1:47:16 And then I’m also going to insert this next

1:47:18 one which is Kate who is studying sociology, so I’m just going to click Run.

1:47:21 And so, now I’ve inserted both of these students into the table.

1:47:23 And if I select all from the student table,

1:47:25 you'll see down here that we get both of these entries.

1:47:28 But Jack has an ID of 1 and Kate as an ID

1:47:30 of 2 even though I didn’t rigorously specify that, right?

1:47:33 So, I didn’t actually add in the ID’s for either of these guys,

1:47:37 and yet they still showed up down here in the table which was pretty useful.

1:47:42 So, that’s a great way for you

1:47:44 to just like automatically increment like something

1:47:46 like a primary key or really any other row in the table by using AUTO_INCREMENT.

1:47:50 So, those are just a couple little things

1:47:52 that you can do when you’re creating the table.

1:47:54 We would call those constraints.

1:47:56 So, you can add constraints onto the specific columns of the table

1:48:00 and that can control like what types of information can be put inside of there.

1:48:04 So, that’s– there’s a little bit more of an advanced

1:48:07 way for you guys to insert information into the database.

1:48:10 [Update& Delete] In this tutorial I’m going to talk to you

1:48:16 guys about updating and deleting rows inside of a database table.

1:48:19 So, in the last few tutorials we've looked

1:48:21 at creating database tables and inserting information into them.

1:48:24 But now once we have that information inside those tables,

1:48:28 I’m going to show you guys how to update it and delete it whenever you want.

1:48:32 So, we have here our student database.

1:48:35 And this is– or the student table and it’s

1:48:37 the table that we've been using throughout the course.

1:48:39 And so, down here you can see it’s just a bunch of students.

1:48:42 And they have names, majors, and ID's.

1:48:44 So, right now, these are all the students

1:48:46 that are stored inside of our database.

1:48:48 So, these are the database entries that we can work with.

1:48:51 The first thing I’ll show you guys show to do is update some of these entries.

1:48:54 And so, you know, let’s say that we wanted, you know, update something.

1:48:58 So, let’s say that we wanted to change the name

1:49:00 of one of the majors in our school, right?

1:49:02 So, let’s say I am the database administrator for my school.

1:49:06 And we decided that instead of calling the major biology,

1:49:10 we wanted to call it bio.

1:49:12 So, the official name for the major is no longer biology.

1:49:15 It’s just going to be bio.

1:49:17 Well, we could actually make that change inside the database.

1:49:20 So, I can use the special keyword called UPDATE.

1:49:22 And I can just type out the name of the table where I want to do the update.

1:49:26 So, in our case, it’s student.

0:00 And over here I can say UPDATE student

0:00 I want to set major equal to ‘Bio’, right?

1:49:27 But here’s the thing.

1:49:37 I don’t want to set the major equal to bio for all the students.

1:49:42 I only want to set the major equal to bio

1:49:44 for the students who have their major as biology.

1:49:46 So, over here we can specify that.

1:49:48 I can say WHERE.

1:49:50 And here I can put a condition.

1:49:51 So, I can say major is equal to ‘Biology’.

1:49:54 And I’m going to be semicolon.

1:49:56 So, this whole thing now is one SQL statement or query.

1:50:00 And so, what I'm doing is I’m updating the student table and I’m setting

1:50:04 the major column equal to ‘Bio’ where

1:50:07 the major column is already equal to ‘Biology’.

1:50:10 So, now if I was to go ahead and run this, you'll see it says Rows affected: 2.

1:50:14 So, we updated to of the rows in this database.

1:50:17 And if I was to select all of them and I’m going to click Run,

1:50:20 you'll see down here now for the two students who are biology majors,

1:50:23 it’s now saying that they’re bio majors, right?

1:50:27 So, it updated the database only in those places

1:50:30 where the student’s major was equal to ‘Biology’.

1:50:33 And that’s what we can do here.

1:50:35 So, this is just a very basic update statement,

1:50:37 but we can use this in order to all sorts of stuff.

1:50:40 So, let’s do another one.

1:50:41 We can do the same for computer science.

1:50:44 So, I could say UPDATE student.

1:50:45 SET major equal to ‘Comp sci’ WHERE major= ‘Computer Science’.

1:50:52 So, I’m basically abbreviating this major.

1:50:54 So, let’s go ahead and run this.

1:50:56 And again, I’m just going to SELECT ALL from the table.

1:50:58 You'll see down here, Mike,

1:51:00 who used to be a ‘Computer Science’ major is now a ‘Comp Sci’ major.

1:51:04 So, I was able to update that.

1:51:06 Another thing you can do is you can update specific things.

1:51:08 So, I can say like UPDATE student.

1:51:10 SET major equal to ‘Comp Sci’, WHERE.

1:51:13 And then over here I can do a different condition.

1:51:15 So, I can say like student_id is equal to 4.

1:51:19 So, now I’m updating the student’s major who has an ID of 4.

1:51:24 So, this is like a different type of query.

1:51:26 So, down here I’m running that.

1:51:28 And then I’m going to SELECT ALL.

1:51:30 And so, now you can see that Jack who used to be a biology

1:51:34 major is now a comp sci major because his student ID was 4.

1:51:37 So, you can get kind of creative, you know,

1:51:39 playing around with setting different

1:51:41 things and then checking different conditions.

1:51:43 You can also use more complex conditions down here.

1:51:47 So, down here we have bio majors and we have chemistry majors, right?

1:51:50 Well, let’s say that any student who is either a biology

1:51:53 or a chemistry major is now going to be a biochemistry major.

1:51:56 So, we're combining those two majors together.

1:51:59 Well, I could update that here.

1:52:01 So, I could say UPDATE student.

1:52:03 SET major equal to ‘Biochemistry’.

1:52:06 WHERE major is equal to ‘Bio’.

1:52:11 And over here we could say OR.

1:52:14 And then we could also say major is equal to ‘Chemistry’.

1:52:18 And this is going to give us some OR logic.

1:52:20 So, now if the student has a major of bio or if they have a major of chemistry,

1:52:24 we're going to set it equal to biochemistry.

1:52:26 So, we're basically combining those two majors together.

1:52:29 So, now I’ll click Run and you can see rows affected is 2.

1:52:33 And I’ll SELECT ALL from students.

1:52:35 And you can see now Jack and Claire– Jack used to be a bio major.

1:52:39 Claire used to be a chem major.

1:52:41 Are both now biochemistry majors.

1:52:43 So, you can use OR in order to check multiple things like that.

1:52:47 You can also set multiple things.

1:52:49 So, over here I’m actually going to get rid of this condition.

1:52:53 And I can say SET name equal to ‘Tom’.

1:52:57 And major equal to ‘undecided’.

1:53:06 WHERE student_id is equal to 1.

1:53:11 So, whoever has the student equal to 1 is going to have

1:53:14 their name set to Tom and their major set to undecided.

1:53:18 So, let’s go ahead and run this.

1:53:20 And you'll see it says 1 row affected.

1:53:22 So, let’s SELECT ALL from the student’s table.

1:53:24 And you'll see down here,

1:53:25 the student with the ID of 1 is now named Tom and their major is undecided.

1:53:29 So, you can change multiple columns within the same query like I did up here.

1:53:34 All right, so there’s one more thing we can do,

1:53:36 which we can actually get rid of this WHERE.

1:53:39 So, this is actually optional.

1:53:40 So, if I got rid of WHERE, I can then set something.

1:53:45 So, why don’t we set major equal to undecided.

1:53:47 So, if I get rid of that WHERE statement,

1:53:48 this is just going to apply to every single row in the table.

1:53:51 So, now when we run this, you'll see rows affected was 5.

1:53:53 And now when we SELECT ALL from the students table,

1:53:56 all of their majors are now undecided.

1:53:58 So, you can either select specific rows or groups of rows with that WHERE

1:54:03 or you can just do it to all of them by dropping the WHERE.

1:54:06 So, now I’m going to show you guys how we can delete rows from the table.

1:54:10 And it’s actually pretty similar to how we would update rows.

1:54:12 So, instead of updating a specific row or a group of rows,

1:54:15 I can actually delete a specific row or a group of rows.

1:54:18 So, it’s like I said, pretty similar to UPDATE.

1:54:21 I’m just going to type in DELETE FROM and then the name of the table.

1:54:25 So in our case it’s going to be student.

1:54:27 And you can actually just end this off right here.

1:54:30 I can put a semicolon here.

1:54:31 And this will go ahead and delete all of the rows inside of the table.

1:54:35 But why don’t we try to get more specific?

1:54:37 So, I’m going to try to delete a specific row.

1:54:39 I can say DELETE FROM student.

1:54:41 WHERE– and now I can specify a condition.

1:54:43 So, I can say like WHERE student_id is equal to 5.

1:54:48 So, what this will do is it’ll delete any students or any

1:54:52 rows from the table that have a student ID of 5.

1:54:55 So, down here we just have one like that.

1:54:58 So, Mike, who is an undecided major has an ID of 5.

1:55:01 So, if I was to run this.

1:55:03 Now you'll see it says rows affected, 1.

1:55:06 And we'll select all from students and Mike is no longer inside the table.

1:55:10 So, we actually deleted a specific row.

1:55:13 And just like update, you can get more fancy with these queries.

1:55:16 So, I could say like WHERE name is equal to Tom.

1:55:21 And you can also do an AND.

1:55:23 So, instead doing OR, we're doing an AND here.

1:55:26 And I can say like major is equal to ‘undecided’.

1:55:30 So, this will delete any students from the table who

1:55:33 have the name of Tom and who are an undecided major.

1:55:37 So, now when I run this query, you'll see– and I select all the students

1:55:40 again– that Tom got deleted from the table.

1:55:44 So, he’s no longer there.

1:55:45 So, just like we used WHERE in the UPDATE statements,

1:55:48 we can use WHERE with the DELETE statements to do all of that.

1:55:51 But if you wanted, you can just delete everything from the table.

1:55:54 So, now I’m just going to run this command.

1:55:56 And if I query the table, you'll see that nothing is there.

1:56:00 So, we deleted everything.

1:56:01 So, updating and deleting is extremely useful.

1:56:03 And you can use that WHERE statement in order to specify what specific

1:56:07 row you want to update and what specific row you want to delete.

1:56:10 [Basic Queries] In this tutorial I’m going to talk

1:56:16 to you guys about getting information from the database.

1:56:18 More specifically we're going to look

1:56:20 at the SELECT keyword and we're going to look

1:56:22 at the different ways that we can ask

1:56:25 the database management system to give us some information back.

1:56:28 So, one of the core tenets of interacting with the database

1:56:30 management system and using SQL is writing these little queries.

1:56:34 And a query is essentially just a block of SQL that’s designed

1:56:39 to ask the database management system for a particular piece of information.

1:56:43 And so, one of the things that we need to be

1:56:46 aware of is that when you’re keeping track of, you know,

1:56:48 huge amounts of information in a database,

1:56:50 you want to be able to grab specific information easily.

1:56:53 So, let’s say that I’m trying to, you know,

1:56:56 grab a bunch of students from our little student table right here.

1:56:59 Well, imagine that we had like a million

1:57:02 students stored inside of that table, right?

1:57:04 I might not want to just grab every single student.

1:57:07 I might want to just grab students who meet a certain condition

1:57:11 or students who have a certain major or students with a certain name.

1:57:14 And we can use SQL queries in order to specify those things.

1:57:18 So, instead of the relational database management system giving

1:57:21 us back all the entries in a specific table, instead it can just give us back

1:57:26 very specific entries which meet a certain condition.

1:57:28 So, we're going to talk a little bit about the basics of doing that stuff.

1:57:32 Now, this is actually a huge topic and it’s the topic that we're

1:57:34 going to be talking about for most of the rest of course.

1:57:36 So, this is going to kind of give you

1:57:39 guys an introduction into writing all of these little queries.

1:57:41 So, over here, you’ll see that I have this query here.

1:57:44 And it’s just SELECT* FROM student.

1:57:48 And actually, if you want, you can put this on two different lines.

1:57:50 A lot of people will do that.

1:57:51 So, this SELECT keyword is a very special word.

1:57:56 And this SELECT keyword is basically going to tell the relational

1:57:59 database management system that we want to get some information from it.

1:58:02 So, I can say SELECT.

1:58:04 And then right next to it I can specify what information I want to get.

1:58:07 And so far in this course we've been using this star or this asterisk.

1:58:12 And basically, the asterisk means that we want to grab all of the information.

1:58:15 But if we wanted, we could specify a specific column.

1:58:19 So, we can select specific columns of information that we want to get

1:58:25 back from the database management system

1:58:26 and then we can say FROM whichever table.

1:58:29 So, SELECT* FROM table could also be read

1:58:33 as select every column from the student table.

1:58:36 So, over here you'll see when I run this, we get this information down here.

1:58:40 So, we're getting all of the students in the table.

1:58:43 We're getting their student ID’s and their names and their majors.

1:58:47 If I wanted, I could change the columns up here.

1:58:49 So, I could say like name.

1:58:52 And now what this will do is it’ll

1:58:53 select all of the names from the student table.

1:58:56 So, if I was to run this query, you'll see down here we get just the names.

1:59:00 So, we have the name and then it’s Jake, Kate, Claire, Jack, and Mike.

1:59:04 So, these are all of the names that were stored inside of the database.

1:59:07 I could also include something else.

1:59:10 So, I could say name, major FROM student.

1:59:13 And then down here when I run this query,

1:59:15 you'll see we're getting the students, names, and the majors.

1:59:18 But we're not getting the student’s ID.

1:59:20 So, we're able to specify which specific columns we want to get back.

1:59:24 You can also pre-pen these with the name of the table.

1:59:27 So, I could say like student.name.

1:59:30 And student.major.

1:59:31 And sometimes people will do this just because student.name,

1:59:34 it’s clear which table the name is coming from.

1:59:37 And as we write more and more complex queries, that can come in handy more.

1:59:40 So, for the most part, I’m probably going to be writing them both ways,

1:59:44 just depending on the situation.

1:59:46 But you could do something like this.

1:59:48 We can also order the information that we get back.

1:59:50 So, here I’m getting the student’s name and the student’s major from student.

1:59:55 And if I wanted, I could order that information.

1:59:58 So, I could use another command which is ORDER BY.

2:00:02 And then over here I can just put the name

2:00:04 of the column that I want to order these by.

2:00:07 So, I can say ORDER BY name.

2:00:08 And now when I run this, you'll see we get the same results,

2:00:12 but they're in alphabetical order based off the name.

2:00:15 So, we get Claire, Jack, Jack, Kate, and Mike.

2:00:17 So, these are now ordered in alphabetical order.

2:00:20 And by default, these are going to be in ascending order.

2:00:23 But if you wanted, you could put them in descending order.

2:00:26 So, you could say DESC and this stands for descending.

2:00:29 So, now if I run this, you'll see

2:00:31 that all the names get returned in the opposite order.

2:00:33 So, Claire is all the way at the bottom.

2:00:36 And then we go all the way back up to Mike.

2:00:39 So, you can order by anything.

2:00:40 So, I can even order by like student_id.

2:00:43 So, I’m not returning the student_id.

2:00:46 In other words, I’m not getting the student ID up here,

2:00:48 but I can still order by it.

2:00:49 And so, now these are going to be in descending order of student ID’s.

2:00:53 So, actually why don’t we just get all of these now.

2:00:56 So, I’ll just say SELECT ALL from student.

2:00:58 And you'll see now it’s ordered in descending order by student ID.

2:01:01 It’s a little bit clearer, 5, 4, 3, 2, 1.

2:01:03 But I can also get rid of it or I can just say ASC which stands for ascending.

2:01:08 And now it will order them in ascending order.

2:01:10 So, you can order by a bunch of different stuff.

2:01:13 You can also order by different sub columns.

2:01:15 So, I could say like ORDER BY– and we'll start with major.

2:01:20 And then after that we'll do student_id.

2:01:23 So, SELECT ALL from student.

2:01:26 ORDER BY major.

2:01:27 And then student_id.

2:01:29 So, it’s going to order them by major first.

2:01:31 And then if there’s any of them that have the same major,

2:01:33 it’ll order them by student ID further.

2:01:35 So, I’m going to run this.

2:01:38 And you'll see down here, it’s ordering everybody by major.

2:01:40 So, we're getting biology, biology, chemistry, computer science, sociology.

2:01:43 In this case, these two students have the same major.

2:01:46 They also have the same name too.

2:01:48 But their student ID’s are different.

2:01:50 So, the student ID’s are now order in ascending order.

2:01:53 But I said descending right here.

2:01:57 And I run this query.

2:01:59 Now you'll notice that the biology major

2:02:01 with student ID 4 came first and then 1.

2:02:04 So, it ordered it first by major and then

2:02:07 within that, if they have the same major, it ordered them by student ID.

2:02:11 And you can specify as many of those as you want.

2:02:13 You can also limit the amount and results you're getting.

2:02:16 So, I can say like SELECT ALL from student.

2:02:19 And then I can say LIMIT.

2:02:20 And here I can put like 2.

2:02:21 And now what this will do, is it’ll limit the results I get back to 2.

2:02:25 So, now instead of getting all the students back, we only got 2.

2:02:28 So, if you only want like a specify number of rows back from the table,

2:02:32 then you’ll only get that.

2:02:33 And you can also combine these.

2:02:35 So, I could also like order them.

2:02:36 So, I could say ORDER BY student_id, descending.

2:02:41 So, now this is going to select all the students.

2:02:44 Order them by student ID only give us 2 of them back.

2:02:47 So, now when I run this you'll see we're getting 2 back.

2:02:50 And it’s ordering them in descending order by student ID.

2:02:53 So, that can be pretty useful.

2:02:55 And that’s just another way that you can make these more complex.

2:02:58 The final thing that we can do that I’m

2:02:59 going to show you guys in this tutorial is filtering.

2:03:01 So, I can say WHERE.

2:03:04 So, if you remember in a previous

2:03:05 tutorial we were updating and deleting students.

2:03:07 And we wanted to only update or delete

2:03:10 specific students where certain conditions were true.

2:03:12 And we used this WHERE condition.

2:03:14 You can do the same thing for SELECT.

2:03:16 So, I could say like SELECT ALL FROM students WHERE major is equal to ‘Biology’.

2:03:22 And so, now this is only going to give us the students who are biology majors.

2:03:26 And you can see down here we get back the two Jacks who are both biology majors.

2:03:31 You can do the same thing for chemistry.

2:03:34 So, let’s see if we have any chemistry majors.

2:03:36 We do.

2:03:37 Looks like Claire is a chemistry major.

2:03:38 And if you wanted, we can only return specific columns, like I said.

2:03:42 So, we can say SELECT, you know, the name and major from student.

2:03:46 WHERE major is ‘Chemistry’.

2:03:48 And now we're only getting the name and the major back.

2:03:51 And you can make these more complex.

2:03:53 So, I could say like WHERE major is

2:03:56 equal to ‘Chemistry’ OR major is equal to ‘Biology’.

2:03:59 And so, now this will give us all of the chemistry

2:04:02 and the biology majors so we get the two Jacks and Claire.

2:04:05 And we can also do different things.

2:04:08 So, like I said, like WHERE major is

2:04:10 equal to ‘Chemistry’ OR name is equal to ‘Kate’.

2:04:13 And so, now we'll get back any of those students.

2:04:16 So, we get back Kate and Claire and they have different majors.

2:04:20 So, you can play around with those WHERE

2:04:22 statements to get specific entries from the individual table.

2:04:26 So, I want to talk to you guys about how

2:04:28 we can make these where’s a little bit more complex.

2:04:31 So, obviously over here we're using equals,

2:04:32 but there’s a bunch of other stuff you can use too.

2:04:35 So, this is actually comment in SQL.

2:04:38 So, if you put two dashes, then anything after it is going to be a comment.

2:04:42 But here I have all the different comparison operators.

2:04:46 So, we have equals.

2:04:47 We also have less than, greater than,

2:04:49 less than or equal to, greater than equal to, equal

2:04:52 to, not equal to– which is these less than/greater than signs.

2:04:56 And then AND and OR.

2:04:57 So, you guys have seen some of these, but we can use these.

2:04:59 So, I can say like major not equal to chemistry.

2:05:02 So, this is going to select all the students

2:05:04 where the major is not equal to chemistry.

2:05:06 So, if I run this, now we get all

2:05:08 the students except Claire because Claire is a chemistry major.

2:05:11 Or we can do the same thing for numbers.

2:05:13 So, I can say like student_id.

2:05:16 So SELECT ALL the students where student_id is less than 3.

2:05:19 And we need to get rid of this.

2:05:21 And so, now we'll get all the students who have student ID’s.

2:05:26 Let me get all of these.

2:05:28 So, we're only getting students 1 and 2.

2:05:32 We're not getting anything– or we can do less than equal to 3.

2:05:35 And now this will also give us that student ID equal to 3.

2:05:39 And you can combine these.

2:05:40 So, like where student_id is less than 3 and name is not equal to Jack.

2:05:46 So, if we run this now, you'll see we get Kate and Claire,

2:05:50 but we don’t get Jack who is student

2:05:52 ID number 1 because it didn’t fit the condition.

2:05:54 So, you can these all these comparison operators to compare,

2:05:57 you know, different things.

2:05:58 And by using them inside of this WHERE statement over here you can

2:06:02 seriously filter the results down to only like the few that you need.

2:06:06 All right, so I want to show you guys one more

2:06:08 cool thing we can do which is using the IN keyword.

2:06:10 So, instead of just like putting a little

2:06:13 condition like this, we could say WHERE.

2:06:16 And then we could say like the name of one of the columns.

2:06:19 So, like name.

2:06:20 And then I can say IN.

2:06:21 And then over here I can put some parenthesis and I can put some values in here.

2:06:24 So, if the name is Claire, Kate, or Mike.

2:06:31 So, basically what this is saying is SELECT ALL from student

2:06:35 WHERE the name is IN these like this group of values, right?

2:06:39 So, if the name is Claire, Kate, or Mike, then it’s going to select that.

2:06:42 So, now I can click Run and you'll see down here,

2:06:45 we get all the entries, Kate, Claire, and Mike.

2:06:48 So, this is a really easy way to compare

2:06:50 like one column to a bunch of different values.

2:06:52 So, like we could check to see if the name is in there.

2:06:55 We could also do the same thing for like major.

2:06:57 So, like WHERE major IN ‘Biology’ or ‘Chemistry’.

2:07:03 And now if we run this, you'll see

2:07:05 that we get Jack and Claire because they’re both biology.

2:07:09 So, we can use IN.

2:07:11 And you can also combine all these things together.

2:07:13 So, you could say like WHERE major IN ‘Biology’

2:07:19 and ‘Chemistry’ AND student_id is greater than 2, right?

2:07:25 And now I'll click Run and you'll see that we get these two students, right?

2:07:30 So, you can combine the SELECTs and the WHEREs and even like

2:07:33 the LIMITs and all that other stuff to make these kind of complex queries.

2:07:38 But that’s sort of the basics of doing these queries.

2:07:41 I mean obviously these are very simple queries.

2:07:43 And as the database schemas get more complex,

2:07:47 the queries that you need to select specific pieces

2:07:49 of information are also going to get more complex.

2:07:52 And we're just using one table here, right?

2:07:54 We're just getting information from the student table.

2:07:56 But if we had multiple different tables, you know,

2:07:59 maybe certain tables have like foreign keys to other tables,

2:08:02 like getting information can get pretty complex.

2:08:05 And as we go forward in the course,

2:08:07 we're actually going to design a more complex database schema.

2:08:10 And using that database schema,

2:08:12 we're going to learn more about using these select commands.

2:08:16 But for now, that kind of shows you guys the basics.

2:08:19 And so, what you want to do is just kind of play around with these, right?

2:08:21 I mean we have our student table.

2:08:22 It’s very simple.

2:08:24 It has three columns.

2:08:25 And you know, just play around with getting

2:08:27 specific pieces of information using these WHEREs

2:08:29 and INs and all of these different keywords

2:08:32 in order to select the information that you want.

2:08:36 [Company Database Into] In this tutorial I’m

2:08:41 going to introduce you guys to a more

2:08:44 complex database query that we're going to be using for the rest of the course.

2:08:47 And so, up to this point in the course we've learned a lot of stuff with SQL.