How To Perform Simple SELECT SQL basics in CockroachDB

Introduction

If you’re using CockroachDB to store and access your data, you’ll need to know how to perform a variety of different queries. Before you can build and execute complex queries, however, it’s important to understand the basics. In this article, we’ll provide instructions for creating queries using the SELECT SQL statement and ordering query results in CockroachDB.

Prerequisites

Before we can start looking at some SQL statements, let’s review a few key prerequisites for the task:

• CockroachDB must be properly installed and configured.

• The CockroachDB service must be running in the background. You can use the following command to start it up:

• You’ll need to open another terminal window and use the following command to start the CockroachDB SQL client:

• You’ll also need to have a basic understanding of SQL syntax to follow along with the examples in this article.

The Sample Database

It’s helpful to be using the same set of data when you test out the examples in this tutorial yourself. We’ll be using a sample database called “studentRecords”, which contains three tables: tblstudentinfo, tblitclub and tblsubjects.

The tblitclub table

Let’s start by creating a table named “tblitclub” that contains the data shown below:

The tblstudentsinfo table

Next we’ll create the “tblstudentsinfo” table, a basic student information table containing the data seen below:

The tblsubjects table

Finally, we’ll create the “tblsubjects” table, a dataset of subjects for the student:

NOTE: If you need any additional information on how to create databases and insert records in CockroachDB, visit this link : How to Perform an Insert In CockroachDB

Using SELECT to select specific columns

When you query CockroachDB for data, you can have your query return only specific columns. The following SQL query illustrates an example of this where only three columns are returned:

The results will look like this:

NOTE: If you prefer to retrieve all columns in your query, use the following statement instead: SELECT * FROM tblstudentsinfo.

Filter values using a list

One way to filter our search statement is by using listed values, separated by commas, for a specified column. This type of filter will work in much the same way as an “OR” statement. Let’s look at an example:

The above statement will return all rows in the tblstudentsinfo table where the course column contains a value of “bsit” or “bshrm”.

The results should look like the following:

Use SELECT and rename columns for output

When you create a SELECT statement, it’s also possible to create aliases for each column name to be displayed in your returned results. This can make your output more readable, as seen in the following example:

The results will look like the output shown below. Notice that the returned results use the renamed column names “student_name” and “student_id” instead of “name” and “studentid”:

Use SELECT to search for string values

In some cases, you may want to search for a string that is a partial value of a column but not an exact match. We can use the LIKE operator to handle these partial-match situations. You can add wildcard functionality to your LIKE clause by using the % sign, which matches 0 or more characters, or the _ sign, which matches a single character. Let’s look at an example of this type of search in action:

In the statement shown above, we select name (which we rename “student_name”), “course” (which we leave as is) and “studentid” (which we rename “student_id”) from the tblstudentsinfo table where the name column contains a value that begins with “R” followed by any number of characters.

The result should look something like this:

Ordering the results of a SELECT statement

If you’d like your results to be in a particular order, you can specify that in your SELECT statement. You can specify which column you’d like to order the results by, and you can also indicate whether you’d like the results sorted in ascending or descending order. Let’s revisit the same query we looked at in the previous section, but this time we’re going to have our results ordered by the value of “student_name” in descending order:

The same three results will be returned, but this time they’ll come back in a different order:

Conclusion

To fully harness the power of CockroachDB, it’s important to be able to write queries that give you the answers you need. In this article, we reviewed some simple examples of creating queries using the SELECT statement and ordering query results in CockroachDB. With these examples and instructions, you’ll have a strong foundation for writing a wide variety of queries.