Chapter 7: Relational Implementation with SQL

1
(No Transcript)
2
Chapter 9 SQL
3
Data Types

• Exact numerics
• integer
• small integer
• numeric (p,s)
• decimal (p,s)
• Approximate numerics
• real
• double precision
• float
• Character strings
• character (n)
• character varying (n)

• Bit strings
• bit (n)
• bit varying (n)
• Datetimes
• date
• time
• timestamp
• time with time zone
• timestamp with time z.
• Intervals
• year-month
• day-time

4
Domain Definition

• Constraints
• Default values
• CREATE DOMAIN domain_name NUMERIC (4) DEFAULT
  0 CHECK (value is not null)
• use when several fields will have same
  characteristics

5
Defining Tables

• Three steps
• name the table
• define each column
• define table constraints
• CREATE TABLE table_name ( field_name fld_fmt
  pri_key for_key REFERENCES table
  constraints)

6
Table Constraints

• Foreign keys
• normal foreign keys
• recursive foreign keys
• on delete constraints if record in referenced
  table is deleted
• set null keep record but set FK field to null
• cascade delete record containing FK
• set default set FK field to specified default

7
Data Manipulation

• Queries
• Data entry
• Data change (update)

8
Simple Queries

• SELECT FROM WHERE
• SELECT worker_name FROM worker WHERE skill_type
  'Plumbing'
• SELECT FROM WHERE

9

• lt _at_ LANGUAGE "JScript" gt
• lthtmlgt
• ltheadgt
• lttitlegttbl_Building_Citylt/titlegt
• lt
• var strSQL
• strSQL "SELECT tbl_Building_City.ID,
  tbl_Building_City.City FROM tbl_Building_City"
• objSQL Server.CreateObject("ADODB.Connection")
• objSQL.Open("FILEDSNC/Program Files/Common
  Files/ODBC/Data Sources/lease.dsn")
• objRS objSQL.Execute(strSQL)
• gt
• lt/headgt

10

• ltbodygt
• ltTABLE bordergtltTRgt
• lt for (i 0 i lt objRS.Fields.Count - 1 i)
  gt
• ltTHgtltobjRS(i).Namegtlt/THgt
• lt gt
• lt/TRgt
• lt while (!objRS.EOF) gt
• ltTRgt
• lt for (i 0 i lt objRS.Fields.Count - 1 i)
  gt
• ltTDgtltobjRS(i)gtlt/TDgt
• lt gt
• lt/TRgt
• lt objRS.MoveNext()
• gt
• lt/bodygtlt/htmlgt
• TRY IT!

11
Less Simple Queries

• Character string literals
• Computations
• standard arithmetic
• Sorting
• SELECT worker_name, 'Weekly Wage ',
  40hrly_rateFROM workerWHERE skill_type
  'Electric'ORDER BY worker_name

12
Movin Right Along

• Comparison operators
• ltgt lt gt lt gt
• Boolean connectives
• AND OR NOT
• Other operators
• BETWEEN x AND y
• IN (list)

13
Wild-Card Characters

• Symbols that stand for unspecified strings of
  characters
• zero (0) or more characters
• _ a single character
• LIKE operator

14
Multiple-Table Queries

• JOIN operation specified in FROM and WHERE
  clauses
• SELECT skill_typeFROM worker, assignmentWHERE
  worker.worker_id assignment.worker_id AND
  bldg_id 435

15
Inner Join Queries

• JOIN statement in FROM clause
• SELECT FirstName, MidName, LastName,
  FocusAreasFROM tblPersons INNER JOIN tblAgents
  ON tblPersons.PersonID tblAgents.PersonIDWHE
  RE City Burbank

16
Aliases

• Defined in the FROM clause
• useful shorthand
• necessary in recursive relations
• SELECT a.worker_name, b.worker_nameFROM worker
  a, worker bWHERE b.worker_id a.supv_id
• SELECT a.worker_name, b.worker_nameFROM worker a
  INNER JOIN worker b ON a.supv_id b.worker_id

17
Subqueries

• SELECT skill_type FROM workerWHERE worker_id
  IN (SELECT worker_id FROM assignment WHERE
  bldg_id 435)
• Outer query and inner query
• Noncorrelated subquery creates a set of values
  that is then used IN the outer query

18
Correlated Subqueries

• Returns a single value, not a set
• value returned depends on record of interest in
  outer query
• SELECT worker_name FROM worker aWHERE
  a.hrly_rate gt (SELECT b.hrly_rate FROM worker
  b WHERE b.worker_id a.supv_id)

19
EXISTS and NOT EXISTS

• EXISTS evaluates to true if the resulting set is
  not empty
• NOT EXISTS evaluates to true if the resulting set
  is empty

20
NOT EXISTS

• Select every worker that has not had an
  assignment in building 435
• SELECT worker_id FROM workerWHERE NOT
  EXISTS (SELECT FROM assignment WHERE
  assignment.worker_id worker.worker_id
  AND bldg_id 435)

21
NOT EXISTS

• Select every worker such that there is no
  building to which they are not assigned i.e.,
  select every worker who has worked in all
  buildings.
• SELECT worker_id FROM worker wWHERE NOT
  EXISTS (SELECT bldg_id FROM building b WHERE
  NOT EXISTS (SELECT FROM assignment a WHERE
  a.bldg_id b.bldg_id AND a.worker_id
  w.worker_id))

22
Built-In Functions

• SUM, AVG, COUNT, MAX, MIN
• SELECT AVG(num_days)FROM assignmentWHERE
  bldg_id435
• DISTINCT eliminates duplicate rowsSELECT COUNT
  (DISTINCT skill_type)

23
GROUP BY

• Rows grouped by common values of specified column
• SELECT contains only group-by column and built-in
  function with arguments
• SELECT supv_id, MAX(hrly_rate)FROM workerGROUP
  BY supv_id

24
HAVING

• HAVING applies a condition to groups as a whole
• WHERE applies a condition to rows individually
• SELECT supv_id, MAX(hrly_rate)FROM workerGROUP
  BY supv_idHAVING COUNT() gt 1

25
Built-In Functions

• Only in SELECT and HAVING clauses
• Can be in
• main query
• noncorrelated subquery
• correlated subquery

26
The Clauses of SQL

• SELECT fields, expressionsFROM table(s)WHERE
  condition applied to fieldsGROUP BY fieldHAVING
  condition applied to groupORDER BY field

27
SQL Statement Processing Order

• FROM identifies involved tables
• WHERE finds all rows meeting stated conditions(s)
• GROUP BY organizes rows according to values in
  stated column(s)
• HAVING finds all groups meeting stated
  condition(s)
• SELECT identifies columns
• ORDER BY sorts rows (of output)
• see Figure 9-8 in text for paths through the
  statements

28
Database Change

• INSERT INTO table (fields)VALUES (values)
• UPDATE table SET field resultWHERE condition
• DELETE FROM tableWHERE condition

29
Embedded SQL

• Allows use of relational operations in
  traditional languages
• SQL statements set off by flag statements
• Differs from one language to another
• Can be used with Access

30
Views

• A restricted portion of the database
• limited data
• related data
• dynamic
• CREATE VIEW view_nameAS
• In Access, views are called queries

31
The Information Schema

• Metadata
• information describing the database is contained
  in the database
• can be queried to learn about the structure of
  the database

32
(No Transcript)