We began looking at SQL last week - with our CREATE TABLE, SELECT, INSERT INTO, DELETE FROM, etc. SQL commands, we only touched on the basic SQL commands. This module looks at additional SQL commands that let you search more than one table to get the necessary information. You are comparing the table data in your JOIN statements (several different types) and similar statements.
A JOIN statement is a relational operation that causes two tables with a common domain (column) to be combined into a single table.
An EQUI-JOIN statement is a join in which the joining condition is based on equality between values in the standard columns. Common columns appear redundantly in the result table.
A NATURAL-JOIN is the same as an equijoin, except the duplicate columns are eliminated in the result table.
An OUTER JOIN is a join in which rows that do not have matching values since common columns are nevertheless included in the result table.
A sub-query is a SELECT query embedded in another SELECT query as part of the WHERE or FROM statement. For example, for the WHERE:
SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS, CUSTOMER_CITY, CUSTOMER_STATE, POSTAL_CODE
FROM CUSTOMER
WHERE CUSTOMER.CUSTOMER_ID = (SELECT ORDER.CUSTOMER_ID FROM ORDER WHERE ORDER_ID = 1008);
An example from the FROM:
SELECT PRODUCT_DESCRIPTION, STANDARAD_PRICE, AVGPRICE
FROM
(SELECT AVG(STANDARD_PRICE), AVGPRICE FROM
PRODUCT),
PRODUCT
WHERE STANDARD_PRICE > AVGPRICE;
------------------------------
Some of the data dictionary tables that come with a relational database are:
DBA_TABLES (describes all tables in the database)
DBA_TAB_COMMENTS (comments on all tables in the database)
DBA_TAB_COLUMNS (describes columns of all tables, views, and clusters)
DBA_USERS (information about all users of the database)
DBA_SYS_PRIVS (describes system privileges granted to users and to roles
There are several other system tables - but these are a few. This allows you to see more information about the database itself as a whole and those who use it.
------------------------------------
SQL:200n
The newer versions of SQL have added some new built in functions:
CEILING (computes the least integer greater than or equal to its argument)
FLOOR (computes the greatest integer less than or equal to its argument)
SQRT (computes the square root of its argument - SQRT(36))
Some new data types include:
BIGINT
MULTISET
XML
Some new statements are:
CREATE TABLE LIKE
MERGE
A trigger is used as an SQL statement that can occur before, after, or instead of an SQL statement.
CREATE TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF} {INSERT|DELETE|UPDATE} ON
table-name
[FOR EACH {ROW|STATEMENT}] [WHEN (search condition)]
<triggered SQL statement here>;
Example:
CREATE TRIGGER STANDARD_PRICE_UPDATE
AFTER UPDATE OF STANDARD_PRICE ON PRODUCT
FOR EACH ROW
INSERT INTO PRICE_UPDATES VALUES (PRODUCT_DESCRIPTION, SYSDATE, STANDARD_PRICE);
The newest versions of PL/SQL (procedures language/SQL) include statements that are like normal programming)
You can have functions and procedures as part of your SQL statements.
SQL has become a powerful language that can be used just like a regular programming language in handling your database data.