Transcript Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems
SQL: Creating and Processing RDBs
Prof. Name
Position
University Name
[email protected]
(123) 456-7890
1
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
2
Introduction

Structured Query Language (SQL) is a complete and widely used
relational database (RDB) query language
– Originally developed by IBM in the late 1970s
– Currently the industry standard
– Not a complete programming language
 Like Visual Basic, C/C++, or Java
– SQL statements may be embedded into general purpose programming
languages to create database applications

Used for management of database objects:
– Creation
– Manipulation
– Control
3
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
4
Types of SQL Statements

We divide SQL statements into four categories based on the database
operations they perform:
– Data Definition Language (DDL) statements
 Define data by creating, altering, and destroying database objects
– Data Manipulation Language (DML) statements
 Manipulate (i.e., select, insert, update, and delete) data in the database tables
– Data Control Language (DCL) statements
 Configure and control the database permissions, roles, and referential integrity
controls
– Database Stored Procedure Language (DSPL) statements
 Used for triggers, event handlers, and stored procedures in transaction
processing
5
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
6
The Data Definition Language (DDL)

Data Definition Language (DDL) statements define the structure of
the data in a database
– Create, alter, and drop database objects like Tables, relationships, keys,
and indexes.
– DDL statements can perform visual steps performed in Chapter 6 and 7 to
create tables, keys, indexes, and relationships.
– We focus on CREATE, DROP, and ALTER DDL statements for database
tables.
7
Review of Database Terminology

Tables are used to store and relate data
– Two-dimensional grid
 Columns: data fields
 Rows: records

A primary key is an attribute or minimal set of attributes that uniquely
identifies each record within the table

A foreign key is an attribute or a set of attributes in a table that acts as
a primary key to the same or another table in the database

A database index is the ordered list of indexing attributes with a
pointer to table rows where the actual record can be found
8
The CREATE Statement

CREATE statements create database objects
– Define primary and foreign keys
– Create an index on a table attribute

Syntax:
CREATE TABLE [Table Name]
({<Column name> <Data type> <Constraint>}, {}…)
CONSTRAINT
{<Key Name> <key Type> (Field1, Field2…)}, {}…;
9
CREATE Statement - Example

Query:
– “Create a new table, tblDepartment, to store department information. Store
the department identification number, name, address, and phone number
for all the departments in the university database.”

SQL Statement:
CREATE TABLE tblDepartment
(DeptID
VARCHAR2 (10)
NOT NULL,
Name
VARCHAR2 (50)
,
Address
VARCHAR2 (200)
,
Phone
NUMBER (30)
DEFAULT 0,
CollegeID VARCHAR2 (3)
CONSTRAINT
DeptPK
)
PRIMARY KEY (DeptID)
DeptFK FOREIGN KEY (CollegeID) REFERENCES tblCollege (CollegeID);
10
SQL Field Constraints

The following tables lists a few column constraints that can be used
with the CREATE TABLE SQL statement:
Constraints
Meaning
MS Access Equivalent
NOT NULL
Cannot be null.
Required.
NULL
Set to null.
Allow Zero Length.
UNIQUE
Should be unique.
-
DEFAULT VALUE
Set the default value.
Default Value.
CHECK (Condition)
Check for the condition.
Validation Rule.
11
CREATE Index - Example

Query:
– “Create an index (named tblDeptAddressIndx) on the Address field of the
department table.”

SQL Statement:
CREATE INDEX
tblDeptAddressIndx
ON
tblDepartment (Address);
12
The DROP Statement

DROP statements removes database objects
– Table, Index

Syntax:
DROP TABLE [Table Name];
DROP INDEX [Index Name];
13
DROP Statement - Examples

Query 1:
– “Remove the department table, its data, columns, and indexes.”

SQL Statement 1:
DROP TABLE tblDepartment;

Query 2:
– “Remove only the index on the Address field of the department table.”

SQL Statement 2:
DROP INDEX
tblDeptAddressIndx;
14
The ALTER Statement

ALTER statements alter existing database objects
– Addition or deletion of columns or constraints
– Revision of data types and field constraints


Add Syntax:
ALTER TABLE
[Table Name]
ADD COLUMN
[<Column name> <Data type> <Constraint>]
ADD CONSTRAINT
[<Key Name> <key Type> (Field1, Field2…)];
Drop Syntax:
ALTER TABLE
[Table Name]
DROP COLUMN
[Column name]
DROP CONSTRAINT
[Key Name];
15
ALTER Statement – Example 1

Query:
– “Add a field for the department chairperson to the department table. Make
this field a foreign key in the faculty table.”

SQL Statement:
ALTER TABLE
tblDepartment
ADD COLUMN
Chair
NUMBER (10)
ADD CONSTRAINT DeptFK2 FOREIGN KEY(Chair) REFERENCES tblFaculty(FacultyID);
16
ALTER Statement – Example 2

Query:
– “Drop the Chair column and DeptFK2 constraint from the department
table.”

SQL Statement:
ALTER TABLE
tblDepartment
DROP COLUMN
Chair
DROP CONSTRAINT
DeptFK2;
17
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
18
The Data Manipulation Language (DML)

Data Manipulation Language (DML) statements involve selection,
insertion, update, and deletion operations
– The core and most frequently used SQL statements
– The manipulation involves selection, insertion, updates, and deletion
operations and the SELECT, INSERT, UPDATE, and DELETE statements
are used to perform these operations, respectively
– All DML statements have equivalent Access query types
19
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
20
The SELECT Statement

SELECT statements are by far the most frequently used SQL
statements
– Used for a wide variety of purposes

Structure:
SELECT
Mandatory
FROM
WHERE
GROUP BY
Optional, but must appear in this order
HAVING
ORDER BY
21
Projection: The SELECT Clause

The basic SELECT statement presented below projects the selected
table columns as a query output
– SELECT clause
 Specify the columns of query output
– FROM clause
 List the tables involved in the query design to select columns from

Syntax:
SELECT [Field name (s)]
FROM
[Table name];
22
Projection: The SELECT Clause - Examples

Query 1:
– “Project the student name, email, address, and phone fields from the
student table.”

SQL Statement 1:
SELECT Name, Email, Address, Phone
FROM

tblStudent;
Query 2:
– “Project all columns from the student table.”

SQL Statement 2:
SELECT * FROM tblStudent;
23
Hands-On Tutorial: Running
DML Statements in the Access Environment

How-to: Write, Execute, and Test SQL Statements in Access
1. Click the Create Query in Design View option in the Database Window to
open a new query in the Design View.
2. Click the Close button in the Show Table dialog box without adding any
tables to open the SQL View.
3. Write the SQL statement and click the Run (!) button on the toolbar to
execute the query.
Running DML SQL Statements in the Access Environment
24
Join: The FROM Clause

Join systematically draws data from multiple tables in order to provide
a unified view of data in individual tables
– A powerful and frequently used operation
– We join tables in SQL by specifying table names (separated by commas) in
the FROM clause

Syntax:
SELECT [Field name (s)]
FROM
[Table1, Table 2, Table 3...];
25
Join: The FROM Clause - Example

Query:
– “Select the contact information and department name for faculty
members.”

SQL Statement:
SELECT Name, Email, Address, Phone, Name
FROM
tblFaculty, tblDepartment;
26
Renaming Query Tables and Fields
Using the Keyword “AS”

To resolve conflicts, identify columns by prefixing tables’ names:
SELECT tblFaculty.Name, Email, Address, Phone, tblDepartment.Name
FROM

tblFaculty, tblDepartment;
If table names are long or complex, use the keyword AS to rename:
SELECT Fac.Name, Email, Address, Phone, Dept.Name
FROM
tblFaculty AS Fac, tblDepartment AS Dept;
27
Selection: The WHERE Clause

The WHERE clause is an optional clause of the SELECT statement
used to specify selection criteria
– Often used to specify the selection condition of an inner join
 Usually equate the primary key and foreign key values
– Can include following operators:
 Arithmetic Operators: =, <, <=, >, >=
 Clause Operators: BETWEEN, LIKE, IN
 Logical Operators: AND, OR, NOT

Syntax:
SELECT [Field Name (s)]
FROM
[Table Name (s)]
WHERE
[Criteria];
28
Selection: The WHERE Clause - Example

Query:
– “Select the contact information and department name of the faculty
members who joined the university after 1995 and whose salary exceeds
$75,000.”

SQL Statement:
SELECT Fac.Name, Email, Address, Phone, Dept.Name
FROM
tblFaculty AS Fac, tblDepartment AS Dept
WHERE
(Fac.DeptID = Dept.DeptID) AND (Salary > 75000)
AND (JoiningDate > #31/12/1995#);
Join Clause
Filter Clauses
29
Using the BETWEEN Clause Operator

The BETWEEN clause operator is used in conjunction with the logical
AND operator to specify a range of values

Query:
– “Select the contact information and department name of the faculty
members whose salary is between $65,000 and $75,000 and who joined
the university after 1995.”

SQL Statement:
SELECT Fac.Name, Email, Address, Phone, Dept.Name
FROM
tblFaculty AS Fac, tblDepartment AS Dept
WHERE
(Fac.DeptID = Dept.DeptID) AND (Salary BETWEEN 65000 AND 75000)
AND (JoiningDate > #31/12/1995#);
30
Using the LIKE Clause Operator

The LIKE clause is widely applied in search criteria
– Often used with the wildcard character: (*)

Query:
– “List the contact information for all the industrial engineering students who
have taken computer- or database-related courses.”

SQL Statement:
SELECT T1.Name, Email, Address, Phone
FROM tblStudent AS T1, tblTranscript AS T2, tblSection AS T3, tblCourse AS T4
WHERE (T1.StudentID = T2.StudentID) AND (T2.SectionID = T3.SectionID)
AND (T3.CourseID = T4.CourseID) AND (T1.DeptID = “ISE”)
AND ((T4.Name LIKE “*Computer*”) OR (T4.Name LIKE “*Database*”));
31
Using the IN Clause Operator

The IN clause is often used for nested or sub queries
– Specifies the inner query to look in

Query:
– “List the contact information for all the distinct industrial engineering
students who have taken computer- or database-related courses.”

SQL Statement:
SELECT Name, Email, Address, Phone
FROM
tblStudent
WHERE (DeptID = ISE)
AND (StudentID IN
(SELECT DISTINCT StudentID FROM tblTranscript WHERE SectionID IN
(SELECT SectionID FROM tblSection WHERE CourseID IN
(SELECT CourseID
FROM
tblCourse
WHERE (Name LIKE “*Computer*”) OR (Name LIKE “*Database*”)
) ) ) );
32
Using the Keyword DISTINCT

We can obtain distinct records using the keyword DISTINCT in the
SELECT clause
– Prevents duplicate records in output recordset

SQL Statement:
SELECT Name, Email, Address, Phone
FROM
tblStudent
WHERE (DeptID = ISE)
AND (StudentID IN
(SELECT DISTINCT StudentID FROM tblTranscript WHERE SectionID IN
(SELECT SectionID FROM tblSection WHERE CourseID IN
(SELECT CourseID
FROM
tblCourse
WHERE (Name LIKE “*Computer*”) OR (Name LIKE “*Database*”)
) ) ) );
33
Sorting: The ORDER BY Clause

We can use the ORDER BY clause to sort the output records based
on one or more attributes in ascending (ASC) or descending (DESC)
order

Syntax:
SELECT
[Field Name (s)]
FROM
[Table Name (s)]
WHERE
[Criteria]
ORDER BY [Field name (s)] <ASC, DESC>;
34
Sorting: The ORDER BY Clause - Example

Query:
– “Select the contact information and department name of the faculty
members whose salary is between $65,000 and $75,000. List the output,
first in ascending order of their joining date and then in descending order
of their salary.”

SQL Statement:
SELECT
Fac.Name, Email, Address, Phone, Dept.Name
FROM
tblFaculty AS Fac, tblDepartment AS Dept
WHERE
(Fac.DeptID = Dept.DeptID) AND (Salary BETWEEN 65000 AND 75000)
ORDER BY
JoiningDate ASC, Salary DESC;
35
Grouping: The GROUP BY Clause

A GROUP BY clause combines similar records and treats them as a
single unit or group
– For use with aggregate functions:
 MIN, MAX: Provides the minimum or maximum value
 SUM, AVG: Calculates the sum or average value
 COUNT: Finds the count of records
– Aggregate function syntax:
[Function Name] [Field name] AS <Rename>

Syntax:
SELECT
[Field Name (s)]
FROM
[Table Name (s)]
WHERE
[Criteria]
GROUP BY [Field name (s)];
36
Grouping: The GROUP BY Clause - Example

Query:
– “Count the number of students in each department in the department
table.”

SQL Statement:
SELECT
Dept.Name, COUNT (Std.StudentID) AS TotalStudents
FROM
tblStudent AS Std, tblDepartment AS Dept
WHERE
Std.DeptID = Dept.DeptID
GROUP BY Dept.Name;
37
Using the HAVING Clause

The HAVING clause is always used with the GROUP BY clause to
specify the filtering criteria for group values
– Similar to the WHERE clause

Syntax:
SELECT
[Field Name (s)]
FROM
[Table Name (s)]
WHERE
[Criteria]
GROUP BY [Field name (s)]
HAVING
[Criteria];
38
Using the HAVING Clause - Example

Query:
– “List the departments in the College of Engineering that have enrollments
of 100 or more students.”

SQL Statement:
SELECT
Dept.Name, COUNT (Std.StudentID) AS TotalStudents
FROM
tblStudent AS Std, tblDepartment AS Dept
WHERE
(Std.DeptID = Dept.DeptID) AND (Dept.CollegeID = “COE”)
GROUP BY Dept.Name
HAVING
(COUNT (Std.StudentID)) > 100;
39
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
40
The INSERT Statement

INSERT statements are DML statements that insert one or more
records into database tables

Syntax:
INSERT INTO [Table Name]
([Column1, Column2, Column3 ...])
VALUES
([Value1, Value2, Value3 ...]);
41
The INSERT Statement - Example

Query:
– “Insert information about the new department, “Digital Institute”, into the
department table.”

SQL Statement:
INSERT INTO tblDepartment
(DeptID, Name, Address, Phone, CollegeID)
VALUES
(“DGI”, “Digital Institute”, “303FAB UFL 32611”, 3921044, “CFA”);
42
The INSERT Statement – More Examples

Query 1:
– “Insert information about the new department, setting the Phone to NULL.”
INSERT INTO tblDepartment
(DeptID, Name, Phone, CollegeID)
VALUES

(“BIE”, “Bio Informatics Eng”, NULL, “COE”);
Query 2:
– “Insert information about multiple new departments from tblDownload.”
INSERT INTO tblDepartment
(DeptID, Name, Address, Phone, CollegeID)
SELECT DeptID1, Name1, Address1, Phone1, CollegeID1
FROM tblDownload WHERE CollegeID1 = “COE”;
43
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
44
The DELETE Statement

DELETE statements are DML statements that delete one or more
records from database tables
– Specify the deletion criteria using a WHERE clause

Syntax:
DELETE FROM [Table Name]
WHERE
[Criteria];
45
The DELETE Statement – Example 1

Query:
– “Delete all last year (senior) industrial engineering students.”

SQL Statement:
DELETE
*
FROM
tblStudent
WHERE
Class = “Senior” AND DeptID = “ISE”;
46
The DELETE Statement – Example 2

Query:
– “Delete all last year (senior) industrial engineering students.”
 Delete records in conjunction with a sub query

SQL Statement:
DELETE
*
FROM
tblStudent
WHERE
StudentID IN
(SELECT StudentID FROM tblStudent
WHERE Class = “Senior” AND DeptID = “ISE”);
47
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
48
The UPDATE Statement

UPDATE statements are DML statements that update one or more
rows of a table
– Specify the update criteria using a WHERE clause

Syntax:
UPDATE
[Table Name]
SET
[Column1 = Value1, Column2 = Value2 ...]
WHERE
[Criteria];
49
The UPDATE Statement – Example 1

Query:
– “Raise the salary field in the faculty table by 5% for all the faculty members
who joined the university before 1995 and who earn less than $70,000.”

SQL Statement:
UPDATE
tblFaculty
SET
Salary = Salary * 1.05
WHERE
JoiningDate < #1/1/1995# AND Salary < 70000;
50
The UPDATE Statement – Example 2

Query:
– “Raise the salary field in the faculty table by 5% for all the faculty members
who joined the university before 1995 and who earn less than $70,000.”
 Update records in conjunction with a sub query

SQL Statement:
UPDATE tblFaculty
SET
Salary = Salary * 1.05
WHERE
FacultyID IN
(SELECT FacultyID FROM tblFaculty
WHERE JoiningDate < #1/1/1995# AND Salary < 70000);
51
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
52
Summary

We divide SQL statements into four categories:
–
–
–
–

Data Definition Language (DDL) statements
Data Manipulation Language (DML) statements
Data Control Language (DCL) statements
Database Stored Procedure Language (DSPL) statements
The SELECT statement features two clauses:
– SELECT clause
 Specify the columns of query output
– FROM clause
 List the tables involved in the query design to select columns from
53
Summary (cont.)

We can use the ORDER BY clause to sort the output records based on
one or more attributes in ascending (ASC) or descending (DESC)
order

We obtain distinct records using the keyword DISTINCT in the
SELECT clause

A SELECT clause can have those fields without aggregate functions
grouped in the GROUP BY clause

A HAVING clause can have those fields that either appear in the
GROUP BY clause or are used with aggregate functions in the
SELECT clause
54
Summary (cont.)

CREATE, DROP, and ALTER statements are used to create, delete,
and update database objects such as tables and indexes

INSERT statements insert one or multiple records into database tables

DELETE statements delete one or more records from database tables
– We specify the deletion criteria using a WHERE clause

UPDATE statements update one or more rows of database tables
– We specify the update criteria using a WHERE clause
55
Overview

9.1 Introduction

9.2 Types of SQL Statements

9.3 The Data Definition Language (DDL)

9.4 The Data Manipulation Language (DML)

9.5 The SELECT Statement

9.6 The INSERT Statement

9.7 The DELETE Statement

9.8 The UPDATE Statement

9.9 Summary

9.10 In-Class Assignment
56
In-Class Assignment

Write SQL statements for the following queries based on the university
database:
– Query 1:
 “Create a new faculty table, tblFaculty. Assume appropriate fields and their data
types, including a primary key field. The table must have a DeptID as a foreign
key referring to the department table, tblDepartment. Also, create an index on
the primary key of the faculty table.”
– Query 2:
 “Display the number of faculty members working for each department in the
department table.”
– Query 3:
 “Delete all those faculty members who have been at the university for 15 years
(use JoiningDate fields) and who are earning less than $50,000 (use Salary
field).”
57
Additional Links

Refer SQL: The Complete Reference by James R Groff and Paul N.
Weinberg for additional details on SQL.

Also refer Access 2003 Bible by Cary N. Prague, Michael R. Irwin,
and Jennifer Reardon.

Also see: http://msdn.microsoft.com/ for useful tips on SQL and
Access.
58