Introduction
SQL is the internationally recognized standard language for dealing with data in relational databases. Developed by IBM, SQL became an international standard in 1986. The standard was updated in 1989, 1992, 1999, 2003, 2008, 2011, and 2016. It continues to evolve and gain capability. Database vendors continually update their products to incorporate the new features of the ISO/IEC standard. (For the curious out there, ISO is the International Organization for Standardization, and IEC is the International Electrotechnical Commission.)
SQL isn’t a general-purpose language, such as C++ or Java. Instead, it’s strictly designed to deal with data in relational databases. With SQL, you can carry out all the following tasks:
- Create a database, including all tables and relationships.
- Fill database tables with data.
- Change the data in database tables.
- Delete data from database tables.
- Retrieve specific information from database tables.
- Grant and revoke access to database tables.
- Protect database tables from corruption due to access conflicts or user mistakes.
About This Book
This book isn’t just about SQL; it’s also about how SQL fits into the process of creating and maintaining databases and database applications. In this book, I cover how SQL fits into the larger world of application development and how it handles data coming in from other computers, which may be on the other side of the world or even in interplanetary space.
Here are some of the things you can do with this book:
- Create a model of a proposed system and then translate that model into a database.
- Find out about the capabilities and limitations of SQL.
- Discover how to develop reliable and maintainable database systems.
- Create databases.
- Speed database queries.
- Protect databases from hardware failures, software bugs, and Internet attacks.
- Control access to sensitive information.
- Write effective database applications.
- Deal with data from a variety of nontraditional data sources by using XML.
Foolish Assumptions
I know that this is a For Dummies book, but I don’t really expect that you’re a dummy. In fact, I assume that you’re a very smart person. After all, you decided to read this book, which is a sign of high intelligence indeed. Therefore, I assume that you may want to do a few things, such as re-create some of the examples in the book. You may even want to enter some SQL code and execute it. To do that, you need at the very least an SQL editor and more likely also a database management system (DBMS) of some sort. Many choices are available, both proprietary and open source. I mention several of these products at various places throughout the book but don’t recommend any one in particular. Any product that complies with the ISO/IEC international SQL standard should be fine.
Take claims of ISO/IEC compliance with a grain of salt, however. No DBMS available today is 100 percent compliant with the ISO/IEC SQL standard. For that reason, some of the code examples I give in this book may not work in the particular SQL implementation that you’re using. The code samples I use in this book are consistent with the international standard rather than with the syntax of any particular implementation unless I specifically state that the code is for a particular implementation.
Conventions Used in This Book
By conventions, I simply mean a set of rules I’ve employed in this book to present information to you consistently. When you see a term italicized, look for its definition, which I’ve included so that you know what things mean in the context of SQL. Website addresses and email addresses appear in monofont so that they stand out from regular text. Many aspects of the SQL language — such as statements, data types, constraints, and keywords — also appear in monofont. Code appears in its own font, set off from the rest of the text, like this:
CREATE SCHEMA RETAIL1 ;
What You Don’t Have to Read
I’ve structured this book modularly — that is, it’s designed so that you can easily find just the information you need — so you don’t have to read whatever doesn’t pertain to your task at hand. Here and there throughout the book, I include sidebars containing interesting information that isn’t necessarily integral to the discussion at hand; feel free to skip them. You also don’t have to read text marked with the Technical Stuff icons, which parses out über-techy tidbits (which may or may not be your cup of tea).
How This Book Is Organized
SQL All-in-One Desk Reference For Dummies, 3rd Edition is split into eight minibooks. You don’t have to read the book sequentially; you don’t have to look at every minibook; you don’t have to review each chapter; and you don’t even have to read all the sections of any particular chapter. (You can if you want to, however; it’s a good read.) The table of contents and index can help you quickly find whatever information you need. In this section, I briefly describe what each minibook contains.
Book 1: SQL Concepts
SQL is a language specifically and solely designed to create, operate on, and manage relational databases. I start with a description of databases and how relational databases differ from other kinds. Then I move on to modeling business and other kinds of tasks in relational terms. Next, I cover how SQL relates to relational databases, provide a detailed description of the components of SQL, and explain how to use those components. I also describe the types of data that SQL deals with, as well as constraints that restrict the data that can be entered into a database.
Book 2: Relational Database Development
Many database development projects, like other software development projects, start in the middle rather than at the beginning, as they should. This fact is responsible for the notorious tendency of software development projects to run behind schedule and over budget. Many self-taught database developers don’t even realize that they’re starting in the middle; they think they’re doing everything right. This minibook introduces the System Development Life Cycle (SDLC), which shows what the true beginning of a software development project is, as well as the middle and the end.
The key to developing an effective database that does what you want is creating an accurate model of the system you’re abstracting in your database. I describe modeling in this minibook, as well as the delicate trade-off between performance and reliability. The actual SQL code used to create a database rounds out the discussion.
Book 3: SQL Queries
Queries sit at the core of any database system. The whole reason for storing data in databases is to retrieve the information you want from those databases later. SQL is, above all, a query language. Its specialty is enabling you to extract from a database exactly the information you want without cluttering what you retrieve with a lot of stuff you don’t want.
This minibook starts with a description of values, variables, expressions, and functions. Then I provide detailed coverage of the powerful tools SQL gives you to zero in on the information you want, even if that information is scattered across multiple tables.
Book 4: Data Security
Your data is one of your most valuable assets. Acknowledging that fact, I discuss ways to protect it from a diverse array of threats. One threat is outright loss due to hardware failures. Another threat is attack by hackers wielding malicious viruses and worms. In this minibook, I discuss how you can protect yourself from such threats, whether they’re random or purposeful.
I also deal extensively with other sources of error, such as the entry of bad data or the harmful interactions of simultaneous users. Finally, I cover how to control access to sensitive data and how to handle errors gracefully when they occur — as they inevitably will.
Book 5: SQL and Programming
SQL’s primary use is as a component of an application program that operates on a database. Because SQL is a data language, not a general-purpose programming language, SQL statements must be integrated somehow with the commands of a language such as Visual Basic, Java, C++, or C#. This book outlines the process with the help of a fictitious sample application, taking it from the beginning — when the need for a new application is perceived — to the release of the finished application. Throughout the example, I emphasize best practices.
Book 6: SQL and XML
XML is the language used to transport data between dissimilar data stores. The 2005 extensions to the SQL:2003 standard greatly expanded SQL’s capacity to handle XML data. This minibook covers the basics of XML and how it relates to SQL. I describe SQL functions that are specifically designed to operate on data in XML format, as well as the operations of storing and retrieving data in XML format.
Book 7: Database Tuning Overview
Depending on how they’re structured, databases can respond efficiently to requests for information or perform very poorly. Often, the performance of a database degrades over time as its structure and the data in it change or as typical types of retrievals change. This minibook describes the parts of a database that are amenable to tuning and optimization. It also gives a procedure for tracking down bottlenecks that are choking the performance of the entire system.
Book 8: Appendices
Appendix A lists words that have a special meaning in SQL:2016. You can’t use these words as the names of tables, columns, views, or anything other than what they were meant to be used for. If you receive a strange error message for an SQL statement that you entered, check whether you inadvertently used a reserved word inappropriately.
Appendix B is a glossary that provides brief definitions of many of the terms used in this book, as well as many others that relate to SQL and databases, whether they’re used in this book or not.
Icons Used in This Book
For Dummies books are known for those helpful icons that point you in the direction of really great information. This section briefly describes the icons used in this book.
The Tip icon points out helpful information that’s likely to make your job easier.
This icon marks a generally interesting and useful fact — something that you may want to remember for later use.
The Warning icon highlights lurking danger. When you see this icon, pay attention, and proceed with caution.
This icon denotes techie stuff nearby. If you’re not feeling very techie, you can skip this info.
Where to Go from Here
Book 1 is the place to go if you’re just getting started with databases. It explains why databases are useful and describes the different types. It focuses on the relational model and describes SQL’s structure and features.
Book 2 goes into detail on how to build a database that’s reliable as well as responsive. Unreliable databases are much too easy to create, and this minibook tells you how to avoid the pitfalls that lie in wait for the unwary.
Go directly to Book 3 if your database already exists and you just want to know how to use SQL to pull from it the information you want.
Book 4 is primarily aimed at the database administrator (DBA) rather than the database application developer or user. It discusses how to build a robust database system that resists data corruption and data loss.
Book 5 is for the application developer. In addition to discussing how to write a database application, it gives an example that describes in a step-by-step manner how to build a reliable application.
If you’re already an old hand at SQL and just want to know how to handle data in XML format in your SQL database, Book 6 is for you.
Book 7 gives you a wide variety of techniques for improving the performance of your database. This minibook is the place to go if your database is operating — but not as well as you think it should. Most of these techniques are things that the DBA can do, rather than the application developer or the database user. If your database isn’t performing the way you think it should, take it up with your DBA. She can do a few things that could help immensely.
Book 8 is a handy reference that helps you quickly find the meaning of a word you’ve encountered or see why an SQL statement that you entered didn’t work as expected. (Maybe you used a reserved word without realizing it.)