SQL and Its Applications

Relational database systems have now become a major technology for the management of data. They apply to large installations where many users share vast amounts of data, as well as small systems where ease of use is of paramount importance. Most relational systems use SQL as their data language.

This volume shows how relational systems – and SQL in particular – can help you solve a variety of information management problems. Authors and experts Raymond A. Lorie and Jean-Jacques Daudenarde begin by introducing the basic concepts of the relational approach, the language itself, and the overall organization of a typical system. After a short review of database design techniques, they continue with the detailed description of the language. Among the topics covered, are:

• the general selection mechanism
• the notion of join and subquery
• the advanced data manipulation facilities
• performance issues
• views
• data protection
• and more

In the second half of the book the authors present case studies that helps you realize the practical applications of SQL. You'll find informative case studies covering:

• classical data processing
• problems dealing with interesting types of data, including textual data and object-oriented data
• the definition and exploitation of cyclic relationships (for example, bill of material)
• the design of an interactive, menu-driven application, illustrating the trade-offs between static and dynamic SQL

SQL and Its Applications will be helpful to you as a student, end user, or programmer with a need to understand SQL and to use it efficiently to solve your information management problems.

• Part 1. Introduction
• Chapter 1. Relational Data Management
• 1.1 Introducing data management concepts
• 1.2 The relational model
• 1.3 Introduction to SQL
• 1.3.1 Creating a table
• 1.3.2 Inserting a row
• 1.3.3 Exploiting relationships
• 1.3.4 The select block
• 1.3.5 Aggregates
• 1.3.6 Sets
• 1.3.7 Subqueries
• 1.3.8 Joins
• 1.3.9 Order
• 1.4 Overview of an SQL system
• 1.4.1 Writing specific SQL applications
• 1.4.2 Program variables
• 1.4.3 Using a program
• 1.4.4 Static SQL
• 1.4.5 Dynamic SQL
• 1.4.6 Error Handling
• 1.5 Summary
• Chapter 2. Database Design
• 2.1 First Normal Form
• 2.2 Looking at the data in multiple ways
• 2.3 Functional dependencies
• 2.4 Second normal form
• 2.5 Third normal form
• 2.6 More than one key
• 2.7 Multivalued dependencies
• 2.8 Summary
• 2.9 Schema for exercises
• 2.10 Exercises
• Part 2. The Language
• Chapter 3. Constructing a Table
• 3.1 Table creation
• 3.1.1 Table names
• 3.1.2 Column names
• 3.1.3 Data types
• 3.1.4 Missing information
• 3.1.5 Examples
• 3.1.6 Table deletion
• 3.2 Data insertion
• 3.2.1 Inserting from a program
• 3.3 Summary
• 3.4 Exercises
• Chapter 4. Elementary Queries
• 4.1 Projection list
• 4.1.1 Selecting columns
• 4.1.2 Selecting computed values
• 4.1.3 Operators
• 4.1.4 Duplicate or distinct rows
• 4.2 Filters
• 4.2.1 Simple comparison expression
• 4.2.2 Operators on character strings
• 4.2.3 Operators on dates
• 4.2.4 Expressions
• 4.2.5 Using between
• 4.2.6 The in operator
• 4.2.7 Complex filters
• 4.2.8 Effect of null
• 4.3 Program interface
• 4.3.1 One-row query
• 4.3.2 Multiple-row query
• 4.3.3 Retrieving null values
• 4.4 Aggregates
• 4.4.1 General aggregation
• 4.4.2 Aggregation with null values
• 4.5 Group by
• 4.6 Order by
• 4.7 Summary
• 4.8 Exercises
• Chapter 5. Complex Queries
• 5.1 The concept of join
• 5.1.1 Remarks
• 5.1.2 Null values in join
• 5.2 Uses of joins
• 5.2.1 Cartesian product
• 5.2.2 Decoding join
• 5.2.3 Multiplicative join
• 5.2.4 Selective join
• 5.2.5 Joins: a summary
• 5.3 Subqueries
• 5.3.1 Subquery returning a single row
• 5.3.2 Correlated subquery returning a single row
• 5.3.3 Subquery returning a set
• 5.3.4 Where the join does not help
• 5.3.5 More complex examples
• 5.4 Union
• 5.5 Summary
• 5.6 Exercises
• Chapter 6. Changing a Table
• 6.1 Set insert
• 6.2 Delete and set delete
• 6.3 Update and set update
• 6.4 Delete based on a cursor
• 6.5 Update based on a cursor
• 6.6 Adding a column to a table
• 6.7 Summary
• 6.8 Exercises
• Chapter 7. Controlling the Performance
• 7.1 Introduction
• 7.2 Storage of rows
• 7.2.1 Files
• 7.2.2 Storing records in a file
• 7.3 Indexes
• 7.3.1 Index performance
• 7.3.2 Index maintenance
• 7.4 Hashing
• 7.5 Support of order by
• 7.5.1 Using a sort
• 7.5.2 Using an index
• 7.6 Query with a filter
• 7.7 Support of group by
• 7.8 Summary: indexes
• 7.9 Joins
• 7.9.1 Nested loops
• 7.9.2 The merge-join algorithm
• 7.10 Subqueries
• 7.11 Index creation
• 7.12 Summary
• 7.13 Exercises
• Chapter 8. Views
• 8.1 Introduction
• 8.2 The general concept of view
• 8.3 Several uses of views
• 8.3.1 Selecting rows and columns
• 8.3.2 Computed columns
• 8.3.3 Joining tables
• 8.3.4 Aggregate functions, grouping
• 8.4 Summary
• 8.5 Exercises
• Chapter 9. Data Protection
• 9.1 Introduction
• 9.2 Protection against user errors
• 9.3 Referential integrity
• 9.4 The notion of transaction
• 9.4.1 Unit of work, transaction
• 9.5 Protection against effects of concurrency
• 9.6 Locking
• 9.6.1 A simple lock protocol
• 9.6.2 Lock modes
• 9.6.3 Lock granularity
• 9.6.4 Practical advice
• 9.6.5 Deadlock
• 9.7 Protection against failure
• 9.7.1 Recovering from soft failures
• 9.7.2 Logging
• 9.7.3 Recovery from hard failures
• 9.8 Protection against unauthorized access
• 9.8.1 Granting a privilege
• 9.8.2 Revoking a privilege
• 9.9 Summary
• 9.10 Exercises
• Part 3. Applications
• Chapter 10. Account Receivable
• 10.1 Overview
• 10.2 Operations
• 10.3 The database design
• 10.4 Sales transaction
• 10.5 Batch processing
• 10.6 Recovery
• 10.7 Summary
• Chapter 11. Resource Allocation
• 11.1 Find an object in a set
• 11.2 Find a sequence of objects
• 11.3 Storing allocated resources only
• 11.4 From discrete to continuous resources
• 11.5 Summary
• Chapter 12. Document Management
• 12.1 Storing a document
• 12.1.1 Formatted data
• 12.2 Unformatted data
• 12.2.1 Long strings
• 12.2.2 Storing a very long string
• 12.3 Structuring the information
• 12.4 Text indexing
• 12.5 Progresive refinement of a search
• 12.6 Document control
• 12.7 Summary
• Chapter 13. An Object-oriented Application
• 13.1 A graphics application
• 13.1.1 Describing icons
• 13.1.2 Describing a drawing
• 13.2 The notion of object
• 13.3 Techniques for managing object data
• 13.3.1 Use of identifiers
• 13.3.2 Fetching an object
• 13.4 Summary
• Chapter 14. Graph Problem
• 14.1 Path to the top of the tree
• 14.2 Finding all nodes under a node
• 14.3 Leaves of the tree
• 14.4 Acyclic graphs (bill of material)
• 14.5 Cyclic graphs (network problems)
• 14.6 Towards more support in SQL
• 14.7 Summary
• Chapter 15. Developing a User Interface
• 15.1 Static SQL for parameterized queries
• 15.2 The use of wild characters
• 15.3 Supporting a set of parameters
• 15.3.1 Static versus dynamic SQL
• 15.3.2 Creating temporary tables
• 15.4 Ad hoc statement
• 15.4.1 Selection of multiple rows
• 15.4.2 Multiple execution of the same statement
• 15.4.3 Other cases
• 15.5 Ad hoc query for a known environment
• 15.6 Summary
• Appendix. Solution of Exercises

A very good book on SQL, that ages excellent. It is still valid.