SQL Commands Introduction

From SwinBrain

• Create and remove entire databases,
• Create, manipulate and remove the structure of tables,
• Add, modify and remove data (typically records),
• Ask for data based on specific criteria.

Most databases support a variant of SQL that conforms (to different levels) to one of the SQL Standards. In this page we will look at some of the basic SQL commands that you can use to work with a database.

This page expands on Database Introduction and Database Relationship Introduction to examine how SQL commands can be used to query and modify data in the database. If you are unfamiliar with databases start by reading the Database Introduction and Database Relationship Introduction then return to this page.

SQL Command Basics

There are four important basic SQL commands used to work with existing structures. These are:

• SELECT: ask for data
• INSERT: add new data
• UPDATE: modify existing data
• DELETE: remove existing data

In addition to these commands DBMSs offer advanced SQL features, which include the ability to create and use stored procedures, triggers, views, transactions, reports and forms. The available SQL features depend on the abilities of the database and its DBMS, however all relational DBMSs will support the four main SQL commands SELECT, INSERT, UPDATE, and DELETE.

Retrieving Data using SELECT

You retrieve data from a relational database using the SELECT command. Getting the data from the database is an important task and SQL gives you a large amount of flexibility here. The basic syntax for this command is as follows.

SELECT syntax:

SELECT (field1 [, field2] ...)
FROM (table1 [, table2] ...)
WHERE (condition1 [, condition2] ...)
ORDER BY (field1 [ASC|DESC] [, field2 [ASC|DESC] ...)

The field1, field2 following the SELECT identify the data that is to be returned. You can use * as a short-hand way to ask for all fields. The tables in the FROM clause indicate where this data is to come from. The WHERE clause indicates the conditions for row selection, these conditions must be true for a row to be included in the output. If the WHERE clause is omitted, all records are returned. The ORDER BY is used to control the order of the rows in the result.

The SELECT command offers a number of other features including the ability to select unique or distinct records and to select the top records. Using SELECT DISTINCT you tell the DBMS that each row in the output must be unique, and duplicates should be removed. SELECT TOP(n) will select only the first n records in the result, giving you the ability to restrict the number of rows returned.

The following command selects the Name of the products from the Product Table that have a Cost less than $1.00. The results are ordered by Cost with the largest cost appearing first.

SELECT Name
FROM ProductTable
WHERE Cost < 1.00
ORDER BY Cost DESC

Given the following data in the database:

You will get the following result:

Some other SELECT examples.

-- Select everything from the product table
SELECT * FROM ProductTable
 
-- Select the Name and Cost
-- Ordered by Name
SELECT Name, Cost
FROM ProductTable
ORDER BY Name ASC
 
-- Select the top 10 products
-- Ordered by Cost descending
-- Gives the 10 most expensive items
SELECT Top(10) Name, Cost
FROM ProductTable
ORDER BY Cost DESC
 
-- Select the unique (distinct) names
-- for all products with a cost between
-- $1.00 and $5.00
SELECT DISTINCT Name
FROM ProductTable
WHERE Cost > 1.00 AND Cost < 5.00

There are many more options related to selecting data using the SELECT command. SQL SELECT command page contains details on some of the other features of this command.

Inserting data using an INSERT command

The INSERT command is used to add data to a table. The INSERT syntax is shown below.

INSERT INTO table_name (field1 [, field2]…)
VALUES (value1 [,value2]…)

This inserts the values (value1, value2, etc) into the table (table_name) at the specified fields (field1, field2, etc). Values must match the field data types and be formatted appropriately, e.g. quoted strings, dates, numbers etc. The field names are optional if the values supplied match the table's field order and data types. The field names and the values need to be separated by commas and surrounded by parenthesis ().

INSERT INTO authors(first_name, last_name)
VALUES ('J.R.','Rowling')

Changing data using an UPDATE command

The UPDATE command is used to change values of records already in the database. The syntax for the UPDATE command follows:

UPDATE table_name 
SET field1 = (value1|expr1) 
 [, field2 = (value2|expr2)]…)
[WHERE (condition1 [, condition2]…)]

The UPDATE command allows you to change fields to new values, or expressions (expr). This updates all of the rows in table_name that match the specified conditions, changing field1 to value1 or expr1, and field2 to value2 or expr2.

The following examples demonstrate different UPDATE commands.

-- Update all books with isbn='11111111' (probably 1),
--  setting the title.
UPDATE book 
SET title = 'The elves strike back'
WHERE isbn='11111111';
 
-- Update all products, adding 10% to the cost
UPDATE product
SET Cost = Cost * 1.10

Deleting data with the DELETE command

Removing data from the database uses the DELETE command. The DELETE syntax is shown below:

DELETE FROM table_name
[WHERE (condition1 [, condition2]…)]

The simple, and very powerful, DELETE command can be used to remove one or more records (rows) from a table. A WHERE condition should be used to specify exactly which records to remove. It is rare to use a DELETE command without a specific WHERE clause.

-- Delete all of the books with isbn = '11111111'
DELETE FROM book 
WHERE isbn=‘11111111’;
 
-- Delete all of the products that cost more than
--  $1000.00
DELETE FROM product
WHERE cost > 1000.00;
 
-- Delete all of the books!
DELETE FROM book;

Links

Links on SwinBrain related to SQL SELECT are:

• Database Introduction introduces basic Database terminology
• Database Relationship Introduction covers linking multiple tables together with relationships
• Database Management Systems Introduction outlines some of the differences between DBMSs and how to select the right one for a project.
• SQL SELECT command page contains details on some of the other features of the SELECT command.