Difference between revisions of "RDBMS-PostgreSQL/C2/Create-database-using-PgAdmin/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''' Create a database using pgAdmin.''' |- | 00:07 | In this tutorial...")
 
 
Line 10: Line 10:
 
|-  
 
|-  
 
| 00:07
 
| 00:07
−
| In this tutorial we will learn about how to connect to the server  
+
| In this tutorial, we will learn about how to:
 +
connect to the '''server''',
  
 
|-  
 
|-  
 
| 00:12
 
| 00:12
−
|  '''database''' and its objects
+
|  database and its '''object'''s,
  
−
How to create a database  
+
how to create a database,
  
 
|-  
 
|-  
 
| 00:17
 
| 00:17
−
|  '''Table''' and its '''attributes''' and  how to create a '''table'''  
+
|  '''Table''' and its '''attributes''' and   
 +
how to create a '''table'''.
  
 
|-  
 
|-  
 
| 00:23
 
| 00:23
−
| 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,
  
 
|-  
 
|-  
 
| 00:30
 
| 00:30
−
|  '''PostgreSQL 9.3.x''' and  '''pgAdmin 1.18'''  
+
|  '''PostgreSQL 9.3.x''' and   
 +
'''pgAdmin 1.18'''  
  
 
|-  
 
|-  
Line 48: Line 51:
 
|-  
 
|-  
 
| 00:56
 
| 00:56
−
| In the '''Search box''', type''' pgAdmin'''. The '''pgAdmin''' icon appears. Click on it.  
+
| In the '''Search box''', type:''' pgAdmin'''. The '''pgAdmin''' icon appears. Click on it.  
  
 
|-  
 
|-  
Line 56: Line 59:
 
|-  
 
|-  
 
| 01:09
 
| 01:09
−
| Here, on the left hand side we can see the object browser pane.  
+
| Here, on the left hand side, we can see the '''object browser pane'''.  
  
 
|-  
 
|-  
Line 84: Line 87:
 
|-  
 
|-  
 
| 01:43
 
| 01:43
−
| In the '''Properties tab''', enter '''localhost''' in the '''Name''' field and '''localhost''' in the '''Host '''field.  
+
| In the '''Properties '''tab, enter '''localhost''' in the '''Name''' field and '''localhost''' in the '''Host '''field.  
  
−
Keep all the other parameters same.  
+
Keep all the other '''parameter'''s same.  
  
 
|-  
 
|-  
Line 94: Line 97:
 
|-  
 
|-  
 
| 02:03
 
| 02:03
−
| Enter the '''password''' which you gave during installation.  
+
| Enter the password which you gave during installation.  
  
 
Click on '''Ok''' button.  
 
Click on '''Ok''' button.  
Line 106: Line 109:
 
|-  
 
|-  
 
| 02:18
 
| 02:18
−
| Always click on this check box ''''Do not show this hint again'''', before you close these windows.  
+
| Always click on this check-box ''''Do not show this hint again'''', before you close these windows.  
  
 
I won't explicitly mention this.  
 
I won't explicitly mention this.  
Line 126: Line 129:
 
|-  
 
|-  
 
| 02:42
 
| 02:42
−
| Now click on the '''Databases''' node.  
+
| Now, click on the '''Databases''' node.  
  
 
The default '''database 'postgres'''' that is created during installation is displayed here.  
 
The default '''database 'postgres'''' that is created during installation is displayed here.  
Line 132: Line 135:
 
|-  
 
|-  
 
| 02:52
 
| 02:52
−
| '''Database''' is a collection of information or data, which is stored in an organized way.  
+
| '''Database''' is a collection of information or data which is stored in an organized way.  
  
 
|-  
 
|-  
 
| 02:59
 
| 02:59
−
| A '''database''' has objects such as '''tables, views, procedures, functions, etc.'''  
+
| A '''database''' has objects such as '''tables, views, procedures, functions''' etc.
  
 
|-  
 
|-  
 
| 03:07
 
| 03:07
−
| We will learn about these objects in the future tutorials in this series.  
+
| We will learn about these objects in the future tutorials, in this series.  
  
 
|-  
 
|-  
 
| 03:12
 
| 03:12
−
| We can have multiple '''databases''' depending upon our application requirement.  
+
| We can have multiple databases depending upon our application requirement.  
  
 
|-  
 
|-  
Line 151: Line 154:
 
|-  
 
|-  
 
| 03:21
 
| 03:21
−
| Now, we will create a new '''database''' for our demonstration purpose.  
+
| Now, we will create a new database for our demonstration purpose.  
  
 
|-  
 
|-  
Line 167: Line 170:
 
|-  
 
|-  
 
| 03:44
 
| 03:44
−
| Next we will add a '''student table''' to this '''sampledb database'''.  
+
| Next, we will add a '''student''' table to this '''sampledb database'''.  
  
 
|-  
 
|-  
 
| 03:50
 
| 03:50
−
| What is table? In '''RDBMS,''' we store data in database objects called '''tables'''.  
+
| What is '''table'''? In '''RDBMS,''' we store data in database objects called '''tables'''.  
  
 
|-  
 
|-  
Line 181: Line 184:
 
| This is an example of a '''STUDENT''' table.  
 
| This is an example of a '''STUDENT''' table.  
  
−
'''Tables''' have columns which are called as '''fields'''.  
+
'''Tables''' have '''column'''s which are called as '''fields'''.  
  
 
|-  
 
|-  
 
| 04:11
 
| 04:11
−
| The fields in the '''STUDENT table''' are '''Student ID, student name, address, city, Phone, Date of birth, CGPA etc'''  
+
| The fields in the '''STUDENT''' table are '''Student ID, student name, address, city, Phone, Date of birth, CGPA '''etc.
  
 
|-  
 
|-  
Line 193: Line 196:
 
|-  
 
|-  
 
| 04:30
 
| 04:30
−
| Here in the '''STUDENT''' table, '''Student Name''' is one of the '''attribute''' that represents names of students.  
+
| Here, in the '''STUDENT''' table, '''Student Name''' is one of the '''attribute''' that represents names of students.  
  
 
|-  
 
|-  
 
| 04:37
 
| 04:37
−
| Now switch back to the interface.  
+
| Now switch back to the '''interface'''.  
  
 
|-  
 
|-  
 
| 04:41
 
| 04:41
−
| To create a new '''table''' in '''Sampledb database''', click on the '''Sampledb''' node.  
+
| To create a new '''table''' in '''Sampledb''' database, click on the '''Sampledb''' node.  
  
 
Then click '''Schemas''' and then '''Public''' and '''Tables'''.  
 
Then click '''Schemas''' and then '''Public''' and '''Tables'''.  
Line 231: Line 234:
 
|-  
 
|-  
 
| 05:25
 
| 05:25
−
| Enter 50 as the length of the field.  
+
| Enter 50 as the length of the '''field'''.  
  
 
|-  
 
|-  
 
| 05:29
 
| 05:29
−
| The length specifies the maximum length of the column of the '''table'''.  
+
| The length specifies the maximum length of the '''column''' of the '''table'''.  
  
 
|-  
 
|-  
Line 247: Line 250:
 
|-  
 
|-  
 
| 05:44
 
| 05:44
−
| '''Integer, numeric''' represents '''numeric data type'''
+
| '''Integer, numeric''' represents '''numeric data type'''.
  
 
|-  
 
|-  
 
| 05:48
 
| 05:48
−
| For example, phone no, age, quantity are of '''numeric data type'''.  
+
| For example: phone no, age, quantity are of '''numeric data type'''.  
  
 
|-  
 
|-  
Line 259: Line 262:
 
|-  
 
|-  
 
| 06:01
 
| 06:01
−
| Here n is the total digits and d is the number of digits after the decimal.  
+
| Here '''n''' is the total digits and '''d''' is the number of digits after the decimal.  
  
 
|-  
 
|-  
 
| 06:07
 
| 06:07
−
| It can be used to represents fields like basic salary, unit price etc.  
+
| It can be used to represent fields like basic salary, unit price etc.  
  
 
|-  
 
|-  
Line 271: Line 274:
 
|-  
 
|-  
 
| 06:19
 
| 06:19
−
| Fields such as name, address, email are of '''string data type'''.  
+
| Fields such as '''name, address, email''' are of '''string data type'''.  
  
 
'''Text''' is a '''string data type''' with unlimited length.  
 
'''Text''' is a '''string data type''' with unlimited length.  
Line 277: Line 280:
 
|-  
 
|-  
 
| 06:29
 
| 06:29
−
| '''Date''' represents '''date data type.'''  
+
| '''Date''' represents '''date''' data type.
  
 
|-  
 
|-  
Line 289: Line 292:
 
|-  
 
|-  
 
| 06:45
 
| 06:45
−
| Add a '''Date of Birth''' column with '''date data type'''.  
+
| Add a '''Date of Birth''' column with '''date''' data type.  
  
 
|-  
 
|-  
 
| 06:49
 
| 06:49
−
| Next add a '''CGPA''' field. Select the '''data type''' as '''numeric'''.  
+
| Next, add a '''CGPA''' field. Select the '''data type''' as '''numeric'''.  
  
 
|-  
 
|-  
 
| 06:56
 
| 06:56
−
| Enter 2 as length and 1 as precision. This is same as the '''decimal data type'''.  
+
| Enter '''2''' as length and '''1''' as precision. This is same as the '''decimal''' data type.  
  
 
|-  
 
|-  
Line 311: Line 314:
 
|-  
 
|-  
 
| 07:21
 
| 07:21
−
| We can see the '''student table''' is created in the '''sampledb database'''.  
+
| We can see the '''student''' table is created in the '''sampledb''' database.  
  
 
Click on it.  
 
Click on it.  
Line 317: Line 320:
 
|-  
 
|-  
 
|07:28
 
|07:28
−
| In the '''SQL pane''' we can see the syntax of the '''create table command''' generated automatically.  
+
| In the '''SQL pane''', we can see the syntax of the '''create table''' command generated automatically.  
  
 
|-  
 
|-  
Line 325: Line 328:
 
|-  
 
|-  
 
| 07:40
 
| 07:40
−
| In this tutorial, we have learnt -  how to connect to the '''server'''  
+
| In this tutorial, we have learnt -  how to connect to the '''server''',
  
−
'''database''' and its objects  
+
'''database''' and its objects,
  
 
|-  
 
|-  
 
| 07:47
 
| 07:47
−
| How to create a '''database'''  
+
| How to create a '''database''',
  
−
'''Table''' and its '''attributes''' and  how to create a '''table'''  
+
'''Table''' and its '''attributes''' and  how to create a '''table'''.
  
 
|-  
 
|-  
 
| 07:54
 
| 07:54
−
| As an Assignment, Connect to the '''server''' and check the default available '''database'''
+
| As an Assignment, connect to the '''server''' and check the default available database.
  
 
|-  
 
|-  
 
| 08:00
 
| 08:00
−
| Create a new '''database Organization'''.  In the '''Organization database, '''create a '''table Emp''' with the columns '''Emp name, Address, Date of Birth and Salary.'''  
+
| Create a new database '''Organization'''.  In the '''Organization''' database, create a table '''Emp''' with the columns '''Emp name, Address, Date of Birth''' and '''Salary'''.
  
 
|-  
 
|-  
 
| 08:13
 
| 08:13
−
| 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 351: Line 354:
 
|-  
 
|-  
 
| 08:20
 
| 08:20
−
| 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 363: Line 364:
 
|-  
 
|-  
 
| 08:32
 
| 08:32
−
| 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 369: Line 370:
 
|-  
 
|-  
 
| 08:44
 
| 08:44
−
| 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 17:10, 4 February 2019

Time Narration
00:01 Welcome to the Spoken Tutorial on Create a database using pgAdmin.
00:07 In this tutorial, we will learn about how to:

connect to the server,

00:12 database and its objects,

how to create a database,

00:17 Table and its attributes and

how to create a table.

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

Ubuntu Linux 14.04 operating system,

00:30 PostgreSQL 9.3.x and

pgAdmin 1.18

00:38 To follow this tutorial, you should have good understanding of basic computer concepts and

working knowledge of any programming language.

00:48 Let us open the pgAdmin.
00:51 Click Dash Home on the top left corner of the computer desktop.
00:56 In the Search box, type: pgAdmin. The pgAdmin icon appears. Click on it.
01:05 The pgAdmin interface is opened now.
01:09 Here, on the left hand side, we can see the object browser pane.
01:14 The various objects of the database are displayed here.
01:18 The upper right pane shows the relevant properties of each object.
01:23 The lower right pane is the SQL pane where the script of the currently selected object is displayed.
01:30 Let us see how to connect to the server.
01:33 Click on the connection icon on the top left corner of the tool bar.
01:39 New Server Registration window appears.
01:43 In the Properties tab, enter localhost in the Name field and localhost in the Host field.

Keep all the other parameters same.

01:56 The database name, and the Username which are shown here are the names given during installation.
02:03 Enter the password which you gave during installation.

Click on Ok button.

02:10 Small pop-up windows will appear from time to time.

These give additional information for our understanding.

02:18 Always click on this check-box 'Do not show this hint again', before you close these windows.

I won't explicitly mention this.

02:27 Click on Ok button.
02:30 Here, we can see the connection established to localhost.

Click on the localhost node.

02:38 We can see several objects for this particular server.
02:42 Now, click on the Databases node.

The default database 'postgres' that is created during installation is displayed here.

02:52 Database is a collection of information or data which is stored in an organized way.
02:59 A database has objects such as tables, views, procedures, functions etc.
03:07 We will learn about these objects in the future tutorials, in this series.
03:12 We can have multiple databases depending upon our application requirement.
03:18 Switch back to pgAdmin interface.
03:21 Now, we will create a new database for our demonstration purpose.
03:26 Right-click on the Databases node and select New Database.
03:31 Type Sampledb as the database name and click Ok.
03:37 Now, we can see the database is listed under the database node, which we created just now.
03:44 Next, we will add a student table to this sampledb database.
03:50 What is table? In RDBMS, we store data in database objects called tables.
03:58 A table is a collection of related data that is stored in rows and columns format.
04:04 This is an example of a STUDENT table.

Tables have columns which are called as fields.

04:11 The fields in the STUDENT table are Student ID, student name, address, city, Phone, Date of birth, CGPA etc.
04:22 A column is a set of value of a particular data type. This is also called as attribute.
04:30 Here, in the STUDENT table, Student Name is one of the attribute that represents names of students.
04:37 Now switch back to the interface.
04:41 To create a new table in Sampledb database, click on the Sampledb node.

Then click Schemas and then Public and Tables.

04:53 Right-click on the table node and select New Table.

Let us give “students” as table name.

05:02 Click on the Columns tab to enter the columns for this table.

Click on the Add button.

05:10 In the new window that opens, enter Student name in the Name field.
05:15 Select character varying as data type from the drop-down list box.
05:20 The data type specifies what type of data the field can hold.
05:25 Enter 50 as the length of the field.
05:29 The length specifies the maximum length of the column of the table.
05:34 Click Ok button to add column to the table.
05:39 We will see what are the common data types used in PostgreSQL.
05:44 Integer, numeric represents numeric data type.
05:48 For example: phone no, age, quantity are of numeric data type.
05:55 numeric(n,d) - is also numeric data type with decimal places.
06:01 Here n is the total digits and d is the number of digits after the decimal.
06:07 It can be used to represent fields like basic salary, unit price etc.
06:14 char and character varying represents string data type.
06:19 Fields such as name, address, email are of string data type.

Text is a string data type with unlimited length.

06:29 Date represents date data type.
06:33 Likewise, I'll add City column with character varying data type and length as 40.
06:41 Add the column Gender as shown.
06:45 Add a Date of Birth column with date data type.
06:49 Next, add a CGPA field. Select the data type as numeric.
06:56 Enter 2 as length and 1 as precision. This is same as the decimal data type.
07:03 We have entered all the columns for the student table. Click on Ok button.
07:11 A window shows some message about Primary keys.

We will see the importance of Primary keys in the future tutorials.

07:21 We can see the student table is created in the sampledb database.

Click on it.

07:28 In the SQL pane, we can see the syntax of the create table command generated automatically.
07:35 With this, we come to the end of this tutorial. Let us summarize.
07:40 In this tutorial, we have learnt - how to connect to the server,

database and its objects,

07:47 How to create a database,

Table and its attributes and how to create a table.

07:54 As an Assignment, connect to the server and check the default available database.
08:00 Create a new database Organization. In the Organization database, create a table Emp with the columns Emp name, Address, Date of Birth and Salary.
08:13 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

08:20 The Spoken Tutorial Project team conducts workshops and gives certificates.

For more details, please write to us.

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

More information on this mission is available at this link.

08:44 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/Create-database-using-PgAdmin/English-timed&oldid=45689"