Using the Cartesian Product
by Sandra L. Emerson, Marcy Darnovsky, Judith S. Bowman
Practical SQL Handbook, The: Using SQL Variants, Fourth Edition
- Copyright
- List of Figures
- Foreword to the Fourth Edition
- Preface
- Introduction
- SQL and Relational Database Management
- Designing Databases
- Creating and Filling a Database
- Selecting Data from the Database
- Sorting Data and Other Selection Techniques
- Grouping Data and Reporting from It
- Joining Tables for Comprehensive Data Analysis
- Structuring Queries with Subqueries
- What Is a Subquery?
- How Do Subqueries Work?
- Joins or Subqueries?
- Subquery Rules
- Subqueries Returning Zero or More Values
- Subqueries Returning a Single Value
- Subqueries Testing Existence
- Subqueries in Multiple Levels of Nesting
- Subqueries in UPDATE, DELETE, and INSERT Statements
- Subqueries in FROM and SELECT Clauses
- Summary
- Creating and Using Views
- Security, Transactions, Performance, and Integrity
- Solving Business Problems
- Syntax Summary for the SQL Used in This Book
- Industry SQL Equivalents
- Glossary
- The bookbiz Sample Database
- Resources
- CD ROM Warranty
- Using the Practical SQL Handbook CD
Using the Cartesian Product
Valid joins are essential to producing comprehensible multitable query results, unless one of the tables contains a single row—perhaps a system variable or a stored value. In that case, you can use a Cartesian product between two tables without fear. For example, test3 contains the current tax rate.
SQLcreate table test3 (rate decimal not null, date_start date not null default current date) [table created] insert test3 (rate) values (.08) [1 row]
You can use test3 with titles without a join to calculate the base-plus-tax price of books.
SQLselect title_id as Book, price as BasePrice, price + ( price * rate) as TaxPrice from titles, test3 where type = 'business' order by title_id Book BasePrice TaxPrice ====== ========== =============== BU1032 29.99 32.39 BU1111 21.95 23.71 BU2075 12.99 14.03 BU7832 29.99 32.39 [4 rows]
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.