Alice Rischert , formerly chair of Columbia University’s Database Application Development and Design program, has taught classes in Oracle SQL, PL/SQL, and database design to hundreds of students. Ms. Rischert’s wide-ranging technology experience encompasses systems integration, database architecture, and project management for a number of companies in the United States, Europe, and Asia. Ms. Rischert has presented on SQL and PL/SQL topics at Oracle conferences and has worked with Oracle since version 5.
eBook
-
ISBN-13:
9780137046942
- Publisher: Pearson Education
- Publication date: 08/12/2009
- Series: Prentice Hall Professional Oracle Series
- Sold by: Barnes & Noble
- Format: eBook
- Pages: 960
- File size: 46 MB
- Note: This product may take a few minutes to download.
Read an Excerpt
Introduction
The SQL language is the de facto standard language for relational databases, and Oracle's database server is the leading relational database on the market today. The Oracle by Example, third edition, presents an introduction to the Oracle SQL language in a unique and highly effective format. Rather than being a reference book, it guides you through the basic skills until you reach a mastery of the language. The book challenges you to work through hands-on guided tasks rather than read through descriptions of functionality. You will be able to retain the material more easily and the additional example questions reinforce and further enhance the learning experience.Who This Book Is For
This book is intended for anyone requiring a background in Oracle's implementation of the SQL language. In particular, application developers, system designers, and database administrators will find many practical and representative real-world examples. Students new to Oracle will gain the necessary experience and confidence to apply their knowledge in solving typical problems they face in the work situation. Individuals already familiar with Oracle SQL but wishing a firmer foundation or those interested in the new Oracle 10g features will discover many of the useful tips, tricks, and information.
The initial audience for the book was the students of the Oracle SQL class at Columbia University's Computer Technology and Applications program. The student body has a wide-ranging level of technology experience. Their questions, learning approaches, and feedback provided the framework for this book. Many students cited the hands-onexercises as critical to their understanding of database technology and the SQL language and continuously asked for more examples and additional challenging topics. This book shares much of the material presented in the classroom and looks at the various angles of many solutions to a particular issue.
The book begins with the basic concepts of relational databases, the SQL*Plus environment, and SQL; it then gradually deepens the knowledge. A reader who already has some fundamental understanding of SQL will benefit from reading this book as it allows him/her to gain a better insight to writing alternative SQL statements. After performing the exercises in this book, you will harness the power of SQL and utilize much of Oracle's SQL functionality.What Makes This Book Different
This book encourages the reader to learn by doing; this requires active participation by performing the exercises. Ultimately, the reward is a thorough understanding of SQL and a high level of comfort dealing with the real-world Oracle SQL topics. Performing the exercises help with the retention of the material, and the Self-Review and Test Your Thinking sections further test your understanding and offer additional challenges. The companion Web site contains solutions to the Test Your Thinking exercises and includes additional exercises and answers.
The book's focus is to give the readers examples of how the SQL language is commonly used, with many exercises supporting the learning experience. Unlike other SQL books, this book discusses Oracle's specific implementation of the language. Learning the language alone is not enough. The book also teaches you how to adopt good habits and educates you about many Oracle-specific technology features that are essential to successful systems development. The examples are derived from a sample database that takes you through the typical tasks you will encounter when working with an Oracle database.
This book is not a reference book but rather it teaches SQL by illustrating its use through many examples. Use the index to lookup previously discussed concepts or refer back to certain topics. The best way to learn the SQL language is to perform the exercises and compare your answers with the sample answers and accompanying explanations. After you have completed the exercises, the companion Web site contains additional questions to test your understanding. It also lists the solutions to the Test Your Thinking exercises at the end of each chapter. You can access the companion Web site at http://www.phptr.com/rischert3e.
This book does not cover the entire Oracle SQL syntax, but emphasizes the essentials of the most-frequently used features with many examples to reinforce the learning. Some of Oracle's syntax options are too numerous and many very infrequently used; including them all would make the book swell by a several hundred additional pages. Instead, I have concentrated on those that you will use most. After performing the exercises, you will also have gained sufficient knowledge to read-up and understand the Oracle documentation, if needed. I hope that you will enjoy this learning experience and come away with the knowledge you hoped to gain.How This Book Is Organized
Each chapter of the book is divided into labs covering a particular topic. The objective of each lab is defined at its beginning, with brief examples that introduce the reader to the covered concepts.
Following the lab's introductory examples are exercises that are the heart of the lab which reinforce and expand the reader's knowledge of the subject. Each exercise consists of a series of steps to follow to perform specific tasks, or particular questions that are designed to help you discover the important aspects of the SQL language. The answers to these questions are given at the end of the exercises, along with more in-depth discussion of the concepts explored.
After you perform the exercises and compare the answers with the sample queries, answers, and explanations, the lab ends with multiple-choice Self-Review questions. These are meant to test that you have understood the material covered in the lab. The answers to these questions appear in Appendix A. There are additional Self-Review questions at this book's companion Web site.
At the end of each chapter, the Test Your Thinking section reinforces the topics learned in labs, and solidifies your skills. The answers to these questions are found on the companion Web site for this book.Layout of a Chapter
- Chapter
- Lab
- Exercises
- Exercise Answers (with detailed discussion)
- Self-Review Questions
- Lab ...
- Test Your Thinking Questions
The chapters should be completed in sequence because concepts covered in earlier chapters are required for the completion of exercises in later chapters.About the Companion Web Site
The companion Web site is located at http://http://www.phptr.com/rischert3e. Here you will find a number of very important things:
Installation files you need before you begin reading the book.
Answers to the Test Your Thinking questions.
Additional Self-Review questions.
Additional Resources such as links, tips, and errata
Installation Files
All of the exercises and questions are based on a sample schema called STUDENT. The required files to install this STUDENT schema and the installation instructions can be downloaded from the Web site.Test Your Thinking
The answers to the Test Your Thinking sections are found at the Web site.Additional Self-Review Questions
The Web site will have many other features, such as additional Self-Review questions, a message board, and periodically updated information about the book.
Visit the companion Web site and download the required files before starting the labs and exercises.What You will Need
To complete the exercises you need the following:
The Oracle® database software
Oracle's SQL*Plus software or a Web browser
Access to the Internet
Oracle 10g is Oracle's latest version of the relational database software and its flagship product. You can use either the Personal Edition or Enterprise Edition. Various beta versions of the Enterprise edition of Oracle 10g were used to create the exercises for this book.
If you have a previous version of the Oracle database you will be able to complete a large majority of the exercises, however some syntax options and features are only available in Oracle 10g. Review the companion Web site for more details.
If you don't have the latest release of the Oracle software available, you can obtain a trial copy of the latest release from Oracle's Web site at http://www.oracle.com. You have the option of either downloading the Personal or Enterprise editions or purchasing a CD pack from the Oracle store (http://oraclestore.oracle.com) for a nominal fee.Oracle SQL*Plus software or web browser
You can perform all the exercises in this book with Oracle's SQL*Plus software. Alternatively, you can use iSQL*Plus, a Web-based version, which does not require the installation of the Oracle SQL*Plus software on your individual machine. Only a browser is necessary. The iSQL*Plus version simplifies the editing and offers a superior display of the result. However, iSQL*Plus does not allow certain functionality and this is pointed out where applicable. For a list of unsupported commands refer to Appendix C, "SQL*Plus Command Reference." Instead of SQL*Plus you can also use alternate SQL execution environments and a list is available in Appendix H, "Resources."Access to the Internet
You will need access to the Internet so that you can access the companion Web site: http://http://www.phptr.com/rischert3e. Here you will find files that are necessary to install the sample STUDENT schema.About the Student Schema
Throughout this book, you access data from a sample schema called STUDENT, which contains information about a computer education program. The schema was designed to record data about instructors, courses, students, and their respective enrollments and grades.
After you download the installation files to create the schema within your Oracle database, you will be able to follow the exercises in the book. In Chapter 1, "SQL and Data," you are introduced to the relational concepts necessary to read the schema diagram. Appendix D, "Student Database Schema," shows you a graphical representation of the schema and Appendix E, "Table and Column Descriptions" lists descriptive information about each table and column.Conventions Used in This Book
There are several conventions used in this book to make your learning experience easier. These are explained here.
Erratanotepaper icon This icon denotes advice and useful information about a particular topic or concept from the author to you, the reader.
lightbulb icon This icon flags tips that are especially helpful tricks that will save you time or trouble, for instance, a shortcut for performing a particular task or a method that the author has found useful.
warning icon Computers are delicate creatures and can be easily damaged. Likewise, they can be dangerous to work on if you're not careful. This icon flags information and precautions that not only save you headaches in the long run, but may even save you or your computer from harm.
web icon Passages referring to the book's companion Web site are flagged with this icon. The companion Web site is located at http://authors.phptr.com/rischert3e.
I have made every effort to make sure there are no errors in the text and code. However, to err is human. As part of the companion Web site, you will find corrections as they're spotted. The exercises and answers were tested against various beta versions of Oracle 10g. In case of a modification in functionality in the production version, you will see this listed as an errata item on Web site's errata page. If you find an error that has not been reported, please let me know by contacting me at ar280@yahoo.com. You comments and suggestions are greatly appreciated.
© Copyright Pearson Education. All rights reserved.
Table of Contents
Foreword xxxi
Preface xxxiv
Chapter 1: SQL and Data 1
Chapter 2: SQL: The Basics 49
Chapter 3: The WHERE and ORDER BY Clauses 101
Chapter 4: Character, Number, and Miscellaneous Functions 133
Chapter 5: Date and Conversion Functions 189
Chapter 6: Aggregate Functions, GROUP BY, and HAVING Clauses 263
Chapter 7: Equijoins 285
Chapter 8: Subqueries 323
Chapter 9: Set Operators 377
Chapter 10: Complex Joins 399
Chapter 11: Insert, Update, and Delete 429
Chapter 12: Create, Alter, and Drop Tables 503
Chapter 13: Indexes, Sequences, and Views 571
Chapter 14: The Data Dictionary, Scripting, and Reporting 615
Chapter 15: Security 661
Chapter 16: Regular Expressions and Hierarchical Queries 695
Chapter 17: Exploring Data Warehousing Features 741
Chapter 18: SQL Optimization 803
Appendix A: Answers to Quiz Questions 839
Appendix B: SQL Formatting Guide 855
Appendix C: SQL*Plus Command Reference 859
Appendix D: STUDENT Database Schema 873
Appendix E: Table and Column Descriptions 875
Appendix F: Additional Example Tables 881
Appendix G: Navigating the Oracle Documentation 887
Appendix H: Resources 893
Appendix I: Oracle Data Types 897
Index 899
Preface
Introduction
The SQL language is the de facto standard language for relational databases, and Oracle's database server is the leading relational database on the market today. The Oracle by Example, third edition, presents an introduction to the Oracle SQL language in a unique and highly effective format. Rather than being a reference book, it guides you through the basic skills until you reach a mastery of the language. The book challenges you to work through hands-on guided tasks rather than read through descriptions of functionality. You will be able to retain the material more easily and the additional example questions reinforce and further enhance the learning experience.
Who This Book Is For
This book is intended for anyone requiring a background in Oracle's implementation of the SQL language. In particular, application developers, system designers, and database administrators will find many practical and representative real-world examples. Students new to Oracle will gain the necessary experience and confidence to apply their knowledge in solving typical problems they face in the work situation. Individuals already familiar with Oracle SQL but wishing a firmer foundation or those interested in the new Oracle 10g features will discover many of the useful tips, tricks, and information.
The initial audience for the book was the students of the Oracle SQL class at Columbia University's Computer Technology and Applications program. The student body has a wide-ranging level of technology experience. Their questions, learning approaches, and feedback provided the framework for this book. Many students cited the hands-on exercises as critical to their understanding of database technology and the SQL language and continuously asked for more examples and additional challenging topics. This book shares much of the material presented in the classroom and looks at the various angles of many solutions to a particular issue.
The book begins with the basic concepts of relational databases, the SQL*Plus environment, and SQL; it then gradually deepens the knowledge. A reader who already has some fundamental understanding of SQL will benefit from reading this book as it allows him/her to gain a better insight to writing alternative SQL statements. After performing the exercises in this book, you will harness the power of SQL and utilize much of Oracle's SQL functionality.
What Makes This Book Different
This book encourages the reader to learn by doing; this requires active participation by performing the exercises. Ultimately, the reward is a thorough understanding of SQL and a high level of comfort dealing with the real-world Oracle SQL topics. Performing the exercises help with the retention of the material, and the Self-Review and Test Your Thinking sections further test your understanding and offer additional challenges. The companion Web site contains solutions to the Test Your Thinking exercises and includes additional exercises and answers.
The book's focus is to give the readers examples of how the SQL language is commonly used, with many exercises supporting the learning experience. Unlike other SQL books, this book discusses Oracle's specific implementation of the language. Learning the language alone is not enough. The book also teaches you how to adopt good habits and educates you about many Oracle-specific technology features that are essential to successful systems development. The examples are derived from a sample database that takes you through the typical tasks you will encounter when working with an Oracle database.
This book is not a reference book but rather it teaches SQL by illustrating its use through many examples. Use the index to lookup previously discussed concepts or refer back to certain topics. The best way to learn the SQL language is to perform the exercises and compare your answers with the sample answers and accompanying explanations. After you have completed the exercises, the companion Web site contains additional questions to test your understanding. It also lists the solutions to the Test Your Thinking exercises at the end of each chapter. You can access the companion Web site at http://www.phptr.com/rischert3e.
This book does not cover the entire Oracle SQL syntax, but emphasizes the essentials of the most-frequently used features with many examples to reinforce the learning. Some of Oracle's syntax options are too numerous and many very infrequently used; including them all would make the book swell by a several hundred additional pages. Instead, I have concentrated on those that you will use most. After performing the exercises, you will also have gained sufficient knowledge to read-up and understand the Oracle documentation, if needed. I hope that you will enjoy this learning experience and come away with the knowledge you hoped to gain.
How This Book Is Organized
Each chapter of the book is divided into labs covering a particular topic. The objective of each lab is defined at its beginning, with brief examples that introduce the reader to the covered concepts.
Following the lab's introductory examples are exercises that are the heart of the lab which reinforce and expand the reader's knowledge of the subject. Each exercise consists of a series of steps to follow to perform specific tasks, or particular questions that are designed to help you discover the important aspects of the SQL language. The answers to these questions are given at the end of the exercises, along with more in-depth discussion of the concepts explored.
After you perform the exercises and compare the answers with the sample queries, answers, and explanations, the lab ends with multiple-choice Self-Review questions. These are meant to test that you have understood the material covered in the lab. The answers to these questions appear in Appendix A. There are additional Self-Review questions at this book's companion Web site.
At the end of each chapter, the Test Your Thinking section reinforces the topics learned in labs, and solidifies your skills. The answers to these questions are found on the companion Web site for this book.
Layout of a Chapter
- Chapter
- Lab
- Exercises
- Exercise Answers (with detailed discussion)
- Self-Review Questions
- Lab ...
- Test Your Thinking Questions
- Lab
The chapters should be completed in sequence because concepts covered in earlier chapters are required for the completion of exercises in later chapters.
About the Companion Web Site
The companion Web site is located at http://http://www.phptr.com/rischert3e. Here you will find a number of very important things:
- Installation files you need before you begin reading the book.
- Answers to the Test Your Thinking questions.
- Additional Self-Review questions.
- Additional Resources such as links, tips, and errata
Installation Files
All of the exercises and questions are based on a sample schema called STUDENT. The required files to install this STUDENT schema and the installation instructions can be downloaded from the Web site.
Test Your Thinking
The answers to the Test Your Thinking sections are found at the Web site.
Additional Self-Review Questions
The Web site will have many other features, such as additional Self-Review questions, a message board, and periodically updated information about the book.
Visit the companion Web site and download the required files before starting the labs and exercises.
What You will Need
To complete the exercises you need the following:
The Oracle® database software
Oracle's SQL*Plus software or a Web browser
Access to the Internet
Oracle 10g
Oracle 10g is Oracle's latest version of the relational database software and its flagship product. You can use either the Personal Edition or Enterprise Edition. Various beta versions of the Enterprise edition of Oracle 10g were used to create the exercises for this book.
If you have a previous version of the Oracle database you will be able to complete a large majority of the exercises, however some syntax options and features are only available in Oracle 10g. Review the companion Web site for more details.
If you don't have the latest release of the Oracle software available, you can obtain a trial copy of the latest release from Oracle's Web site at http://www.oracle.com. You have the option of either downloading the Personal or Enterprise editions or purchasing a CD pack from the Oracle store (http://oraclestore.oracle.com) for a nominal fee.
Oracle SQL*Plus software or web browser
You can perform all the exercises in this book with Oracle's SQL*Plus software. Alternatively, you can use iSQL*Plus, a Web-based version, which does not require the installation of the Oracle SQL*Plus software on your individual machine. Only a browser is necessary. The iSQL*Plus version simplifies the editing and offers a superior display of the result. However, iSQL*Plus does not allow certain functionality and this is pointed out where applicable. For a list of unsupported commands refer to Appendix C, "SQL*Plus Command Reference." Instead of SQL*Plus you can also use alternate SQL execution environments and a list is available in Appendix H, "Resources."
Access to the Internet
You will need access to the Internet so that you can access the companion Web site: http://http://www.phptr.com/rischert3e. Here you will find files that are necessary to install the sample STUDENT schema.
About the Student Schema
Throughout this book, you access data from a sample schema called STUDENT, which contains information about a computer education program. The schema was designed to record data about instructors, courses, students, and their respective enrollments and grades.
After you download the installation files to create the schema within your Oracle database, you will be able to follow the exercises in the book. In Chapter 1, "SQL and Data," you are introduced to the relational concepts necessary to read the schema diagram. Appendix D, "Student Database Schema," shows you a graphical representation of the schema and Appendix E, "Table and Column Descriptions" lists descriptive information about each table and column.
Conventions Used in This Book
There are several conventions used in this book to make your learning experience easier. These are explained here.
notepaper icon This icon denotes advice and useful information about a particular topic or concept from the author to you, the reader.
lightbulb icon This icon flags tips that are especially helpful tricks that will save you time or trouble, for instance, a shortcut for performing a particular task or a method that the author has found useful.
warning icon Computers are delicate creatures and can be easily damaged. Likewise, they can be dangerous to work on if you're not careful. This icon flags information and precautions that not only save you headaches in the long run, but may even save you or your computer from harm.
web icon Passages referring to the book's companion Web site are flagged with this icon. The companion Web site is located at http://authors.phptr.com/rischert3e .
Errata
I have made every effort to make sure there are no errors in the text and code. However, to err is human. As part of the companion Web site, you will find corrections as they're spotted. The exercises and answers were tested against various beta versions of Oracle 10g. In case of a modification in functionality in the production version, you will see this listed as an errata item on Web site's errata page. If you find an error that has not been reported, please let me know by contacting me at ar280@yahoo.com. You comments and suggestions are greatly appreciated.
© Copyright Pearson Education. All rights reserved.
Available on NOOK devices and apps
- NOOK eReaders
- NOOK GlowLight 4 Plus
- NOOK GlowLight 4e
- NOOK GlowLight 4
- NOOK GlowLight Plus 7.8"
- NOOK GlowLight 3
- NOOK GlowLight Plus 6"
- NOOK Tablets
- NOOK 9" Lenovo Tablet (Arctic Grey and Frost Blue)
- NOOK 10" HD Lenovo Tablet
- NOOK Tablet 7" & 10.1"
- NOOK by Samsung Galaxy Tab 7.0 [Tab A and Tab 4]
- NOOK by Samsung [Tab 4 10.1, S2 & E]
- Free NOOK Reading Apps
- NOOK for iOS
- NOOK for Android
Want a NOOK? Explore Now
Share favorite eBook with your friends & family.
Most eBook can be loaned for up to 14 days.
See LendMe™ FAQs
The World’s #1 Hands-On Oracle SQL Workbook–Fully Updated for Oracle 11g
Crafted for hands-on learning and tested in classrooms worldwide, this book illuminates in-depth every Oracle SQL technique you’ll need. From the simplest query fundamentals to regular expressions and with newly added coverage of Oracle’s powerful new SQL Developer tool, you will focus on the tasks that matter most. Hundreds of step-by-step, guided lab exercises will systematically strengthen your expertise in writing effective, high-performance SQL. Along the way, you’ll acquire a powerful arsenal of useful skills–and an extraordinary library of solutions for your real-world challenges with Oracle SQL.
Coverage includes
- 100% focused on Oracle SQL for Oracle 11g, today’s #1 database platform–not “generic” SQL!
- Master all core SQL techniques including every type of join such as equijoins, self joins, and outer joins
- Understand Oracle functions in depth, especially character, number, date, timestamp, interval, conversion, aggregate, regular expressions, analytical, and more
- Practice all types of subqueries, such as correlated and scalar subqueries, and learn about set operators and hierarchical queries
- Build effective queries and learn fundamental Oracle SQL Developer and SQL*Plus skills
- Make the most of the Data Dictionary and create tables, views, indexes, and sequences
- Secure databases using Oracle privileges, roles, and synonyms
- Explore Oracle 11g’s advanced data warehousing features
- Learn many practical tips about performance optimization, security, and architectural solutions
- Avoid common pitfalls and understand and solve common mistakes
- For every database developer, administrator, designer, or architect, regardless of experience!
Customers Who Bought This Item Also Bought
-
- Sams Teach Yourself SQL in One…
- by Ryan StephensRon PlewArie D. Jones
-
Average rating: 2.4 Average rating:
-
- Oracle PL/SQL by Example
- by Benjamin RosenzweigElena Rakhimov
-
Average rating: 4.0 Average rating:
-
- Microsoft SQL Server 2008…
- by Hitachi ConsultingScott Cameron
-
Average rating: 0.0 Average rating:
-
- Microsoft SQL Server 2008…
- by William Stanek
-
Average rating: 3.0 Average rating:
-
- Microsoft SQL Server 2008 MDX…
- by Bryan SmithC. ClayHitachi Consulting
-
Average rating: 3.5 Average rating:
-
- Beginning PL/SQL: From Novice…
- by Donald Bales
-
Average rating: 5.0 Average rating:
-
- SQL Pocket Guide
- by Jonathan Gennick
-
Average rating: 0.0 Average rating:
-
- Oracle SQL Tuning Pocket…
- by Mark Gurry
-
Average rating: 0.0 Average rating:
-
- Smart Business Intelligence…
- by Lynn LangitKevin S. GoffDavide MauriSahil MalikJohn Welch
-
Average rating: 5.0 Average rating:
-
- MySQL Database Design and…
- by Robert D Schneider
-
Average rating: 0.0 Average rating:
-
- MySQL Clustering
- by Alex DaviesHarrison Fisk
-
Average rating: 0.0 Average rating:
-
- Microsoft SQL Server 2008…
- by Kalen DelaneyAdam MachanicPaul S. RandalKimberly L. TrippConor CunninghamBen Nevarez
-
Average rating: 3.1 Average rating:
-
- Effortless E-Commerce with PHP…
- by Larry Ullman
-
Average rating: 0.0 Average rating:
-
- Microsoft SQL Server 2008…
- by Brian Larson
-
Average rating: 0.0 Average rating:
-
- Joe Celko's Analytics and…
- by Joe Celko
-
Average rating: 0.0 Average rating:
-
- Pro Oracle Database 11g…
- by Darl Kuhn
-
Average rating: 0.0 Average rating:
-
- MySQL Tutorial
- by Luke WellingLaura Thomson
-
Average rating: 0.0 Average rating:
-
- Object-Oriented Programming in…
- by Robert Lafore
-
Average rating: 4.5 Average rating:
Recently Viewed
-
- Oracle SQL by Example
-
Average rating: 0.0 Average rating:
Related Subjects
Add to Wish List
Pick up in Store
There was an error finding your current location. Please try again or enter your zip code below.