Welcome to the World of SQL

Integrity Constraints

2010-03-29T11:14:00.000-07:00

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in referential integrity (RI).

Primary Key Constraints
Unique Constraints
Foreign Key Constraints

NOT NULL Constraints

Primary Key Constraints

Primary key is the term used to identify one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key. For example, either the employee's Social Security number or an assigned employee identification number is the logical primary key for an employee table. The objective is for every record to have a unique primary key or value for the employee's identification number. Because there is probably no need to have more than one record for each employee in an employee table, the employee identification number makes a logical primary key. The primary key is assigned at table creation.

The following example identifies the EMP_ID column as the PRIMARY KEY for the EMPLOYEES table:

CREATE TABLE EMPLOYEE_TBL
(EMP_ID CHAR(9) NOT NULL PRIMARY KEY,EMP_NAME VARCHAR2(40) NOT NULL,EMP_ST_ADDR VARCHAR2(20) NOT NULL,EMP_CITY VARCHAR2(15) NOT NULL,EMP_ST CHAR(2) NOT NULL,EMP_ZIP NUMBER(5) NOT NULL,EMP_PHONE NUMBER(10) NULL,EMP_PAGER NUMBER(10) NULL);

Unique ConstraintsA unique column constraint in a table is similar to a primary key in that the value in that column for every row of data in the table must have a unique value. While a primary key constraint is placed on one column, you can place a unique constraint on another column even though it is not actually for use as the primary key.
Study the following example:
CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL PRIMARY KEY, EMP_NAME VARCHAR2(40) NOT NULL, EMP_ST_ADDR VARCHAR2(20) NOT NULL, EMP_CITY VARCHAR2(15) NOT NULL, EMP_ST CHAR(2) NOT NULL, EMP_ZIP NUMBER(5) NOT NULL, EMP_PHONE NUMBER(10) NULL UNIQUE, EMP_PAGER NUMBER(10) NULL);

Foreign Key Constraints
A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism used to enforce referential integrity between tables in a relational database. A column defined as a foreign key is used to reference a column defined as a primary key in another table.
Study the creation of the foreign key in the following example:
CREATE TABLE EMPLOYEE_PAY_TBL (EMP_ID CHAR(9) NOT NULL, POSITION VARCHAR2(15) NOT NULL, DATE_HIRE DATE NULL, PAY_RATE NUMBER(4,2) NOT NULL, DATE_LAST_RAISE DATE NULL, CONSTRAINT EMP_ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID));
The EMP_ID column in this example has been designated as the foreign key for the EMPLOYEE_PAY_TBL table. This foreign key, as you can see, references the EMP_ID co-lumn in the EMPLOYEE_TBL table. This foreign key ensures that for every EMP_ID in the EMPLOYEE_PAY_TBL, there is a corresponding EMP_ID in the EMPLOYEE_TBL. This is called a parent/child relationship. The parent table is the EMPLOYEE_TBL table, and the child table is theEMPLOYEE_PAY_TBL table. Study Table for a better understanding of the parent table/child table relationship.

Figure 3.2: The parent/child table relationship.
In this figure, the EMP_ID column in the child table references the EMP_ID column in the parent table. In order for a value to be inserted for EMP_ID in the child table, there must first exist a value for EMP_ID in the parent table. Likewise, for a value to be removed for EMP_ID in the parent table, all corresponding values for EMP_ID must first be removed from the child table. This is how referential integrity works.
A foreign key can be added to a table using the ALTER TABLE command, as shown in the following example:
ALTER TABLE EMPLOYEE_PAY_TBL ADD CONSTRAINT ID_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID);

The options available with the ALTER TABLE command differ among different implementations of SQL, particularly when dealing with constraints.In addition, the actual use and definitions of constraints also vary, but the concept of referential integrity should be the same with all relational databases.
NOT NULL Constraints
The keywords NULL and NOT NULL listed on the same line as each column and after the data type. NOT NULL is a constraint that you can place on a table's column. This constraint disallows the entrance of NULLvalues into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column.

What Are Database Objects?

2010-03-25T13:00:00.000-07:00

A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views, clusters, sequences, indexes, and synonyms.

What Is a Schema?

A schema is a collection of database objects associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database. Basically, any user who creates an object has just created his or her own schema. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.

Say you have been issued a database username and password by the database administrator. Your username is USER1. Suppose you log on to the database and then create a table called EMPLOYEE_TBL. Your table's actual name isUSER1.EMPLOYEE_TBL. The schema name for that table is USER1, which is also the owner of that table. You have just created the first table of a schema.

The good thing about schemas is that when you access a table that you own (in your own schema), you do not have to refer to the schema name. For instance, you could refer to your table as either one of the following:

EMPLOYEE_TBL USER1.EMPLOYEE_TBL

The first option is preferred because it requires fewer keystrokes. If another user were to query one of your tables, the user would have to specify the schema, as follows:

USER1.EMPLOYEE_TBL

Schemas in a database.

There are, in Fig, two user accounts in the database that own tables: USER1 and USER2. Each user account has its own schema. Some examples for how the two users can access their own tables and tables owned by the other user follow:

USER1 accesses own table1:	TABLE1
USER1 accesses own test:	TEST
USER1 accesses USER2's table10:	USER2.TABLE10
USER1 accesses USER2's test:	USER2.TEST

Both users have a table called TEST. Tables can have the same names in a database as long as they belong to different schemas. If you look at it this way, table names are always unique in a database, because the schema owner is actually part of the table name. For instance, USER1.TEST is different than USER2.TEST. If you do not specify a schema with the table name when accessing tables in a database, the database server looks for a table that you own by default. That is, if USER1 tries to access TEST, the database server looks for a USER1-owned table named TEST before it looks for other objects owned by USER1, such as synonyms to tables in another schema.

What Is Data?

2010-03-25T12:47:00.000-07:00

Data is a collection of information stored in a database as one of several different data types. Data includes names, numbers, dollar amounts, text, graphics, decimals, figures, calculations, summarization, and just about anything else you can possibly imagine. Data can be stored in uppercase, lowercase, or mixed case. Data can be manipulated or changed; most data does not remain static for its lifetime.

Basic Data Types

The following sections discuss the basic data types supported by ANSI SQL. Data types are characteristics of the data itself, whose attributes are placed on fields within a table. For example, you can specify that a field must contain numeric values, disallowing the entering of alphanumeric strings. After all, you would not want to enter alphabetic characters in a field for a dollar amount.

Note

Every implementation of SQL seems to have its own specific data types. The use of implementation-specific data types is necessary to support the philosophy of each implementation on how to handle the storage of data. However, the basics are the same among all implementations.

The very basic data types, as with most other languages, are

Character strings
Numeric strings
Date and time values

Fixed-Length Characters

Constant characters, those strings that always have the same length, are stored using a fixed-length data type. The following is the standard for an SQL fixed-length character:

CHARACTER(n)

n represents a number identifying the allocated, or maximum length, of the particular field with this definition.

Some implementations of SQL use the CHAR data type to store fixed-length data. Alphanumeric data can be stored in this data type. An example of a constant length data type would be for a state abbreviation because all state abbreviations are two characters.

Spaces are normally used to fill extra spots when using a fixed-length data type; if a field's length was set to 10 and data entered filled only five places, the remaining five spaces are recorded as spaces. The padding of spaces ensures that each value in a field is a fixed length.

Warning

Be careful not to use a fixed-length data type for fields that may contain varying-length values, such as an individual's name. If you use the fixed-length data type inappropriately, problems such as the waste of available space and the inability to make accurate comparisons between data will eventually be encountered.

Variable Characters

SQL supports the use of varying-length strings,strings whose length is not constant for all data. The following is the standard for an SQL varying-length character:

CHARACTER VARYING(n)

n represents a number identifying the allocated, or maximum length, of the particular field with this definition.

Common data types for variable-length character values are the VARCHAR and VARCHAR2 data types. VARCHAR is the ANSI standard, which Microsoft SQL Server uses; VARCHAR2 is used by Oracle and should be used in Oracle, because VARCHAR's usage in the future may change. The data stored can be alphanumeric.

Remember that fixed-length data types typically pad spaces to fill in allocated places not used by the field. The varying-length data type does not work this way. For instance, if the allocated length of a varying-length field is 10, and a string of five characters is entered, the total length of that particular value is only 5. Spaces are not used to fill unused places in a column.

Tip

Always use the varying-length data type for non-constant character strings to save database space.

Numeric Values

Numeric values are stored in fields that are defined as some type of number, typically referred to as NUMBER, INTEGER, REAL, DECIMAL, and so on.

The following are the standards for SQL numeric values:

BIT(n)
BIT VARYING(n)
DECIMAL(p,s)
INTEGER
SMALLINT
FLOAT(p)
REAL(s)
DOUBLE PRECISION(P)

p represents a number identifying the allocated, or maximum length, of the particular field for each appropriate definition.

s is a number to the right of the decimal point, such as 34.ss.

A common numeric data type in SQL implementations is NUMBER, which accommodates the direction for numeric values provided by ANSI. Numeric values can be stored as zero, positive, negative, fixed, and floating-point numbers. The following is an example using NUMBER:

NUMBER(5)

This example restricts the maximum value entered in a particular field to 99999.

Decimal Values

Decimal values are numeric values that include the use of a decimal point. The standard for a decimal in SQL follows, where the p is the precision and the s is the decimal's scale:

DECIMAL(p,s)

The precision is the total length of the numeric value. In a numeric defined DECIMAL(4,2), the precision is 4, which is the total length allocated for a numeric value.

The scale is the number of digits to the right of the decimal point. The scale is 2 in the previous DECIMAL(4,2) example.

34.33 inserted into a DECIMAL(3,1) is typically rounded to 34.3.

If a numeric value was defined as the following data type, the maximum value allowed would be 99.99:

DECIMAL(4,2)

The precision is 4, which represents the total length allocated for an associated value. The scale is 2, which represents the number of places, or bytes, reserved to the right side of the decimal point. The decimal point itself does not count as a character.

Allowed values for a column defined as DECIMAL(4,2) include the following:

12
12.4
12.44
12.449

The last numeric value, 12.449, is rounded off to 12.45 upon input into the column.

Integers

An integer is a numeric value that does not contain a decimal, only whole numbers (both positive and negative).

Valid integers include the following:

1
0
-1
99
-99
199

Floating-Point Decimals

Floating-point decimals are decimal values whose precision and scale are variable lengths and virtually without limit. Any precision and scale is acceptable. The REAL data type designates a column with single-precision, floating-point numbers. The DOUBLE PRECISION data type designates a column that contains double-precision, floating-point numbers. To be considered a single-precision floating point, the precision must be between 1 and 21 inclusive. To be considered a double-precision floating point, the precision must be between 22 and 53 inclusive. The following are examples of the FLOAT data type:

FLOAT
FLOAT(15)
FLOAT(50)

Dates and Time

Date and time data types are quite obviously used to keep track of information concerning dates and time. Standard SQL supports what are called DATETIME data types, which include the following specific data types:

DATE
TIME
INTERVAL
TIMESTAMP

The elements of a DATETIME data type consist of the following:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND

Note

The SECOND element can also be broken down to fractions of a second. The range is from 00.000 to 61.999, although some implementations of SQL may not support this range.

Be aware that each implementation of SQL may have its own customized data type for dates and times. The previous data types and elements are standards to which each SQL vendor should adhere, but be advised that most implementations have their own data type for date values, varying in both appearance and the way date information is actually stored internally.

A length is not normally specified for a date data type. Later in this hour, you learn more about dates, how date information is stored in some implementations, how to manipulate dates and times using conversion functions, and study practical examples of how dates and time are used in the real world.

Literal Strings

A literal string is a series of characters, such as a name or a phone number, that is explicitly specified by a user or program. Literal strings consist of data with the same attributes as the previously discussed data types, but the value of the string is known; the value of a column itself is usually unknown, because there is typically a different value for a column associated with each row of data in a table.

You do not actually specify data types with literal strings—you simply specify the string. Some examples of literal strings follow:

'Hello'
45000
"45000"
3.14
'November 1, 1997'

The alphanumeric strings are enclosed by single quotation marks, whereas the number value 45000 is not. Also notice that the second numeric value of 45000 is enclosed by quotation marks. Generally speaking, character strings require quotation marks, whereas numeric strings don't. You see later how literal strings are used with database queries.

NULL Data Types

a NULL value isa missing value or a column in a row of data that has not been assigned a value. NULL values are used in nearly all parts of SQL, including the creation of tables, search conditions for queries, and even in literal strings.

The following are two methods for referencing a NULL value:

NULL (the keyword NULL itself)
'' (single quotation marks with nothing in between)

The following does not represent a NULL value, but a literal string containing the characters N-U-L-L:

'NULL'

BOOLEAN Values

A BOOLEAN value is a value of either TRUE, FALSE, or NULL. BOOLEAN values are used to make data comparisons. For example, when criteria are specified for a query, each condition evaluates to either a TRUE, FALSE, or NULL. If theBOOLEAN value of TRUE is returned by all conditions in a query, data is returned. If a BOOLEAN value of FALSE or NULL is returned, data may not be returned.

Consider the following example:

WHERE NAME = 'SMITH'

This line might be a condition found in a query. The condition is evaluated for every row of data in the table that is being queried. If the value of NAME is SMITH for a row of data in the table, the condition returns the value TRUE, thereby returning the data associated with that record.

User-Defined Types

A user-defined type is a data type that is defined by the user. User-defined types allow users to customize their own data types based on existing data types. The CREATE TYPE statement is used to create a user-defined type.

For example, you can create a type as follows:

CREATE TYPE PERSON AS OBJECT (NAME       VARCHAR2(30),  SSN    VARCHAR2(9));

You can reference your user-defined type as follows:

CREATE TABLE EMP_PAY (EMPLOYEE   PERSON,  SALARY     NUMBER(10,2),  HIRE_DATE      DATE);

Notice that the data type referenced for the first column EMPLOYEE is PERSON. PERSON is the user-defined type you created in the first example.

Domains

A domain is a set of valid data types that can be used. A domain is associated with a data type, so that only certain data is accepted. After a domain is created, you can add constraints to the domain. The domain is used like the user-defined type.

You can create a domain as follows:

CREATE DOMAIN MONEY_D AS NUMBER(8,2);

You can add constraints to your domain as follows:

ALTER DOMAIN MONEY_D  ADD CONSTRAINT MONEY_CON1  CHECK (VALUE > 5);

You can reference the domain as follows:

CREATE TABLE EMP_PAY (EMP_ID        NUMBER(9),  EMP_NAME      VARCHAR2(30),  PAY_RATE      MONEY_D);

Types of SQL Commands

2010-03-25T12:40:00.000-07:00

The basic categories of commands used in SQL to perform various functions. These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.

The main categories are

DDL (Data Definition Language)
DML(Data Manipulation Language)
DQL (Data Query Language)
DCL (Data Control Language)
Data administration commands
Transactional control commands

Defining Database Structures (DDL)
Data Definition Language, DDL, is the part of SQL that allows a database user to create and restructure database objects, such as the creation or the deletion of a table.
The main DDL commands discussed during following hours include the following:
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX

Manipulating Data (DML)
Data Manipulation Language, DML, is the part of SQL used to manipulate data within objects of a relational database.
There are three basic DML commands:
INSERT
UPDATE
DELETE

Selecting Data (DQL)
Data Query Language (DQL) is the most concentrated focus of SQL for a relational database user. The command is as follows:
SELECT

Data Control Language (DCL)
Data control commands in SQL allow you to control access to data within the database. These DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows:
ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM

Data Administration Commands
Data administration commands allow the user to perform audits and perform analyses on operations within the database. They can also be used to help analyze system performance. Two general data administration commands are as follows:
START AUDIT
STOP AUDIT

Transactional Control Commands
In addition to the previously introduced categories of commands, there are commands that allow the user to manage database transactions.
COMMIT Used to save database transactions
ROLLBACK Used to undo database transactions
SAVEPOINT Creates points within groups of transactions in which to ROLLBACK
SET TRANSACTION Places a name on a transaction

What Is SQL?

2010-03-25T12:31:00.000-07:00

What Is SQL?

SQL, Structured Query Language, is the standard language used to communicate with a relational database.

What Is a Database?

In very simple terms, a database is a collection of data. Some like to think of a database as an organized mechanism that has the capability of storing information, through which a user can retrieve stored information in an effective and efficient manner.

People use databases every day without realizing it. A phone book is a database. The data contained consists of individuals' names, addresses, and telephone numbers. The listings are alphabetized or indexed, which allows the user to reference a particular local resident with ease. Ultimately, this data is stored in a database somewhere on a computer. After all, each page of a phone book is not manually typed each year a new edition is released.

The database has to be maintained. As people move to different cities or states, entries may have to be added or removed from the phone book. Likewise, entries will have to be modified for people changing names, addresses, or telephone numbers, and so on.

A relational database is a database divided into logical units called tables, where tables are related to one another within the database. A relational database allows data to be broken down into logical, smaller, more manageable units, allowing for easier maintenance and providing more optimal database performance according to the level of organization.

Welcome to the World of SQL

Integrity Constraints

Unique Constraints

NOT NULL Constraints

Primary Key Constraints

Unique Constraints

Foreign Key Constraints

NOT NULL Constraints

What Are Database Objects?

What Is a Schema?

What Is Data?

Basic Data Types

Fixed-Length Characters

Variable Characters

Numeric Values

Decimal Values

Integers

Floating-Point Decimals

Dates and Time

Literal Strings

NULL Data Types

BOOLEAN Values

User-Defined Types

Domains

Types of SQL Commands

Defining Database Structures (DDL)

Manipulating Data (DML)

Selecting Data (DQL)

Data Control Language (DCL)

Data Administration Commands

Transactional Control Commands

What Is SQL?

What Is SQL?

What Is a Database?