SQL For Dummies

by Allen G. Taylor

Overview

Get ready to make SQL easy!

Updated for the latest version of SQL, the new edition of this perennial bestseller shows programmers and web developers how to use SQL to build relational databases and get valuable information from them. Covering everything you need to know to make working with SQL easier than ever, topics include how to use SQL to structure a DBMS and implement a database design; secure a database; and retrieve information from a database; and much more.  

SQL is the international standard database language used to create, access, manipulate, maintain, and store information in relational database management systems (DBMS) such as Access, Oracle, SQL Server, and MySQL. SQL adds powerful data manipulation and retrieval capabilities to conventional languages—and this book shows you how to harness the core element of relational databases with ease.

• Server platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems
• Find great examples on the use of temporal data
• Jump right in—without previous knowledge of database programming or SQL

As database-driven websites continue to grow in popularity—and complexity—SQL For Dummies is the easy-to-understand, go-to resource you need to use it seamlessly.

Product Details

ISBN-13:	9781119527091
Publisher:	Wiley
Publication date:	11/20/2018
Sold by:	JOHN WILEY & SONS
Format:	eBook
Pages:	512
File size:	3 MB

About the Author

Allen G. Taylor is a 30-year veteran of the computer industry and the author of over 40 books, including SQL For Dummies and Crystal Reports For Dummies. He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider.

Read an Excerpt

Chapter 16
SQL on the Internet

In This Chapter

• Comparing the Internet with a local access network
• Discussing client/server and Web architectures
• Defining the role of SQL on the Internet

---

The Internet, and particularly that portion of it known as the World Wide Web, has mushroomed in importance in the last couple of years. Just about every aspect of computing seems to be viewed in light of how it relates to the Web. Database is no exception. The World Wide Web lives up to its name. It provides a web of connectivity that envelops the globe. Anyone anywhere who has an Internet connection can access data residing on a Web server on the other side of town or, just as easily, on the other side of the world.

The ability to make your data available to anyone anywhere in the world opens up a whole new kind of database usage. This new usage, database publishing, is more akin to book publishing or radio broadcasting than it is to the point-to-point communication characteristic of operations on a local area network. The information you make available on the Web can be accessed and used by thousands or even millions of people that you will never meet. The most popular sites on the Web today receive more than a million visits, or hits, a day. You can make a substantial impact with your Web-based database, even if you are nowhere near that league.

The LAN is SQL's Ancestral Home

SQL was originally created by IBM to facilitate communication between large databases residing on mainframe computers and users on client machines that were connected to those mainframes by a local area network (LAN). SQL gradually became a defacto standard means, and then an official ANSI and ISO standard means, of communicating between users and databases. Companies producing relational databases designed to operate across local area networks embraced the SQL standard and made it the communications medium of choice on systems in which the user was located on a different machine from the database, with a LAN running between them.

SQL, coupled with ODBC, enabled an application running on a user's machine to simultaneously access data located on two or even more server machines. This combination proved to be a great boon to organizations whose information processing infrastructure had grown up over time without the benefit of centralized planning. Different machines, running different operating systems and different applications, could share information. Marvelous as this kind of flexibility is, it pales in comparison with what is possible over the Internet.

How the Internet Differs from a Classic LAN

A local area network (LAN) is a collection of computers that are all in physical proximity (that's where the local comes from). The computers, forming nodes on the network, are interconnected by wired or wireless communication links. Many local area networks are small, having anywhere from 10 to 50 nodes. Large organizations may be served by LANs that have more than a thousand nodes. In either case, you can exercise some centralized control over the network. This makes specifying a proprietary database interface possible, and you can expect all the users to be using access tools that are compatible with it.

The Internet is an entirely different story. It has millions of nodes, and they are not in physical proximity. No one has centralized control over what goes on. In this environment, the owner of a database server cannot make any assumptions about what kind of access tools the user has. The user has a Web browser, possibly supplemented with a plug-in that hosts the client end of a client-server database system. Because the most popular Web browsers run on all the popular client platforms, the client software does not have to be specifically tailored to run on a specific back-end database.

Note: The ordinary Web browser, such as Netscape Navigator or Microsoft Internet Explorer, comes close to being that Holy Grail of database access, the Universal Front End. If it existed, the Universal Front End would interface seamlessly with any database server that you want. It would allow the user to create tables easily, manipulate data, and operate database applications regardless of what kind of server the database is on or what kind of DBMS is controlling it. By itself, a browser cannot do this, of course. But by downloading the appropriate Netscape plug-in or ActiveX component (see Chapter 15 for more about these) before attempting to deal with the database, the browser can come very close. When a connection is established, state-of-the-art database publishers check the client machine for the appropriate plug-in. If they find it, they download the client part of their application and proceed. If they do not find the appropriate plug-in, they download the plug-in, followed by the client part of their application. This whole sequence can be relatively transparent to the user.

Two areas where operation on the Internet may differ significantly from operation on a LAN are network protocol and security. If you are considering allowing remote access to your database from over the Internet, you should carefully consider the impact of these two aspects of operation.

Network protocol

In order for the nodes on a network to communicate with each other, they must all speak the same "language." When one node sends a message, it must be formatted in such a way that the intended receiving node can understand it and take appropriate action. The people who first hooked personal computers together to form local area networks were not concerned with making their systems compatible with the Internet. At that time, the Internet was running only on large mainframe computers that ran the UNIX operating system and that were located at government organizations and research universities. The personal computer world seemed far removed from that of the mainframes used by "big science." Consequently, the "languages," or protocols, that were developed for PC LANs were different from what the Internet used.

Today, many PC LANs still operate with protocols that have evolved from those early PC protocols. The IPX/SPX protocol and the NetBEUI protocol are probably the most common of these. In contrast, the Internet uses a protocol named TCP/IP (Transmission Control Protocol/Internet Protocol). Anyone who wants to engage in database operations over the Web must do so using TCP/IP. Generally, this doesn't require any kind of a hardware change, but it can require a software reconfiguration.

Security

Security is a much bigger issue on the Internet than it is on any organizational LAN. On a LAN, you can be reasonably sure that no one is going to purposely try to sabotage your system. On the Internet, that would be a very foolish and dangerous assumption to make. All kinds of people are out there on the Internet, and some of them may want to hurt you -- just for the sheer, twisted fun of it. Competitors or even enemies may have stronger reasons to give you trouble. When you are exposing your database server to the Internet, you must take significant extra precautions, beyond what would be normal for a LAN.

The principal defense against attacks by hackers or other malefactors on the Internet is to install a firewall between your organizational network and the Internet. A firewall is a software system, or combination of hardware and software, that insulates your network from the Internet. All traffic, both in and out, must pass through the firewall. The firewall authenticates the packets passing through it according to standards that you set up. It passes packets that meet your criteria and throws away those that don't. It also allows you to monitor traffic for suspicious activity and to trace attempts at breaching your security.

When you make the decision to take the big step of putting your server on the Internet, be sure to provide adequate protection to sensitive information that you do not want inquisitive outsiders to know or malicious outsiders to damage.

From Client/Server to Internet-based Database

Most database systems found on LANs are structured according to client/server architecture. Data is stored on one or more servers whose specific task is providing access to that data. Smaller, client machines are spread throughout the organization. They host the user interface of the applications that access the database. Users, interacting with the client part of the application, access the data on the server by communicating over the LAN.

Compelling reasons exist to make database data available over the Internet. A commercial enterprise may want certain of its operational data to be available to vendors or customers with which it works closely. Such an enterprise may want to make detailed information about its products available to the general public, in hopes that some of them will become customers. Entities that are in the information dissemination business, such as libraries, may want to make their information available to a wider audience than those who are able to make a physical visit. For these and other reasons, many groups have decided to establish a presence on the Internet.

Beyond putting up a simple Web page, many organizations are engaging in database publishing, making selected internal information available to those who access their Web site. Some such information is freely available to anyone who logs in to the Web site. Using passwords, publishers can restrict access to authorized users, enabling them to access proprietary databases on the site or databases for which a fee is being charged.

The client/server architecture provides many of the key ingredients of a successful Web database publishing installation. Clients on the Web have similar equipment and operating environments to what is typical for clients on a corporate LAN. The database server of a Web-based system is no different from what serves that purpose on a LAN. Yes, you must address protocol and security issues, but good solutions exist for both. Investigating how client/server architecture may be applied to Web database publishing makes sense.

Two-tier client/server architecture

The original implementation of client/server computing on PC LANs used a two-tier architecture. This architecture had two main elements -- the database client and the database server -- connected by the LAN. You can implement a two-tier client/server system in several ways. One way, the so-called fat client architecture, places most of the computational burden on the client machine and relatively little on the server. A second major architecture is the thin client (also called fat server) model. Here, most of the computation is done by the server, and the client provides little more than the user interface. Figure 16-1 is a schematic representation of a two-tier client/server system.

Regardless of how a two-tier client/server system is implemented, all the necessary functions are performed by either the database client software on the client machine or by the database server software on the server machine.

Three-tier client/server architecture

Three-tier client/server architecture is a relatively new development that is rapidly replacing the older two-tier model. It adds another functional block or level to the server side of the system. This new functional block, often called middleware, assumes some of the responsibilities normally handled by both the database client and the database server, allowing both of them to be thinner. Thinning the client is good, because potentially so many of them exist, and the less capable the client machines need to be, the cheaper overall the system will be. Thinning the database server is also good because, when freed of computational tasks, the server can concentrate on moving data into and out of the database, speeding up operations. The higher level of modularization in a three-tier system also makes maintenance and troubleshooting easier. Figure 16-2 is a schematic representation of a three-tier client/server system.

Two-tier Web architecture

The traditional architecture of the World Wide Web can also be viewed as a two-tier structure. A Web server hosts HTML (HyperText Markup Language) pages, which are accessible over the Internet to Web browsers running on client machines. This architecture is similar to a two-tier client/server system in that the Web browser on an Internet client performs the same function as the user interface running on a client/server database client. The Web server performs a similar job to that of the client/server database server -- dispensing information. The main differences are that a Web browser is thinner than even the thinnest database client in a thin-client client/server system, and a Web server is incapable of the database manipulation required of even a thin-server implementation of a client/server system. This state of affairs is fine as long as you are not trying to perform database operations over the Web. If all you are doing is putting HTML pages up for people to read, you don't need to do anything more. Figure 16-3 shows the structure of a two-tier Web system.

Three-tier Web database architecture

To effectively perform database operations over the Web, you must combine elements from a two-tier client/server system with elements from a two-tier Web system to produce a composite three-tier solution. On the client side, the Web browser, perhaps enhanced by a Netscape plug-in or ActiveX component, provides the database application user interface. On the server side, the database server interfaces directly with the data source, just as it does in a classic client/server system.

The three-tier Web database architecture differs from the three-tier client/server database architecture in the middleware. The third tier (middleware) of a three-tier Web database system incorporates the Web server of a two-tier system and adds to it a server extension program. The signals and protocols handled by the Web server grew up in the Web environment and are accepted as standards in that realm. The signals and protocols that the database server is accustomed to seeing grew up in the client/server environment and are accepted as standards in that realm. The server extension program translates between these two incompatible standards. When requests are traveling from the client out on the Web to the data source behind the database server, the server extension program translates HTML to a form that the database server can understand, such as ODBC-compliant SQL. When result sets are traveling in the opposite direction, the server extension program translates them back into HTML for transmission over the Web. Figure 16-4 schematically shows the structure of a three-tier Web database system.

The Role of SQL

SQL was originally developed as a means for a remote client to communicate with a database. Local area networks (or wide area networks) passed the SQL from client to server, encoding it according to a network protocol on the source end and decoding it at the destination end. A Web-based system adds an additional level of complication. The Web browser on the client end transforms a user request into packets in TCP/IP format for transmission over the Web. At the server end, the Web server passes these packets on to the server extension program, which translates them back into SQL that the database server can understand and respond to. So, whether you are accessing a database over the Web or on a LAN, SQL is the means by which communication is conducted.

Where ODBC fits in

Whereas SQL is a standard language for communicating with a database, database vendors comply with that standard (commonly called SQL-92) to a greater or lesser extent. An application using SQL for database access is by no means guaranteed to successfully communicate with a DBMS that claims to be SQL-92 compliant. You have two ways to address this problem. One is to write native drivers for all the popular database servers. A native driver is specifically written to communicate with a particular database server, and no other. For example, Netscape provides native drivers for Informix, Oracle, and Sybase databases and is working on a native driver for IBM's DB2 database. Microsoft provides native driver support for its own SQL Server database.

Native drivers are fast and efficient because they are specifically written for the database client and database server that they are connecting. The disadvantage is that a different native driver must be written for each database that you want to access -- for each database client to which you want that access to be provided. The magnitude of the task of providing all those drivers for all those combinations of clients and servers motivated Microsoft to develop, and the industry to adopt, ODBC as a standard method of conveying SQL statements from clients to servers. If the SQL on the client end is always ODBC compliant, only one driver must be written for each type of server, and far fewer different server types exist than do clients. ODBC-compliant drivers are now available for the overwhelming majority of servers that anyone would want to connect to.

Java and SQL

Java is a language developed by Sun Microsystems specifically for use on the World Wide Web. It is similar in many respects to C++, but simpler to learn and use. People maintaining Web sites create applications written in Java, called applets, that reside on their Web server. When a user connects to a database server, the server downloads an applet to the user's browser, where it serves as a client-side extension to the browser. This system allows the user to access much more of the functionality available on the server than what's possible with just a "plain vanilla" browser.

SQL is a data sublanguage. It was never meant to be a complete language in itself, but was designed to be embedded in programs written in some other "host" language. Java can serve the function of being that host language just as well as can C++, Basic, or any other commonly used programming language. Sun has published a specification for JDBC (Java DataBase Connectivity) that performs the same function that ODBC performs in making a client-generated SQL statement understandable to a wide array of possible database servers. The JDBC standard provides writers of Java applets with the ground rules they need to produce applets that will work with multiple, different database servers.

Table of Contents

Introduction 1

About This Book 1

Foolish Assumptions 2

Icons Used in This Book 2

Beyond the Book 3

Where to Go from Here 3

Part 1: Getting Started with SQL 5

Chapter 1: Relational Database Fundamentals 7

Keeping Track of Things 8

What Is a Database? 9

Database Size and Complexity 10

What Is a Database Management System? 10

Flat Files 12

Database Models 13

Database Design Considerations 20

Chapter 2: SQL Fundamentals 23

What SQL Is and Isn’t 23

A (Very) Little History 25

SQL Statements 26

Reserved Words 28

Data Types 28

Null Values 49

Constraints 50

Using SQL in a Client/Server System 50

Using SQL on the Internet or an Intranet 52

Chapter 3: The Components of SQL 55

Data Definition Language 56

Data Manipulation Language 68

Data Control Language 76

Part 2: Using SQL to Build Databases 83

Chapter 4: Building and Maintaining a Simple Database Structure 85

Using a RAD Tool to Build a Simple Database 86

Building POWER with SQL’s DDL 98

Portability Considerations 107

Chapter 5: Building a Multi-table Relational Database 109

Designing a Database 110

Working with Indexes 119

Maintaining Data Integrity 122

Normalizing the Database 134

Part 3: Storing and Retrieving Data 141

Chapter 6: Manipulating Database Data 143

Retrieving Data 144

Creating Views 145

Updating Views 149

Adding New Data 150

Chapter 7: Handling Temporal Data 163

Understanding Times and Periods 164

Working with Application-Time Period Tables 165

Working with System-Versioned Tables 171

Tracking Even More Time Data with Bitemporal Tables 175

Formatting and Parsing Dates and Times 176

Chapter 8: Specifying Values 179

Values 179

Value Expressions 186

Functions 189

Chapter 9: Using Advanced SQL Value Expressions 209

CASE Conditional Expressions 210

CAST Data-Type Conversions 217

Row Value Expressions 221

Chapter 10: Zeroing In on the Data You Want 223

Modifying Clauses 224

FROM Clauses 225

WHERE Clauses 226

Logical Connectives 243

GROUP BY Clauses 245

HAVING Clauses 247

ORDER BY Clauses 248

Limited FETCH 250

Peering through a Window to Create a Result Set 251

Chapter 11: Using Relational Operators 259

UNION 259

INTERSECT 262

EXCEPT 264

Join Operators 265

ON versus WHERE 282

Chapter 12: Delving Deep with Nested Queries 283

What Subqueries Do 285

Chapter 13: Recursive Queries 303

What Is Recursion? 303

What Is a Recursive Query? 306

Where Might You Use a Recursive Query? 306

Where Else Might You Use a Recursive Query? 311

Part 4: Controlling Operations 313

Chapter 14: Providing Database Security 315

The SQL Data Control Language 316

User Access Levels 316

Granting Privileges to Users 318

Granting Privileges across Levels 325

Granting the Power to Grant Privileges 327

Taking Privileges Away 328

Using GRANT and REVOKE Together to Save Time and Effort 329

Chapter 15: Protecting Data 331

Threats to Data Integrity 332

Reducing Vulnerability to Data Corruption 336

Constraints Within Transactions 345

Avoiding SQL Injection Attacks 350

Chapter 16: Using SQL within Applications 351

SQL in an Application 352

Hooking SQL into Procedural Languages 354

Part 5: Taking SQL to the Real World 365

Chapter 17: Accessing Data with ODBC and JDBC 367

ODBC 368

ODBC in a Client/Server Environment 370

ODBC and the Internet 370

ODBC and an Intranet 373

JDBC 373

Chapter 18: Operating on XML Data with SQL 377

How XML Relates to SQL 377

The XML Data Type 378

Mapping SQL to XML and XML to SQL 380

SQL Functions That Operate on XML Data 385

Predicates 390

Transforming XML Data into SQL Tables 392

Mapping Non-Predefined Data Types to XML 393

The Marriage of SQL and XML 398

Chapter 19: SQL and JSON 399

Using JSON with SQL 400

The SQL/JSON Data Model 401

SQL/JSON Functions 403

SQL/JSON Path Language 411

There’s More 412

Part 6: Advanced Topics 413

Chapter 20: Stepping through a Dataset with Cursors 415

Declaring a Cursor 416

Opening a Cursor 421

Fetching Data from a Single Row 422

Closing a Cursor 425

Chapter 21: Adding Procedural Capabilities with Persistent Stored Modules 427

Compound Statements 428

Flow of Control Statements 435

Stored Procedures 440

Stored Functions 442

Privileges 442

Stored Modules 443

Chapter 22: Handling Errors 445

SQLSTATE 445

WHENEVER Clause 447

Diagnostics Areas 448

Handling Exceptions 455

Chapter 23: Triggers 457

Examining Some Applications of Triggers 457

Creating a Trigger 458

Firing a Succession of Triggers 460

Referencing Old Values and New Values 461

Firing Multiple Triggers on a Single Table 462

Part 7: The Parts of Tens 463

Chapter 24: Ten Common Mistakes 465

Assuming That Your Clients Know What They Need 465

Ignoring Project Scope 466

Considering Only Technical Factors 466

Not Asking for Client Feedback 466

Always Using Your Favorite Development Environment 467

Using Your Favorite System Architecture Exclusively 467

Designing Database Tables in Isolation 467

Neglecting Design Reviews 468

Skipping Beta Testing 468

Not Documenting Your Process 468

Chapter 25: Ten Retrieval Tips 469

Verify the Database Structure 470

Try Queries on a Test Database 470

Double-Check Queries That Include Joins 470

Triple-Check Queries with Subselects 470

Summarize Data with GROUP BY 471

Watch GROUP BY Clause Restrictions 471

Use Parentheses with AND, OR, and NOT 471

Control Retrieval Privileges 472

Back Up Your Databases Regularly 472

Handle Error Conditions Gracefully 472

Appendix: ISO/IEC SQL: 2016 Reserved Words 473

Index 479