WebEnabled Decision Support Systems

1
Web-Enabled Decision Support Systems

• SQL Creating and Processing RDBs

Prof. Name
name_at_email.com Position
[phone] University Name
2
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

3
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

4
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

5
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

6
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

7
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.

8
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

9
The CREATE Statement

• CREATE statements create database objects
• Define primary and foreign keys
• Create an index on a table attribute
• Syntax

10
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

11
SQL Field Constraints

• The following tables lists a few column
  constraints that can be used with the CREATE
  TABLE SQL statement

12
CREATE Index - Example

• Query
• Create an index (named tblDeptAddressIndx) on
  the Address field of the department table.
• SQL Statement

13
The DROP Statement

• DROP statements removes database objects
• Table, Index
• Syntax

14
DROP Statement - Examples

• Query 1
• Remove the department table, its data, columns,
  and indexes.
• SQL Statement 1
• Query 2
• Remove only the index on the Address field of
  the department table.
• SQL Statement 2

15
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
• Drop Syntax

16
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

17
ALTER Statement Example 2

• Query
• Drop the Chair column and DeptFK2 constraint
  from the department table.
• SQL Statement

18
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

19
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

20
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

21
The SELECT Statement

• SELECT statements are by far the most frequently
  used SQL statements
• Used for a wide variety of purposes
• Structure

Mandatory
Optional, but must appear in this order
22
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

23
Projection The SELECT Clause - Examples

• Query 1
• Project the student name, email, address, and
  phone fields from the student table.
• SQL Statement 1
• Query 2
• Project all columns from the student table.
• SQL Statement 2

24
Hands-On Tutorial Running DML Statements in the
Access Environment

• How-to Write, Execute, and Test SQL Statements
  in Access
• Click the Create Query in Design View option in
  the Database Window to open a new query in the
  Design View.
• Click the Close button in the Show Table dialog
  box without adding any tables to open the SQL
  View.
• Write the SQL statement and click the Run (!)
  button on the toolbar to execute the query.

Running DML SQL Statements in the Access
Environment
25
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

26
Join The FROM Clause - Example

• Query
• Select the contact information and department
  name for faculty members.
• SQL Statement

27
Renaming Query Tables and Fields Using the
Keyword AS

• To resolve conflicts, identify columns by
  prefixing tables names
• If table names are long or complex, use the
  keyword AS to rename

28
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 , lt, lt, gt, gt
• Clause Operators BETWEEN, LIKE, IN
• Logical Operators AND, OR, NOT
• Syntax

29
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

Join Clause
Filter Clauses
30
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

31
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

32
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

33
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

34
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

35
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

36
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
• Syntax

37
Grouping The GROUP BY Clause - Example

• Query
• Count the number of students in each department
  in the department table.
• SQL Statement

38
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

39
Using the HAVING Clause - Example

• Query
• List the departments in the College of
  Engineering that have enrollments of 100 or more
  students.
• SQL Statement

40
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

41
The INSERT Statement

• INSERT statements are DML statements that insert
  one or more records into database tables
• Syntax

42
The INSERT Statement - Example

• Query
• Insert information about the new department,
  Digital Institute, into the department table.
• SQL Statement

43
The INSERT Statement More Examples

• Query 1
• Insert information about the new department,
  setting the Phone to NULL.
• Query 2
• Insert information about multiple new
  departments from tblDownload.

44
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

45
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

46
The DELETE Statement Example 1

• Query
• Delete all last year (senior) industrial
  engineering students.
• SQL Statement

47
The DELETE Statement Example 2

• Query
• Delete all last year (senior) industrial
  engineering students.
• Delete records in conjunction with a sub query
• SQL Statement

48
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

49
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

50
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

51
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

52
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

53
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

54
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

55
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

56
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

57
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).

58
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.