RDBMS-PostgreSQL/C2/Select-statement/English-timed
From Script | Spoken-Tutorial
| 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. |
