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
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 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’
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
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:
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.
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)