IT420: Database Management and
Organization
SQL: Structured Query Language
25 January 2006
Adina Crăiniceanu
www.cs.usna.edu/~adina
1
Goals
 SQL
2
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.
 The key: precise semantics for relational
queries
 Allows the optimizer to extensively re-order
operations, and still ensure that the answer
does not change
3
SQL: The Structured Query
Language
 Developed by IBM (system R) in the
1970s
 Need for a standard since it is used by
many vendors
 Standards:




SQL-86
SQL-89 (minor revision)
SQL-92 (major revision)
SQL-99 (major extensions, current standard)
4
SQL as a Data Sublanguage
 SQL is not a full featured programming
language as are C, C#, and Java
 SQL is a data sublanguage
 Create and process database data
 SQL is ubiquitous in enterprise-class
DBMS products
 SQL programming is a critical skill
5
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
6
SQL DDL Statements
 CREATE
 ALTER
 DROP
7
CREATE TABLE
 CREATE TABLE statement is used for creating
relations
 Each column is described with three parts:
 column name
 data type
 optional constraints
8
CREATE TABLE Example
STUDENTS
StudentNumber: INTEGER NOT NULL
StudentLastName: CHAR(18) NOT NULL
StudentFirstName: CHAR(18) NOT NULL
Email: CHAR(50) NULL
PhoneNumber: CHAR(18) NULL
Student Student
Number LastName
CREATE TABLE Students
(StudentNumber integer,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL,
Email char(50),
PhoneNumber char(18)
)
Student
FirstName
Email
PhoneNumber
410-431-3456
190
Smith
John
[email protected]
673
Doe
Jane
[email protected]
312
Red
Bob
[email protected]
443-451-7865
9
Constraints
 Constraints can be defined within the CREATE
TABLE statement, or they can be added to the
table after it is created using the ALTER table
statement
 Five types of constraints:





NULL/NOT NULL
PRIMARY KEY may not have null values
UNIQUE may have null values
CHECK
FOREIGN KEY
10
Constraints Examples
STUDENTS
StudentNumber: INTEGER NOT NULL
CREATE TABLE Students
(StudentNumber integer,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL,
Email char(50),
PhoneNumber char(18),
PRIMARY KEY (StudentNumber),
UNIQUE (Email)
)
StudentLastName: CHAR(18) NOT NULL
StudentFirstName: CHAR(18) NOT NULL
Email: CHAR(50) NULL (AK1.1)
PhoneNumber: CHAR(18) NULL
Student Student
Number LastName
Student
FirstName
Email
PhoneNumber
410-431-3456
190
Smith
John
[email protected]
673
Doe
Jane
[email protected]
312
Doe
Bob
[email protected]
443-451-7865
11
Default Values and Data Constraints
 Students table
 Default value for
PhoneNumber:
410-123-4567
 Email like
[email protected]
CREATE TABLE Students
(StudentNumber integer,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL,
Email char(50),
PhoneNumber char(18) DEFAULT “410123-4567”,
PRIMARY KEY (StudentNumber),
UNIQUE(Email),
CHECK (Email LIKE [email protected])
)
Syntax depends on DBMS!!!
12
FOREIGN KEY Constraints
DEPARTMENTS
DepartmentName
Phone
Building
Room
Mathematics
410-293-4573
Michelson Hall
308
History
410-293-2255
Sampson Hall
120
Computer Science
410-293-6800
Michelson Hall
340
DepartmentName: char(18)
Phone: char(18)
Building: char(18)
Room: integer
D:SN
U:C
Majors
Student Student
Student
Number LastName FirstName
Email
PhoneNumber
410-431-3456
190
Smith
John
[email protected]
673
Doe
Jane
[email protected]
312
Doe
Bob
[email protected]
MajorDepartmentName
Computer Science
443-451-7865
Mathematics
I:SN
U:SN
STUDENTS
StudentNumber: integer
StudentLastName: char(18)
StudentFirstName: char(18)
Email: varchar(50)
PhoneNumber: char(18)
DepartmentName: char(18) (FK)
13
FOREIGN KEY Constraints
DEPARTMENTS
DepartmentName
Phone
Building
Room
Mathematics
410-293-4573
Michelson Hall
308
History
410-293-2255
Sampson Hall
120
Computer Science
410-293-6800
Michelson Hall
340
DepartmentName: char(18)
Phone: char(18)
Building: char(18)
Room: integer
D:SN
U:C
Majors
Student Student
Student
Number LastName FirstName
Email
PhoneNumber
410-431-3456
190
Smith
John
[email protected]
673
Doe
Jane
[email protected]
312
Doe
Bob
[email protected]
MajorDepartmentName
Computer Science
443-451-7865
Mathematics
I:SN
U:SN
STUDENTS
StudentNumber: integer
StudentLastName: char(18)
StudentFirstName: char(18)
Email: varchar(50)
PhoneNumber: char(18)
DepartmentName: char(18) (FK)
CREATE TABLE Departments
(DepartmentName char(18),
Phone char(18) NOT NULL,
Building char(18),
Room integer,
PRIMARY KEY (DepartmentName)
)
14
FOREIGN KEY Constraints
 SQL/92 and SQL:1999
support all 4 options on
deletes and updates.
CREATE TABLE Students
(StudentNumber integer,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL,
 Default is NO ACTION
Email char(50),
(delete/update is
PhoneNumber char(18),
rejected)
MajorDepartmentName char(18),
PRIMARY KEY (StudentNumber),
 CASCADE (also delete
UNIQUE(Email),
all rows that refer to
FOREIGN KEY (MajorDepartmentName)
deleted row)
REFERENCES Departments (DepartmentName)
 SET NULL / SET
DEFAULT (sets foreign ON DELETE SET NULL
key value of referencing ON UPDATE CASCADE
)
row)
15
Implementing Cardinalities
16
ALTER Statement
 ALTER statement changes
 table structure,
 properties, or
 constraints
after the table has been created
17
Adding and Dropping Columns
 The following statement will add a column
named BirthDate to the Students table:
ALTER TABLE Students ADD COLUMN BirthDate Datetime
NULL;
 You can drop an existing column with the
statement:
ALTER TABLE Students DROP COLUMN BirthDate;
18
Adding and Dropping Constraints
 ALTER can be used to add a constraint as
follows:
ALTER TABLE Student ADD CONSTRAINT DepartmentFK
FOREIGN KEY (MajorDepartmentName)
REFERENCES Departments (DepartmentName)
ON DELETE NO ACTION
ON UPDATE CASCADE
 ALTER can be used to drop a constraint:
ALTER TABLE Student DROP CONSTRAINT DepartmentFK;
19
Removing Tables
 SQL DROP TABLE:
DROP TABLE Departments;
 If there are constraints:
ALTER TABLE Students
DROP CONSTRAINT DepartmentFK;
DROP TABLE Departments;
20
Class Exercise
21
SQL DDL and DML
 Data definition language (DDL)
statements
 Used for creating and modifying tables, views, and
other structures
 CREATE, ALTER, DROP
 Data manipulation language (DML)
statements.
 Used for queries and data modification
 INSERT, DELETE, UPDATE, SELECT
22
INSERT Statement
 INSERT command:
INSERT INTO Students (StudentNumber, StudentLastName,
StudentFirstName)
VALUES (190, ‘Smith', ‘John’);
INSERT INTO Students VALUES(190, ‘Smith’, ‘John’,
[email protected], ‘410-431-3456’)
 Bulk INSERT:
INSERT INTO Students (StudentNumber, StudentLastName,
StudentFirstName, Email, PhoneNumber)
SELECT *
FROM Second_Class_Students;
23
UPDATE Statement
 UPDATE command:
UPDATE
SET
WHERE
Students
PhoneNumber = ‘410-123-4567’
StudentNumber = 673;
 BULK UPDATE command:
UPDATE
SET
WHERE
Students
PhoneNumber = ‘410-123-4567’
StudentLAstName = ‘Doe’;
Student Student
Number LastName
Student
FirstName
Email
PhoneNumber
410-431-3456
190
Smith
John
[email protected]
673
Doe
Jane
[email protected]
312
Doe
Bob
[email protected]
443-451-7865
24
DELETE Statement
 DELETE command:
DELETE FROM Students
WHERE StudentNumber = 190;
 If you omit the WHERE clause, you will
delete every row in the table!
25
The SQL SELECT Statement
 The fundamental framework for SQL query
statement is the SQL SELECT statement:
 SELECT
 FROM
 WHERE
{ColumnName(s)}
{TableName(s)}
{Conditions}
26
Specific Columns on One Table
SELECT
StudentNumber,StudentLastName
FROM Students;
StudentNumber
StudentLastName
190
Smith
673
Doe
312
Doe
27
Specify Column Order
SELECT StudentLastName,StudentNumber
FROM Students;
StudentLastName StudentNumber
Smith
190
Doe
673
Doe
312
28
The DISTINCT Keyword
SELECT
SELECT DISTINCT
StudentLastName
StudentLastName
FROM Students;
FROM Students;
StudentLastName
StudentLastName
Smith
Doe
Smith
Doe
Doe
29
Selecting All Columns:
The Asterisk (*) Keyword
SELECT
*
FROM Students;
Student Student
Number LastName
Student
FirstName
Email
PhoneNumber
410-431-3456
190
Smith
John
[email protected]
673
Doe
Jane
[email protected]
312
Doe
Bob
[email protected]
443-451-7865
30
Specific Rows from One Table
SELECT
*
FROM
Students
WHERE StudentLastName = ‘Doe';
NOTE: SQL wants a plain ASCII single quote: ' NOT ‘ !
Student Student
Number LastName
Student
FirstName
Email
673
Doe
Jane
[email protected]
312
Doe
Bob
[email protected]
PhoneNumber
443-451-7865
31
Specific Columns and Rows from
One Table
SELECT
StudentNumber,
StudentLastName,
StudentFirstName
FROM
Students
WHERE
PhoneNumber NOT NULL;
Student Student
Number LastName
Student
FirstName
190
Smith
John
312
Doe
Bob
32
Descargar

Kroenke-DBP-e10-PPT-Chapter06