Difference between revisions of "RDBMS-PostgreSQL/C2/Select-statement/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''' Select statement.''' |- | 00:06 | In this tutorial we will learn abo...")
 
Line 6: Line 6:
 
|-  
 
|-  
 
| 00:01
 
| 00:01
−
| Welcome to the''' Spoken Tutorial''' on''' Select statement.'''  
+
| Welcome to the''' Spoken Tutorial''' on''' Select''' statement.
  
 
|-  
 
|-  
 
| 00:06
 
| 00:06
−
| In this tutorial we will learn about  
+
| In this tutorial, we will learn about:
  
−
Basic '''Select statement'''  
+
basic '''Select''' statement,
  
 
|-  
 
|-  
 
| 00:12
 
| 00:12
−
|  '''Select''' with '''WHERE clause'''  
+
|  '''Select''' with '''WHERE''' clause,
  
 
|-  
 
|-  
 
| 00:15
 
| 00:15
−
|  '''Select''' with '''relational operators'''  
+
|  '''Select''' with '''relational operators''',
  
 
|-  
 
|-  
 
| 00:18
 
| 00:18
−
|  '''Select''' with '''logical operators'''  
+
|  '''Select''' with '''logical operators''',
  
 
|-  
 
|-  
 
| 00:21
 
| 00:21
−
|  '''Alias''' for column names  
+
|  '''Alias''' for '''column''' names.
  
 
|-  
 
|-  
 
| 00:24
 
| 00:24
−
| 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,
  
 
|-  
 
|-  
Line 44: Line 44:
 
|-  
 
|-  
 
| 00:39
 
| 00:39
−
| To follow this tutorial, you should have  
+
| To follow this tutorial, you should have basic understanding of '''database''' and '''tables'''.  
−
 
+
−
Basic understanding of '''database''' and '''tables'''.  
+
  
 
|-  
 
|-  
Line 58: Line 56:
 
|-  
 
|-  
 
| 00:56
 
| 00:56
−
| Right-click on the '''students table''' and select '''View Data''' and then '''View All Rows.'''  
+
| Right-click on the '''students''' table and select '''View Data''' and then '''View All Rows.'''  
  
 
|-  
 
|-  
 
| 01:04
 
| 01:04
−
| We can see the '''students table''' with the 4 records that we inserted earlier.  
+
| We can see the '''students''' table with the 4 '''record'''s that we inserted earlier.  
  
 
|-  
 
|-  
 
| 01:10
 
| 01:10
−
| I'll show how to insert records in the ''' Edit Data''' window.  
+
| I'll show how to insert '''record'''s in the ''' Edit Data''' window.  
  
 
|-  
 
|-  
 
| 01:19
 
| 01:19
−
| Click on the save icon on the top left of the toolbar to save the changes.  
+
| Click on the '''save''' icon on the top left of the toolbar to save the changes.  
  
 
|-  
 
|-  
 
| 01:25
 
| 01:25
−
| We can insert records either in this '''Edit data '''window or through the '''Insert statement.'''  
+
| We can insert records either in this '''Edit data '''window or through the '''Insert''' statement.
  
 
Close this window.  
 
Close this window.  
Line 82: Line 80:
 
| I’ll insert some more records for demonstration purpose.  
 
| I’ll insert some more records for demonstration purpose.  
  
−
I’ll open the SQL editor window and type the insert statement shown here.  
+
I’ll open the '''SQL editor window''' and type the '''insert''' statement shown here.  
  
 
|-  
 
|-  
 
| 01:43
 
| 01:43
−
| Note that, I have entered ‘NULL’ value in the '''date of birth''' column for '''student id ‘s012’.'''  
+
| Note that I have entered '''‘NULL’''' value in the '''date of birth''' column for '''student id ‘s012’.'''  
  
 
|-  
 
|-  
Line 94: Line 92:
 
|-  
 
|-  
 
| 01:59
 
| 01:59
−
| A field with a '''NULL''' value represents no value.  
+
| A '''field''' with a '''NULL''' value represents no value.  
  
 
|-  
 
|-  
 
| 02:03
 
| 02:03
−
| Execute the query.  
+
| '''Execute''' the '''query'''.  
  
 
Retrieve the data from the table to see the output.  
 
Retrieve the data from the table to see the output.  
Line 104: Line 102:
 
|-  
 
|-  
 
| 02:09
 
| 02:09
−
| So far we have inserted 14 records.  
+
| So far, we have inserted 14 records.  
  
 
|-  
 
|-  
Line 112: Line 110:
 
|-  
 
|-  
 
| 02:17
 
| 02:17
−
| Null is not the same as a zero value or space value.  
+
| '''Null''' is not the same as a zero value or space value.  
  
 
|-  
 
|-  
Line 122: Line 120:
 
| Pause the tutorial and do this assignment.  
 
| Pause the tutorial and do this assignment.  
  
−
Insert 10 records into the '''students table'''.  
+
Insert 10 records into the '''students''' table.  
  
 
|-  
 
|-  
Line 130: Line 128:
 
|-  
 
|-  
 
|02:41
 
|02:41
−
| The '''SELECT statement '''retrieves data from a '''database'''.  
+
| The '''SELECT''' statement retrieves data from a '''database'''.  
  
 
|-  
 
|-  
Line 142: Line 140:
 
|-  
 
|-  
 
| 02:57
 
| 02:57
−
| The basic syntax of the '''SELECT '''statement is:''' '''
+
| The basic syntax of the '''SELECT '''statement is:  
  
 
'''Select column-names from table-name;'''  
 
'''Select column-names from table-name;'''  
Line 148: Line 146:
 
|-  
 
|-  
 
| 03:05
 
| 03:05
−
| Now let us learn to write queries using '''Select statements'''.  
+
| Now let us learn to write queries using '''Select''' statements.  
  
 
|-  
 
|-  
Line 156: Line 154:
 
|-  
 
|-  
 
| 03:14
 
| 03:14
−
| Right-click on '''students''' node, and select '''Scripts''' and then click on '''SELECT script.'''  
+
| Right-click on '''students''' node and select '''Scripts''' and then click on '''SELECT script.'''  
 
   
 
   
 
|-  
 
|-  
 
| 03:21
 
| 03:21
−
| This is a simple '''Select statement '''which retrieves data from the '''students table'''.  
+
| This is a simple '''Select''' statement which retrieves data from the '''students table'''.  
  
 
|-  
 
|-  
Line 190: Line 188:
 
|-  
 
|-  
 
|04:04
 
|04:04
−
| '''SELECT with WHERE clause''' is used, To filter the rows which match the criteria that is specified and  To limit the number of rows  
+
| '''SELECT with WHERE clause''' is used  to filter the rows which match the criteria that is specified and  to limit the number of rows.
  
 
|-  
 
|-  
 
|04:14
 
|04:14
−
| The syntax of this '''statement''' is, '''SELECT column-names FROM table-name WHERE condition'''  
+
| The syntax of this statement is, '''SELECT column-names FROM table-name WHERE condition'''.
  
 
|-  
 
|-  
Line 206: Line 204:
 
|-  
 
|-  
 
| 04:34
 
| 04:34
−
| Type, '''SELECT ''' studentid, studentname, city, cgpa''' from students where city ='Mumbai'.'''  
+
| Type: '''SELECT studentid, studentname, city, cgpa from students where city ='Mumbai'.'''  
  
 
|-  
 
|-  
 
| 04:43
 
| 04:43
−
| Now, execute this query.  
+
| Now, '''execute''' this '''query'''.  
  
 
|-  
 
|-  
 
| 04:46
 
| 04:46
−
| It will retrieve the rows from the '''students table '''and display students from '''Mumbai''' city.  
+
| It will retrieve the rows from the '''students''' table and display students from '''Mumbai''' city.  
  
 
'''WHERE''' is followed by a condition that returns either '''true''' or '''false'''.  
 
'''WHERE''' is followed by a condition that returns either '''true''' or '''false'''.  
Line 220: Line 218:
 
|-  
 
|-  
 
| 04:57
 
| 04:57
−
| Here the '''City''' is the column name and ''''Mumbai'''' is the value to filter.  
+
| Here the '''City''' is the column name and ''''Mumbai'''' is the value to '''filter'''.  
  
 
|-  
 
|-  
 
| 05:03
 
| 05:03
−
| Now let’s change the city value to ' '''Bangalore'''' and execute the query.  
+
| Now, let’s change the city value to ' '''Bangalore'''' and execute the query.  
  
 
We can see that there are no records retrieved.  
 
We can see that there are no records retrieved.  
Line 230: Line 228:
 
|-  
 
|-  
 
| 05:12
 
| 05:12
−
| This is because the '''condition '''returns '''false '''on execution.
+
| This is because the '''condition '''returns '''false '''on execution  
  
 
|-  
 
|-  
 
| 05:17
 
| 05:17
−
| Which means there are no records that match with the given '''condition'''.  
+
| which means there are no records that match with the given '''condition'''.  
  
 
|-  
 
|-  

Revision as of 15:45, 5 February 2019

Time Narration
00:01 Welcome to the Spoken Tutorial on Select statement.
00:06 In this tutorial, we will learn about:

basic Select statement,

00:12 Select with WHERE clause,
00:15 Select with relational operators,
00:18 Select with logical operators,
00:21 Alias for column names.
00:24 To record this tutorial, I am using:

Ubuntu Linux 14.04 operating system,

00:31 PostgreSQL 9.3.x

and pgAdmin 1.18

00:39 To follow this tutorial, you should have basic understanding of database and tables.
00:46 For more details, refer to the RDBMS PostgreSQL series on this website.
00:53 Let us open pgAdmin.
00:56 Right-click on the students table and select View Data and then View All Rows.
01:04 We can see the students table with the 4 records that we inserted earlier.
01:10 I'll show how to insert records in the Edit Data window.
01:19 Click on the save icon on the top left of the toolbar to save the changes.
01:25 We can insert records either in this Edit data window or through the Insert statement.

Close this window.

01:33 I’ll insert some more records for demonstration purpose.

I’ll open the SQL editor window and type the insert statement shown here.

01:43 Note that I have entered ‘NULL’ value in the date of birth column for student id ‘s012’.
01:51 In PostgreSQL, the term NULL is used to represent a missing value or an unknown value.
01:59 A field with a NULL value represents no value.
02:03 Execute the query.

Retrieve the data from the table to see the output.

02:09 So far, we have inserted 14 records.
02:13 A NULL value field appears to be blank.
02:17 Null is not the same as a zero value or space value.
02:22 It implies that a database field value has not been stored.
02:27 Pause the tutorial and do this assignment.

Insert 10 records into the students table.

02:35 Next, we will see about how to use SELECT statement with many clauses.
02:41 The SELECT statement retrieves data from a database.
02:45 The data is returned in rows and columns format i.e. in a tabular format.
02:51 It has many clauses that we can combine to form a powerful query.
02:57 The basic syntax of the SELECT statement is:

Select column-names from table-name;

03:05 Now let us learn to write queries using Select statements.
03:10 Switch back to the PgAdmin main screen.
03:14 Right-click on students node and select Scripts and then click on SELECT script.
03:21 This is a simple Select statement which retrieves data from the students table.
03:26 You can specify the column names as shown here or use 'asterisk' to select all the columns.
03:34 I want the details of certain columns only.

So, type the code as shown here with specific column names.

03:43 The SQL statement will display - the student id, student name, city and cgpa columns

from the Students table.

03:54 Now, let’s execute the query to see the output.
03:58 The expected rows and columns are displayed.

Let’s move ahead.

04:04 SELECT with WHERE clause is used to filter the rows which match the criteria that is specified and to limit the number of rows.
04:14 The syntax of this statement is, SELECT column-names FROM table-name WHERE condition.
04:23 Records that satisfy the specified condition will be displayed on executing this statement.
04:30 Let’s switch back to the pgAdmin SQL Editor.
04:34 Type: SELECT studentid, studentname, city, cgpa from students where city ='Mumbai'.
04:43 Now, execute this query.
04:46 It will retrieve the rows from the students table and display students from Mumbai city.

WHERE is followed by a condition that returns either true or false.

04:57 Here the City is the column name and 'Mumbai' is the value to filter.
05:03 Now, let’s change the city value to ' Bangalore' and execute the query.

We can see that there are no records retrieved.

05:12 This is because the condition returns false on execution
05:17 which means there are no records that match with the given condition.
05:21 Next, we will see how to use comparison or relational operators in the WHERE clause.
05:28 Relational operators listed here, can also be used within the WHERE clause.
05:34 Switch back to the pgAdmin SQL Editor.
05:37 Type, Select studentid, studentname, city, cgpa from students where cgpa > 8.5
05:49 Execute the query.
05:51 The output shows details of students who have cgpa greater than 8.5
05:57 Let us type another query to retrieve the student details who are not from Mumbai city.
06:04 Note the 'Not equal to' operator.
06:07 It is symbolised by an open and close angular bracket.

Execute the query.

06:14 Likewise, we can use relational operators to filter data depending upon our requirement.
06:21 Next let’s learn how to use arithmetic operators in the Select statement.
06:27 When we need to do calculations within SQL statements, we can use arithmetic expressions.
06:33 Arithmetic expressions contain column names, arithmetic operators and numeric values.
06:40 Arithmetic operators will however, not work for character data type columns.
06:47 Let’s do a simple arithmetic calculation using some operators.

Clear the screen.

06:54 Type Select 2+15+98+45;

Execute to see the output.

07:03 Type Select 230 divided by 5;

Again execute the query to see the output.

07:11 Let us see how to use arithmetic operators with a column of the table.
07:16 We have a column called cgpa with numeric data type.

CGPA stands for Cumulative Grade Point Average.

07:26 Let us calculate the percentage from cgpa score.

Switch back to SQL Editor window.

07:33 Type the query as shown here.

What we want to do is multiply the cgpa with 9.5 to get the actual percentage.

07:44 Here, we are using the multiplication operator asterix to calculate the percentage.
07:50 Let’s execute the query now.
07:53 Percentage is calculated and displayed in a new column, as shown here.

But there is no specific column name for this column.

08:02 This percentage data is not stored in the database. It only displays the calculation.
08:09 Now I want to give a name to this column, so that it is more meaningful.

For that, we can use an alias name.

08:18 An alias is used to rename a column temporarily.
08:23 It appears as a heading in the output of the query.
08:27 Use column alias if the original column name is not meaningful or

when there is a column name conflict.

08:35 Here add the text as Percentage after the calculation column.

Execute the query to see the output.

08:44 We can also specify in capital letters as “PERCENTAGE” within double quotes.
08:51 Please note, Column names which are mixed-case or uppercase have to be within double quotes in postgresql.
08:59 Execute the query.
09:01 We can see the column name has changed to PERCENTAGE in capital letters.
09:07 Next we will see how to use logical operators within the Select statement.
09:13 Logical operators are used where more than one condition needs to be checked.
09:18 Or Using logical operators, we can combine two or more conditions.
09:25 The most commonly used logical operators are:

AND: This joins two or more conditions and returns results only when all the conditions are true.

09:38 OR: This joins two or more conditions and returns results when at least one of the conditions is true.
09:47 NOT: This operator results in the reverse of a condition.
09:53 Switch back to PgAdmin interface.

Let us see few examples for logical operators.

10:00 Type the code as shown.

Here we are checking for two conditions ie. Students who are from Pune city whose gender is Female.

10:11 It will return the output when both the conditions are true.

Notice how the 'AND' logical operator is written.

10:20 Execute the query to see the output.
10:23 Next, let’s change the 'AND' to 'OR' logical operator and see the output.
10:29 The records are retrieved if either one of the conditions is true.
10:34 That is either the student is from Pune city or gender is ‘Female’
10:40 This query will display students who are not from Mumbai or Chennai.
10:46 Here we have used the NOT and AND operators together.
10:52 Execute the query and see the output.
10:55 With this we come to the end of this tutorial.

Let us summarize.

11:01 In this tutorial, we have learnt about

Basic Select statement

11:05 Select with WHERE clause

Select with relational operators

11:10 Select with logical operators and

Alias for column names

11:15 As an assignment, write a Select statement to Display all columns from students table for the student name 'Ram'
11:24 Display studentid, studentname, dob of students whose cgpa is greater than 8 and less than 9.5
11:34 The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

11:40 The Spoken Tutorial Project Team

conducts workshops and gives certificates

For more details, please write to us.

11:49 Please post your timed queries in this forum.
11:53 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

12:04 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/Select-statement/English-timed&oldid=45695"