In this video, you'll learn how to view some records in your table in Postgres, how to update some data, and how to delete data from a table. This is a continuation of Part 1, so watch that video as well if you haven't seen it. Let's get started by opening PG admin, the SQL editor that we're using to work with the Postgres database. Open a new tab for writing SQL by clicking this button here. In the Part 1 video, we created a table and added some data. We can run a select statement on this table to see what's inside. We can run the query and see the results here. I've added a link in the description to the SQL script that you can use to create this table if you want to follow along. The select star command will select all columns in our table. What if we only want to select some columns? We can do this by specifying the column names instead of star. To select both the product name and price columns, we can write this query. Select product name, price from product. We don't have to specify a star to get all columns. We can specify each of the column headings. We can run this query and see all of the columns and rows in the table. If we only want to see the product name, we can change the query to just select the product name. We can run the query now and see the results. It shows only the product name and not the price. We can also select only the price column like this. We can run this query and see only the price column. The order of columns can be changed as well. Let's say we want to show both columns, but we want to see the price first and then the product name. We can write a query that selects the price than the product name. Run this query and we can see the results here, which shows the price column first, then the product name. In each of our queries so far, we've seen all rows in the table. What if we only want to see some of the rows? We can do this by adding another keyword to our query. This keyword is called where. It allows us to specify the criteria to filter our results. We add some code after the where keyword or where clause. Any rows that meet the criteria are shown and any rows that don't meet the criteria are not shown. Let's update our query. We add the where keyword at the end of our query here. Make sure you remove the semicolon if you've added one as we don't want to end the query yet. After the where keyword we specify the column we want to filter on. Let's say we want to show records where the product name is genes. So we add the column of product name. Then we add an equal sign. Then we add the word genes. This means that only products with the product name of genes will be shown. We can run the query here but we get an error. It says error column genes does not exist. It also tells us the line number and SQL code that causes the error. If we look at our SQL again, we can see that the word genes has a red underline. What does this mean? Well, the error happens because post-crest thinks that the word genes is a column in our table. It can't find the column called genes so it shows an error. But it's not a column. It's a value we want to use for checking our product name. So how can we fix this? In SQL, whenever we want to write a text value that is not a database object like a column, we need to enclose it in single quotes. This tells the database to treat it as a text value and not try to look for a column or a table or something else with that name. So we can enclose the word genes in single quotes. We can see in PG admin that the color of the text has changed as well. We can run this query and see the results. We can see that only one record is shown, which is the record with the product name of genes. So we can write code in the where clause of the select statement to limit the rows that are shown. Let's see another example. We want to see all products that have a price greater than 80. To do this, we can change our where clause to check the price column. We can change the equals to greater than and change the genes to a value of 80. This means we check for records where the price is greater than 80. We don't need to enclose the value of 80 inside quotes because numbers are always treated as numbers and the database doesn't try to check that they are a name of a table or a column. We can run this query and see the results. We can see one row here, which is the dress with a price of 100. This is shown because the value of 100 is greater than 80. We don't have to use the equal sign. We can use other operators such as greater than, less than, greater than or equal to, less than or equal to and not equal. We've seen how we can view data that meets different criteria. What if we want to make changes to the data after we have inserted it into a table? We can do that in Postgres and SQL in general by using the update statement. The update statement lets you update data in a table. You can specify the new value for a column and the criteria for the rows to be updated. We start with the word update. We then specify the table name, which is product. Then we add the set keyword, which specifies the data that will be updated. What do we want to update? Let's say we want to change the price of the genes product to 60, which currently has a value of 50. So after the set keyword, we add the column name, which is price. Then we add an equal sign. Then we add the value we want to set this column to. In our example, we specify 60. Now let's see what happens when we run this statement. The output here indicates the query was successful. The data has been updated. Let's run a select statement on the data to see what it looks like. We can run the same query as before. The results are shown here. Uh oh. We can see that the price is set to 60 for both rows. We only wanted to set it for the genes product, not the dress product. This is an important thing to know about the update statement. You can specify which rows you want to update, and this is done using the where clause. But the where clause is optional. You can write and run an update statement without a where clause, and it will run and update all records in the table. So let's fix our data and add a where clause to the update statement. We can update the dress product to change the price to 100, which was the original value. We have an update statement that sets the price to 100. Then we add where, and specify the product name column, then equals, then dress in single quotes. This means the price will only be updated for that row. Run this statement and we can see it is successful. We can run the select statement to see all records, and see that the genes product has a price of 60, and dress has a price of 100. So that's how you can use the update statement to update data. The final thing we'll cover in this video is deleting data. How can we remove a record from a table? You might want to do this if you've added a record by mistake, or want to remove all rows, from a table. This can be done with SQL using the delete statement. We start with the delete statement, then the word from. Then we add the table name, which is product. Now just like the update statement, we should specify the criteria for the rows. Adding a where clause will let a specify which rows to delete. However, it's optional, so you can run a delete statement without a where clause. If you do, all rows will be deleted. In our situation, we don't want to delete all rows. Let's say we want to delete the genes product. So we add a where clause to say where product name equals genes. Now we run this statement and see it is successful. We can select from the table and see that there is only one record. We have the dress record here, but the genes record is not shown because it was deleted. If we want the genes record back in the table, we can insert it again using the insert statement that we learned at the table. So now we've learned how to show some columns in the table, show only some of the rows in the table, update data in the table, and delete records from the table. Check out the next lesson for more about making changes to the table. If you want to learn more about database design at SQL, visit my website at database.com. If you like this video, consider subscribing to my channel. Thanks for watching.