Database Reference
In-Depth Information
herefore, it is not good for bulk operations. On the other hand, SQL code is portable and can be
executed by bulk operations.
With SQL, database developers can create database objects such as tables and views. hey
can also use SQL to perform operations such as inserting, updating, and deleting records in a
table. SQL provides functions and procedures to help you implement database security measures
and performance tuning. SQL commands can be used to manage databases such as deining
constraints to maintain database integrity. Since the early 1970s, SQL has been a primary tool in
database development.
As the most important database development tool, SQL is supported by various DBMS prod-
ucts. It has been modiied in many ways to serve the special needs of each individual DBMS prod-
uct. To set up a common ground, the American National Standards Institute (ANSI) published the
standard for SQL. In 1986, the ANSI standardized the database querying language and named it
ANSI SQL-86. Since then, the SQL standard has been updated several times. SQL has been for-
mally adopted as an International Standard by the International Organization for Standardization
(ISO), the International Electrotechnical Commission (IEC), and it has also been adopted as a
Federal Information Processing Standard (FIPS) for the U.S. federal government. here are impor-
tant new features added to this latest version such as the support for XML and additional collection
data types.
Even with the added features, ANSI-based SQL is still a very basic query language. DBMS
vendors have added their own extensions on top of the basic ANSI-based SQL. he extended SQL
language used by Microsoft SQL Server is called Transact-SQL, which supports ANSI SQL. In
addition, Transact-SQL has added some commands for procedural programming. In this chapter,
let us irst study the basic commands for creating and managing database objects.
here are three major types of commands for creating and managing database objects:
◾
Data Deinition Language (DDL)
: Statements in DDL can be used to create, modify, and
delete database objects such as databases, tables, columns, and constraints. he commonly
used DDL statements are
CREATE: Used to create database objects
ALTER: Used to modify database objects
DROP: Used to delete database objects
◾
Data Control Language (DCL)
: Statements in DCL can be used to grant or revoke per-
missions on database objects. he commonly used DCL statements are
GRANT: Used to grant permissions for a user to use a database object or execute some SQL
commands
REVOKE: Used to remove previously granted permissions
◾
Data Manipulation Language (DML)
: Statements in DML can be used to select, insert,
update, and delete data in a database object. he commonly used DML statements are
SELECT: Used to query information from a database
INSERT: Used to insert new rows of data into database tables
UPDATE: Used to modify data values in database tables
DELETE: Used to delete existing rows in database tables
SQL statements by various DBMS vendors may be diferent. Often, the diference is minor.
SQL code conversion from one DBMS to another DBMS does not take much efort. his makes
SQL almost portable across DBMS products from diferent database vendors. Some companies
