Categories

Netsparker Web Application Security Scanner

SQL SELECT Query

SQL SELECT Statement

The 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 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!!

If you like this article, consider subscribing to get all Testing Updates in your Inbox for FREE enter Email here:

Enter your email address:

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>