SQL Tutorial - Learn SQL in Under 90 Minutes | Jon Avis | Skillshare

SQL Tutorial - Learn SQL in Under 90 Minutes

Jon Avis, IT Systems Engineer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
3 Lessons (1h 21m)
    • 1. Class Introduction

      1:16
    • 2. SQL in Under 90 Minutes

      78:41
    • 3. Class Summary and Project

      0:53

About This Class

In this class you will learn all about databases and how to use SQL in Under 90 Minutes. By the end of this class you will know:

  • What a relational database is and what it is used for
  • How to create, modify and delete tables from a databases
  • How to insert, modify and delete data from tables
  • How to retrieve data from a table using select SQL queries
  • How to filter your results set using where clauses
  • How to order and limit your results sets using SQL
  • How to use column name alias'
  • The differences between 4 different types of joins
  • How to use inner joins
  • How to use outer, left and right joins

This is the best and quickest way to begin your SQL and database journey.

Transcripts

1. Class Introduction: Hi. My name is John and I'm in I T systems Engineer working with SQL and Databases on a daily basis. Welcome to my class. Learn SQL in under 90 minutes in this class, you're gonna learn what a relational databases will go through the installation and set up walk through for post Rescue L, which is the relational database will use in this class. You're gonna learn how to create database tables. Using SQL, you'll learn how to insert, modify and delete data from a table on. You're also gonna learn how to retrieve data from a table using select queries. We will also go through filtering results, sets using where clauses on how to order your data. You'll learn how to use the in between and like statements on. Then we'll go through how to use. SQL joins to retrieve data from multiple tables within a database. The course is designed for you to code along with me. So once you've installed post crescent roll, you can coat along with me in the videos. And then finally there's a project to complete, which will test what you've learned in this class. So let's get started with sq and data basis in the next video 2. SQL in Under 90 Minutes: So what do we mean by databases? So a database is an ordered collection of information or data and databases allow us to store data and also manipulate data. So by manipulating data, we mean adding data to a database, modifying and deleting data and also retrieving data from the database. So most of the databases you may have heard of already, such as Oracle, my SQL Microsoft SQL Server, are actually what we call relational databases. So what this means is that the data is stored in tables or relations, and these tables are linked or related to each other. So you can see in this database schematic or schemer. For short. Each box is a table, so we have seven tables on each line. Between the tables is a relationship or link. So this is actually an example of a cinema booking system database so you can see we have a customer's table on the top left, which is related to a booking stable. So this means that customers can make bookings on this booking Stable is then related to a table which holds data on the movie screenings and also a table which holds data about sees on in relational databases. Tables are related to each other. Three primary keys on foreign keys. So what are primary and foreign keys? So a primary key is a column or a set of columns, which uniquely identifies a row of data in a table so you can see this example. Table. It's an example of a pet stable, so it's holding data about a group of pets, and you can see here we have an I. D. Column. A Species column, A full name column, an age column on an owner i D column. So the species is holding data like dog rabbit, cat, etcetera. And then we have their names, their age and their owner, I. D. So the primary key in this table is actually the first column, the i. D column, and you can see that each value in the I. D. Column is unique, so we can use this column to uniquely at identify each row of data so we can see it's unique because it's been going 123456 etcetera, etcetera and you can see other columns or no, you need So, for example, we have in the species column we have dog twice, so we cannot uniquely identify each row of table just using the species column. So a primary key is a column which uniquely identifies each row of data within that table. So a foreign Kiiza column whose values match the values off another tables. Primary KEY column So you can see here we have two tables, and these are the two tables we're gonna create later on in this video. So the first table was actually a people's table. You can see it's holding data about certain people. So their first name, their last name, the city they come from and the state they come from. And you can see this table also has an I. D column, going from 12345 And this is also the primary key for this table. So the I D column is uniquely identifying each row of data within this people's table. And then you can see the second table, which is our pet stable, which we saw from the slide before. And this has a column called Owner I. D. On this owner I D column is actually a foreign key, which is matching the I D column from the people's table. So you can see for the dog Rex sick, for example, the owner I d. Is equal to one. And this is referencing the idea of one in the people stable so that Samuel Smith. So this is how we link tables together. Within databases, we use a foreign key which references or matches the data in another tables. Primary key. So here you can see the dog. Rex is owned by Samuel Smith because the owner I d for Dog Rex is one and the i d. For Samuel Smith is also one, and you can see our up rabbit called Fluffy as an owner idea of five. So she has an owner off Simon Smith. And then we have our cat called Tom, who has an owner idea of two. And we have our mouse called Jerry, which has ah oh, no idea of two as well. So they're both owned by Emma Johnson, and then you can see the dog. Biggles has a no no idea of one say she is owned by Samuel Smith, And then you can see our final pet, Squirtle, who has an owner idea of free So John Oliver, who has an idea free is the owner off Squirtle. And this relationship between tables is what makes relational databases such as mice you O Oracle, SQL Server. So powerful. So now we need to know what data a table can hold within a database. So table columns will have different data types depending on what sort of data they hold. There are many different types of data types in sq. Oh, but we will just take a look at some of the most common ones. So one of the most common data types is the data type, and this will hold images or whole numbers. So an example column, which would be an int data type, is an age column. So this age column will hold data such as 10 years old, 18 25 etcetera, etcetera. And then we have another data type called a decimal, and this holds decimal numbers, say 1.451 point 56 etcetera etcetera. So an example column for a decimal data type would be a height column, and now we have another data type, which is specific to post rescue L, which is the relational database which will be using in this video. And that's the serial data type on this serial data type will automatically increment the values with Air three row of data, which is entered into that table. So this is a great data type to use for our primary key columns because it will automatically increments a unique value for every row of data, which is entered in a table so we can keep our primary key columns unique. We can also hold words in our databases in our tables, so some data types, which can hold strings of actual data types on the voucher, will hold strings of different lengths. So if we have a column which needs to hold data where the length of AH word is changing with each row of data, we can use a bar chart data type. So an example column for this will be a name column. So this will hold names of different lengths such as Samuel and, uh, Jonathan, etcetera, etcetera. Then we also have a chart dates type. Now this is like a vulture, except it holds strings of the same length. So an example of a Chart Data type column would be a state column where we're just holding the two letter abbreviation off each state in America. So see a for California T X for Texas, etcetera, etcetera. So this is what we would use a charred data type for. And then quite an interesting data type is the 1,000,000,000 data type. So Billy and Collins can hold true or false values only so it will have a true or false value. An example. A column for a 1,000,000,000 would be an in stock column. So, for example, Amazon may have a column which is telling us whether they have a product in stock or not. And this will hold a value for true if it isn't stock on a value of force if it's out of stock and we can also hold date and time data in our columns. So the dates data tape holds year, month and day. So 1990 August 21st freaks, for example. So an example column for a date data type would be a date of birth. So people whose date of birth and then we have ah, Time data type, which holds our minute and second and an example off a time column would be a start Time column. And then finally, the last data type will look at is the timestamp data type, and this is a combination of date and time. So it holds the year, the mark, the day, the hour, the minute on the second and an example of a time stamp column would be on order. Time column. So Amazon would also hold data on what time in order was placed, and they may hold this data as a time stamp data type. So finally, what is SQL or sequel? SQL stands for structured query language. It is the language we use to interact with databases. So we write SQL queries to create databases to create, modify and delete tables within our databases and to insert, modify and delete the data in the tables. And we also use it to retrieve data from our database is so now we know about databases, relational databases and SQL Oh, let's install the relational database we're gonna be using in this video post Kresk UAL, and then we can start writing our very own SQL queries. Okay, so now that we know the basics about databases, let's actually go and install post rescue. Well, so you go to your favorite browser and type in post rescue well, or place Griesa zero on the first option you will see is Post Gresko dog and we want to click on that on it will take you to this page. Um, we can go to download. And once we've clicked on download, it's going to take us to this page where you have packages for Lennox, for Mac and for Windows. So obviously choose the system that you're on. So I'm on the Mac OS, so I'm going to be clicking on Mac OS. But if you're on Windows, you want to click on the Windows Link on that would take you to this page Now is gonna be the same where you're using Mac or Windows or Lennox. So what you want to click on next is download the installer and then it's gonna ask you to select your version on also select your operating system, so always go for the latest version. So at the time of recording, this is post rescue Well, 10.4. Say click on that and then you will ask you to select your operating system's Lennox, Windows or Mac. I'm using Mac. So I click on Mac OS on, Then click download now so it will open up this pop up and you want to save the file and you can see here that it's saving it. So once it's done that you can click here and you can open it up on this will pop up here and we want to click on post Rescue well, tempering for or DoubleClick net. And then we want to click open. And if you're using Mac OS, it's gonna ask you for your password when you install when he software. So just type in your password for Windows. It shouldn't do there. So ask, uh, you want this to change something on your PC and you're gonna get yes. So you type in your purse password, click OK, and just wait for that and you can see it's loading. And now we have the set up wizard for Placek Rescue. Well, so we want to click next on, then next again. So this is just installing it. Library post Crisco slash 10 And here we want Post Kresk, your old server. We want peut admin for we don't want Stack Builder, and we can leave command line tools ticked as well. So until sack builder, then click next click next again and it wants a password for the Super User. So type in any password that and remember, don't forget this password on Click. Next on It will give you a poor, so just click next and click next again next on DNA. Now it's ready to install, so click next and it's going to install Post Chris Que Well, so now that's finished. We've actually installed post crust girl on our computer. We can click finish on. We can get rid of this now and now. If you're using Mac, you can go to launch pad. Or, if you're using Windows, you can go to the start menu and you can search for PG that mean so don't search for post dressed girl sexual PG admin and we want to open. PG Admin four on PT at Men is the management tool for the post Cresco database. So this is what we're going to be using to write our SQL queries and stuff like this, a case that's the installation and set up for post Cresco and PG Admin finished. We've now open PCI at men four on. We're ready to start creating databases on creating tables and start learning. SQL. So now let's create our first state base. So in this video, we're gonna be creating a very simple database which holds information on pets on their owners. So let's create a database called Pet Underscore Owner. So you can see here on the left hand side, we have this server service and we have a plus sign. So if we just click on that plus sign, it's going to show us which post Cresswell versions we have installed on our PC. So I have 9.6 on 10 but you probably only have 10 if you've only just gone through the installation. So let's click on place, Chris, grow 10 and type in our password, which we set when we were installing Post Quest Girl 10 on. We can save that password, and now you can see it's opened up this list here under post Crest grow 10. So we have databases, we have locking groups and we have table spaces. So if we click plus on databases, it will show you which dates basis we have already in our place. Gross girl. 10. So you can see we have post crests, which is automatic re created when we install post GREss girl 10. So now let's create our very own database on. We can do that by right clicking on databases and we can then go to create and then database. And here we can give our database its name. So I want to call it pet underscore owner and then we can save up. And you can see here we now have two databases. So if we click on pet ona, you can see we get even more drop down options, but we can ignore them for now. What we want to do is we want Teoh, right? Click on pet ona on. We want to click on create script and this will open a window where we can write SQL queries to the pet owner database So you can see here it's already written some sq o on This is the create database pet ona So this is what it ran when we did that right Click create database pet ona It ran this query and created the pet owner database and we can come in here and we can write some some of our in SQL queries. So in this database, we're gonna be creating two tables were gonna be creating a pet stable, which will hold information about our pets, and we're going to create an owner's table, which will hold information about the owners of the past. So the first SQL query we want to write is a query which is going to create our pets table . So to create a table in SQL, we need to type the commands create table, followed by the table name. So let's start writing our first career e. But just before I do that, I'm going to zoom in on the browser just so you can read what I'm typing. So let's now right. Our first query to create the pets table. We need to type creates table, followed by the table name. So in this case is going to be called pets. And then on the next line, let's open a pair of brackets now within these brackets were going to be typing the columns , which you're gonna be in the pets table and also also the data type off the columns. So in the pet stable, we're gonna have full columns we're gonna ever i d column, which is gonna be our primary key, which is going to uniquely identify each row of data within the pets table. Then we're gonna have a species column, which contains data on the species of the pet. So dog cat etcetera, then we'll have the name of the pat, and then we'll have an age column as well. So the first column we want is our i d column. So we're going to call it I D. And then we're gonna define the dates type. So the data type for an I D column it is gonna be a primary key is normally given as a serial. So the serial data type is gonna auto increments with each row of data has entered into the table. So the first row of data, the I D. Will have a value of one, and then the next were of data. The I D will have a value of two, so their serial data type is automatically gonna enter each I devalue and is gonna auto increments with each row we add. And then we're gonna have another space on. We're gonna type primary key. So every time we want to identify a column is the primary key. We have to type the column name, which is I d, followed by the serial data type, followed by primary key. And then we have a comma at the end and we can move on to the next line and we can enter our next column in the paint stable, which is gonna be called Species. So we're gonna have a Species column now, this is gonna contain words. So cat dog rabbit, etcetera. So the data type for the species column is gonna be a vulture. So if our chart data type can hold words or varying length, that's what watch our means and Crafar char data types. We also have to define the maximum number of characters that this column can contain in one value. So if we say if we open a pair brackets and type 30 here, that means that this species column will be able to contain words that have a maximum length of 30. So any word up to the length of 30 and then we need to put a comma at the end, and we can move on to our next column, which is gonna be the name now, instead of calling this column name, Which is there a reserve word in Post Gresko? Where is she going to call a full name. And then as this contains words as well, we're also gonna have this as a vulture data type with a maximum length of 30. And then for the final column in our pets table, we're gonna have the age of the pair. Now the age of the pet is going to be a number is going to be 45 10 etcetera. So this data type is gonna be an intense and then for the final column, we don't actually need to end it with a comma. We can just end it like this, Andi, just make sure that outside of these brackets or these parentheses, you put a semi coat on so every sq oh, statement or every query must ends with a semi colon. And this lets the database? No, that it's the end of the query. So this is how we create tables in SQL we type create table followed by the table name. So in this case, pets, we open a pair of brackets, and within those brackets we type are column name, followed by the data type and for the primary kiwi type primary Key. Then we have a comma for our next column, a column name, data type, comma column name, data type, calmer and then column name and dates type. So if we run this query now and if we click the plus sign next there are pet owner database , go down to schema. So schemers is like a database diagram on. And if we then go down to tables, you can see we have the pets table created on. If we just expand this a bit, you can see the columns of the pets table of the I. D. The species, the full name and the age columns. So we've created our first table in our pet owner database. Now we need to create the second table, which is our own A table. So in the owner's table, we're gonna have five columns. We're gonna have I d column, like in the pet stable, which is gonna be our primary key. We're gonna have the first name on the last name off the onus we're gonna have to city. They live in on the state that they live in a swell. So let's create the owners table now. So again we need to type create table, followed by the table name. So in this case, it's owners and also as a convention and sq oh, it's normal that tables are plural. So pets, owners, etcetera there may need to go to the next line, open our brackets like before and we need to type in our columns. So the 1st 1 is exactly the same as the pets is I d, followed by the data type, which is the serial data type for that by Primary K. And then the next column in the owner's table was gonna be the first name of the owners, and this is gonna contain names. So it's gonna have to be a vulture again. So watch our and then we'll give it a maximum length for 30. As names can be quite long on, then the next column is gonna be last name. So the last name of the owner, which is those they're going to be a vulture with a maximum length of 30. And then we're gonna have a city column, which would also contain words for strings. So it's gonna be the bar chart data type with a maximum length of 30 again. And then the last column is going to be a state column. So the state they're from, but we're just going to use the two letter abbreviation of each state so we can actually use the char data type. So the voucher data type is for words that vary in length. So the first name off Emily is five characters long on a first name of Emma is four characters long, so Bar chart can take strings or varying length, whether, whereas a Charl is for fix Ling, so each state has a two letter abbreviation so we can use the chart data type and in brackets we can put, too. So they sort This column. This State column is now only gonna accept, um, words off length to it went, except words off length five or one or 10 only length to. So that's it for the owners table, I say, Let's just make sure the query has ended with a semi colon and then we can run this query and you can see it was successful on. We've created the second table in our database now, as well as creating tables in SQL. We can also modify tables that already exist in our database, and we can modify tables by using the altar table command so we could like OTA Table followed by the table name. So let's say we wanted to add a column to the pet stable weaken type altar table, followed by the table name. So pets and then on the next line, we can type at Column. So this is gonna modify their pet stable, and it's gonna add a new column to the pet stable. So we need to type at column, followed by the column name on its data type. Now I want to create a new column in our pets stable, which is actually gonna be a foreign key to the I d. Filled in our owners table. So this is gonna be a foreign key, which is gonna link the pet stable to the onus table, and it's going to contain the I D values from the owner table. So I'm gonna cool this column Owner I d. And then as it's gonna contain numbers, the data type for this owner I d is going to be in. And then we can finish the query with a semi coat on. So this is how we at columns to a table we type altar table, followed by the table name. And this will always take this when we're modifying tables in SQL off the table, followed by the table name and then to add a column, we type at column, followed by the column name and then followed by the data type. So let's run this query now and now you can see it was successful and we have a new column in our pet state table and I can show you that's true by typing the SQL Query Select Star from Pets. So this is going to select all the data and all the columns from the pets table. We're going to go through this select star from a table with much more detail later on in this video, and you can see it's what we got no rotor data in our table, but it's returned the I D species, full name and age columns as well as our new column. The owner I d. Now I said that I wanted to make this owner i d column of Foreign key, which is related to this I d filled in the onus table. So I can also do that by modifying our table using the altar table command. So to do that, I can write a new query down here. I can type o to table as we're modifying a table, followed by the table name. So I want to modify the pets table and I want to add a constrain, and I want to add a foreign key constraint. So I'm gonna add constrain. And now I need to follow that with a constraint name. So I'm gonna call it F k for Foreign Key. And then I'm just gonna say ona a pair, so underscore in underscore pair, and that's going to be the name of our foreign key constrain. And then on the next line, I can just like foreign key and we're gonna make the owner i d column off foreign key. So, in brackets, I need to type o na i d. And then I need to type references, so I need to type references, followed by the column name that this owner i d column is gonna be referencing, and it's gonna be referencing the i. D column in the owner's table. So first I need to type bonus. So owners table on and then brackets, I need to take the I D column. And then I need to end the query with Match full. And this just ensures that there's matching values between the owner I D column in the pet stable on the icy column in the owner's table. And then we can just finish this query with a semi colon. Andi if we run this, we've now created a foreign key in the pet stable when the owner I D column, which is referencing the I. D column in the owner's table. So now we have a relationship between the two tables. So this is how we at foreign keys to a table we type altar table, followed by the table name at constrain, as it's a foreign key constraint, we're adding to the table, followed by the constraint name, and we type foreign key with the column name that we want to create as the foreign key in brackets. And then we type references followed by the table that we're referencing, followed by the column that we're referencing Demi tight match full and end with the same code on. Okay, so we know how to create tables using the create table statement. We know how to modify tables using altar table. And then finally, we can also delete tables from the database. And to do that, we type drop table, followed by the table name. So if we wanted to delete the pet stable from the database, we could just type pets and run that query. But I'm not going to cause I don't want to drop. I don't dilly the pet stable on. If I wanted to delete the owners table, I could just try a type drop table followed by owners. Okay, so now we know how to create, modify and delete tables in post rescue role. It's actually insert some data into our pets and owners table. So to insert data into our tables in post Crisco, well, we need to write the SQL query starting with Insert into so we type insert into followed by our table name. So first I want enter data into our owners table, so we need to type insert into owners. Then we need to open a pair of brackets on within the brackets. We need to type the name of each column now, because the I. D column is a serial date site that's going to be automatically inserted for us. The values were that column, so we don't need to include that in our insert into statement. So the first column we need to write is our first name column, and then we need a comma. So each column should be comma separated, followed by our next column, which is last name, followed by a comma followed by city than a comma state. So we're gonna be inserting into the owners table, and we're gonna be inserting data for these four columns and then on the next line, weaken type values and open another pair of brackets. So within these brackets are gonna be the actual values that we're going to enter into our table for each of these columns. So the first owner is gonna be called Samuel on for words or characters. We need to have them in single quotes. But for numbers we can just have them without the single quays. So we need to type Samuel in single quotes and then his last name, which is gonna be Smith. And then the city is gonna be Boston, and then the state that he comes from is going to be Massachusetts. So I m a is the two letter abbreviation for Massachusetts on. We can end that query with a semi colon. And then if we run this, you can see that it was successful. And now, if we select star from owners to grab all the data which has been entered into the owners table, we're gonna look at select star from a table with more detail later. You can see we have our first entry in the owner's table. So we have a first name. Samuel, Last name. Smith City. Boston. Stay m A. And we also have this I, d field, which has been entered. And that's because the I d field was a serial data type, so it automatically gets entered, starting from one on auto incremental ing with each row of data which is entered into the table. So that's how we insert one row of data in a table, but we can actually insert multiple rows rows of data within one SQL query. So let's have a look at that now. So if we just make some space on copy and paste this insert into statement on, then just change. Just get rid of the values because we're gonna enter new values. We're now gonna enter four rows of data in one SQL query. So it's still the same. You still say Insert into owners, followed by the column names and then values on an open repair brackets, and we enter our new data. So our next owner is gonna be called Emma with a surname of Johnson. She's from Seattle, which is in Washington. Say, W A for the two letter state. And then that's our second row of data entered. And now we just need to add a comma, open a new pair of brackets and enter another set of data. So the next owner is gonna be called John with a surname off Oliver. He's from New York. Spell it correctly, which is in New York state. So N. Y. And then a calmer And if we just go to the next line we can add our third row of data. This is gonna be Olivia with the surname of Brown. She's gonna be from San Francisco, San Francisco, which is in the state of California. And then our final entry in the owner's table is gonna be Simon and he's gonna have a surname of Smith and he's from Houston, Texas T X. So to enter multiple rows of data in one SQL query, we just have to come a separate each entry of data, each row of data that we're entering. So now let's run this query and you can see it was successful When if we select star from owners again, you can see we now have five entries into the owners table and you can see this I d Field was automatically entered for us from 12345 So now we have the data in our owners table. We just need to enter the data in our pets table. So we're going to be entering 66 pets into our pets table. So that's come down here on right, Another insert into query. So this time you wanna type insert into pets and we want to open a pair brackets and tight our column Names in the pet stable So species full underscore Name age. Andi the owner I d on. Then on the next line, weaken type values on weaken tape each value in brackets again. So the 1st 1 is going to be a dog and he's gonna be cooled. Rex T R E x. He's going to be six years old, and he's gonna have a owner idea of one. And what this owner idea of one refers to is the first entry in the owner's table. So this dog Rex has the owner off Samuel Smith from Boston, Massachusetts. So this is what the owner I d. Is referencing in the pet stable. And then our second part is going to be a rabbit called Fluffy on. She's gonna have an age of free and own idea of five, which is Simon Smith from Houston. And then our next part is gonna be a cup. Tom and Tom is going to be eight years old and have owner idea of to on. Then the next pet is gonna be a mouse Cool Jerry with an age of three on dinner, idea of to, And then the next pair is gonna be another dog. Cool. Biggles and Biggles is gonna be four year four years old on have owner idea of one. And then our final pair is gonna be a tortoise toward toys called Squirtle on. They're gonna be 42 years old and they're gonna have a owner idea of free so you can see for strings or for characters. We need to have them in single quotes. But for integers, we don't need them in single quotes. Weaken just tightly into their own design. And if we run this insert into query on, then we can select star from the pets table and run that you can see now we have six rows off data in our pets table on the I. D. Feel. It has automatically been entered for us from 1 to 6. And we can also update data which has already been entered into our tables. So if we quickly select star from the owners table, we can see that Simon Smith is from Houston, Texas. But let's say that Simon Smith moved to Dallas in Texas. We would need to update this. Houston to Dallas in the owner's table, and we can do that through an update statement. So to update data, we need to update followed by the table name. So we want to update data in the owner's table, and then we need to type sets on. We're going to set a column to a certain value, so we're going to be setting the city column, so we need to type City equal to Dallas. But if we just run this like this, it would update every single value to Dallas, which is not what we want to do. We just want to update Houston to Dallas. So to do that, we need to also add a where clause. So this is going to say only update the city to Dallas. Where column. So let's say City is equal to Houston, and then we can end with a semi coat on. So this is gonna update the owners table is going to set the City column equal to Dallas, but only where City is already equal to Houston. So let's run this update statement on it was successful when if we select star from owners again on wait for this green successfully run to go away and you can see Simon Smith now has a city of Dallas. Whereas before it was Houston. So that's how we update data in a table. We can also update multiple rows of data at once. So free, select star from pets. You can see that our rabbit Fluffy has an age of free, and so does our mouths, Coach Jerry. So we actually want to update this to to, So we can do that by writing another update statement. So we need to type update followed by the table name. So it's pets, and we need to set the age column equal to two where the Age column is equal to three. So it's gonna look for wherever age is equal to free, and it's gonna set the new value to to. So if we run this now and then, if we go back up here, we can select star from pets again, and you can see now we have a rabbit and mouse which did have an age of free have no what an age of two. So that's how we update data in a table. We can also delete data from a table, so if we wanted to delete all the rays of data in our pets stable. We could type deletes from pets. And if we run this query, it's actually gonna duly every single row of data in the pet stable. But I don't want to do that, so I'm not gonna run the query. But this is how we delete all the rows of data from a table delete from table name. But if we only wanted to really certain rows of data, we can also add a where clause So we can say delete from pets where name is equal to Rex. And if we were to run, this query is only gonna delete this first row here where the full name. Sorry, that should say full name where full name is equal to Rex. And again we can delete multiple rows of data up in one statement. If we were to say, delete from pats where the species column is equal to dog and then with semi coat on, if we were to run, this query is gonna delete Rex because he has a species of dog. But it's also gonna tilly Biggles because he is also adopt. Okay, so Now we've inserted all our data into our database and we know how to update and delete data. Let's have a look at writing queries, which are going to retrieve data from our database. So to return data from a database, we used the select semen. So we've seen it before where we select star from a table. So select star from owners, and this star means that it's going return or the columns and all the rows of data in our owners table. So we're selecting everything from the owners table. So let's run that query and you can see it returns all five rows and order columns of data . But we can also just select individual columns so we can select, Let's say, first name. So the first name column from Onus. And if we run this now, it's just going to return our first name column. So Samuel image on Olivia on Simon on. We can come in Here and we can Ada's many columns as we want, as long as we commerce separate them so we can select first name and last name from minus. And like I said, we can select as many columns as we want. We just have to comma separate each column, they select first name. Call my last name comma state from Owners and you can see here we have the first name last name and state column returned. We can also filter which rows of data or returns using where clauses. So if we say select star from pats on run that query, you can see it returned. Six rows of data returns, basically all the data in our pet stable. So if we come in here in other wear clothes, we can say where a certain column is equal to a certain value. So let's say where the species column is equal to talk. And now, if we run this instead of returning all six ways, the data, it just returns to rows of data for the species equal to dog on. We can also do it for the numeric columns. So let's select species on full name from pats, where age is equal to two. So I noticed that our age column doesn't have to appear in the columns were selecting to be in the wear clothes. We can still use a where clause where age is equal to two and has returned our rabbit fluffy and our mouse Jerry who both of an age of two. And we can actually add as many where closes as we want into our select statements. So we can say where age is equal to two. And then we can also at another where clause by typing and where a column is equal to another value. So let's see where species is equal to rabbit. And if we run this, you can see it. Just returns are rabbit called fluffy, so it doesn't return our mouths school Jerry anymore. Because any returns rays of data which satisfy both where clauses So where age is equal to two andre species is equal to rabbit. And if we copy and paste this query rather than using and we can also use all. So if we say or species is equal to car and run this query now you can see it returned. Three resident eater So it returns are rabbit on our mouse because they have an age of two and it also returns are capped coltan because his species is equal to cap. So this is going to return Rosa Data, where age is equal to two or the species is equal to cut. So that's we have to wear clauses, but we can also have as many where closes as we want. So if we copy and paste that query again, we can say where O na idea is equal to two. Andi species equals car on, and full name to the full name column is equal to Tom, and if we run that, you can see it returns our cat call Tom so you can have as many Where closes as you want. You just have to keep typing, ands you anywhere close on, dure anywhere close. So far, we've seen where columns are equal to one value. But what if we wanted to have a where clause when the value of a column is equal to two or more values? Well, we can do that using the in statement. So let's select star from owners where the state column is in open a prayer brackets. And then let's put two separate values here. So let's say where state is in W A course state is in en Y on. If we just end with a semi colon and run that you can see it returns Emma Johnson from Seattle, Washington. He was a state of W. A, and it also returns John Oliver off New York with the state of N Y. So you can see here we said, where a column is in W A N y. So this is saying where state is either equal to W. A or equal tow N y. That row of data is 3. Class Summary and Project: congratulations for getting this far. Now you know all about sq and databases. It's time to put your newfound knowledge to the test with a project. So the project for this class is to write three SQL queries. The 1st 1 I want you to write is an SQL query to return the first and last name off the owner from the state of California. The second SQL query should return the names off the three oldest pets. And then the final SQL query I want you to write is I want you to write a joint query, which contains at least one where Klaus has at least one column name Alias on where the data is also ordered. So post your answers in the project section. And finally, I just like to say thank you for taking this class. I hope you enjoyed it and learned a lot. Thank you