Difference between revisions of "RDBMS-PostgreSQL/C2/Updating-Data/English-timed"
From Script | Spoken-Tutorial
Sandhya.np14 (Talk | contribs) |
Sandhya.np14 (Talk | contribs) |
||
| Line 88: | Line 88: | ||
|- | |- | ||
| 02:10 | | 02:10 | ||
| − | | The existing '''city '''value ''''Chennai'''' will be changed to ''''Bangalore'''' for the '''studentid | + | | The existing '''city '''value ''''Chennai'''' will be changed to ''''Bangalore'''' for the '''studentid''' |
| − | s008'''. | + | '''s008'''. |
|- | |- | ||
Revision as of 17:45, 6 February 2019
| Time | Narration |
| 00:01 | Welcome to the Spoken Tutorial on Updating Data. |
| 00:06 | In this tutorial, we will learn about Update statement and Delete statement. |
| 00:13 | To record this tutorial, I am using:
Ubuntu Linux 14.04 operating system, |
| 00:20 | PostgreSQL 9.3.x
and pgAdmin 1.18 |
| 00:28 | To follow this tutorial, you should have basic understanding of SELECT statement. |
| 00:35 | For more details, refer to the RDBMS PostgreSQL series on this website. |
| 00:41 | The update query is used to modify the existing records in a table. |
| 00:47 | We have to use the WHERE clause with the UPDATE statement to update the selected rows. |
| 00:53 | The syntax for update statement is shown here.
The columns to be modified are to be specified in the SET clause. |
| 01:03 | Column1 would be assigned the value1 and Column2 would be assigned the value2 and so on. |
| 01:12 | Other columns in the table will retain their previous values. |
| 01:17 | WHERE clause is used to find the exact row based on the specified condition to update it. |
| 01:24 | If no conditions are provided then all records in the table will be updated. |
| 01:30 | Let's switch to pgAdmin main screen. |
| 01:34 | Now, right-click on students node, then on View data and then on View All rows. |
| 01:43 | Notice here - it says the student whose id is 's008', is from 'Chennai' city. |
| 01:51 | Now I want to change the city to 'Bangalore' for this student. |
| 01:56 | Now, switch to the SQL editor window and type the update statement as shown here. |
| 02:05 | This is a simple update statement which updates a single column. |
| 02:10 | The existing city value 'Chennai' will be changed to 'Bangalore' for the studentid
s008. |
| 02:18 | Execute the query.
We can see a message that the query is successful. |
| 02:24 | Let's retrieve the record to see the changes. |
| 02:28 | Clear the editor window and type this select statement. |
| 02:34 | Now, execute the query to see the output.
Note the updated value in the city field for student ‘s008’. |
| 02:45 | Next we will see how to update multiple columns. |
| 02:50 | Type the code. We can also update multiple columns by separating the column and value pairs, with commas, as shown here. |
| 03:02 | Remember, the date of birth column was null for the student 'Ram charan'. |
| 03:08 | This will update two columns, date of birth and city, with the values given in the update statement. |
| 03:16 | What will happen if you forget to give the where condition? |
| 03:20 | The entire students table will be updated with the given value. |
| 03:25 | Execute the query. |
| 03:28 | Let us see the updated value for the student name ‘Ram Charan’. |
| 03:33 | Type the select statement as shown here. |
| 03:37 | Execute the query.
We can see the date of birth column and city column have been updated. |
| 03:46 | Next we will learn about the delete statement. |
| 03:50 | The syntax for delete statement is as follows:
DELETE FROM table-name [WHERE condition] |
| 03:59 | WHERE clause is used in the delete statement to delete specific rows which satisfy a particular condition. |
| 04:06 | All records will be deleted if no conditions are specified. |
| 04:11 | Let us see an example of the delete statement. |
| 04:15 | Switch to the SQL Editor window. |
| 04:18 | Type the code as shown here.
This statement will delete the student record whose id is 's014'. |
| 04:28 | Let us execute the query.
So, one row is deleted now. |
| 04:34 | If you want to remove all the rows from the students table, type delete from students. |
| 04:40 | There is no Where condition is specified here.
I'll not execute the query, as I don’t want to delete all the records. |
| 04:50 | Next let us see an example where we have two conditions in the DELETE statement. |
| 04:57 | Type the code as shown here. |
| 05:01 | This delete statement will delete the student details which satisfies two different conditions.
That is, the department should be 'CS' and CGPA score should be less than 7.5 |
| 05:15 | Let us execute this query. |
| 05:18 | In the output, we can see ‘0 rows affected’.
This indicates that no records are deleted. |
| 05:27 | That means there are no records in the student table which satisfies both the conditions. |
| 05:34 | With this we have covered the basics of common SQL statements such as
INSERT , SELECT , DELETE and UPDATE in this series. |
| 05:47 | These statements are otherwise called as Data Manipulation language.
DML is the short form of Data Manipulation Language. |
| 05:58 | It includes commands that are used to manipulate data in a database. |
| 06:04 | With this we come to the end of this tutorial.
Let us summarize. |
| 06:10 | In this tutorial, we have learnt about the - Update statement and Delete statement. |
| 06:17 | As an assignment, for studentid 's013', modify the student name from 'Sharmila' to 'Sharmila Babu' in the students table. |
| 06:28 | Delete the students whose cgpa is less than 5. |
| 06:33 | The video at the following link summarizes the Spoken Tutorial project.
Please download and watch it. |
| 06:40 | The Spoken Tutorial Project team conducts workshops and gives certificates.
For more details, please write to us. |
| 06:49 | Please post your timed queries in this forum. |
| 06:53 | Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.
More information on this mission is available at this link. |
| 07:05 | This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching. |
