Difference between revisions of "RDBMS-PostgreSQL/C2/Create-database-using-PgAdmin/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "'''>>Title of script''':''' Create a database using pgAdmin''' '''Author: Nirmala Venkat''' '''Keywords: RDBMS, PostgreSQL, pgAdmin, Database, table, field, record, video t...")
 
 
(One intermediate revision by the same user not shown)
Line 67: Line 67:
 
The various objects of the database are displayed here.
 
The various objects of the database are displayed here.
  
The upper right pane shows the relevant properties of each object.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Highlight the upper right pane
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|The upper right pane shows the relevant properties of each object.
  
The lower right pane is the '''SQL''' pane where the script of the currently selected object is displayed
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Highlight the lower right pane
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|The lower right pane is the '''SQL''' pane where the script of the currently selected object is displayed.
  
 
|-
 
|-
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to the connection icon in the tool bar.
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to the connection icon in the tool bar.
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Let us see how to connect to the server.  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Let us see how to connect to the '''server'''.  
  
 
Click on the '''connection icon''' on the top left corner of the tool bar.
 
Click on the '''connection icon''' on the top left corner of the tool bar.
Line 82: Line 86:
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to properties tab.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to Properties tab.
  
 
Type localhost in the Name field
 
Type localhost in the Name field
  
 
Type localhost in the Username field
 
Type localhost in the Username field
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| In the properties tab, enter '''localhost''' in the '''Name''' field''' '''and '''localhost''' in the '''Host '''field.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| 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 parameters same.
  
The '''database''' name, '''Username''' which are shown here are the names given during installation.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point as per narration.
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| The '''database''' name, '''Username''' which are shown here are the names given during installation.
  
Enter the password which you gave during installation.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Type the password and press Enter >> Click on OK
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Enter the '''password''' which you gave during installation.
  
Click on '''ok''' button.
+
Click on '''Ok''' button.
  
Small pop-up windows will appear from time to time.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to the window.
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Small pop-up windows will appear from time to time.
  
 
These give additional information for our understanding.
 
These give additional information for our understanding.
  
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.
  
Click on 'Ok' button.
+
Click on '''Ok''' button.
  
 
|-
 
|-
Line 117: Line 127:
 
Click on the '''localhost''' node.
 
Click on the '''localhost''' node.
  
We can see several objects for this particular server.
+
We can see several objects for this particular '''server'''.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on Databases
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now click on the '''Databases''' node.
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| 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 142: Line 152:
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Back to pgadmin
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Switch back to '''pgAdmin''' interface.
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Switch back to '''pgAdmin''' interface.
 
|-
 
|-
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now, we will create a new database for our demonstration purpose.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now, we will create a new '''database''' for our demonstration purpose.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Right-click on Databases >> select New Database
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Right click on the '''Databases''' node and select '''New Database.'''
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Right-click on the '''Databases''' node and select '''New Database.'''
  
 
+
|-
Type '''Sampledb''' as the database name and click '''ok'''.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Type Sampledb >> Click Ok
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|Type '''Sampledb''' as the '''database''' name and click '''Ok'''.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to the database
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now, we can see the database is listed under the database node which we created just now.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now, we can see the '''database''' is listed under the '''database''' node, which we created just now.
  
 
|-
 
|-
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Next we will add a '''student''' table to this '''sampledb''' database.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Next we will add a '''student table''' to this '''sampledb database'''.
  
 
|-
 
|-
Line 177: Line 188:
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| This is an example of a '''STUDENT''' table.
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| This is an example of a '''STUDENT''' table.
  
Tables have columns which are called as '''fields'''.
+
'''Tables''' have columns which are called as '''fields'''.
  
The fields in the '''STUDENT''' table are '''Student ID, student name, address, city, Phone, DOB, CGPA.'''
+
The fields in the '''STUDENT table''' are '''Student ID, student name, address, city, Phone, DOB, CGPA.'''
  
 
|-
 
|-
Line 187: Line 198:
  
 
Show the student_ column.jpg
 
Show the student_ column.jpg
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| A column is a set of value of a particular data type. This is also called as '''attribute'''.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| A column is a set of value of a particular '''data type'''. This is also called as '''attribute'''.
  
 
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.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Back to interface.
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now switch back to the interface.
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Now switch back to the interface.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on Sampedb >> Schemas >> Public >> Tables
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| To create a new table in '''sampledb''' database, click on the sampledb node.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| 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'''.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Right-click on table >> New Table >> Type students
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Right click on the table node and select '''New Table.'''
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Right-click on the '''table''' node and select '''New Table.'''
  
Let us give “'''students'''” as table name.
+
Let us give “'''students'''” as '''table''' name.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on Columns >> Add button
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on the '''columns''' tab to enter the columns for this table.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on the '''Columns''' tab to enter the columns for this '''table'''.
  
 
Click on the '''Add''' button.
 
Click on the '''Add''' button.
  
In the new window that opens, enter '''Student''' '''name''' in the name field.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|Type Name = Student name
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|In the new window that opens, enter '''Student name''' in the '''Name''' field.
  
 
|-
 
|-
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Select character varying
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Select '''Character''' '''varying''' as data type from the drop down list box.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Select '''character varying''' as '''data type''' from the drop-down list box.
  
The '''data type''' specifies what '''type''' of data the '''field''' can hold.
+
The '''data type''' specifies what '''type''' of data the field can hold.
  
Enter 50 in the length field.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Type 50 in length field.
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Enter 50 in the length field.
  
 
The length specifies the maximum length of the column of the '''table'''.
 
The length specifies the maximum length of the column of the '''table'''.
  
Click ok button to add column to the table.
+
|-
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click Ok button
 +
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click '''Ok''' button to add column to the '''table'''.
  
 
|-
 
|-
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
 
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| We will see what are the common data types used in '''PostgreSQL'''.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| We will see what are the common '''data types''' used in '''PostgreSQL'''.
  
 
|-
 
|-
Line 240: Line 257:
 
numeric (n,d) – where n is the total digits and d is the number of digits after the decimal
 
numeric (n,d) – where n is the total digits and d is the number of digits after the decimal
  
Char (size) - holds a fixed length string
+
char (size) - holds a fixed length string
  
character varying(size),varchar(size) - holds a variable length string
+
character varying(size), varchar(size) - holds a variable length string
  
 
text – variable unlimited length
 
text – variable unlimited length
  
 
Date – date data type
 
Date – date data type
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| '''Integer, numeric''' represents numeric data type.
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| '''Integer, numeric''' represents '''numeric data type''''''Bold text'''.
  
For example, phone no, age, quantity are of numeric data type.
+
For example, phone no, age, quantity are of '''numeric data type'''.
  
'''numeric(n,d) - '''is also numeric data type with decimal places.
+
'''numeric(n,d) - '''is also '''numeric data type''' with decimal places.
  
 
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.
Line 257: Line 274:
 
It can be used to represents fields like basic salary, unit price etc.
 
It can be used to represents fields like basic salary, unit price etc.
  
'''Char''' and character varying represents string data type.
+
'''char''' and '''character varying''' represents '''string data type'''.
  
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.
  
'''Date''' represents date data type.
+
'''Date''' represents '''date data type.'''
  
 +
|-
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Add City column
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Likewise, I'll add '''City''' column with '''character varying data type''' and length as 40.
  
 
|-
 
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Add Gender column
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Likewise,I'll add '''Address''' column with '''character varying''' data type and length as 100.
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|Add the column '''Gender''' as shown here.
  
Add the column '''City, phone as shown here'''
+
|-
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Add Date of Birth column
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|Add a '''Date of Birth''' column with '''date data type'''.
  
Add a date of birth column with '''date''' data type.
+
|-
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Add CGPA column
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|Next add a '''CGPA''' field. Select the '''data type''' as '''numeric'''. Enter 2 as length and 1 as precision.
  
Next add a '''CGPA''' field. Select the data type as '''numeric'''. Enter 2 as length and 1 as precision.
+
This is same as the '''decimal data type'''.
  
This is same as the decimal data type.
+
|-
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click Ok
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| We have entered all the columns for the '''student''' table.  Click on '''Ok''' button.
  
 
|-
 
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to window
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| We have entered all the columns for the '''student''' table. Click on '''ok''' button.
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|A window shows some message about '''Primary keys'''.  
  
A window shows some message about '''primary''' '''keys'''.
+
We will see the importance of '''Primary keys''' in the future tutorials.
 
+
We will see the importance of primary keys in the future tutorials.
+
  
 
|-
 
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Point to student table.
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| We can see the '''student table''' is created in the '''sampledb''' database.
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| We can see the '''student table''' is created in the '''sampledb database'''.
  
 
|-
 
|-
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on students table.
 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on it.  
 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Click on it.  
  
In the '''sql''' pane, we can see the syntax of the create table command generated automatically.
+
|-
 +
|style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Highlight SQL pane
 +
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| In the '''SQL pane''' we can see the syntax of the '''create table command''' generated automatically.
  
 
|-
 
|-
Line 303: Line 329:
 
|-
 
|-
 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Summary
 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Summary
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| In this tutorial, we have learnt about
+
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| In this tutorial, we have learnt -
  
* how to connect to the server
+
* how to connect to the '''server'''
 
* '''database''' and its objects
 
* '''database''' and its objects
* how to create a database
+
* how to create a '''database'''
 
* '''Table''' and its '''attributes''' and
 
* '''Table''' and its '''attributes''' and
 
* how to create a '''table'''
 
* how to create a '''table'''
Line 345: Line 371:
  
 
Forum for specific questions
 
Forum for specific questions
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"|  
+
| style="background-color:#ffffff;border-top:none;border-bottom:1pt solid #000001;border-left:1pt solid #000001;border-right:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Please post your timed queries in this forum.
 
+
* Please post your timed queries in this forum.
+
  
 
|-
 
|-
Line 356: Line 380:
 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.
 
| style="background-color:#ffffff;border:1pt solid #000001;padding-top:0cm;padding-bottom:0cm;padding-left:0.079cm;padding-right:0.191cm;"| Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.
  
More information on this mission is available at
+
More information on this mission is available at this link.
 
+
this link.
+
  
 
|-
 
|-

Latest revision as of 12:59, 31 March 2017

>>Title of script: Create a database using pgAdmin


Author: Nirmala Venkat

Keywords: RDBMS, PostgreSQL, pgAdmin, Database, table, field, record, video tutorial

Visual Cue Narration
Slide 1: Welcome to the Spoken Tutorial on Create a database using pgAdmin.
Slide 2:

Learning Objectives

In this tutorial we will learn about how to
  • connect to the server
  • database and its objects
  • how to create a database
  • Table and its attributes and
  • how to create a table
Slide 3:

System requirement

For this tutorial, I am using
  • Ubuntu Linux 14.04 operating system
  • PostgreSQL 9.3.x
  • 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.
Let us open the pgAdmin.
Click Dash home Click Dash Home on the top left corner of the computer desktop.
In the search box type, pgadmin In the Search box, type pgAdmin. The pgAdmin icon appears. Click on it.
Point to the new window pgAdmin interface is opened now.

Here, on the left hand side we can see the object browser pane.

The various objects of the database are displayed here.

Highlight the upper right pane The upper right pane shows the relevant properties of each object.
Highlight the lower right pane The lower right pane is the SQL pane where the script of the currently selected object is displayed.
Point to the connection icon in the tool bar. Let us see how to connect to the server.

Click on the connection icon on the top left corner of the tool bar.

Point to the window. New Server Registration window appears.
Point to Properties tab.

Type localhost in the Name field

Type localhost in the Username field

In the Properties tab, enter localhost in the Name field and localhost in the Host field.

Keep all the other parameters same.

Point as per narration. The database name, Username which are shown here are the names given during installation.
Type the password and press Enter >> Click on OK Enter the password which you gave during installation.

Click on Ok button.

Point to the window. Small pop-up windows will appear from time to time.

These give additional information for our understanding.

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

I won't explicitly mention this.

Click on Ok button.

Point to the connection

Click on the localhost node.

Point to the objects under the server

Here, we can see the connection established to localhost.

Click on the localhost node.

We can see several objects for this particular server.

Click on Databases Now click on the Databases node.

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

Slide 5:

Database

Image

Database.svg

Database is a collection of information or data, which is stored in an organized way.

A database has objects such as tables, views, procedures, functions, etc.

We will learn about these objects in the future tutorials in this series.

We can have multiple databases depending upon our application requirement.

Back to pgadmin Switch back to pgAdmin interface.
Now, we will create a new database for our demonstration purpose.
Right-click on Databases >> select New Database Right-click on the Databases node and select New Database.
Type Sampledb >> Click Ok Type Sampledb as the database name and click Ok.
Point to the database Now, we can see the database is listed under the database node, which we created just now.
Next we will add a student table to this sampledb database.
Slide:

What is table?

In RDBMS, we store data in database objects called tables.

A table is a collection of related data that is stored in rows and columns format.

Slide:

Show the table image:student.jpg

This is an example of a STUDENT table.

Tables have columns which are called as fields.

The fields in the STUDENT table are Student ID, student name, address, city, Phone, DOB, CGPA.

Slide:

What is a Column?

Show the student_ column.jpg

A column is a set of value of a particular data type. This is also called as attribute.

Here in the STUDENT table, Student Name is one of the attribute that represents names of students.

Back to interface. Now switch back to the interface.
Click on Sampedb >> Schemas >> Public >> Tables To create a new table in Sampledb database, click on the Sampledb node.

Then click Schemas and then Public and Tables.

Right-click on table >> New Table >> Type students Right-click on the table node and select New Table.

Let us give “students” as table name.

Click on Columns >> Add button Click on the Columns tab to enter the columns for this table.

Click on the Add button.

Type Name = Student name In the new window that opens, enter Student name in the Name field.
Select character varying Select character varying as data type from the drop-down list box.

The data type specifies what type of data the field can hold.

Type 50 in length field. Enter 50 in the length field.

The length specifies the maximum length of the column of the table.

Click Ok button Click Ok button to add column to the table.
We will see what are the common data types used in PostgreSQL.
Slide

Data types:

integer, numeric - numeric data type

numeric (n,d) – where n is the total digits and d is the number of digits after the decimal

char (size) - holds a fixed length string

character varying(size), varchar(size) - holds a variable length string

text – variable unlimited length

Date – date data type

Integer, numeric' represents numeric data type'Bold text.

For example, phone no, age, quantity are of numeric data type.

numeric(n,d) - is also numeric data type with decimal places.

Here n is the total digits and d is the number of digits after the decimal.

It can be used to represents fields like basic salary, unit price etc.

char and character varying represents string data type.

Fields such as name, address, email are of string data type.


Text is a string data type with unlimited length.

Date represents date data type.

Add City column Likewise, I'll add City column with character varying data type and length as 40.
Add Gender column Add the column Gender as shown here.
Add Date of Birth column Add a Date of Birth column with date data type.
Add CGPA column Next add a CGPA field. Select the data type as numeric. Enter 2 as length and 1 as precision.

This is same as the decimal data type.

Click Ok We have entered all the columns for the student table. Click on Ok button.
Point to window A window shows some message about Primary keys.

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

Point to student table. We can see the student table is created in the sampledb database.
Click on students table. Click on it.
Highlight SQL pane In the SQL pane we can see the syntax of the create table command generated automatically.
With this, we come to the end of this tutorial. Let us summarize.
Summary In this tutorial, we have learnt -
  • how to connect to the server
  • database and its objects
  • how to create a database
  • Table and its attributes and
  • how to create a table
Assignment As an Assignment,
  1. Connect to the server and check the default available database
  2. Create a new database Organization.
  3. In the Organization database, create a table Emp with the columns Emp name, Address, DOB and Salary.
Slide 12:

About Spoken Tutorial project

[1]

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

Nancyvarkey, Nirmala Venkat