Muutke küpsiste eelistusi

E-raamat: Practical Oracle SQL: Mastering the Full Power of Oracle Database

5.00/5 (1 hinnangut Goodreads-ist)
  • Formaat: PDF+DRM
  • Ilmumisaeg: 19-Feb-2020
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484256176
Teised raamatud teemal:
  • Formaat - PDF+DRM
  • Hind: 61,74 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Lisa ostukorvi
  • Lisa soovinimekirja
  • See e-raamat on mõeldud ainult isiklikuks kasutamiseks. E-raamatuid ei saa tagastada.
Practical Oracle SQL: Mastering the Full Power of Oracle DatabaseSuurem pilt
  • Formaat: PDF+DRM
  • Ilmumisaeg: 19-Feb-2020
  • Kirjastus: APress
  • Keel: eng
  • ISBN-13: 9781484256176
Teised raamatud teemal:

DRM piirangud

  • Kopeerimine (copy/paste):

    ei ole lubatud

  • Printimine:

    ei ole lubatud

  • Kasutamine:

    Digitaalõiguste kaitse (DRM)
    Kirjastus on väljastanud selle e-raamatu krüpteeritud kujul, mis tähendab, et selle lugemiseks peate installeerima spetsiaalse tarkvara. Samuti peate looma endale  Adobe ID Rohkem infot siin. E-raamatut saab lugeda 1 kasutaja ning alla laadida kuni 6'de seadmesse (kõik autoriseeritud sama Adobe ID-ga).

    Vajalik tarkvara
    Mobiilsetes seadmetes (telefon või tahvelarvuti) lugemiseks peate installeerima selle tasuta rakenduse: PocketBook Reader (iOS / Android)

    PC või Mac seadmes lugemiseks peate installima Adobe Digital Editionsi (Seeon tasuta rakendus spetsiaalselt e-raamatute lugemiseks. Seda ei tohi segamini ajada Adober Reader'iga, mis tõenäoliselt on juba teie arvutisse installeeritud )

    Seda e-raamatut ei saa lugeda Amazon Kindle's. 

Write powerful queries using as much of the feature-rich Oracle SQL language as possible, progressing beyond the simple queries of basic SQL as standardized in SQL-92.

Both standard SQL and Oracle’s own extensions to the language have progressed far over the decades in terms of how much you can work with your data in a single, albeit sometimes complex, SQL statement. If you already know the basics of SQL, this book provides many examples of how to write even more advanced SQL to huge benefit in your applications, such as:
  • Pivoting rows to columns and columns to rows
  • Recursion in SQL with MODEL and WITH clauses
  • Answering Top-N questions
  • Forecasting with linear regressions
  • Row pattern matching to group or distribute rows
  • Using MATCH_RECOGNIZE as a row processing engine
The process of starting from simpler statements in SQL, and gradually working those statements stepwise into more complex statements that deliver powerful results, is covered in each example. By trying out the recipes and examples for yourself, you will put together the building blocks into powerful SQL statements that will make your application run circles around your competitors.


What You Will Learn
  • Take full advantage of advanced and modern features in Oracle SQL
  • Recognize when modern SQL constructs can help create better applications
  • Improve SQL query building skills through stepwise refinement
  • Apply set-based thinking to process more data in fewer queries
  • Make cross-row calculations with analytic functions
  • Search for patterns across multiple rows using row pattern matching
  • Break complex calculations into smaller steps with subquery factoring

Who This Book Is For

Oracle Database developers who already know some SQL, but rarely use features of the language beyond the SQL-92 standard. And it is for developers who would like to apply the more modern features of Oracle SQL, but don’t know where to start. The book also is for those who want to write increasingly complex queries in a stepwise and understandable manner. Experienced developers will use the book to develop more efficient queries using the advanced features of the Oracle SQL language.



IOUG Press cover treatment, Intermediate-Advanced user level
About the Author xiii
Acknowledgments xv
Introduction xvii
Part I: Core SQL 1(190)
Chapter 1 Correlating Inline Views
3(14)
Brewery products and sales
3(2)
Scalar subqueries and multiple columns
5(4)
Correlating inline view
9(7)
Outer joining correlated inline view
12(4)
Lessons learned
16(1)
Chapter 2 Pitfalls of Set Operations
17(22)
Sets of beer
18(2)
Set operators
20(7)
Set concatenation
21(4)
The three set operators
25(2)
Multiset operators
27(7)
Multiset union
28(2)
Multiset intersect
30(2)
Multiset except
32(2)
Minus vs. multiset except
34(4)
Lessons learned
38(1)
Chapter 3 Divide and Conquer with Subquery Factoring
39(18)
Products and sales data
40(1)
Best-selling years of the less strong beers
40(4)
Modularization using the with clause
44(11)
Multiple uses of the same subquery
47(4)
Listing column names
51(4)
Lessons learned
55(2)
Chapter 4 Tree Calculations with Recursion
57(16)
Bottles in boxes on pallets
57(3)
Multiplying hierarchical quantities
60(11)
Recursive subquery factoring
61(7)
Dynamic SQL in PUSQL function
68(3)
Lessons learned
71(2)
Chapter 5 Functions Defined Within SQL
73(14)
Table with beer alcohol data
73(2)
Blood alcohol concentration
75(2)
Function with PRAGMA UDF
77(3)
Function in the with clause
80(5)
Encapsulated in a view
83(2)
Lessons learned
85(2)
Chapter 6 Iterative Calculations with Multidimensional Data
87(20)
Conway's Game of Life
87(2)
Live neighbor count with the model clause
89(7)
Iterating generations
96(10)
Lessons learned
106(1)
Chapter 7 Unpivoting Columns to Rows
107(24)
Data received in columns
108(1)
Unpivoting to rows
108(12)
Do-it-yourself unpivoting
111(2)
More than one dimension and/or measure
113(7)
Using dimension tables
120(8)
Dynamic mapping to dimension tables
123(5)
Lessons learned
128(3)
Chapter 8 Pivoting Rows to Columns
131(14)
Tables for pivoting
132(2)
Pivoting single measure and dimension
134(5)
Do-it-yourself manual pivoting
138(1)
Multiple measures
139(5)
Multiple dimensions as well
141(3)
Lessons learned
144(1)
Chapter 9 Splitting Delimited Text
145(24)
Customer favorites and reviews
145(1)
Delimited single values
146(11)
Pipelined table function
147(5)
Built-in APEX table function
152(1)
Straight SQL with row generators
153(2)
Treating the string as a JSON array
155(2)
Delimited multiple values
157(11)
Custom ODCI table function
157(5)
Combining apex_string.split and substr
162(1)
Row generators and regexp_substr
163(2)
Transformation to JSON
165(3)
Lessons learned
168(1)
Chapter 10 Creating Delimited Text
169(22)
Delimited lists of products
169(2)
String aggregation
171(13)
Aggregate function listagg
172(1)
Aggregate function collect
173(4)
Custom aggregate function stragg
177(5)
Aggregate function xmlagg
182(2)
When it doesn't fit in a VARCHAR2
184(6)
Get just the first part of the result
185(1)
Try to make it fit with reduced data
186(1)
Use a CLOB instead of a VARCHAR2
187(3)
Lessons learned
190(1)
Part II: Analytic Functions 191(132)
Chapter 11 Analytic Partitions, Ordering, and Windows
193(22)
Sums of quantities
194(1)
Analytic syntax
195(8)
Partitions
197(2)
Ordering and windows
199(4)
Flexibility of the window clause
203(3)
Windows on value ranges
206(2)
The danger of the default window
208(5)
Lessons learned
213(2)
Chapter 12 Answering Top-N Questions
215(22)
Top-N of sales data
215(7)
Which kind of Top-3 do you mean?
217(1)
The sales data for the beer
218(4)
Traditional rownum method
222(1)
Analytic functions for ranking
222(3)
Fetch only the first rows
225(6)
Handling of ties
226(3)
What the row limiting clause cannot do
229(2)
Top-N in multiple partitions
231(4)
The lateral trick for the row limiting clause
233(2)
Lessons learned
235(2)
Chapter 13 Ordered Subsets with Rolling Sums
237(30)
Data for goods picking
238(2)
Building the picking SQL
240(24)
Solving picking an order by FIFO
240(6)
Easy switch of picking principle
246(2)
Solving optimal picking route
248(4)
Solving batch picking
252(10)
Finalizing the complete picking SQL
262(2)
Lessons learned
264(3)
Chapter 14 Analyzing Activity Logs with Lead
267(20)
Picking activity log
268(3)
Analyzing departures and arrivals
271(4)
Analyzing picking activity
275(8)
Complete picking cycle analysis
280(3)
Teaser: row pattern matching
283(3)
Lessons learned
286(1)
Chapter 15 Forecasting with Linear Regression
287(20)
Sales forecasting
288(17)
Time series
289(3)
Calculating the basis for regression
292(6)
Linear regression
298(3)
Final forecast
301(4)
Lessons learned
305(2)
Chapter 16 Rolling Sums to Forecast Reaching Minimums
307(16)
Inventory, budget, and order
307(4)
The data
310(1)
Accumulating until zero
311(4)
Restocking when minimum reached
315(7)
Lessons learned
322(1)
Part III: Row Pattern Matching 323(130)
Chapter 17 Up-and-Down Patterns
325(26)
The stock ticker example
325(2)
Classifying downs and ups
327(5)
Downs + ups = V shapes
332(9)
Revisiting if SAME is needed
338(3)
V + V = W shapes
341(8)
Overlapping W shapes
346(3)
Lessons learned
349(2)
Chapter 18 Grouping Data Through Patterns
351(20)
Two sets of data to group
351(1)
Three grouping conditions
352(17)
Group consecutive data
353(11)
Group until gap too large
364(3)
Group until fixed limit
367(2)
Lessons learned
369(2)
Chapter 19 Merging Date Ranges
371(18)
Job hire periods
371(7)
Temporal validity
375(3)
Merging overlapping ranges
378(10)
Attempts comparing to the previous row
379(2)
Better comparing to the maximum end date
381(5)
Handling the null dates
386(2)
Lessons learned
388(1)
Chapter 20 Finding Abnormal Peaks
389(22)
Web page counter history
389(2)
The counter data
391(6)
Patterns in the raw counter data
393(4)
Looking at daily visits
397(13)
Patterns in daily visits data
399(7)
More complex-patterns
406(4)
Lessons learned
410(1)
Chapter 21 Bin Fitting
411(24)
Inventory to be packed in boxes
411(2)
Bin fitting with unlimited number of bins of limited capacity
413(13)
Showing where box capacity is too small
422(4)
Bin fitting with limited number of bins of unlimited capacity
426(7)
Lessons learned
433(2)
Chapter 22 Counting Children in Trees
435(18)
Hierarchical tree of employees
435(2)
Counting subordinates of all levels
437(14)
Counting with row pattern matching
439(3)
The details of each match
442(6)
Fiddling with the output
448(3)
Lessons learned
451(2)
Index 453
Kim Berg Hansen is a database developer from Middelfart in Denmark. As a youngster he originally wanted to work with electronics, and he tried computer programming and discovered that the programs he wrote worked wellunlike the electronics projects he soldered that often failed. This led to a VIC-20 with 5 kilobytes RAM and many hours programming in Commodore Basic. Having discovered his talent, Kim financed computer science studies at Odense University with a summer job as sheriff of Legoredo, while learning methodology and programming in Modula-2 and C. From there he moved into consulting as a developer making customizations to ERP software. That gave him his first introduction to Oracle SQL and PL/SQL, with which he has worked extensively since the year 2000. His professional passion is to work with data inside the database utilizing the SQL language to the fullest to achieve the best application experience for his application users. With a background fitting programs into 5 KB RAM, Kim hates to waste computing resources unnecessarily.







Kim shares his experience and knowledge by blogging at the kibeha.dk website, presenting at various Oracle User Group conferences, and being the SQL quizmaster at the Oracle Dev Gym. His motivation comes from peers who say now I understand after his explanations, and from end users who cant live without his application coding. He is a certified Oracle OCE in SQL, and an Oracle ACE Director. 







Outside the coding world, Kim is married, loves to cook, and is a card-carrying member of the Danish Beer Enthusiasts Association.
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97814842561762e.html
Märksõnad: