SQL

1
SQL

• Structured Query Language standard language for
  relational data manipulation
• DB2, SQL/DS, Oracle, INGRES, SYBASE, SQL Server,
  dBase/Win, Paradox, Access, and others all rely
  upon SQL

2
Introduction to SQL

• IBM in the mid-1970s as SEQUEL
• SQ92 1992 ANSI standard a newer standard
  exists for SQL to extend it to object-oriented
  languages
• data access language that is embedded in
  application programs
• result of an SQL statement is a relation

3

• many vendors go beyond the ANSI standards for SQL
  because they want to better position their
  product in relation to their competitors
• consequently there are minor variations among
  vendors

4

• stand-alone
• SQL can be used by itself to retrieve and report
  information
• embedded
• SQL is frequently embedded in application
  programs
• SQL is not a programming language

5
Sample Data
ENROLLMENT Relation
STUDENT Relation
CLASS Relation
6
Simple Select

• SELECT SID, Name, Major
• FROM STUDENT

STUDENT Relation
7

• SELECT Major
• FROM STUDENT

• SELECT DISINCT Major
• FROM STUDENT

as opposed to
STUDENT Relation
8
SELECT DISINCT Major FROM STUDENT
9
Selection

• SELECT SID, Name, Major, GradeLevel, Age
• FROM STUDENT
• WHERE Major MATH

STUDENT Relation
10
SELECT SID, Name, Major, GradeLevel, AgeFROM
STUDENTWHERE Major MATH
11
Selection

• SELECT SID, Name, Major, GradeLevel, Age
• FROM STUDENT
• WHERE Major MATH AND Agegt21

STUDENT Relation
12
SELECT SID, Name, Major, GradeLevel, AgeFROM
STUDENTWHERE Major MATH AND Agegt21
13
Selection

• SELECT SID, Name, Major, GradeLevel, Age
• FROM STUDENT
• WHERE GradeLevel IN FR, SO

What about NOT IN ?
STUDENT Relation
14
SELECT SID, Name, Major, GradeLevel, AgeFROM
STUDENTWHERE GradeLevel IN FR, SO
15
Selection
is a wildcard match, like is a wildcard
match _ (the underscore symbol) is for a
character-by-character match

• SELECT Name
• FROM STUDENT
• WHERE Name LIKE R

STUDENT Relation
16
Sorting

• SELECT Name, Major, Age
• FROM STUDENT
• WHERE Major ACCOUNTING
• ORDER BY Name

STUDENT Relation
17
SELECT Name, Major, AgeFROM STUDENTWHERE Major
ACCOUNTINGORDER BY Name
18
Sorting

• SELECT Name, Major, Age
• FROM STUDENT
• WHERE Major ACCOUNTING
• ORDER BY Name DESC

ASC is for ascending
STUDENT Relation
19
SQL Built-In Functions

• there are five
• COUNT
• SUM only applies to numeric fields
• AVG only applies to numeric fields
• MIN
• MAX

20
SQL Built-In Functions

• SELECT COUNT(Major)
• FROM STUDENT
• SELECT COUNT(DISTINCT Major)
• FROM STUDENT

yields 8 as the answer
yields 3 as the answer
21
SELECT COUNT(DISTINCT Major)FROM STUDENT
22
Grouping

• SELECT Major, COUNT()
• FROM STUDENT
• GROUP BY Major
• HAVING COUNT() gt 1

FROM and WHERE go together and GROUP BY and
HAVING go together
23
Querying Multiple Tables

• Retrieval Using Subquery
• Joining with SQL

24
what are the names of students in BD445?
STUDENT Relation
ENROLLMENT Relation
CLASS Relation
25
Subquery (the second SELECT)
so this SELECT yields Jones and Baker

• SELECT Name
• FROM STUDENT
• WHERE SID IN
• (SELECT StudentNumber
• FROM ENROLLMENT
• WHERE ClassName BD445)

this SELECT yields 100 and 200
26
Joining with SQL
column names are unique within a table but it
helps to fully qualify a column name when more
than one table is targeted by the FROM parameter

• SELECT STUDENT.SID, STUDENT.Name,
  ENROLLMENT.ClassName
• FROM STUDENT, ENROLLMENT
• WHERE STUDENT.SID ENROLLMENT.StudentNumber

in a JOIN always look to match the common column
values
students not in a class dont get reported, why?
27
SELECT STUDENT.SID, STUDENT.Name,
ENROLLMENT.ClassNameFROM STUDENT,
ENROLLMENTWHERE STUDENT.SID ENROLLMENT.StudentN
umber
28
What Is The Answer To This Query?

• SELECT SID, Name
• FROM STUDENT
• WHERE SID NOT IN
• (SELECT DISTINCT StudentNumber
• FROM ENROLLMENT)

why is DISTINCT used?
what is this query really asking?
29
Inserting Data
you must know both the field order and field
type text fields require surrounding apostrophes

• INSERT INTO ENROLLMENT
• VALUES (400, BD445, 44)

to insert a STUDENT record where you dont know
the Major or GradeLevel
INSERT INTO STUDENT
notice the empty positions will place null values
in the table
VALUES (500, Hamilton, , , 45)
key values must always be entered
30
Modifying Data

• UPDATE ENROLLMENT
• SET PositionNumber 44
• WHERE SID 400

be careful of wildcard matches
31
Deleting Data

• DELETE STUDENT
• WHERE STUDENT.SID 100

probably the most dangerous SQL statement