Lesson 6, Exercise 1: Using the SQL Query tool to run SQL statements

In this exercise, you’ll use the SQL Query tool to run several SQL statements that retrieve data from the customers collection.

To run the SQL statements

1. Launch Studio 3T and connect to MongoDB Atlas.

2. In the Connection Tree, expand the sales database node and, if necessary, expand the Collections node for that database.

3. Right-click the customers collection node and click Open SQL. This launches the SQL Query tool, which opens as a tab in the main window, as shown in the following figure.

The SQL tab is divided into two sections. The top section provides a command prompt for running SQL statements. When you first open the SQL tab, the command prompt is prepopulated with a SELECT statement that retrieves all data from the current collection. In this case, the statement is specific to the customers collection, as shown in the following code:

select *
from customers;

The bottom section of the SQL tab is similar to the bottom section of a collection tab, except that it also includes the SQL Query tab, which displays the SQL query associated with the current results. The tab’s contents are blank until you run an SQL statement.

4. Click the run button on the toolbar above the command prompt (third button from the left). SQL Query displays the query results in the Result tab in the lower section. The collection should include 1,000 documents.

5. Go to the Query Code tab. By default, Studio 3T displays the mongo shell statement used to retrieve all the documents from the customers collection, as shown in the following code.

use sales;
db.getCollection("customers").find({});

The statement calls the find method on the customers collection. Because no arguments are passed into the method, the statement returns all documents in the collection. 

6. At the command prompt in the SQL editor, replace the existing code with the following SELECT statement:

select first, last, interests
from customers;

The SELECT clause limits the results to the first, last, and interests columns, and the FROM clause specifies the customers collection as the target data source.

7. On the SQL Query toolbar, click the run button. The query should return all 1,000 documents, but only the first, last, and interests fields.

8. Go to the Result tab in the lower window and display the documents in Table View if not already displayed in that view.

9. If the values in the interests field are not displayed, right click one of the field’s values and click Show Embedded Fields. Your results should look similar to the following figure.

10. At the command prompt in the SQL editor, replace the existing code with the following SELECT statement:

select first, last, interests
from customers
where dob < ISODate('1990-01-01T00:00:00.000+0000');

The statement is similar to the previous one except that it now includes a WHERE clause, which limits the results to customers who were born before January 1, 1990.

11. Press F5 to run the SELECT statement. The statement should return only 812 documents, rather than the full 1,000.

12. At the command prompt in the SQL editor, replace the existing code with the following SELECT statement and then press F5 to run the statement:

select first, last, interests
from customers
where dob < ISODate('1990-01-01T00:00:00.000+0000')
order by last, first;

The statement adds an ORDER BY clause, which sorts the documents by the customers’ last names and then their first names. By default, the values are sorted in ascending order.

13. At the command prompt in the SQL editor, replace the existing code with the following SELECT statement and then press F5 to run the statement:

select first, last, interests
from customers
where dob < ISODate('1990-01-01T00:00:00.000+0000')
  and interests like '%database%'
order by last, first;

The statement expands the WHERE clause to limit the results further to those that include the term database in the list of values in the interests array.

The new search condition uses the LIKE operator and the percent (%) wildcard before and after the specified value. 

By using the wildcard, an array value can include the search term anywhere within the value for the document to be returned.

For example, the statement could return documents with any of the following array values: Database, databases, NoSQL databases, or Database SQL. Because of the added search condition, the query now returns only 17 documents.

14. At the command prompt in the SQL editor, replace the existing code with the following SELECT statement and then press F5 to run the statement:

select first, last, interests
from customers
where dob < ISODate('1990-01-01T00:00:00.000+0000')
  and (interests like '%database%'
    or interests like '%gaming%')
order by last, first;

The WHERE clause now includes an additional search condition stating that an array value can include either database or gaming (or both).

Notice that the two search conditions are connected with an OR operating, indicating either of the two conditions can evaluate to true for the document to be returned. The query now returns 33 documents.

15. Go to the Query Code tab. Notice that the find statement includes the query, projection, and sort sections.

The query section uses regular expressions to limit the results to those documents that contain either database or gaming in the interests array, as shown in the following figure.

The regular expressions are the values associated with the two interests array elements.

Forward slashes enclose each expression, followed by the i option, which indicates that the search condition is case-insensitive.

The expression itself begins with the carrot (^) anchor and ends with the dollar sign ($) anchor. These are added by default to help better utilize the indexes. 

The search term itself (database or gaming) begins and ends with the period/asterisk (.*) combination, which matches any alphanumeric character that appears zero or more times.

This makes it possible to find the search term regardless of where it appears in the array value.

16. At the command prompt in the SQL editor, replace the existing code with the following SELECT statement:

select first, last, interests, year(dob)
from customers
where dob < ISODate('1990-01-01T00:00:00.000+0000')
  and (interests like '%database%'
    or interests like '%gaming%')
order by last, first;

The statement is similar to the previous one, except that it adds the dob field to the SELECT clause, specifying the field name as an argument to the YEAR function. The function is used by multiple database management systems to extract the year from a date/time value.

17. Press F5 to run the statement. The statement returns an error stating that the YEAR function is not supported. As this demonstrates, SQL Query does not support all SQL statement elements.

If you encounter this issue, you’ll need to modify your statement, run it to verify whether it works, and then update the mongo shell code manually to get the exact results you want.

18. Leave the SQL tab open for the next exercise.