Difference between revisions of "RDBMS-PostgreSQL/C2/Updating-Data/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with " {| border=1 | '''Time''' | '''Narration''' |- |00:01 | Welcome to the''' Spoken Tutorial''' on''' Updating Data''' |- |00:06 | In this tutorial we will learn about '''...")
 
Line 6: Line 6:
 
|-  
 
|-  
 
|00:01
 
|00:01
−
| Welcome to the''' Spoken Tutorial''' on''' Updating Data'''  
+
| Welcome to the''' Spoken Tutorial''' on''' Updating Data'''.
  
 
|-  
 
|-  
 
|00:06
 
|00:06
−
| In this tutorial we will learn about '''Update statement '''and '''Delete statement'''  
+
| In this tutorial, we will learn about '''Update''' statement and '''Delete''' statement.
  
 
|-  
 
|-  
 
| 00:13
 
| 00:13
−
| To record this tutorial, I am using  
+
| To record this tutorial, I am using:
  
−
'''Ubuntu Linux 14.04''' operating system  
+
'''Ubuntu Linux 14.04''' operating system,
  
 
|-  
 
|-  
Line 26: Line 26:
 
|-  
 
|-  
 
| 00:28
 
| 00:28
−
| To follow this tutorial, you should have  
+
| To follow this tutorial, you should have basic understanding of '''SELECT''' statement.  
−
 
+
−
Basic understanding of '''SELECT statement'''.  
+
  
 
|-  
 
|-  
Line 40: Line 38:
 
|-  
 
|-  
 
| 00:47
 
| 00:47
−
| We have to use the '''WHERE clause''' with the '''UPDATE statement''' to update the selected rows.  
+
| We have to use the '''WHERE clause''' with the '''UPDATE''' statement to update the selected rows.  
  
 
|-  
 
|-  
 
| 00:53
 
| 00:53
−
| The syntax for '''update statement''' is shown here.  
+
| The syntax for '''update''' statement is shown here.  
  
−
The '''columns''' to be modified are to be specified in the '''SET clause'''.  
+
The '''columns''' to be modified are to be specified in the '''SET''' clause.  
  
 
|-  
 
|-  
Line 54: Line 52:
 
|-  
 
|-  
 
| 01:12
 
| 01:12
−
| Other '''columns''' in the '''table''' will retain their previous values.  
+
| Other '''column'''s in the '''table''' will retain their previous values.  
  
 
|-  
 
|-  
 
| 01:17
 
| 01:17
−
| '''WHERE''' clause is used to find the exact row based on the specified '''condition''', to '''update''' it.  
+
| '''WHERE''' clause is used to find the exact row based on the specified '''condition''' to '''update''' it.  
  
 
|-  
 
|-  
 
| 01:24
 
| 01:24
−
| If no '''conditions''' are provided, then all '''records '''in the '''table''' will be updated.  
+
| If no conditions are provided then all '''record'''s in the '''table''' will be updated.  
  
 
|-  
 
|-  
Line 70: Line 68:
 
|-  
 
|-  
 
| 01:34
 
| 01:34
−
| Now right-click on '''students''' node, then on '''View data''' and then on '''View''' '''All rows.'''  
+
| Now, right-click on '''students''' node, then on '''View data''' and then on '''View All rows.'''  
  
 
|-  
 
|-  
 
| 01:43
 
| 01:43
−
| Notice here - it says the student whose id is ''''s008'''' is from ''''Chennai'''' city.  
+
| Notice here - it says the student whose '''id''' is 's008', is from ''''Chennai'''' city.  
  
 
|-  
 
|-  
Line 82: Line 80:
 
|-  
 
|-  
 
|01:56
 
|01:56
−
| Now switch to the '''SQL editor''' window and type the '''update statement, '''as shown here.  
+
| Now, switch to the '''SQL editor''' window and type the '''update''' statement as shown here.  
  
 
|-  
 
|-  
Line 90: Line 88:
 
|-  
 
|-  
 
| 02:10
 
| 02:10
−
| The existing '''city '''value ''''Chennai'''' will be changed to ''''Bangalore'''' for the '''studentid s008''''.  
+
| The existing '''city '''value ''''Chennai'''' will be changed to ''''Bangalore'''' for the '''studentid  
 +
s008'''.  
  
 
|-  
 
|-  
 
|02:18
 
|02:18
−
| Execute the query.  
+
| '''Execute''' the '''query'''.  
  
 
We can see a message that the query is successful.  
 
We can see a message that the query is successful.  
Line 100: Line 99:
 
|-  
 
|-  
 
| 02:24
 
| 02:24
−
| Let's retrieve the record to see the changes.  
+
| Let's retrieve the '''record''' to see the changes.  
  
 
|-  
 
|-  
 
| 02:28
 
| 02:28
−
| Clear the editor window and type this '''select statement.'''  
+
| Clear the editor window and type this '''select''' statement.
  
 
|-  
 
|-  
Line 110: Line 109:
 
| Now, execute the query to see the output.  
 
| Now, execute the query to see the output.  
  
−
Note the updated value in the '''city''' field for student ‘'''s008’.'''  
+
Note the updated value in the '''city''' field for student '''‘s008’.'''  
  
 
|-  
 
|-  
 
| 02:45
 
| 02:45
−
|Next we will see how to update multiple '''columns'''.  
+
|Next we will see how to update multiple columns.  
  
 
|-  
 
|-  
 
| 02:50
 
| 02:50
−
| Type the code. We can also update multiple '''columns '''by separating the '''column '''and value pairs, with commas, as shown here.  
+
| Type the code. We can also update multiple columns by separating the '''column '''and value pairs, with commas, as shown here.  
  
 
|-  
 
|-  
 
| 03:02
 
| 03:02
−
| Remember, the '''date of birth column '''was '''null''' for the student ''''Ram charan''''.  
+
| Remember, the '''date of birth''' column was '''null''' for the student 'Ram charan'.  
  
 
|-  
 
|-  
 
| 03:08
 
| 03:08
−
| This will update two '''columns, date of birth '''and '''city,''' with the values given in the '''update statement.'''  
+
| This will update two columns, '''date of birth '''and '''city,''' with the values given in the '''update statement.'''  
  
 
|-  
 
|-  
 
| 03:16
 
| 03:16
−
| What will happen if you forget to give the '''where condition'''?  
+
| What will happen if you forget to give the '''where''' condition?  
  
 
|-  
 
|-  
Line 142: Line 141:
 
|-  
 
|-  
 
| 03:28
 
| 03:28
−
|Let us see the updated value for the student name ‘'''Ram Charan’'''
+
|Let us see the updated value for the student name ‘Ram Charan’.
  
 
|-  
 
|-  
 
|03:33
 
|03:33
−
|Type the '''select statement''' as shown here.  
+
|Type the '''select''' statement as shown here.  
  
 
|-  
 
|-  
Line 152: Line 151:
 
|Execute the query.  
 
|Execute the query.  
  
−
We can see the '''date of birth column''' and '''city column''' has been updated.  
+
We can see the '''date of birth''' column and '''city''' column have been updated.  
  
 
|-  
 
|-  
 
| 03:46
 
| 03:46
−
| Next we will learn about the''' delete statement.'''  
+
| Next we will learn about the''' delete''' statement.
  
 
|-  
 
|-  
 
|03:50
 
|03:50
−
| The syntax for '''delete statement''' is as follows:  
+
| The syntax for '''delete''' statement is as follows:  
  
 
'''DELETE FROM table-name '''  
 
'''DELETE FROM table-name '''  
Line 168: Line 167:
 
|-  
 
|-  
 
| 03:59
 
| 03:59
−
| '''WHERE clause''' is used in the '''delete statement''' to delete specific rows which satisfy a particular '''condition'''.  
+
| '''WHERE''' clause is used in the '''delete''' statement to delete specific rows which satisfy a particular '''condition'''.  
 
   
 
   
 
|-  
 
|-  
 
| 04:06
 
| 04:06
−
| All records will be deleted if no '''conditions '''are specified.''' '''
+
| All records will be deleted if no '''conditions '''are specified.  
  
 
|-  
 
|-  
Line 192: Line 191:
 
|Let us execute the query.  
 
|Let us execute the query.  
  
−
So one row is deleted now.  
+
So, one row is deleted now.  
  
 
|-  
 
|-  
 
| 04:34
 
| 04:34
−
| If you want to remove all the rows from the '''students table''', type '''delete from students'''.  
+
| If you want to remove all the rows from the '''students''' table, type '''delete from students'''.  
  
 
|-  
 
|-  
Line 202: Line 201:
 
| There is no '''Where''' condition is specified here.  
 
| There is no '''Where''' condition is specified here.  
  
−
I'll not execute the query, as I don’t want to delete all the record.  
+
I'll not execute the query, as I don’t want to delete all the records.  
  
 
|-  
 
|-  
Line 224: Line 223:
 
|-  
 
|-  
 
| 05:18
 
| 05:18
−
| In the output, we can see ‘'''0 rows affected'''’.  
+
| In the output, we can see '''‘0 rows affected’'''.  
 
   
 
   
 
This indicates that no records are deleted.  
 
This indicates that no records are deleted.  
Line 256: Line 255:
 
|-  
 
|-  
 
|06:10
 
|06:10
−
| In this tutorial, we have learnt about the -  '''Update statement '''and  '''Delete statement'''  
+
| In this tutorial, we have learnt about the -  '''Update''' statement and  '''Delete''' statement.
  
 
|-  
 
|-  
 
| 06:17
 
| 06:17
−
| As an assignment, For '''studentid 's013'''', modify the student name from ''''Sharmila'''' to ''''Sharmila Babu'''' in the '''students table '''  
+
| As an assignment, for '''studentid 's013'''', modify the student name from ''''Sharmila'''' to ''''Sharmila Babu'''' in the '''students''' table. 
  
 
|-  
 
|-  
 
| 06:28
 
| 06:28
−
| Delete the students whose '''cgpa''' is less than 5  
+
| Delete the students whose '''cgpa''' is less than 5.
  
 
|-  
 
|-  
 
|06:33
 
|06:33
−
| The video at the following link summarises the Spoken Tutorial project.  
+
| The video at the following link summarizes the '''Spoken Tutorial''' project.  
  
 
Please download and watch it.  
 
Please download and watch it.  
Line 274: Line 273:
 
|-  
 
|-  
 
|06:40
 
|06:40
−
| The''' Spoken Tutorial Project''' Team
+
| The''' Spoken Tutorial Project''' team conducts workshops and  gives certificates.
−
 
+
−
conducts workshops and  gives certificates  
+
  
 
For more details, please write to us.  
 
For more details, please write to us.  
Line 286: Line 283:
 
|-  
 
|-  
 
| 06:53
 
| 06:53
−
| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.  
+
| Spoken Tutorial project is funded by '''NMEICT, MHRD''', Government of India.  
  
 
More information on this mission is available at this link.  
 
More information on this mission is available at this link.  
Line 292: Line 289:
 
|-  
 
|-  
 
| 07:05
 
| 07:05
−
| This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.  
+
| This is Nirmala Venkat from '''IIT Bombay''', signing off. Thanks for watching.  
  
 
|}
 
|}

Revision as of 17:44, 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.

Contributors and Content Editors

PoojaMoolya, Sandhya.np14

Retrieved from "https://script.spoken-tutorial.org/index.php?title=RDBMS-PostgreSQL/C2/Updating-Data/English-timed&oldid=45710"