Structured questions Language (SQL) is a database language designed for managing data held in a relational database monitoring system. SQL to be initially developed by IBM in the beforehand 1970s (Date 1986). The initial version, called SEQUEL (Structured English ask Language), was designed come manipulate and also retrieve data save in IBM’s quasi-relational database management system, mechanism R. Then in the so late 1970s, Relational software application Inc., which is now Oracle Corporation, introduced the first commercially accessible implementation the SQL, Oracle V2 because that VAX computers.

You are watching: A table alias can consist of a maximum of ____ characters.

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.

*
Figure 15.1. Example of Microsoft SQL Server, by A. Watt.

In a DBMS, the SQL database language is offered to:

Create the database and also table structuresPerform an easy data management chores (add, delete and modify)Perform facility queries to transform raw data into helpful information

In this chapter, us will emphasis on using SQL to develop the database and table structures, largely using SQL together a data definition language (DDL). In thing 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 explanation are develop DATABASE and also CREATE/DROP/ALTER TABLE. The SQL statement develop is provided to develop the database and also table structures.

Example: develop DATABASE SW

A new database called SW is developed by the SQL statement create DATABASE SW. As soon as the database is created, the following step is to create the database tables.

The general style for the develop TABLE command is:


CREATE TABLE (ColumnName, Datatype, Optional tower Constraint,ColumnName, Datatype, Optional column Constraint,Optional table Constraints);

Tablename is the name of the database table such together Employee. Each ar in the create TABLE has actually three components (see above):

ColumnNameData typeOptional tower Constraint

ColumnName

The ColumnName must be distinct within the table. Some examples of ColumnNames room FirstName and LastName.

Data Type

The data type, as described below, have to be a mechanism data form or a user-defined data type. Many the the data varieties have a dimension 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) v 2^31 – 1 (2,147,483,647)

Smallint –Integer data native 2^15 (-32,768) v 2^15 – 1 (32,767)

Tinyint Integer data indigenous 0 with 255

Decimal –Fixed precision and scale numeric data from -10^38 -1 through 10^38

Numeric –A synonym for decimal

Timestamp –A database-wide distinct number

Uniqueidentifier –A globally distinctive identifier (GUID)

Money – financial data worths from -2^63 (-922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), v accuracy to one-ten-thousandth that a monetary unit

Smallmoney –Monetary data values from -214,748.3648 v +214,748.3647, with accuracy to one-ten-thousandth the a monetary unit

Float –Floating precision number data native -1.79E + 308 through 1.79E + 308

Real –Floating precision number data indigenous -3.40E + 38 through 3.40E + 38

Datetime –Date and also time data indigenous January 1, 1753, come December 31, 9999, with an accuracy the one-three-hundredths that a second, or 3.33 milliseconds

Smalldatetime –Date and also time data native January 1, 1900, with June 6, 2079, v an accuracy the one minute

Char –Fixed-length non-Unicode personality data v a maximum size of 8,000 characters

Varchar –Variable-length non-Unicode data with a maximum of 8,000 characters

Text –Variable-length non-Unicode data through a maximum size of 2^31 – 1 (2,147,483,647) characters

Binary –Fixed-length binary data with a maximum length of 8,000 bytes

Varbinary –Variable-length binary data v a maximum size of 8,000 bytes

Image Variable-length binary data v a maximum length of 2^31 – 1 (2,147,483,647) bytes

Optional column Constraints

The Optional ColumnConstraints are NULL, no NULL, UNIQUE, PRIMARY key and DEFAULT, used to initialize a worth for a new record. The shaft constraint NULL indicates that null values space allowed, which method that a row have the right to be developed without a value for this column. The shaft constraint not NULL indicates that a value have to be supplied as soon as a new row is created.

To illustrate, we will use the SQL statement create TABLE employee to produce the employee table v 16 qualities or fields.


USE SWCREATE TABLE EMPLOYEES(EmployeeNo CHAR(10) not NULL UNIQUE,DepartmentName CHAR(30) not NULL DEFAULT “Human Resources”,FirstName CHAR(25) no NULL,LastName CHAR(25) no NULL,Category CHAR(20) no NULL,HourlyRate currency NOT NULL,TimeCard LOGICAL no NULL,HourlySalaried CHAR(1) no NULL,EmpType CHAR(1) not NULL,Terminated LOGICAL not NULL,ExemptCode CHAR(2) not NULL,Supervisor LOGICAL no NULL,SupervisorName CHAR(50) no NULL,BirthDate day NOT NULL,CollegeDegree CHAR(5) not NULL,CONSTRAINT Employee_PK major KEY(EmployeeNo);

The very first field is EmployeeNo through a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL).

Similarly, the 2nd field is DepartmentName v a field form CHAR of size 30. After all the table columns are defined, a table constraint, established by words CONSTRAINT, is supplied to create the primary key:


We will talk about the constraint property additional later in this chapter.

Likewise, we can produce a department table, a project table and an Assignment table making use of the create TABLE SQL DDL command as presented in the below example.


USE SWCREATE TABLE DEPARTMENT(DepartmentName Char(35) not NULL,BudgetCode Char(30) not NULL,OfficeNumber Char(15) no NULL,Phone Char(15) no NULL,CONSTRAINT DEPARTMENT_PK main KEY(DepartmentName));

In this example, a project table is developed with 7 fields: ProjectID, ProjectName, Department, MaxHours, StartDate, and EndDate.


USE SWCREATE TABLE PROJECT(ProjectID Int not NULL identity (1000,100),ProjectName Char(50) no NULL,Department Char(35) no NULL,MaxHours Numeric(8,2) no NULL DEFAULT 100,StartDate DateTime NULL,EndDate DateTime NULL,CONSTRAINT ASSIGNMENT_PK main KEY(ProjectID));

In this last example, one assignment table is created with three fields: ProjectID, EmployeeNumber, and also HoursWorked. The assignment table is provided to document who (EmployeeNumber) and how lot time(HoursWorked) an employee operated on the particular project(ProjectID).


USE SWCREATE TABLE ASSIGNMENT(ProjectID Int no NULL,EmployeeNumber Int no NULL,HoursWorked Numeric(6,2) NULL,);

Table Constraints

Table constraints are figured out by the CONSTRAINT keyword and can be used to implement assorted constraints explained below.

IDENTITY constraint

We can use the optional shaft constraint identity to administer a unique, incremental value for the column. Identification columns are often used with the PRIMARY key constraints to serve as the unique row identifier because that the table. The identification property have the right to be assigned to a pillar with a tinyint, smallint, int, decimal or numeric data type. This constraint:

Generates sequential numbersDoes no enforce reality integrityOnly one column have the right to have the identification propertyMust be defined as an integer, numeric or decimal data typeCannot upgrade a shaft with the identification propertyCannot save on computer NULL valuesCannot tie defaults and default constraints to the column

For IDENTITY<(seed, increment)>

Seed – the initial worth of the identity columnIncrement – the worth to add to the last increment column

We will use another database example to further illustrate the SQL DDL statements by developing the table tblHotel in this HOTEL database.


CREATE TABLE tblHotel(HotelNo Int identity (1,1),Name Char(50) not NULL,Address Char(50) NULL,City Char(25) NULL,)

UNIQUE constraint

The unique constraint avoids duplicate worths from being gotten in into a column.

Both PK and UNIQUE border are offered to enforce entity integrity.Multiple unique constraints can be defined for a table.When a unique constraint is included to an currently table, the currently data is constantly validated.A distinct constraint deserve to be placed on columns that accept nulls. Only one row can be NULL.A distinct constraint instantly creates a unique index top top the selected column.

This is the general syntax because that the distinctive constraint:


UNIQUE (col_name <, col_name2 <…, col_name16>>)

FOREIGN crucial constraint

The FOREIGN vital (FK) constraint specifies a column, or mix of columns, whose values complement the PRIMARY key (PK) of an additional table.

Values in an FK are immediately updated as soon as the PK values in the linked table space updated/changed.FK constraints should reference PK or the distinctive constraint of one more table.The number of columns because that FK should be exact same as PK or distinctive constraint.If the with NOCHECK alternative is used, the FK constraint will certainly not validate existing data in a table.No table of contents is developed on the columns that participate in one FK constraint.

This is the basic syntax because that the FOREIGN an essential constraint:


>)>REFERENCES ref_table <(ref_col <, ref_col2 <…, ref_col16>>)>

In this example, the field HotelNo in the tblRoom table is a FK come the field HotelNo in the tblHotel table displayed previously.


USE HOTELGOCREATE TABLE tblRoom(HotelNo Int not NULL ,RoomNo Int not NULL,Type Char(50) NULL,Price Money NULL,PRIMARY vital (HotelNo, RoomNo),FOREIGN crucial (HotelNo) recommendations tblHotel)

CHECK constraint

The examine constraint restricts values that can be entered into a table.

It have the right to contain find conditions comparable to a whereby clause.It can reference columns in the exact same table.The data validation preeminence for a inspect constraint have to evaluate to a boolean expression.It can be defined for a tower that has a preeminence bound come it.

This is the basic syntax because that the check constraint:


In this example, the kind field is minimal to have only the types ‘Single’, ‘Double’, ‘Suite’ or ‘Executive’.


USE HOTELGOCREATE TABLE tblRoom(HotelNo Int not NULL,RoomNo Int not NULL,Type Char(50) NULL,Price Money NULL,PRIMARY vital (HotelNo, RoomNo),FOREIGN crucial (HotelNo) references tblHotelCONSTRAINT Valid_TypeCHECK (Type IN (‘Single’, ‘Double’, ‘Suite’, ‘Executive’)))

In this second example, the employee hire day should be before January 1, 2004, or have actually a salary limit of $300,000.


GOCREATE TABLE SALESREPS(Empl_num Int no NullCHECK (Empl_num between 101 and 199),Name Char (15),Age Int examine (Age >= 21),Quota Money check (Quota >= 0.0),HireDate DateTime,CONSTRAINT QuotaCap examine ((HireDate )

DEFAULT constraint

The DEFAULT constraint is provided to supply a value that is automatically added for a shaft if the user does no supply one.

A column can have just one DEFAULT.The DEFAULT constraint cannot be supplied on columns through a timestamp data form or identity property.DEFAULT limit are instantly bound to a shaft when they room created.

The basic syntax for the DEFAULT constraint is:


User identified Types

User defined types are constantly based on system-supplied data type. They can enforce data integrity and also they enable nulls.

To develop a user-defined data form in SQL Server, choose varieties under “Programmability” in your database. Next, right click and choose ‘New’ –>‘User-defined data type’ or execute the sp_addtype mechanism stored procedure. After this, type:


This will add a brand-new user-defined data kind called SIN v nine characters.

In this example, the field EmployeeSIN supplies the user-defined data type SIN.


CREATE TABLE SINTable(EmployeeID INT main Key,EmployeeSIN SIN,CONSTRAINT CheckSINCHECK (EmployeeSIN LIKE‘ <0-9><0-9><0-9> – <0-9><0-9> <0-9> – <0-9><0-9><0-9> ‘))

ALTER TABLE

You have the right to use alter TABLE explanation to add and fall constraints.

ALTER TABLE allows columns to be removed.When a constraint is added, every existing data are confirmed for violations.

In this example, we use the alter TABLE statement to the identification property to a ColumnName field.


Use the transform TABLE declare to include a shaft with the identification property such as change TABLE TableName.


DROP TABLE

The drop TABLE will remove a table native the database. Make certain you have the correct database selected.


DDL: abbreviation for data an interpretation language

DML: abbreviation for data manipulation language

SEQUEL: acronym for Structured English query Language; designed come manipulate and retrieve data stored in IBM’s quasi-relational database administration system, system R

Structured ask Language (SQL): a database language designed for regulating data hosted in a relational database monitoring system
Using the details for the Chapter 9 exercise, carry out the schema making use of Transact SQL (show SQL statements for each table). Carry out the constraints as well.Create the table shown here in SQL Server and show the statements friend used.Table: Employee

ATTRIBUTE (FIELD) NAMEDATA DECLARATION
EMP_NUMCHAR(3)
EMP_LNAMEVARCHAR(15)
EMP_FNAMEVARCHAR(15)
EMP_INITIALCHAR(1)
EMP_HIREDATEDATE
JOB_CODECHAR(3)
Write the SQL code to adjust the project code come 501 because that the human being whose personnel number is 107. ~ you have actually completed the task, examine the results, and then reset the task code come its initial value.Assuming that the data shown in the Employee table have actually been entered, compose the SQL code that perform all attributes for a job code the 502.Write the SQL password to delete the heat for the human named william Smithfield, who was hired on June 22, 2004, and whose job code group is 500. (Hint: use logical operators to include all the information provided in this problem.)Add the qualities EMP_PCT and also PROJ_NUM come the Employee table. The EMP_PCT is the bonus portion to be payment to each employee.Using a single command, write the SQL code that will get in the task number (PROJ_NUM) = 18 for all employees whose job group (JOB_CODE) is 500.Using a single command, write the SQL password that will go into the job number (PROJ_NUM) = 25 for all employees whose job group (JOB_CODE) is 502 or higher.Write the SQL code that will adjust the PROJ_NUM come 14 for those employees that were hired prior to January 1, 1994, and also whose task code is at the very least 501. (You may assume the the table will certainly be restored to that original condition preceding this question.)

Also see Appendix C: SQL Lab with Solution


References

Date, C.J. Relational Database Selected Writings.

See more: Are There Coconut Trees In Florida (Not The Florida Keys), » Yes They Do!

 Reading: Mass: Addison-Wesley Publishing firm Inc., 1986, p. 269-311.