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 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