Difference between revisions of "RDBMS-PostgreSQL/C2/Select-with-Aggregate-functions/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 with Aggregate functions.''' |- | 00:07 | In this tutorial w...")
 
 
Line 9: Line 9:
 
|-  
 
|-  
 
| 00:07
 
| 00:07
−
| In this tutorial we will learn more '''clauses''' that can be used with the '''select statement, '''such as-  
+
| In this tutorial, we will learn more clauses that can be used with the '''select''' statement, such as-  
  
−
'''Distinct'''  
+
'''Distinct''',
  
 
|-  
 
|-  
 
|00:16
 
|00:16
−
| '''Between'''  
+
| '''Between''',
  
−
'''Like'''  
+
'''Like''',
  
 
|-  
 
|-  
 
|00:19
 
|00:19
−
|  '''In'''  
+
|  '''In''',
  
−
'''Is Null''' and  '''Aggregate functions'''  
+
'''Is Null''' and  '''Aggregate functions'''.
  
 
|-  
 
|-  
 
| 00:26
 
| 00:26
−
| 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,
  
−
'''PostgreSQL 9.3.x'''  
+
'''PostgreSQL 9.3.x''',
  
 
'''pgAdmin 1.18'''  
 
'''pgAdmin 1.18'''  
Line 37: Line 37:
 
|-  
 
|-  
 
| 00:41
 
| 00:41
−
| To follow this tutorial, you should have basic understanding of database and tables.  
+
| To follow this tutorial, you should have basic understanding of '''database''' and '''table'''s.  
  
 
|-  
 
|-  
Line 49: Line 49:
 
|-  
 
|-  
 
| 01:01
 
| 01:01
−
| '''DISTINCT keyword '''is used in the '''SELECT statement '''to list distinct and unique values.  
+
| '''DISTINCT''' keyword is used in the '''SELECT''' statement to list distinct and unique values.  
  
−
It removes the duplicate rows or column values from the query result.  
+
It removes the duplicate '''row'''s or '''column''' values from the '''query''' result.  
  
 
|-  
 
|-  
Line 69: Line 69:
 
|-  
 
|-  
 
| 01:38
 
| 01:38
−
| Type the code as shown here.  
+
| Type the '''code''' as shown here.  
  
−
This '''select statement '''will retrieve the unique city names from the '''students table'''.  
+
This '''select''' statement will retrieve the unique city names from the '''students''' table.  
  
 
|-  
 
|-  
Line 79: Line 79:
 
|-  
 
|-  
 
| 01:54
 
| 01:54
−
| Let us Execute the query to see the output.  
+
| Let us '''execute''' the query to see the '''output'''.  
  
 
|-  
 
|-  
 
| 01:59
 
| 01:59
−
| Next, we will learn about the '''BETWEEN operator.'''  
+
| Next, we will learn about the '''BETWEEN''' operator.
  
 
|-  
 
|-  
 
| 02:03
 
| 02:03
−
| '''BETWEEN operator''' is used to retrieve values within a given range.  
+
| '''BETWEEN''' operator is used to retrieve values within a given range.  
  
 
|-  
 
|-  
Line 95: Line 95:
 
|-  
 
|-  
 
| 02:19
 
| 02:19
−
| Let's see an example for between operator.  
+
| Let's see an example for '''between''' operator.  
  
 
|-  
 
|-  
Line 117: Line 117:
 
|-  
 
|-  
 
| 02:52
 
| 02:52
−
| This '''query''' will show all rows from the''' students table,''' where the '''cgpa''' value is between 7.8 and 8.5.  
+
| This '''query''' will show all rows from the''' students''' table where the '''cgpa''' value is between 7.8 and 8.5.  
  
 
|-  
 
|-  
Line 143: Line 143:
 
|-  
 
|-  
 
| 03:32
 
| 03:32
−
| Next we will learn about the '''LIKE operator.'''  
+
| Next we will learn about the '''LIKE''' operator.
  
 
|-  
 
|-  
Line 151: Line 151:
 
|-  
 
|-  
 
|03:42
 
|03:42
−
| It allows '''wildcard characters '''to be used within the '''WHERE clause'''.  
+
| It allows '''wildcard characters '''to be used within the '''WHERE''' clause.  
  
 
|-  
 
|-  
 
| 03:47
 
| 03:47
−
| There are two '''wildcards '''that can be used in conjunction with the '''LIKE operator''':
+
| There are two '''wildcards '''that can be used in conjunction with the '''LIKE operator'''-
  
 
|-  
 
|-  
Line 177: Line 177:
 
| Suppose I don't remember the exact name of a student.  
 
| Suppose I don't remember the exact name of a student.  
  
−
But I remember that the student’s name begins with the letter ''''Ra''''.  
+
But I remember that the student’s name begins with the letter 'Ra'.  
  
 
|-  
 
|-  
 
|04:22
 
|04:22
−
| It is very difficult to search the entire '''students table, '''as there are many rows in the '''table.'''  
+
| It is very difficult to search the entire '''students''' table as there are many rows in the '''table.'''  
  
 
|-  
 
|-  
 
| 04:29
 
| 04:29
−
| To make the search easy, we can use the '''LIKE operator''' as shown here.  
+
| To make the search easy, we can use the '''LIKE''' operator as shown here.  
  
 
|-  
 
|-  
 
| 04:35
 
| 04:35
−
| Here, all the student's names start with ''''Ra'''' and is followed by any sequence of characters.  
+
| Here, all the student's names start with 'Ra' and is followed by any sequence of characters.  
  
 
This is called '''pattern matching'''.  
 
This is called '''pattern matching'''.  
Line 203: Line 203:
 
|-  
 
|-  
 
|04:56
 
|04:56
−
| This '''query '''returns those rows where the students’ names end with ''''la'.'''
+
| This '''query '''returns those rows where the students’ names end with 'la'.  
  
 
|-  
 
|-  
 
|05:03
 
|05:03
−
| There can be any number of characters before ''''la'.'''
+
| There can be any number of characters before 'la'.  
  
 
|-  
 
|-  
Line 213: Line 213:
 
| Let us see another pattern.  
 
| Let us see another pattern.  
  
−
This '''statement''' returns all the rows where the student’s name has''' 'am' '''in the middle.  
+
This statement returns all the rows where the student’s name has 'am' in the middle.  
  
 
|-  
 
|-  
 
|05:21
 
|05:21
−
| Note''' percent '''sign before and after ''''am'''' .  
+
| Note percent sign before and after 'am'.  
  
 
|-  
 
|-  
Line 225: Line 225:
 
|-  
 
|-  
 
|05:34
 
|05:34
−
| This will fetch rows of students whose name has three characters and has ''''a'''' in the 2<sup>nd</sup> position.  
+
| This will fetch rows of students whose name has three characters and has 'a' in the 2<sup>nd</sup> position.  
  
 
|-  
 
|-  
Line 231: Line 231:
 
|  '''Underscore 'r' percent '''matches students whose names begin with a single character.  
 
|  '''Underscore 'r' percent '''matches students whose names begin with a single character.  
  
−
It is followed by ''''r'''' and ends with any number of characters.  
+
It is followed by 'r' and ends with any number of characters.  
  
 
Let's see the output.  
 
Let's see the output.  
Line 237: Line 237:
 
|-  
 
|-  
 
| 05:58
 
| 05:58
−
| Next we will see how to use '''IN operator.'''  
+
| Next we will see how to use '''IN''' operator.
  
 
|-  
 
|-  
 
| 06:03
 
| 06:03
−
| Use '''IN operator '''in the '''WHERE clause '''to check if the value matches any value in a given list.  
+
| Use '''IN''' operator in the '''WHERE '''clause to check if the value matches any value in a given list.  
  
−
The syntax is: '''Select column-name from table-name WHERE column-name IN ( value1, value2...)''' etc
+
The syntax is: '''Select column-name from table-name WHERE column-name IN ( value1, value2...)''' etc.
  
 
|-  
 
|-  
Line 251: Line 251:
 
|-  
 
|-  
 
| 06:25
 
| 06:25
−
| This '''statement''' will return all rows from the '''students table ''' where the city is either '''Chennai, Nagpur '''or '''Hyderabad'''.  
+
| This statement will return all rows from the '''students''' table  where the city is either Chennai, Nagpur or Hyderabad.  
  
 
|-  
 
|-  
 
| 06:35
 
| 06:35
−
| It helps to reduce the usage of multiple '''OR conditions''' to get the same result.  
+
| It helps to reduce the usage of multiple '''OR''' conditions to get the same result.  
  
−
Let us see the output  
+
Let us see the output.
  
 
|-  
 
|-  
 
| 06:44
 
| 06:44
−
| Next we will see how to use the '''IS NULL operator '''in the '''select statement'''.  
+
| Next we will see how to use the '''IS NULL''' operator in the '''select''' statement.  
  
 
|-  
 
|-  
 
| 06:51
 
| 06:51
−
| This '''statement '''will list down the records where the data of birth column is empty.  
+
| This statement will list down the '''record'''s where the '''date of birth''' column is empty.  
  
 
|-  
 
|-  
 
|06:58
 
|06:58
−
| In the same way, we can also use the '''IS NOT NULL operator''', as shown here.  
+
| In the same way, we can also use the '''IS NOT NULL operator''' as shown here.  
  
 
|-  
 
|-  
Line 285: Line 285:
 
|-  
 
|-  
 
| 07:22
 
| 07:22
−
| '''List of aggregate functions'''  
+
| List of '''aggregate functions''' -
  
 
|-  
 
|-  
 
| 07:25
 
| 07:25
−
| '''Count''' – It Returns the number of rows in a table.
+
| '''Count''' – It returns the number of rows in a table.
  
 
|-  
 
|-  
 
| 07:30
 
| 07:30
−
| '''Sum''' – Returns the sum of a selected column  
+
| '''Sum''' – Returns the sum of a selected column.
  
 
|-  
 
|-  
 
| 07:34
 
| 07:34
−
| '''Max''' - Returns the largest value of a specified column  
+
| '''Max''' - Returns the largest value of a specified column.
  
 
|-  
 
|-  
 
| 07:39
 
| 07:39
−
| '''Min''' – Returns the smallest value of a specified column  
+
| '''Min''' – Returns the smallest value of a specified column.
  
 
|-  
 
|-  
 
| 07:44
 
| 07:44
−
| '''Avg''' – Returns the average value for a specified column  
+
| '''Avg''' – Returns the average value for a specified column.
 
   
 
   
 
|-  
 
|-  
Line 313: Line 313:
 
|-  
 
|-  
 
| 07:53
 
| 07:53
−
| Let’s say, we want to count the number of existing records in the '''students table.'''  
+
| Let’s say, we want to count the number of existing records in the '''students''' table.
  
 
For that, type the code as shown here.  
 
For that, type the code as shown here.  
Line 319: Line 319:
 
|-  
 
|-  
 
| 08:03
 
| 08:03
−
| The '''count open parentheses asterisk close parentheses function '''returns the number of rows that are fetched by the '''SELECT statement.'''  
+
| The '''count open parentheses asterisk close parentheses''' function returns the number of rows that are fetched by the '''SELECT''' statement.
  
 
|-  
 
|-  
Line 327: Line 327:
 
|-  
 
|-  
 
| 08:17
 
| 08:17
−
| There are 14 records in the '''students table.'''  
+
| There are 14 records in the '''students''' table.
  
 
|-  
 
|-  
Line 349: Line 349:
 
|-  
 
|-  
 
| 08:45
 
| 08:45
−
| Type the code and execute.  
+
| Type the code and '''execute'''.  
  
 
|-  
 
|-  
 
| 08:49
 
| 08:49
−
| Here we can see the '''date of birth''' column value is '''NULL''' for the student ''''Ram charan'.'''
+
| Here, we can see the '''date of birth''' column value is '''NULL''' for the student 'Ram charan'.  
  
 
|-  
 
|-  
Line 359: Line 359:
 
| I have left '''date of birth''' column blank during record creation for this student.  
 
| I have left '''date of birth''' column blank during record creation for this student.  
  
−
So the''' count function''' displays the output as 13.  
+
So the''' count''' function displays the output as 13.  
  
 
|-  
 
|-  
 
| 09:07
 
| 09:07
−
| Next let us see how to use the''' SUM function.'''  
+
| Next, let us see how to use the''' SUM''' function.
  
 
|-  
 
|-  
Line 389: Line 389:
 
|-  
 
|-  
 
| 09:42
 
| 09:42
−
| This '''select statement '''returns the maximum '''cgpa''' score from the '''students table'''.  
+
| This '''select''' statement returns the maximum '''cgpa''' score from the '''students''' table.  
  
 
9.6 is the highest '''cgpa''' score in the '''students''' table.  
 
9.6 is the highest '''cgpa''' score in the '''students''' table.  
Line 407: Line 407:
 
| In this tutorial, we have learnt about More clauses that can be used with the '''select statement, '''such as-  
 
| In this tutorial, we have learnt about More clauses that can be used with the '''select statement, '''such as-  
  
−
'''Distinct''' ,  '''Between''' ,  '''Like''' ,  '''In''' , '''Is Null''' and '''Aggregate functions'''  
+
'''Distinct''' ,  '''Between''' ,  '''Like''' ,  '''In''' , '''Is Null''' and '''Aggregate functions'''.
 
   
 
   
 
|-  
 
|-  
 
| 10:21
 
| 10:21
−
| As an assignment, write '''select statements''' to Display all columns from '''students table''' where the student name ends with ''''a''''
+
| As an assignment, write '''select statements''' to display all columns from '''students''' table where the student name ends with 'a'.
  
 
|-  
 
|-  
Line 419: Line 419:
 
|-  
 
|-  
 
| 10:36
 
| 10:36
−
| 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 425: Line 425:
 
|-  
 
|-  
 
| 10:44
 
| 10:44
−
| The''' Spoken Tutorial Project''' Team
+
| The''' Spoken Tutorial Project''' team
−
 
+
 
conducts workshops and  gives certificates  
 
conducts workshops and  gives certificates  
  
Line 437: Line 436:
 
|-  
 
|-  
 
| 10:58
 
| 10:58
−
| 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 443: Line 442:
 
|-  
 
|-  
 
| 11:11
 
| 11:11
−
| 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 21:11, 5 February 2019

Time Narration
00:01 Welcome to the Spoken Tutorial on Select with Aggregate functions.
00:07 In this tutorial, we will learn more clauses that can be used with the select statement, such as-

Distinct,

00:16 Between,

Like,

00:19 In,

Is Null and Aggregate functions.

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

Ubuntu Linux 14.04 operating system,

PostgreSQL 9.3.x,

pgAdmin 1.18

00:41 To follow this tutorial, you should have basic understanding of database and tables.
00:48 For more details, refer to the RDBMS – PostgreSQL series on this website.
00:55 Let’s begin. First we will learn about the DISTINCT clause.
01:01 DISTINCT keyword is used in the SELECT statement to list distinct and unique values.

It removes the duplicate rows or column values from the query result.

01:18 The syntax is as follows:

SELECT DISTINCT column-names FROM table-name WHERE [condition]

01:28 Let us open the pgAdmin for the demonstration.
01:33 Click on the SQL icon to open the SQL Editor.
01:38 Type the code as shown here.

This select statement will retrieve the unique city names from the students table.

01:48 It will eliminate duplicate values from the specified column i.e. city.
01:54 Let us execute the query to see the output.
01:59 Next, we will learn about the BETWEEN operator.
02:03 BETWEEN operator is used to retrieve values within a given range.
02:09 The syntax is: SELECT column-names FROM table-name WHERE column-name BETWEEN value1 and value2;
02:19 Let's see an example for between operator.
02:23 Let's clear the screen and type the code for the query.
02:28 Say, I want to retrieve details of students who are born between January 1995 and January 1996.
02:38 Let's see the output.

Notice the output shows the list of students as expected.

02:47 We will see another example for BETWEEN operator.
02:52 This query will show all rows from the students table where the cgpa value is between 7.8 and 8.5.
03:02 Let us see the output.
03:05 Notice from the output that the value is inclusive of 7.8 and 8.5.
03:13 The same query can also be written as shown here.
03:18 Instead of BETWEEN operator, we can use- greater than or equal to and

lesser than or equal to operators.

03:27 Execute the query to see the output.
03:32 Next we will learn about the LIKE operator.
03:36 LIKE operator is used to match text values against a given pattern.
03:42 It allows wildcard characters to be used within the WHERE clause.
03:47 There are two wildcards that can be used in conjunction with the LIKE operator-
03:53 Percent (%):

The percent sign matches any sequence of string of any length.

04:00 Underscore (_):

The underscore matches a single character.

04:06 We will see few examples using percent wildcard.
04:11 Suppose I don't remember the exact name of a student.

But I remember that the student’s name begins with the letter 'Ra'.

04:22 It is very difficult to search the entire students table as there are many rows in the table.
04:29 To make the search easy, we can use the LIKE operator as shown here.
04:35 Here, all the student's names start with 'Ra' and is followed by any sequence of characters.

This is called pattern matching.

04:48 Let us see a few more examples.
04:52 Type the code as shown.
04:56 This query returns those rows where the students’ names end with 'la'.
05:03 There can be any number of characters before 'la'.
05:10 Let us see another pattern.

This statement returns all the rows where the student’s name has 'am' in the middle.

05:21 Note percent sign before and after 'am'.
05:27 Next we will see how to use underscore to match a single character.
05:34 This will fetch rows of students whose name has three characters and has 'a' in the 2nd position.
05:44 Underscore 'r' percent matches students whose names begin with a single character.

It is followed by 'r' and ends with any number of characters.

Let's see the output.

05:58 Next we will see how to use IN operator.
06:03 Use IN operator in the WHERE clause to check if the value matches any value in a given list.

The syntax is: Select column-name from table-name WHERE column-name IN ( value1, value2...) etc.

06:22 Let us see an example.
06:25 This statement will return all rows from the students table where the city is either Chennai, Nagpur or Hyderabad.
06:35 It helps to reduce the usage of multiple OR conditions to get the same result.

Let us see the output.

06:44 Next we will see how to use the IS NULL operator in the select statement.
06:51 This statement will list down the records where the date of birth column is empty.
06:58 In the same way, we can also use the IS NOT NULL operator as shown here.
07:07 Let us now learn about aggregate function.
07:11 Aggregate functions are PostgreSQL built-in functions.
07:16 It operates on several rows of a query and returns a single result.
07:22 List of aggregate functions -
07:25 Count – It returns the number of rows in a table.
07:30 Sum – Returns the sum of a selected column.
07:34 Max - Returns the largest value of a specified column.
07:39 Min – Returns the smallest value of a specified column.
07:44 Avg – Returns the average value for a specified column.
07:49 Switch back to SQL Editor window.
07:53 Let’s say, we want to count the number of existing records in the students table.

For that, type the code as shown here.

08:03 The count open parentheses asterisk close parentheses function returns the number of rows that are fetched by the SELECT statement.
08:13 Execute this query to see the output.
08:17 There are 14 records in the students table.
08:21 We can also specify the column name in the function as shown here.
08:27 Once again execute the query.
08:31 Why is it showing 13?

Because the count function only counts values which are not NULL.

08:40 Let us see the output when we specify the date of birth column.
08:45 Type the code and execute.
08:49 Here, we can see the date of birth column value is NULL for the student 'Ram charan'.
08:56 I have left date of birth column blank during record creation for this student.

So the count function displays the output as 13.

09:07 Next, let us see how to use the SUM function.
09:11 Let us clear the screen.
09:14 Type the code as shown here and execute the query.

This returns the summed-up value of the cgpa column.

09:24 You can also give an additional 'Where' condition as shown here.
09:30 This returns the sum of cgpa values of the students who are from Mumbai city.
09:38 Next, let us find the maximum cgpa score.
09:42 This select statement returns the maximum cgpa score from the students table.

9.6 is the highest cgpa score in the students table.

09:54 Likewise, you can try the remaining aggregate functions on your own.
10:00 With this we come to the end of this tutorial.

Let us summarize.

10:06 In this tutorial, we have learnt about More clauses that can be used with the select statement, such as-

Distinct , Between , Like , In , Is Null and Aggregate functions.

10:21 As an assignment, write select statements to display all columns from students table where the student name ends with 'a'.
01:31 Display the minimum and average of the cgpa column.
10:36 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

10:44 The Spoken Tutorial Project team

conducts workshops and gives certificates

For more details, please write to us.

10:54 Please post your timed queries in this forum.
10:58 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

11:11 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-with-Aggregate-functions/English-timed&oldid=45703"