SQL All-in-One For Dummies

Allen G. Taylor

• Publisher: For Dummies
• Year: 2019
• ISBN:
  9781119569596
• Edition: 3
• Topic: Computer Science
• Subtopic: Programming in SQL
• Index: Computer Science

The latest on SQL databases

SQL All -In-One For Dummies, 3rd Edition, is a one-stop shop for everything you need to know about SQL and SQL-based relational databases. Everyone from database administrators to application programmers and the people who manage them will find clear, concise explanations of the SQL language and its many powerful applications.

With the ballooning amount of data out there, more and more businesses, large and small, are moving from spreadsheets to SQL databases like Access, Microsoft SQL Server, Oracle databases, MySQL, and PostgreSQL. This compendium of information covers designing, developing, and maintaining these databases.

• Cope with any issue that arises in SQL database creation and management
• Get current on the newest SQL updates and capabilities
• Reference information on querying SQL-based databases in the SQL language
• Understand relational databases and their importance to today's organizations

SQL All-In-One For Dummies is a timely update to the popular reference for readers who want detailed information about SQL databases and queries.

Book 1

SQL Concepts

Contents at a Glance

1. Chapter 1: Understanding Relational Databases
   1. Understanding Why Today’s Databases Are Better than Early Databases
   2. Databases, Queries, and Database Applications
   3. Examining Competing Database Models
   4. Why the Relational Model Won
2. Chapter 2: Modeling a System
   1. Capturing the Users’ Data Model
   2. Translating the Users’ Data Model to a Formal Entity-Relationship Model
3. Chapter 3: Getting to Know SQL
   1. Where SQL Came From
   2. Knowing What SQL Does
   3. The ISO/IEC SQL Standard
   4. Knowing What SQL Does Not Do
   5. Choosing and Using an Available DBMS Implementation
4. Chapter 4: SQL and the Relational Model
   1. Sets, Relations, Multisets, and Tables
   2. Functional Dependencies
   3. Keys
   4. Views
   5. Users
   6. Privileges
   7. Schemas
   8. Catalogs
   9. Connections, Sessions, and Transactions
   10. Routines
   11. Paths
5. Chapter 5: Knowing the Major Components of SQL
   1. Creating a Database with the Data Definition Language
   2. Operating on Data with the Data Manipulation Language (DML)
   3. Maintaining Security in the Data Control Language (DCL)
6. Chapter 6: Drilling Down to the SQL Nitty-Gritty
   1. Executing SQL Statements
   2. Using Reserved Words Correctly
   3. SQL’s Data Types
   4. Handling Null Values
   5. Applying Constraints

Chapter 1

Understanding Relational Databases

IN THIS CHAPTER

Working with data files and databases

Seeing how databases, queries, and database applications fit together

Looking at different database models

Charting the rise of relational databases

SQL (pronounced ess cue el, but you’ll hear some people say see quel) is the international standard language used in conjunction with relational databases — and it just so happens that relational databases are the dominant form of data storage throughout the world. In order to understand why relational databases are the primary repositories for the data of both small and large organizations, you must first understand the various ways in which computer data can be stored and how those storage methods relate to the relational database model. To help you gain that understanding, I spend a good portion of this chapter going back to the earliest days of electronic computers and recapping the history of data storage.

I realize that grand historical overviews aren’t everybody’s cup of tea, but I’d argue that it’s important to see that the different data storage strategies that have been used over the years each have their own strengths and weaknesses. Ultimately, the strengths of the relational model overshadowed its weaknesses and it became the most frequently used method of data storage. Shortly after that, SQL became the most frequently used method of dealing with data stored in a relational database.

Understanding Why Today’s Databases Are Better than Early Databases

In the early days of computers, the concept of a database was more theoretical than practical. Vannevar Bush, the twentieth-century visionary, conceived of the idea of a database in 1945, even before the first electronic computer was built. However, practical implementations of databases — such as IBM’s IMS (Information Management System), which kept track of all the parts on the Apollo moon mission and its commercial followers — did not appear for a number of years after that. For far too long, computer data was still being kept in files rather than migrated to databases.

Irreducible complexity

Any software system that performs a useful function is complex. The more valuable the function, the more complex its implementation. Regardless of how the data is stored, the complexity remains. The only question is where that complexity resides.

Any nontrivial computer application has two major components: the program and the data. Although an application’s level of complexity depends on the task to be performed, developers have some control over the location of that complexity. The complexity may reside primarily in the program part of the overall system, or it may reside in the data part. In the sections that follow, I tell you how the location of complexity in databases shifted over the years as technological improvements made that possible.

Managing data with complicated programs

In the earliest applications of computers to solve problems, all of the complexity resided in the program. The data consisted of one data record of fixed length after another, stored sequentially in a file. This is called a flat file data structure. The data file contains nothing but data. The program file must include information about where particular records are within the data file (one form of metadata, whose sole purpose is to organize the primary data you really care about). Thus, for this type of organization, the complexity of managing the data is entirely in the program.

Here’s an example of data organized in a flat file structure:

Harold Percival26262 S. Howards Mill Rd.Westminster CA92683

Jerry Appel 32323 S. River Lane Road Santa Ana CA92705

Adrian Hansen 232 Glenwood Court Anaheim CA92640

John Baker 2222 Lafayette Street Garden GroveCA92643

Michael Pens 77730 S. New Era Road Irvine CA92715

Bob Michimoto 25252 S. Kelmsley Drive Stanton CA92610

Linda Smith 444 S.E. Seventh StreetCosta Mesa CA92635

Robert Funnell 2424 Sheri Court Anaheim CA92640

Bill Checkal 9595 Curry Drive Stanton CA92610

Jed Style 3535 Randall Street Santa Ana CA92705

This example includes fields for name, address, city, state, and zip code. Each field has a specific length, and data entries must be truncated to fit into that length. If entries don’t use all the space allotted to them, storage space is wasted.

The flat file method of storing data has several consequences, some beneficial and some not. First, the beneficial consequences:

• Storage requirements are minimized. Because the data files contain nothing but data, they take up a minimum amount of space on hard disks or other storage media. The code that must b...