1 Introduction to SQL and database objectsIBM Information Management – Cloud Computing Center of Competence IBM Canada Labs © IBM Corporation

2 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

3 Supporting reading materialReading materials Getting started with DB2 Express-C eBook • Chapter 8: Working with database objects Database Fundamentals eBook Chapter 5: Introduction to SQL Getting started with IBM Data Studio for DB2 eBook • Chapter 4: Creating SQL and XQuery scripts © IBM Corporation

4 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

5 SQL Database objects Overview Schema Table View Index Synonyms / AliasDatabase application objects Sequences Triggers User Defined Functions (UDFs) Stored Procedures SQL © IBM Corporation

6 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

7 Database objects Schema Table View Index  Synonyms / AliasDatabase application objects Sequences  • Triggers User Defined Functions (UDFs) Stored Procedures These will be covered later in the course, in the topic about application development © IBM Corporation

8 Schemas To create a schema explicitly, use this statement:Schemas are name spaces for a collection of database objects All database objects (except public synonyms) are qualified by a two-part name: . A fully qualified object name must be unique To create a schema explicitly, use this statement:  CREATE SCHEMA Implicit schemas Uses the connection ID as the implicit schema Can be set with the SET SCHEMA command © IBM Corporation

9 Tables Tables store dataEMPLOYEE Table Value Firstname Age Country Peter 15 Singapore Susan 32 Australia Lastname Pan Austin ... Record (Row) Field (Column) Tables store data A table consists of data logically arranged in columns and rows Each column contains values of the same data type Each row contains a set of values for each column available © IBM Corporation

10 Creating a table CREATE TABLE myTable (col1 integer) myTable col1 120(artno name TABLE artists SMALLINT VARCHAR(50) not null, with default not null, logged, 'abc', classification bio picture ) in mytbls1 CHAR(1) CLOB(100K) BLOB(2M) not logged compact © IBM Corporation

11 Data Types © IBM Corporation

12 Large Objects Store large character strings, large binary strings, or files © IBM Corporation

13 Nulls A NULL represents an unknown stateUse NOT NULL on a column definition when that column must contain a known data value. Specifying a default value for NOT NULL columns may be useful CREATE TABLE ID NAME DEPT Staff ( SMALLINT NOT VARCHAR(9) , SMALLINT NOT NULL, NULL with default 10, JOB YEARS SALARY CHAR(5) , SMALLINT , DECIMAL(7,2) , COMM ) DECIMAL(7,2) with default 15 © IBM Corporation

14 System Catalog Tables Each database has its own system catalog tables/views They store metadata about the database objects You can query these tables just like any other tables Reside in three schemas: • SYSIBM - base tables, optimized for DB2 SYSCAT - views based on SYSIBM tables, optimized for ease of use SYSSTAT - database statistics Examples: • SYSCAT.TABLES, SYSCAT.INDEXES, SYSCAT.COLUMNS, SYSCAT.FUNCTIONS, SYSCAT.PROCEDURES © IBM Corporation

15 User temporary tables Tables in memory created by an applicationGood for performance Require a USER temporary table space Two types: Declared global temporary (DGTT) DECLARE GLOBAL TEMPORARY TABLE mydgtt (col1 int, col2 varchar(10)) Create global temporary (CGTT) CREATE GLOBAL TEMPORARY TABLE mycgtt (col1 int, col2 varchar(10)) © IBM Corporation

16 Views Virtual table derived from other tables or viewsPopulated when invoked, based on a SELECT statement Some views can be updatable, others are read-only CONNECT TO MYDB1 CREATE VIEW MYVIEW1 AS SELECT ARTNO, NAME, CLASSIFICATION FROM ARTISTS SELECT * FROM MYVIEW1 ARTNO NAME CLASSIFICATION ------ 10 HUMAN A 20 MY PLANT C 30 THE STORE E ... © IBM Corporation

17 Synonyms / Aliases Alternate name for a table or viewA synonym is also known as an alias Synonyms can be private or public Private synonyms have a schema_name as other db objects CREATE SYNONYM empinfo FOR employees Public synonyms do not have a schema_name CREATE PUBLIC SYNONYM empinfo FOR employees © IBM Corporation

18 Indexes Ordered set of pointers that refer to rows in a base table.They are based upon one or more columns but stored as a separate entity. © IBM Corporation

19 Good for performance and to guarantee uniqueness Indexes Good for performance and to guarantee uniqueness Index Characteristics: ascending or descending unique or non-unique compound cluster bi-directional (default behavior) Examples: create unique index artno_ix on artists (artno) © IBM Corporation

20 Sequence objects Generates a unique numeric valueUsed at the database level, independent of tables Example: CREATE SEQUENCE myseq START WITH 1 INCREMENT BY 1 CACHE 5 INSERT INTO t1 VALUES (nextval for myseq, ...) SELECT prevval for myseq FROM sysibm.sysdummy1 © IBM Corporation

21 Constraints Constraints allow you to define rules for the data in your table. There are different types of constraints: (with suggested prefixes to use for the names): PRIMARY KEY: _pk UNIQUE: _uq DEFAULT: _df CHECK: _ck FOREIGN KEY: _fk • © IBM Corporation

22 Constraints - Example CREATE TABLE EMPLOYEE (ID integer NOT NULL CONSTRAINT ID_pk PRIMARY KEY, NAME varchar(9), DEPT smallint CONSTRAINT dept_ck1 CHECK (DEPT BETWEEN 10 AND 100), JOB char(5) CONSTRAINT dept_ck2 CHECK (JOB IN ('Sales','Mgr','Clerk')), HIREDATE date, SALARY decimal(7,2), CONSTRAINT yearsal_ck CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500) ) © IBM Corporation

23 Referential Integrityor unique constraint create table employee (empno primary key (empno) foreign key (workdept) references department on delete restrict) in DMS01 © IBM Corporation

24 Referential Integrity – rules for deletion or updatescreate table employee (empno ... ... references department on delete restrict) When a row is deleted or updated in a parent table, what happens to a row in the dependant tables? Use the clause “on delete ” or “on update ” when defining the RI to set the behavior to use: Behavior Description NO ACTION Do not allow the delete or update to happen on parent row (enforce after other referential constraints). This is the default. RESTRICT Do not allow the delete or update to happen on parent row (enforce before other referential constraints) CASCADE Cascade delete of update of parent row to dependant rows SET NULL Set all dependant rows to NULL © IBM Corporation

25 Referential Integrity – Syntax 1CREATE TABLE DEPENDANT_TABLE ( ID integer CONSTRAINT id_fk REFERENCES BASE_TABLE(UNIQUE_OR_PRIMARY_KEY), NAME varchar(9), ... ); © IBM Corporation

26 Referential Integrity – Syntax 2CREATE TABLE DEPENDANT_TABLE ( ID integer, NAME varchar(9), ..., CONSTRAINT constraint_name FOREIGN KEY (ID) REFERENCES BASE_TABLE(UNIQUE_OR_PRIMARY_KEY) ); © IBM Corporation

27 Referential Integrity – Syntax 3CREATE TABLE ( DEPENDANT_TABLE ID INTEGER, NAME VARCHAR(9), ... ); ALTER TABLE DEPENDANT_TABLE ADD CONSTRAINT constraint_name FOREIGN KEY (ID) REFERENCES BASE_TABLE(UNIQUE_OR_PRIMARY_KEY); © IBM Corporation

28 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

29 Structured Query Language - SQL© IBM Corporation

30 Data Definition Language(DDL)Creates, modifies, deletes database objects Statements: CREATE, ALTER, DROP, Examples: DECLARE CREATE CREATE TABLE

31 Data Definition Language(DDL)Other examples: ALTER TABLE myTable ALTER COLUMN col1 set not null RENAME

32 Data Manipulation Language (DML)Retrieves, inserts, updates, and deletes database objects Statements: Examples: SELECT, INSERT, UPDATE, DELETE SELECT INSERT UPDATE DELETE * FROM employee INTO EMPLOYEE (name) values ('Peter') EMPLOYEE set name FROM employee = 'Paul' © IBM Corporation

33 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

34 select * from The SELECT statement select * from Select statement: Query Result from the query: Result set/table © IBM Corporation

35 Example: select * from bookdb2=> select * from book BOOK_ID TITLE EDITION YEAR PRICE ISBN PAGES AISLE DESCRIPTION B1 Getting st DB-A01 Teaches you the esse B2 Database F DB-A02 Teaches you the fund B3 Getting st DB-A01 Teaches you the esse B4 Getting st DB-A01 Teaches you the esse 4 record(s) selected. © IBM Corporation

36 select , … , from Example: select , … , from db2=> select book_id, title, edition, year, price, isbn, pages, aisle, description from book BOOK_ID TITLE EDITION YEAR PRICE ISBN PAGES AISLE DESCRIPTION B1 B2 B3 B4 Getting st Database F Getting st Getting st 3 1 2009 2010 280 DB-A01 300 DB-A02 298 DB-A01 278 DB-A01 Teaches you the esse Teaches you the fund Teaches you the esse Teaches you the esse 4 record(s) selected. © IBM Corporation

37 Projecting columns from a tableselect , from book db2 => select BOOK_ID TITLE B1 B2 B3 B4 book_id, title from book Getting started with DB2 Database Fundamentals Getting started with DB2 Getting started with WAS Express-C App Dev CE 4 record(s) selected. © IBM Corporation

38 Changing the order of the columnsselect , from book db2 => select title, book_id TITLE from book BOOK_ID B1 B2 B3 B4 Getting started with DB2 Database Fundamentals Getting started with DB2 Getting started with WAS Express-C App Dev CE 4 record(s) selected. © IBM Corporation

39 Restricting rows from a tableselect book_id, title from book WHERE predicate db2 => select book_id, title from WHERE book_id='B1' TITLE Getting started with DB2 Express-C book BOOK_ID B1 1 record(s) selected. © IBM Corporation

40 Comparison operators Equals to Greater than Lesser thanRestricting rows from a table (Continued) Comparison operators Equals to Greater than Lesser than Greater than or equal to Less than or equal to Not equal to = > < >= <= <> © IBM Corporation

41 Limiting Result Set Size select book_id, title from bookFETCH FIRST ROWS ONLY db2 => select book_id, title from book FETCH FIRST 2 ROWS ONLY TITLE Getting started with DB2 Express-C Database Fundamentals BOOK_ID B1 B2 2 record(s) selected. © IBM Corporation

42 predicate_1 AND predicate_2Restricting rows using multiple conditions select book_id, title from book where predicate_1 AND predicate_2 db2 => select book_id, title from book where book_id like 'B%' AND title like 'Getting%' BOOK_ID TITLE B1 Getting started with DB2 Express-C B3 App Dev B4 WAS CE 3 record(s) selected. © IBM Corporation

43 Selecting from multiple tablesselect , from 1>,

44 Selecting from multiple tables (Continued)db2 => select title, lastname, firstname from book, author_list, author where book.book_id=author_list.book_id and author_list.author_id=author.author_id and book.book_id='B1' TITLE LASTNAME FIRSTNAME ------ ----- Getting started with CHONG RAUL AHUJA RAV HAKES IAN 3 record(s) selected. © IBM Corporation

45 Correlation Names db2 => select title,lastname,firstname from bookB, author_list AL, author A where B.book_id=AL.book_id and and B.book_id ='B1' AL.author_id=A.author_id TITLE LASTNAME FIRSTNAME Getting started with CHONG RAUL AHUJA RAV HAKES IAN 3 record(s) selected. © IBM Corporation

46 Sorting the result-setdb2 => select title from book TITLE Getting started with DB2 Database Fundamentals Getting started with DB2 Getting started with WAS Express-C App Dev CE 4 record(s) selected. © IBM Corporation

47 Order By clause order by title db2 => select title from book TITLE Database Fundamentals db2 => select title from book Getting started with DB2 App Dev Express-C WAS CE 4 record(s) selected. © IBM Corporation

48 Order By clause (Continued)db2 => select title from book order by title desc TITLE Getting started with WAS CE Getting started with DB2 Express-C Getting started with DB2 App Dev Database Fundamentals 4 record(s) selected. © IBM Corporation

49 Order By clause (Continued)db2 => select title, pages order by 2 from book TITLE PAGES Getting started with WAS CE 278 DB2 Express-C 280 App Dev 298 Database Fundamentals 300 4 record(s) selected. © IBM Corporation

50 Eliminating Duplicatesdb2 => select country from COUNTRY AU BR ... CN CN ... IN IN IN ... RO RO author order by 1 20 record(s) selected. © IBM Corporation

51 Distinct clause db2 => select distinct(country) from author COUNTRYBR CA CN IN RO 6 record(s) selected. © IBM Corporation

52 Group by clause db2 => select country, count(country) fromauthor group by country COUNTRY 2 AU 1 BR 1 CA 3 CN 6 IN 6 RO 3 6 record(s) selected. © IBM Corporation

53 Group by clause (Continued)db2 => select country, count(country) as count from author group by country COUNTRY COUNT AU 1 BR CA 3 CN 6 IN RO 6 record(s) selected. © IBM Corporation

54 Having clause by country > 4 having count(country)db2 => select country, count(country) as count from author group by country > 4 having COUNTRY CN IN count(country) COUNT 6 2 record(s) selected. © IBM Corporation

55 where firstname like 'R%'String patterns db2 => select firstname from author where firstname like 'R%' FIRSTNAME RAUL RAV 2 record(s) selected. © IBM Corporation

56 String patterns (Continued)db2 => select firstname, lastname from author where FIRSTNAME lastname like '_ua%' LASTNAME Jiang Lin Dai Chi Run Quan Xuan Hua 3 record(s) selected. © IBM Corporation

57 Example: pages select >= 290 title, pages from AND pages <=300db2 => pages select >= 290 title, pages from AND pages <=300 book where TITLE PAGES Database Fundamentals 300 Getting started with DB2 App Dev 298 2 record(s) selected. © IBM Corporation

58 Searching in Ranges select , from where BETWEEN AND db2 => pages TITLE select title, pages from book where between 290 and 300 PAGES Database Fundamentals Getting started with DB2 App Dev 300 298 2 record(s) selected. © IBM Corporation

59 Example: db2 => insert into book values ('B5','For Example',5,NULL,24.99,' ',400,'DB- A12','Dummy Entry') DB20000I The SQL command completed successfully. © IBM Corporation

60 Searching for NULL select , from where IS NULL db2 => select book_id,title,year from book where year IS NULL BOOK_ID TITLE YEAR B5 For Example - 1 record(s) selected. © IBM Corporation

61 Searching for NOT NULL select , from where IS NOT NULL db2 => select book_id,title,year from book where year IS NOT NULL BOOK_ID TITLE B1 B2 B3 B4 YEAR 2009 2010 Getting started with DB2 E Database Fundamentals Getting started with DB2 A Getting started with WAS C 4 record(s) selected. © IBM Corporation

62 Example: db2 => select firstname, lastname, country from author where country='AU' OR country='BR' FIRSTNAME LASTNAME COUNTRY Xiqiang Ji AU Juliano Martins BR 2 record(s) selected. © IBM Corporation

63 country from country='CA' country='RO' author where Continued... db2 => select firstname, lastname, country from country='CA' country='RO' author where or country='IN' or or country='CN' © IBM Corporation

64 Searching for a set of valuesselect , from where IN (a set of values) db2 => select firstname, lastname, country from author where country IN ('AU','BR') FIRSTNAME LASTNAME COUNTRY Xiqiang Ji AU Juliano Martins BR 2 record(s) selected. © IBM Corporation

65 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

66 Adds new rows to a table or updatable viewsINSERT statements Adds new rows to a table or updatable views Inserting row on to updatable views, inserts data into underlying table Syntax: INSERT INTO [TableName | Viewname] <([ColumnName],...)> VALUES ([Value],...) © IBM Corporation

67 INSERT statements (continued)Example: INSERT INTO AUTHOR (AUTHOR_ID, LASTNAME, FIRSTNAME, , CITY, COUNTRY) VALUES VALUES clause specifies one or more data values to be added to the column(s) in the table or updatable view specified. © IBM Corporation

68 INSERT statements (continued)Syntax: INSERT INTO [TableName | ViewName] <([ColumnName],...)> [SELECT statement] Example: INSERT INTO AUTHOR_1 SELECT AUTHOR_ID,LASTNAME,FIRSTNAME, ,CITY,COUNTRY FROM AUTHOR WHERE FIRSTNAME = 'RAUL' © IBM Corporation

69 INSERT statement (continued)Example – Simple INSERT INSERT INTO AUTHOR VALUES Example – Inserting multiple rows INSERT INTO AUTHOR (AUTHOR_ID, LASTNAME, FIRSTNAME, VALUES , CITY, COUNTRY) © IBM Corporation

70 Modifies the data in a table or a view Syntax:UPDATE statement Modifies the data in a table or a view Syntax: UPDATE [TableName | ViewName] SET [[ColumnName]=[Value] [Condition]> | NULL DEFAULT, ...] Types Searched update Updates one or more rows in a table ● Positioned update Always embedded in a program – uses cursors ● © IBM Corporation

71 UPDATE statement (Continued)Example UPDATE AUTHOR SET LASTNAME = 'KATTA' FIRSTNAME = 'LAKSHMI' WHERE AUTHOR_ID = 'A2' Or SET (LASTNAME,FIRSTNAME) = ('KATTA','LAKSHMI') WHERE AUTHOR_ID = 'A2' Warning: If no WHERE clause is used, all rows will be updated! © IBM Corporation

72 UPDATE statement (Continued)Example to remove values UPDATE AUTHOR SET COUNTRY = NULL © IBM Corporation

73 Used to remove ROWS from a table or a view SyntaxDELETE statement Used to remove ROWS from a table or a view Syntax DELETE FROM [TABLEName | ViewName] Types Searched Delete Used to delete one or more rows in a table ● Positioned Delete Always embedded in a program – uses cursors ● © IBM Corporation

74 DELETE statement (Continued)Example DELETE FROM AUTHOR WHERE AUTHOR_ID IN ('A2','A3') Warning: If no WHERE clause is used, all rows will be deleted! © IBM Corporation

75 Agenda Overview Database objects SQL introduction The SELECT statementThe UPDATE, INSERT and DELETE statements Working with JOINs © IBM Corporation

76 Types of Joins • INNER JOIN • LEFT OUTER JOIN (or LEFT JOIN for short)RIGHT OUTER JOIN (or RIGHT JOIN for short) • FULL OUTER JOIN (or FULL JOIN for short) • CROSS JOIN © IBM Corporation

77 Result is the intersection of the two tablesINNER JOIN Result is the intersection of the two tables © IBM Corporation

78 LEFT JOIN © IBM Corporation

79 RIGHT JOIN © IBM Corporation

80 FULL JOIN © IBM Corporation

81 CROSS JOIN Cartesian product Simpler syntax © IBM Corporation

82 Joining Multiple TablesYou have to combine tables 2 at a time. SELECT B.LASTNAME, L.COPY_ID, C.STATUS FROM BORROWER B INNER JOIN LOAN L ON B.BORROWER_ID = L.BORROWER_ID INNER JOIN COPY C ON L.COPY_ID = C.COPY_ID © IBM Corporation

83 94 © IBM Corporation Thank you!