ADVANCED DATA MANAGEMENT COMMANDS

ADVANCED DATA MANAGEMENT COMMANDS

SORTING
REMOVING DUPLICATES
NUMERIC FUNCTIONS
     COUNT
     MAX
     MIN
     AVG
     SUM
     NESTING QUERIES
GROUPING DATA
HAVING CLAUSE
VIEWS
INDEXES
NATURAL JOIN
INNER JOIN
PARAMETER QUERY

MORE COMPLEX QUERIES AND SQL FUNCTIONS

Ordering a Listing (136)
Listings can be ordered in ascending or descending order.

The sorting process is a logical process only; the actual table contents are not affected.

To produce a list in ascending order, use the clause:

ORDER BY <attributes>

To produce a list in descending order, use the clause:

ORDER BY <attribute> DESC

To list PRODUCT by P_PRICE in ascending order:

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE;

To produce a decending listing ordered by P_PRICE:

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE DESC;

A more complex example:

SELECT P_DESCRIPT, V_CODE, P_INDATE, P_PRICE
     FROM PRODUCT
          WHERE P_INDATE < #08/20/1999#
               AND P_PRICE <= 50.00
                    ORDER BY V_CODE, P_PRICE, DESC;

Note that both V_CODE and P_PRICE will be in descending order, sorted by V_CODE first

According to the standards, NULLs are grouped either before or after known values; the position is implementation defined.

The ORDER BY clause must always be listed last in the command sequence.

Eliminating Duplicates (139)
The DISTINCT clause will yield a list of only those values that are different from one another, i.e., duplicates are eliminated.

To create a list of vendor codes with no duplication:

SELECT DISTINCT V_CODE
FROM PRODUCT;

Numeric Functions

All of the numeric functions can be used with or without specified conditions.

numeric functions can be used in conjunction with more complex logical restrictions.

numeric functions yield only one value based on all the values in the table

COUNT: tallies the number of specific values of an attribute (140)

To count the number of vendors in the PRODUCT table:

SELECT COUNT ( DISTINCT V_CODE )
     FROM PRODUCT;

To find the number of vendors referenced in the PRODUCT table who have supplied products with prices are less than, or equal to, $10.00:

SELECT COUNT ( DISTINCT V_CODE )
     FROM PRODUCT
          WHERE P_PRICE <= 10.00;

MAX: finds the highest (maximum) value for a given attribute (141)

To find the highest P_PRICE in the PRODUCT table:

SELECT MAX ( P_PRICE )
     FROM PRODUCT;

MIN: finds the lowest (minimum) value for a given attribute (141)

To find the lowest P_PRICE in the PRODUCT table:

SELECT MIN ( P_PRICE )
     FROM PRODUCT;

AVG: determines the average value of a specific attribute (142)

To determine the average P_PRICE value:

SELECT AVG ( P_PRICE )
     FROM PRODUCT;

SUM: computes the total for any specified attribute (142)

To find the total value of all items carried in inventory:

SELECT SUM ( P_ONHAND * P_PRICE )
     FROM PRODUCT;

* SQL commands are often used in conjunction with the arithmetic operators +-*/

Nested Queries (142)

The inner loop is executed first

The outer loop is executed last. The outer loop is always the first SELECT command encountered in the command sequence.

To determine which product has the highest price:

SELECT P_CODE, P_DESCRIPT, P_PRICE <---outer
FROM PRODUCT
WHERE P_PRICE = ( SELECT MAX ( P_PRICE ) FROM PRODUCT );

This command sequence provides the outer loop with a value to compare each PRICE value to.

------

To list in descending order all products whose price exceeds the average price:

SELECT *
     FROM PRODUCT
          WHERE P_PRICE > ( SELECT AVG ( P_PRICE ) FROM PRODUCT)
               ORDER BY P_PRICE DESC;

Grouping Data (143)

The GROUP BY clause can be used to create frequency distributions. The GROUP BY clause can be used with any of the numeric functions. Only attributes specified in the SELECT command can be included in the GROUP BY clause.

To find the minimum price for each sale code:

SELECT P_SALECODE, MIN ( P_PRICE )
     FROM PRODUCT
          GROUP BY P_SALECODE;

To find the average price within each sales code:

SELECT P_SALECODE, AVG ( P_PRICE )
     FROM PRODUCT
          GROUP BY P_SALECODE;

Note that whenever the SELECT statement includes one of the numeric functions as well as additional attributes, those attributes MUST be listed in the GROUP BY clause.

Incorrect usage of the GROUP BY (SALE_CODE not listed in the SELECT command.):

SELECT *
FROM PRODUCT
GROUP BY P_SALECODE;

HAVING Clause (144)

The HAVING clause is used to specify conditions that must be met by the records before they will be listed by the GROUP BY clause, i.e., it specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause. After GROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.

HAVING is optional. HAVING is similar to WHERE, which determines which records are selected. After records are grouped with GROUP BY, HAVING determines which records are displayed:

SELECT CategoryID, Sum (UnitsInStock)
     FROM Products
          GROUP BY CategoryID
               HAVING Sum ( UnitsInStock ) > 100;

Virtual Tables: Creating a View (145)

The VIEW command can be used to create a logical (virtual) table.

The logical table exists only in memory, but may be treated as if it is a real table.

It is useful to provide the users with a VIEW rather than the real table, because if they have access only to a view, they cannot corrupt or damage the real table.

The VIEW command is especially useful because it may be used to restrict user access to selected portions of the data contained in a table.

VIEWs are dynamically updated, so if the data in the original table is altered, those alterations are reflected in the VIEWs.

CREATE VIEW <view name> AS
     SELECT <column names of columns to be included in the view>
          FROM <contributing table name>
               WHERE <condition of inclusion>;

To create a logical table called PRODUCT_3 that contains only the designated attributes P_DESCRIPT, P_ONHAND, and P_PRICE, but only for those prices greater than $50.00:

CREATE VIEW PRODUCT_3 AS
     SELECT P_DESCRIPT, P_ONHAND, P_PRICE
          FROM PRODUCT
               WHERE P_PRICE > 50.00;

SQL Indexes (146)
SQL indexes can be created on the basis of any selected attribute.

CREATE INDEX <index name>
ON <table to be indexed> (<column used as index>);

To create an index named P_CODEX based on the attribute P_CODE of the PRODUCT table:

CREATE INDEX P_CODEX
ON PRODUCT (P_CODE);

Joining Database Tables (146)

The ability to combine (JOIN) tables on common attributes is one of the most important distinctions between relational databases and others.

A JOIN is performed when data are retrieved from more than one table at a time.

In order to perform a successful JOIN, the source of the attributes listed in the SELECT must be defined.

The syntax used in the command sequence puts the data source (table) before the attribute, separating the table name from the attribute name with a period.

To list information from the PRODUCT and VENDOR databases, the V_CODE is used to establish the link:

SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE,
         VENDOR.V_NAME, VENDOR.V_CONTACT,
         VENDOR.V_AREACODE, VENDOR.V_PHONE
     FROM PRODUCT, VENDOR
          WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

A more predictable list can be generated by using the ORDER BY condition:

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
     V_AREACODE, V_PHONE
     FROM PRODUCT, VENDOR
          WHERE PRODUCT.V_CODE = VENDOR.V_CODE
               ORDER BY P_PRICE;

the preceding command joins a row in the PRODUCT table with a matching row in the VENDOR table's V_CODE column.

each V_CODE in VENDOR can be matched with many V_CODE rows in PRODUCT

Most current RDBMS packages do not require table names to be used as prefixes, unless the same attribute name occurs in multiple tables.

if the WHERE clause is omitted, the result is a PRODUCT rather than a JOIN

all of the SQL commands can be used on joined tables

example:

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
     FROM PRODUCT, VENDOR
          WHERE PRODUCT.V_CODE = VENDOR.V_CODE
                AND P_INDATE > #08/15/1999#;

an alias (alternate name) may be used to identify the source(s) from which the data are taken.

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT,
     V_AREACODE, V_PHONE
     FROM PRODUCT P, VENDOR V
          WHERE P.V_CODE = V.V_CODE
               ORDER BY P_PRICE;

an alias is especially useful when a table must be joined to itself for grouping purposes.

INNER JOIN

The preceding type of Join is known as a Natural Join or an Inner Join. The SQL-92 standard introduced the SQL statement Inner Join, which is commonly seen in Access. The syntax isn't fully implemented in most SQL products. Instead of using the WHERE clause to join tables, the INNER JOIN-ON is used. While the result is the same, the syntax is considerably different.

Syntax using WHERE:

SELECT columns
FROM table1, table2
WHERE table1.foreignKey = table2.primaryKey;

Syntax using INNER JOIN

SELECT columns
FROM table1 INNER JOIN table2
ON table1.foreignKey = table2.primaryKey;

In order to convert a JOIN that uses the WHERE clause to an INNER JOIN, simply replace the comma between the table names with INNER JOIN and the WHERE with ON. Joins between more than two tables can become fairly complicated. Remember that every INNER JOIN must be paired with an ON.

Examples:

Get the name of all suppliers that have shipped to London.

SELECT DISTINCT suppliers.suppliername
     FROM Projects INNER JOIN
          (Suppliers INNER JOIN Shipments
                ON Suppliers.SupplierNum = Shipments.SupplierNum)
                     On Projects.projectnum = Shipments.ProjectNum
                           WHERE projects.city = "London";

The statement below will return all those parts supplied by a supplier in the
same city to a project in the same city:

SELECT DISTINCT Shipments.partNum, Suppliers.supplierNum, Projects.projectNum, Projects.city, Suppliers.city, Parts.city
     FROM Parts INNER JOIN
            (Suppliers INNER JOIN
                (Shipments INNER JOIN Projects
                 ON Shipments.projectNum = Projects.projectNum)
                      ON Suppliers.SupplierNum = Shipments.SupplierNum)
                            ON Parts.partNum = Shipments.partNum
                                  WHERE (Suppliers.city = Projects.city) and (Suppliers.city = Parts.city);

Parameter Queries

Portions of the text were derived from Database Systems: Design, Implementation, and Management, Fourth Edition by Peter Rob and Carlos Coronel.