MavigBot

The biggest draw to learning SQL is the ability to query and manipulate data in complex ways in order to serve the needs of any application. In this article, I’ll be discussing some of the most important statements and clauses in SQL. I’ll begin by explaining how we can fill the example table we constructed in this previous post with data that we can work with.

The INSERT Statement

To best demonstrate the various abilities we have with SQL statements, we’ll first want to insert records into our “cars” table. To do this, we’ll be using the INSERT statement, an example of DML (Data Manipulation Language) which consists of any commands used to alter the data stored within the database tables.

We’ll need to specify the name of the table we want to insert into, followed by the column we would like to fill, and finally, the values we would like to fill them with. Both of these lists should be comma separated.

INSERT INTO cars (make,model,year)
VALUES ('Honda','Civic',2002);

Omitting the “id” column tells postgres that we want that column to be automatically filled for us, since the data type is SERIAL. If we were to omit one of the other TEXT or INTEGER columns, that field would instead remain NULL. For example:

INSERT INTO cars (make,model)
VALUES ('Jeep','Wrangler');

In the following table, we can see that the above two INSERT statements have created two rows – our 2002 Honda Civic, which was given ID 1, and our Jeep Wrangler of ID 2. We can also clearly see the missing value in the “year” column.

It’s also worth noting that the INSERT INTO statement can be used without specifying the desired column names if the intention is to fill all columns. In this case, the values must be given in order:

INSERT INTO cars
VALUES (DEFAULT,'Toyota','Tacoma',2021);

We can also see that the DEFAULT keyword is used to request a generated value for our SERIAL column. Here is the resulting table after all three records have been inserted:

The SELECT Statement

Now that our test database has some actual data within, we can move on to what is arguably the most essential SQL statement of all – the SELECT statement. This statement is used to return desired information from a database – typically from a specific table. SELECT statements also happen to be the primary focus of DQL (Data Query Language).

In our test database, we can return all of the information we have from the “cars” table by running the statement:

SELECT *
FROM cars;

After running this statement, all of the information stored in the “cars” table will appear formatted in the terminal:

 id |  make  |  model   | year 
----+--------+----------+------
  1 | Honda  | Civic    | 2002
  2 | Jeep   | Wrangler |     
  3 | Toyota | Tacoma   | 2021

The FROM keyword in our statement is used to specify the table we want to query, while the asterisk indicates that we want to return data for every column. If we wanted to be more specific about what results are desired, we can adjust the statement by replacing the asterisk with a comma separated list of column names, like so:

SELECT id, model
FROM cars;

 id |  model   
----+----------
  1 | Civic
  2 | Wrangler
  3 | Tacoma

The WHERE Clause

So far, we’re able to get all of the information we have from a particular table. However, if we want to narrow down our results to something more specific, we’ll need to add conditions to our SELECT statements. Using a WHERE clause allows us to only return results that match whatever conditions we specify.

SELECT *
FROM cars
WHERE id = 1;

 id | make  | model | year 
----+-------+-------+------
  1 | Honda | Civic | 2002

We can easily make use of our primary key column “id” to return a specific object. Alternatively, we can use a WHERE clause that filters our results to only include Toyota vehicles with the following statement:

SELECT *
FROM cars
WHERE make = 'Toyota';

 id |  make  | model  | year 
----+--------+--------+------
  3 | Toyota | Tacoma | 2021

WHERE clauses can also be made more complex. Instead of filtering by one condition, we can narrow our results even further by using multiple conditions separated by the AND, OR, and NOT keywords. In a final example, we can make use of these keywords to return all hypothetical Ford vehicles other than Ford Explorers.

SELECT *
FROM cars
WHERE make = 'Ford' AND NOT model = 'Explorer';

WHERE clauses are an extremely important concept to grasp, as they can and will be used in many other SQL statements, not just with SELECT statements.

The UPDATE Statement

The UPDATE statement is used to make changes to preexisting rows within the database. The statement first takes a table name, followed by a SET clause. This clause is where we will specify the column names we want to change, along with the values we want to set them to.

Without including a WHERE clause, any update we make would affect every record in the database, so it’s best to put some kind of condition on the statement unless it’s actually necessary to change a column across the board.

To change the year of car #2 (the Jeep Wrangler), we can use this statement:

UPDATE cars
SET year = 2006
WHERE id = 2

In order to verify that our changes were successful, we can run a SELECT statement afterwards, or we can make use of the RETURNING clause. Adding this clause to the end of our modifying statement will return specific values to allow us to check our work.

UPDATE cars
SET year = 2006
WHERE id = 2
RETURNING id, year;

 id | year 
----+------
  2 | 2006

The DELETE Statement

The DELETE statement is a very powerful, and potentially dangerous, tool. Be very careful not to run a DELETE statement without a WHERE clause unless you want an empty table. It’s a good idea to test your WHERE clause on a SELECT statement prior to removing records, to ensure that only the records you want deleted are returned.

To delete car #2 from our database, we would run the following statement:

DELETE FROM cars
WHERE id = 2;

In this article, we went through all of the fundamental ways we can manipulate the data in our database by discussing INSERT, SELECT, UPDATE, and DELETE statements. Using these statements alongside accompanying WHERE clauses, a large variety of useful tasks can be accomplished with our database. In a future article, I’ll be diving further into relationships we can have between the tables in our database, alongside ways to manage these relationships. As always, if you have any questions, please feel free to leave a comment below.