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

From Script | Spoken-Tutorial
Revision as of 16:43, 5 February 2019 by Sandhya.np14 (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
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