The SQL Guide: From Fundamentals to Advanced, by Ari Hovi
Become adept at SQL by starting with the basics and progressing through advanced SQL topics, such as finding and eliminating duplicates, comparing tables, updating from another table, creating crosstab queries, and much more!
1.1 Structure
1.2. Processing
1.3 Integrity Rules
1.4 Database Design
2.1 Indexes and the Optimizer
2.2 Data Independence
2.3 Procedures and Triggers
2.4 Views
2.5 System Catalog
3.1 Components and Types of Usage of SQL
3.2 Some History of SQL and SQL Products
3.3 The Standards Situation
3.4 Product-Oriented or Portable SQL?
3.5 SQL and Reporting Tools
3.6 SQL and Data Warehousing
4.1 Tables
4.2 Conceptual and Physical Models
5.1 SQL Databases in Examples
5.2 Presentation of SQL Statements
5.3 Comments
6.1 Fundamentals
6.2 Selecting Rows: The WHERE Clause
6.3 String Searches
6.4 Functions
6.5 Dates
6.6 GROUPING
6.7 Calculation with Columns
7.1 Join Syntax
7.2 ”Traditional” Join Syntax
7.3 The Advantages of the Join Syntax
7.4 Outer Join
7.5 Join and Group By Together
9.1 Basic Subqueries
9.2 Correlated Subqueries – Connecting Subqueries to the Main Query
10.1 Join or Subquery
10.2 “Does not Belong” Type Queries
10.3 Summary of the SELECT Statement
10.4 Advice on How to Build a SELECT Statement
10.5 Typical SELECT Statement Mistakes
11.1 Creating Tables Introduction
11.2 Data Types
11.3 Creating Tables
11.4 Auto Increment Primary Key
11.5 Altering Tables
11.6 Name Changes
11.7 Temporary Tables
11.8 Dropping Tables
12.1 Adding Rows to a Table
12.2 Update
12.3 Delete
13.1 What is a Transaction?
13.2 The ACID Properties of Transactions
13.3 Transactions in Different Products
14.1 A View with one Table
14.2 Complex Views
14.3 SQL View Rules
14.4 Updating Tables via a View
14.5 Check Option
14.6 Dropping Views
14.7 Synonyms and Aliases
15.1 What is an index?
15.2 Creating an Index
15.3 Removing an Index
16.1 Users and Roles
16.2 Examples
17.1 Oracle’s System Catalog Tables
17.2 System Catalog of SQL Server, Snowflake and PostgreSQL
17.3 DB2 System Catalog
17.4 MySQL System Catalog
18.1 NULL Handling, Conditional Reasoning and Converting
18.2 Special Cases of Character Searches
18.3 Find the First Word of a String
18.4 Adding Leading Zeros
18.5 How to Extract Year + Month from a Date
18.6 Calculating Age from Date of Birth
18.7 Finding Duplicate Rows
18.8 Removing Duplicate Rows
19.1 A Subquery as a Table, Derived Table
19.2 A Subquery in the SELECT List
19.3 Outer Join – Some Deeper Analysis
19.4 Cartesian product
19.5 Division – All Included
19.6 ANY and ALL Subqueries
19.7 Comparing Tables
19.8 Handling Hierarchies and Self Joins
19.9 “Most of” Queries
19.10 At Most n or None
19.11 Replacing a Correlated Subquery with a Derived Table
19.12 Building a Star Schema Join
20.1 Statistical Functions
20.2 Cumulative Sum
20.3 Grouping without GROUP BY
20.4 Grouping Salaries in Categories
20.5 Fetch Top n
20.6 Samples: Fetch Every Tenth Row
20.7 Rankings, Partitions, Values from Previous on Next Row
20.8 Conditions to Formulate the Result Set
20.9 Detail and Sum on the Same Row
20.10 Grand Total and Subtotals
20.11 Crosstab Query
20.12 Formulation of Column Alias Name
21.1 Updating Data from Another Table
21.2 Insert and Update at the Same Time
21.3 Generating New Rows from the Same Table
21.4 Swapping Column Data
21.5 Updating a Column with a Value Dependent on a Value in Other Column
22.1 SQL and Performance
22.2 The Optimizer
22.3 Multicolumn Indexes
22.4 Examples of Slow SQL Statements
Become adept at SQL by starting with the basics and progressing through advanced SQL topics, such as finding and eliminating duplicates, comparing tables, updating from another table, creating crosstab queries, and much more!
Over 300 examples allow you to practice and apply to your own projects. A training database accompanies this book, along with instructions on completing the exercises in an easy-to-build online environment.
Many database courses used the previous version of this book. This book has been refined with these teaching experiences to have an even broader appeal for use in academia.
The book covers seven popular databases: SQL Server, Oracle, PostgreSQL, MySQL, DB2, Snowflake, and Hive. Examples cover SQL dialect variations. We also provide hints for Databricks, MariaDB, Netezza, SQL database in Microsoft Fabric, and Amazon Redshift.
The best book for beginners, yet also the best book for SQL practitioners looking to skill up on advanced techniques and build superfast queries. The book contains a lot of useful suggestions and pointers to avoid costly mistakes and save time!
Ari Hovi has almost 40 years of experience in SQL, databases, and data modeling. Working as an independent consultant on various projects, he understands how to use SQL well. He has taught his beginner and advanced SQL courses to over two thousand participants, and he has used many products, including SQL Server, DB2, Oracle, PostgreSQL, and MySQL. Ari’s previous SQL book was a local bestseller with 13 editions. He has also written other books about database design, Microsoft Access, and data warehousing. He is also a cofounder of the Ellie data modeling tool.
Please complete all fields.
