The Internet Encyclopedia (Volume 3)

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

SQL ̇OLE WL040/Bidgolio-Vol I WL040-Sample.cls June 20, 2003 13:16 Char Count= 0

360 STRUCTUREDQUERYLANGUAGE(SQL)

Domains

A domain is a user-created data type that has a constrained

value set. For our movie example we could create a movie

rating domain that was a 5-character data type that only

allowed the following ratings:{G, PG, PG-13, R, NC-17,

X, NR}. Once the domain was created, it could be used in

any location where a predefined data type could be used,

such as table definitions. Not all database vendors support

the creation and use of domains; check the user guide for

information and syntax specifications.

DCL

SQL’s DCL is used to control authorization for data ac-

cess and auditing database use. As with any application

with Internet access, security is a prime concern. Today’s

RDBMSs provide a host of security mechanisms that the

database administrators can use to prevent unauthorized

access to the data stored within.

Granting Access to Data

By default, in an enterprise-edition RDBMS, the only user

who has access to an object (table, index, etc.) is the user

who created that object. This can be problematic when

we have to generate a report based on information that is

stored in tables that are owned by other users. Fortunately,

the solution is thegrantstatement. The syntax is

grant <access> on <table> to <user>;

One drawback to the grant statement is that it re-

quires a grant statement for every user–object–access

level combination—which can be a challenging task in a

large enterprise where data security is critical. Accounting

departments usually want a high degree of accountability

from those individuals who have access to their data. De-

pending on a database’s implementation, there may be

mechanisms in place to help ease the burden of security

management.

DML

Inserting Data

Syntax:

insert into <table> (column {[, column]}))

values (<value> | <expression>)

For A Single Row

For example, inserting a new row of data into the

ticketprice table would look like this:

insert into TICKET_PRICE values (

'MIDNIGHT SHOW', 5.50);

For Multiple Rows

If we want to insert multiple rows of data we simply have

multiple insert statements; however, if we have data stored

in another table that we wish to place in our table (per-

haps copying data from a production database into a de-

veloper’s personal database), we can use an expression.

For instance, if we had ticket price information in a

table named masterticketprice, and we wanted to copy

the data into our local ticketprice table, we could issue

the following command:

insert into TICKET_PRICE

select * from MASTER_TICKET_PRICE;

Now this is assuming that the structure of THE

MASTERTICKETPRICE table and the TICKETPRICE

table are the same (i.e., the same columns). If we only

want a partial set of values from the MASTERTICKET

PRICE table, we can add awhereclause to our select state-

ment. Likewise, we can reduce the number of columns

by selecting only those columns from the MASTER

TICKETPRICE table that match our TICKETPRICE

table.

Modifying Data

Syntax:

update <table>

set <column> = <value> | <expression>

{where_clause};

For a Single Row

To update only one row in a table, we must specify the

whereclause that would return only that row. For example,

if we wanted to raise the price of a KIDS movie ticket to

$5.00, we would issue the following:

update TICKET_PRICE

set PRICE = 5.00

where TICKET_TYPE = 'KIDS';

We could perform a calculation or retrieve a value from

another database table instead of setting the price equal

to a constant (5.00).

For Multiple Rows

For modifying multiple rows there are two options—if we

want to modify all of the rows in a given table (say to

increase the price of movie tickets by 10% or to change

the case of a column), we just leave off thewhereclause

as such:

update TICKET_PRICE

set PRICE = PRICE * 1.10;

If we want to modify selected rows, then we must spec-

ify awhereclause that will return only the desired rows.

For example, if we want to raise just the adult prices by

10%, we issue the following:

update TICKET_PRICE

set PRICE = PRICE * 1.10

where TICKET_TYPE like 'ADULT%';