دانلود کتاب SQL for IBM i: A Database Modernization Guide

فهرست مطالب :

Front Cover\nTitle Page\nCopyright\nContents\nAcknowledgments\nWhy You Need This Book\n How Can You Get More Out of the Database?\n1: Meet the UMADB: Our Example Database\n The Students Table\n The Courses Table\n The Teachers Table\n The Classes Table\n The Grades Table\n Just a Few Tables, and So Many Problems\n In This Chapter, You’ve Learned ...\n2: A Data Manipulation Language Basics Recap\n Using the BETWEEN and IN Predicates\n Joining Tables\n The JOIN You’ve Been Using Without Realizing: INNER JOIN\n Start Joining Tables Left and Right\n “Just Get Me Everything”: FULL JOIN\n A Join Summary\n A Handful of Column Functions\n The COUNT Function\n Finding the Minimum and Maximum Values of a Column\n Sums and Averages Made Easy\n The Shape-shifting CAST Function\n Aggregating Data with GROUP BY\n The Two Flavors of INSERT\n Vanilla INSERT: Plain, Simple, and Kind of Boring\n Strawberry (or Whatever Your Favorite Ice Cream Flavor Is) INSERT\n Data Adjustments with UPDATE\n DELETE: A Blessing and a Curse\n In This Chapter, You’ve Learned ...\n3: A Data Definition Language Basics Recap\n Converting UMADB’s Physical Files to SQL Tables\n CREATE TABLE Fundamentals\n Moving a Step Closer to a Real SQL Table\n Adding a Unique, Self-Managed Key\n Providing Proper Default Values for Optional and Audit-Related Columns\n Time for Some Practice\n A First Step Toward Easier Data Access: Creating a Few Simple Views\n Why Do You Need Views?\n How Do You Create Views?\n Repeat After Me: A View Is Not a Logical File\n The Missing Piece: SQL Indexes\n A Side Note: Why Is It So Important to Keep the Source Statements Safe?\n Exploring a Few CREATE INDEX Options\n In This Chapter, You’ve Learned …\n4: Making the Database User-Friendly\n Using Longer Table and Column Names\n Time for Some Practice\n Hiding Database Complexity\n Using the Same Table Twice in a Query\n Using Sub-queries\n Using EXISTS to Omit Records on a SELECT Statement\n Checking Database Consistency with the Exception Join\n Time for Some (More) Practice\n Using CASE to Return “Friendlier” Column Values\n Using CASE in the ORDER BY Clause\n Using a Secondary Table to Produce Customizable Sorting Sequences\n In This Chapter, You’ve Learned ...\n5: Tidying Up the Database\n A Quick Refresher on Relational Databases\n Relational Databases and Their Use of Keys\n Primary Keys\n Unique Keys\n Foreign Keys\n Table Relationship Types\n Understanding Identifying and Non-Identifying Relationships\n Referential Integrity\n Entity Relationship Diagram Notation\n Solving UMADB’s Database Problems Through Normalization\n Data Anomalies\n Some Boring, Yet Important, Math Jargon\n Introducing the Normal Forms, Academic Version\n Normalizing UMADB to 3NF\n Data Modeling with Visual Paradigm\n Introducing Visual Paradigm Community Edition\n Time for Some Practice\n Creating the New and Improved UMADB\n Time for Some (More) Practice\n Data Modeling Is Fun, but Where’s the Code?\n Migrating the Data to the New, Improved UMADB\n Populating the Teacher Ranks Table\n Populating the Departments Table\n Creating the Persons Data\n Populating the Teachers Table\n Populating the Students Table\n Populating the Courses Table\n Creating the Class Definitions Data\n Creating the Classes per Year Data\n Creating the Class Enrollment per Year Data\n Populating the Sort Sequence Table\n Time for a Bit More Practice\n Hiding the Database Complexity (Again) by Recreating the Views\n In This Chapter, You’ve Learned ...\n6: Introducing Data-Centric Programming and SQL/PSM\n A Call from the Dean’s Office\n Introducing Data-Centric Programming\n An SQL/PSM Crash Course\n Variable Declaration\n Variable Assignment\n Flow Control\n Loop Control\n Calling Other SQL Routines, FOR Loops, and Cursors\n Dynamic SQL\n Compound Statements\n Error Handling\n In This Chapter, You’ve Learned ...\n7: Creating and Using Stored Procedures\n SPs, UDFs, and Triggers: When to Use Each\n Analyzing the SP’s Structure\n What’s in a Name?\n Procedure Parameters\n Procedure Properties\n Procedure Options\n Procedure Body\n Creating Your First Stored Procedure\n Time for Some Practice\n Data-returning Stored Procedures\n Creating Data-returning Stored Procedures\n Creating More Complex Data-returning Stored Procedures\n Time for Some (More) Practice\n Using Data-returning Stored Procedures\n How to Use the Result Sets Returned by a Stored Procedure\n And There’s Still Time for a Little More Practice\n In This Chapter, You’ve Learned ...\n8: Exploring User-Defined Functions\n Function Versus Procedure: The RETURNS Property\n Pick Your UDF Flavor\n A Couple of Simple UDFs to Get You Started\n Using Flow Control in a UDF\n UDF Overloading\n Testing the Get_Student_Id UDF\n Time for Some Practice\n A More Complex UDF: Calculating the Student GPA\n Creating a Generic “Translation” Table ...\n ... And a Couple of Functions to Access It\n Finding a Place for the Credit Hours per Class\n Creating the Calculate_GPA UDF\n Creating a Simple UDTF\n Anatomy of a UDTF\n Using a UDTF in a SELECT Statement\n Writing More Complex UDTFs\n Scheduled User Profile Review UDTF\n Using a UDTF’s Data in Another UDTF: Introducing the User Review Report\n In This Chapter, You’ve Learned ...\n9: Making a Trigger-Happy Database\n Introducing Database Triggers\n What Triggers a Trigger?\n Why Use Triggers?\n SQL Triggers’ Advantages\n SQL Trigger Mechanics\n A Simple Trigger Example\n Testing Your First Trigger\n Time for Some Practice\n Exploring More Complex Trigger Scenarios\n Using Triggers for Data Validation\n Testing the Teacher Salary Update Check Trigger\n Using Triggers for Auditing\n Testing the Trigger\n Using Triggers to Compose and Fill Columns\n Using INSTEAD OF Triggers to Supercharge Views\n Use Triggers with Care\n Triggers Are Like Salt\n In This Chapter, You’ve Learned ...\n10: Moving Business Validations to the Database\n Re-introducing DDL Constraints\n Unique Constraints\n Referential Constraints\n Check Constraints\n Enforcing More Complex Sets of Business Rules\n Keeping the Teachers’ Salaries in Check\n Tidying Up the Personnel (Records)\n Enforcing the “Informal” Integrity of the Courses Table\n In This Chapter, You’ve Learned ...\n11: Exploring the Database Catalog Information\n Data About Data and the System Catalog\n Where It All Starts: Tables\n In This Chapter, You’ve Learned ...\n12: Parting Gifts\n The Personnel Contact Information Update Scenario\n Creating a Table Based on a SELECT Statement\n Anatomy of the MERGE Statement\n A Quick-and-Dirty UPDATE-only Alternative to MERGE\n Common Table Expressions\n CTE Anatomy\n A Simple CTE Example\n Another CTE Example: Recreating the Student Grades Info View\n Never Stop Learning!\nIndex