Become A Web Developer - Part 24: Databases, SQL | Andrei Neagoie | Skillshare

Become A Web Developer - Part 24: Databases, SQL

Andrei Neagoie, Senior Software Developer + Instructor

Become A Web Developer - Part 24: Databases, SQL

Andrei Neagoie, Senior Software Developer + Instructor

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
11 Lessons (51m)
    • 1. Introduction to Databases

    • 2. Installing PostgreSQL

    • 3. SQL: Create Table

    • 4. SQL: Insert Into + Select

    • 5. SQL: Alter Table + Update

    • 6. SQL: Conditional Selections

    • 7. SQL: Functions

    • 8. Joining Tables 1

    • 9. Joining Tables 2

    • 10. SQL: Delete From + Drop Tables

    • 11. Where to go from here?

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Databases + SQL

This is the tutorial you've been looking for to become a web developer this year. It doesn’t just cover a small portion of the industry. In this multipart video series we will covers everything you need to know to get hired: from absolute zero knowledge to being able to put things on your resume that will allow you to live the life you want. 

Sounds too good to be true? Give me 5 minutes of your time to explain to you why I built this course and what is different here than thousands of other courses all over the internet.

  1. There is no wasted time here. We won’t be using outdated technologies like PHP, Wordpress and JQuery. Although still useful, outdated technologies like the above are low paying and demands for them are decreasing. In this course, you will learn the specific technologies that are the most in demand in the industry right now. These include tools and technologies used by the biggest tech companies like Google, Facebook, Instagram, etc… It’s geared specifically for people that want to learn employable skills this year.

  2. After finishing this course, you will be able to apply for developer roles, or upgrade your job title as a developer and earn a higher salary. We won't be taking any shortcuts in this course. I am going to take your from absolute zero, where I teach you how the internet works, to mastery, where I show you how to build an image recognition app using a Machine Learning API (a subset of Artificial Intelligence).

  3. This course is taught by an instructor who has worked in silicon valley, and one of the top tech companies in Toronto. I have built large scale applications, and have managed a team of developers. I have worked directly with these technologies. I am not an online marketer or a salesman. I am a senior software developer who loves programming and believes that there needs to be a course out there that actually teaches valuable skills.

  4. I know your time is valuable. You want a course that outlines the best way to learn the topic in simple, well explained methods so that you fully understand topics instead of watching somebody on your screen and having no clue what is going on. I have created this course after consuming hundreds of books, tutorials, and online courses while I was learning. I have taken the best pieces, the best methods, that I have found, and condensed everything so that you can learn in the most efficient way possible. 50 hours of videos doesn't mean much if the instructor isn't engaging or focuses on outdated topics. 

  5. We have a thriving online chat community so you really feel like you are part of a classroom and not just watching videos by yourself. You will have a chance to communicate with fellow students, work on group projects together, and contribute to open source. Anytime you have a question you can ask in the chat and someone will surely be able to help you right away. 
  6. The course is designed to give you employable skills so you can get a job. Here is what one student recently wrote after taking the course and being hired right away: 
    "I’m a self taught dev, who has been out of work for ~6 months. I had some family things that came up that prevented me from seeking permanent employment, so for awhile I was Postmates/Uber delivery driver.
    After taking this course, I immediately got catapulted back to where I was before, but better equipped and with the knowledge to take on the next job. I had just finished the React section when I went to a recent interview, and it really helped me excel. As of today, I am officially re-employed back in the field, and it is all thanks to this course. I had a blast creating the final project, and FINALLY got around to learning SQL statements, and getting to use them in a project. I’m really ecstatic that Andrei went with teaching relational databases over something like MongoDB, which can also be fun, but is less practical for use on the job. So thanks Andrei , I really enjoyed the course and will be making sure to share it with others who find it helpful. I’m also looking forward to the new ES7/8 content that was recently added, and going through the DB stuff again when I go to build a personal project."

In this course, you will be taken through online videos and exercises where you will be able to do the following things by the end:

  • Build real complex applications and websites
  • Build an image recognition app together at the end of the course so you can add it to your portfolio
  • Go into a job interview confident that you understand the fundamental building blocks of web development and the developer space this year.
  • Be able to go off on your own and grow your skills as a developer having built a solid foundation
  • Learn how front-end, servers, and databases communicate and how they all fit together in the eco system
  • Build your own startup landing page. 
  • Go off and remotely work by being a freelance developer and bid on projects.
  • Know EXACLTY what a day in the life of a developer is like and what the day to day technologies and tools will be that you are using. 

By the end of this course you will be comfortable using the below skills and you will be able to put them on your resume:

  • HTML5
  • CSS
  • Bootstrap 4
  • Javascript (ES6/ES7/ES8)
  • React + Redux
  • Git + Github
  • Node.js
  • Express.js
  • NPM
  • PostgresSQL
  • SQL

This course is the accumulation of all of my years working, learning, and teaching coding and all of the frustrations and incomplete information I have encountered along the way. There is so much information out there, so many opinions, and so many ways of doing things, that unless you have spent the last years working with these technologies in a company, you will never fully understand. So this course is the answer to that exact problem. I have gone through thousands of coding books and online tutorials and bootcamps. I have worked with these tools on real applications. Throughout the years I have taken notes on what has worked, and what hasn't and I've created this course to narrow down the best way to learn and the most relevant information. 

I will succeed if you succeed in this course. Therefore, I will be there every step of the way helping you understand concepts and answer any questions you have.

I am 100% confident that you won't find a course like this out there that is as well organized, and as useful, to build a strong foundation for you to start a new career. We're not going to be building simple todo applications and cat image sliders. We are going to learn actual practical skills that will put you into the workforce. Some unique sections that you won't find anywhere else are:

  • React.js + Redux: you will learn the library that companies like Netflix, Facebook and Instagram use to build fast, scalable applications. This is one of the highest in-demand skill in the industry.
  • A day in the life of a developer: What will your day to day look like and what tools will you use? I will take you through a sample day at a tech company.
  • How does the internet actually work. What is the history of these technologies: I will actually have you understand the underlying concepts of the internet, and how the technologies we have now, have come to be where they are.
  • How do you actually deploy a real life app so that it is secure, and won't get hacked: How does a real life app get out to the public?
  • What is Machine learning and how you can harness it's power: Whether you have heard about it or not, this is something that you will hear more and more in the coming years. Those who understand the high level concepts and can harness it will have an advantage. 
  • What is your developer environment on your computer look like: We will be setting up our computers with all the tools necessary of a developer so you can use the same setup when you go into the workforce.

Why do we teach the above? Because in this day and age, just knowing HTML CSS and Javascript is not good enough and you won't be able to grow in your role and command a higher salary. I am teaching you these things because these are the things you should know so that you are miles ahead of the rest. 

Make this the year that you took a risk, you learned highly in demand skills, you had new experiences, and you received new opportunities. I hope you join me in this journey. 

This is the proudest work I have ever done in my life and I am confident that you won't find a course better than this. 

See you inside!

Taught by: 

Andrei is the instructor of the highest rated Web Development course on many platforms. His graduates have moved on to work for some of the biggest tech companies around the world like Apple. He has been working as a senior software developer in Silicon Valley and Toronto for many years, and is now taking all that he has learned, to teach programming skills and to help you discover the amazing career opportunities that being a developer allows in life. 

Having been a self taught programmer, he understands that there is an overwhelming number of online courses, tutorials and books that are overly verbose and inadequate at teaching proper skills. Most people feel paralyzed and don't know where to start when learning a complex subject matter, or even worse, most people don't have $20,000 to spend on a coding bootcamp. Programming skills should be affordable and open to all. An education material should teach real life skills that are current and they should not waste a student's valuable time.  Having learned important lessons from working for Fortune 500 companies, tech startups, to even founding his own business, he is now dedicating 100% of his time to teaching others valuable software development skills in order to take control of their life and work in an exciting industry with infinite possibilities. 

Andrei promises you that there are no other courses out there as comprehensive and as well explained. He believes that in order to learn anything of value, you need to start with the foundation and develop the roots of the tree. Only from there will you be able to learn concepts and specific skills(leaves) that connect to the foundation. Learning becomes exponential when structured in this way. 

Taking his experience in educational psychology and coding, Andrei's courses will take you on an understanding of complex subjects that you never thought would be possible.  

See you inside the course! 

Meet Your Teacher

Teacher Profile Image

Andrei Neagoie

Senior Software Developer + Instructor


Andrei has been working as a senior software developer in Silicon Valley and Toronto for many years. He is now taking all that he has learned to teach others programming skills in order to discover the amazing career opportunities that being a developer allows in life. 

Having been self-taught, he understands that there is an overwhelming number of online courses, tutorials and books that are overly verbose and inadequate at teaching proper skills. Most people feel paralyzed and don't know where to start when learning a complex subject matter, or even worse, most people don't have $20,000 to spend on a coding bootcamp. Programming skills should be affordable and open to all. An education material should teach real-l... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. Introduction to Databases: all companies store data from a simple record book of a small business, maybe even on a journal or a piece of paper to massive companies like Amazon that need computers to store petabytes of data. Now, in order to store this information pretty much every company, sometimes individuals to needs something called a database. We saw in the last section how hard it was to have our server remember a new user. Every time the server crashed, the variable would get reset, and then it would start all over. Or if we wanted to do a log in or a sign in check and iterated through the e mails and passwords, doing a loop through JavaScript that's inefficient. That's where database come to the rescue. So let's ask ourselves, What is a database? A database is a collection of data and what we mean by data. There are many forms of them, right? There could be numbers that can be dates. There could be password hashes. There could be user information. Databases allow us to organize this data in a way that is useful to us, and it makes data management easy, and something came out of that and it's called Database Management System or D B M s for short. It's a collection of programs which allows us to access databases and work with data, and it also allows control access to database users. So a D. B. M s is something in this box something that allows us a tool, a piece of software that allows us to communicate with the database store information that's useful for us and also allows us to update, insert, delete. Look up whatever is in the database and this idea of ah d B. M s really started to take shape in 19 sixties and now it is at the forefront off many exciting things. There's an entire industry of engineers that just work with databases because it is so important. Now there are two types of D. B. M s that are really popular right now and that we're going to talk about in this course. And these two types are represented really well by post Chris and Mongo DB. Let's talk about the 1st 1 which is relational database, and this is probably the most popular, as you can see on the right hand side. You see that all of these are relational databases. Post GREss Oracle, SQL Server You may have heard of my SQL Ask you a light. The's air old types of databases that have this relation Aled database tag to them, and the beauty with them is that they're all pretty much similar. They all follow a same standard format. Relational databases consists of two or more tables with columns and rose. So in this case, users is a table and full name user named Tax created at our columns. And whatever values they have here are rose. Each road represents an entry, and each column source a very specific type of information like name, address or phone numbers. And then the relation between tables and feels is called a schema in a relational database , the schemo must be clearly defined before any information can be added. And if this is confusing right now, don't worry. We're actually gonna create thes databases, so it makes sense to you. If we had Twitter, for example, you can see over here the types of tables that we would have and how he would organize it, we would have a user stable, a tweet, stable, a following table and we can connect all these information, for example, by connecting different pieces of the table. For example, the user name will be the same in the user stable as it is in the tweets stable. That way we can say that the user name in the tweets table is the foreign key off the user name in the user stable and they're from user will be the foreign key of user name in the following table. Now things like full name or I d something that identifies each row in a table. It's called a primary key, and we'll go through that later on in this section. All right, so all these databases, we're gonna learn about post GREss SQL. But they're all pretty much the same. So once you know on, it's fairly easy to pick up the other ones. How do they actually communicate with he server with the backend? We'll all relational databases used something called SQL and SQL allows us to communicate just like a CDP did between the front and on the back end with SQL were able to communicate with the database and modify or get or update however we want, and we'll have a video on this topic as well. All right, so that's relational databases. What about the second type? The second type is called a non relational database or a no SQL database. And this once again, there are many, many different types of data basis, with some cool names like couch TB and hyper Table. Now, a mongo DB or a non relational database lets you build an application without having to define the schema first, unlike a relational database, if we go back to relational database, this schema or these tables have to kind of be pre defined, right. We need to know how are AP is going to look make thes stables so that once the app is public, we can start entering user information and tweets and followers with a no SQL or non relational database. We can just define it as we go, and they all have different ways off storing this information, so each one is very, very different. And this is another powerful reason to use a non relational databases that if your data requirements aren't clear at the outset of your project, and maybe you have a massive amount of on structured data, you may not have the luxury of developing a relational database with a clearly defined schema. The offer, instead a greater flexibility in that a non relational database are more like folders just assembling related information of all types. No mongo DB is something called document oriented. Its stores information as documents. Let me illustrate this point to you. I have my desktop here with two folders. One is a relational database and here have users tweets profile following, and you can think of relational databases with this type of storage where this folder lift somewhere on a computer that stores this information, and any time we need to ask a data base for something, well, we grab the users. And if we also want to grab the tweets of the user, we find the user that were interested in. Then find the tweets of the user that we're also interested in with the foreign key that links to the users. And we can also find however many followers that user has. We grab that information and display it on our Web app. Mongo DB, on the other hand, well, actually have each user as a document. So if I wanted to grab the first user. Well, I just grab this document and it has all the followers, the tweets, the profile. Everything is in this one document. And you might be thinking, mongo DB looks like a better choice than having this way of doing things right. Well, it depends on your need. And that's why databases is such a complex topic. It really, really depends on your situation. You can see this mongo db way being very useful if you have something like a profile, maybe a linked in profile. But if you needed something where you're working, was just following data or just tweets data, maybe with the tweets you want to see what is the average tweet size? Well, it's really easy to just grab this fall and calculate that versus this way where you might have to extract it from each one of the users. So it really depends on your needs. Michelle. You another diagram that might be useful when we look of relational versus non relational databases, you can think of relation ALS, as each table has a block post a block tag, a block comment, and it links the stables to give you the blawg information versus a non relational that stores this entire block post in an entry and has comments, tax categories and all other related data just in one single place. So what does mongo db used to communicate if we go back to our example here, we had ask you all for relational databases such as Post Press. Well, Mongo DB has its own what we call query language, and it is just the Mongo DB query language. But they both aimed to do the same thing that is, to communicate with the database, provide an easy way for the server to communicate with the database. And in this section, we're gonna be talking about that, and we're gonna finally connect the dots and show you the power of databases once they're connected to a server. I'll see in the next one nobody 2. Installing PostgreSQL: Welcome back. We're going to start setting up post stress on our computer so that just like we did with servers and front end, we're gonna have a terminal that's running our front end and a back end that is running our server. But also another terminal that is running a database because, as you remember, thes air just computers running somewhere in the same way that we ran our server on our computer. We can also run our database on our computer and in this video, we're gonna get us set up with Post Press. Now, Mongo DB has got him popular. However, nothing beats a relational database for really developing your knowledge of databases. Wants you understand relational databases. It gives you really, really good tools and mental models to work with. Mongo db mongo DB is very, very simple. You can think of it as a Jason data structure where we just have Jason that looks like an object, and we store that information there with SQL and Relational databases. It's more complex than that. So if you understand this, you are miles ahead when it comes to understanding databases. So we're gonna be focusing on that because the principles are still the same for Mongo. Db. All right, so let's get Post Press set up on our computer. The first thing we want to do is download something called a gooey so a graphical user interface for Post Press. And this exists for pretty much any database out there. I like peace equal cause it's free. It's really, really good for Mac, and it's very light and simple. I'll also make sure to add notes on how to download something like this and post GREss in general, if you're using a Windows machine, but otherwise, just click on the download link will be completely fast. Just open it up and you'll have something like this that I'm gonna be using throughout the course. But there's many, many options and can take a look for it. You just searched for post GREss SQL gooey, and once you have peace equal installed, you'll have it in your application folder and you can follow along. Now. Let's go back to the terminal in order to have Post Crescent's told. There's many, many ways, and again I'll provide some notes on how you can do it. But the best way especially O Mac, is to use brew, which we've already talked about every time you do something with home brew again. It's a way to download packages from the Internet on your terminal. You always want to make sure that you do a brew update to make sure that everything is updated. And also a brew doctor was. Just make sure everything is working on your system. Let's clear that. Perfect. Once you have that set up, we're going to just say brew, install both breasts SQL. And this will install for us Post Crest, and you see over here that I already have postcards installed. But if it's your first time, it will run through the commands and then actually, at the end, tell you exactly how to start using it. But in our case, also Stelly, who will be much easier to start Post Crest, you need to do brew services. Start post dress. And don't worry. This isn't Post Crest specific. Any database that you're downloading, your essentially downloading the software and then you want to start the service. Each one has its own commands, but it's a simple as Googling and figuring out how to run the database. So we've started Post Crest SQL. It looks like I've already started the service. And just to show you if I actually do stop here, it will stop the post Press service. Unless you start again and you can see that it's starting Now, in order for us to use Post Press, we need to create a database. And the way we do that is to use their create DB Command That has come once we've installed Post Crest and here we can call it whatever we want. So let's call it test. It's gonna create the DB. And there you go. Here's the cool part. If I go back to my piece equal, open this up. Let me close this. I have over here p sequel and it asked for the host, which is the link to where the data base is located. In my case, it's located at my local host, right. It's running on my computer. So you wanted to be local host and the port number This is the default poor number off 5432 Remember how we had poor 3000 and 3001 for the front end and back end. It's similar to that it's opening up a port for it to listen to the database, and this is the standard port for Post Crest that you'll see. So when I did the start of pro stress and create DB, the default or 5432 was already set. I can actually set what port and change it up, but we want to keep everything standards, so we'll keep it the way it is. If I now go to the database and we called a test so again will do test database hit, connect. All right, we're connected to the database, but there's no much in here, is there, But at least we know that we're connected to local host test and another way to connect to this. Remember, this is a gooey a graphical user interface, and I'm showing you this because it's really good for Democrat persists. But I want you to get comfortable working with the terminal as well, in order to do the same thing we just did with the gooey. We can type in P SQL again, which is a command that we'll get when we install Post Press, and we'll say which database we want to connect you in our case will be the test. And there you go. Just like when we run node and it changes terminals. Now we're inside off Post Press That's running on our computer, but we don't know any commands you, Dewey. So in the next video, we're gonna learn some SQL commands and finally create some tables in our post press database. I'll see in that one, but my 3. SQL: Create Table: All right, so let's create a table. If you remember to go to our post press database, we simply write P SQL and then our database name and we'll enter the database and now we can communicate with it using SQL. Now let's create a table. How can we do that again? SQL comes with some language language, syntax that we can use and to create a table. We can use something like this we say create table, which is an SQL command. We enter a table name followed by brackets, and then we say what we want the column want to say with the data type column to with the data type column three with the data type and then ending it with semi college. Let me just press enter and see what happens here. I get in there type data type does not exist, and that is because data type is referring to what Post crests or are relational Database knows we have to say ahead of time. What type of data column? One. Call him, too, and column three. Hope if we go back to our chrome browser, we see that for Post Crest. We have a few data types that we can hold. String data types, numeric data types and date and time data types and each one of them have different things . For a numeric data type, we have integer, which is a four byte integer. We have big Int, which allows for a bigger number and all these different things We have even money, which is currency value. And the reason we have to give this data type is because of the way databases work there, optimized to make sure that as long as everything is the same in their column, they're able to work really, really fast to perform actions. In our case, let's use some of the most common ones we can use text for the first column. So let's do that. I'm gonna press the out barrow and get to create table. I'm also gonna name the stable. Let's call it users and column one will be name data type can be text column two. We can say that is age and data type for an age. Well, if we look down here, we look at numeric types. We want to use something like small int, which is a to bite signed integer. It means that a max for a small int If I go to small int max, we see that the max range is 32,762. Well, nobody's gonna be that old, right? So we can use small. And for now, let's do that. I'm gonna say ages, small int. And finally, the third column will just have birthday and birthday can be a date data type and we can just have a date. Let's press center and I get create table. Let's look at our p SQL here and I'm minimize this. And if I click refresh here. I see that we have users. Look of that and we have columns for the table users. That is name H Birthday. It tells us the type and the length. So now we have our table. Awesome. There's also a way to do it here. If we do back slash d, I get a list of relations and look at that. We have our users table and the owner which is me vehicle. There you go. We've created our first table. Now that we have this, we can start entering some information, and by the way you might be asking, How many times do we run these commands? Well, ideally, once you create your database and it lives somewhere on a computer somewhere in the world, you only run this once, right? You create a table of why you want your database toe look like and the rest is just filling in and inserting information and reading information off of it. So this is the command that we're going to set up when we create our own database to set up our schema as and then after that, it will just run on its own and it will be able to communicate with the back It it's gonna be very fun. By the way, before we get into the next video, I want to show you one more command. We learned the slash d shows us our tables. If I do slash Q. We exit out of here and or back to the terminal. All right, I'll see you in the next one. But by 4. SQL: Insert Into + Select: Welcome back. Let's go back into our database. We've just created our users table, but now we want to actually insert some information. How can you do that again? Using SQL, we have the insert into command that were able to insert into whichever table name we want . We're going to specify the column and then the values of each of those respective cops and always ending in a semi college. I also want to emphasize that with SQL, the capitalization is just a standard. But if I made this lower case, it will still work its case insensitive. So, looking at our database, we have name Agent Birthday. Let's insert something. We'll say that we want to insert into users column. One will be named and then we have age first day. And no, you don't have to insert every time into every column. You can just change the name if you want. All right. Name will be Andre. Value will be 31 and then birthday will say is if you remember the day syntax If I open up here is this year, Month day, so I simply do 19. We're gonna make myself really old. Well, say January 25th Press Center. I get insert Awesome. If I do slash d again, I see my table. But how can I see the value that I just entered? Well, first, let's check here. Let's check with our gooey quick on content, select users and look of that name age Birthday. I have Andre 31 with my birthday. Very cool. Now how can we grab this information again? A SQL syntax is select and we want to say what we want to select. In our case, we can say name, age, birthday from and then the table name in our case users semicolon, press center And look at that. I get Andre age 31 birthday. If I want to just grab the entire columns without spelling out everything. Imagine if we had tons of columns we can use the wildcard symbol off star. That's shift eight to get star and I get the same thing. Very cool. By the way, with P SQL, we can actually run these queries here, see how already has select. I can say select star from users and even gives me nice coloring press, enter or press semi colons and Ron Queer look at that. It gives me the table for me. And it also has the history, which is really, really nice. Let's add a few more users here. I'm going to insert this time somebody else. I'm just going to change a few things. 41 Sally inserted. If I select star from users, I have Andre and Sally going back here. Ron Query. I have Andre and Sally in the database. How cool is that? All right, we know how to create a table. We know how to insert into a table and we know how to grab this information. We can already start picturing what powers we can have now with our final project. But there's still a few more things we want to learn for that I'll see you in the next video, But why 5. SQL: Alter Table + Update: Welcome back. I've added one more user here, so we have a little bit of a bigger database. But let's say we have a problem. We realize that we're working on this project and hopes we actually needed to also include a score call. How can we add another column to a pre existing table? We have ask you all for that. We can use altar table and using this format we can at and you call him. So let's say that in the user's table we want to add a column that is score and data type we want. That's a small ant, a score that can be over 30,000. So let's say Max scores 1000 so small, it will be fine. I press enter and now we have altered table. If I refresh here, go to users. I see that I have scores, but we have a problem now. We have scores, but it's no. There's nothing for these users. What if we wanted to just update the information here to include the scores? Let's try and do that to update we have the update table name. Oh, and we have three rows, which we don't want. But for now, I just want to show you we have update table name and then we want to say Set whichever call him. We want to equal a value where the column equals some value. That looks confusing, doesn't it? Let me show you what I mean. I can say update users and in the user's I want to set score column to equal. What do we wanted to equal? Let's say that we want Andre to have a score of 50. We can say update Users set score 50 Where, which is another key word. Name equals Andre. It reads pretty nicely, Right? Update. Users set the score to 50. If the column name is Andre, looks like we got an update. If I now do select star from users, I see that I have Andre with Score 50. And you know what? We also want to update Sally and John, but they both have the same score of Let's say ah 100. How can you do that? We can say update to users. Set score too 100 where name equals John. But also we can say and name equals Sally for a press center. I get an update. If I go up to select users, you see that I did not get an update. And that is because end just like we did in JavaScript means that name should equal John and Sally. And we don't have that. It should be Sally or John, right? So we can just press up to get our role command and change this to or we have two updates that just happened. And if I look up users, look at that. I have Sally and John with Score 100 and Andre with 50 looking at our gooey If I refresh look at that. We have our table with our new scores. Very cool. A few more commands that I want to show you in the next video. It's all seeing that one, but why? 6. SQL: Conditional Selections: Welcome back while you're gone. Amy just joined our user base with a score of 88. Perfect. Now let's learn a few more SQL commands that are useful. What if I wanted to grab all users with names, starting with A With SQL. It's super fast and super easy. We can simply say select Oh, from users where name And this is a new one We're learning like and like allows us to add a condition. In our case, we can say a and then the percentage sign, which means anything after a if I click semi colons and enter. Look at that. I get Andre and Amy. Let's try this on our gulyas well, and show you what we could do if I go over here. Just copy and paste this. And if I change in this to Lower Case and Ron Query, well, I don't get anything because it has to be uppercase rate were searching for a There you go , although with SQL our commands can be lower case or upper case. When we do searches, we want to make sure that the case matters. And that is why mostly when we store information in databases We want to make sure that there's some consistency. So when we save these information such as inserting, maybe in the server, we lower case everything so that we don't have this issue. What if I wanted to do all names ending in? Why, if I do percentage? Why? Wrong query. I get Sally and Amy Amazing. Okay. What if I want to do something different? Let's say I want to get everything sorted in order. Maybe from the highest score to the lowest score. Well, we can select all from users and we can order by the column. We want to order by. In our case will be the score. And if we want to do ascending or descending, let's do descending first and I spelled Descending wrong. It's D E S C press center and look at that. Ah, half Sally and John as the top score. If I do Ascending a S c I guess Low score. Andre, I'm not doing too well in this game. Very cool. Are you starting to see the power of SQL? Remember all the looping that we would have had to do if we had user names? Sq old is really really good at grabbing this information. You saw how fastest is even if this was hundreds of lines will be really good at grabbing information. Updating information may be ordering it. It is extremely powerful pretty much into since the 19 sixties. It's been just the language of choice for databases because it's so good. Alright, awesome. We're starting to learn more and more. A few more to go. I'll see in the next one, but by 7. SQL: Functions: Welcome back. Now SQL can also have functions, and I'm gonna show you three of the most popular, most common ones in this video just to check what we have in our table while we have users . And if I do select star users I forgot to use from, we have our users. Now let's say we wanted to get the average score of the users. We can do that by saying Select average and give the column name inside of average. Let's say score from users table so you can see that their function, just like in JavaScript, has the function name and Andy brackets around. The column that we want Let's press center and look at that average is 84.5. Very cool. What other functions do we have? Let's say we wanted to get the some of everybody's age. I can invoke the some function and find out that we haven't accumulated somewhere 1 32 and then finally, there's also count another useful one. And if we want to find out how many users we have in our database, we can just say count name and we have four active users with our game Alright, We gotta work on that to increase that number. Perfect. There's a few more functions that you can use, but these are probably the most common. You can already imagine some of the power that comes with these valleys. I'll see you in the next one, but why? 8. Joining Tables 1: Welcome back. Now. We've gone over a few SQL commands and you should feel pretty comfortable with most of them . But there's one thing that I've kind of omitted until now. You remember here when we spoke about relational databases, I mentioned something about a primary key and a foreign key. How these tables are linked with one another with some values, like user name. Well, we're going to start introducing this idea of connecting the tables and how we can do that . The first thing I want to show you is this command right here. I want to create a long and table with creative will which we've seen and now within here. I have a few things. I have an I d that has data types, serial. Let's look at what that is. We see that the data type cereal is an auto implementing integer value. Okay, and this auto implemented value. Every time a news user is created, it will go one, 2345 So it's a unique I D that we can assign to each user. However, this is for the log in table. So that is we're creating an I d For each log an entry here and you see that it says, not know, not know means that this well, Haas to be a filled in property. Remember when we had the user stable and we didn't have the scores and we had no here? Well, if we do not know, that means that that's not possible. And then we also have primary key and setting primary keys when you're creating a table is one of the most important things By saying that the i d here is the primary key. It's saying that this is what I want to access when I'm looking for things and databases air really, really good when you set a primary key to grab this information. And, as you know, primary keys are on Lee one per table, usually something like an i D or something unique, like an email. By setting this as primary key, we now have a really, really fast way of selecting or grabbing information. If we look at the second value here, the second column, we have secret, which is a very bold character of 100. Let's look at what that data type is for the string data type variable character has a size , and we can say that in your Mary characters to store. So we're saying that the secret value in our case, this will be the hash will not be greater than 100 and it also is not know that is. This always has to be felt when we're inserting something into the stable. Otherwise it's gonna fail. And then we have name, which will be text that we've used before. But it also has to be unique and the unique key, Ward says that if a name already exists, well, then we can't insert into it. So let's run this command and see what happens. Let's refresh. We have the long and table now with I D secret and name. Let's enter some information in here. I'm going to say, insert and to log in. We don't need to now insert anything in the i D, because that's actually done for us because it's cereal. It will just automatically increment. But for the secret, we can say that secret will be a hash, and the name will be some sort of a text. That's four month. This a little bit values, and we'll say some sort of a string will just say ABC here and the name will be Andre. Let's run the query. Let's go back to the lodge in refresh, Go to log in and we see that Andre has been inserted with I d of one. If I go back to the query and at a different person, let's do Sally And her password is that's why Z run the query and now she should be in there. And you also notice that her idea is now, too. What if I enter this again? I get a Nair do prickly key value violates unique constraint. Log in name key because we said that we want unique for name Maykel. Let's enter the last one and this will be John, because that's what we have in our user stable. So we have John perfect. And if we go to log in, we also have John with his secret, which is a well and notice here that it's four instead of three because we ran a command. But we failed to do this but doesn't really matter because as long as these air unique d'Isere the primary keys, this is the one thing that we make sure that is unique at all times in our table. But by doing this, we've created something interesting. We have Andre, Sally and John that also live with Andre, Sally and John in the user stable. When we design databases like this, we can now have Andre, Sally and John referencing the users. And if I wanted to grab Andre, who are Andres Secret, which is ABC? In that case, I have a foreign key, which is this this name column in the log in is the foreign key that references the in our case, the primary key here. Because these are the unique identifying valleys in the user stable, that is Andre. And the next video. I'm gonna show you how we can finally connect these two together. I'll see you on that one. Goodbye. 9. Joining Tables 2: Welcome back in the previous video, we've created a log in table. Now that we have these two tables together, I want to show you the real power of relational databases going back to our drawing. The most important part of relational databases is this idea of scheme us off having tables that are just concerned about storing information specific to them, such as users, tweets and following and connecting them through a primary key and a foreign key. I'm gonna show you how to do that. Well, we've already done it. But how do we grab information from if we go back to our table? We remember that we ran our query. We created that create log in table here and we create a name very bold that when we planned, this was going to match whatever the user stable had. And we can use something called joints now with our queries. And this is a new word in SQL on probably one of the most powerful because we don't necessarily want to have one massive table with hundreds of columns. Hundreds of rose databases aren't designed to just have just like an excel sheet, tons of rows and columns and have all that information in one place because it's not efficient. It will be a lot slower, will be really, really hard to manage. And the bigger and bigger your company grows, it's gonna be really, really tough. A good way to design a database is to have these small little tables that are just concerned, just like separation of concerns, concerned with their minimal needed information. And if something else is needed, we can just join tables together to grab information. Let me show you what I mean. We have over here are test database and let's do slash T to see. All right, we have three files here. We have the log in and users which we created. But what is the log in I d sequence? Remember how we created a primary key? The way relational databases work is when you create a primary Klay, it creates another file that contains this primary key, and you can see that the type of sequence. Now, this is a file that most likely won't access. But internally, Post Crest is going to make sure because we set this primary key. It's gonna be really, really fast to grab data from the log in table when we request it. But let's say I wanted to get information from log in and users perhaps I want to get Sally's information, but I need her birthday score age as well as her secret. By using joins, we can do this. I can say Select star. So everything from users. And now if I do join, I can say another table name that I want to join with. The users in our case log in and we have to say on what are we gonna join or not? How is it going to know what matches with Log in? Because if I go back here, how's the database going to know that I want to match the first line with Andre? The second line with Sally of third line with John the on keyword allows us to make sure that we match on something. In our case, it's the name their primary key here and the foreign key here. So let's do that on users dot name equals log in dot name semi Colin. Let's do that and look at that. We have name, age, birthday score, but also I D secret and name all together. We've just joined these values and they all match because we've done log in Don name and user Don name and with joins come great power. Now we can have small little tables based on what we actually need, and any time something specific is needed. We just joined tables and you can do multiple tables, multiple joints in order to select the information that we need. All right, I think we've done a really, really good job understanding SQL on the par of relational databases I'll see in the next one and good job getting this far, but by 10. SQL: Delete From + Drop Tables: Welcome back. Now we have our tables here. But what if we wanted to delete something? Let's say Sally decides to. Let's try that again. Let's say Sally wants to just not be part of this app anymore. And she's going to remove her user. Well, she can send a request and on the back end weekend, simply do delete the table name users. And then we say, Which user? Well, we can use the where keyword and here say name equals Sally for a press center. And with delete, we actually have to do delete from so delete from users where name equals Sally and we have one. Delete. If I do select star from users. There you go. Sally's gun. All right, I think we're done with this example. Let's delete the stables. How can you do that? We can just drop table and say, log in. Logan has been removed and then dropped table users. If I look at what tables we have, we have no relations, no tables. All right. Good job getting this far. I'll see you in the next video. Bye bye. 11. Where to go from here?: