Description
The past few decades have seen a proliferation of organizations that rely heavily on information technology. These organizations store their data in databases, and many choose Oracle database management systems to access their data. The current Oracle database version, Oracle 12c, is a database management system that enables users to create, manipulate, and retrieve data. The purpose of this textbook is to introduce students to basic SQL commands for interacting with Oracle 12c databases in a business environment. In addition, concepts relating to objectives of the current Oracle certification exams have been incorporated for students wanting to pursue certification.
The Intended Audience
This textbook has been designed for students in technical two-year or four-year programs who need to learn how to interact with databases. Although having an understanding of database design is preferable, an introductory chapter has been included to review the basic concepts of E-R modeling and the normalization process.
Oracle Certification Program (OCP)
This textbook covers the objectives of SQL Fundamentals Exams available: 1Z0-051 for Oracle 11g and Exam 1Z0-061 for Oracle Database 12c. Most objectives for Exam 1Z0- 047, Oracle Database SQL Expert, are also covered. Any of these exams serve as the first exam in the Oracle Application Development and Oracle Database Administrator Oracle Certified Associate level certification tracks. Information about registering for these exams, along with other reference material, is available at www.oracle.com.
The Approach
The concepts introduced in this textbook are discussed in the context of a hypothetical real-world business: an online book retailer named JustLee Books. The company’s business operation and the database structure are introduced and analyzed, and as commands are introduced throughout the textbook, they’re modeled with examples using the JustLee Books database. Using consistent examples of a hypothetical company helps you learn the syntax of commands and how to use them in a real-world environment. In addition, a script file that generates the database is available to give you hands-on practice in re-creating examples and practicing variations of SQL commands to enhance your understanding.
To explain what a database is and how it’s created, this textbook initially focuses on creating tables and learning how to perform data manipulation operations. After you’re familiar with the database structure, the focus then turns to querying a database.In Chapters 8 through 13, you learn how to retrieve data from the database, using the many options of a SELECT statement, including row filtering, joins, functions, and subqueries.
To reinforce the material, each chapter includes a chapter summary and, when appropriate, a syntax guide for the commands covered in the chapter. In addition, each chapter includes review questions and hands-on activities that test your knowledge and challenge you to apply that knowledge to solving business problems. A running case study that builds throughout the textbook provides a second real-world setting—a city jail system—as another opportunity to work with databases.
BRIEF CONTENTS
Preface xiv
Chapter 1 Overview of Database Concepts 1
Chapter 2 Basic SQL SELECT Statements 25
Chapter 3 Table Creation and Management 57
Chapter 4 Constraints 103
Chapter 5 Data Manipulation and Transaction Control 141
Chapter 6 Additional Database Objects 181
Chapter 7 User Creation and Management 225
Chapter 8 Restricting Rows and Sorting Data 255
Chapter 9 Joining Data from Multiple Tables 295
Chapter 10 Selected Single-Row Functions 347
Chapter 11 Group Functions 401
Chapter 12 Subqueries and MERGE Statements 449
Chapter 13 Views 495
Appendix A Tables for the JustLee Books Database 539
Appendix B SQL*Plus and SQL Developer Overview 547
Appendix C Oracle Resources 555
Appendix D SQL*Loader 557
Appendix E SQL Tuning Topics 561
Appendix F SQL in Various Databases 579
Glossary 583
Index 589
Copyright