RDBMS-PostgreSQL/C2/Table-with-primary-keys/English-timed

From Script | Spoken-Tutorial
Revision as of 16:30, 30 January 2019 by PoojaMoolya (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
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 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 summarises 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