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:

cockroach start --insecure --listen-addr=localhost
  • You’ll need to open another terminal window and use the following command to start the CockroachDB SQL client:
cockroach sql --insecure --host=localhost:26257
  • 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:

id | name | studentid | course | yearlvl
+----+------------------+-----------+--------+---------+
1 | Risa Dira | 2940410 | bscs | 2nd
2 | Rommel Galisanao | 2940409 | bsit | 2nd

The tblstudentsinfo table

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

id | name | studentid | course | yearlvl
+----+------------------+-----------+--------+---------+
1 | Raizel Mendez | 2940408 | bshrm | 2nd
2 | Rommel Galisanao | 2940409 | bsit | 2nd
3 | Risa Dira | 2940410 | bscs | 2nd

The tblsubjects table

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

id | subjectid | subjecttitle | yearlvl
+----+-----------+-----------------------------------+---------+
1 | engl 101 | academic english 1 | 1st
2 | itcs 101 | introduction TO computers & it | 1st
3 | math 101 | calculus 1 | 1st
4 | phys 101 | general physics | 1st
5 | engl 201 | academic english 2 | 2nd
6 | itcs 201 | object-oriented programming 1 | 2nd
7 | math 101 | calculus 1 | 2nd
8 | itms 203 | internet application AND services | 2nd

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:

SELECT name, course, studentid FROM tblstudentsinfo;

The results will look like this:

name | course | studentid
+------------------+--------+-----------+
Raizel Mendez | bshrm | 2940408
Rommel Galisanao | bsit | 2940409
Risa Dira | bscs | 2940410
(3 ROWS)

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:

SELECT * FROM tblstudentsinfo WHERE course IN ('bsit','bshrm');

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:

id | name | studentid | course | yearlvl
+----+------------------+-----------+--------+---------+
1 | Raizel Mendez | 2940408 | bshrm | 2nd
2 | Rommel Galisanao | 2940409 | bsit | 2nd
(2 ROWS)

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:

SELECT name AS student_name, studentid AS student_id FROM tblstudentsinfo;

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”:

student_name | student_id
+------------------+------------+
Raizel Mendez | 2940408
Rommel Galisanao | 2940409
Risa Dira | 2940410
(3 ROWS)

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:

SELECT name AS student_name, course, studentid AS student_id FROM tblstudentsinfo WHERE name LIKE 'R%';

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:

student_name | course | student_id
+------------------+--------+------------+
Raizel Mendez | bshrm | 2940408
Rommel Galisanao | bsit | 2940409
Risa Dira | bscs | 2940410

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:

SELECT name AS student_name, course, studentid AS student_id FROM tblstudentsinfo WHERE name LIKE 'R%' ORDER BY student_name DESC;

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

student_name | course | student_id
+------------------+--------+------------+
Rommel Galisanao | bsit | 2940409
Risa Dira | bscs | 2940410
Raizel Mendez | bshrm | 2940408

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.