In this chapter we will look at some of the advanced features of SQL.

In this chapter we will be looking at some of the advanced features of SQL. As we were first learning about SQL we were dealing with selections from a single table. For many computer applications we need to be able to select data from more than one table at the same time. We have many ways in which we can do this. We can select data from multiple tables that have common columns. Many times this is done through the primary key-foreign key relationship. The foreign key table points back to the primary key table.

The most frequently used relational operation is called a “join”. We can specify a join with the FROM clause and WHERE clause. When we refer to multiple tables to get data our result is still a single “table”. Many new commands have been added to deal with the processing of multiple tables. An explicit JOIN…ON is part of the FROM clause. Other terms include: INNER, OUTER, FULL, LEFT, RIGHT, CROSS and UNION> No matter which join you use there should be one ON or WHERE specification for each pair of tables being joined.

EQUI-JOIN

An “equi-join” is based on the equality between values in common columns. An example is where we want to find customers who have placed orders. Two tables are involved – a customer table and an order table. We would write such a query as:

SELECT CustomerTable.CustomerID, Ordertable.CustomerID, CustomerName, OrderID

FROM CustomerTable, OrderTable

WHERE CustomerTable.CustomerID = OrderTable.CustomerID

ORDER BY Order ID;

 

This type of join can be accomplished in a variety of ways. Microsoft Access format is:

 

SELECT CustomerTable.CustomerID, OrderTable.CustomerID, CustomerName, Order ID

FROM CustomerTable INNER JOIN OrderTable ON CustomerTable.CustomerID = OrderTable.CustomerID

ORDER BY OrderID;

 

Another way:

 

SELECT CustomerTable.CustomerID, OrderTable.CustomerIC, CustomerName, Order ID

FROM CustomerTable INNER JOIN OrderTable USING CustomerID

ORDER BY OrderID;

 

NATURAL JOIN

 

A Natural Join is the same as an equi-join except one of the duplicate columns is eliminated in the result table. The natural join is the most commonly used type of a join operation.

 

SELECT CustomerTable.CustomerID, CustomerName, OrderID

FROM CustomerTable NATURAL JOIN OrderTable ON CustomerTable.CustomerID – OrderTable.CustomerID;

 

OUTER JOIN  

 

In many join operations we find that we don’t have matchingrows. For instance, many customers in the database may not have placed an order recently. Companies might want to know customer who have ordered items and also those customers who haven’t ordered. This is normally done with an OUTER JOIN. Rows that do not have matching values are also included in the result table.

 

SELECT CustomerTable.CustomerID, CustomerName, OrderID

FROM Customertable LEFT OUTER JOIN OrderTable

WHERE CustomerTable.CustomerID = OrderTable.CustomerID;

 

UNION JOIN

 

This type of join hasn’t been implemented in all RDBS products. The results of a UNION JOIN will be a table that includes all data from each table that is joined.

 

SELF-JOIN

 

One other type of join is where we join a table with itself – matching rows with other rows in the same table.

 

SELECT E.EmployeeID, E.EmployeeName, M.EmployeeName AS Manager

FROM Employee_T E, Employee_T M

WHERE E.EmployeeSuperviser – M.EmployeeID;

 

SUBQUERIES

 

Subqueries occur when we put an embedded query as part of a FROM clause.

 

SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerCode

FROM CustomerTable

WHERE CustomerTable.CustomerID =

(SELECT OrderTable.CustomerID

   FROM OrderTable

   WHERE OrderID = 1008);

 

SELECT CustomerName

FROM CustomerTable

WHERE CustomerID IN

(SELECT DISTINCT CustomerID

   FROM OrderTable);

 

USING DERIVED TABLES

 

Our subqueries are not limited to the WHERE clause – but may also be used in the FROM clause

 

SELECT ProductDescription, ProductSandardPrice, AvgPrice

FROM

(SELECT AVG (ProductStandardPrice) AvgPrice

   FROM ProductTable), ProductTable

   WHERE ProductStandardPrice > AvgPrice;

 

CASE Statements – Conditional Expressions

 

CASE statements can replace the use of IF-THEN-ELSE statements

 

SELECT CASE

WHEN ProductLine = 1

THEN ProductDescription

ELSE ‘####’

END AS ProductDescription

FROM ProductTable;

 

GUIDELINES FOR BETTER QUERY DESIGN:

  1. Understand how indexes are used in query processing
  2. Keep optimizer statistics up-to-date
  3. Use compatible data types for fields and literals in queries
  4. Write simple queries
  5. Break complex queries into multiple simple parts
  6. Don’t next one query inside another query
  7. Don’t combine a table with itself
  8. Create temporary tables for groups of queries
  9. Combine updae operations
  10. Retrieve only the data you need
  11. Don’t have the DBMS sort without an index
  12. Know how your DBMS works and what operations are allowed
  13. Consider the total query processing time for ad hoc queries

 

DATA DICTIONARY FACILITIES

 

RBDMs store database definition information in secure system-created tables that we can refer to as a data dictionary. Each RDBMS keeps various internal tables for information about the database. You can see the contents of these tables:

 

DBA_TABLES                      Describes all tables in the database

DBA_TAB_COMMENTS     Comments on all tables in the database

DBA_CLUSTERS                 Describes all clusters in the database

DBA_TAB_COLUMNS       Describes columns of all tables, views and clusters

DBA_COL_PRIVS               Includes all grants on columns in the database

DBA_COL_COMMENTS    Comments on all columns in tables and views

DBA_CONSTRAINTS         Constraint definitions on all tables

DBA_USERS                         Information about all users of the database

DBA_SYS_PRIVS                Describes system privileges granted to users and to roles

DBA_ROLES                                    Describes all roles that exist in the database

 

There are many additional types of internal tables.

 

SQL:200n ENHANCEMENTS

 

Many new commands have been added to the SQL:200n releases. Some types of exhancements include analytical and OLAP functions. These include: CEILING, FLOOR, SQRT, RANK, DENSE_rANK, ROLLUP, CUBE, SAMPLE, OVER or WINDOW.

 

In addition, new data types have been added: BITINT, MULTISET, XML.

 

A new CREATE TABLE LIKE command has also been added.

 

Additional programming commands have been added: CASE, IF, LOOP, LEAVE, FOR, WHILE, REPEAT, ITERATE. This now allows SQL to behave like most traditional programming languages.

 

TRIGGERS

 

Triggers and routines are procedures that are stored in the database and can be used in applications. Triggers and routines consist of block of procedural code. Routines are stored block of code that must be called to operate. Triggers are a special type of stored procedure and may run in response to either DML or DDL commands.

 

EMBEDDED SQL and DYNAMIC SQL

 

SQL commands can be embedded in 3rd generation languages such as Ada, COBOL, C, PHP, .NET and JAVA.