Basic SQL for Web Developers – Part 1

Basic SQL for Web Developers – Part 1

Why SQL Became a Must Learn As a Self-Taught Web Developer

SQL…As a self-taught web developer who learned through the Ruby on Rails framework, I had the luxury of not having to know SQL when I first started. Why?

Rails has an ORM (object relational mapper) called ActiveRecord. You simply type something like User.where(first_name: “Phil”) and the basic SQL statement to talk to the database gets generated behind the scenes for you.

The basics work great when you’re just getting started.

As you progress and work in more complicated code bases, the need for the skill at understanding and writing raw SQL increases. You need it when your boss or another stakeholder needs some basic information that can be easily pulled from the database.

In addition, it’s also incredibly helpful for understanding more complicated Rails queries that are chained together. For instance you may see something like User.where(first_name: “Phil”).joins(:addresses).limit(5). Just knowing the raw SQL behind that and what it generates can help you understand the choices ActiveRecord makes behind the scenes.

In my case, I ended up needing it because some reporting tools at a company I worked at generated raw text files using SQL and it became necessary to update those files.

A terminology primer for databases

• Database – This is the structured set of data that can be accessed.
• Schema – This describes the logical structure of the database.
• Table – A grid representation of a set of data in the database that organized in rows and columns is called a table. For example, you may have a table containing rows and columns of information about swimmers. Each row would represent information about one particular swimmer and a column represents a characteristic common to all swimmers.
• Query – A “command” that is run against the database in order to read, update, insert, or delete data from the database and its associated tables is called a query.

Basic SQL Queries and corresponding ActiveRecord queries

Let’s suppose we have a database called “swimming_database” and a table in that database called “swimmers”.

Select

SELECT * FROM swimmers;

The above statement will select all rows and all columns from the swimmers table.

SELECT last_name, first_name FROM swimmers;

The above statement will select all the rows from the swimmers table with columns last_name and first_name.

In general, you can either cherry-pick what columns you want from the table or you can use the * operator to get all the columns.

Insert

INSERT INTO swimmers (id, last_name, first_name) VALUES (4, ‘Smith’, ‘Phil’);

The above statement will insert the value of ‘Smith’ into the swimmers table as a last_name and the value of ‘Phil’ as a first_name. It inserts the value of 4 for an id.

Update

UPDATE swimmers SET first_name = ‘Phillip’ WHERE id = 4;

What if Phil wants to be referred to as Phillip? Using the update statement you can modify his first name. Notice the use of the WHERE clause to ensure we target the correct row in the database to ensure we update the right data.

Delete

DELETE FROM swimmers;

Null values

In an SQL database a NULL value represents the non-existence of a value. NULL is not zero nor is it an empty string. Two NULL “values” are not equal to each other. Think of it as a “flag” telling you that data does not exist.

The thing to remember about NULLs are that the SQL standard says that a query predicate (the part of an SQL statement that evaluates to true or false, typically a WHERE clause) that evaluates a NULL can never be true AND it can never be false either. Hence, it is unknown.

Let’s look at the following query and what happens when you attempt to treat NULL like a value:

SELECT first_name, last_name

FROM swimmers

WHERE first_name = NULL;

Remember, NULL is not a value, and since the database cannot compare two NULL values, the result will be an empty set of data.

Instead, you should use IS NULL and IS NOT NULL if you are trying to figure out whether a column contains NULL.

Practice Time

I made a rails application called sql_modeler to seed a practice database to practice some SQL queries. Give it a try and watch your web development skills reach new heights with SQL!