Basic SQL Queries for Beginners: SELECT, INSERT, UPDATE, DELETE

One of the defining characteristics of Structured Query Language (SQL) is that it closely resembles English — SQL queries are typically read from left to right, much like an English sentence. This makes learning the basics of SQL more accessible. In this post, you’ll get a crash course in basic SQL syntax that can be used when developing scripts, add-ons, or applications, or for managing a SQL database.

The CREATE Statement

CREATE DATABASE

In most cases two things will be required before you can start using SQL queries like SELECT, INSERT, UPDATE, DELETE: a database and a table. Both of them are easily created using the CREATE statement. Let’s have a look:

CREATE DATABASE database_name;

The statement above creates a database called ‘database_name’. The following statement creates a table called ‘customers’ in our ‘database_name’ database:

CREATE TABLE;

Unlike the CREATE DATABASE statement above, CREATE TABLE has a few minimum requirements to be used successfully: at the very least one column and its corresponding data type will have to be defined.

While the above is technically possible, a column with a unique value is recommended to simplify accessing stored records. To this end, it is recommended to define a row that will contain the primary key for each row. For example:

CREATE TABLE Customers(
CustomerID int IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),LastName VARCHAR(50)
);

In the table above it is assumed that the customer ID will always be unique, which means it can be used as a unique identifier for our records.

IDENTITY(1,1) defines an auto-incrementing integer (int) where the first 1 sets the starting value (the initial CustomerID), and the second 1 specifies that each subsequent record’s CustomerID will increase by 1.

The INSERT Statement

Now that we’ve created our database and a table where we can store data, let’s do just that – store some data. Here’s the INSERT statement being used to store a first name and a last name:

INSERT INTO Customers (FirstName, LastName)
VALUES ('John', 'Doe');

When we created the table, we defined CustomerID as the Primary Key for the table and set it to auto increment. As such, John Doe now has CustomerID ‘1’. Every time a new record is added, the CustomerID automatically increases by one, ensuring each record has a unique identifier without manually entering it.

If we wanted to add multiple records, the statement would look like this:

INSERT INTO Customers (FirstName, LastName)
VALUES ('Jane', 'Doe'), ('Barney', 'Rubble'),('Fred', 'Flintstone');

The SELECT Statement

The SELECT statement is one of the most commonly used SQL commands and is essential for retrieving data from a database. It allows you to specify which columns you want to retrieve, filter data with conditions, sort the results, and more. The basic structure of a SELECT statement is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• SELECT: Specifies which columns of data you want to retrieve.
• FROM: Indicates the table from which you are selecting data.
• WHERE: (Optional) Adds a filter to specify which records to retrieve based on certain conditions.

Consider the Customers table we’ve been working with, which contains columns for CustomerID, FirstName, and LastName. To retrieve all records from this table, you would write:

SELECT CustomerID, FirstName, LastName
FROM Customers;

This will display a list of all customers along with their CustomerID, FirstName, and LastName.

If you only want to retrieve records for customers with a specific first name, such as “John”, you can use the WHERE clause:

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE FirstName = 'John';

This query will return all customers named “John” and show their corresponding CustomerID and LastName.

You don’t always need to retrieve all columns. For instance, if you only want to see the FirstName and LastName of customers, you can adjust your query as follows:

SELECT FirstName, LastName
FROM Customers;

The UPDATE Statement

The UPDATE statement can be used to make changes to an existing SQL database record. In its most basic form the UPDATE statement requires:

• The name of the table to be updated, and
• The statement specifying what changes should be made to the columns (records)

While this may suffice in exceptional circumstances, we need one more parameter to specify which records should be updated. For example, let’s say we have two customers named ‘John Smith’ in the database, how will the statement know which one to update? The WHERE clause can be used to specify the individual record(s) to update.

That means our basic SQL statement will look like this”

UPDATE Customers
SET FirstName = 'Joan', LastName = 'd''Arc'
WHERE CustomerID = 1;

Note: In the SET statement above you’ll note there are two apostrophes. The first apostrophe prevents SQL from interpreting the second apostrophe as the end of the value. Or, in technical terms, an apostrophe can be used to escape another apostrophe or special character.

But what if we wanted to update multiple records at the same time?

If we wanted to make the same change to multiple records, we can still use the UPDATE statement as shown above:

UPDATE Customers
SET LastName = 'Smith'
WHERE CustomerID IN (1, 2, 3);

Bonus: How to Use The CASE Statement

The previous statement will change LastName of records 1, 2, and 3 to ‘Smith’. But, more often than not, we’ll want to make different changes to different records. Instead of using the UPDATE statement, we can use the CASE statement. The CASE statement allows you to assign different values based on conditions (in this case, CustomerID).

Here’s an example:

UPDATE Customers
SET LastName = CASE

WHEN CustomerID = 1 THEN 'Smith'

WHEN CustomerID = 2 THEN 'Johnson'

WHEN CustomerID = 3 THEN 'Williams'

END
WHERE CustomerID IN (1, 2, 3);

The DELETE Statement

The DELETE statement is used to remove records from the database. Here’s an example:

DELETE FROM Customers
WHERE FirstName = 'John';

This will delete all records where the FirstName column matches ‘John’. Alternatively, if we wanted to delete the first four records:

DELETE FROM Customers
WHERE CustomerID < 5;

If we want to delete multiple records based on different criteria, you could use an OR clause:

DELETE FROM Customers
WHERE (CustomerID = 1 AND FirstName = 'John')
OR (CustomerID = 2 AND FirstName = 'Joan');

This way, you are specifying different conditions for each record, which would achieve the desired result of deleting specific records based on both CustomerID and FirstName.

Conclusion

Understanding basic SQL queries such as SELECT, INSERT, UPDATE, and DELETE is essential for anyone working with relational databases. These fundamental operations allow you to manipulate and manage data efficiently. By mastering these queries, you can easily retrieve data, add new information, modify existing records, and remove outdated entries. As you gain more experience with SQL, you’ll be able to handle more complex queries and database tasks, giving you greater control over your data and applications. Keep experimenting with these queries, and soon you’ll find yourself capable of tackling advanced database management with confidence.

Storm’s fully-managed SQL Server hosting delivers an award-winning platform that’s robust, super secure, and scalable. You’ll never have to worry about server maintenance tasks like security, updates, and patches because it’s expertly managed while you’re moving forward. Learn more »