Appendix B: Commonly Used SQL Statements

Structured query language (SQL) is a language you use to work with relational databases. In this appendix, we'll discuss some commonly used SQL queries and show how to construct them. To test the SQL statements, we'll use the familiar Northwind database—specifically, its Customers and Orders tables.

Note

SQL queries are also referred to as SQL statements.

Understanding SQL References

For the most part, we used Access 2000's Northwind database to illustrate the SQL statements discussed in this appendix, but you can also use the Northwind database that's in SQL Server. (We use the SQL Server version in the examples in the "Understanding Views" section because Access doesn't support the SQL concept of views as virtual tables.) To test SQL statements, you can open the Northwind database in Access and create a new query by selecting Queries and clicking the New button. This opens the New Query dialog box, as shown in Figure B-1.

Figure B-1: Creating a new query in Access 2000

The Show Table dialog box displays all the tables in the database and lets you pick the tables with which you want to work (see Figure B-2). Click the Close button.

Figure B-2: Selecting database tables

Now, you can open the Query Editor by simply right-clicking the query pane and selecting the SQL View menu item. The Query Editor is basically a blank screen that appears when you're creating a new query, but you can also use it to examine the contents of existing queries (see Figure B-3). In this case, you can either type in a SQL statement directly or paste one in and then choose Query Run Query to execute the statement.

Figure B-3: The Query Editor of Access 2000

Now let's take a look at the Northwind database's Customers table. Figure B-4 shows the table schema (the structure) with its column names and their data types (all of type Text in this case). Note that the CustomerID field is the primary key field.

Figure B-4: Customers table schema of the Northwind database

The Orders table columns and their data types look like Figure B-5. OrderID is the primary key in the Orders table. CustomerID and EmployeeID function as foreign keys and provide the "glue" that links data in the Customers table to data stored in Orders and Employees. (Employees is another table in the Northwind database that stores employee records.)

Figure B-5: Orders table schema of the Northwind database

Using the SELECT Statement

The SELECT statement is the workhorse of the SQL language; it enables you to retrieve data from database tables and views. If you want to retrieve data from selected columns, you list the column names in the SELECT statement. You use the WHERE clause to retrieve data from selected rows.

The simplest form of the SELECT statement is SELECT...FROM, which returns records from a table defined after FROM. This is the syntax:

 SELECT column1, column2, .., FROM table 

For example, the following SELECT statement returns all records from the CustomerID, CompanyName, Address, and City columns of the Customers table:

 SELECT CustomerID, CompanyName, Address, City FROM Customers 

The output looks like Figure B-6.

Figure B-6: Output of the SELECT statement

Note

You can switch back to the SQL view be selecting View SQL View.

You use SELECT * to return all columns from a table (the asterisk is a handy wildcard character in most dialects of SQL). For example, the following statement returns all records from the Customers table:

 SELECT * FROM Customers 

The output looks like Figure B-7.

Figure B-7: Output of SELECT * FROM Customers statement

There are some occasions when the database table stores redundant records, but you generally don't want duplicate records returned. You can use the SELECT DISTINCT statement for this purpose. This is the syntax:

 SELECT DISTINCT column1, column2, .., FROM table 

For example, the following statement returns only unique records from the Customers table:

 SELECT DISTINCT CompanyName FROM Customers 

In addition to restricting columns to those you're interested in, you can also restrict rows by using the SELECT...FROM ...WHERE statement. The WHERE clause takes a conditional statement:

 SELECT column1, column2, .., FROM table WHERE condition 

For example, the following query:

 SELECT * FROM Customers WHERE CustomerID = "BOTTM" 

returns records only having a CustomerID that equals BOTTM. The output of the Northwind database's Customers table looks like Figure B-8.

Figure B-8: Output of the SELECT...WHERE statement

Table B-1 summarizes the conditional statements used in SQL.

Table B-1: Conditional Statements Used in SQL

OPERATOR	MEANING
<	Less Than
>	Greater Than
<>	Not Equal To
=	Equal To

You can also use the SUM and AVG functions to return the sum and average of numeric columns, respectively:

 SELECT function(column) FROM table 

For example, the following query returns the sum of the Freight column in the Orders table:

 SELECT SUM(Freight) FROM Orders 

See Figure B-9 for the output of this statement.

Figure B-9: Output of query that uses the SQL SUM function

The following query returns the average of the Freight column:

 SELECT AVG (Freight) FROM Orders 

You can use SELECT COUNT to return the number of rows in a table (based on a column). This is the syntax:

 SELECT COUNT(column) FROM table 

For example, the following statement returns the number of rows in the Customers table:

 SELECT COUNT(CustomerID) FROM Customers; 

The output of this statement looks like Figure B-10.

Figure B-10: Output of COUNT

GROUP BY is another handy clause that returns results grouped by the mentioned column. This is the syntax:

 SELECT column1, SUM(column2) FROM table GROUP BY column1 

For example, the following statement returns the number of rows grouped by OrderDate from the Orders table:

 SELECT OrderDate, SUM(Freight) FROM Orders GROUP BY(OrderDate) 

The output of the query looks like Figure B-11.

Figure B-11: Output of GROUP BY

The HAVING clause limits output based on a criterion. You can use it with or without the GROUP BY clause. This is the syntax:

 SELECT column1, column2 FROM table GROUP BY column1 HAVING condition 

For example, the following statement returns records of the OrderDate column that have a total freight of more than 900:

 SELECT OrderDate FROM Orders GROUP BY(OrderDate) HAVING SUM(Freight) > 900 

Using the UPDATE Statement

The UPDATE statement makes changes to existing records in a database table. The UPDATE statement takes database table names and table columns and their values.

The syntax for UPDATE statement is as follows:

 UPDATE table SET column1 = [new value], column2 =[new value],.,WHERE {condition} 

For example, the following query updates the Freight column value to 500 in the Orders table where OrderId is 10248:

 UPDATE Orders SET Freight = 500 WHERE OrderId = 10248 

Using a comma, you can update as many as columns as you want. If you want to update all the rows of a column or more than one column, you don't use the WHERE clause. For example, the following query updates all the rows of the Freight column with the value of 500:

 UPDATE Orders SET Freight = 500 

Using the DELETE Statement

The DELETE statement removes records from a database table. Using the DELETE statement, which is dangerously simple, you can either delete records based on certain criteria or delete all the records of a database table.

The syntax of the DELETE statement is as follows:

 DELETE FROM table WHERE {condition} 

For example, the following statement deletes all rows of OrderId 10248 (there should be only one because OrderID is the primary key):

 DELETE FROM Orders WHERE OrderID = 10248 

If you want to delete all of a table's rows, retaining an empty table, you can use the following statement:

 DELETE FROM Orders 

(In Access, DELETE statements can impact more than one table's rows depending on whether cascading deletes are enabled.)

Using the CREATE TABLE Statement

A database table is a collection of rows and columns. Each field in a table is represented as a column. Each field of a table must have a defined data type and a unique name. You can use the CREATE TABLE statement to create database tables programmatically.

The syntax for CREATE TABLE is as follows:

 CREATE TABLE table (column1 column1_datatype, column2 column2_datetype, .., column column_datatype) 

The following statement creates a myTable table with the columns myId, myName, myAddress, and myBalance, which can store integer, character 50, character 255, and floating values, respectively. The CONSTRAINT...PRIMARY KEY syntax makes a column a primary key column:

 CREATE TABLE myTable (myId INTEGER CONSTRAINT PKeyMyId PRIMARY KEY, myName CHAR(50), myAddress CHAR(255), myBalance FLOAT) 

Using the DROP TABLE Statement

There might be some occasions when either you need to delete a table permanently or you need to create temporary, "scratch" tables and then delete them. The DROP TABLE statement deletes a database table.

The syntax for DROP TABLE is as follows:

 DROP TABLE table 

For example, the following statement deletes myTable from the database:

 DROP TABLE myTable 

Using the TRUNCATE TABLE Statement

DROP TABLE deletes all records of a table and the table itself from the database. But what if you don't want to delete the table, just its records? One way of doing this is to use the DELETE FROM query. TRUNCATE TABLE is another way to remove the data of a table without getting rid of the table itself. TRUNCATE TABLE removes all records from a table without logging the individual record deletes. The DELETE statement removes records one at a time and makes an entry in the transaction log for each deleted record. TRUNCATE TABLE is faster than DELETE because it removes the data by deallocating the database table data pages, and only deallocations of the pages are recorded in the transaction log.

TRUNCATE TABLE doesn't remove the table structure, columns, constraints, or indexes. If you want to remove a table definition and its data, use DROP TABLE instead.

The syntax of this statement is simple:

 TRUNCATE TABLE table 

For example, the following statement truncates the Customers table:

 TRUNCATE TABLE Customers 

Note

Access databases don't natively support TRUNCATE TABLE.

Using the INSERT Statement

In the previous sections you saw how to retrieve information from tables. But how do these rows of data get into the tables in the first place? This is what this section, covering INSERT INTO, and the next section, covering UPDATE, are about.

There are basically two ways to insert data into a table. One way is to insert them one row at a time, and the other way is to insert the data several rows at a time. First let's look at how you can insert data one row at a time.

The syntax for inserting data into a table one row at a time is as follows:

 INSERT INTO table (column1, column2, .., columnn) VALUES (value1, value2, ..., valunen) 

For example, the following query adds a new record to the Customers table columns with their corresponding values:

 INSERT INTO Customers(CompanyName, ContactName, ContactTitle, Address, City, Phone) VALUES ("New Name","New Contact","New Title", "New Address", "New City", "New Phone") 

You can also insert records in a table by selecting records from another table. You can do that by mixing INSERT INTO and SELECT statements. The only condition is that the data type of the columns must match in both tables.

The syntax for this is as follows:

 INSERT INTO table1 (column1, column2, ...) SELECT column1, column2, ... FROM table2 

The previous syntax selects data from table2 and inserts it in table1. The data types of column1 in table1 must match with the data type of column1 in table2 and so on.

The following query reads CustName and ContName from NewTable and inserts data to the Customers table:

 INSERT INTO Customers (CustomerName, ContactName) SELECT CustName, ContName FROM NewTable 

You can also apply WHERE and other clauses on this query as you have applied them on the previous SELECT statements.

Using Joins and Aliases

You can represent a table and column in a SQL statement using their alias names. Aliases are frequently used as shorthand—especially in join queries. This is the syntax:

 SELECT aliastable.column1 aliascolumn FROM table aliastable 

For example, this code uses A1 as the alias for the Customers table:

 SELECT A1.CustomerID FROM Customers A1; 

Figure B-12 shows the output of this statement. You can use the same syntax for multiple tables by separating them with commas.

Figure B-12: Output of the Alias statement

Joins are useful when you need to select data from multiple tables based on selection criteria from more than one table. For example, you want to select data from the Orders and Customers tables where the CustomerID column in the Customers table exists in the CustomerID column of the Orders table as a foreign key. The following statement returns you the result:

 SELECT DISTINCT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Customers.CompanyName, Customers.Address, Customers.City FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; 

The output of this statement looks like Figure B-13.

Figure B-13: Output of the JOIN statement

There are a handful of different types of joins, the most important of which are inner joins, outer joins, and cross joins.

Inner joins return records when both tables have at least one row that satisfies the join condition.

Outer joins work with LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, and FULL (OUTER) JOIN clauses. OUTER JOIN with RIGHT JOIN return all records from the right table and null values for the unmatched records in the left table. The OUTER JOIN with LEFT JOIN clause is the reverse of the OUTER JOIN with RIGHT JOIN clause. It returns all records from the left table and null values for the unmatched records in the right table. The OUTER JOIN with the FULL JOIN clause returns all records from both tables. If there's no match for a record of the left table in the right table, the right table returns null values and vice versa.

Cross joins return all records from the left table, and each row of the left table is combined with all records of the right table.