Introduction

SQL, or Structured Query Language, is the primary language used to communicate with relational databases. The goal of this book is to serve as a useful introductory guide to this essential language.

In an alternate universe, the title of this book might have been The Logic of SQL. This is because, like all computer languages, the language of SQL has much more to do with cold hard logic than with English vocabulary. Nevertheless, the word language has been retained in the title for a number of reasons. First, a certain language-based syntax in SQL distinguishes it from other computer languages. Unlike other languages, SQL employs many ordinary words, such as WHERE and FROM, as keywords in its syntax.

In the spirit of the language embedded in SQL, we’ve adopted an emphasis on language in our sequence of topics. With this book, you’ll learn SQL as you would learn English. SQL keywords are presented in a logical progression, from simple to more complex. In essence, this is an attempt to deal with language and logic simultaneously.

To learn any language, one must begin by hearing and remembering the actual words that form the basis of its utterance. At the same time, those words have a certain meaning that must be understood. In the case of SQL, the meaning has a great deal to do with logic.

One final reason for persisting with the title The Language of SQL rather than The Logic of SQL is that it simply sounds better. While there can be few literary pretensions in this type of book, the hope is that the presence of the word language in the title will generate some additional enthusiasm for a subject that is, after all, quite interesting.

Topics and Features

Even if you’re not yet familiar with SQL, suffice it to say that it is a complex language with many components and features. In this book, we’ll focus on one main topic:

Image How to use SQL to retrieve data from a database

To a lesser extent, we will also cover:

Image How to update data in a database

Image How to build and maintain databases

Image How to design relational databases

Image Strategies for displaying data after it has been retrieved

A number of features make this book unique among introductory SQL books:

Image You will not be required to download software or sit with a computer as you read the text.

Our intent is to provide examples of SQL usage that can be understood simply by reading the book. The text includes small data samples that allow you to clearly see how SQL statements work.

Image A language-based approach is employed to enable you to learn SQL as you would learn English.

Topics are organized in an intuitive and logical sequence. SQL keywords are introduced one at a time, allowing you to build on your prior understanding as you encounter new words and concepts.

Image This book covers the syntax of three widely used databases: Microsoft SQL Server, MySQL, and Oracle.

If there are any differences between these databases, the Microsoft SQL Server syntax is shown in the main text. Special “Database Differences” sidebars show and explain any variations in the syntax for MySQL or Oracle.

Image An emphasis is given to relevant aspects of SQL for retrieving data.

This approach is useful for those who need only to use SQL in conjunction with a reporting tool. In our final chapter, we’ll move beyond pure SQL to cover strategies for displaying data after it has been retrieved, including ideas on how to use crosstab reports and pivot tables. In the real world, these types of tools can substantially lessen the burden on the SQL developer and provide greater flexibility for the end user.

What’s New in the Third Edition

Here are some of the new features of this third edition:

Image Coverage of the latest database versions

All syntax and examples have been taken from the latest versions of the three main databases covered in this book: Microsoft SQL Server 2019, MySQL 8.0, and Oracle 18c.

Image Expanded coverage of common analytical tasks

The third edition adds new sidebars on common analytical tasks. While not essential to learning the language, these topics cover useful calculations and procedures such as calculating a median and creating fiscal calendars.

Image Expanded coverage of functions

This edition adds a number of new date/time and numeric function in Chapter 4, “Using Functions.” Chapter 6, “Selection Criteria,” adds a discussion of functions that select by sound. In Chapter 9, “Summarizing Data,” we have expanded our coverage of rank functions and partitions by adding material on analytic functions, a useful topic for the business analyst.

Image Excel Pivot Charts

In Chapter 20, “Using Excel,” we extend our discussion of Excel pivot tables to also include charts and pivot charts. This is useful for the analyst who wants to extend the power of SQL to visually explore data.

Image New datasets

As in the prior editions, each chapter has its own small set of data to use as examples. These datasets are revised in this edition to be more contemporary.

Image Improved Supplemental Materials

The supplemental materials on the companion website have been reorganized with setup scripts and SQL statements in separate files. These files are now organized by both table and chapter, making it easier for the reader to find any desired SQL statement. In addition, we’ve also added an Excel file with the source data seen in Chapter 20.

Plan of the Book

This book presents its topics in a unique sequence. The majority of SQL books run through their topics as if you were a database administrator who needs to create and design a database from scratch, then load the database with data, and then finally start to retrieve that data. In this book, we start right off with data retrieval, and then come around to database design in the final chapters. This is done as a motivational tactic, allowing you to quickly get into interesting topics related to data retrieval before having to deal with the more arcane subjects of indexes and foreign keys.

The 20 chapters in the book can be broken down into a number of broad sections:

Image Chapter 1 presents introductory material about relational databases that is necessary to understand before encountering the SELECT statement.

Image Chapters 2 through 5 begin an exploration of the SELECT statement, covering the basics of calculations, functions, and sorting.

Image Chapters 6 through 8 deal with selection criteria, from simple Boolean logic to conditional logic.

Image Chapters 9 and 10 explore ways to summarize data, from simple counts to more complex aggregations and subtotals.

Image Chapters 11 through 15 discuss ways to retrieve data from multiple tables via joins, subqueries, views, and set logic.

Image Chapters 16 through 18 move beyond the SELECT statement to focus on broader topics associated with relational databases, such as stored procedures, updates, and table maintenance.

Image Finally, Chapters 19 and 20 bring us back to the basics of database design and then to strategies for using Excel to further explore data beyond what is possible with SQL.

Appendixes A, B, and C provide information on how to get started with each of the three databases covered in the book: Microsoft SQL Server, MySQL, and Oracle.

Companion Website

A listing of all SQL statements in this book can be found at this site:

Image www.informit.com/store/language-of-sql-9780134658254

These seven files are provided:

Image Setup Script for Microsoft SQL Server

Image Setup Script for MySQL

Image Setup Script for Oracle

Image SQL Statements for Microsoft SQL Server

Image SQL Statements for MySQL

Image SQL Statements for Oracle

Image Chapter 20 Data

The three Setup Script files are TXT files that allow you to run a single script that will generate all the sample data used in this book. Instructions on how to execute the setup script are provided within each of the files.

The three SQL Statements files are TXT files that list all SQL statements in the book for each of these databases. After running the setup script, you will be able to execute statements found in the book and see the same output.

The Chapter 20 Data file is an Excel spreadsheet with the source data referenced in that chapter.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.94.99.173