Advanced SQL Using DB2 V6 Refresher Workshop

1
Advanced SQL Using DB2 V6Refresher Workshop

• Florida State University
• AIS / University Data Services
• Penny Bowman
• November, 2002
• Using DB2 Developers Guide, Fourth Edition, by
  Craig Mullins

2
Advanced SQL Using DB2 V6Workshop Objectives

• SQL Overview
• Union and Union All
• Inner and Outer Join Techniques
• CASE Statement
• SubQuery Techniques
• DB2 Functions
• DB2 Datatypes
• Review of Using the Explain Plan
• Programming Guidelines

3
SQL Overview

• SQL Overview
• Part 1 Chapter 1 - Page 11 21
• Static vs. Dynamic SQL
• Part 1 Chapter 1 - Page 46 49
• It Depends!
• Part 1 Chapter 2 - Page 62

4
Relational Concepts Review

• A relational database is a collection of data
  where logically related data is grouped into
  tables within the database (files)
• Tables within a relational database hold columns
  of data that appear vertically in a report
  (fields)
• Rows appear horizontally in a report, and contain
  one or more columns (records)
• Indexes are created for faster access to the data

5
SQL Review of the Basics

• How do we write an SQL SELECT statement that
  retrieves data from a single table ?
• How do we filter the data vertically by
  requesting only the columns / fields we need ?
• How do we filter the data horizontally by using
  the WHERE clause of the SELECT STATEMENT to limit
  the rows returned ?

6
SQL Review of the Basics

• Sorting and Grouping
• Part 1 Chapter 1 Page 36 37
• Order By ascending or descending
• Group By to apply functions to groups of data
• Having and Where
• Part 1 Chapter 1 Page 37 38
• HAVING follows the GROUP BY and can be used to
  place a condition on the GROUP
• Where indicates which rows are to be returned

7
Column Functions with GROUP BY (Page 104-108)

• AVG(DISTINCT field)
• Calculates the average value of the rows in the
  group
• COUNT (DISTINCT field )
• Counts the number of rows in a group, returns an
  integer.
• MAX(DISTINCT field)
• Identifies the maximum value of a numeric column
  or columns in a group
• MIN(DISTINCT field)
• Identifies the maximum value of a numeric column
  or columns in a group
• SUM(DISTINCT field)
• Calculates the sum of a numeric column or columns
  in a group

8
Group By Course Number

• SELECT COURSE_NUMBER,
• COUNT() AS NUMBER_OF_SECTIONS
• FROM
• FSDBA.CURRENT_COURSES
• GROUP BY COURSE_NUMBER

9
Group By Division and Major code

• SELECT CURR_DIV, CURR_MAJOR, COUNT() AS
  TOTAL_STUDENTS
• FROM FSDBA.STUDENT_MASTER
• GROUP BY CURR_DIV, CURR_MAJOR
  
• ORDER BY 3 DESC

10
Group By Course Dept, Number, Indicator

• SELECT COURSE_DEPT_NUMBER,
• COURSE_NUMBER, COURSE_IND
  
• FROM FSDBA.COURSE_MASTER
• GROUP BY COURSE_DEPT_NUMBER,
• COURSE_NUMBER, COURSE_IND
  

11
Group by Courses Having Capacity gt500

• SELECT COURSE_NUMBER,
• AVG(COURSE_CAPACITY)
• AS AVERAGE_CAPACITY
• FROM FSDBA.CURRENT_COURSES
• GROUP BY COURSE_NUMBER
  HAVING SUM(COURSE_CAPACITY) gt 500

12
Increase SQL Complexity

• Now that we have reviewed the basics, lets
  increase the complexity of our SQL statements
• By retrieving data from more than one table,
• Using unions, joins, case, and subqueries,
• And use taking advantage of DB2 functions

13
Union Part 1 Chapter 1 Page 32 - 33

• UNION
• Generates a result set for each SELECT statement.
  Combines the results, removes duplicate rows, and
  sorts the final result.
• UNION ALL
• Generates a result set for each SELECT statement.
  Combines the results, but retains all the rows
  from each set and does not perform a sort.

14
Union / Union All

• SELECT LAST_NAME, FIRST_NAME
• FROM FSDBA.TEACHER_MASTER
• WHERE LAST_NAME LIKE 'A'
• UNION ALL
• SELECT LAST_NAME, FIRST_NAME
• FROM FSDBA.STUDENT_MASTER
• WHERE LAST_NAME LIKE 'A'
• Note try both UNION and UNION ALL

15
Union / Union All

• SELECT DISTINCT(STATE)
• FROM FSDBA.ADDRESS_MASTER
• UNION
• SELECT DISTINCT(STATE)
• FROM FSDBA.STUDENT_MASTER
• Note try both UNION and UNION ALL

16
Union / Union All

• SELECT COURSE_NUMBER, COURSE_IND, SECTION_NUMBER
• -- , 'CURRENT'
• FROM FSDBA.CURRENT_COURSES AS CCOURSE
• WHERE COURSE_NUMBER 'ACG3171'
• UNION
• SELECT COURSE_NUMBER, COURSE_IND, SECTION_NUMBER
  
• -- , 'STUDENT'
• FROM FSDBA.STUDENT_COURSE AS SCOURSE
• WHERE COURSE_NUMBER 'ACG3171'
• Note try both UNION and UNION ALL

17
Joining TablesPart 1 Chapter 1 - Page 23 28

• Combining Data from More than 1 Table
• Join Syntax
• Join Criteria
• Cartesian Products

18
Inner Join

• Retrieves rows that exist in both of the named
  tables, based on the conditions expressed in the
  WHERE clause. Retrieves the matches from the
  tables based on the join predicate.
• Old DB2 format (like Oracles format)
• SELECT expression FROM Table or view or
  (sub-select) ,
• Table or
  view or (sub-select)
• WHERE predicates (join and local)

19
Inner Join

• New SQL Standardized format
• SELECT expression FROM table or view or
  (sub-select)
• INNER JOIN Table or view or (sub-select)
• ON column operator column and
• column operator column
• WHERE predicates (local)

20
Inner Join (old syntax)

• select c2.course_number, c2.course_ind,
  course_title, year, term, building, room_number
• fromfsdba.current_courses as c1,fsdba.course_mas
  ter as c2
• wherec1.course_number c2.course_number
  andc1.course_ind c2.course_ind
  andc1.course_number like 'AML
• group byc2.course_number, c2.course_ind,
  course_title, year, term, building, room_number
• order by 1,2

21
Inner Join (new syntax)

• select c2.course_number, c2.course_ind,
  course_title, year, term, building, room_number
• fromfsdba.current_courses as c1 inner
  joinfsdba.course_master as c2onc1.course_number
  c2.course_number andc1.course_ind
  c2.course_ind
• Where c1.course_number like 'AML
• group by c2.course_number, c2.course_ind,
  course_title, year, term, building, room_number
• order by 1,2

22
Inner Join With Union

• SELECT 'TEACHER', LAST_NAME, FIRST_NAME,
• COURSE_NUMBER, SECTION_NUMBER,
• YEAR, TERM
• FROM FSDBA.CURRENT_COURSES AS C
• INNER JOIN FSDBA.TEACHER_MASTER AS
  D
• ON C.FACULTY_ID D.FACULTY_ID
• WHERE COURSE_NUMBER 'ACG3171
• UNION ALL

23
Inner Join With Union

• (continued from previous page)
• SELECT 'STUDENT', LAST_NAME, FIRST_NAME,
• COURSE_NUMBER, SECTION_NUMBER,
• YEAR, TERM
• FROM FSDBA.STUDENT_COURSE AS E
• INNER JOIN FSDBA.STUDENT_MASTER
  AS F
• ON E.STUDENT_ID F.STUDENT_ID
• WHERE E.COURSE_NUMBER 'ACG3171'
• Note try both UNION and UNION ALL

24
Outer Join Types

• LEFT OUTER JOIN Shows all matches, plus rows
  from the left (first named) table that have no
  corresponding rows in the right table.
• RIGHT OUTER JOIN Shows all matches, plus rows
  from the right (second named) table that have no
  corresponding rows in the left table.
• FULL OUTER JOIN Shows all matches, plus rows
  from the left (first named) table that have no
  corresponding rows in the right table, plus rows
  from the right (second named) table that have no
  corresponding rows in the left table.

25
Outer Join - Part 1 Chapter 1 Page 34 - 36

• SELECT expression
• FROM table or view or (sub-select)
• LEFT or RIGHT or FULL JOIN
• table or view or (sub-select)
• ON column operator column
• WHERE predicates (local)

26
Outer Join Example

• List all the courses in the Course_Master that
  are not being taught.
• Use a Left Outer Join. 
• Now, include any courses that are being offered
  that were not in the Course Master.
• Use a Right Outer Join.  
• What courses are in both tables?
• Use a Full Outer Join.

27
Outer Join Example

• SELECT A.Course_Dept_Number,
• B.Course_number, B.Course_Ind, B.Section_Number,
• A.Course_Title
• FROM Course_Master as A
• LEFT or RIGHT or FULL JOIN
• Current_Courses as B
• ON A.Course_Number B.Course_Number and
• A.Course_Ind B.Course_Ind

28
Outer Join Example

• SELECT A.STUDENT_ID,
• B.EFFECTIVE_DATE AS EFF_DATE,
• B.STOP AS STOP_CODE
  
• FROM FSDBA.STUDENT_MASTER AS A
  
• LEFT or RIGHT or FULL JOIN
• FSDBA.STUDENT_HOLDS AS B
• ON A.STUDENT_ID B.STUDENT_ID
• Where Student_Id Like 0015

29
Coalesce in an Outer Join

• Use the COALESCE function
• in an OUTER JOIN
• to derive non-null values
• for the columns of the result set
• that are not present in the joined table.

30
Coalesce in an Outer Join

• SELECT A.STUDENT_ID,
• COALESCE ( B.EFFECTIVE_DATE,
• CURRENT_DATE ) AS EFF_DATE,
• COALESCE ( B.STOP, 'NO STOP CODE ) AS
  STOP_CODE
• FROM FSDBA.STUDENT_MASTER AS A
  
• LEFT OUTER JOIN
• FSDBA.STUDENT_HOLDS AS B
• ON A.STUDENT_ID B.STUDENT_ID

31
CASE Expressions Part 1Chapter 1Page 3941

• A CASE statement enables limited logic testing in
  a SQL statement
• It uses the value of a specified expression to
  select one statement among several for execution
• Use a simple WHEN clause to test for equality of
  an expression
• Use a searched WHEN clause for more complex
  expression testing
• CASE can also be used to perform table pivoting

32
CASE With UNION

• Report the average cumulative GPA of all the
  students, for males, and for females, during the
  spring term for 1999.
• The first SELECT counts the Males and Females
  the second SELECT counts the total. The result is
  the union of these two sets.
• SELECT CASE GENDER
• WHEN 'M' THEN 'MALE'
• WHEN 'F' THEN 'FEMALE'
• ELSE 'UNKNOWN'
• END AS GENDER,

33
CASE With UNION

• (continuation from the previous page)
• DECIMAL(AVG(FSU_CUMM_GPA),6,3) AS AVG, COUNT()
  AS COUNT
• FROM FSDWH.DATA_SHARE
• WHERE YEAR '1999' AND TERM '1'
• GROUP BY GENDER
• UNION

34
CASE With UNION

• (continuation from the previous page)
• SELECT 'ALL',
• DECIMAL(AVG(FSU_CUMM_GPA),6,3)
• AS AVG,
• COUNT() AS COUNT
• FROM FSDWH.DATA_SHARE
• WHERE YEAR '1999' AND TERM '1
• Order By 1

35
CASE Code Translation

• Generate a report of the courses offered this
  term by the 3 art departments.
• SELECT A.COURSE_DEPT_NUMBER,
  B.COURSE_NUMBER, B.COURSE_IND,
  B.SECTION_NUMBER, A.COURSE_TITLE,
• CASE COURSE_DEPT_NUMBER
• WHEN '4206' THEN 'ART'
• WHEN '4207' THEN 'ART HISTORY'
• WHEN '4208' THEN 'ART EDUCATION'
• END

36
CASE Code Translation

• (continuation from the previous page)
• FROM FSDBA.COURSE_MASTER AS A
• INNER JOIN
• FSDBA.CURRENT_COURSES AS B
• ON A.COURSE_NUMBER B.COURSE_NUMBER
• AND A.COURSE_IND B.COURSE_IND
• WHERE YEAR '1998' AND TERM '1 AND
• COURSE_DEPT_NUMBER IN ('4206', '4207', '4208')

37
CASE Code Translation

• Generate a report of the students taking ranges
  of credit hours.
• SELECT A.LAST_NAME, A.FIRST_NAME,
• A.STUDENT_ID, YEAR, TERM, SUM(HOURS),
• CASE
• WHEN SUM(HOURS) BETWEEN 1 AND 6
• THEN 'SIXORLESS'
• WHEN SUM(HOURS) BETWEEN 7 AND 11
• THEN 'BETWEEN7AND11'

38
CASE Code Translation

• WHEN SUM(HOURS) gt 11 THEN 'MORETHAN11'
• END AS RANGE_HOURS
• FROM FSDBA.STUDENT_MASTER AS A
• INNER JOIN
• FSDBA.STUDENT_COURSE AS B
• ON A.STUDENT_ID B.STUDENT_ID
• GROUP BY A.LAST_NAME, A.FIRST_NAME,
• A.STUDENT_ID, YEAR, TERM

39
CASE Tallying

• Count the number of students by current class.
• SELECT
• sum (CASE curr_class WHEN '0' then 1 else 0 END)
  AS zero,
• sum (CASE curr_class WHEN '1' then 1 else 0 END)
  AS one,
• sum (CASE curr_class WHEN '2' then 1 else 0 END)
  AS two,
• sum (CASE curr_class WHEN '3' then 1 else 0 END)
  AS three,
• (continued on the next page)

40
CASE Tallying

• (continued from the previous page)
• sum (CASE curr_class WHEN '4' then 1 else 0 END)
  AS four,
• sum (CASE curr_class WHEN '5' then 1 else 0 END)
  AS five,
• sum (CASE curr_class WHEN '6' then 1 else 0 END)
  AS six,
• sum (CASE curr_class WHEN '7' then 1 else 0 END)
  AS seven,
• sum (CASE curr_class WHEN '8' then 1 else 0 END)
  AS eight,
• sum (CASE curr_class WHEN '9' then 1 else 0 END)
  AS nine
• FROM FSDBA.STUDENT_master

41
CASE Tallying

• Count the number of course sections offered this
  term by the 3 art departments.
• SELECT
• 'ART CLASSES , SUM(CASE COURSE_DEPT_NUMBER
• WHEN '4206 THEN 1 ELSE 0
  END),
• 'ART HISTORY CLASSES ', SUM(CASE
• COURSE_DEPT_NUMBER
• WHEN '4207' THEN 1 ELSE 0
  END),

42
CASE Tallying

• (continuation from the previous page)
• 'ART EDUCATION CLASSES ', SUM(CASE
• COURSE_DEPT_NUMBER
• WHEN '4208 THEN 1 ELSE 0
  END)
• FROM FSDBA.COURSE_MASTER AS A INNER JOIN
• FSDBA.CURRENT_COURSES AS B
• ON A.COURSE_NUMBER B.COURSE_NUMBER
• AND A.COURSE_IND B.COURSE_IND
• WHERE YEAR '1998' AND TERM '1'

43
DB2 Subquery

• SubQuery Techniques
• Part 1 Chapter 1 - Page 28 30
• Optimizing a Subquery
• Part III Chapter 19, Page 707 710
• Join Vs. Subqueries
• Part 1 Chapter 1 - Page 31 Page 710
• Inline Views
• Part 1 Chapter 2 - Page 91 - 94

44
DB2 Subquery

• A subquery is a form of the SELECT statement that
  appears inside another SQL statement.
• A subquery can be used in 2 places in a SELECT
  statement
• In the FROM clause to define a table or a set of
  rows to be operated on by SELECT, INSERT, UPDATE,
  and DELETE statements
• In the predicates of the WHERE or HAVING clause
  to provide values for conditions in SELECT,
  UPDATE, and DELETE statements

45
SubQueries In the From Clause

• When a Subquery is used in the FROM clause it can
  SELECT 1 or more columns.
• Give the subquery a correlation name then JOIN
  the result set of the subquery to another table
  or view using the correlation name.
• Useful when you need to mix detail with aggregate
  data such as SELECTing a column function
  (i.e..COUNT, SUM, MIN, MAX, AVG) in addition to
  one or more non-column function expressions other
  than a table or view column name.
• The database selects the rows from the tables for
  the Subquery before it is joined with the other
  tables.

46
Average Salary by Year

• First Example Write a query to calculate the
  average salary for each year of hire in an
  Employee table.
• SELECT HIREYEAR, AVG(SALARY) as AvgSalary
• FROM
• (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
• FROM Employee) AS NEWEMP
• GROUP BY HIREYEAR

47
Average Salary by Year

• HireYear AvgSalary
• -------- ------------
• 1984 800
• 1985 2014.13
• 1986 2433.33
• 1987 1255

48
Student Birth Year Count

• SELECT BIRTHYEAR,
• COUNT() as Count
• FROM(SELECT YEAR(BIRTHDATE)
• AS
  BIRTHYEAR
• FROM FSDBA.STUDENT_MASTER)
• AS
  STUD_TABLE
• GROUP BY BIRTHYEAR

49
DB2 Example

• DB2 version
• SELECT HIREYEAR, AVG(SALARY)
• FROM
• (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY
• FROM pbowman.Employee) AS NEWEMP
• GROUP BY HIREYEAR

50
Oracle Example

• Oracle version
• SELECT HIREYEAR,
• round(AVG(SALARY),2) as AvgSalary
• FROM
• (SELECT to_char(HIRE_DATE, 'YYYY') AS
  HIREYEAR,
• SALARY FROM pbowman.Employee) NEWEMP
• GROUP BY HIREYEAR

51
Average GPA by Division

• Select the average GPA and minimum GPA of each
  division that have students enrolled in Fall,
  1998, including only those students with a
  non-zero cumulative GPA.
• First Analyze the query request and identify
  the query conditions.
• Next ? Count the students for each division.
• Then ? Calculate the average GPA and minimum GPA
  for each Division. Use only Non zero cumulative
  GPAs
• Finally ? Combine the queries into the final
  statement.

52
Count Students By Division

• First Query CHECK COUNT OF STUDENTS FOR EACH
  DIVISION
• SELECT CURR_DIV, COUNT()
• FROM FSDWH.DATA_SHARE
• WHERE YEAR '1998 AND TERM '9'
• GROUP BY CURR_DIV

53
Calculate the Average GPA

• Second Query CHECK AVG FOR EACH DIVISION
• SELECT CURR_DIV, AVG(FSU_CUMM_GPA)
• FROM FSDWH.DATA_SHARE
• WHERE YEAR '1998 AND
• TERM '9 AND
• FSU_CUMM_GPA ltgt 0.00
• GROUP BY CURR_DIV

54
Calculate the Minimum GPA

• Third Query CHECK MIN FOR EACH DIVISION
• SELECT CURR_DIV,
• MIN(FSU_CUMM_GPA)
• FROM FSDWH.DATA_SHARE
• WHERE YEAR '1998 AND
• TERM '9 AND
• FSU_CUMM_GPA 0.00
• GROUP BY CURR_DIV

55
Build the Final Statement

• Now - Study this combined query
• SELECT AVG.CURR_DIV,
• COUNT() AS NUMBER_OF_STUDENTS,
• AVG_GPA, MIN_GPA
  
• FROM (SELECT CURR_DIV,
• DECIMAL(AVG(FSU_CUMM_GPA),4,3)
• AS AVG_GPA,

56
Build the Final Statement

• MIN(FSU_CUMM_GPA) AS MIN_GPA
• FROM FSDWH.DATA_SHARE
  
• WHERE YEAR '1999' AND
• TERM '9' AND
• FSU_CUMM_GPA ltgt 0.00
  
• GROUP BY CURR_DIV) AS AVG

57
Build the Final Statement

• RIGHT OUTER JOIN
• FSDWH.DATA_SHARE AS DSH
  
• ON DSH.CURR_DIV AVG.CURR_DIV
• WHERE YEAR '1999' AND TERM '9'
  
• GROUP BY AVG.CURR_DIV, AVG_GPA, MIN_GPA  

58
Review - SubQueries In the From Clause

• Any questions about using a subquery in a FROM
  clause of a SELECT statement?
• This technique is also referred to as In-Line
  Views.
• DB2 JOINs the result set of the subquery to
  another table or view using the correlation name.
• You can mix detail with aggregate data.
• The database selects the rows from the tables for
  the Subquery before it is joined with the other
  tables.

59
SubQueries In a Predicate

• When a subquery is used in a predicate, the
  subquery supplies information needed
• to qualify a row (in a WHERE clause),
• or a group of rows (in a HAVING clause).
• The subquery is used as the right operand of a
  search condition, rather than a value or a column
  name.
• Note Order By cannot be used in the subquery.
• The subquery can SELECT only 1 column, but can
  reference more than 1 table.

60
SubQueries In a Predicate

• When only 1 row is returned by the subquery, you
  can use these comparative operators between the
  left operand and the Subquery
• lt lt gt gt ltgt
• When more than 1 row is returned by the subquery,
  you can use relational operators between the
  left operand and the Subquery.

61
When gt1 Row Is Returned By a Subquery

• For equality checking, use
• IN, NOT IN, ANY, SOME, ALL
• If the SOME or ANY statement is used, then the
  comparison is true if at least one value from the
  logical result table of the subselect meets the
  condition.
• If the ALL statement is used, then the comparison
  is true if the logical result table is empty or,
  if the comparison is true for all values of the
  result table.
• You can combine comparative operators with
  relational operators gt ALL lt ALL lt SOME

62
SubQuery Example Review

• List the Members of Carol Clarks department.
  First write a subquery to determine the
  department in which Clark works. Then answer the
  original question with a parent SELECT statement.
• select last_name, first_name, department_id
• from pbowman.employee
• where department_id
• (select department_id from pbowman.employee
• where last_name 'CLARK' and first_name
  'CAROL')

63
SubQuery Example Review

• LAST_NAME FIRST_NAME DEPARTMENT_ID
• --------------- ---------------
  -------------
• CLARK CAROL
  10
• KING FRANCIS
  10
• MILLER BARBARA
  10
• The database runs the subquery one time to
  determine the department for Carol Clark, then
  uses that value in the predicate to generate the
  report from the Employee table.

64
Using IN

• Compares a value to each value in a list or
  returned by a query. Equivalent to ANY.
• SELECT FROM employee
• WHERE job_id IN (670, 671)
• SELECT FROM employee
• WHERE job_id IN (Select job_id
• from employee where salary gt 40000)

65
Using IN

• SELECT a.work_dept_number, faculty_rank,
  a.last_name, a.first_name
• FROM fsdba.teacher_master a
• WHERE a.work_dept_number in ( SELECT
  work_dept_number FROM
  fsdba.teacher_master group by
  work_dept_number having max(faculty_rank)
  '05' )ORDER BY 1, 2 desc, 3, 4

66
Using EXISTS

• The (NOT) EXISTS predicate evaluates a subquery
  as True or False without materializing the data
  in the subquery.
• WHERE EXISTS (SUBSELECT)
• The result is true if the SUBSELECT defines a
  logical result table of at least one row.
• Since only existence is important in this case,
  the field(s) defined by the SUBSELECT is
  immaterial. Many developers use the constant 1.
• There is an improvement with DB2 Version 6 that
  transforms a NOT IN to a NOT EXISTS with the
  appropriate predicate.

67
Using Exists

• SELECT a.work_dept_number, faculty_rank,
  a.last_name, a.first_name
• FROM fsdba.teacher_master a
• WHERE exists
• ( SELECT work_dept_number
• FROM FSDBA.teacher_MASTER as b
• where a.work_dept_number b.work_dept_number
  group by work_dept_number having
  max(faculty_rank) '05' )ORDER BY 1, 2 desc, 3,
  4

68
Using EXISTS

• Create a list of courses for the term, but only
  if students have started registering.
• SELECT DISTINCT A.COURSE_NUMBER,
  A.COURSE_IND, A.SECTION_NUMBER,
• A.YEAR, A.TERM
• FROM FSDBA.CURRENT_COURSES AS A
• WHERE A.YEAR '1998' AND A.TERM '1'
  
• AND EXISTS

69
Using EXISTS

• AND EXISTS
• (SELECT 1 FROM FSDBA.STUDENT_COURSE B
• WHERE B.YEAR '1998' AND B.TERM
  '1')
• ( expecting 609)
• ORDER BY COURSE_NUMBER, SECTION_NUMBER
• Alternate predicate in the subselect
• WHERE B.YEAR '1998' AND B.TERM '9')
  (expecting no rows)

70
Using SOME and ANY

• Evaluates to FALSE if the query returns no rows.
  Compares a value to each value in a list or
  returned by a query. Must be preceded by , !,
  gt, lt, lt, gt
• SELECT a.work_dept_number, faculty_rank,
  a.last_name, a.first_name
• FROM fsdba.teacher_master a
• WHERE a.work_dept_number some
• ( SELECT work_dept_number FROM
  fsdba.teacher_master group by
  work_dept_number having max(faculty_rank)
  '05')ORDER BY 1, 2 desc, 3, 4

71
Using ALL

• Evaluates to TRUE if the query returns no rows.
  Compares a value to each value in a list or
  returned by a query. Must be preceded by , !,
  gt, lt, lt, gt
• SELECT manager_id, employee_id, last_name,
  first_name, salary
• FROM pbowman.employee
• WHERE salary gt ALL (1400, 3000)
• order by manager_id, last_name, first_name (5
  rows selected)
• WHERE salary lt ALL (1400, 3000) (15 rows
  selected)

72
Using ALL (, lt, gt)

• SELECT a.work_dept_number, faculty_rank,
  a.last_name, a.first_name
• FROM fsdba.teacher_master a
• WHERE a.work_dept_number all
• ( SELECT work_dept_number FROM
  fsdba.teacher_master group by
  work_dept_number having max(faculty_rank)
  '05')ORDER BY 1, 2 desc, 3, 4
• having max(faculty_rank) invalidValue'

73
Using a Subquery in Having

• -- report the students that are enrolled in the
  most number of hours
• SELECT A.STUDENT_ID, A.LAST_NAME, A.FIRST_NAME,
  SUM(B.HOURS)
• FROM FSDBA.STUDENT_MASTER A
• INNER JOIN FSDBA.STUDENT_COURSE BON A.STUDENT_ID
  B.STUDENT_ID
• GROUP BY A.STUDENT_ID, A.LAST_NAME, A.FIRST_NAME
• HAVING SUM(HOURS)
• ( SELECT MAX(MYQUERY.LISTOFHOURS) FROM (
  SELECT STUDENT_ID, SUM(HOURS) AS LISTOFHOURS
  FROM FSDBA.STUDENT_COURSE
  GROUP BY STUDENT_ID) AS MYQUERY )

74
SubQuery Correlation

• A Correlated Subquery executes repeatedly, in
  sync with the outer querys execution. The outer
  query can be a SELECT, UPDATE, or DELETE
  statement.
• A Non-correlated Subquery executes first, and
  once. Bottom-up execution. The previous subquery
  examples have been non-correlated.

75
Correlated SubQuery

• Use a correlated subquery to answer a
  multiple-part question whose answer depends on
  the value in each row processed by the parent
  statement.
• For example, a correlated subquery can be used to
  determine which employees earn more than the
  average salaries for their departments. The
  correlated subquery specifically computes the
  average salary for each department.
• The database performs a correlated subquery when
  the subquery references a column from a table
  from the parent statement.

76
Correlated Subquery

• Example List employees whose salaries exceed the
  averages for their departments. Notice the
  statement assigns an alias to the Employee table,
  the table, and then uses the alias in the
  correlated subquery.
• SELECT department_id, employee_id, last_name,
  first_name, salary
• FROM employee x
• WHERE salary gt
• (SELECT AVG(salary) FROM employee
• WHERE x.department_id department_id)
• ORDER BY department_id

77
Correlated Subquery

• For each row of the EMP table, the parent query
  uses the correlated subquery to compute the
  average salary for members of the same
  department. The correlated subquery performs
  these steps for each row of the employee table
• The DEPTNO of the row is determined.
• The DEPTNO is then used to evaluate the parent
  query.
• If that rows salary is greater than the average
  salary for that rows department then the row is
  returned.
• The subquery is evaluated once for each row of
  the Employee table.

78
Correlated Subquery Example

• -- report the Art courses that were offered for
  minimum credit
• SELECT s.course_number , s.hours, s.year,
  s.term from fsdba.student_course s where hours
  in --gt some --lt some
• (select integer(min_credit)
• from fsdba.course_master c where
  c.course_number s.course_number and
  c.course_number like 'ART49')
• group by s.Course_number , s.hours, s.year,
  s.term

79
Review - SubQueries In the Where and Having

• Any questions about using a subquery in a WHERE
  and HAVING clauses of a SELECT statement?
• Use operators to connect the subquery to the
  expression.
• If the subquery returns a single row, you can use
  
• If the subquery returns gt1 row, you must use the
  inequality operators with IN, SOME, ANY, or ALL
• Non correlated subqueries execute first.
• Correlated subqueries execute along with the
  outer query.

80
Complex SQL Guidelines Part 1 Chapter 2 Page
82 94

• Joins
• Subqueries
• Union
• Grouping
• Using Correlated Subselect to Determine Top Ten

81
DB2 Version 6 Functions

• DB2 Version 6 SQL Functions
• Part 1 Chapter 1 Page 41 42
• Scalar Functions (Includes Date Functions)
• Part 1 Chapter 3 Page 109 117

82
DB2 Version 6 Functions

• ABS or ABSVAL
• ACOS
• ASIN
• ATAN
• ATANH
• ATAN2
• BLOB

• CEIL or CEILING
• CHAR
• CLOB
• COALESCE
• CONCAT
• COS
• COSH

83
DB2 Version 6 Functions

• DATE
• DAY
• DAYOFMONTH
• DAYOFWEEK
• DAYOFYEAR
• DAYS
• DBCLOB

• DECIMAL or DEC
• DEGREES
• DIGITS
• DOUBLE or DOUBLE-PRECISIION
• EXP
• FLOAT
• FLOOR

84
DB2 Version 6 Functions

• GRAPHIC
• HEX
• HOUR
• IFNULL
• INSERT
• INTEGER or INT
• JULIAN_DAY
• LCASE or LOWER

• LEFT
• LENGTH
• LOCATE
• LOG
• LOG10
• LOWER OR LCASE
• LTRIM

85
DB2 Version 6 Functions

• MICROSECOND
• MIDNIGHT_ SECONDS
• MINUTE
• MOD
• MONTH
• NULLIF

• POSSTR
• POWER
• QUARTER
• RADIANS
• RAND
• REAL
• REPEAT

86
DB2 Version 6 Functions

• REPLACE
• RIGHT
• ROUND
• ROWID
• RTRIM
• SECOND
• SIGN
• SIN

• SINH
• SMALLINT
• SPACE
• SQRT
• STRIP
• SUBSTR

87
DB2 Version 6 Functions

• TAN
• TANH
• TIME
• TIMESTAMP
• TRANSLATE
• TRUNCATE or TRUNC

• UCASE OR UPPER
• VALUE
• VARCHAR
• VARGRAPHIC
• VARIANCE OR VAR
• WEEK
• YEAR

88
DB2 Version 6 Functions

• Raise_Error Function
• Part 1 Chapter 3 Page 117 118
• Built-In Function Guidelines
• Part 1 Chapter 3 Page 118 - 120

89
DB2 Date / Time Part 1 Chapter 2Page 94-97, 213

• Date/time values in DB2 are stored in a special
  internal format. When you load or retrieve data,
  DB2 can convert to or from any of the valid
  formats.
• DATE
• A date is a three-part value representing a year,
  month, and day in the range 0001-01-01 to
  9999-12-31 YYYYMMDD
• YYYY corresponds to a year value from 0000 to
  9999
• MM corresponds to a month value from 1 to 12
• DD corresponds to a day value from 1 to 31

90
Time Datatype

• TIME
• A time is a three-part value representing a time
  of day in hours, minutes, and seconds, in the
  range 00.00.00 to 24.00.00.
• HHMMSS
• HH corresponds to an hour value from 00 to 24
• MM corresponds to a minute value from 0 to 59
• SS corresponds to a second value from 0 to 59

91
Timestamp Datatype

• TIMESTAMP
• A timestamp is a seven-part value representing a
  date and time by year, month, day, hour, minute,
  second, and microsecond, in the range
  0001-01-01-00.00.00.000000 to 9999-12-31-24.00.00.
  000000.
• YYYYMMDDHHMMSSMICROS
• YYYYMMDD corresponds to YEAR, MONTH and DAY
• HHMMSS corresponds to HOUR, MINUTE and SECOND
• MICROS corresponds to microseconds

92
Date, Time, Timestamp Data Types

• The combination of using DATE and TIME requires 7
  bytes, and using them in combination can save
  space over using TIMESTAMP. To calculate a
  duration using DATE and TIME columns, two
  subtractions must occur one for the DATE column
  and one for the TIME column.
• Use the DB2 TIMESTAMP data type when the date and
  time are always needed together, but rarely
  needed alone. TIMESTAMP provides greater time
  accuracy, down to the microsecond level, requires
  10 bytes. Use this when greater precision is
  important. Subtracting one TIMESTAMP field from
  another results in a TIMESTAMP duration.

93
CHAR Function to Format Date and Time

• The CHAR function returns a character
  representation of a date, a time, a timestamp or
  a decimal number.
• CHAR function allows you to easily convert date
  and time columns from the system format to one of
  several international standard formats.
• CHAR( field, ISOUSAEURJIS )

94
CHAR Function to Format Date and Time

• IF field is a date - The result is a date
  character string in the specified format (ISO is
  the default, USA, EUR, JIS)
• IF field is a time -The result is a time
  character string in the specified format (ISO is
  the default, USA, EUR, JIS)
• IF field is a timestamp - The result is a
  timestamp character string, no format can be
  specified.
• IF field is a decimal number - The result is a
  decimal character string (including sign), no
  format can be specified.

95
Current Date / Time Functions

• These functions return current information from
  DB2
• CURRENT DATE
• CURRENT TIME
• CURRENT TIMESTAMP
• If you want to only manipulate the current date
  or time, and you are not selecting other columns
  from a table, you may issue a SELECT statement
  using the system provided one row table
• SELECT CURRENT DATE, CURRENT TIME FROM
  SYSIBM.SYSDUMMY1

96
CHAR Function Examples

• SELECT CURRENT DATE, CURRENT TIME, CURRENT
  TIMESTAMP, CHAR(CURRENT DATE, ISO) AS
  ISODATE, CHAR(CURRENT DATE, USA) AS
  USADATE, CHAR(CURRENT DATE, EUR) AS
  EURDATE, CHAR(CURRENT DATE, JIS) AS JISDATE,
  CHAR(CURRENT TIME, ISO) AS ISODATE,
  CHAR(CURRENT TIME, USA) AS USADATE,
  CHAR(CURRENT TIME, EUR) AS EURDATE,
  CHAR(CURRENT TIME, JIS) AS JISDATEFROMSYSIBM.SYS
  DUMMY1

97
DATE Function Examples

• SELECT
• DATE(CURRENT_DATE 30 DAYS)
  AS DATEOFDATE,
• DATE(CURRENT_TIMESTAMP)
  AS DATEOFTIMESTAMP,
• DATE(730000) AS DATEOFINTEGER,
• DATE('2002001') AS DATEOFCHAR
• FROM SYSIBM.SYSDUMMY1

98
DayOf Examples

• SELECT DAYOFMONTH(CURRENT_DATE),
• DAYOFWEEK(CURRENT_DATE),
• DAYOFYEAR(CURRENT_DATE)
• FROM SYSIBM.SYSDUMMY1

99
HOUR, MINUTE, SECOND

• SELECT HOUR(CURRENT_TIMESTAMP),
• MINUTE(CURRENT_TIMESTAMP),
• SECOND(CURRENT_TIMESTAMP),
• MICROSECOND(CURRENT_TIMESTAMP),
• TIME(CURRENT_TIMESTAMP)
• FROM SYSIBM.SYSDUMMY1

100
YEAR, MONTH, DAY

• SELECT YEAR(CURRENT_DATE),
• MONTH(CURRENT_DATE),
• DAY(CURRENT_DATE),
• QUARTER(CURRENT_DATE),
• WEEK(CURRENT_DATE),
• YEAR(CURRENT_DATE)
• FROM SYSIBM.SYSDUMMY1

101
DB2 Developers Guide

• Data Types
• Part IX - Appendix G - Page 1553 - 1555
• Row and Column Guidelines
• Part 1 Chapter 5 Page 208 217
• DB2 Table Parameters
• Part 1 Chapter 5 Page 219 222
• Using DB2 Referential Integrity
• Part 1 Chapter 5 Page 223 227

102
DB2 Developers Guide

• Use Check Constraints
• Part 1 Chapter 5 Page 227 230
• Embedded SQL Guidelines (for COBOL)
• Part II Chapter 9 Page 313- 322
• SQL Reserved Words
• Part II Chapter 9 Page 322- 323

103
DB2 Developers Guide

• Host Variables
• Part II Chapter 9 Page 324 332
• Programming With Cursors
• Part II Chapter 9 Page 332 340
• Modifying Data With Embedded SQL
• Part II Chapter 9 Page 340 342

104
DB2 Developers Guide

• Application Development Guidelines
• Part II Chapter 9 Page 342 351
• Batch Programming Guidelines
• Part II Chapter 9 Page 351 361
• Online Programming Guidelines
• Part II Chapter 9 Page 361 367

105
DB2 Developers Guide

• Dynamic SQL Performance Guidelines
• Part II Chapter 10 Page 370 393
• Filter Factor Formulas
• Part III Chapter 19 Page 674 675
• SQL Access Guidelines (use Explain)
• Part 1 Chapter 2 Page 62 82
• Using Explain
• Part IV Chapter 23 Page 824 839
• The RUNSTATS Utility
• Part VI Chapter 32 Page 1109 - 1121

106
DB2 Developers Guide

• Hierarchy of Efficient Access Paths
• Part IV Chapter 23 Page 847 848
• Navigational Queries from the Catalog
• Part IV Chapter 24 Page 851 857
• Programmers Aid Queries
• Part IV Chapter 24 Page 866 869

107
DB2 Developers Guide

• Authorization Queries
• Part IV Chapter 24 Page 871 875
• Design Review Guidelines
• Part VII Chapter 38 Page 1274 1278
• General Distributed Performance Guidelines
• Part VIII Chapter 41 Page 1329 1330
• DB2 SQLCODE and SQLSTATE Values
• Part IX - Appendix A Page 1370 1427
• The DB2 Catalog Tables
• Part IX - Appendix B Page 1430 - 1523

108
Advanced SQL Using DB2Review

• SQL Overview
• Combining Data from More than 1 Table
• Union and Join Techniques
• CASE Statement
• SubQuery Techniques
• DB2 Version 6 Functions