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

>>Title of script: Table with primary keys

Author: Nirmala Venkat

Keywords: Primary key, Insert, Select, PostgreSQL, pgAdmin, Database, table, field, record, Data redundancy, video tutorial

Visual Cue	Narration
Slide 1:	Welcome to the Spoken Tutorial on Table with primary keys
Slide 2: Learning Objectives	In this tutorial we will learn to Insert data Retrieve data Data Redundancy Importance of primary keys and Create a table with primary keys
Slide 3: System requirement	To record this tutorial, I am using Ubuntu Linux 14.04 operating system PostgreSQL 9.3.x and pgAdmin 1.18
Slide 4: Pre-requisites	To follow this tutorial, you should have Good understanding of basic computer concepts and Working knowledge of any programming language.
Open pgAdmin	Let us open pgAdmin.
Point to the connection and select the sampledb database	Ensure that you have connected to the localhost server. Select the 'Sampledb' database.
Click on the Students table node and click Columns node.	Click on the students table node and then click on Columns node.
Point to the columns in the student table	We can see the students table with 5 columns which we created earlier. The structure of the table is ready.
	Let us learn how to insert some data into this table.
Right click on the Student table and select Scripts from the drop down menu. Then select INSERT script.	Right-click on the students table. From the drop-down menu, select Scripts and then select INSERT script.
Point to the default Insert statement.	The SQL Editor window opens up with the basic syntax for an Insert statement.
Slide 6: Syntax - INSERT Syntax: Insert - one row INSERT INTO table(column1,column2...) VALUES (value1, value2,...); Highlight according to narration	Insert statement inserts one or more rows into a table. The values to be inserted must match the order of the columns and the datatype of each column. That is - column 1 takes the value 1 and column 2 takes the value 2 and so on.
Insert into student(studentname, city, dob,gender,cgpa) values ('Ram','Mumbai','1996-10-23', ‘Male’,9.6);	Type the values as shown. Here I have entered a single row of data for the student 'Ram'.
Highlight the code	The order of the column name should match the values. Note that character data type values are always enclosed in quotes. And numeric data type values are not enclosed within quotes.
	Now we will execute the insert statement.
Click on the triangle icon in the toolbar	Click on the triangle icon in the toolbar to execute the query.
Point to the message	We can see the message 'Query returned successfully: One row affected'. This indicates that one row of data is inserted into the students table.
	I’ll insert one more record.
copy and paste the code Insert into student(studentname, city, dob,gender,cgpa) values ('kishore','Mumbai',’Male’,'1996-08-09', 8.5)	Type the code as shown. Execute the query. I have inserted 2 records now.
	Let us now retrieve the data which we have inserted, from the table.
Click on the “Clear edit window” icon in the tool bar	Click on the “Clear edit window” icon in the toolbar to clear the previous statement.
Type, Select * from	Then type, Select asterisk from
Press ctrl and space bar	Here we have to specify the table name. Press Ctrl and spacebar together.
Select Students table from the list	A list of available tables appears as shown. Select the Students table.
Highlight Select	Here, Select is a query command which is used to retrieve data from the tables.
Highlight asterisk	Asterisk means all the rows from the table. This statement will fetch all the values from the students table.
Click on the triangle icon in the tool bar	Now click on the triangle icon in the toolbar to execute the query.
Point to the output pane	We can see the output in a tabular form, in the output pane.
	Next I will demonstrate how to add multiple student details at the same time.
Slide: Syntax: Insert - Multiple row INSERT INTO table(column1,column2...) VALUES (value1, value2,...), (value1, value2,...), …..;	The syntax to insert multiple rows is shown here.
Insert into students(studentname, city,gender, dob,cgpa) values ('Ram',’Chennai,’Male’,'1995-11-21', 9.2), ('kishore','Mumbai',’Male’,'1996-08-09', 8.5), (Pooja,'Pune',’Female’,'1995-03-25', 9.2);	We can insert multiple rows in a single insert statement. Here I have entered the information of the students 'Ram', 'Kishore' and ‘Pooja’.
Highlight the insert statements	Let us save the script of this Insert statement for future use.
Click on the save file icon on the tool bar.	To do so, click on the Save file icon on the toolbar.
Name of the file >> insertscript-student.	Enter the name of the file as insertscript hyphen student.
Select the Desktop folder. Point to the file format .sql	Select the folder where you want to save the file. I'll select Desktop. By default, the file will always be saved with dot sql extension.
Click on the save button.	Click on the Save button.
Click on the triangle icon in the tool bar	As before, click on the triangle icon in the toolbar to execute the query.
Point to the message	In the output pane, we can see the message “3 rows affected”. This indicates that three records are inserted in the students table.
	Once again we will retrieve the data from the students table.
Click on the Previous queries	To do so, go to the top left corner and click on the Previous queries drop-down box. All the previous typed sql statements are stored here.
Click on “Select * from students”	Click on “Select * from students” from the list.
Click on the triangle icon in the toolbar	Now execute the query.
Point to the student ‘Ram’	We can see there are two students with the same name ‘Ram’ but the other column values are different. How will the institute or organization identify a particular student Ram’s details?
	Each student is unique and they need to be identified with some unique attribute. For example, student id will be unique for each student.
	Therefore in our example student id can be the ‘primary key constraint’.
Slide: Primary key	Primary key is a column or a set of columns, that uniquely identifies each row in the table.
Slide: Rules to define a primary key: A table has only one primary key A primary key can be a single column or a set of columns. The combination of values in these multiple columns must be unique. It should not contain null values.	Follow these rules when you define a primary key for a table. A table should have only one primary key. A primary key can be a single column or a set of columns. The combination of values in these multiple columns must be unique. It should not contain null values.
Point to the duplicate row	Here, there a duplicate row of data for the student name ‘Kishore’
Point to the duplicate row	By mistake, I have entered the student Kishore’s details twice. First I demonstrated to insert a single record 2 times and then I inserted 3 records. The duplication of data is Data redundancy.
SLIDE Data redundancy Data redundancy, that is data repetition will affect the performance of the database.	Data redundancy, that is data repetition will affect the performance of the database. More information on Data redundancy is given in the Additional material link, of this tutorial.
SLIDE:How to prevent duplication of data? How can we prevent insertion of duplicate data? We can do so by adding a primary key constraint to the table.	How can we prevent insertion of duplicate data? We can do so by adding a primary key constraint to a table.
	Now I’ll show how to add a primary key.
	Let’s go to the pgAdmin main screen.
Copy and paste the code in the SQL Editor	Let me copy the script of create table statement and paste in the SQL Editor.
CREATE TABLE students( studentname character varying(50), city character varying(40), gender character varying(10), dob date, cgpa numeric(2,1));	This is the code which we executed to create the students table. Can we add a student name column as the primary key to identify each student? No, student name cannot be assigned as a primary key as there may be many students with the same name. We have to identify a column with unique values. So, we will add a student id column to this table to identify each student.
CREATE TABLE students ( studentid character varying(10), studentname character varying(50), city character varying(40), gender character varying(10), dob date, cgpa numeric(2,1), primary key(studentId));	Type the code as shown on the screen. I have added a new column called studentid, which will store a unique code for each student. I have defined studentid as the primary key. Now let’s execute the query.
Point to the error	We can see an error saying “ relation 'students' already exists'. This means we tried to create a table with the same name students. Two objects cannot have the same name in a database.
	First we will drop the students table along with the data which we inserted now. Then we will again create the students table with primary key constraints.
	Go to the pgAdmin main screen.
	Right-click on the students table. From the drop-down menu, select Delete/Drop.
	A pop-up window for Drop table appears. Click on the Yes button.
	This removes a table definition and all its associated data.
Slide: Drop table tablename;	The syntax to drop a table is DROP TABLE tablename. We can also type the statement in the SQL Editor and execute the query.
	Switch to the SQL Editor window.
	Now let us create the students table with student id as primary key.
Click on execute button	Execute the query. Now the students table is successfully created.
Point to the message.	But the table is empty without any data. I'll insert the data from the insertscript hyphen student.sql file which we saved earlier.
Click “Open file” icon. select the insertscript-student.sql file.	Click “Open file” icon in the toolbar and select the insertscript hyphen student.sql file.
Point to the script Type studentid Type the values	The file is opened in the SQL editor window. I’ll add the studentid column as shown. Next I’ll pass a unique id for each student. 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.
Point to the message	We can see that four records have been successfully inserted.
	What will happen if we try to insert the same set of data into the students table again?
	Let us try that. Execute the insert statement one more time.
Point to the error.	We can see the error message “duplicate key value violates unique constraint”
	It shows an error which indicates the duplication of data. Hence the primary key helps to avoid the data redundancy in a table.
	Next, let us retrieve the inserted data.
Execute the query.	Execute the query.
Point to the output	We can see there is no duplication of data.
Summary	With this we come to the end of this tutorial. Let us summarize. In this tutorial, we have learnt about Inserting data Retrieving data Data Redundancy Importance of Primary keys Creating table with primary keys
Assignment Drop the emp table Create the emp table with emp_no as primary key. Insert few records. Retrieve the data.	As an Assignment, Drop the Emp table which was created in an earlier assignment in this series. Create the Emp table with empno as primary key. Keep the other columns the same. Insert few records into the Emp table. Retrieve the data and see the output.
Slide 12: About Spoken Tutorial project	The video at the following link summarises the Spoken Tutorial project. Please download and watch it.
Slide 13: Spoken Tutorial workshops	The Spoken Tutorial Project Team conducts workshops gives certificates For more details, please write to us.
Slide 13: Forum for specific questions	Please post your timed queries in this forum.
Slide 14: Acknowledgement	Spoken Tutorial project is funded by NMEICT, MHRD, Government of India. More information on this mission is available at this link.
	This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nirmala Venkat