Chapter 7 Intro to SQL

In the SQL environment, a schema is

A group of database objects—such as tables and indexes—that are related to each other. Usually belongs to a single user or application.

The command sequence ends with

A semicolon. (Remember, your RDBMS may require that you omit the semicolon.)

The CREATE VIEW statement is

A data definition command that stores the subquery specification—the SELECT statement used to generate the virtual table—in the data dictionary.

NUMBER

Numeric data. NUMBER(9,2) is used to specify numbers with two decimal places and up to nine digits long, including the decimal places. Some RDBMSs permit the use of a MONEY or a CURRENC Y data type.

In general, ANSI SQL permits the use of

ON DELETE and ON UPDATE clauses to cover CASCADE, SET NULL, or SET DEFAULT.

If your RDBMS does not support the VARCHAR2 and FCHAR format

Use CHAR.

If your RDBMS does not support SINT or SMALLINT

Use INTEGER or INT. If INTEGER is not supported, use NUMBER.

FK

Foreign Key

SQL SYNTAX Syntax notation for SQL commands used in this book:

1) CAPITALS- Required SQL command keywords 2) italics- An end-user-provided parameter (generally required) 3) {a | b | ..}- A mandatory parameter; use one option from the list separated by | 4) []- An optional parameter—anything inside square brackets is optional 5) Tablename- The name of a table 6) Column- The name of an attribute in a table 7) data type- A valid data-type definition 8) constraint- A valid constraint definition 9) condition- A valid conditional expression (evaluates to true or false) 10) columnlist- One or more column names or expressions separated by commas 11) tablelist- One or more table names separated by commas 12) conditionlist- One or more conditional expressions separated by logical operators 13) expression- A simple value (such as 76 or Married) or a formula (such as P_PRICE − 10)

Besides the PRIMARY KEY and FOREIGN KEY constraints, the ANSI SQL standard also defines the following constraints:

1) The NOT NULL constraint ensures that a column does not accept nulls. 2) The UNIQUE constraint ensures that all values in a column are unique. 3) The DEFAULT constraint assigns a value to an attribute when a new row is added to a table. The end user may, of course, enter a value other than the default value. 4) The CHECK constraint is used to validate data when an attribute value is entered. The CHECK constraint does precisely what its name suggests: it checks to see that a specified condition exists. Examples of such constraints include the following: - The minimum order value must be at least 10. - The date must be after April 15, 2010. If the CHECK constraint is met for the specified attribute (that is, the condition is true), the data are accepted for that attribute. If the condition is found to be false, an error message is generated and the data are not accepted.

That foreign key constraint definition ensures that:

1) You cannot delete a vendor from the VENDOR table if at least one product row references that vendor. This is the default behavior for the treatment of foreign keys. 2) On the other hand, if a change is made in an existing VENDOR table's V_CODE, that change must be reflected automatically in any PRODUCT table V_CODE reference (ON UPDATE CASCADE). That restriction makes it impossible for a V_CODE value to exist in the PRODUCT table pointing to a nonexistent VENDOR table V_CODE value. In other words, the ON UPDATE CASCADE specification ensures the preservation of referential integrity. (Oracle does not support ON UPDATE CASCADE.)

A view is

A virtual table based on a SELECT query. The query can contain columns, computed columns, aliases, and aggregate functions from one or more tables.

When it is crucial to have the data available, the NOT NULL specification will NOT

Allow the end user to leave the attribute empty (with no data entry at all). Because this specification is made at the table level and stored in the data dictionary, application programs can use this information to create the data dictionary validation automatically.

By default, all indexes produce results that are listed in

Ascending order, but you can create an index that yields output in descending order.

In that case, the deletion of an INVOICE row will

Automatically delete all of the LINE rows related to the invoice

The UNIQUE specification creates a unique index in the respective attribute. Use it to

Avoid having duplicated values in a column

The tables on which the view is based are called

Base tables.

Common CHARACTER SQL Data Types

CHAR(L) VARCHAR(L) or VARCHAR2(L)

SQL Data Definition Commands

CREATE SCHEMA AUTHORIZATION CREATE TABLE NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT CHECK CREATE INDEX CREATE VIEW ALTER TABLE CREATE TABLE AS DROP TABLE DROP INDEX DROP VIEW

You can create a view by using the CREATE VIEW command:

CREATE VIEW viewname AS SELECT query

DATE formats vary.

Commonly accepted formats are: 1) 'DD-M ON-YYYY' 2) 'DD-M ON-YY', 3) 'MM/DD/YYYY', 4) 'MM/DD/YY'

Oracle accepts the VARCHAR data type and automatically

Converts it to VARCHAR2.

CREATE TABLE AS

Creates a new table based on a query in the user's database schema

Common DATE SQL Data Type

DATE

ANSI SQL standards

Define a command to create a database schema

The ON UPDATE CASCADE clause is part of the ANSI standard, but it may not be supported by your RDBMS.

Delete the ON UPDATE CASCADE clause.

If your RDBMS does not support primary and foreign key designations or the UNIQUE specification

Delete them from the SQL code shown here

The entire table definition is

Enclosed in parentheses. A comma is used to separate each table element (attributes, primary key, and foreign key) definition.

An RDBMS will automatically

Enforce referential integrity for foreign keys. That is, you cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor.

The NOT NULL specifications for the attributes

Ensure that a data entry will be made.

CHAR

Fixed character length data, 1 to 255 characters

CHAR(L).

Fixed-length character data for up to 255 characters. If you store strings that are not as long as the CHAR parameter value, the remaining spaces are left unused. Therefore, if you specify CHAR(25), strings such as Smith and Katzenjammer are each stored as 25 characters. However, a U.S. area code is always three digits long, so CHAR(3) would be appropriate if you wanted to store such codes.

SQL Data Manipulation Commands

INSERT SELECT: WHERE, GROUP BY. HAVING, ORDER BY UPDATE DELETE COMMIT ROLLBACK =, <, >, <=, >=, <> AND/OR/NOT COUNT MIN MAX SUM AVG BETWEEN IS NULL LIKE IN EXISTS DISTINCT

The ON UPDATE CASCADE specification ensures

If you make a change in any VENDOR's V_CODE, that change is automatically applied to all foreign key references throughout the system (cascade) to ensure that referential integrity is maintained. (Although the ON UPDATE CASCADE clause is part of the ANSI standard, some RDBMSs, such as Oracle, do not support ON UPDATE CASCADE. If your RDBMS does not support the clause, delete it from the code shown here.)

INT

Integer values only

SMALLINT

Like INTEGER but limited to integer values up to six digits. If your integer values are relatively small, use SMALLINT instead of INT

DECIMAL(L,D)

Like the NUMBER specification, but the storage length is a minimum specification. That is, greater lengths are acceptable, but smaller ones are not. DECIMAL(9,2), DECIMAL(9), and DECIMAL are all acceptable.

INTEGER

May be abbreviated as INT. Integers are (whole) counting numbers, so they cannot be used if you want to store numbers that require decimal places.

A single database can hold

Multiple schemas belonging to different users or applications

When the DBMS is used, the CREATE SCHEMA AUTHORIZATION command must

Must be issued by the user who owns the schema. Example: That is, if you log on as JONES, you can only use CREATE SCHEMA AUTHORIZATION JONES.

Common NUMERIC SQL Data Types

NUMBER(L,D) INTEGER SMALLINT DECIMAL(L,D)

A column constraint applies to just

One column; a table constraint may apply to many columns. Those constraints are supported at varying levels of compliance by enterprise RDBMSs.

The primary key attributes contain both a NOT NULL and a UNIQUE specification. Those specifications enforce the entity integrity requirements. If the NOT NULL and UNIQUE specifications are not supported, use

PRIMARY KEY without the specifications. (For example, if you designate the PK in MS Access, the NOT NULL and UNIQUE specifications are automatically assumed and are not spelled out.)

DROP TABLE

Permanently deletes a table (and its data)

DROP VIEW

Permanently deletes a view

DROP INDEX

Permanently deletes an index

Unique composite indexes are often used to

Prevent data duplication.

In the creation of the LINE table, note that a UNIQUE constraint is added to

Prevent the duplication of an invoice line. A UNIQUE constraint is enforced through the creation of a unique index

PK

Primary Key

ON DELETE CASCADE foreign key action enforces

Referential integrity. The use of ON DELETE CASCADE is recommended for weak entities to ensure that the deletion of a row in the strong entity automatically triggers the deletion of the corresponding rows in the dependent weak entity.

SMALL IN T

Small integer values only

DATE.

Stores dates in the Julian date format.

For most RDBMSs( relational database management system)

The CREATE SCHEMA AUTHORIZATION is optional.

NOTE TO MS ACCESS USERS

The CREATE VIEW command is not directly supported in MS Access. To create a view in MS Access, you just need to create a SQL query and then save it.

The CHECK condition is validated whether a customer row is added or modified. However, while the CHECK condition may include any valid expression, it applies only to

The attributes in the table being checked. If you want to check for conditions that include attributes in other tables, you must use triggers.

NUMBER(L,D)

The declaration NUMBER(7,2) indicates numbers that will be stored with two decimal places and may be up to seven digits long, including the sign and the decimal place. Examples: 12.32, -134.99.

If you use Access, you can use the NUMBER data type, but you cannot use

The number delimiters at the SQL level. For example, using NUMBER(8,2) to indicate numbers with up to eight characters and two decimal places is fine in Oracle, but you cannot use it in Access—you must use NUMBER without the delimiters.

NOTE ABOUT COLUMN NAMES Do not use mathematical symbols such as +, −, and / in your column names; instead

Use an underscore to separate words, if necessary. For example, PER-NUM might generate an error message, but PER_NUM is acceptable. Also, do not use reserved words. Reserved words are words used by SQL to perform specific functions. For example, in some RDBMSs, the column name INITIAL will generate the message invalid column name.

To make the SQL code more readable, most SQL programmers

Use one line per column (attribute) definition. In addition, spaces are used to line up the attribute characteristics and constraints. Finally, both table and attribute names are fully capitalized.

VAR CHAR

Variable character length data, 1 to 2,000 characters. VAR CHAR is automatically converted to VAR CHAR2 in Oracle.

VARCHAR(L) or VARCHAR2(L)

Variable-length character data. The designation VARCHAR2(25) will let you store characters up to 25 characters long. However, VARCHAR will not leave unused spaces. Oracle automatically converts VARCHAR to VARCHAR2.

It is important to note that the DEFAULT value applies only

When new rows are added to a table and then only when no value is entered for the customer's area code. (The default value is not used when the table is modified.)

Note that the CREATE TABLE command lets you define constraints in two different places:

When you create the column definition (known as a column constraint). When you use the CONSTRAINT keyword (known as a table constraint).

If you use the PRIMARY KEY designation in Oracle

You do not need the NOT NULL and UNIQUE specifications.

NOTE ABOUT REFERENTIAL CONSTRAINT ACTIONS The support for the referential constraints actions varies from product to product. For example:

• MS Access, SQL Server, and Oracle support ON DELETE CASCADE. • MS Access and SQL Server support ON UPDATE CASCADE. • Oracle does not support ON UPDATE CASCADE. • Oracle supports SET NULL. • MS Access and SQL Server do not support SET NULL. • Refer to your product manuals for additional information on referential constraints. While MS Access does not support ON DELETE CASCADE or ON UPDATE CASCADE at the SQL command-line level, it does support them through the relationship window interface. In fact, whenever you try to establish a relationship between two tables in Access, the relationship window interface will automatically pop up