SQL basics I: Data queries

Whether you’re just getting in to programming or you’ve avoided learning SQL, it’s something every developer faces eventually. You may not be responsible for building and maintaining a database, but somewhere down the line you’ll be required to work with one.

I’ve created this overview of basic SQL syntax for the interested developer and anyone who could benefit from a cheat sheet. I’ll focus on basic data manipulation queries and follow up with future articles about altering the database itself and more advanced query concepts if there is enough interest.

What is a SQL database, anyway?
Structured Query Language (SQL) is a standard for communicating with a database that is maintained by ANSI. The latest version is SQL-99, though a new standard is in the works (SQL-200n). Most databases adhere to a subset of the ANSI-92 standard. There’s been some discussion about the validity of the more recent standards. Proprietary database manufacturers deviate from it as they develop new concepts in data-storage manipulation.Almost every distinct database contains some unique set of syntax, although it is generally very similar to the ANSI standard. In most cases, these are expansions upon the original standard, although there are some instances where certain syntax will have wildly different results from database to database. It is always a good idea to review the documentation provided by your database manufacturer if your results are not as expected.

In case this is your first exposure to SQL, there are some basic concepts you should understand. I’ve tried to be brief here, so feel free to post your questions in the discussion area below or skip ahead to the next section if you’re comfortable with database terms.

In general terms, “SQL database” is the common name for Relational Database Management System (RDMS). For some systems, “database” also refers to a group of tables, data, and configuration information that are inherently separate from other, similar constructs. In this case, each SQL database installation can consist of several databases. In other systems, these are referred to as table spaces.

A table is a database construct that consists of named columns containing rows of data. Usually tables are constructed to contain related information, and several tables can be created within the same database or table space.

A column within a table holds data of one type and should be named something that represents the data. For example, a column called “LastName” should contain last name entries in every row. This premise is what allows a relational database query to return consistent results.

A field is the data (or place to hold data) from one row, in one column. Alternatively, a data set can be data from multiple rows and multiple columns, and it generally describes all of the data in your database or table. A result set is the data that is returned from a database query; it can describe anything from a single field to all the data contained in your database.

A database query describes instructions sent to the database in SQL that request some kind of function to be performed on your data set or database.

Now we’ll look at the basic types of database queries that are focused on manipulating data within a database. For our purposes, all examples are in standard SQL, and these basic functions should translate to almost any environment.

Data query types
There are four basic types of data queries in SQL:

SELECT: This statement asks the database to return a specified result set of data; you use this to retrieve information that is stored in your database.

INSERT: You use this type of statement to add a new row of data into a table.

UPDATE: This changes existing data in your database.

DELETE: This removes rows of data from your database.

Each of these statements has various qualifiers and functions that you can use to define the data set you would like to affect and that control the way the result set is returned. The SELECT statement has the most options. There are a variety of alternate query types used in conjunction with SELECT, such as JOIN and UNION. For now, we’ll just focus on basic usage.

Use the SELECT statement to retrieve stored information
To get information stored in the database, you use SELECT. The basic function is limited to a single table, although there are constructs for other scopes. To return all the rows of data for particular columns, you use this statement:

SELECT column1, column2 FROM table_name;

Alternatively, you can get all the columns from a table by using the wildcard, “*”:

SELECT * FROM table_name;

While this is useful if you’re planning on parsing the result set in your code, you can further limit it with the handy WHERE clause, which allows you to define a certain condition for selecting data. The following query will return all columns from all rows where “column1” contains the value "3."

SELECT * FROM table_name WHERE column1 = 3;

Besides the “=” (equal to) condition, you may use any of the following: SELECT column1, column2 FROM table_name;

Table A

= Equal <> Not Equal > Greater Than < Less Than >= Greater Than Or Equal To <= Less Than Or Equal To

SQL Conditional Statements

Additionally, you can use the statements BETWEEN and LIKE for comparison with the WHERE clause, and any combination of comparisons with the operators AND and OR. Note that the OR statement is inclusive. Here’s an example combining all of these concepts:

SELECT * FROM table_name WHERE ((Age < 18) AND (LastName BETWEEN ‘Anderson’ AND ‘Miller’)) OR Company LIKE ‘%School%’;

In English, this statement reads “Select all columns from table_name where either the Age column has a value less than 18 and the LastName column has a value alphabetically between and including ‘Anderson’ and ‘Miller’ or the Company column value contains the word ‘School’.”

Use the INSERT statement for new rows of data
Use the INSERT statement to create a new row of data. While you don’t have to populate every field in the row, if you want to put a value into a row where a field is empty, you must use UPDATE.

INSERT syntax is as follows:
INSERT INTO table_name (column1, column2, column3)
VALUES (‘data1’, ‘data2’, ‘data3’);

If you’re going to insert all the values in the same order that the columns of the table exist, you don’t have to specify column names, although for readability this is generally preferred. In addition, if you list the columns, it is not necessary to include them in the order they appear in the database, as long as the values you list correlate. You do not have to list all of the columns if you aren’t entering information for them.

Once you have data in your database, changing it is very similar.

The UPDATE statement and the WHERE clause
UPDATE is used to change an existing value or empty field in a row, so it must both match an existing data set and provide acceptable values. Unless you truly want to change the values in all rows, you must use the WHERE clause.

UPDATE table_name SET column1 = ‘data1’, column2 = ‘data2’
      WHERE column3 = ‘data3’;

You are free to match WHERE to any column, including one that you are changing. This is helpful when you’re looking to change one specific value to another:

UPDATE table_name SET FirstName = ‘Shelley’
WHERE FirstName = ‘Shelly’ AND LastName = ‘Doll’;

Be careful! The DELETE statement zaps whole rows
The DELETE statement will erase an entire row from a table in your database. If you want to delete just a single field, you should use the UPDATE statement to change it to some value that represents NULL to your application. Be careful to limit the scope of your DELETE statement with a WHERE clause, or you run the risk of emptying your entire table.

DELETE FROM table_name WHERE column1 = ‘data1’;

Once a row has been removed from your database, it is gone, so generally speaking it may be desirable to include a column called “IsActive,” or something similar, that you can change to a zero to represent disabled data. You should only use DELETE when you are absolutely sure you will no longer need the affected information.

Now you know the basics of SQL database commands
SQL is the language of databases, and we’ve reviewed the most basic command statements used in data queries. There are a number of basic concepts that weren’t covered, such as SUM and COUNT, but the few commands listed above should get you up and running.