4) Working With Db2 Data Using Sql And Xquery

IBM DB2 9

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

Present By: By

Ajay Shankar Khare Technical Consultant DB2 –IBM Academic Initiative WEBTEK LABS PVT LTD [email protected]

© 2008 IBM Corporation

IBM DB2 9

Section 4 - Working with DB2 Data using SQL and XQuery (23.5%)  Given a DML SQL statement, ability to identify results  Ability to use SQL to SELECT data from tables  Ability to use SQL to SORT or GROUP data  Ability to use SQL to INSERT, UPDATE, or DELETE data  Knowledge of transactions (i.e., COMMIT, ROLLBACK, and transaction boundaries)  Ability to call a procedure or invoke a user defined function  Given an XQuery statement, knowledge to identify results 2

IBM DB2 9

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.

3

-

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

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

IBM DB2 9

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

IBM DB2 9

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 6

IBM DB2 9

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;

7

IBM DB2 9

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

8

IBM DB2 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 9

IBM DB2 9

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

10

Output 3,SILVER 4,SILVER 1,PLATINUM 1,ERROR 5,SILVER 3,GOLD 1,PLATINUM

IBM DB2 9

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 11

IBM DB2 9

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() -

12

IBM DB2 9

 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 13

IBM DB2 9

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

IBM DB2 9

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 Full outer join = inner join + the rows from both the tables

3. 15

IBM DB2 9

Using Joins — Examples  SELECT lastname, deptname FROM employee e, department d WHERE e.workdept = d.deptno OR  SELECT 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 16

IBM DB2 9

17

IBM DB2 9

18

IBM DB2 9

19

IBM DB2 9

20

IBM DB2 9

INSERT Statement  The 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 21

IBM DB2 9

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 ---5 6 7

22

CY --------Europe North America 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

UPDATE Statement  The 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) 23

IBM DB2 9

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

24

IBM DB2 9

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

IBM DB2 9

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; 26

IBM DB2 9

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

27

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

<customer id=“A6789”> John Smith Co

<street>1234 W. Main St Toledo <state>OH 95141

<partNo>A54 12 <partNo>985 1

IBM DB2 9

28

IBM DB2 9

29

IBM DB2 9

30

IBM DB2 9

XML specifications DTD

describes

XML documents

supersedes XSD

describes uses

searches XQuery

searches XPath

XSLT

uses

uses

uses 31

transforms

IBM DB2 9

32

IBM DB2 9

33

IBM DB2 9

34

IBM DB2 9

XPath Expressions

35

IBM DB2 9

36

IBM DB2 9

37

IBM DB2 9

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

38

IBM DB2 9

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 -

39

Part 2

IBM DB2 9

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);

query or $d in db2-fn:xmlcolumn(‘dept.deptdoc’)/dept et $emp := $d//employee/name where $d/@bldg > 95 rder by $d/@bldg eturn <EmpList> {$d/@bldg, $emp} 40

Input:

<dept bldg=101> <employee id=901> John Doe 408 555 1212 344 <employee id=902> Peter Pan 408 555 9918 216

IBM DB2 9

<dept bldg=“101”> <employee id=“901”> John Doe 408 555 1212 344 <employee id=“902”> Peter Pan 408 555 9918 216

FLWOR Expression

create table dept (deptID char(8), deptdoc xml); xquery for $d in xmlcolumn(‘deptdoc’)/dept where $d/@bldg = 101 return {$d/employee/name} xquery for $d in xmlcolumn(‘deptdoc’)/dept where $d/@bldg = 101 return $d/employee/name John Doe Peter Pan

xquery for $d in xmlcolumn(‘deptdoc’)/dept where $d/@bldg = 101 return $d/employee/name/text() John Doe Peter Pan

This result is not an XML document! 41

John Doe Peter Pan

IBM DB2 9

Two ways to query XML data in DB2  Using XQuery as the primary language -

Done

 Using SQL as the primary language -

42

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

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

IBM DB2 9

DML statements for XML Data  INSERT INTO customers (custinfo) VALUES ( XMLPARSE(DOCUMENT 'John Doe' 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 ‘Jane Doe’ PRESERVE WHITESPACE) WHERE empno = 11; 44

IBM DB2 9

XMLTABLE - XML to relational 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”

45

CID

Name

ZipType

Zip

1325

Bobby

US

33129

4711

null

US

95023

IBM DB2 9

46

IBM DB2 9

 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. UNION  B. INTERSECT  C. EXCEPT  D. MERGE 47

IBM DB2 9

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

IBM DB2 9

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 49

IBM DB2 9

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) 50

IBM DB2 9

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) 51

IBM DB2 9

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

IBM DB2 9

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, ?) 53

IBM DB2 9

Grazie

Hebrew

Italian

Gracias

Spanish

Russian

Arabic

Traditional Chinese

Obrigado

Thank You

Portuguese

Merci

French

Danke

German

English

Thai Simplified Chinese

Japanese

54

Tamil

Korean