IBM DB2 9

© 2008 IBM Corporation

Section -4) Working with DB2 Data Working with DB2 Data using SQL and XQueryusing SQL and XQuery

Ajay Shankar KhareAjay Shankar KhareTechnical Consultant DB2 –IBM Academic InitiativeTechnical Consultant DB2 –IBM Academic InitiativeWEBTEK LABS PVT LTDWEBTEK LABS PVT LTDajay@webteklabs.comajay@webteklabs.com

Present ByPresent By:

IBM DB2 9

2

Section 4 - Working with DB2 Data using Section 4 - Working with DB2 Data using SQL and XQuery (23.5%)SQL and XQuery (23.5%)

Given a DML SQL statement, ability to identify resultsGiven a DML SQL statement, ability to identify results Ability to use SQL to SELECT data from tablesAbility to use SQL to SELECT data from tables Ability to use SQL to SORT or GROUP dataAbility to use SQL to SORT or GROUP data Ability to use SQL to INSERT, UPDATE, or DELETE dataAbility to use SQL to INSERT, UPDATE, or DELETE data Knowledge of transactions (i.e., COMMIT, ROLLBACK, Knowledge of transactions (i.e., COMMIT, ROLLBACK,

and transaction boundaries)and transaction boundaries) Ability to call a procedure or invoke a user defined Ability to call a procedure or invoke a user defined

functionfunction Given an XQuery statement, knowledge to identify resultsGiven an XQuery statement, knowledge to identify results

IBM DB2 9

3

What is (Structured Query Language) SQL? Standard language of relational database access is SQL.

Designed to access tabular data.

Invented by IBM in the 1970s, the SQL language continues to evolve and is the only way to access relational database data.

Three major categories.

- DDL (Data Definition Language) - Used to create, modify, or drop database objects

- DML (Data Manipulation Language) - Used to select, insert, update, or delete database data (records)

- DCL (Data Control Language) - Used to provide data object access control

IBM DB2 9

4

Managing Database Objects - DDL To create, modify, delete objects in a database, SQL Data

Definition Language (DDL) is used. DDL has four basic SQL statements:

- CREATE To create objects in a database.

- DECLARE To create Temporary Tables in a database.

- ALTER To make permitted alteration in structure of an object.

- DROP To remove any object created with CREATE/ DECLARE

statements from database.

IBM DB2 9

5

Manipulate Data - DML

To retrieve, insert, update or delete from tables and/or views, SQL Data Manipulation Language (DML) is used.

DML also has four basic SQL statements:- SELECT

To retrieve data from table or view.- INSERT

To add records in tables.- UPDATE

To change data.- DELETE

To delete records from tables.

IBM DB2 9

6

Using SELECT to Retrieve Data

The SELECT statement is used to retrieve table or view data.

Syntax -- SELECT [DISTINCT] columns- FROM TABLE(S)- WHERE condition- GROUP BY column- HAVING condition- ORDER BY column

IBM DB2 9

7

Examples -

SELECT * FROM staff;

SELECT * FROM staff FETCH FIRST 10 ROWS ONLY;

SELECT name, salary FROM staff;

SELECT DISTINCT dept, job FROM staff;

SELECT name, salary + comm AS pay FROM staff;

IBM DB2 9

8

Example

Given the following Query:

SELECT last_name, first_name, age, hire_date

FROM employee WHERE age > 40

What will be the command: To return the rows sorted by AGE, oldest first, and by

LAST_NAME, from A to Z

Answer: ORDER BY age DESC, last_name

IBM DB2 9

9

CASE Expressions In SELECT

SELECT empno, lastname, job, salary,

CASE WHEN job IN ('MANAGER‘, ‘ADMIN’) THEN salary * 1.10

WHEN job IN ('DBA‘, ‘ARCHITECT’) THEN salary * 1.08

WHEN job = 'PROGRAMMER' THEN salary * 1.05

ELSE salary * 1.035

END as new_salary

FROM employee

IBM DB2 9

10

ExampleSELECT quantity,CASE WHEN itemcode = '099' THEN 'SILVER'WHEN itemcode = '788' THEN 'GOLD'WHEN itemcode = '899' THEN 'PLATINUM'ELSE 'ERROR'ENDFROM supplier Query:SUPPLIER------------------------------------------QUANTITY ITEMCODE 3 099 4 099 1 788 1 899 5 009 3 788 1 899

Output3,SILVER4,SILVER

1,PLATINUM1,ERROR5,SILVER3,GOLD

1,PLATINUM

IBM DB2 9

11

With WHERE clause

Use the WHERE clause to select specific rows from a table or view.

Relational operators including:

=, >, <, >=, <=, <> The WHERE clause of SELECT statement can include:

- LIKE and NOT LIKE ‘_’ and ‘%’ wildcard character with LIKE/ NOT LIKE

- IS NULL and IS NOT NULL- BETWEEN and NOT BETWEEN- IN and NOT IN- AND, NOT, and OR Connectives in Complex Conditions

IBM DB2 9

12

Using Functions to Transform Data

Scalar functions - Also known as row functions- Operate on a single value to return another single value.

- ABS(), HEX(), LENGTH(), YEAR(), MONTH(), DAY(), LCASE(), LOWER(), UCASE() or UPPER()

Column functions - Also known as vector functions. - Work on a group of rows to provide a result. - SUM(), AVG(), MIN(), MAX(), COUNT()

IBM DB2 9

13

SELECT lastname, hiredate FROM employee WHERE MONTHNAME(hiredate) = 'December' AND salary NOT BETWEEN 20000.00 AND 90000.00

SELECT * FROM emp_exp_02 UNION SELECT * FROM emp_exp_01

SELECT empno, lastname FROM employee WHERE lastname LIKE 'S%' ORDER BY empno

SELECT COUNT(*) FROM employee WHERE workdept IN (SELECT deptno FROM department WHERE admrdept = 'A00')

SELECT workdept, DECIMAL(AVG(salary), 9, 2) AS avg_salary FROM employee GROUP BY work dept HAVING AVG(salary) > 60000

IBM DB2 9

14

Using Set Operators to Combine Multiple Queries

With DB2, it is possible to combine two or more queries into a single query by using a special operator known as a set operator. When a set operator is used, the results of each query executed are combined in a specific manner to produce a single result data set

UNION - the result data sets produced by each individual query are combined and all duplicate rows are eliminated

INTERSECT - common records that are found in both the result data sets will be shown as final result set.

EXCEPT - all records found in the first result data set that do not have a corresponding record in the second result data set will be shown as final result set.

IBM DB2 9

15

Using Joins - Concepts A join combines data from two or more tables. A join

condition is required to refine the result set (eliminates a cartesian product). Joining can be INNER or OUTER.

INNER JOIN - Keeps only the rows from the cross product that meet the join condition. If a row exists in both the tables then only they will be included in the result set.

OUTER JOIN is a concatenation of the inner join and rows from the left table, right table, or both tables that are missing from the inner join. There are 3 types of Outer Join.

1. Left outer join = inner join + rows from the left table 2. Right outer join = inner join + rows from the right table

3. Full outer join = inner join + the rows from both the tables

IBM DB2 9

16

Using Joins — Examples

SELECT lastname, deptname FROM employee e, department d WHERE e.workdept = d.deptno

ORSELECT lastname, deptname FROM employee e INNER

JOIN department d ON e.workdept = d.deptno SELECT lastname, deptname FROM employee e LEFT

OUTER JOIN department d ON e.workdept = d.deptno SELECT lastname, deptname FROM employee e RIGHT

OUTER JOIN department d ON e.workdept = d.deptno SELECT lastname, deptname FROM employee e FULL

OUTER JOIN department d ON e.workdept = d.deptno

IBM DB2 9

17

IBM DB2 9

18

IBM DB2 9

19

IBM DB2 9

20

IBM DB2 9

21

INSERT StatementThe INSERT statement is used to add new rows to a table or a view.

Examples:INSERT INTO staff VALUES (1212,'Cerny',NULL,'Sales',3)

INSERT INTO staff (id, name, dept, job, years)

VALUES (1212,'Cerny',20,'Sales',3),

(1213,'Wolfrum',20,NULL,2)

INSERT INTO department (deptno, deptname) SELECT deptno, deptname FROM sales_depts

IBM DB2 9

22

Example of inserting values in Table using values of other Table Given the following two tables:

TAB1 C1 C2 __ __________ 1 Antarctica 2 Africa 3 Asia 4 Australia

TAB2 CX CY ---- --------- 5 Europe 6 North America 7 South America

Query:Insert all rows found in table TAB2 into table TAB1.

Command:INSERT INTO tab1 SELECT cx, cy FROM tab2

IBM DB2 9

23

UPDATE StatementThe UPDATE statement is used to change the data in a table or a view.

For example:

UPDATE staff SET dept = NULL

WHERE ename LIKE ‘A%’

UPDATE staff SET (dept, sal)=(51, 7000)

WHERE id = 750

UPDATE employees SET (dept) =(SELECT deptname FROM department

WHERE deptno = 1)

IBM DB2 9

24

DELETE Statement

The DELETE statement is used to delete entire rows of data from a table.

For example:

DELETE FROM staff WHERE id IN (1212, 1213)

DELETE FROM sales WHERE salesperson IS NULL

IBM DB2 9

25

The COMMIT and ROLLBACK statements

A unit of work (UOW), also known as a transaction, is a recoverable sequence of operations within an application process. Ex. transfer funds transaction.

The application ends the UOW by issuing either a COMMIT or a ROLLBACK statement, whichever is appropriate.

The COMMIT statement makes all changes made within the UOW permanent, whereas the ROLLBACK statement reverses those changes.

If the application ends normally without an explicit COMMIT or ROLLBACK statement, the UOW is automatically committed.

If the application ends abnormally before the end of a UOW, that unit of work is automatically rolled back.

IBM DB2 9

26

Savepoints

A savepoint lets you selectively roll back a subset of actions that make up a UOW without losing the entire transaction.

do some work;

savepoint A;

do some more work;

savepoint B;

do even more work;

savepoint C;

wrap it up;

roll back to savepoint B;

IBM DB2 9

27

What is XML? XML was designed to structure, store and to send

data/information. At its core XML is text formatted in the form of tags and text

that follow a well-defined set of rules. This text may be stored/represented in:

- A normal file stored on disk- A message being sent over HTTP- A character string in a programming language- A CLOB (character large object) in a database- Any other way textual data can be used

<? xml version=“1.0” ?><purchaseOrder id=‘12345” secretKey=‘4x%$^’> <customer id=“A6789”> <name>John Smith Co</name> <address> <street>1234 W. Main St</street> <city>Toledo</city> <state>OH</state> <zip>95141</zip> </address> </customer> <itemList> <item> <partNo>A54</partNo> <quantity>12</quantity> </item> <item> <partNo>985</partno> <quantity>1</quantity> </item> </itemList></purchaseOrder>

IBM DB2 9

28

IBM DB2 9

29

IBM DB2 9

30

IBM DB2 9

31

XML specifications

XML documents

XSD

XQuery XPathXSLT

DTDdescribes

describes

searches searches transforms

uses

uses

uses

uses

supersedes

IBM DB2 9

32

IBM DB2 9

33

IBM DB2 9

34

IBM DB2 9

35

XPath Expressions

IBM DB2 9

36

IBM DB2 9

37

IBM DB2 9

38

XML Facilities XML data type for columns

- create s1.t1 (c1 int, c2 xml)

Language bindings for XML type in programming languages- cobol, c, java, etc..

XML indexes- create ix1 on s1.t1(c2) generate keys using pattern ‘/dept/emp/@empno’

An XML schema/DTD repository Support for XQuery as a primary language as well as:

- Support for SQL within XQuery- Support for XQuery with SQL- Support for new SQL/XML functions

Performance, scale, and everything else they expect from a DBMS

IBM DB2 9

39

Two ways to query XML data in DB2

Using XQuery as the primary language- All queries begin with XQuery.- Tells the DB2 parser what to expect- Can execute SQL within a query beginning with XQUERY

Using SQL as the primary language- Part 2

IBM DB2 9

40

XQuery: The FLWOR Expression FOR: iterates through a sequence, bind variable to items LET: binds a variable to a sequence WHERE: eliminates items of the iteration ORDER: reorders items of the iteration RETURN: constructs query results

create table dept(deptID char(8), deptdoc xml);xqueryfor $d in db2-fn:xmlcolumn(‘dept.deptdoc’)/deptlet $emp := $d//employee/namewhere $d/@bldg > 95 order by $d/@bldgreturn <EmpList> {$d/@bldg, $emp} </EmpList>

<dept bldg=101> <employee id=901> <name>John Doe</name>

<phone>408 555 1212</phone><office>344</office>

</employee><employee id=902>

<name>Peter Pan</name><phone>408 555 9918</phone><office>216</office>

</employee></dept>

Input:

IBM DB2 9

41

John DoePeter Pan

<namelist> <name>John Doe</name> <name>Peter Pan</name></namelist>

FLWOR Expression

<name>John Doe</name><name>Peter Pan</name>

xqueryfor $d in xmlcolumn(‘deptdoc’)/deptwhere $d/@bldg = 101return <namelist>

{$d/employee/name} </namelist>

This result is not an XML document!

xqueryfor $d in xmlcolumn(‘deptdoc’)/deptwhere $d/@bldg = 101return $d/employee/name/text()

xqueryfor $d in xmlcolumn(‘deptdoc’)/deptwhere $d/@bldg = 101return $d/employee/name

<dept bldg=“101”> <employee id=“901”> <name>John Doe</name>

<phone>408 555 1212</phone><office>344</office>

</employee><employee id=“902”>

<name>Peter Pan</name><phone>408 555 9918</phone><office>216</office>

</employee></dept>

create table dept (deptID char(8), deptdoc xml);

IBM DB2 9

42

Two ways to query XML data in DB2

Using XQuery as the primary language- Done

Using SQL as the primary language- Looking at query only- All queries begin with “Select”- XML Retrieval done with XMLQUERY (flags db2 parser to

switch to XML Processing)

IBM DB2 9

43

XML Functions XMLQUERY(). function that enables you to execute an XQuery

expression from within an SQL context. It returns an XML value, which is an XML sequence. This sequence can be empty or it can contain one or more items.

XMLTABLE(). returns a table from the evaluation of XQuery expressions; XQuery expressions normally return values as a sequence, however, XMLTABLE() allows you to execute an XQuery expression and return values as a table instead.

XMLEXISTS(). If it returns an empty sequence, XMLEXISTS returns FALSE; otherwise, TRUE is returned. The XMLEXISTS predicate can be used in the WHERE clauses of UPDATE, DELETE, and SELECT statements.

IBM DB2 9

44

DML statements for XML Data INSERT INTO customers (custinfo) VALUES (

XMLPARSE(DOCUMENT '<name>John Doe</name>' PRESERVE WHITESPACE))

DELETE FROM customer WHERE XMLEXISTS ('declare default element namespace "http://custrecord.dat"; $info/customerinfo[name/text()=" John Doe"]'

PASSING custinfo AS "info")

UPDATE customer SET custinfo = XMLPARSE (DOCUMENT ‘<name>Jane

Doe</name>’ PRESERVE WHITESPACE) WHERE empno = 11;

IBM DB2 9

45

XMLTABLE - XML to relational

<zip>95023</zip>USnull4711

<zip>33129</zip>USBobby1325

ZipZipTypeNameCID

SELECT X.* from XMLTABLE (‘db2-fn:xmlcolumn(“PORDERS.PO”)//customer’ COLUMNS “CID” INTEGER PATH ‘@id’, “Name” VARCHAR(30) PATH ‘name’, “ZipType” CHAR(2) PATH ‘zip/@type’, “Zip” XML PATH ‘zip’ ) AS “X”

IBM DB2 9

46

IBM DB2 9

47

Given the following queries:

SELECT c1 FROM tab1; SELECT c1 FROM tab2; Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated?

A. UNIONB. INTERSECTC. EXCEPTD. MERGE

IBM DB2 9

48

Given the following UPDATE statement:

UPDATE employees SET workdept = (SELECT deptno FROM department WHERE deptno = 'A01') WHERE workdept IS NULL

Which of the following describes the result if this statement is executed?

A. The statement will fail because an UPDATE statement cannot contain a subquery.

B. The statement will only succeed if the data retrieved by the subquery does not contain multiple records.

C. The statement will succeed; if the data retrieved by the subquery contains multiple records, only the first record will be used to perform the update.

D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.

IBM DB2 9

49

Given the following SALES table definition:

SALES_DATE DATE SALES_PERSON CHAR(20) REGION CHAR(20) SALES INTEGER

Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?

A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995

B. DELETE FROM sales WHERE YEAR(sales_date) = 1995

C. DROP * FROM sales WHERE YEAR(sales_date) = 1995

D. DROP FROM sales WHERE YEAR(sales_date) = 1995

IBM DB2 9

50

Given the following EMPLOYEEStable definition:

EMP ID INTEGER NAME CHAR(20) DEPT CHAR(10)

SALARY DECIMAL (10, 2) COMMISSION DECIMAL (8, 2)

Assuming the DEPT column contains the values 'ADMIN', 'PRODUCTION', and 'SALES', which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

A. SELECT name, dept FROM employees ORDER BY dept

B. SELECT name, dept FROM employees GROUP BY dept

C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)

D. SELECT name, dept FROM employees GROUP BY CUBE (dept)

IBM DB2 9

51

Given the following EMPLOYEEStable definition:

EMP ID INTEGER NAME CHAR(20) DEPT CHAR(10)

SALARY DECIMAL (10, 2) COMMISSION DECIMAL (8, 2)

Assuming the DEPT column contains the values 'ADMIN', 'PRODUCTION', and 'SALES', which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?

A. SELECT name, dept FROM employees ORDER BY dept

B. SELECT name, dept FROM employees GROUP BY dept

C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)

D. SELECT name, dept FROM employees GROUP BY CUBE (dept)

IBM DB2 9

52

Which of the following best describes a unit of work? A. It is a recoverable sequence of operations whose point of

consistency is established when a connection to a database has been established or when a mechanism known as a savepoint is created.

B. It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables.

C. It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated.

D. It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as a savepoint is created.

IBM DB2 9

53

A stored procedure has been created with the following statement:CREATE PROCEDURE proc1 (IN var1 VARCHAR(10), OUT rc INTEGER) SPECIFIC myproc LANGUAGE SQL … What is the correct way to invoke this procedure from the command line processor (CLP)?

A. CALL proc1 ('SALES', ?)

B. CALL myproc ('SALES', ?)

C. CALL proc1 (SALES, ?)

D. RUN proc1 (SALES, ?)

IBM DB2 9

54

Japanese

Hebrew

Thank You

English

MerciFrench

Russian

DankeGerman

GrazieItalian

GraciasSpanish

Obrigado Portuguese

Arabic

Simplified Chinese

Traditional Chinese

Tamil

Thai

Korean