Chapter 6
Introduction to SQL:
Structured Query Language
Objectives
Define terms
n Define a database using SQL data definition
language
n Write single table queries using SQL
n Establish referential integrity using SQL
n Discuss SQL:1999 and SQL:200n standards
n
Figure 6-1
A simplified schematic of a typical SQL environment, as
described by the SQL: 200n standard
SQL Environment
n
Data Definition Language (DDL)
- Commands that define a database, including creating, altering,
and dropping tables and establishing constraints
- CREATE / DROP / ALTER,
Data Manipulation Language (DML)
- Commands that maintain and query a database
- INSERT, UPDATE, DELETE, SELECT,
Data Control Language (DCL)
- Commands that control a database, including administering
privileges and committing data
- GRANT, ADD, REVOKE
SQL Database Definition
Data Definition Language (DDL)
n Major CREATE statements:
n
CREATE SCHEMAdefines a portion of the
database owned by a particular user
CREATE TABLEdefines a new table and its
columns
CREATE VIEWdefines a logical table from
one or more tables or views
DDL: Table Creation
General syntax for CREATE TABLE
used in data definition language
Steps in table creation:
1. Identify data types for
attributes
2. Identify columns that can
and cannot be null
CREATE TABLE table_name (
field type constraints,
field2 type2,
CONSTRAINT name ...,
...
);
3. Identify columns that
must be unique
(candidate keys)
CREATE TABLE Book (
ISBN CHAR(9)
NOT NULL,
Title VARCHAR(20) UNIQUE,
Pages INTEGER,
CONSTRAINT ISBN PRIMARY KEY
);
6. Identify constraints on
columns (domain
specifications)
4. Identify primary key
5. Determine default values
7. Identify foreign keys
SQL Data Types
The following slides create tables for
this enterprise data model
(from Chapter 1, Figure 1-3)
Figure 6-6 SQL database definition commands for Pine Valley Furniture
Company (Oracle 11g)
Overall table
definitions
1. Defining attributes and their data types
This is Oracle syntax.
In MySQL
NUMBER should be replaced by NUMERIC
VARCHAR2 should be replaced by VARCHAR
2. Non-nullable specification
Primary keys
can never have
NULL values
4. Identifying Primary Key
Non-nullable specifications
Primary key
Some primary keys are composite
Controlling the Values in Attributes
5. Default value
6. Domain constraint
7. Identifying foreign keys and establishing relationships
Primary key of
parent table
Foreign key of dependent table
STUDENT'(StudentID,'StudentName)'
'
'
QUALIFIED'(FacultyID,'CourseID,'DateQualified)'
'
StudentID)
StudentName'
FacultyID)
CourseID)
DateQualified'
38214'
54907'
66324'
70542'
'
Letersky'
Altvater'
Aiken'
Marra'
'
2143'
2143'
3467'
3467'
4756'
4756'
'
ISM'3112'
ISM'3113'
ISM'4212'
ISM'4930'
ISM'3113'
ISM'3112'
'
9/1988'
9/1988'
9/1995'
9/1996'
9/1991'
9/1991'
'
FACULTY'(FacultyID,'FacultyName)'
'
'
SECTION'(SectionNo,'Semester,'CourseID)'
'
FacultyID)
FacultyName'
SectionNo)
Semester)
CourseID'
2143'
3467'
4756'
'
Birkin'
Berndt'
Collins'
'
2712'
2713'
2714'
2715'
'
IR2008'
IR2008'
IR2008'
IR2008'
'
ISM'3113'
ISM'3113'
ISM'4212'
ISM'4930'
'
COURSE'(CourseID,'CourseName)'
'
'
REGISTRATION'(StudentID,'SectionNo,'Semester)'
'
CourseID)
CourseName'
StudentID)
SectionNo)
Semester)
ISM'3113'
ISM'3112'
ISM'4212'
ISM'4930'
'
Syst'Analysis'
Syst'Design'
Database'
Networking'
'
38214'
54907'
54907'
66324'
RRR'
2714'
2714'
2715'
2713'
IR2008'
IR2008'
IR2008'
IR2008'
'
'
Practice: Exercise #1
Write a database description for each of the relations shown,
using SQL DDL. Assume the following attribute data types:
StudentID (integer, primary key)
StudentName (max 25 characters)
FacultyID (integer, primary key)
to
FacultyName (max 25 characters)
e in
d
o
c
l
CourseID (8 characters, primary key)
Q L eg.sq
S
r
tR
ou
CourseName (max 15 characters)
e y uden
v
a
S le St
DateQualified (date)
a fi
SectionNo (integer, primary key)
Semester (max 7 characters)
Using MySQL
n
n
n
n
n
n
n
Available on csdb.csc.villanova.edu
Invoke with
mysql u username D database -p
SHOW DATABASES;
SHOW TABLES;
DESCRIBE name_T; (or SHOW COLUMNS FROM name_T;)
SOURCE script.sql
\! shell_command
Conventions
n
n
n
n
n
-- comments until end of line
/* can also use C-style comments */
SQL is case insensitive (except for data)
But we usually type reserved words in ALL CAPS
Use single quotes for character constants
Changing Tables
n
ALTER TABLE statement allows you to
change column specifications:
Table Actions:
Example (adding a new column with a default value):
Should be single quotes!
Removing Tables
n
DROP TABLE statement allows you to
remove tables from your schema:
n
DROP TABLE CUSTOMER_T
Practice: Exercise #4
Write SQL data definition commands for each of the following:
1. Add an attribute Class to the Student table, then drop it
2. Create a new Dummy table, then remove it
3. Change the FacultyName field from 25 characters to 40
characters
Insert
n
INSERT INTO table (fields)
VALUES (values)
Insert Statement
n
Adds one or more rows to a table
Inserting into a table
Better practice is to list the fields that actually get data
Inserting from another table
STUDENT'(StudentID,'StudentName)'
'
'
StudentID)
StudentName'
38214'
54907'
66324'
70542'
'
Letersky'
Altvater'
Aiken'
Marra'
'
QUALIFIED'(FacultyID,'CourseID,'DateQualified)'
'
FacultyID)
'
ISM'3112'
ISM'3113'
ISM'4212'
ISM'4930'
ISM'3113'
ISM'3112'
'
9/1988'
9/1988'
9/1995'
9/1996'
9/1991'
9/1991'
'
FacultyID)
FacultyName'
SectionNo)
Semester)
CourseID'
2143'
3467'
4756'
'
Birkin'
Berndt'
Collins'
'
2712'
2713'
2714'
2715'
'
IR2008'
IR2008'
IR2008'
IR2008'
'
ISM'3113'
ISM'3113'
ISM'4212'
ISM'4930'
'
COURSE'(CourseID,'CourseName)'
'
'
DateQualified'
l
sq
g. ith
e
R sw
nt
de table
u
St our
end late y
t
x
E opu
to p data.
this
SECTION'(SectionNo,'Semester,'CourseID)'
'
FACULTY'(FacultyID,'FacultyName)'
'
CourseID)
2143'
2143'
3467'
3467'
4756'
4756'
'
REGISTRATION'(StudentID,'SectionNo,'Semester)'
'
CourseID)
CourseName'
StudentID)
SectionNo)
Semester)
ISM'3113'
ISM'3112'
ISM'4212'
ISM'4930'
'
Syst'Analysis'
Syst'Design'
Database'
Networking'
'
38214'
54907'
54907'
66324'
RRR'
2714'
2714'
2715'
2713'
IR2008'
IR2008'
IR2008'
IR2008'
'
'
Creating Tables with Identity Columns
Introduced with SQL:200n
Inserting into a table does not require explicit customer ID entry:
INSERT INTO CUSTOMER_T VALUES ( Contemporary Casuals,
1355 S. Himes Blvd., Gainesville, FL, 32601);
Note: In mysql only the primary key can be auto-incremented:
ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT
Delete Statement
n
Removes rows from a table:
DELETE FROM table
WHERE conditions;
n
n
If no conditions, delete all data
Does NOT delete the meta-data,
use DROP TABLE for that
Delete Statement
n
Delete certain rows
n
DELETE FROM Customer_T WHERE
CustomerState = CA;
Delete all rows
n
DELETE FROM CUSTOMER_T;
Update Statement
n
Modifies data in existing rows:
nUPDATE
table
SET field = value
WHERE conditions
Update Statement
n
Can use the field to modify in an expression:
UPDATE Student
SET Age = Age+1
WHERE StudentID = 1
Do this:
-Add an Age field to the Student_T table, with a
default value of 18
-Increment the Age of the student with ID 54907
Practice: Exercise #5
Write SQL commands for the following:
1. Create two different forms of the INSERT command to add a
student with a student ID of 65798 and last name Lopez to
the Student table.
2. Now write a command that will remove Lopez from the
Student table.
3. Create an SQL command that will modify the name of course
ISM 4212 from Database to Introduction to Relational
Databases.
Data Integrity Controls
n
Referential integrity constraint that ensures that
foreign key values of a table must match primary
key values of a related table in 1:M relationships
Restricting:
Deletes of primary records
n Updates of primary records
n Inserts of dependent records
n
Data Integrity Controls
Write CREATE TABLE statements:
What if a major is deleted from the Major table?
What should happen to the rows pointing to that major?
Data Integrity Controls
CREATE TABLE Student(
Id INTEGER PRIMARY_KEY,
Name VARCHAR(20) NOT NULL,
MajorId CHAR(3) REFERENCES Major(Id) ON UPDATE RESTRICT
);
Options: ON [UPDATE | DELETE]
RESTRICT
/*
CASCADE
/*
SET NULL
/*
SET DEFAULT /*
STUDENT'(StudentID,'StudentName)'
'
'
QUALIFIED'(FacultyID,'CourseID,'DateQualified)'
'
StudentID)
StudentName'
FacultyID)
CourseID)
DateQualified'
38214'
54907'
66324'
70542'
'
Letersky'
Altvater'
Aiken'
Marra'
'
2143'
2143'
3467'
3467'
4756'
4756'
'
ISM'3112'
ISM'3113'
ISM'4212'
ISM'4930'
ISM'3113'
ISM'3112'
'
9/1988'
9/1988'
9/1995'
9/1996'
9/1991'
9/1991'
'
FACULTY'(FacultyID,'FacultyName)'
'
'
a
Dat
FacultyID)
FacultyName'
SectionNo)
Semester)
CourseID'
2143'
3467'
4756'
'
Birkin'
Berndt'
Collins'
'
2712'
2713'
2714'
2715'
'
IR2008'
IR2008'
IR2008'
IR2008'
'
ISM'3113'
ISM'3113'
ISM'4212'
ISM'4930'
COURSE'(CourseID,'CourseName)'
'
s
trol
SECTION'(SectionNo,'Semester,'CourseID)'
'
'
'
do not allow */
propagate change */
Set MajorId to NULL */
Set MajorId to its default value */
REGISTRATION'(StudentID,'SectionNo,'Semester)'
'
CourseID)
CourseName'
StudentID)
SectionNo)
Semester)
ISM'3113'
ISM'3112'
ISM'4212'
ISM'4930'
'
Syst'Analysis'
Syst'Design'
Database'
Networking'
'
38214'
54907'
54907'
66324'
RRR'
2714'
2714'
2715'
2713'
IR2008'
IR2008'
IR2008'
IR2008'
'
'
on
yC
t
i
r
g
Inte
Basic SELECT
Basic SELECT
n
Used for queries on single or multiple tables.
SELECT [DISTINCT] attribute-list
FROM table-list
WHERE conditions
SELECT : the columns (and expressions) to be returned
from the query
FROM: indicate the table(s) or view(s) from which
data will be obtained
WHERE: indicate the conditions under which a row
will be included in the result
Basic SELECT
n
Used for queries on single or multiple tables.
SELECT [DISTINCT] attribute-list
FROM table-list
WHERE conditions
Conditions: comparisons, combined
with AND, OR, NOT
DISTINCT is an optional keyword
indicating that the answer should not
contain duplicates. The default is that
duplicates are not eliminated!
Fragment of Figure 6-10: SQL statement processing order
SELECT Example
n
Find products with standard price less than $275
Table 6-3: Comparison Operators in SQL
SELECT Example Using Alias
n
Alias is an alternative column or table name
SELECT Cust.CustomerName AS Name,
Cust.CustomerAddress
FROM Customer_T AS Cust
WHERE CustomerName = Home Furnishings;
Practice: Exercise #6
Write SQL queries to answer the following questions:
1. Which students have an ID number that is less than 50000?
2. What is the name of the faculty member whose ID is 4756?
Summary
n
DDL
- CREATE TABLE
- DROP TABLE
- ALTER TABLE
DML
- INSERT INTO
- UPDATE
- DELETE FROM
- SELECT
