Aliases and Subqueries
In SQL, an an alias (or alias name) is used to rename a table or a column within a query.
Alias are very useful if you have very long or complex table names or column names. They help in making queries more readable. They also help in a self-join. A self-join is a situation where a table is used to join it itself. We will discuss the self-join concept later.
An alias name could be anything, but usually it is short.
In this section, we will talk about Aliases, In and the use of
subqueries, and how these can be used in a 3-table example. First, look at this
query which prints the last name of those owners who have placed an order and
what the order is, only listing those orders which can be filled (that is, there
is a buyer who owns that ordered item):
SELECT OWN.OWNERLASTNAME "Last Name", ORD.ITEMDESIRED "Item Ordered"
FROM ORDERS ORD, ANTIQUEOWNERS OWN
WHERE ORD.OWNERID =
OWN.OWNERID
AND ORD.ITEMDESIRED IN
(SELECT ITEM
FROM ANTIQUES);This gives:
Last Name Item Ordered
--------- ------------
Smith Table
Smith Desk
Akins Chair
Lawson Mirror
There are several things to note about this query:
- First, the "Last Name" and "Item Ordered" in the Select lines gives the
headers on the report.
- The OWN & ORD are aliases; these are new names for the two tables
listed in the FROM clause that are used as prefixes for all dot notations of
column names in the query (see above). This eliminates ambiguity, especially
in the equijoin WHERE clause where both tables have the column named OwnerID,
and the dot notation tells SQL that we are talking about two different
OwnerID's from the two different tables.
- Note that the Orders table is listed first in the FROM clause; this makes
sure listing is done off of that table, and the AntiqueOwners table is only
used for the detail information (Last Name).
- Most importantly, the AND in the WHERE clause forces the In Subquery to be
invoked ("= ANY" or "= SOME" are two equivalent uses of IN). What this does
is, the subquery is performed, returning all of the Items owned from the
Antiques table, as there is no WHERE clause. Then, for a row from the Orders
table to be listed, the ItemDesired must be in that returned list of Items
owned from the Antiques table, thus listing an item only if the order can be
filled from another owner. You can think of it this way: the subquery returns
a set of Items from which each ItemDesired in the Orders table is
compared; the In condition is true only if the ItemDesired is in that returned
set from the Antiques table.
- Also notice, that in this case, that there happened to be an antique
available for each one desired...obviously, that won't always be the case. In
addition, notice that when the IN, "= ANY", or "= SOME" is used, that these
keywords refer to any possible row matches, not column matches...that is, you
cannot put multiple columns in the subquery Select clause, in an attempt to
match the column in the outer Where clause to one of multiple possible column
values in the subquery; only one column can be listed in the subquery, and the
possible match comes from multiple row values in that one
column, not vice-versa.
Whew! That's enough on the topic of complex
SELECT queries for now. Now on to other SQL statements.
Miscellaneous SQL Statements
Aggregate Functions
I will discuss five important aggregate functions: SUM, AVG, MAX, MIN,
and COUNT. They are called aggregate functions because they summarize the
results of a query, rather than listing all of the rows.
- SUM () gives the total of all the rows, satisfying any conditions, of the
given column, where the given column is numeric.
- AVG () gives the average of the given column.
- MAX () gives the largest figure in the given column.
- MIN () gives the smallest figure in the given column.
- COUNT(*) gives the number of rows satisfying the conditions.
Looking at the tables at the top of the document, let's look at three
examples:
SELECT SUM(LATECHARGE), AVG(LATECHARGE)
FROM
MEMBERTABLE;
This query shows the total of all salaries in the table, and the average
salary of all of the entries in the table.
SELECT MIN(BENEFITS)
FROM MEMBERTABLE
WHERE CITY = 'Manager';
This query gives the smallest figure of the Benefits column, of the employees
who are Managers, which is 12500.
SELECT COUNT(*)
FROM MEMBERTABLE
WHERE CITY = 'Staff';
This query tells you how many employees have Staff status (3).
Views
In SQL, you might (check your DBA) have access to create views for yourself.
What a view does is to allow you to assign the results of a query to a new,
personal table, that you can use in other queries, where this new table is given
the view name in your FROM clause. When you access a view, the query that is
defined in your view creation statement is performed (generally), and the
results of that query look just like another table in the query that you wrote
invoking the view. For example, to create a view:
CREATE VIEW ANTVIEW AS SELECT ITEMDESIRED FROM ORDERS;
Now, write a query using this view as a table, where the table is just a
listing of all Items Desired from the Orders table:
SELECT SELLERID
FROM ANTIQUES, ANTVIEW
WHERE
ITEMDESIRED = ITEM;
This query shows all SellerID's from the Antiques table where the Item in
that table happens to appear in the Antview view, which is just all of the Items
Desired in the Orders table. The listing is generated by going through the
Antique Items one-by-one until there's a match with the Antview view. Views can
be used to restrict database access, as well as, in this case, simplify a
complex query.
Creating New Tables
All tables within a database must be created at some point in time...let's
see how we would create the Orders table:
CREATE TABLE ORDERS
(OWNERID INTEGER NOT NULL,
ITEMDESIRED CHAR(40) NOT NULL);
This statement gives the table name and tells the DBMS about each column in
the table. Please note that this statement uses generic data
types, and that the data types might be different, depending on what DBMS you
are using. As usual, check local listings. Some common generic data types are:
- Char(x) - A column of characters, where x is a number designating the
maximum number of characters allowed (maximum length) in the column.
- Integer - A column of whole numbers, positive or negative.
- Decimal(x, y) - A column of decimal numbers, where x is the maximum length
in digits of the decimal numbers in this column, and y is the maximum number
of digits allowed after the decimal point. The maximum (4,2) number would be
99.99.
- Date - A date column in a DBMS-specific format.
- Logical - A column that can hold only two values: TRUE or FALSE.
One other note, the NOT NULL means that the column must have a value
in each row. If NULL was used, that column may be left empty in a given row.
Altering Tables
Let's add a column to the Antiques table to allow the entry of the price of a
given Item:
ALTER TABLE ANTIQUES ADD (PRICE DECIMAL(8,2) NULL);
The data for this new column can be updated or inserted as shown later.
Adding Data
To insert rows into a table, do the following:
INSERT INTO ANTIQUES VALUES (21, 01, 'Ottoman', 200.00);
This inserts the data into the table, as a new row, column-by-column, in the
pre-defined order. Instead, let's change the order and leave Price blank:
INSERT INTO ANTIQUES (BUYERID, SELLERID, ITEM)
VALUES (01,
21, 'Ottoman');
Deleting Data
Let's delete this new row back out of the database:
DELETE FROM ANTIQUES
WHERE ITEM = 'Ottoman';
But if there is another row that contains 'Ottoman', that row will be deleted
also. Let's delete all rows (one, in this case) that contain the specific data
we added before:
DELETE FROM ANTIQUES
WHERE ITEM = 'Ottoman' AND BUYERID = 01
AND SELLERID = 21;
Updating Data
Let's update a Price into a row that doesn't have a price listed yet:
UPDATE ANTIQUES SET PRICE = 500.00 WHERE ITEM = 'Chair';
This sets all Chair's Prices to 500.00. As shown above, more WHERE
conditionals, using AND, must be used to limit the updating to more specific
rows. Also, additional columns may be set by separating equal statements with
commas.