IT420: Database Management and Organization

1
IT420 Database Management and Organization

• SQL Structured Query Language
• 25 January 2006
• Adina Crainiceanu
• www.cs.usna.edu/adina

2
Goals

• SQL

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.
• The key precise semantics for relational queries
• Allows the optimizer to extensively re-order
  operations, and still ensure that the answer does
  not change

4
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)

5
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

6
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

7
SQL DDL Statements

• CREATE
• ALTER
• DROP

8
CREATE TABLE

• CREATE TABLE statement is used for creating
  relations
• Each column is described with three parts
• column name
• data type
• optional constraints

9
CREATE TABLE Example
CREATE TABLE Students (StudentNumber
integer, StudentLastName char(18) NOT
NULL, StudentFirstName char(18) NOT NULL, Email
char(50), PhoneNumber char(18) )
10
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

11
Constraints Examples
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) )
12
Default Values and Data Constraints

• Students table
• Default value for PhoneNumber 410-123-4567
• Email like _at_usna.edu

CREATE TABLE Students (StudentNumber integer,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL, Email
char(50), PhoneNumber char(18) DEFAULT
410-123-4567, PRIMARY KEY (StudentNumber),
UNIQUE(Email), CHECK (Email LIKE
_at_usna.edu) )
Syntax depends on DBMS!!!
13
FOREIGN KEY Constraints
14
FOREIGN KEY Constraints
CREATE TABLE Departments (DepartmentName
char(18), Phone char(18) NOT NULL,
Building char(18), Room integer, PRIMARY
KEY (DepartmentName) )
15
FOREIGN KEY Constraints
CREATE TABLE Students (StudentNumber integer,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL, Email
char(50), PhoneNumber char(18),
MajorDepartmentName char(18), PRIMARY KEY
(StudentNumber), UNIQUE(Email), FOREIGN KEY
(MajorDepartmentName) REFERENCES Departments
(DepartmentName) ON DELETE SET NULL ON UPDATE
CASCADE )

• SQL/92 and SQL1999 support all 4 options on
  deletes and updates.
• Default is NO ACTION (delete/update is rejected)
• CASCADE (also delete all rows that refer to
  deleted row)
• SET NULL / SET DEFAULT (sets foreign key value of
  referencing row)

16
Implementing Cardinalities
17
ALTER Statement

• ALTER statement changes
• table structure,
• properties, or
• constraints
• after the table has been created

18
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

19
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

20
Removing Tables

• SQL DROP TABLE
• DROP TABLE Departments
• If there are constraints
• ALTER TABLE Students
• DROP CONSTRAINT DepartmentFK
• DROP TABLE Departments

21
Class Exercise
22
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

23
INSERT Statement

• INSERT command
• INSERT INTO Students (StudentNumber,
  StudentLastName, StudentFirstName)
• VALUES (190, Smith', John)
• INSERT INTO Students VALUES(190, Smith, John,
  jsmith_at_usna.edu, 410-431-3456)
• Bulk INSERT
• INSERT INTO Students (StudentNumber,
  StudentLastName, StudentFirstName, Email,
  PhoneNumber)
• SELECT
• FROM Second_Class_Students

24
UPDATE Statement

• UPDATE command
• UPDATE Students
• SET PhoneNumber 410-123-4567
• WHERE StudentNumber 673
• BULK UPDATE command
• UPDATE Students
• SET PhoneNumber 410-123-4567
• WHERE StudentLAstName Doe

25
DELETE Statement

• DELETE command
• DELETE FROM Students
• WHERE StudentNumber 190
• If you omit the WHERE clause, you will delete
  every row in the table!

26
The SQL SELECT Statement

• The fundamental framework for SQL query statement
  is the SQL SELECT statement
• SELECT ColumnName(s)
• FROM TableName(s)
• WHERE Conditions

27
Specific Columns on One Table

• SELECT StudentNumber,StudentLastName
• FROM Students

28
Specify Column Order

• SELECT StudentLastName,StudentNumber
• FROM Students

29
The DISTINCT Keyword

• SELECT StudentLastName
• FROM Students

SELECT DISTINCT StudentLastName FROM Students
30
Selecting All Columns The Asterisk () Keyword

• SELECT
• FROM Students

31
Specific Rows from One Table

• SELECT
• FROM Students
• WHERE StudentLastName Doe'
• NOTE SQL wants a plain ASCII single quote '
  NOT !

32
Specific Columns and Rows from One Table

• SELECT StudentNumber,
• StudentLastName,
• StudentFirstName
• FROM Students
• WHERE PhoneNumber NOT NULL