Introduction to Databases and Microsoft Access 4/8

 The INSERT statement
The DELETE statement
The UPDATE statement

Sorting Data
Sub-queries

* The INSERT statement

The INSERT statement allows you to add rows to the database in two ways: with the VALUES keyword or a SELECT statement.

a)    The VALUES keyword specifies data values for some or all of the columns in a new row. A generalized version of the syntax for the INSERT using the VALUES keyword is the following:
INSERT INTO table_name [(column1 [, column2]. . .)]
VALUES (constant1 [, constant2]. . .)

Example 1:
This INSERT statement adds a new row to the publishers table, giving a value for every column in the row:
INSERT INTO publishers
VALUES (‘1622’, ‘Jardin, Inc.’, ‘Camden’, ‘NJ’)
Note that the column order have to appear in the same order that the columns appear in the table.

Example 2:
To insert data in some, but not all columns in a row, you need to specify the column names. The columns that you don’t put data into need to have defaults or be defined as null to prevent failure. For example, adding entries in only two columns requires a command like this -
INSERT INTO publishers (pub_id, pub_name)
VALUES (‘1756’, ‘HealthText’)

b)    You can use a SELECT statement in an INSERT to get values from one or more other tables. A simple version of the syntax for the INSERT command using a SELECT statement is:
INSERT INTO table_name [(insert_column_list)]
SELECT column_list
FROM table_list
WHERE search_conditions

Example 1:
If all the columns of the two tables are compatible, i.e. in the same order, you don’t need to specify column names in either table. For example,
INSERT INTO authors
SELECT au_id, au_lname, au_fname, phone, address, city, state, zip
FROM newauthors
is equivalent to
INSERT INTO authors
SELECT *
FROM newauthors

* The DELETE statement

Like INSERT, DELETE works for single row operations as well as multiple row operations. The DELETE syntax looks like this:
DELETE FROM table_name
WHERE search_conditions

The WHERE clause specifies which rows to remove.

Example 1:
Suppose you want to delete the entry for which the publisher id is 1622:
DELETE FROM publishers
WHERE pub_id = ‘1622’

* The UPDATE statement

The UPDATE statement basically changes existing rows, one table at a time. A simplified version of its syntax is as follows:
UPDATE table_name
SET column_name = expression
[WHERE search_conditions]
The SET clause specifies the column(s) and the changed value(s), while the WHERE clause determines which row(s) will be changed.

Example 1:
The following statement selects all rows where the state = CA and city = Los Angeles, and changes the fields to NY and Rochester respectively.
UPDATE authors
SET state = ‘NY’, city = ‘Rochester’
WHERE state = ‘CA’ AND city = ‘Los Angeles’

Example 2:
Note that if there is no where clause, you’ll update the specified columns in all the records in the table under consideration.
UPDATE authors
SET state = ‘FL’

* Sorting Data

The ORDER BY clause can make query results more readable. It allows sorting by any column or expression in the select list. Each sort can be in ascending or descending order.

The general syntax for the SELECT statement ORDER BY is similar to this:

SELECT select_list

FROM table_list

[WHERE conditions]

[ORDER BY {expression [ASC|DESC] | position [ASC|DESC] } . . . ]

You can specify a direction – low to high, or high to low – for each individual sort by using the ascending )ASC) or descending (DESC) keyword immediately after the sort item. Ascending is the default.


Example 1:

SELECT price, title_id, pub_id

FROM titles

ORDER BY price DESC

This query would list the price, title ID no., and publisher ID of the books in the database, sorted by price in descending order.

Sorts within sorts: Now that the results are sorted by price, you might also want to make sure that books within each price category by the same publisher are listed together. This can be accomplished by adding pub_id to the ORDER BY list.


Example 2:

SELECT price, title_id, pub_id

FROM titles

ORDER BY price DESC, pub_id

In this case, books will be sorted by price in descending order, and books having the same price will be sorted amongst themselves according to the publisher ID, in ascending order (the default sorting order).

When you use more than one column in the ORDER BY clause, sorts are nested in the order of items listed. For example, were the clause above listed as

ORDER BY pub_id, price

Then the books would first be ordered by publisher ID, and then by price.

You can have as many levels of sorts as you like.

Sorting by Expression: Suppose you wanted to sort the results of the query by income (price * ytd_sales) in a statement of the form:

SELECT pub_id, price * ytd_sales, price, title_id

FROM titles

SQL allows you to use the expression’s position in the SELECT list as the order element, which, in this case, would be the number “2”. (When counting SELECT list elements, start with “1” and move from left to right.)


Example 3:

SELECT pub_id, price * ytd_sales, price, title_id

FROM titles

ORDER BY pub_id, 2 DESC

This example query arranges the results sorted primarily by publisher ID, and then in descending order by income (price * ytd_sales) in a nested sort.

Numbers can be used to represent columns as well as expressions.

Eliminating Duplicate Rows: The DISTINCT and ALL keywords in the SELECT list let you specify what to do with duplicate rows in your results. ALL returns all results and is the default. DISTINCT only returns those that are unique. The following example is self explanatory.


Example 4:

SELECT DISTINCT au_id

FROM titleauthors

* Subqueries:

A subquery is an additional method for handling multi-table manipulations. It is a SELECT statement that nests

There are basically two types of subqueries: correlated, and non-correlated. Both are compared below:


Example 1: Noncorrelated subquery

SELECT pub_name

FROM publishers

WHERE publishers.pub_id IN

      (SELECT pub_id

       FROM titles

       WHERE type = ‘business’)

The inner query is independent, gets evaluated first and passes the result to the outer query. First, the inner query returns the identification numbers of those publishers that have published business books. Second, these values are substituted into the outer query, which finds the names that go with the identification numbers in the publishers table.


Example 2 : Correlated subqueries

SELECT pub_name

FROM publishers

WHERE ‘business’ in

      (SELECT type

       FROM titles

       WHERE titles.pub_id = publishers.pub_id)

In this case, the inner query cannot be evaluated independently: it references the outer query and is executed once for each row in the outer query.

Of course, though the above queries return ‘all’ records matching the given parameters, it is possible to have a subqueried statement that returns a single value. Ideally, in order to use this kind of query, you must be familiar enough with your data and with the nature of the problem to know that the subquery will return exactly one value. When you expect more than one value, use IN or a modified comparison operator.

For example, if you suppose each publisher to be located in only one city, and you want to find the names of authors that live in the city where ABC Books is located, you can use a simple comparison operator “=”.


Example 3:

SELECT au_lname, au_fname

FROM authors

WHERE city =

      ( SELECT city

        FROM publishers

        WHERE pub_name = ‘ABC Books’)

Exercises:

Construct queries for bookstore project (to be added later)