RDBMS-PostgreSQL/C2/Select-with-Aggregate-functions/English-timed
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 data 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 summarises 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. |