1 / 46

SQL Jumpstart

SQL Jumpstart. Mark Holm Centerfield Technology. Goals. Get you started with SQL Show how SQL works with an existing environment Lay the groundwork for learning more. 2. Notes. V4R3 and higher syntax used in examples Examples show only a small subset of what can be done!. 3. Agenda.

rasul
Télécharger la présentation

SQL Jumpstart

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQLJumpstart Mark Holm Centerfield Technology

  2. Goals • Get you started with SQL • Show how SQL works with an existing environment • Lay the groundwork for learning more 2

  3. Notes • V4R3 and higher syntax used in examples • Examples show only a small subset of what can be done! 3

  4. Agenda • Explain why SQL is important • Compare SQL with traditional approaches • Data definition statements • Selecting data • Changing data • Using SQL day to day 4

  5. Why SQL? • Simplifies cross platform development • SQL generation can be automated • Allows quick and flexible access to data • Operates on “sets” of data -> powerful • Official industry standard used by pervasive APIs & products • ODBC/JDBC • Most query tools • Can perform better than traditional methods! 5

  6. Why SQL? • Business Intelligence • Query tools • Data manipulation & (some) data cleanup • Web sites & internet applications • Net.Data • JDBC • ERP applications • SSA eBPCS • JDE One World • Mapics XA 6

  7. Tools • Interactive SQL (STRSQL) • Run SQL statement (RUNSQLSTM) • Operations Navigator • Query tools (ODBC) • And many others….. 7

  8. Basic statements • Create Collection = CRTLIB • Create Table = CRTPF • Create View = CRTLF (no key) • Create Index = CRTLF (key) • Insert, Update, Delete = add, change, and remove records • Select = select records & columns to read 8

  9. Data definition (DDL) • Data definition statements create objects • Collections (libraries) • Tables (physical files) • Views (non-keyed logical files) • Indexes (keyed logical files) 9

  10. Create collection • Create collection = Create library • Example: CREATE COLLECTION HR • Objects created: • Library named ‘HR’ • Journal & journal receiver • Catalogs (e.g. SYSTABLES) 10

  11. DSPLIB of a newly created COLLECTION

  12. Create Table • Create table = Create physical file (CRTPF) • Objects created, etc.: • Physical file • File automatically journaled to QSQJRN • Example: Create a table to hold employee information 12

  13. Create table CREATE TABLE HR/EMPLOYEE (FIRST_NAME FOR COLUMN FNAME CHAR (20 ) NOT NULL, LAST_NAME FOR COLUMN LNAME CHAR (30 ) NOT NULL, EMPLOYEE_ID FOR COLUMN EMP_ID NUMERIC (6 , 0) NOT NULL WITH DEFAULT 0, DEPARTMENT FOR COLUMN DEPT DECIMAL (3 , 0) NOT NULL WITH DEFAULT, TITLE CHAR (30 ) NOT NULL WITH DEFAULT, HIRE_DATE FOR COLUMN HDATE DATE NOT NULL) 13

  14. Tables Dirty data can’t be inserted Marked as a ‘table’ Maximum 1 member No maximum size Default to reuse deleted rows Table is automatically journaled (collection) Physical files Dirty data can be inserted Not an SQL object No limit on members Default is to cap size Default to not reuse deleted records File is not automatically journaled Tables vs. Physicals 14

  15. Prompt for CREATE TABLE in Interactive SQL

  16. Create View • Create View = Create logical file (CRTLF) • Objects created: • “Logical file” • No keyed access path • Example: View to format an employees first & last name and identify employees hired less than 2 years prior to today 16

  17. Create View CREATE VIEW HR/NEWBIES (EMPLOYEE_NAME, DEPARTMENT, HIRE_DATE)AS SELECTconcat(concat(strip(last_name),','),strip(first_name)), department, hire_date FROMHR/EMPLOYEE WHERE (year(current date)-year(hire_date)) < 2 17

  18. DSPFD of a newly created VIEW

  19. View uses • Subset columns to users or applications • Create new columns for users or applications • Subset the rows returned (hide data) • Join tables together 19

  20. View Looks like logical file Never has an access path (index) Powerful data manipulation Slower OPEN time Views can reference views One member Logical File Is a logical file Can be keyed or non-keyed Basic data manipulation Faster OPEN time Logical files can not reference LF’s Many members Views vs. Logical files 20

  21. Create Index • Create Index = CRTLF (keyed logical) • Objects created: • “Logical file” • Physical access path (index) • Created to improve performance -- not for function • Example: Create an index over the employee identifier 21

  22. Create Index CREATE UNIQUE INDEX HR/EMPIDIX ONHR/EMPLOYEE (EMPLOYEE_ID ASC) 22

  23. Index Looks like logical file Powerful data manipulation Not usable as file One member Logical file Is a logical file Basic data manipulation Like a normal file Many members Indexes vs. Keyed logical files 23

  24. Deleting objects • Done with a DROP statement • DROP TABLE • DROP VIEW • DROP INDEX • By default these statements drop dependent objects unlike CL commands (e.g. you can’t delete a PF via DLTF if there are dependent logical files) 24

  25. Data Manipulation (DML) • Insert - add rows to a table • Update - update column values in a table's rows • Delete - delete rows in a table • Select - retrieve rows from one or more tables 25

  26. Insert • Insert = add a new row (record) • Works just like HLL verbs • Example: Add a couple of employees to our table in the human resources library 26

  27. Insert INSERT INTO HR/EMPLOYEE (FIRST_NAME, LAST_NAME, EMPLOYEE_ID, DEPARTMENT, TITLE, HIRE_DATE) VALUES ('Joe', 'Jones', 4793, 522, ’Hotshot Programmer', '1998-01-16') INSERT INTO HR/EMPLOYEE VALUES ('Jane', ’Smith', 3290, 712, ’Psychic Business Planner', '1999-12-01') 27

  28. Update • Update = change data in existing row • Works just like HLL verbs • Example: Give a 20% raise to one our star employees UPDATE HR/EMPLOYEE SET Salary = Salary * 1.2 WHERE Employee_id = 5228 28

  29. Delete • Delete = Remove one or more rows • Example: Fire the human resources department DELETEFROM HR/EMPLOYEE WHERE Department = 108 29

  30. Select • Select statement is the most complex and powerful SQL statement • If you know how to write good select statements you are well on your way to mastering the language 30

  31. Select • SELECT statement overview: Select column(s) From table(s) or view(s) Where selection criteria Group By grouping columns Having selection criteria for groups Order By sort order for result rows 31

  32. Select - example 1 • Selects all columns (designated by ‘*’) • Selects data from the HR/EMPLOYEE table • Select all rows SELECT*FROMHR/EMPLOYEE 32

  33. Select - example 2 • Selects only two columns • Selects data from the HR/EMPLOYEE table • Select only rows with EMPID *EQ 556 SELECTLNAME, FNAME FROMHR/EMPLOYEE WHEREEMPID = 556 33

  34. Select - example 3 SELECTShirtName, SKU, PRICE*.8 AS DISCPRICE FROMPRODLIB/INVENTORY WHERESYLE = ‘HAWIAN’ OR COLOR = ‘PINK’ • Selects two columns and calculates a third called DISCPRICE • Selects data from the INVENTORY table • Selects parts in a defined range 34

  35. Select - example 4 SELECTTERRITORY, SUM(SaleAmt), AVG(SaleAmt), COUNT(*) FROMPRODLIB/SALES WHERE COUNTRY = ‘USA’ GROUP BYTERRITORY HAVINGAVG(SaleAmt) > 10000.0 ORDER BY2 DESC • Rank sales in largest to smallest order summarized by territory. Only look at sales in the United States that average more than $10000. 35

  36. Useful functions 36

  37. More useful functions 37

  38. Embedded SQL • Combines the power of SQL with HLL’s like RPG, COBOL, or C • All of the benefits of HLL’s such as performance, complex logic, and control over data manipulation….with SQL’s capabilities for set at a time processing and dynamic selection 38

  39. Embedded SQL • The following statement is an example of what an embedded statement looks like: SELECT fname, lname, address FROM employee WHERE empid = :INPUTID • INPUTID is a ‘host variable’ 39

  40. Embedded SQL • Embedded SQL programs are pre-compiled and then compiled again • CRTSQLRPGI, CRTSQLCBLI • Pre-compiler identifies SQL with special tags to indicate start and end of SQL statement 40

  41. Source file SQL pre-compiler Temporary Source file HLL compiler Module or Program 41

  42. Embedded SQL C/Exec SQL C+ Update Employee C+ Set lname = :NewLN -- assign new last name C+ Where empId = :EmployId C/End-Exec ----------------------------------------------------- Exec SQL Update Employee Set lname = :NewLN Where empId = :EmployId End-Exec RPG COBOL 42

  43. To start learning SQL use ISQL or Operations Navigator Create a sample collection and tables Add, change, and delete data Try different select statements and functions to get a feel for the power of SQL Read and try examples listed in the book and internet resources in this presentation Create tables with short and long names Put related SQL objects in the same collection If selecting a small amount of data, create an index over the columns that most uniquely identify the data Be careful with UPDATE and DELETE statements without a WHERE clause Use the AS clause to give calculations understandable names Tips 43

  44. Other resources • Database Design and Programming for DB2/400 - book by Paul Conte • SQL for Smarties - book by Joe Celko • SQL Tutorial - www.as400network.com • AS/400 DB2 web site at http://www.as400.ibm.com/db2/db2main.htm • Publications at http://publib.boulder.ibm.com/pubs/html/as400/ • Our web site at http://www.centerfieldtechnology.com 44

  45. Summary • SQL is becoming increasingly important for many reasons • Basic DDL is very similar to DDS • Power and flexibility come with the DML statements -- in particular SELECT • There are many resources to help take you to the next level of understanding

  46. I hear and forget. I see and remember. I do and I understand. Kung Futse 551 B.C.

More Related