Quick Intro to SQL

SQL (Structured Query Language) is a fairly standard language used for all sorts of tasks involving databases. If you're new to working with databases, SQL may seem like a challenging thing to learn. The important part is to grasp the basics and then, as I recommend with all languages, play with it.

If you're well experienced with it then this article may not be for you, but it's hopefully the first of many articles covering SQL topics. If I'm going to write others then I might as well provide an intro too.

A Basic Example

Let's not waste too much time, here's an example:

SELECT
  id,
  lower(first_name),
  upper(substring(first_name, 1, 1)) ||lower(substring(first_name, 2)) || ' ' ||
  upper(substring(last_name, 1, 1)) || lower(substring(last_name, 2)) AS full_name
FROM customers
WHERE status = 'A' -- Active customers only
ORDER BY last_name ASC;

What's Going On

Pretty simple — we're fetching a set of records from a table and being specific about which columns and rows we want. This is a SELECT statement, and is comprised of several clauses. I've highlighted all these terms because they are fundamental things that you will see often and should be familiar with.

It's probably obvious, but this is what each of the clauses are doing for us:

• SELECT - Specifies the output columns, separated by commas, with optional names and possibly complex expressions for values
• FROM - Defines where we get the data that the other clauses are working with
• WHERE - Apply filtering so that we only return the rows we want
• ORDER BY - Specify what order we want the results in

So that's a pretty basic example of querying data. It can get much more complex than this. It's possible to write statements that are hundreds of lines long, with hundreds of calculated output values, involving many tables and ... so ... Much ... MORE.

There will be limits to the size of a single statement that your database will allow, but you usually want to keep your statements small enough to be easily understood. It's not only harder to understand and maintain huge statements, but you're also more likely to run into performance issues! If it's complex for you, imagine being the database engine trying to figure out how to deal with that thing!

Some other things to know:

• Case Insensitivity — SQL is generally case-insensitive
  • The keywords in this example are UPPERCASE, which is a common convention, but not required
  • Keywords and names (field names, schema names, etc) are converted to UPPERCASE when they are not quoted, making them act as case-insensitive
  • If you surround a name with double quotes (") then they will preserve the exact casing given
    • This does allow for usage of most characters in field names, but has obvious tradeoffs
• Statement Termination — Varies somewhat by the context of the statement
  • Semicolons (;) are required when executing multiple statements, but multiple statements aren't supported in all contexts
  • Statements embedded in programs or scripts generally don't allow semicolons as you can only execute one statement at a time
  • The single limitation may be worked around by compound statements
• White space — Indentation and line breaks are ignored but very helpful for readability
• Comments - Single and block comments are supported
  • Single line comments begin with --, not the // you might be used to
  • Block comments with the /* to characters */

Other Basic Statements

The other super common statements that you will write are INSERT, UPDATE, and DELETE statements. Let's have some quick examples of those.

The INSERT Statement

This is the statement to use for adding data to a table.

INSERT INTO paid_actors(name, email, amount)
VALUES
  ('J. Doe', 'j.doe@example.com', 100),
  ('A. Guy', 'a.guy@example.com', 250)

Here, we're inserting two rows into our paid_actors table. The VALUES expression allows for multiple rows, with multiple columns, to be provided by surrounding each row's columns with parenthesis (( ..., ... )), and including a comma between rows. Our example is using simple value expressions, but nothing stops them from being much more complex, or even subselect statements.

I want to keep this page brief, so I won't provide an example here, but you should be aware that another very common way to do inserts is using the INSERT INTO ... SELECT statement, where you use a SELECT statement instead of the VALUES statement.

The UPDATE Statement

Yeah, the UPDATE statement allows you to update records.

UPDATE checking_account
SET balance = (balance * 2.0)  -- double the money
WHERE account_number = 8675309 -- in my account ;)

Like the SELECT, VALUES, WHERE and other statements, the SET works with values and expressions. In this case, we're setting a field on the table being UPDATEd to the result of an expression.

WARNING

The WHERE clause is optional, so you can accidentally forget it and UPDATE all the rows in a table (or remove them on a DELETE statement). Ask me how I know! 😅

The DELETE Statement

Another no surprises statement, the DELETE is how we remove records from tables.

DELETE
FROM users
WHERE last_active_date < date('1999-12-31')

Unlike the SET and UPDATE statements, the DELETE is not interested in columns, it is interested in rows. So there is no specification in it for columns. We go right from the DELETE keyword to the FROM keyword. Just make sure you remember that WHERE clause or you're going to wipe out all of your data!

TIP

💡 To help avoid mistakes with deletes or updates, first write your statement as a select statement! Run it and verify that it is getting the right records. For updates, include the calculations in your select so you can verify that too!

Then, change the select part into the update or delete syntax without changing the other parts. You will be pretty safe from mistakes such as making the wrong calculations, changing the wrong fields, or affecting the wrong records.

The Sublanguages of SQL

So far, you have only heard me talk about SQL statements. Often, you will read guides that mention terms other than just SQL. You'll read about using DQL, DDL, or DML to do different tasks. These are actually just subsets of SQL statements. I don't really see them as distinct languages, but a way of conceptually grouping statements by their functionality.

Acronym	Name	Description	Examples
DQL	Data Query Language	Used to query data from the database.	SELECT
DDL	Data Definition Language	Used to define and modify the structure of database objects.	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Used to insert, update, or delete data within tables.	INSERT, UPDATE, DELETE, MERGE
DCL	Data Control Language	Used to control access and permissions to database objects.	GRANT, REVOKE
TCL	Transaction Control Language	Used to manage transactions within a database.	COMMIT, ROLLBACK, SAVEPOINT

What's Next

If you got anything out of this guide, I really think the next thing to do is go and play around. There's really no better way to learn than just doing, in my opinion. You can run free, open source databases locally quite easily, and take advantage of tooling for working with queries.

A great way to get started on your computer is to use the XAMPP installer from Apache Friends. It's one of the easiest ways to get MariaDB (open source fork of MySql) up and running on your system. It also comes out of the box with an excellent web based GUI for working with it, phpMyAdmin.

There are many other ways to run a database locally, but if you're new to it then I really recommend sticking to the simple stuff. What might be even easier than that is to use online playgrounds. Just like there are playground websites for programming languages, there are also playground sites for SQL languages. I haven't really spent time with these tools for SQL, but just a quick search and it seems like RunSQL has an excellent interface and choice options.

I feel like that's about enough for this article. I'll be back with more stuff relating to SQL, and in particular DB2 on the IBM i because that's largely what I've been doing at work lately. If you're still here then thanks for reading and I hope you found something useful here. 👋🙂