1
1
IT420: Database Management and
Organization
SQL: Structured Query
Language
(Chapter 7)
2
Goals
SQL: Data Definition Language
CREATE
ALTER
DROP
SQL: Data Manipulation Language
INSERT
DELETE
UPDATE
SELECT
3
Relational Query Languages
A major strength of the relational model:
supports simple, powerful querying of data
Queries can be written intuitively, and the
DBMS is responsible for efficient
evaluation.
4
SQL DDL and DML
SQL statements can be divided into two
categories:
Data definition language (DDL) statements
Used for creating and modifying tables, views, and
other structures
CREATE, DROP, ALTER
Data manipulation language (DML)
statements.
Used for queries and data modification
INSERT, DELETE, UPDATE, SELECT
5
Creating Tables
CREATE TABLE table_name(
column_name1 column_type1 [constraints1],
…,
[[CONSTRAINT constraint_name] table_constra int]
)
Table constraints:
NULL/NOT NULL
PRIMARY KEY (columns)
UNIQUE (columns)
CHECK (conditions)
FOREIGN KEY (local_columns) REFERENC ESf oreign_table
(foreign_columns) [ON DELETE action_d ON UPDATE act ion_u]
Specify surrogate key in SQL Server:
column_name int_type IDENTITY (seed, increment)
6
CREATE TABLE Example
CREATE TABLE Students
(StudentNumber integer NOT NULL,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL,
Email char(50),
PhoneNumber char(18),
MajorDepartmentName char(18),
CONSTRAINT PK_Students PRIMARY KEY (StudentNumber),
CONSTRAINT U_Email UNIQUE (Email),
CONSTRAINT FK_Dept FOREIGN KEY(MajorDe partmentName)
REFERENCES DEPARTMENTS(Dep artmentName)
ON DELETE NO ACTION ON UPDATE CASCAD E
)
