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

>>Title of script: Select with Aggregate functions

Author: Nirmala Venkat

Keywords: RDBMS, PostgreSQL, pgAdmin, DISTINCT, LIKE, IN, IS NULL, BETWEEN, aggregate functions, video tutorial

Visual Cue	Narration
Slide 1:	Welcome to the Spoken Tutorial on Select with Aggregate functions.
Slide 2: Learning Objectives	In this tutorial we will learn more clauses that can be used with the select statement, such as- Distinct Between Like In Is Null and Aggregate functions
Slide 3: System requirement	To record this tutorial, I am using Ubuntu Linux 14.04 operating system PostgreSQL 9.3.x pgAdmin 1.18
Slide 4: Pre-requisites	To follow this tutorial, you should have basic understanding of database and tables. For more details, refer to the RDBMS – PostgreSQL series on this website.
	Let’s begin. First we will learn about the DISTINCT clause.
Slide 5: DISTINCT Note: Duplicate values are not removed from the database	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.
Slide 5: DISTINCT	The syntax is as follows: SELECT DISTINCT column-names FROM table-name WHERE [condition]
Open pgAdmin	Let us open the pgAdmin for the demonstration.
Click on the SQL icon on the toolbar	Click on the SQL icon to open the SQL Editor.
Type select distinct city from students	Type the code as shown here. This select statement will retrieve the unique city names from the students table. It will eliminate duplicate values from the specified column i.e. city.
Execute the query	Execute the query to see the output. <<PAUSE>>
	Next, we will learn about the BETWEEN operator.
Slide 6: BETWEEN	BETWEEN operator is used to retrieve values within a given range. The syntax is: SELECT column-names FROM table-name WHERE column-name BETWEEN value1 and value2;
	Let's see an example for between operator.
	Let's clear the screen and type the code for the query. Say, I want to retrieve details of students who are born between January 1995 and January 1996.
Type and execute the query Select * from students where dob between '1995-01-01' and '1996-01-01'	Let's see the output. Notice the output shows the list of students as expected. <<PAUSE>>
	We will see another example for BETWEEN operator.
Type and execute the code Select * from students where cgpa 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. Notice from the output that the value is inclusive of 7.8 and 8.5. <<PAUSE>>
Type the code and show the result Select * from students where cgpa >= 7.8 and cgpa <= 8.5	The same query can also be written as shown here. Instead of BETWEEN operator, we can use- greater than or equal to and lesser than or equal to operators.
Execute the query	Execute the query to see the output. <<PAUSE>>
	Next we will learn about the LIKE operator.
Slide 7(a): LIKE	LIKE operator is used to match text values against a given pattern. It allows wildcard characters to be used within the WHERE clause.
Slide 7(b): LIKE	There are two wildcards that can be used in conjunction with the LIKE operator: Percent (%): The percent sign matches any sequence of string of any length. Underscore (_): The underscore matches a single character.
	We will see few examples using percent wildcard.
	Suppose I don't remember the exact name of a student. But I remember that the student’s name begins with the letter 'Ra'. It is very difficult to search the entire students table, as there are many rows in the table.
Type the code	To make the search easy, we can use the LIKE operator as shown here.
Select * from students where studentname like 'Ra %';	Here, all the student's names start with 'Ra' and is followed by any sequence of characters. This is called pattern matching.
	Let us see a few more examples.
Select * from students where studentname like '%la';	Type the code as shown. This query returns those rows where the students’ names end with 'la'. There can be any number of characters before 'la'.
Select * from students where studentname like '%am%';	Let us see another pattern. This statement returns all the rows where the student’s name has 'am' in the middle. Note percent sign before and after 'am' . <<PAUSE>>
	Next we will see how to use underscore to match a single character.
Select * from students where studentname like '_a_';	This will fetch rows of students whose name has three characters and has 'a' in the 2nd position.
Select * from students where studentname like '_r%’;	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. <<PAUSE>>
	Next we will see how to use IN operator.
Slide 8: IN	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...)
	Let us see an example.
Select * from students where city IN ('Chennai', 'Nagpur', 'Hyderabad')	This statement will return all rows from the students table where the city is either Chennai, Nagpur or Hyderabad. It helps to reduce the usage of multiple OR conditions to get the same result. Let us see the output <<PAUSE>>
	Next we will see how to use the IS NULL operator in the select statement.
Type the code Select * from students where dob is null;	This statement will list down the records where the data of birth column is empty.
Type the code Select * from students where dob is not null;	In the same way, we can also use the IS NOT NULL operator, as shown here. <<PAUSE>>
	Let us now learn about aggregate function.
Slide 5: Aggregate function	Aggregate functions are PostgreSQL built-in functions. It operates on several rows of a query and returns a single result.
Slide 6: List of aggregate functions	List of aggregate functions Count – Returns the number of rows Sum – Returns the sum of a selected column Max - Returns the largest value of a specified column Min – Returns the smallest value of a specified column Avg – Returns the average value for a specified column
	Switch back to SQL Editor window.
Type, SELECT count(*) FROM students;	Let’s say, we want to count the number of existing records in the students table. For that, type the code as shown here. The count open parentheses asterisk close parentheses function returns the number of rows that are fetched by the SELECT statement.
Click on the execute button.	Execute this query to see the output.
	There are 14 records in the students table.
SELECT count(dob) from students;	We can also specify the column name in the function as shown here.
Click on the execute button.	Once again execute the query.
Point to the output	Why is it showing 13? Because the count function only counts values which are not NULL.
	Let us see the output when we specify the dob column.
Select student name, dob from students	Type the code and execute.
Point to the output	Here we can see the dob column value is NULL for the student 'Ram charan'. I have left dob column blank during record creation for this student. So the count function displays the output as 13. <<PAUSE>>
	Next let us see how to use the SUM function.
Click on clear edit window icon.	Let’s clear the screen.
Type, select sum(cgpa) from students	Type the code as shown here and execute the query. This returns the summed-up value of the cgpa column.
Type, select sum(cgpa) from students where city='Mumbai'	You can also give an additional 'Where' condition as shown here.
Highlight/Point to output	This returns the sum of cgpa values of the students who are from Mumbai city.
	Let us find the maximum cgpa score.
Type, select Max(cgpa) from students	This select statement returns the maximum cgpa score from the students table. 9.6 is the highest cgpa score in the students table. <<PAUSE>>
	Likewise, you can try the remaining aggregate functions on your own.
	With this we come to the end of this tutorial. Let us summarize.
Summary	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
Assignment	As an assignment, write select statements to Display all columns from students table where the student name ends with 'a' Display the minimum and average of the cgpa column.
Slide 12: About Spoken Tutorial project	The video at the following link summarises the Spoken Tutorial project. Please download and watch it.
Slide 13: Spoken Tutorial workshops	The Spoken Tutorial Project Team conducts workshops gives certificates For more details, please write to us.
Slide 13: Forum for specific questions	Please post your timed queries in this forum.
Slide 14: Acknowledgement	Spoken Tutorial project is funded by NMEICT, MHRD, Government of India. More information on this mission is available at this link.
	This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nancyvarkey, Nirmala Venkat