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 SQL Interactive Workbook, second 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. 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 9i 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.
What Makes This Book Different
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 teaches you about many Oracle-specific technology features that are essential to successful systems development.
The book's exercises build knowledge step-by-step by introducing you to relational database concepts, the SQL*Plus environment, and the SQL language. Learning involves not just reading about a subject, but also doing it. The book's focus is to give the reader examples of how the SQL language is commonly used, with many exercises reinforcing the learning experience.
Unlike other SQL books, this book is not a reference book. 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 www.phptr.comischert.
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. We 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
Each chapter contains:
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
On the companion Web site you will find three very important things:
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 also 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.
What You will Need
Oracle 9i is Oracle's relational database software and its flagship product. You can use either the Personal Edition or Enterprise Edition. The Enterprise editions of Oracle 9.01 and 9.2 were used to create the exercises for this book, but subsequent versions should be compatible. 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 9i.
Oracle SQL*Plus Software or Web Browser
You can perform all the exercises in this book with Oracle's SQL*Plus software. Oracle introduced iSQL*Plus with Oracle version 8.1.7. This browser-based version does not require the installation of the Oracle SQL*Plus software on your individual machine. Only a browser is necessary and access to an HTTP iSQL*Plus server. 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://www.phptr.com.rischert) Here you will find files that are necessary to install the sample STUDENT schema.
About the Student Schema
Throughout this workbook, 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 workbook. 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.
Foreword.
Acknowledgments.
About the Author.
Introduction.
1. SQL and Data.
Data, Databases, and the Definition of SQL. Table Relationships. The Student Schema Diagram.
2. SQL: The Basics.
The SQL*Plus Environment. The Anatomy of a SELECT Statement. Editing a SQL Statement. The WHERE Clause: Comparison and Logical Operators. The ORDER BY Clause.
3. Character, Number, and Miscellaneous Functions.
Character Functions. Number Functions. Miscellaneous Single-]Row Functions.
4. Date and Conversion Functions.
Applying Oracle's Date Format Models. Performing Date and Time Math. Using the New Oracle 9i DATETIME Datatypes. Performing Calculations with the INTERVAL Datatype. Converting from One Datatype to Another.
5. Aggregate Functions, Group By, and Having.
Aggregate Functions. The GROUP BY and HAVING Clauses.
6. Equijoins.
The Two-Table Join. Joining Three or More Tables.
7. Subqueries.
Simple Subqueries. Correlated Subqueries. Inline Views, Scalar Subquery Expressions, and the Subquery WITH Clause. ANY, SOME, and ALL Operators in Subqueries.
8. Set Operators.
The Power of UNION and UNION ALL. The MINUS and INTERSECT Set Operators.
9. Complex Joins.
Outer Joins. Self-Joins.
10. Insert, Update, and Delete.
Creating Data and Transaction Control. Updating and Deleting Data.
11. Create, Alter, and Drop Tables.
Creating and Dropping Tables. Altering Tables and Manipulating Constraints.
12. Views, Indexes, and Sequences.
Creating and Modifying Views. Indexes. Sequences.
13. The Data Dictionary and Dynamic SQL Scripts.
The Oracle Data Dictionary Views. Dynamic SQL Scripts.
14. Security.
Users, Privileges, Roles, and Synonyms.
15. Advanced SQL Queries.
Advanced SQL Concepts and Analytical Functions. ROLLUP and CUBE Operators. Hierarchical Queries.
16. SQL Optimization.
The Oracle Optimizer and Writing Effective SQL Statements.
Appendix A. Answers to Self-Review Questions.
Appendix B. SQL Formatting Guide.
Appendix C. SQL*Plus Command Reference.
Appendix D. Student Database Schema.
Appendix E. Table and Column Descriptions.
Appendix F. Additional Example Tables.
Appendix G. Navigating through the Oracle Documentation.
Appendix H. Resources.
Index.
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 SQL Interactive Workbook, second 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. You will be able to retain the material more easily and the additional example questions reinforce and further enhance the learning experience.
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 9i 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.
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 teaches you about many Oracle-specific technology features that are essential to successful systems development.
The book's exercises build knowledge step-by-step by introducing you to relational database concepts, the SQL*Plus environment, and the SQL language. Learning involves not just reading about a subject, but also doing it. The book's focus is to give the reader examples of how the SQL language is commonly used, with many exercises reinforcing the learning experience.
Unlike other SQL books, this book is not a reference book. 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 www.phptr.comischert.
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. We hope that you will enjoy this learning experience and come away with the knowledge you hoped to gain.
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.
Each chapter contains:
The chapters should be completed in sequence because concepts covered in earlier chapters are required for the completion of exercises in later chapters.
On the companion Web site you will find three very important things:
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.
The answers to the Test Your Thinking sections are also found at the Web site.
The Web site will have many other features, such as additional Self-Review questions, a message board, and periodically updated information about the book.
Oracle 9i is Oracle's relational database software and its flagship product. You can use either the Personal Edition or Enterprise Edition. The Enterprise editions of Oracle 9.01 and 9.2 were used to create the exercises for this book, but subsequent versions should be compatible. 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 9i.
You can perform all the exercises in this book with Oracle's SQL*Plus software. Oracle introduced iSQL*Plus with Oracle version 8.1.7. This browser-based version does not require the installation of the Oracle SQL*Plus software on your individual machine. Only a browser is necessary and access to an HTTP iSQL*Plus server. 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."
You will need access to the Internet so that you can access the companion Web site. (http://www.phptr.com.rischert) Here you will find files that are necessary to install the sample STUDENT schema.
Throughout this workbook, 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 workbook. 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.
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 SQL Interactive Workbook, second 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. You will be able to retain the material more easily and the additional example questions reinforce and further enhance the learning experience.
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 9i 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 ascritical 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.
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 teaches you about many Oracle-specific technology features that are essential to successful systems development.
The book's exercises build knowledge step-by-step by introducing you to relational database concepts, the SQL*Plus environment, and the SQL language. Learning involves not just reading about a subject, but also doing it. The book's focus is to give the reader examples of how the SQL language is commonly used, with many exercises reinforcing the learning experience.
Unlike other SQL books, this book is not a reference book. 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 www.phptr.comischert.
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. We hope that you will enjoy this learning experience and come away with the knowledge you hoped to gain.
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.
Each chapter contains:
The chapters should be completed in sequence because concepts covered in earlier chapters are requi completion of exercises in later chapters.
On the companion Web site you will find three very important things:
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.
The answers to the Test Your Thinking sections are also found at the Web site.
The Web site will have many other features, such as additional Self-Review questions, a message board, and periodically updated information about the book.
Oracle 9i is Oracle's relational database software and its flagship product. You can use either the Personal Edition or Enterprise Edition. The Enterprise editions of Oracle 9.01 and 9.2 were used to create the exercises for this book, but subsequent versions should be compatible. 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 9i.
You can perform all the exercises in th Oracle's SQL*Plus software. Oracle introduced iSQL*Plus with Oracle version 8.1.7. This browser-based version does not require the installation of the Oracle SQL*Plus software on your individual machine. Only a browser is necessary and access to an HTTP iSQL*Plus server. 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."
You will need access to the Internet so that you can access the companion Web site. Here you will find files that are necessary to install the sample STUDENT schema.
Throughout this workbook, 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 workbook. 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.
Overview