Chapter 15 SQL Structured Query Language
Adrienne Watt & Nelson Eng
Structured Query Language (SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM’s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.
Many of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server (shown in Figure 15.1), MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL.
In a DBMS, the SQL database language is used to:
- Create the database and table structures
- Perform basic data management chores (add, delete and modify)
- Perform complex queries to transform raw data into useful information
In this chapter, we will focus on using SQL to create the database and table structures, mainly using SQL as a data definition language (DDL). In Chapter 16, we will use SQL as a data manipulation language (DML) to insert, delete, select and update data within the database tables.
Create Database
The major SQL DDL statements are CREATE DATABASE and CREATE/DROP/ALTER TABLE. The SQL statement CREATE is used to create the database and table structures.
Example: CREATE DATABASE SW
A new database named SW is created by the SQL statement CREATE DATABASE SW. Once the database is created, the next step is to create the database tables.
The general format for the CREATE TABLE command is:
(
ColumnName, Datatype, Optional Column Constraint,
ColumnName, Datatype, Optional Column Constraint,
Optional table Constraints
);
Tablename is the name of the database table such as Employee. Each field in the CREATE TABLE has three parts (see above):
- ColumnName
- Data type
- Optional Column Constraint
ColumnName
The ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName.
Data Type
The data type, as described below, must be a system data type or a user-defined data type. Many of the data types have a size such as CHAR(35) or Numeric(8,2).
Bit –Integer data with either a 1 or 0 value
Int –Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 – 1 (2,147,483,647)
Smallint –Integer data from 2^15 (-32,768) through 2^15 – 1 (32,767)
Tinyint –Integer data from 0 through 255
Decimal –Fixed precision and scale numeric data from -10^38 -1 through 10^38
Numeric –A synonym for decimal
Timestamp –A database-wide unique number
Uniqueidentifier –A globally unique identifier (GUID)
Money – Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accuracy to one-ten-thousandth of a monetary unit
Smallmoney –Monetary data values from -214,748.3648 thro