Sandhya.np14 at 07:19, 5 February 2019

2019-02-05T07:19:19Z

PoojaMoolya: 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..."

2019-01-30T11:00:54Z

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..."

New page

{| border=1
| '''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.

|}

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

Sandhya.np14 at 07:19, 5 February 2019

PoojaMoolya: 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..."