Scott's Recipes Logo

A Basic Introduction to SQL

Last Updated On: 2025-09-01 04:31:52 -0400

A Basic Introduction to SQL

SQL or Structured Query Language is a way to talk to a database and serves the following functions. The upper case word below after the description is the SQL keyword that you primarily use for that function.

Earlier today I taught a friend the basics of SQL in about an hour and, reflecting on it, I realized that an hour long core dump from an expert isn’t all that useful without something to refer back to hence my writing up this blog post. You may find that this post is a bit random as it reflects a one on one teaching session where a bunch of questions came up but I hope it is useful to someone.

Database Basics

Every SQL database comes down to a series of core concepts:

Each of these is discussed below.

What is a Database?

A database is a collection of information. When you use SQL, that database is generally referred to as a relational database which indicates that a bit of information in one part of the database can be “related” to another bit of information in a different part of the database.

The metaphor for a database is a folder of spreadsheets.

What is a Table?

Just as a database is a collection of information, a table is also a collection of information but a table is a collection of like information where the information has similar characteristics.

The metaphor for a table is a spreadsheet of a single sheet.

What is a Row?

A row is a single entry within a table. Each row is generally, although not always, identified by a unique identifier often referred to as an id or primary key column.

The metaphor for a row is a single row in a spreadsheet of a single spreadsheet.

What is a Column?

Every row is broken into multiple columns each of which contain different elements of the table.

The metaphor for a column is a cell in a spreadsheet.

SQL Keywords

While there are a number of different SQL keywords, the bulk of the SQL that you write uses only a small handful of keywords or key phrases:

Each of these is discussed below.

SELECT

The SELECT statement is used to query one or more SQL tables. A SELECT statement typically has four parts:

SELECT a, b FROM c WHERE d=e LIMIT f

The Columns to Retrieve

Let’s say that you have a simple table named users which two columns: id and last_name. If you want to get back all columns then you could use a query like this:

SELECT * FROM users

If you want only the last names from the table, this statement would select all names from this table:

SELECT last_name FROM users

This will return back to you a set of all last_name values from the table including all duplicates.

Let’s say that you want only unique last_name values then you should use:

SELECT DISTINCT last_name FROM users;

This illustrates using a built in SQL function along with a SELECT statement. Another example using DISTINCT is:

SELECT DISTINCT(id, last_name) FROM users

This would return a set of the distinct tuples of the id and the last_name from the users table.

The Table or Tables You Want to Query

All SELECT statements get data from a table or tables. The table names are what come after the FROM clause of a SELECT statement. Since we illustrated a simple one table SELECT statement above

SELECT medications.name, medication_doses.user_id FROM medications, medication_doses WHERE

CREATE TABLE

The CREATE TABLE statement, well, creates a table where data can be stored and selected. Here’s an example:

CREATE TABLE users (
  Id integer,
  Last_name string
)

TODOJSJ - need to fix this example.

INSERT

The INSERT statement puts data into tables. Here’s an example of inserting data into a table with four columns:

The id column isn’t specified in the INSERT statement above as it is automatically provided by the database system.

UPDATE

The UPDATE statement changes rows in database tables based on the WHERE clause 

### DELETE

The DELETE statement is simple – it deletes rows from a table. Just like a SELECT takes a WHERE clause which limits what it does, so too does a DELETE statement Here are some of the forms:

Deleting Everything

If you wanted to delete every row in the users table, you would use:

DELETE FROM users

Deleting Only Rows Created Today

DELETE FROM users WHERE DATE(created_at) = '2022-09-22'

Please note that this assumes that your users table has a column named created_at which has a full timestamp in it that is compatible with the SQL DATE statement (the DATE statement takes a 24 hour time stamp 2022-09-03 09:28:15.069614 and converts it to just a date like ‘2022-09-03’ so you don’t have to write a range statement like:

DELETE FROM users WHERE created_at >= '2022-09-03 00:00:00.000000' AND created_at <= '2022-09-03 23:59:59.999999'

NOTE: Technically the statement above might be slightly wrong since it has been an age since I’ve written a range statement like the above. Trust but verify …

Notes on Delete

DELETE operates very much like SELECT with the exception that while you type SELECT x,y,z or SELECT *, you always just type DELETE since DELETE operates on the entire table instead of a selection of columns from the table.

ALTER TABLE

Note: This is still TBD.

Putting it All Together with an Example: Database, Tables, Rows and Columns

For an example, let’s consider an online store that that has three things:

The overall database is the collection of these three tables: products, customers and orders.

Each of these tables might be structured as follows:

The products Table

The products table could be expressed using sql as follows:

create table products (
  id,
  name
  picture_url
  price
)

The customers Table

Note: This is still TBD.

The orders Table

Note: This is still TBD.

Glossary

Here are the specialized terms we used:

SQL Query Examples

Note: This is still TBD.

Access Versus SQL

Note: This is still TBD.

Naming Conventions

One of the best ways to make SQL and databases understandable is to focus

The Joe Celko Way of Rightness and Good in the World

Joe Celko was an Infoworld columnist back in the 90s who popularized a way of doing SQL that I follow to this day. Here’s an example

SELECT id, name FROM users WHERE DATE(created_at) >= '2022-01-01';

The characteristics of Celko style SQL are:

Using Version Control with SQL

Note: This is still TBD.

Tools for the Aspiring SQL Developer

A Coding Editor: Sublime Text

A Database: Postgres or MySQL / MariaDB

A Database GUI: Postico

Using SQL Against CSV Files