10 - SQL

Since ISO and ANSI are two of the major international standards setting institutions, a standard that is adopted by both of them will be widely used by software manufacturers and thus, end users.

SQL is a complete database language because it is both a data definition language (DDL) and a data manipulation language (DML). It can create a relational database, query it, and manipulate the data in the database.

SQL is not a complete programming language since it cannot be used to write computer programs and procedures. However, SQL can be embedded in standard programming languages such as COBOL and C.

One of the major operational advantages of a relational DBMS is that DDL statements (data definition statements such as creating a table) can be executed at any time. Earlier DBMSs often require halting the entire system to create a new table, views, and indexes.

A base table is an autonomous, named table. It is autonomous because it exists in its own right. Views are derived from one or more base tables and actually do not exist independently of the base table. A view is also called a virtual table.

The primary key is used to uniquely identify any record of a table and is typically the primary index by which a table is searched. A unique key also uniquely identifies a table, but it is only used for additional indexes to the table.

An index is an ordered set of pointers to rows of a base table. It stores data about the position of the data in a certain column of a base table. Indexes are used to accelerate data access and ensure uniqueness.

The three choices for the ON DELETE command are: restrict, cascade, set null. The restrict option does permit deletion of a primary key when there are matching foreign keys in a related table. The cascade option automaticallydeletes all rows in a related table with matching foreign keys. The set null option sets all corresponding rows to null. The following table shows the advantages and disadvantages of all three options.

The command DROP TABLE would erase the entire table. Deleting all rows, however, would leave an empty table.

A view can be created for a variety of reasons:

• to restrict access to certain rows or columns
• to handle derived data (data conversion)
• to avoid writing common SQL queries

The view definition is stored in the systems catalogue and the view is actually created only when it is referenced in a query.

CREATE UNIQUE INDEX firmname ON shr (shrfirm); 

a. No, it would not make sense to create a unique index for PE since two companies might have the same PE.

When two tables are connected using Product, each row of one table is concatenated with each row of the other table. Join combines only those rows with matching columns (typically a primary key - foreign key match).

An equijoin table contains two identical columns. If one of these two columns is dropped, the remaining table is called a natural join.

It would be wise to choose the alternative with the simple subquery since it would take less time to execute. A correlated subquery must be executed more than once and thus takes more time.

SQL can be used as an interactive query language or embedded in an application program.

DELIMITER //
CREATE PROCEDURE increaseCredit (
IN amount DECIMAL(5),
IN countryIn CHARACTER(25))
LANGUAGE SQL
BEGIN
UPDATE Customers
SET creditLimit = creditLimit + amount
WHERE country = countryIn;
END //

SQL processes one table at a time while COBOL processes one record at a time. Thus, COBOL uses a loop to process each record of a table.