No products in the cart.
When working with databases, one common function is to create a query whereby a database programmer or administrator needs to select some data from from a table within a database. In SQL (Structure Query Language), this is accomplished by using the SQL SELECT statement. And that is the topic of today’s database programming tutorial.
Read: Best Certifications for Database Administrators
In SQL, programmers can retrieve data from a table using the SQL SELECT statement. The syntax for the SELECT statement can be seen in the following example code:
In this above SQL example, we identify that we want to SELECT all of the data that is stored in columnA, which resides in the table named table_example. If we had a table that had columns representing first names stored in columnA, then the above query would return every name in that column.
But what if we wanted to return more than one column in our table? Suppose we have two columns in table_example: columnA stores first names and columnB stores last names. If we wanted to retrieve both of these columns, we could simply modify our previous code example to reflect the following:
When we run the above query, both columnA and columnB get returned from the table_example table.
If you have a table in a database with multiple columns and you wish to retrieve all of the data from all of the columns in a particular table, you can do so by using a wildcard operator, represented by an asterisk *. Here is how you would return every column in a table using the SQL SELECT statement:
The SQL SELECT statement has sever optional parameters and arguments that database developers can use to enhance their queries. These include:
Here is a code example showing how to use SQL WHERE in a SELECT statement:
In the above example SQL code, we are telling the database to search columnA from the table named table_example and return results only for cells that contain the text “Ronnie”. Since we did not specify the order in which results are returned, they will be returned by ascending order by default.
Further note that the query will only return results with the word “Ronnie” in it. For example, if there is a cell in columnA that contains the word “RonnieBonnie” and one that contains “Ronnie”, only the “Ronnie” result will be returned.
Read: Best Online Courses to Learn SQL
If we wanted to include any result that contained the string “Ronnie”, then we would use the SQL LIKE statement, alongside one of its two wildcard characters:
Observe the following code example, which shows how to use the LIKE clause, WHERE clause, FROM, and the SQL SELECT statement to return any results from columnA that contain any text with “Ronnie”, regardless of what text comes before or after the word “Ronnie”:
Note the % wildcard in the front and back of our string “Ronnie”. The % symbol in front of the word means that any number of characters can appear before the word “Ronnie” and the % wildcard after the text means that any characters can appear after the text “Ronnie”. If we removed the first %, then no characters would be allowed prior to the text “Ronnie”, but any text could appear after the text “Ronnie”.
The minus – wildcard works the same way, only it indicates any single character may appear. For instance, in this scenario:
the following results would be valid:
These results, however, would not be valid, because more than one character exists in front of and/or behind the text “Ronnie”:
Finally, to sort our results in ascending or descending order, we simply use the ASC or DESC parameters of the ORDER BY clause:
If we were selecting data from multiple columns – and sorting by multiple columns – then we could use an SQL query similar to the following:
In this example, any result containing “Ronnie” in columnA will be returned, along with any corresponding data from columnB. Then that data will be sorted by the values in columnA in ascending order first, then columnB in descending order.
Read: Project Management Software for Database Developers
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.