There's never been a time in the Information Technology industry where professional certifications have been more important. Because of the specialized nature of technological careers today, certifications are considered by some to be just as important as technological degrees. This focus on certifications has led to the rise of an entire industry around books that assist readers in preparing for various certification tests. In the author's opinion, many, if not most, of these books make a lot of assumptions as to the prior knowledge of the reader and serve more as reference material than a cohesive learning experience.
In my role as an instructor of Oracle technologies, I have noticed a shift in the types of people seeking to learn Oracle. In the past several years, more and more students are seeking to break into an Oracle career path with little or no experience. Whether they come from backgrounds in business analysis, project management, or other non-database technical areas, these students need to be able to learn Oracle from the ground up. When instructing these types of students, I cannot make assumptions as to the knowledge they bring with them. We must start at the beginning and work our way to certification level knowledge. To accomplish this goal in class, the accompanying textbook must be designed in the same way.
Similarly, many certification books today serve only as exam cram books that neglect an application to real world scenarios. Readers of these types of books may find themselves with a certification, yet possess no way to apply the knowledge in their first job.
My goal in writing this book is to address both of these problems. This book attempts to begin at the foundation and continue to the knowledge of the subject required for the certification exam, using real world examples and tips along the way. This book is heavily example-oriented and is intended to serve as step-by-step instruction instead of reference material. In essence, I attempt to bring the classroom experience to the reader, using examples, real world tips, and end-of-the-chapter review. This book is written to be read cover to cover, with the reader completing the examples and questions as they go. Using this process, it is my hope that readers can truly begin at the beginning, regardless of previous experience, and learn SQL in a relevant way that will serve them in their pursuit of an Oracle certification as well as an Oracle career path.
The Oracle Database 11g: SQL Fundamentals I exam is the first stepping stone in earning the Oracle Certified Associate Certification for Oracle Database 11g. The SQL programming language is used in every major relational database today, and understanding the real-world application of it is the key to becoming a successful DBA.
This book gives you the essential real-world skills to master relational data manipulation with Oracle SQL and prepares you to become an Oracle Certified Associate. Beginners are introduced to concepts in a logical manner while practitioners can use it as a reference to jump to relevant concepts directly.
We begin with the essentials of why databases are important in today's information technology world and how they work. We continue by explaining the concepts of querying and modifying data in Oracle using a range of techniques, including data projection, selection, creation, joins, sub-queries, and functions. Finally, we learn to create and manipulate database objects and to use them in the same way as today's expert SQL programmers.
This book prepares you to master the fundamentals of the SQL programming language using an example-driven approach that is easy to understand.
This definitive certification guide provides a disciplined approach to be adopted for successfully clearing the 1Z0-051 SQL Fundamentals I exam, which is the first stepping stone towards attaining the OCA on Oracle Database 11g certification.
Each chapter contains ample practice questions at the end. A full-blown mock test is included for practice so you can test your knowledge and get a feel for the actual exam.
Chapter 1, SQL and Relational Database, examines the purpose and use of relational database management systems, including the use of entity relationship diagrams and the structure of tables. We then introduce Structured Query Language and the SQL Developer tool.
Chapter 2, SQL SELECT Statements, explores the most foundational SQL clause; the SELECT statement. We use SELECT statements for single and multi-column data retrieval and take a look at using SQL to do basic mathematical operations.
Chapter 3, Using Conditional Statements, examines the concept of data selection using the WHERE clause paired with conditions. In it, we construct selective statements using conditions of both equality and non-equality. We also use range and set conditions with the WHERE clause for further data selectivity. Finally, we examine the concept of sorting data using the ORDER BY clause.
Chapter 4, Data Manipulation with DML, explores the use of Data Manipulation Language to add, modify, and remove table data using INSERT, UPDATE, and DELETE statements. Lastly, we look at transaction control in SQL.
Chapter 5, Combining Data from Multiple Tables, examines the concept of combining data from multiple tables using various join statements. We accomplish this using both ANSI standard and Oracle syntax.
Chapter 6, Row Level Data Transformation, explores the concept of row level data transformation using single-row functions. We use these functions to transform date, character and numeric data.
Chapter 7, Aggregate Data Transformation, explores data transformation using functions, this time with multi-row functions. We combine these functions with the GROUP BY and HAVING statements to perform aggregate data transformation.
Chapter 8, Combining Queries, focuses on using several types of subqueries to combine data from multiple tables. We close the chapter by exploring set theory in Oracle and implement it using SQL set operators.
Chapter 9, Creating Tables, introduces the concept of Data Definition Language and how to use it to create database tables. We also use SQL to write database constraints that enforce business data rules.
Chapter 10, Creating Other Database Objects, examines the use of DDL statements to create some of the other common objects available to us in Oracle. We use these statements to create indexes, views, sequences, and synonyms.
Chapter 11, Using SQL in Application Development, examines how SQL is used in real-world programming languages such as PL/SQL, Perl, Python, and Java. We close by offering hints and strategies for taking the SQL certification exam.
Appendix A , Companylink Table Reference, a reference section describing the various tables used as examples in this book.
Appendix B , Getting Started with APEX, shows an alternative method for completing the examples in this book that does not require installing the Oracle database software.
The answers to the Test Your Knowledge section can be found at http://www.packtpub.com/sites/default/files/downloads/testyourknowledge_answers.pdf
Mock practice test paper can be downloaded from http://www.packtpub.com/sites/default/files/downloads/mock_test_paper.pdf
Mock test paper answers will be available at http://www.packtpub.com/sites/default/files/downloads/mock_test_answers.pdf
This book is heavily example-oriented. As such, it will be beneficial for the reader to download and install the Oracle database software as outlined in Chapter 1, SQL and Relational Database. The reader will also receive the greatest benefit by downloading and running the example code available from the Packt support website. No prior knowledge of programming or database concepts is required.
This book is for anyone who needs the essential skills to pass the Oracle Database SQL Fundamentals I exam and use those skills in daily life as an SQL developer or database administrator.
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text are shown as follows: "Simply unzip the companylink.zip file into a directory and double-click the companylink_db.cmd file."
A block of code is set as follows:
SELECT {column, column, …}
FROM {table};Any command-line input or output is written as follows:
INSTR(column_expression, search_character, starting_position, occurrence_number)
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "In our example, data such as Firstname, lastname, Address and Branch name are the attributes of the Employee entity".
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]> and mention the book title via the subject of your message.
If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or e-mail <[email protected]>.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]>
with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.