The Dolt for Beginners series focuses on topics people getting started with Dolt will likely be interested in. Most of our other blog articles go pretty deep so we want a space to talk about topics that experts may find boring.

Dolt is a Structured Query Language (SQL) database. SQL databases have been around since the early, 1970s, over 50 years now! SQL is still the most popular language used to interact with data. In SQL databases, data is structured in tables retrieved with SELECT queries and modified with INSERT, UPDATE, and DELETE queries. There's a lot to cover for a beginner to SQL when using Dolt. But, SQL is an essential tool in almost any engineer's toolkit.

If you haven't read the schema beginner's article, I would start there. This article assumes you already have a schema amd covers basic SQL SELECT queries used to read data from an existing Dolt database.

Download and Install the Workbench

The Dolt Workbench is the easiest way for Beginners to get started with Dolt. It is a graphical user interface that doesn't require a terminal to get started. Install it.

The Dolt Workbench is a standard desktop application. It is available in both the Mac and Windows App Stores or you can download the appropriate installer from GitHub.

Once you have it installed launch it like you would launch any other Desktop application.

After Starting the Dolt Workbench you are prompted to connect to a database server. The Dolt Workbench works with any MySQL- or Postgres-compatible database.

I have a bunch of connections to various databases but you won't have one as a beginner. So click the "Add Connection" button. We're going to start a local SQL server and select the option to clone a database from DoltHub. This will download a database from DoltHub for us to practice our SQL on. We won't have to create a database from scratch which is pretty cool if you ask me.

Click "Start Clone" and you should see the stocks database conveniently provided by user post-no-preference. It takes a bit of time to download, the database is a little over 2GB, but once it completes you will have a database to play with.

Select

So, as you see in the image above you are greeted with a view of the stocks database. As seen in the left navigation bar, the database has four tables: dividend, ohlcv, split, and symbol. ohlcv is a standard stock market abbreviation that stands for "Open, High, Low, Close, and Volume". It's the standard way to report stock prices since the newspaper days.

You'll also notice that the dividend table is displayed by default in the right section of the workbench. The query bar at the top contains the SQL query SELECT * FROM `dividend` . This is your first SQL lesson and you didn't even need to type! select * from <table> is SQL for "give me every row and column in this table." This is the most basic SQL and you'll use it a lot. select * from <table> means show me this table.

In the workbench if we want to view another table, say symbol, we can either hover over a table in the left section and click the "play" button. Or we can type:

select * from symbol;

In the query box and click play. I'll type the query like a chump:

And after I click play, I see the symbol table.

Filtering

SQL databases are for large data, think millions of rows with dozens of columns. One of the reasons to put data in a SQL database is SQL comes with built in filtering mechanisms that can return only the data you need, fast. You can filter columns or rows.

Columns

If you just want a single column you use a query of the form:

select <column> from <table>;

* means all column names. You can instead provide a comma separated list of column names. If I want a list of all the symbols in the symbol table I run:

SELECT act_symbol FROM symbol;

And it produces the following results:

Rows

If you want to filter rows, you add what is called a "where" clause. Queries that filter rows take the general form:

select * from <table> where <column>=<value>;

In our database, if I wanted only the row of the stock with symbol A, I would run the SQL:

SELECT * FROM symbol where act_symbol='A';

And it would return this:

The where clause is a whole language in and of itself that I can't hope to cover in this article. A few helpful tips are:

1. You can string together multiple clauses with and or or.
2. You can negate a clause with not.
3. > or < are valid for numeric types.

Here's a complicated example query for the symbol table:

SELECT * FROM `symbol` where listing_exchange='NYSE' and is_etf=1 and round_lot_size < 1000;

That returns these results:

As you can see, with SQL, you get a powerful filtering capabilities to only get the data you need.

Ordering

In SQL, you can order the results of a query by one or many columns. Ordered queries take the form:

select * from <table> order by <column>;

Let's say I want to see what stock paid the highest ever dividend in this database. I could run this query:

SELECT * FROM dividend order by amount;

This returns the following results:

You'll notice the default order is in ascending order. We can modify the order by clause with a desc, short for descending, to get the results highest to lowest.

SELECT * FROM dividend order by amount desc;

Running this query returns the results we want:

It looks like multiple stocks have given a $1,000/share dividend.

Limit

Along with ordering, you can also limit the number of results returned. Limiting results in SQL requires queries of the form:

select * from <table> limit N;

where N is any integer number.

Limit is especially useful when testing out queries because limiting the number of results returned often makes queries return faster. Moreover, the tool you are using to render the results also has to do less work processing the results.

Let's use the above dividend example but only show the top 5 results. The query to do this is:

SELECT * FROM dividend order by amount desc limit 5;

When we run that in the Dolt Workbench, we get the following results.

Example

Now let's put it all together in a more complicated example. Let's find the highest close of Apple stock in the past 365 days.

The first thing we need to do is find the symbol for Apple. To do this whe use the symbol table. Inspecting the schema using the right hand table of the workbench, it has a column named security_name. SQL supports pattern matching, called "regular expressions," using the like operator in the where clause. The % symbol is a wildcard. So, to find all symbols with the name Apple in the name we can run the following SQL:

SELECT * FROM symbol where security_name like '%Apple%';

Running this in the workbench produces 4 results:

It looks like the symbol we are looking for is AAPL. Now we go to the ohlcv table to find the high price. The high column is conveniently called high. We also want to filter on the past 365 days. To do this we can use some arithmetic on the current date outside of SQL. You can do this in SQL but it is a bit more advanced. So, a query to get the highest price for Apple in the past 365 would look like:

select act_symbol, `date`, high from ohlcv where act_symbol='AAPL' and date > '2024-04-01' order by high desc limit 1;

Note, date requires backticks because it is a special word in SQL so you have to tell SQL you mean the date column using backticks. This query returns the following results:

It looks like the high for Apple in the past year was on December 26, 2024 at $260.10 per share.

Conclusion

You now know how to write basic SQL Select queries and how to use them to query public data shared on DoltHub. Feel free to practice your SQL on the stocks database you have locally. More questions? Check out the rest of our Dolt for Beginners series or come by our Discord and ask us questions. We're here to help.