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

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