The SQL SELECT Query the basic query is used to retrieve the selected data from database. This is very powerful and largely used command. The result of the select query is stored in result table which is also called as result-set
It’s part of the data manipulation language that is responsible for query the data from the database.
SQL SELECT Query statement syntax
It is the most frequently used SQL command and has the following general syntax
SELECT column_name(s) FROM table_name
Or
SELECT * FROM table_name
- [SELECT column_name(s)] – The result set retrieve all entered columns data. It may contain multiple columns. If pass ‘*’ then it results all columns from selected table.
- [FROM table_name] – This is a table name from which result set is retrieved.
Examples:
Let’s see upon executing following query on Employee table resulting following list:
SELECT * FROM Employee
Following is the result after executing the above SQL query:
| Employee_Id | Full_Name | Gender | Date_Of_Birth | Phone |
| 1 | Tanya Smith | Female | 1982-09-30 | 9123456789 |
| 2 | Janet Tampi | Female | 1980-07-10 | 9876543210 |
| 3 | Nilsen Phil | Male | NULL | 9112233445 |
| 4 | Nilsen Jones | Male | 1983-09-17 | 9988776655 |
| 5 | Pettersen Willams | Male | 1980-07-12 | 9944552299 |
Note: In SQL all queries are case insensitive, so we can use command SELECT or select.
In above query we have seen how to get all the rows and columns from the Employee table.
Using the SELECT keyword allows you to select all columns or specific column(s). The ‘*’ is used to select all columns in the result set & if you want to any specific column(s) in the result set then you can specify comma separated column name list after the SELECT keyword like the one shown below:
SELECT Full_name, Date_Of_Birth, Phone FROM Employee
Following is the result after executing the above SQL query:
| Full_Name | Date_Of_Birth | Phone |
| Tanya Smith | 1982-09-30 | 9123456789 |
| Janet Tampi | 1980-07-10 | 9876543210 |
| Nilsen Phil | NULL | 9112233445 |
| Nilsen Jones | 1983-09-17 | 9988776655 |
| Pettersen Willams | 1980-07-12 | 9944552299 |
SQL Concat Function:
SQL query also supports the functions to join the column data & display in one column. The Concat () function is used in the SELECT query to join the multiple column data.
If you are looking for Employee Name with Gender in the format of [Full_Name, (Gender)] then Concat () function will help you to retrieve the required data in the above format using following query:
SELECT Concat(Full_Name, '(', Gender, ')'), Phone FROM Employee
In this query:
- Concat () SQL function is used join the two columns values together.
- The line “Concat(Full_Name, ‘(‘, Gender, ‘)’)” sentence is used to join the Full_Name & Gender column data. Use of opening & closing brackets is in Concat function with list of column names.
Following is the result after executing the above SQL query:
| Phone | |
| Tanya Smith (Female) | 9123456789 |
| Janet Tampi (Female) | 9876543210 |
| Nilsen Phil (Male) | 9112233445 |
| Nilsen Jones (Male) | 9988776655 |
| Pettersen Willams (Male) | 9944552299 |
SQL Alias:
When we use the any function in the SQL query and execute the query then in the result set name of the column is showing weird. Here while using any function in the query we can use Alias name so that in the result set name of the column is appears as the Alias name what you entered.
SELECT Concat(Full_Name, '(', Gender, ')') [AS] Name(M/F), Phone FROM Employee
Following is the result after executing the above SQL query:
| Name (M/F) | Phone |
| Tanya Smith (Female) | 9123456789 |
| Janet Tampi (Female) | 9876543210 |
| Nilsen Phil (Male) | 9112233445 |
| Nilsen Jones (Male) | 9988776655 |
| Pettersen Willams (Male) | 9944552299 |
Such as Concat() function SQL support lots of function, Here you can get list of most popular function used regularly in testing:
SQL Aggregate Functions:
- SQL sum()
- SQL max()
- SQL min()
- SQL avg()
- SQL first()
- SQL last()
- SQL count()
SQL Scalar Functions:
- SQL format()
- SQL lcase()
- SQL ucase()
- SQL len()
- SQL mid()
- SQL now()
- SQL round()
Over to You!!
Please let me us know your thoughts and feedback through comments. All you experienced DB testers out there, please add your experiences to our article.
If you like this article, consider subscribing to get all Testing Updates in your Inbox for FREE enter Email here: