Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Basic SQL SELECT Statements

In this article, we will learn about SQL SELECT statements and how to use them to retrieve data from our database.

SQL SELECT statements are data manipulation statements which are used to retrieve the data from the database and manipulate it according to the use. All the statements in the SQL are case insensitive, so is the SELECT statement. SELECT statements are the most widely used statements and are often referred to as queries. It can give you all the rows from the database or a particular column or attribute value depending on the conditions that you have specified in the statement.

SQL SELECT statements can be used in a variety of forms. We will be discussing here some of the common and basic forms which are used in day to day life. The basic form of a select statement is like:

SELECT <*|attribute name| expression>

FROM <table name>

WHERE <Condition>

Here:

  • SELECT Keyword is used to specify the name of columns that are to be displayed.
  • FROM specifies the name of the table from where the data is to be retrieved.
  • WHERE can be used to specify a condition on the basis of which data will be retrieved.

Suppose we have a table called Author in our database which looks like:

Author

AUTHOR_ID FIRST_NAME LAST_NAME PHONE BOOKS SALARY WORKING_FOR
1 John Doe 999123456 5 24000 ABC
5 Steve Brown 698296556 5 45000 ABC
7 Ben Stuart 877034486 4 15000 XYZ
4 April Berker 573902845 9 78900 ABC
2 Stefan Richardson 234551738 7 45000 XYZ
3 Elena Snow 890376186 5 20987 PQR
6 Mark Wright 905684728 10 100000 PQR
Step 1: In this step we will learn to select all the rows from a database using SELECT statement.

Listing 1: SQL statement to select all rows from database

SELECT *

FROM Author

When we run the above query, then we will get all the rows and columns of the table ‘Author’. ‘*’ is a wild character which is used to specify that we want to select all the data.

The output of the following query is as follows:

Output 1:

AUTHOR_ID FIRST_NAME LAST_NAME PHONE BOOKS SALARY WORKING_FOR
1 John Doe 999123456 5 24000 ABC
5 Steve Brown 698296556 5 45000 ABC
7 Ben Stuart 877034486 4 15000 XYZ
4 April Berker 573902845 9 78900 ABC
2 Stefan Richardson 234551738 7 45000 XYZ
3 Elena Snow 890376186 5 20987 PQR
6 Mark Wright 905684728 10 100000 PQR
Result of Output 1

Figure 1: Result of Output 1

Step 2: In this step we will learn to select specific columns from a table.

Listing 2: SQL query to select specific columns

SELECT Author_Id,First_Name,Last_Name

FROM Author

When we run the above query, then we will get only Author_Id, First_Name and Last_Name columns. We have to specify explicitly the name of each column that we want to retrieve after the SELECT keyword.

The output of the following query is as follows:

Output 2:

AUTHOR_ID FIRST_NAME LAST_NAME
1 John Doe
5 Steve Brown
7 Ben Stuart
4 April Berker
2 Stefan Richardson
3 Elena Snow
6 Mark Wright
Result of Output 2

Figure 2: Result of Output 2

Step 3: In this step we will learn to select specific rows from a table.

Listing 3: SQL query to select specific rows


SELECT *

FROM Author

WHERE Working_For='ABC'

When we run the above query, then we will get rows in which value of attribute ‘Working_For’ is ‘ABC’ . We have to specify the condition on the basis of which rows will be selected.

The output of the following query is as follows:

Output 3:

AUTHOR_ID FIRST_NAME LAST_NAME PHONE BOOKS SALARY WORKING_FOR
1 John Doe 999123456 5 24000 ABC
5 Steve Brown 698296556 5 45000 ABC
4 April Berker 573902845 9 78900 ABC
Result of Output 3

Figure 3: Result of Output 3

Step 4: In this step we will learn to select distinct rows from a table.

Listing 4: SQL query to select distinct rows

SELECT DISTINCT  Working_For

FROM Author

When we run the above query, then we will get rows only having distinct values of the attribute ‘Working_For’ . We have used the keyword ‘DISTINCT’ together with the column name to get all the distinct rows.

The output of the following query is as follows:

Output 4:

WORKING_FOR
PQR
ABC
XYZ
Result of Output 4

Figure 4: Result of Output 4

Step 5: In this step we will learn to use operators to create expressions in Select statement.

Listing 5: SQL query to create expressions in statement.

SELECT Author_Id, First_Name, Last_Name, Salary, 12*Salary

FROM Author

When we run the above query, then we will get columns named as ‘Author_Id’,’First_Name’, ‘Last_Name’, ‘Salary’ and an extra column named ‘12*Salary’ and having the value of expression:12*Salary.

The output of the following query is as follows:

Output 5:

AUTHOR_ID FIRST_NAME LAST_NAME SALARY 12*SALARY
1 John Doe 24000 288000
5 Steve Brown 45000 540000
7 Ben Stuart 15000 180000
4 April Berker 78900 946800
2 Stefan Richardson 45000 540000
3 Elena Snow 20987 251844
6 Mark Wright 100000 1200000
Result of Output 5

Figure 5: Result of Output 5

Step 6: In this step we will learn to rename a column heading in the output of query.

Listing 6: SQL query to rename a column heading.

SELECT Author_Id, First_Name, Last_Name,  12*Salary  AS  Yearly_Salary

FROM Author

When we run the above query, then we will get columns named as ‘Author_Id’,’First_Name’, ‘Last_Name’ and an extra column named ‘Yearly_Salary’ and having the value as 12*Salary. We have used the keyword ‘AS’ to rename the column.

The output of the following query is as follows:

Output 6:

AUTHOR_ID FIRST_NAME LAST_NAME YEARLY_SALARY
1 John Doe 288000
5 Steve Brown 540000
7 Ben Stuart 180000
4 April Berker 946800
2 Stefan Richardson 540000
3 Elena Snow 251844
6 Mark Wright 1200000
Result of Output 6

Figure 6: Result of Output 6

Step 7: In this step we will learn to use concatenation operators to concatenate strings or columns.

Listing 7: SQL query to concatenate column names and strings.

SELECT Author_Id, First_Name || '  ' || Last_Name  AS  Name, Salary

FROM Author

When we run the above query, then we will get a column named ‘Name’ which will be formed by joining ‘First_Name’ and space and ‘Last_Name’. We have used the concatenation operator ‘||’ to concatenate the column names and to insert a blank between them.

The output of the following query is as follows:

Output 7:

AUTHOR_ID NAME SALARY
1 John Doe 24000
5 Steve Brown 45000
7 Ben Stuart 15000
4 April Berker 78900
2 Stefan Richardson 45000
3 Elena Snow 20987
6 Mark Wright 100000
Result of Output 7

Figure 7: Result of Output 7

Step 8: In this step we will learn to use comparison operators in a SELECT statement

Listing 8: SQL query to use comparison operators

SELECT Author_Id, First_Name, Last_Name, Salary, Books

FROM Author

WHERE Salary BETWEEN 20000 AND 90000

When we run the above query, then we will get information ,with columns named ‘Author_Id’, ‘First_Name’, ‘Last_Name’, ‘Salary’ and ‘Books’, of only those authors who have salary between 20000 and 90000. We have used two keywords ‘BETWEEN’ and ‘AND’ to separate the rows on the basis of salary.

The output of the following query is as follows:

Output 8:

AUTHOR_ID FIRST_NAME LAST_NAME SALARY BOOKS
1 John Doe 24000 5
5 Steve Brown 45000 5
4 April Berker 78900 9
2 Stefan Richardson 45000 7
3 Elena Snow 20987 5
Result of Output 8

Figure 8: Result of Output 8

Step 9: In this step we will learn to use logical operators in SELECT statement.

Listing 9: SQL query to use Logical (NOT) operator.

SELECT Author_Id, First_Name, Last_Name, Salary, Books

FROM Author

WHERE NOT(Books=9)

When we run the above query, then we will get information ,with columns named ‘Author_Id’, ‘First_Name’, ‘Last_Name’, ‘Salary’ and ‘Books’, of only those authors who have not published 9 books. We have used ‘NOT’ logical operator which returns true when the condition is false.

There are other two logical operators:

  • AND: It returns true when all the conditions are true.
  • OR: It returns true when any of the condition is true.

The output of the following query is as follows:

Output 9:

AUTHOR_ID FIRST_NAME LAST_NAME SALARY BOOKS
1 John Doe 24000 5
5 Steve Brown 45000 5
7 Ben Stuart 15000 4
2 Stefan Richardson 45000 7
3 Elena Snow 20987 5
6 Mark Wright 100000 10

Listing 10: SQL query to use Logical(OR) operator.

SELECT Author_Id, First_Name, Last_Name, Salary, Books

FROM Author

WHERE Salary < 50000 OR Salary >80000

When we run the above query, then we will get information ,with columns named ‘Author_Id’, ‘First_Name’, ‘Last_Name’, ‘Salary’ and ‘Books’, of only those authors whose salary is either less than 50000 or greater than 80000.

Output 10:

AUTHOR_ID FIRST_NAME LAST_NAME SALARY BOOKS
1 John Doe 24000 5
5 Steve Brown 45000 5
7 Ben Stuart 15000 4
2 Stefan Richardson 45000 7
3 Elena Snow 20987 5
6 Mark Wright 100000 10
Result of Output 9

Figure 9: Result of Output 9

Result of Output 10

Figure 10: Result of Output 10

Step 10: In this step we will learn to order the result of SELECT query according to an attribute value.

Listing 11: SQL query to order the SELECT statement result.

SELECT Author_Id, First_Name, Last_Name, Salary

FROM Author

ORDER BY Author_ID  ASC

When we run the above query, then we will get the tuples of the database ordered in ascending order of primary key. We have used the keyword ‘ASC’ to specify ascending order and we can also use ‘DESC’ to specify the descending order.

The output of the following query is as follows:

Output 11:

AUTHOR_ID FIRST_NAME LAST_NAME SALARY
1 John Doe 24000
2 Stefan Richardson 45000
3 Elena Snow 20987
4 April Berker 78900
5 Steve Brown 45000
6 Mark Wright 100000
7 Ben Stuart 15000
Result of Output 11

Figure 11: Result of Output 11

Conclusion:

In this article, we have learnt about the SQL SELECT statements. We have learned how we can use them in various forms and together with arithmetic, logical, comparison and concatenation operators to obtain the required result.

See you next time!

See also



I have done my bachelor in Computer Science and I am well versed with programming languages such as JAVA, C#, html and done courses on Android development.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login