Muutke küpsiste eelistusi

E-raamat: SQL Cookbook

4.03/5 (409 hinnangut Goodreads-ist)
,
  • Formaat: PDF+DRM
  • Ilmumisaeg: 03-Nov-2020
  • Kirjastus: O'Reilly Media
  • Keel: eng
  • ISBN-13: 9781492077411
Teised raamatud teemal:
  • Formaat - PDF+DRM
  • Hind: 47,96 €*
  • * 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.
SQL CookbookSuurem pilt
  • Formaat: PDF+DRM
  • Ilmumisaeg: 03-Nov-2020
  • Kirjastus: O'Reilly Media
  • Keel: eng
  • ISBN-13: 9781492077411
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. 

You may know SQL basics, but are you taking advantage of its expressive power? This second edition applies a highly practical approach to Structured Query Language (SQL) so you can create and manipulate large stores of data. Based on real-world examples, this updated cookbook provides a framework to help you construct solutions and executable examples in severalflavors of SQL, including Oracle, DB2, SQL Server, MySQL, andPostgreSQL.

SQL programmers, analysts, data scientists, database administrators, and even relatively casual SQL users will find SQL Cookbook to be a valuable problem-solving guide for everyday issues. No other resource offers recipes in this unique format to help you tackle nagging day-to-day conundrums with SQL.

The second edition includes:

  • Fully revised recipes that recognize the greater adoption of window functions in SQL implementations
  • Additional recipes that reflect the widespread adoption of common table expressions (CTEs) for more readable, easier-to-implement solutions
  • New recipes to make SQL more useful for people who aren&;t database experts, including data scientists
  • Expanded solutions for working with numbers and strings
  • Up-to-date SQL recipes throughout the book to guide you through the basics
Preface xi
1 Retrieving Records 1(14)
1.1 Retrieving All Rows and Columns from a Table
1(1)
1.2 Retrieving a Subset of Rows from a Table
2(1)
1.3 Finding Rows That Satisfy Multiple Conditions
2(1)
1.4 Retrieving a Subset of Columns from a Table
3(1)
1.5 Providing Meaningful Names for Columns
4(1)
1.6 Referencing an Aliased Column in the WHERE Clause
5(1)
1.7 Concatenating Column Values
6(1)
1.8 Using Conditional Logic in a SELECT Statement
7(1)
1.9 Limiting the Number of Rows Returned
8(2)
1.10 Returning n Random Records from a Table
10(1)
1.11 Finding Null Values
11(1)
1.12 Transforming Nulls into Real Values
12(1)
1.13 Searching for Patterns
13(1)
1.14 Summing Up
14(1)
2 Sorting Query Results 15(14)
2.1 Returning Query Results in a Specified Order
15(1)
2.2 Sorting by Multiple Fields
16(1)
2.3 Sorting by Substrings
17(1)
2.4 Sorting Mixed Alphanumeric Data
18(3)
2.5 Dealing with Nulls When Sorting
21(6)
2.6 Sorting on a Data-Dependent Key
27(1)
2.7 Summing Up
28(1)
3 Working with Multiple Tables 29(38)
3.1 Stacking One Rowset atop Another
29(2)
3.2 Combining Related Rows
31(2)
3.3 Finding Rows in Common Between Two Tables
33(1)
3.4 Retrieving Values from One Table That Do Not Exist in Another
34(6)
3.5 Retrieving Rows from One Table That Do Not Correspond to Rows in Another
40(2)
3.6 Adding Joins to a Query Without Interfering with Other Joins
42(2)
3.7 Determining Whether Two Tables Have the Same Data
44(7)
3.8 Identifying and Avoiding Cartesian Products
51(1)
3.9 Performing Joins When Using Aggregates
52(5)
3.10 Performing Outer Joins When Using Aggregates
57(3)
3.11 Returning Missing Data from Multiple Tables
60(4)
3.12 Using NULLs in Operations and Comparisons
64(1)
3.13 Summing Up
65(2)
4 Inserting, Updating, and Deleting 67(24)
4.1 Inserting a New Record
68(1)
4.2 Inserting Default Values
68(2)
4.3 Overriding a Default Value with NULL
70(1)
4.4 Copying Rows from One Table into Another
70(1)
4.5 Copying a Table Definition
71(1)
4.6 Inserting into Multiple Tables at Once
72(2)
4.7 Blocking Inserts to Certain Columns
74(1)
4.8 Modifying Records in a Table
75(2)
4.9 Updating When Corresponding Rows Exist
77(1)
4.10 Updating with Values from Another Table
78(3)
4.11 Merging Records
81(2)
4.12 Deleting All Records from a Table
83(1)
4.13 Deleting Specific Records
83(1)
4.14 Deleting a Single Record
84(1)
4.15 Deleting Referential Integrity Violations
85(1)
4.16 Deleting Duplicate Records
85(2)
4.17 Deleting Records Referenced from Another Table
87(2)
4.18 Summing Up
89(2)
5 Metadata Queries 91(14)
5.1 Listing Tables in a Schema
91(2)
5.2 Listing a Table's Columns
93(1)
5.3 Listing Indexed Columns for a Table
94(1)
5.4 Listing Constraints on a Table
95(2)
5.5 Listing Foreign Keys Without Corresponding Indexes
97(3)
5.6 Using SQL to Generate SQL
100(2)
5.7 Describing the Data Dictionary Views in an Oracle Database
102(1)
5.8 Summing Up
103(2)
6 Working with Strings 105(64)
6.1 Walking a String
106(2)
6.2 Embedding Quotes Within String Literals
108(1)
6.3 Counting the Occurrences of a Character in a String
109(1)
6.4 Removing Unwanted Characters from a String
110(2)
6.5 Separating Numeric and Character Data
112(4)
6.6 Determining Whether a String Is Alphanumeric
116(4)
6.7 Extracting Initials from a Name
120(5)
6.8 Ordering by Parts of a String
125(1)
6.9 Ordering by a Number in a String
126(6)
6.10 Creating a Delimited List from Table Rows
132(4)
6.11 Converting Delimited Data into a Multivalued IN-List
136(5)
6.12 Alphabetizing a String
141(6)
6.13 Identifying Strings That Can Be Treated as Numbers
147(6)
6.14 Extracting the nth Delimited Substring
153(7)
6.15 Parsing an IP Address
160(2)
6.16 Comparing Strings by Sound
162(2)
6.17 Finding Text Not Matching a Pattern
164(3)
6.18 Summing Up
167(2)
7 Working with Numbers 169(36)
7.1 Computing an Average
169(2)
7.2 Finding the Min/Max Value in a Column
171(2)
7.3 Summing the Values in a Column
173(2)
7.4 Counting Rows in a Table
175(2)
7.5 Counting Values in a Column
177(1)
7.6 Generating a Running Total
178(1)
7.7 Generating a Running Product
179(2)
7.8 Smoothing a Series of Values
181(1)
7.9 Calculating a Mode
182(3)
7.10 Calculating a Median
185(2)
7.11 Determining the Percentage of a Total
187(3)
7.12 Aggregating Nullable Columns
190(1)
7.13 Computing Averages Without High and Low Values
191(2)
7.14 Converting Alphanumeric Strings into Numbers
193(3)
7.15 Changing Values in a Running Total
196(1)
7.16 Finding Outliers Using the Median Absolute Deviation
197(4)
7.17 Finding Anomalies Using Benford's Law
201(2)
7.18 Summing Up
203(2)
8 Date Arithmetic 205(34)
8.1 Adding and Subtracting Days, Months, and Years
205(3)
8.2 Determining the Number of Days Between Two Dates
208(2)
8.3 Determining the Number of Business Days Between Two Dates
210(5)
8.4 Determining the Number of Months or Years Between Two Dates
215(3)
8.5 Determining the Number of Seconds, Minutes, or Hours Between Two Dates
218(2)
8.6 Counting the Occurrences of Weekdays in a Year
220(11)
8.7 Determining the Date Difference Between the Current Record and the Next Record
231(6)
8.8 Summing Up
237(2)
9 Date Manipulation 239(74)
9.1 Determining Whether a Year Is a Leap Year
240(6)
9.2 Determining the Number of Days in a Year
246(3)
9.3 Extracting Units of Time from a Date
249(3)
9.4 Determining the First and Last Days of a Month
252(3)
9.5 Determining All Dates for a Particular Weekday Throughout a Year
255(6)
9.6 Determining the Date of the First and Last Occurrences of a Specific Weekday in a Month
261(7)
9.7 Creating a Calendar
268(13)
9.8 Listing Quarter Start and End Dates for the Year
281(5)
9.9 Determining Quarter Start and End Dates for a Given Quarter
286(7)
9.10 Filling in Missing Dates
293(8)
9.11 Searching on Specific Units of Time
301(1)
9.12 Comparing Records Using Specific Parts of a Date
302(3)
9.13 Identifying Overlapping Date Ranges
305(6)
9.14 Summing Up
311(2)
10 Working with Ranges 313(22)
10.1 Locating a Range of Consecutive Values
313(4)
10.2 Finding Differences Between Rows in the Same Group or Partition
317(6)
10.3 Locating the Beginning and End of a Range of Consecutive Values
323(3)
10.4 Filling in Missing Values in a Range of Values
326(4)
10.5 Generating Consecutive Numeric Values
330(3)
10.6 Summing Up
333(2)
11 Advanced Searching 335(34)
11.1 Paginating Through a Result Set
335(3)
11.2 Skipping n Rows from a Table
338(1)
11.3 Incorporating OR Logic When Using Outer Joins
339(2)
11.4 Determining Which Rows Are Reciprocals
341(2)
11.5 Selecting the Top n Records
343(1)
11.6 Finding Records with the Highest and Lowest Values
344(1)
11.7 Investigating Future Rows
345(2)
11.8 Shifting Row Values
347(3)
11.9 Ranking Results
350(1)
11.10 Suppressing Duplicates
351(2)
11.11 Finding Knight Values
353(6)
11.12 Generating Simple Forecasts
359(8)
11.13 Summing Up
367(2)
12 Reporting and Reshaping 369(66)
12.1 Pivoting a Result Set into One Row
369(3)
12.2 Pivoting a Result Set into Multiple Rows
372(5)
12.3 Reverse Pivoting a Result Set
377(2)
12.4 Reverse Pivoting a Result Set into One Column
379(3)
12.5 Suppressing Repeating Values from a Result Set
382(2)
12.6 Pivoting a Result Set to Facilitate Inter-Row Calculations
384(2)
12.7 Creating Buckets of Data, of a Fixed Size
386(2)
12.8 Creating a Predefined Number of Buckets
388(2)
12.9 Creating Horizontal Histograms
390(2)
12.10 Creating Vertical Histograms
392(2)
12.11 Returning Non-GROUP BY Columns
394(3)
12.12 Calculating Simple Subtotals
397(3)
12.13 Calculating Subtotals for All Possible Expression Combinations
400(10)
12.14 Identifying Rows That Are Not Subtotals
410(2)
12.15 Using Case Expressions to Flag Rows
412(2)
12.16 Creating a Sparse Matrix
414(2)
12.17 Grouping Rows by Units of Time
416(4)
12.18 Performing Aggregations over Different Groups/Partitions Simultaneously
420(2)
12.19 Performing Aggregations over a Moving Range of Values
422(7)
12.20 Pivoting a Result Set with Subtotals
429(5)
12.21 Summing Up
434(1)
13 Hierarchical Queries 435(24)
13.1 Expressing a Parent-Child Relationship
436(4)
13.2 Expressing a Child-Parent-Grandparent Relationship
440(4)
13.3 Creating a Hierarchical View of a Table
444(5)
13.4 Finding All Child Rows for a Given Parent Row
449(1)
13.5 Determining Which Rows Are Leaf, Branch, or Root Nodes
450(8)
13.6 Summing Up
458(1)
14 Odds 'n' Ends 459(48)
14.1 Creating Cross-Tab Reports Using SQL Server's PIVOT Operator
459(2)
14.2 Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator
461(2)
14.3 Transposing a Result Set Using Oracle's MODEL Clause
463(4)
14.4 Extracting Elements of a String from Unfixed Locations
467(3)
14.5 Finding the Number of Days in a Year (an Alternate Solution for Oracle)
470(2)
14.6 Searching for Mixed Alphanumeric Strings
472(2)
14.7 Converting Whole Numbers to Binary Using Oracle
474(3)
14.8 Pivoting a Ranked Result Set
477(4)
14.9 Adding a Column Header into a Double Pivoted Result Set
481(12)
14.10 Converting a Scalar Subquery to a Composite Subquery in Oracle
493(2)
14.11 Parsing Serialized Data into Rows
495(5)
14.12 Calculating Percent Relative to Total
500(2)
14.13 Testing for Existence of a Value Within a Group
502(3)
14.14 Summing Up
505(2)
A Window Function Refresher 507(28)
B Common Table Expressions 535(4)
Index 539
Anthony Molinaro is a SQL developer and database administrator with many years experience in helping developers improve their SQL queries. SQL is particular passion of Anthony's, and he's become known as the go-to guy among his clients when it comes to solving difficult SQL query problems. He's well-read, understands relational theory well, and has nine years of hands-on experience solving tough, SQL problems. Anthony is particularly well-acquainted with new and powerful SQL features such as the windowing function syntax that was added to the most recent SQL standard.

Robert de Graaf graduated as an Engineer, and worked in the Manufacturing industry after completing studies. While working as an Engineer, Robert discovered the power of statistics for solving real world problems, and completed a Masters in Statistics in time to benefit from the Data Science boom. He has worked for RightShip as their Senior Data Scientist since 2013, and is the author of Managing Your Data Science Projects.
Lisainfo e-raamatute kohta Lisainfo e-raamatute kohta
Püsilink: https://www.kriso.ee/db/97814920774112e.html
Märksõnad: