Amazon no longer offers textbook rentals. We do!

SQL Fundamentals

by: Patrick, John J.

SQL Fundamentals

by: Patrick, John J.

ISBN 13:
9780130960160
ISBN 10:
0130960160
Format: Paperback
Copyright: 06/01/1999
Publisher: Prentice Hall PTR
Newer Edition

Rent

Sorry, this item is currently unavailable.

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

Extend or Purchase Your Rental at Any Time

Need to keep your rental past your due date? At any time before your due date you can extend or purchase your rental through your account.

Summary

There are plenty of SQL books, but this is the one to buy if you really want to understand how SQL works and how to make it work for you. It emphasizes practical concepts and examples from the two leading SQL implementations: Access on the desktop, and Oracle for the enterprise. Start with the basics: creating simple queries to retrieve information from a single table. Next, move on to more complex queries involving many tables. Key topics include outer joins, process verification and program testing. Each topic is discussed in detail, with practical examples that show how to write queries others can understand and modify; verify the correctness of your queries and account for all the data returned. By the end of the book, you'll be able to design your own databases. Whether you plan to use SQL for ad hoc reports, database design or data warehousing, this book gives you the strong foundation you need to succeed. For anyone who wants to learn SQL on the leading desktop platform, Access; or the leading enterprise platform, Oracle.

Preface

Storing Information in Tables

(44)

Introduction

(8)

What is SQL?

(2)

What is a Relational Database and why would you use one?

(2)

Why learn SQL?

(2)

What is in this book?

(2)

The parts of a table

(14)

Data is stored in tables

(2)

A row represents an object and the information about it

(2)

A column represents one type of information

(2)

A cell is the smallest part of a table

(2)

Each cell should express just one thing

(2)

Primary key columns identify each row

(2)

Most tables are tall and thin

(2)

Examples of tables

(8)

An example of a table in Oracle and Access

(2)

Some database design decisions

(2)

The Lunches database

(4)

Oracle and Access

(13)

Obtaining Oracle and Access

(2)

Using Oracle

(4)

Using Access

(7)

Getting Information from a Table

(62)

The Select statement

(4)

The goal: get a few columns and rows from a table

(2)

Overview of the Select statement

(2)

The Select clause

(10)

Overview of the Select clause

(2)

Using the Select clause to get a list of columns

(2)

Using the Select clause to get all the columns

(2)

Using the Select clause to get the distinct values in one column

(2)

Using the Select clause to get the distinct values of several columns

(2)

The Where clause

(22)

Overview of the Where clause

(2)

Using an Equal condition in the Where clause

(2)

Using a Less Than condition in the Where clause

(2)

Using a Not Equal To condition in the Where clause

(2)

Using the In condition in the Where clause

(2)

Using the Between condition in the Where clause

(2)

Using the Like condition in the Where clause

(2)

Using the Is Null condition in the Where clause

(2)

Using a compound condition in the Where clause

(2)

Using a complex compound condition in the Where clause

(2)

Using NOT with IN, BETWEEN, LIKE, and IS NULL

(2)

The Order By clause

(6)

Overview of the Order By clause

(2)

Sorting the rows of several columns in ascending order

(2)

Sorting the rows of several columns with some in ascending order and others in descending order

(2)

Other techniques

(2)

Using a lookup table to find the meanings of codes

(2)

The Whole Process

(6)

The whole process---so far

(2)

Punctuation matters

(4)

Summary

(1)

Exercises

(10)

Saving Your Results

107

(56)

Saving your results in a new table or view

108

(14)

Creating a new table from the results of a Select statement

108

(2)

Creating a new view from the results of a Select statement

110

(2)

The similarities and differences between a table and a view

112

(2)

Deleting a table or a view

114

(4)

One view can be built on top of another view

118

(2)

Preventative deletes

120

(2)

Modifying the data in a table

122

(10)

Adding one new row to a table

122

(2)

Adding many new rows to a table with a Select statement

124

(2)

Changing data in the rows already in a table

126

(2)

Deleting rows from a table

128

(2)

Using the GUI environment to change the table data in Access

130

(2)

Modifying data through a view

132

(10)

Changing data through a view

132

(2)

Example of changing data through a view

134

(6)

Views using With Check Option

140

(2)

Finding information about tables and views

142

(12)

The Data Dictionary

142

(2)

How to find the names of all the tables

144

(2)

How to find the names of all the views

146

(2)

How to find the Select statement that defines a view

148

(2)

How to find the names of the columns in a table or view

150

(2)

How to find the primary key of a table

152

(2)

Summary

154

(1)

Exercises

155

(8)

Creating Your Own Tables

163

(42)

Creating tables

164

(6)

The Create Table command

164

(2)

Datatypes in Oracle and Access

166

(2)

Sequences

168

(2)

Changing tables

170

(12)

Adding a primary key to a table

170

(2)

Deleting a primary key from a table

172

(2)

Adding a new column to a table

174

(2)

Deleting a column from a table

176

(2)

Making other changes to tables

178

(2)

Tables with duplicate rows

180

(2)

Finding more information about tables in the Data Dictionary

182

(8)

Finding information about columns

182

(2)

Finding information about sequences

184

(2)

Finding information about your database objects

186

(2)

Finding where to find things in the Data Dictionary

188

(2)

Formats

190

(6)

Date formats

190

(2)

Displaying formatted dates

192

(2)

Entering formatted dates

194

(2)

Summary

196

(1)

Exercises

197

(8)

Row Functions

205

(54)

Introduction to row functions

206

(8)

Getting data directly from the beginning table

206

(2)

Understanding a row function

208

(2)

An example of a row function

210

(2)

Using a series of steps with a row function

212

(2)

Number functions

214

(4)

Functions on numbers

214

(2)

Testing row functions

216

(2)

Text functions

218

(6)

Functions on text

218

(2)

Combining the first and last names

220

(2)

Capitalization

222

(2)

Date functions

224

(4)

Function on dates

224

(2)

An example of a date function

226

(2)

Other functions

228

(10)

Other functions

228

(2)

Using functions to change the datatype

230

(2)

Using functions to change nulls to other values

232

(2)

Using functions to identify the user and the date

234

(2)

Starting Expression Builder in Access

236

(2)

Creating patterns of numbers and dates

238

(10)

Listing the multiples of three

238

(2)

Listing the prime numbers

240

(2)

Listing all the days of one week

242

(6)

Summary

248

(1)

Exercises

249

(10)

Summarizing Data

259

(56)

Introduction to the column functions

260

(26)

Summary of all the data in a column

260

(2)

Summary of groups of data within a column

262

(2)

A list of the column functions

264

(2)

Finding the maximum and minimum values

266

(2)

Using a Where clause with a column function

268

(2)

Finding the rows that have the maximum or minimum value

270

(2)

Two types of counting: counting rows and counting data

272

(2)

Counting to zero

274

(2)

Counting the number of distinct values in a column

276

(2)

The Sum and Average functions

278

(2)

The problem with addition and how to solve it

280

(4)

A practical example

284

(2)

Dividing a table into groups of rows and summarizing each group

286

(20)

The Group By clause

286

(2)

Groups formed on two or more columns

288

(2)

Summarized data cannot mix with non-summarized data in the same Select statement

290

(6)

Null groups in a single grouping column

296

(2)

Null groups in two or more grouping columns

298

(2)

An example

300

(2)

The Having clause

302

(2)

Occasions when a Where clause and a Having clause can do the same thing

304

(2)

Solutions to some problems

306

(4)

Counting the number of nulls in a column

306

(2)

Using DISTINCT more than once in the same query

308

(2)

Summary

310

(1)

Exercises

311

(4)

Inner Joins

315

(50)

Introduction to joins

316

(4)

A query can use data from several tables

316

(2)

The best approach is to join two tables at a time

318

(2)

Inner joins of two tables

320

(12)

A one-to-one relationship

320

(2)

A many-to-one relationship

322

(2)

A one-to-many relationship

324

(2)

A many-to-many relationship

326

(2)

Dropping unmatched rows

328

(2)

Dropping rows with a null in the matching column

330

(2)

Variations of the join condition

332

(6)

Using two or more matching columns

332

(2)

Using Between to match on a range of values

334

(2)

Using Greater Than in the join condition

336

(2)

Inner joins of three or more tables

338

(4)

Joining three tables with one Select statement

340

(2)

Joining three tables with a series of steps

342

(1)

The new syntax for an inner join

342

(6)

Writing the join condition in the From clause

342

(2)

Joining three tables with the new syntax in one Select statement

344

(2)

Joining three tables with the new syntax in a series of steps

346

(2)

Other issues

348

(10)

Inner joins are symmetric---the order in which the tables are joined does not matter

348

(2)

Some tables should always be joined to other tables in one particular way

350

(2)

A view can standardize the way tables are joined

352

(4)

Sometimes tables can be joined to each other in several different ways

356

(2)

Summary

358

(1)

Exercises

359

(6)

Outer Joins and Unions

365

(54)

Outer joins

366

(18)

Outer joins are derived from inner joins

366

(2)

The three types of outer joins

368

(2)

The left outer join

370

(2)

The right outer join

372

(2)

The full outer join

374

(2)

An introduction to unions

376

(2)

An example of a union of two tables with matching columns

378

(2)

A full outer join in sorted order

380

(2)

The symmetry of full outer joins

382

(2)

Unions

384

(12)

The difference between a union and a join

384

(2)

Union all

386

(2)

Using a literal in a union to identify the source of the data

388

(2)

Automatic datatype conversion in a union

390

(2)

Using datatype conversion functions in a union

392

(2)

A union of two tables with different numbers and types of columns

394

(2)

Applications of unions (divide and conquer)

396

(12)

Determining whether two tables are equal

396

(2)

Attaching messages for warnings and errors

398

(2)

Overriding several rows of data in a table

400

(4)

Dividing data into different columns

404

(2)

Applying two functions to different parts of the data

406

(2)

Set Intersection and Set Difference in Oracle

408

(4)

Set Intersection

408

(2)

Set Difference

410

(2)

Summary

412

(1)

Exercises

413

(6)

Self Joins, Cross Joins, and Subqueries

419

(44)

Self joins

420

(16)

Why join a table with itself?

420

(2)

An example of a self join

422

(2)

Generating the numbers from 0 to 99

424

(2)

Generating the numbers from 1 to 10,000

426

(2)

Numbering the lines of a report in Oracle and Access

428

(2)

Numbering the lines of a report in standard SQL

430

(2)

Numbering the lines of each group

432

(4)

Cross joins

436

(8)

What is a cross join?

436

(2)

Inner joins are derived from cross joins

438

(2)

The properties of an inner and outer join are derived from the properties of a cross join

440

(2)

An error in the join condition can appear as a cross join

442

(2)

Subqueries

444

(12)

Introduction to subqueries

444

(2)

Subqueries that result in a list of values

446

(2)

Subqueries that result in a single value

448

(2)

Avoiding NOT IN with nulls

450

(2)

Many subqueries can be written with a join

452

(2)

Finding the differences between two tables

454

(2)

Summary

456

(1)

Exercises

457

(6)

Advanced Queries

463

(45)

The Decode and IIF functions

464

(12)

The Decode function in Oracle

464

(2)

The Immediate If (IIF) function in Access

466

(2)

Attaching messages to rows

468

(2)

Overriding several rows of a table

470

(2)

Dividing data into different columns

472

(2)

Applying two functions to different parts of the data

474

(2)

Using the environment in which SQL runs

476

(16)

Parameter queries in Oracle

476

(2)

Parameter queries in Access

478

(2)

Reporting details, subtotals, and grand totals in Oracle

480

(2)

Reporting details, subtotals, and grand totals in Access

482

(10)

Spreadsheet reports

492

(16)

Database format and spreadsheet format

492

(2)

Creating a spreadsheet report in Oracle

494

(4)

Creating a spreadsheet report in Access

498

(2)

Creating a spreadsheet report in Access using the IIF function

500

(2)

Creating a spreadsheet report using a union

502

(2)

Graphing one column against another

504

(4)

Summary

508

(1)

Index

509

Amazon no longer offers textbook rentals. We do!

SQL Fundamentals

SQL Fundamentals

9780130960160

0130960160

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review

Amazon no longer offers textbook rentals. We do!

SQL Fundamentals

SQL Fundamentals

9780130960160

0130960160

Summary

Author Biography

Table of Contents

Supplemental Materials

Write a Review

Related Products