Difference between revisions of "RDBMS-PostgreSQL/C2/Table-with-primary-keys/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''' Table with primary keys''' |- |00:06 |In this tutorial we will learn t...")
 
 
Line 6: Line 6:
 
|-  
 
|-  
 
|00:01
 
|00:01
−
|Welcome to the''' Spoken Tutorial''' on''' Table with primary keys'''  
+
|Welcome to the''' Spoken Tutorial''' on''' Table with primary keys'''.
  
 
|-  
 
|-  
 
|00:06
 
|00:06
−
|In this tutorial we will learn to  
+
|In this tutorial, we will learn to:
  
−
Insert data  
+
insert data,
  
−
Retrieve data  
+
retrieve data,
  
 
|-  
 
|-  
 
|00:13
 
|00:13
−
| '''Data Redundancy'''  
+
| '''data redundancy''',
  
−
Importance of '''primary keys '''and Create a '''table '''with '''primary keys'''  
+
importance of '''primary keys '''and create a '''table '''with '''primary keys'''.
  
 
|-  
 
|-  
 
|00:21
 
|00:21
−
|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,
  
 
'''PostgreSQL 9.3.x '''and  
 
'''PostgreSQL 9.3.x '''and  
  
−
'''pgAdmin 1.18'''  
+
'''pgAdmin 1.18'''.
  
 
|-  
 
|-  
 
| 00:36
 
| 00:36
−
|To follow this tutorial, you should have Good understanding of basic computer concepts and  
+
|To follow this tutorial, you should have good understanding of basic computer concepts and  
  
 
|-  
 
|-  
 
|00:43
 
|00:43
−
| Working knowledge of any programming language.  
+
| working knowledge of any programming language.  
  
 
|-  
 
|-  
Line 48: Line 48:
 
|Ensure that you have connected to the '''localhost''' '''server.'''  
 
|Ensure that you have connected to the '''localhost''' '''server.'''  
  
−
Select the ''''Sampledb'''' '''database'''.  
+
Select the ''''Sampledb'''' database.  
  
 
|-  
 
|-  
 
|00:59
 
|00:59
−
|Click on the '''students table'''  and then click on '''Columns''' node.  
+
|Click on the ''''students' table'''  and then click on '''Columns''' node.  
  
 
|-  
 
|-  
 
|01:06
 
|01:06
−
|We can see the '''students table''' with 5 columns which we created earlier.  
+
|We can see the '''students''' table with 5 columns which we created earlier.  
  
 
The structure of the '''table''' is ready.  
 
The structure of the '''table''' is ready.  
Line 66: Line 66:
 
|-  
 
|-  
 
|01:20
 
|01:20
−
|Right-click on the '''students table'''.  
+
|Right-click on the '''students''' table.  
  
−
From the drop-down menu, select '''Scripts '''and''' '''then select '''INSERT script.'''  
+
From the drop-down menu, select '''Scripts '''and then select '''INSERT script.'''  
  
 
|-  
 
|-  
 
|01:29
 
|01:29
−
|The '''SQL Editor''' window opens up with the basic syntax for an '''Insert statement.'''  
+
|The '''SQL Editor''' window opens up with the basic syntax for an '''Insert''' statement.
  
 
|-  
 
|-  
 
|01:36
 
|01:36
−
|'''Insert statement''' inserts one or more rows into a '''table'''.  
+
|'''Insert''' statement inserts one or more rows into a '''table'''.  
  
 
|-  
 
|-  
Line 90: Line 90:
 
|Type the values as shown.  
 
|Type the values as shown.  
  
−
Here I have entered a single row of data for the student ''''Ram''''.  
+
Here I have entered a single row of data for the student 'Ram'.  
  
 
|-  
 
|-  
Line 104: Line 104:
 
|-  
 
|-  
 
| 02:21
 
| 02:21
−
|Next we will execute the '''insert statement.'''  
+
|Next we will '''execute''' the '''insert''' statement.
  
 
|-  
 
|-  
 
|02:25
 
|02:25
−
|Click on the triangle icon in the toolbar to execute the query.  
+
|Click on the triangle icon in the toolbar to '''execute''' the '''query'''.  
  
 
|-  
 
|-  
Line 116: Line 116:
 
|-  
 
|-  
 
|02:36
 
|02:36
−
|This indicates that one row of data is inserted into the '''students table'''.  
+
|This indicates that one row of data is inserted into the '''students''' table.  
  
 
|-  
 
|-  
 
| 02:42
 
| 02:42
−
|I’ll insert one more record.  
+
|I’ll insert one more '''record'''.  
  
 
|-  
 
|-  
Line 138: Line 138:
 
|-  
 
|-  
 
|03:03
 
|03:03
−
|Then type, '''Select asterisk from'''  
+
|Then type: '''Select asterisk from'''  
  
 
|-  
 
|-  
Line 148: Line 148:
 
|A list of available tables appears as shown.  
 
|A list of available tables appears as shown.  
  
−
Select the '''Students table. '''  
+
Select the '''Students''' table. 
  
 
|-  
 
|-  
Line 160: Line 160:
 
|-  
 
|-  
 
|03:33
 
|03:33
−
|This statement will fetch all the values from the '''students''' '''table'''.  
+
|This statement will fetch all the values from the '''students''' table.  
  
 
|-  
 
|-  
Line 168: Line 168:
 
|-  
 
|-  
 
|03:44
 
|03:44
−
|We can see the output in a tabular form, in the output pane.  
+
|We can see the '''output''' in a tabular form, in the output pane.  
  
 
|-  
 
|-  
Line 180: Line 180:
 
|-  
 
|-  
 
|04:01
 
|04:01
−
|We can insert multiple rows in a single '''insert statement.'''  
+
|We can insert multiple rows in a single '''insert''' statement.
  
 
|-  
 
|-  
 
|04:06
 
|04:06
−
|Here I have entered the information of the students ''''Ram', 'Kishore' '''and ‘'''Pooja'''’.  
+
|Here, I have entered the information of the students 'Ram', 'Kishore' and ‘Pooja’.  
  
 
|-  
 
|-  
 
|04:13
 
|04:13
−
|Let us save the script of this '''Insert statement''' for future use.  
+
|Let us '''save''' the script of this '''Insert''' statement for future use.  
  
 
|-  
 
|-  
Line 200: Line 200:
 
|-  
 
|-  
 
|04:28
 
|04:28
−
|Select the folder where you want to save the file. I'll select '''Desktop'''.  
+
|Select the folder where you want to '''save''' the file. I'll select '''Desktop'''.  
  
 
|-  
 
|-  
 
|04:35
 
|04:35
−
|By default, the file will always be saved with '''dot sql''' extension.  
+
|By default, the file will always be saved with ''''dot sql' extension'''.  
  
 
|-  
 
|-  
Line 218: Line 218:
 
|In the output pane, we can see the message '''“3 rows affected”.'''  
 
|In the output pane, we can see the message '''“3 rows affected”.'''  
  
−
This indicates that three records are inserted in the '''students table.'''  
+
This indicates that three records are inserted in the '''students''' table.
  
 
|-  
 
|-  
Line 250: Line 250:
 
|Each student is unique and they need to be identified with some unique attribute.  
 
|Each student is unique and they need to be identified with some unique attribute.  
  
−
For example, student id will be unique for each student.  
+
For example, student-id will be unique for each student.  
  
 
|-  
 
|-  
Line 258: Line 258:
 
|-  
 
|-  
 
|05:55
 
|05:55
−
|'''Primary key''' is a column or a set of columns, that uniquely identifies each row in the '''table'''.  
+
|'''Primary key''' is a column or a set of columns that uniquely identifies each row in the '''table'''.  
  
 
|-  
 
|-  
 
|06:03
 
|06:03
−
|Follow these rules when you define a '''primary''' '''key''' for a '''table'''.  
+
|Follow these rules when you define a '''primary key''' for a '''table'''.  
  
 
|-  
 
|-  
Line 280: Line 280:
 
|-  
 
|-  
 
|06:26
 
|06:26
−
|For example
+
|For example-
  
 
|-  
 
|-  
 
|06:32
 
|06:32
−
|Here, there a duplicate row of data for the student name ‘Kishore’  
+
|here, there is a duplicate row of data for the student name ‘Kishore’.
  
 
|-  
 
|-  
Line 292: Line 292:
 
|-  
 
|-  
 
|06:43
 
|06:43
−
|First I demonstrated to insert a single record 2 times and then I inserted 3 records.  
+
|First, I demonstrated to insert a single record 2 times and then I inserted 3 records.  
  
−
The duplication of data is Data redundancy.  
+
The duplication of data is '''Data redundancy'''.  
  
 
|-  
 
|-  
Line 302: Line 302:
 
|-  
 
|-  
 
|07:02
 
|07:02
−
|More information on '''Data redundancy''' is given in the '''Additional material '''link, of this tutorial.  
+
|More information on '''Data redundancy''' is given in the '''Additional material '''link of this tutorial.  
  
 
|-  
 
|-  
Line 312: Line 312:
 
|-  
 
|-  
 
| 07:18
 
| 07:18
−
|Now I’ll show how to add a primary key.  
+
|Now I’ll show how to add a '''primary key'''.  
  
 
|-  
 
|-  
Line 320: Line 320:
 
|-  
 
|-  
 
|07:26
 
|07:26
−
|Let me copy the script of '''create table statement '''and paste in the '''SQL Editor.'''  
+
|Let me copy the script of '''create table''' statement and paste in the '''SQL Editor.'''  
  
 
|-  
 
|-  
 
|07:32
 
|07:32
−
|This is the code which we executed to create the '''students table.'''  
+
|This is the '''code''' which we executed to create the '''students''' table.
  
 
|-  
 
|-  
 
|07:38
 
|07:38
−
|Can we add a '''student name''' column as the '''primary''' '''key''' to identify each student?  
+
|Can we add a '''student name''' column as the '''primary key''' to identify each student?  
  
 
|-  
 
|-  
Line 348: Line 348:
 
|-  
 
|-  
 
|08:09
 
|08:09
−
|I have added a new column called '''studentid,''' which will store a unique code for each student.  
+
|I have added a new column called '''studentid''' which will store a unique code for each student.  
  
 
|-  
 
|-  
Line 354: Line 354:
 
|I have defined '''studentid''' as the '''primary key.'''  
 
|I have defined '''studentid''' as the '''primary key.'''  
  
−
Now let’s execute the query.  
+
Now let’s '''execute''' the '''query'''.  
  
 
|-  
 
|-  
 
|08:24
 
|08:24
−
|We can see an error saying “ '''relation 'students' already exists'.'''  
+
|We can see an error saying '''"relation 'students' already exists".'''  
  
 
|-  
 
|-  
Line 374: Line 374:
 
|-  
 
|-  
 
|08:47
 
|08:47
−
|Then we will again create the '''students table '''with '''primary key constraints'''.  
+
|Then we will again create the '''students''' table with '''primary key constraints'''.  
  
 
|-  
 
|-  
Line 382: Line 382:
 
|-  
 
|-  
 
| 08:56
 
| 08:56
−
|Right-click on the '''students table'''.  
+
|Right-click on the '''students''' table.  
  
 
From the drop-down menu, select '''Delete/Drop.'''  
 
From the drop-down menu, select '''Delete/Drop.'''  
Line 394: Line 394:
 
|-  
 
|-  
 
| 09:10
 
| 09:10
−
|This removes a '''table definition''' and all its associated data.  
+
|This removes a '''table''' definition and all its associated data.  
  
 
|-  
 
|-  
Line 410: Line 410:
 
|-  
 
|-  
 
| 09:30
 
| 09:30
−
|Now let us create the '''students table''' with '''student id '''as '''primary key.'''  
+
|Now, let us create the '''students table''' with '''student id '''as '''primary key.'''  
  
 
|-  
 
|-  
Line 436: Line 436:
 
|-  
 
|-  
 
|10:10
 
|10:10
−
|Next I’ll pass a unique id for each student.  
+
|Next I’ll pass a unique '''id''' for each student.  
  
 
|-  
 
|-  
 
|10:15
 
|10:15
−
|I’ll add another record for student '''Ram''' with unique student id as shown.  
+
|I’ll add another '''record''' for student '''Ram''' with unique '''student id''' as shown.  
  
 
Note there is no duplicate row of data.  
 
Note there is no duplicate row of data.  
Line 460: Line 460:
 
|-  
 
|-  
 
|10:47
 
|10:47
−
|We can see the error message '''“duplicate key value violates unique constraint”'''  
+
|We can see the error message '''“duplicate key value violates unique constraint”'''.
  
 
|-  
 
|-  
 
| 10:54
 
| 10:54
−
|It shows an error which indicates the duplication of data.  
+
|It shows an '''error''' which indicates the duplication of data.  
 
   
 
   
 
Hence the '''primary key '''helps to avoid the data redundancy in a '''table'''.  
 
Hence the '''primary key '''helps to avoid the data redundancy in a '''table'''.  
Line 482: Line 482:
 
|-  
 
|-  
 
|11:19
 
|11:19
−
|In this tutorial, we have learnt about  
+
|In this tutorial, we have learnt about:
  
−
Inserting data  
+
inserting data,
  
−
Retrieving data  
+
retrieving data,
  
 
|-  
 
|-  
 
|11:25
 
|11:25
−
| '''Data Redundancy'''  
+
| '''data redundancy''',
  
−
Importance of '''Primary keys'''  
+
importance of '''Primary keys''',
  
−
Creating '''table''' with '''primary keys'''  
+
creating '''table''' with '''primary keys'''.
  
 
|-  
 
|-  
 
|11:32
 
|11:32
−
|As an Assignment, Drop the '''Emp table''' which was created in an earlier assignment in this series.  
+
|As an Assignment, '''drop''' the '''Emp table''' which was created in an earlier assignment in this series.  
  
 
|-  
 
|-  
Line 514: Line 514:
 
|-  
 
|-  
 
|11:55
 
|11:55
−
|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 520: Line 520:
 
|-  
 
|-  
 
|12:03
 
|12:03
−
|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 532: Line 530:
 
|-  
 
|-  
 
|12:16
 
|12:16
−
|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 538: Line 536:
 
|-  
 
|-  
 
| 12:29
 
| 12:29
−
|This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.  
+
|This is Nirmala Venkat from '''IIT Bombay,''' signing off. Thanks for watching.  
  
 
|}
 
|}

Latest revision as of 12:49, 5 February 2019

Time Narration
00:01 Welcome to the Spoken Tutorial on Table with primary keys.
00:06 In this tutorial, we will learn to:

insert data,

retrieve data,

00:13 data redundancy,

importance of primary keys and create a table with primary keys.

00:21 To record this tutorial, I am using:

Ubuntu Linux 14.04 operating system,

PostgreSQL 9.3.x and

pgAdmin 1.18.

00:36 To follow this tutorial, you should have good understanding of basic computer concepts and
00:43 working knowledge of any programming language.
00:48 Let us open pgAdmin.
00:51 Ensure that you have connected to the localhost server.

Select the 'Sampledb' database.

00:59 Click on the 'students' table and then click on Columns node.
01:06 We can see the students table with 5 columns which we created earlier.

The structure of the table is ready.

01:15 Let us learn how to insert some data into this table.
01:20 Right-click on the students table.

From the drop-down menu, select Scripts and then select INSERT script.

01:29 The SQL Editor window opens up with the basic syntax for an Insert statement.
01:36 Insert statement inserts one or more rows into a table.
01:42 The values to be inserted must match the order of the columns and the datatype of each column.
01:50 That is - column 1 takes the value 1 and column 2 takes the value 2 and so on.
01:58 Type the values as shown.

Here I have entered a single row of data for the student 'Ram'.

02:06 The order of the column name should match the values.
02:10 Note that character data type values are always enclosed in quotes.

And numeric data type values are not enclosed within quotes.

02:21 Next we will execute the insert statement.
02:25 Click on the triangle icon in the toolbar to execute the query.
02:30 We can see the message 'Query returned successfully: One row affected'.
02:36 This indicates that one row of data is inserted into the students table.
02:42 I’ll insert one more record.
02:45 Execute the query.

I have inserted 2 records now.

02:52 Let us now retrieve the data which we have inserted, from the table.
02:57 Click on the “Clear edit window” icon in the toolbar to clear the previous statement.
03:03 Then type: Select asterisk from
03:07 Here we have to specify the table name. Press Ctrl and spacebar together.
03:15 A list of available tables appears as shown.

Select the Students table.

03:23 Here, Select is a query command which is used to retrieve data from the tables.
03:29 Asterisk means all the rows from the table.
03:33 This statement will fetch all the values from the students table.
03:38 Now click on the triangle icon in the toolbar to execute the query.
03:44 We can see the output in a tabular form, in the output pane.
03:49 Next I will demonstrate how to add multiple student details at the same time.
03:56 The syntax to insert multiple rows is shown here.
04:01 We can insert multiple rows in a single insert statement.
04:06 Here, I have entered the information of the students 'Ram', 'Kishore' and ‘Pooja’.
04:13 Let us save the script of this Insert statement for future use.
04:18 To do so, click on the Save file icon on the toolbar.
04:23 Enter the name of the file as insertscript hyphen student.
04:28 Select the folder where you want to save the file. I'll select Desktop.
04:35 By default, the file will always be saved with 'dot sql' extension.
04:41 Click on the Save button.
04:44 As before, click on the triangle icon in the toolbar to execute the query.
04:50 In the output pane, we can see the message “3 rows affected”.

This indicates that three records are inserted in the students table.

05:00 Once again we will retrieve the data from the students table.
05:05 To do so, click on the Previous queries drop-down box.

All the previous typed sql statements are stored here.

05:15 Click on “Select asterisk from students” from the list.
05:20 Now execute the query.
05:23 We can see there are two students with the same name ‘Ram’ but the other column values are different.
05:31 How will the institute or organization identify a particular student Ram’s details?
05:38 Each student is unique and they need to be identified with some unique attribute.

For example, student-id will be unique for each student.

05:49 Therefore in our example student id can be the ‘primary key constraint’.
05:55 Primary key is a column or a set of columns that uniquely identifies each row in the table.
06:03 Follow these rules when you define a primary key for a table.
06:08 A table should have only one primary key.
06:12 A primary key can be a single column or a set of columns.
06:17 The combination of values in these multiple columns must be unique.

It should not contain null values.

06:26 For example-
06:32 here, there is a duplicate row of data for the student name ‘Kishore’.
06:38 By mistake, I have entered the student Kishore’s details twice.
06:43 First, I demonstrated to insert a single record 2 times and then I inserted 3 records.

The duplication of data is Data redundancy.

06:55 Data redundancy, that is data repetition will affect the performance of the database.
07:02 More information on Data redundancy is given in the Additional material link of this tutorial.
07:09 How can we prevent insertion of duplicate data?

We can do so by adding a primary key constraint to a table.

07:18 Now I’ll show how to add a primary key.
07:22 Let’s go to the pgAdmin main screen.
07:26 Let me copy the script of create table statement and paste in the SQL Editor.
07:32 This is the code which we executed to create the students table.
07:38 Can we add a student name column as the primary key to identify each student?
07:45 No, student name cannot be assigned as a primary key as there may be many students with the same name.
07:53 We have to identify a column with unique values.
07:58 So, we will add a student id column to this table to identify each student.
08:05 Type the code as shown on the screen.
08:09 I have added a new column called studentid which will store a unique code for each student.
08:16 I have defined studentid as the primary key.

Now let’s execute the query.

08:24 We can see an error saying "relation 'students' already exists".
08:30 This means we tried to create a table with the same name students.
08:36 Two objects cannot have the same name in a database.
08:41 First we will drop the students table along with the data which we inserted now.
08:47 Then we will again create the students table with primary key constraints.
08:53 Go to the pgAdmin main screen.
08:56 Right-click on the students table.

From the drop-down menu, select Delete/Drop.

09:03 A pop-up window for Drop table appears.

Click on the Yes button.

09:10 This removes a table definition and all its associated data.
09:16 The syntax to drop a table is DROP TABLE tablename.
09:21 We can also type the statement in the SQL Editor and execute the query.
09:27 Switch to the SQL Editor window.
09:30 Now, let us create the students table with student id as primary key.
09:36 Execute the query.

Now the students table is successfully created.

But the table is empty without any data.

09:47 I'll insert the data from the insertscript hyphen student.sql file which we saved earlier.
09:54 Click “Open file” icon in the toolbar and select the insertscript hyphen student file.
10:01 The file is opened in the SQL editor window.

I’ll add the studentid column as shown.

10:10 Next I’ll pass a unique id for each student.
10:15 I’ll add another record for student Ram with unique student id as shown.

Note there is no duplicate row of data.

Now execute the query.

10:29 We can see that four records have been successfully inserted.
10:34 What will happen if we try to insert the same set of data into the students table again?
10:41 Let us try that. Execute the insert statement one more time.
10:47 We can see the error message “duplicate key value violates unique constraint”.
10:54 It shows an error which indicates the duplication of data.

Hence the primary key helps to avoid the data redundancy in a table.

11:05 Next, let us retrieve the inserted data.
11:09 We can see there is no duplication of data.
11:13 With this we come to the end of this tutorial. Let us summarize.
11:19 In this tutorial, we have learnt about:

inserting data,

retrieving data,

11:25 data redundancy,

importance of Primary keys,

creating table with primary keys.

11:32 As an Assignment, drop the Emp table which was created in an earlier assignment in this series.
11:39 Create the Emp table with empno as primary key.

Keep the other columns the same.

11:47 Insert few records into the Emp table.

Retrieve the data and see the output.

11:55 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

12:03 The Spoken Tutorial Project team conducts workshops and gives certificates.

For more details, please write to us.

12:12 Please post your timed queries in this forum.
12:16 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

12:29 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/Table-with-primary-keys/English-timed&oldid=45692"