RDBMS-PostgreSQL/C2/Select-with-Aggregate-functions/English-timed

From Script | Spoken-Tutorial
Jump to: navigation, search
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