DDL 1

Data Definition Language and Data Manipulation Language

SQL supports functions such as building and manipulating database objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access and overall database administration. Such functions can be classified into a number of categories and the most well known two categories are Data Definition Language (DLL) andData Manipulation Language (DML).

DDL allows user to create and restructure database objects, such as creating and deleting database tables. Besides, DDL can be used to define table indexes as well asforeign keys between tables. Some of the commonly used DDL commands are:

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • CREATE INDEX
  • ALTER INDEX
  • DROP INDEX
  • CREATE VIEW
  • DROP VIEW

DML allows users to manipulate data within the objects of a database.Some of the commonly used DML commands are:

  • SELECT
  • INSERT INTO
  • UPDATE
  • DELETE

DDL allows database users to define database objects whereas DML allows database users to retrieve, insert, delete and update data in a database.

  1. Create table with primary key

For each table,it is necessary to have a field or a combination of selected fields such that their values can be used to identify each table row uniquely. Such an identifier is known as a candidate key. The concept of candidate key is essential to good database design. The most commonly used candidate key of a table is typically selected to be the primary key of the table.

The PRIMARY KEY keyword is used to specify the fields in a table that compose the table’s primary key.

Syntax

CREATE TABLE TableName
(
Column1DataType, NOT NULL
Column2DataType, NOT NULL
......
PRIMARY KEY (Column1, Column2, …)
)
  • Technically, all fields in a primary key should be defined to be UNIQUE and NOT NULL. Although some databases like Microsoft Access 2003 may take all primary key fields as UNIQUE and NOT NULL even though they are not specified, it is a good practice to specify them explicitly.

Example

To create a table called“Student” with the primary key “StdID”, we can use the following statement:

CREATE TABLE Student
(
StdID char(7) NOT NULL UNIQUE,
Name varchar(30),
Class char(10),
Age smallint,
OverduePay decimal(5,2),
PRIMARY KEY (StdID)
)
  1. Createtable with foreign key

A foreign key (which may be composite) to another table ensures that the value of the foreign key field(s) can be found in the primary key of the foreign table. The following example shows how to create a table in a database with foreign key.

Syntax

CREATE TABLE TableName1
(
Column1DataType1,
Column2DataType2,
......
FOREIGN KEY (ColumnX, ColumnY) REFERENCES TableName2
)

Example

In this example, we would like to create a table “LoanRecord” with a primary key “LoanRecID” and two foreign keys “StdID” and “BookID”that references tables “Student” and “Book” respectively by using the following statement.

CREATE TABLE LoanRecord
(
LoanRecID char(8) NOT NULL,
StdID char(7) NOT NULL,
BookID char(8) NOT NULL,
Dateofborrow date,
Status char(1),
PRIMARY KEY (LoanRecID),
FOREIGN KEY (StdID) REFERENCES Student,
FOREIGN KEY (BOOKID) REFERENCES Book
)
  1. Creating and Deleting Data View

Create a data view

With the use of the CREATE VIEW statement, users may create a special view on one or more tables (or views) in the database in form of a new “virtual” table. The data view is created with the use of an associated SELECT statement. Most SQL statements that apply to a database table can also be applied to a data view.

Syntax

CREATE VIEW ViewName (Column1, Column2…)
AS Select-Statement;

Example

CREATE VIEW BookOnLoan_n_Borrower_View (StdID, Name, BookID)
AS SELECT Student.StdID, Name, BookID
FROM Student, LoanRecord WHERE Student.StdID = LoanRecord.StdID;

Delete a data view

A data view can be deleted with the use of the DROP VIEW statement.

Syntax

DROP VIEW ViewName;
  1. Create/DropTable Index

Indexing is commonly used to enhance the performance of a database system. With the CREATE INDEX statement, we can create indexing structures (which is a pre-processed list) for database tables so as to provide an efficient access path to various table rows. When running a query, database will examineanyrelevant index for a more efficient data access instead of traversing the entire table. To delete an index, the DROP INDEX statement can be used.

Syntax (CREATE INDEX)

CREATE INDEX IndexNameON TableName(Column1,Column2,...)

Syntax (DROP INDEX)

DROP INDEX IndexNameON TableName

Example (CREATE INDEX)

In the following, we create an index for the class and student number combination in the Student table as the two fields are often accessedby various queries.

CREATE INDEX ind_class_stdID
ON Student (class, StdID)

Example (DROP INDEX)

The following delete the index that was created in the previous example.

DROP INDEX ind_class_stdIDON Student