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