RDBMS-PostgreSQL/C2/Select-statement/English-timed

Time	Narration
00:01	Welcome to the Spoken Tutorial on Select statement.
00:06	In this tutorial, we will learn about: basic Select statement,
00:12	Select with WHERE clause,
00:15	Select with relational operators,
00:18	Select with logical operators,
00:21	Alias for column names.
00:24	To record this tutorial, I am using: Ubuntu Linux 14.04 operating system,
00:31	PostgreSQL 9.3.x and pgAdmin 1.18
00:39	To follow this tutorial, you should have basic understanding of database and tables.
00:46	For more details, refer to the RDBMS PostgreSQL series on this website.
00:53	Let us open pgAdmin.
00:56	Right-click on the students table and select View Data and then View All Rows.
01:04	We can see the students table with the 4 records that we inserted earlier.
01:10	I'll show how to insert records in the Edit Data window.
01:19	Click on the save icon on the top left of the toolbar to save the changes.
01:25	We can insert records either in this Edit data window or through the Insert statement. Close this window.
01:33	I’ll insert some more records for demonstration purpose. I’ll open the SQL editor window and type the insert statement shown here.
01:43	Note that I have entered ‘NULL’ value in the date of birth column for student id ‘s012’.
01:51	In PostgreSQL, the term NULL is used to represent a missing value or an unknown value.
01:59	A field with a NULL value represents no value.
02:03	Execute the query. Retrieve the data from the table to see the output.
02:09	So far, we have inserted 14 records.
02:13	A NULL value field appears to be blank.
02:17	Null is not the same as a zero value or space value.
02:22	It implies that a database field value has not been stored.
02:27	Pause the tutorial and do this assignment. Insert 10 records into the students table.
02:35	Next, we will see about how to use SELECT statement with many clauses.
02:41	The SELECT statement retrieves data from a database.
02:45	The data is returned in rows and columns format i.e. in a tabular format.
02:51	It has many clauses that we can combine to form a powerful query.
02:57	The basic syntax of the SELECT statement is: Select column-names from table-name;
03:05	Now let us learn to write queries using Select statements.
03:10	Switch back to the PgAdmin main screen.
03:14	Right-click on students node and select Scripts and then click on SELECT script.
03:21	This is a simple Select statement which retrieves data from the students table.
03:26	You can specify the column names as shown here or use 'asterisk' to select all the columns.
03:34	I want the details of certain columns only. So, type the code as shown here with specific column names.
03:43	The SQL statement will display - the student id, student name, city and cgpa columns from the Students table.
03:54	Now, let’s execute the query to see the output.
03:58	The expected rows and columns are displayed. Let’s move ahead.
04:04	SELECT with WHERE clause is used to filter the rows which match the criteria that is specified and to limit the number of rows.
04:14	The syntax of this statement is, SELECT column-names FROM table-name WHERE condition.
04:23	Records that satisfy the specified condition will be displayed on executing this statement.
04:30	Let’s switch back to the pgAdmin SQL Editor.
04:34	Type: SELECT studentid, studentname, city, cgpa from students where city ='Mumbai'.
04:43	Now, execute this query.
04:46	It will retrieve the rows from the students table and display students from Mumbai city. WHERE is followed by a condition that returns either true or false.
04:57	Here the City is the column name and 'Mumbai' is the value to filter.
05:03	Now, let’s change the city value to 'Bangalore' and execute the query. We can see that there are no records retrieved.
05:12	This is because the condition returns false on execution
05:17	which means there are no records that match with the given condition.
05:21	Next, we will see how to use comparison or relational operators in the WHERE clause.
05:28	Relational operators listed here, can also be used within the WHERE clause.
05:34	Switch back to the pgAdmin SQL Editor.
05:37	Type: Select studentid, studentname, city, cgpa from students where cgpa > 8.5
05:49	Execute the query.
05:51	The output shows details of students who have cgpa greater than 8.5
05:57	Let us type another query to retrieve the student details who are not from Mumbai city.
06:04	Note the 'Not equal to' operator.
06:07	It is symbolized by an open and close angular bracket. Execute the query.
06:14	Likewise, we can use relational operators to filter data depending upon our requirement.
06:21	Next let’s learn how to use arithmetic operators in the Select statement.
06:27	When we need to do calculations within SQL statements, we can use arithmetic expressions.
06:33	Arithmetic expressions contain column names, arithmetic operators and numeric values.
06:40	Arithmetic operators will however not work for character data type columns.
06:47	Let’s do a simple arithmetic calculation using some operators. Clear the screen.
06:54	Type: Select 2+15+98+45; Execute to see the output.
07:03	Type: Select 230 divided by 5; Again execute the query to see the output.
07:11	Let us see how to use arithmetic operators with a column of the table.
07:16	We have a column called cgpa with numeric data type. CGPA stands for Cumulative Grade Point Average.
07:26	Let us calculate the percentage from cgpa score. Switch back to SQL Editor window.
07:33	Type the query as shown here. What we want to do is, multiply the cgpa with 9.5 to get the actual percentage.
07:44	Here, we are using the multiplication operator asterisk, to calculate the percentage.
07:50	Let’s execute the query now.
07:53	Percentage is calculated and displayed in a new column, as shown here. But there is no specific column name for this column.
08:02	This percentage data is not stored in the database. It only displays the calculation.
08:09	Now I want to give a name to this column so that it is more meaningful. For that, we can use an alias name.
08:18	An alias is used to rename a column temporarily.
08:23	It appears as a heading in the output of the query.
08:27	Use column alias if the original column name is not meaningful or when there is a column name conflict.
08:35	Here, add the text as Percentage after the calculation column. Execute the query to see the output.
08:44	We can also specify in capital letters as “PERCENTAGE” within double quotes.
08:51	Please note, column names which are mixed-case or uppercase have to be within double quotes in postgresql.
08:59	Execute the query.
09:01	We can see the column name has changed to PERCENTAGE in capital letters.
09:07	Next we will see how to use logical operators within the Select statement.
09:13	Logical operators are used where more than one condition needs to be checked.
09:18	Or, using logical operators, we can combine two or more conditions.
09:25	The most commonly used logical operators are: AND: This joins two or more conditions and returns results only when all the conditions are true.
09:38	OR: This joins two or more conditions and returns results when at least one of the conditions is true.
09:47	NOT: This operator results in the reverse of a condition.
09:53	Switch back to PgAdmin interface. Let us see few examples for logical operators.
10:00	Type the code as shown. Here, we are checking for two conditions i.e. students who are from Pune city whose gender is Female.
10:11	It will return the output when both the conditions are true. Notice how the 'AND' logical operator is written.
10:20	Execute the query to see the output.
10:23	Next, let’s change the 'AND' to 'OR' logical operator and see the output.
10:29	The records are retrieved if either one of the conditions is true.
10:34	That is either the student is from Pune city or gender is ‘Female’
10:40	This query will display students who are not from Mumbai or Chennai.
10:46	Here we have used the NOT and AND operators together.
10:52	Execute the query and see the output.
10:55	With this, we come to the end of this tutorial. Let us summarize.
11:01	In this tutorial, we have learnt about basic Select statement,
11:05	Select with WHERE clause, Select with relational operators,
11:10	Select with logical operators and Alias for column names.
11:15	As an assignment, write a Select statement to display all columns from students table for the student name 'Ram'.
11:24	Display studentid, studentname, dob of students whose cgpa is greater than 8 and less than 9.5
11:34	The video at the following link summarizes the Spoken Tutorial project. Please download and watch it.
11:40	The Spoken Tutorial Project team conducts workshops and gives certificates. For more details, please write to us.
11:49	Please post your timed queries in this forum.
11:53	Spoken Tutorial project is funded by NMEICT, MHRD, Government of India. More information on this mission is available at this link.
12:04	This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

PoojaMoolya, Sandhya.np14