_175x220.jpg)
Murach’s Java Servlets and JSP (3rd Edition)
Murach’s Python for Data Science (2nd Edition)
Murach’s Java Programming (6th Edition)
Murach’s R for Data AnalysisOur Ironclad Guarantee
You must be satisfied. Try our print books for 30 days or our eBooks for 14 days. If they aren't the best you've ever used, you can return the books or cancel the eBooks for a prompt refund. No questions asked!
Oracle SQL and PL/SQL (3rd Edition)
The more things change, the more they remain the same. Even with dramatic changes in technology such as the rise of cloud computing and AI, Oracle Database remains a dominant database, especially for mission-critical enterprise systems.
The third edition of this book works better than ever for learning how to use SQL and PL/SQL to work with an Oracle database. We’ve carefully checked and updated every example to follow modern best practices, and we’ve streamlined this book to focus on the most essential skills. Whether you’re a beginner with no coding experience or an experienced application developer who needs to learn how to use SQL and PL/SQL, you’ll appreciate the easy-to-understand explanations, clear examples, and step-by-step instructions.
College Instructors
Go to our instructor's site to learn more about this book and its instructor's materials.
This book is worth its cost just for its detailed and complete description of the SQL SELECT statement. The book takes 4 chapters (129 pages) to cover all aspects of it. I have not found any other book, including Oracle’s own documentation, which more completely describes how to use the SELECT statement."
Eric Mortensen, North East Ohio Oracle Users Group
- About this Book
- Table of Contents
- FREE Downloads
- Book FAQs
- Corrections
- Reviews
What you’ll learn in this book
Section 1: An introduction to SQL
This section presents the concepts and terms for working with any database. Then, it shows how to use SQL Developer to work with an Oracle database.
Section 2: The essential SQL skills
This section presents the essential SQL skills for retrieving data from a database and for adding, updating, and deleting that data. These skills move from the simple to the complex so you won’t have any trouble if you’re new to coding. But these skills are also sure to raise your expertise even if you’re already an experienced programmer.
Section 3: Database design and implementation
This section shows how to design a database and how to implement that design by using SQL statements to create a database. In addition, it shows how to create views and manage database security. Finally, it shows how to host an Oracle database in the cloud. This can make your database more affordable, flexible, and scalable.
Section 4: The essential PL/SQL skills
This section shows how to use Oracle’s procedural language, PL/SQL, to create stored procedures, functions, and triggers. In addition, it shows how to manage transactions and locking. This is a powerful set of PL/SQL skills.
Who this book is for
This book is for anyone who wants to learn how to work with an Oracle database, regardless of prior programming experience. This includes…
- Anyone who wants to learn how to use SQL to work with relational databases
- Developers who need to write SQL to retrieve and update data in a database
- Data analysts and scientists who need to write SQL to retrieve and analyze the data in a database
- Experienced developers who want to write SQL statements that work and perform better
- Developers who are interested in becoming a database administrator (DBA)
Why you’ll learn faster and better with this book
Here are three features that will help you learn faster and better.
It starts by showing how to query a database
This book starts by showing how to query an existing database rather than showing how to create a new database. Why?
First, it’s motivating to see results right away, and querying can provide immediate results with just a single line of code. Second, querying an existing database helps you gain insight into the decisions that have to be made when designing a database. As a result, learning how to query a database first makes it easier to learn how to design a database later.
It shows how to use Oracle SQL Developer
To work with an Oracle database, this book shows how to use SQL Developer, an intuitive graphical user interface that also provides many powerful features. This makes learning easier than using SQL*Plus, a command-line interface that has a steeper learning curve.
It presents hundreds of real-world examples
This book contains hundreds of example SQL statements that range from the simple to the complex, and every one of them is included in the download for this book. That way, your students can quickly get the idea of how a feature works from the simple examples and also see how the feature works in the real world from the complex ones.
What software you need for this book
- Oracle Database XE (Express Edition) or the
Oracle Cloud Autonomous Database service - Oracle SQL Developer
This software is available for free, and appendixes A (Windows) and B (macOS) show how to install it.
What's new for this edition
- We added a new chapter on using the Oracle Cloud Autonomous Database service to host an Oracle database in the cloud.
- We added coverage of container databases (CDBs) and pluggable databases (PDBs) to chapter 10.
- We added a new appendix that shows how to use macOS with this book.
- We added coverage of analytic functions to chapter 5.
- We streamlined chapters 1 and 2 to help your students get started more quickly.
- We combined chapters 8 and 17 to consolidate coverage of the temporal data types in a single chapter.
- We updated the entire book to reflect modern best practices.
- We improved the text and code examples to make the book easier to understand and use than ever.
What people say about this book
"If you use Oracle as a backend database to your VB, C#, or ASP.NET database applications, then Murach’s Oracle SQL and PL/SQL should be the next addition to your developer library."
- Jeff Manley, a1vbcode.com
"This book is worth its cost just for its detailed and complete description of the SQL SELECT statement. The book takes 4 chapters (129 pages) to cover all aspects of it. I have not found any other book, including Oracle’s own documentation, which more completely describes how to use the SELECT statement."
- Eric Mortensen, North East Ohio Oracle Users Group
"A lifesaver! Got stuck on one of my Oracle 9i assignments and it took just one look in this book and I was all set and ready to take on other assignments. It is really a reliable reference book."
- Posted at an online bookseller
"Although I am primarily a SQL Server programmer, this book explained many of the ‘Oracle’ ways to do the stuff I already do with SQL Server in a manner that I could understand and relate to. I found lots of examples of stuff that I was struggling with in entering into my first large, full-scale Oracle programming projects. It is open daily when I work on Oracle code now."
- Posted at an online bookseller
"Murach books have a reputation for accessibility to the novice user. Murach’s Oracle SQL and PL/SQL is just as accessible to the novice, but also contains plenty to assist other Oracle users.... The result is a book that is great for Oracle novices, but also great for casual intermediate users like myself."
- David O’Meara, JavaRanch.com
"I bought this book to take my SQL skills up a notch or more. This book really does that. It moves from the basics to advanced queries. The advanced queries are very well explained and you get useful skills and understand how to extend this to other queries."
- Posted at an online bookseller
“This book is a must-have for programmers looking to get into data management. It is clear and simple and the examples are easy to understand. All programmers that deal with databases should have this book in their library.”
- Posted at an online bookseller
“Loved the layout. Not long winded; just the facts. The page on the left gives a more indepth explanation. The page on the right gives you the nitty-gritty of what you need to know. I highly recommend the book and will look for Murach's books in the future.”
- Posted at an online bookseller
View the table of contents for this book in a PDF: Table of Contents (PDF)
Click on any chapter title to display or hide its content.
Section 1 An introduction to SQL
Chapter 1 An introduction to relational databases and SQL
An introduction to client/server systems
The hardware components of a client/server system
The software components of a client/server system
Other client/server architectures
An introduction to the relational database model
How a database table is organized
How tables are related
How columns in a table are defined
An introduction to SQL and SQL-based systems
A brief history of SQL
A comparison of four relational databases
SQL statements and comments
An introduction to SQL statements
Typical SQL statements
SQL coding guidelines
How to code comments
Chapter 2 How to use SQL Developer
How to use SQL Developer to work with a database
How to create a database connection
How to navigate through the database objects
How to view the column definitions and data for a table
How to use SQL Developer to run SQL statements
How to enter and run a SQL statement
How to handle syntax errors
How to open and save SQL scripts
How to code and run SQL scripts
How to use the Oracle Database documentation
Section 2 The essential SQL skills
Chapter 3 How to retrieve data from a single table
An introduction to the SELECT statement
How to interpret syntax in this book
The basic syntax of the SELECT statement
SELECT statement examples
How to code the SELECT clause
How to code column specifications
How to name the columns in a result set
How to code string expressions
How to code arithmetic expressions
How to use scalar functions
How to use DISTINCT to eliminate duplicate rows
How to code the WHERE clause
How to use the comparison operators
How to use the AND, OR, and NOT logical operators
How to use the IN phrase
How to use the BETWEEN phrase
How to use the LIKE operator
How to use the REGEXP_LIKE function
How to use the IS NULL condition
How to code the ORDER BY clause
How to sort by a column name
How to sort by an alias, an expression, or a column number
Two more skills
How to use the row limiting clause
How to test expressions
Chapter 4 How to retrieve data from two or more tables
How to work with inner joins
How to code an inner join
When and how to use table aliases
How to use compound join conditions
How to use a self-join
How to join more than two tables
How to use the implicit inner join syntax
How to work with outer joins
How to code an outer join
Outer join examples
Outer joins that join more than two tables
How to use the implicit outer join syntax
Other skills for working with joins
How to combine inner and outer joins
How to join tables with the USING keyword
How to join tables with the NATURAL keyword
How to use cross joins
How to work with unions
The syntax of a union
Unions that combine data from different tables
Unions that combine data from the same table
How to use the MINUS and INTERSECT operators
Chapter 5 How to code summary queries
How to work with aggregate functions
How to code aggregate functions
Queries that use aggregate functions
How to group and summarize data
How to code the GROUP BY and HAVING clauses
Queries that use the GROUP BY and HAVING clauses
How the HAVING clause compares to the WHERE clause
How to code complex search conditions
How to summarize data using the ROLLUP CUBE clause
How to use ROLLUP
How to use CUBE
How to code analytic functions
How analytic functions work
How to code frames
Two more examples of frames
How to use named windows
How to use the ranking functions
Chapter 6 How to code subqueries
An introduction to subqueries
How to use subqueries
How subqueries compare to joins
How to code subqueries in search conditions
How to use subqueries with the IN operator
How to compare a subquery with an expression
How to use the ALL keyword
How to use the ANY and SOME keywords
How to code correlated subqueries
How to use the EXISTS operator
Other ways to use subqueries
How to code subqueries in the FROM clause
How to code subqueries in the SELECT clause
Guidelines for working with complex queries
A complex query that uses subqueries
A procedure for building complex queries
How to use subquery factoring
How to use the WITH clause
How to code a recursive query
How to use the hierarchical query clause
Chapter 7 How to insert, update, and delete data
How to create test tables
How to re-create the tables for this book
How to create a table from a SELECT statement
How to insert new rows
How to insert a single row
How to insert default values and null values
How to use a subquery to insert multiple rows
How to update existing rows
How to update rows
How to use a subquery in an UPDATE statement
How to delete existing rows
How to delete rows
How to use a subquery in a DELETE statement
How to commit and roll back changes
Chapter 8 How to work with data types and functions
Data type overview
How to work with character data
The character data types
How to use character functions
How to parse a string
How to work with numeric data
The numeric data types
Common number format elements
How to use numeric functions
How to search for floating-point numbers
How to work with temporal data
The temporal data types
Common datetime format elements
How to use datetime functions
How to perform a date search
Common timestamp and interval formats
How to use timestamp functions
How to use interval functions
How to use the EXTRACT function
How to convert data from one type to another
How to convert characters, numbers, and dates
How to sort strings in numerical sequence
How to perform a time search
How to convert characters to and from their numeric codes
Other functions you should know about
How to use the CASE expression
How to use the COALESCE, NVL, and NVL2 functions
How to use the GROUPING function
Section 3 Database design and implementation
Chapter 9 How to design a database
How to design a data structure
The basic steps for designing a data structure
How to identify the data elements
How to subdivide the data elements
How to identify the tables and assign columns
How to identify the primary and foreign keys
How to enforce the relationships between tables
How normalization works
How to identify the columns to be indexed
How to normalize a data structure
The seven normal forms
How to apply the first normal form
How to apply the second normal form
How to apply the third normal form
When and how to denormalize a data structure
Chapter 10 How to create a database
How to work with container databases
An introduction to CDBs and PDBs
How to create and drop a pluggable database
How to work with tables
How to create a table
How to code a primary key constraint
How to code a foreign key constraint
How to code a check constraint
How to alter the columns of a table
How to alter the constraints of a table
How to rename, truncate, and drop a table
How to work with indexes
How to create an index
How to drop an index
How to work with sequences
How to create a sequence
How to use a sequence
How to alter a sequence
How to drop a sequence
How to automatically generate ID values
A script that’s used to create a schema
An introduction to scripts
How the DDL statements work
How to use SQL Developer
How to work with tables, indexes, and sequences
How to display an EER diagram for a table
Chapter 11 How to create views
An introduction to views
How views work
Benefits of using views
How to work with views
How to create a view
How to create an updatable view
How to create a read-only view
How to use WITH CHECK OPTION
How to insert or delete rows through a view
How to alter or drop a view
How to use SQL Developer with views
Chapter 12 How to manage database security
How to work with users and roles
How to create a user
How to create an admin user
How to alter and drop a user
How to create and drop a role
How to work with privileges and synonyms
System privileges and object privileges
How to grant privileges
How to revoke privileges
How to work with synonyms
A script that creates roles and users
How to view the privileges for users and roles
How to use SQL Developer
How to create an admin connection for a PDB
How to work with users
How to grant and revoke roles
How to grant and revoke system privileges
Chapter 13 How to host a database in the cloud
How to get started with Oracle Cloud
The Oracle Cloud portal
How to create a database in the cloud
How to create a user for a schema
How to create the tables for a schema
How to view the database objects for a schema
How to use SQL Developer with a cloud database
How to connect to a cloud database
How to run SQL against a cloud database
More skills for working with a cloud database
How to restore and delete a cloud database
How to restart a cloud database
Section 4 The essential PL/SQL skills
Chapter 14 How to write PL/SQL code
An introduction to PL/SQL
An anonymous PL/SQL block in a script
Statements for working with PL/SQL and scripts
How to code the basic PL/SQL statements
How to print data to an output window
How to declare and use variables
How to code IF statements
How to code CASE statements
How to code loops
How to use a cursor
How to work with composite variables
How to use collections
How to use records
How to work with exceptions and errors
How to handle exceptions
Predefined exceptions
How to drop database objects without displaying errors
Chapter 15 How to manage transactions and locking
How to work with transactions
How to commit and roll back transactions
How to work with save points
How to work with concurrency and locking
How concurrency and locking are related
How to set the transaction isolation level
Best practices for concurrency
Chapter 16 How to create stored procedures and functions
How to code stored procedures
How to create a stored procedure
How to call a stored procedure
How to code input and output parameters
How to code optional parameters
How to raise a predefined exception
How to raise a user-defined exception
A stored procedure that inserts a row
How to drop a stored procedure
How to code functions
How to create and call a function
A function that uses multiple RETURN statements
How to drop a function
How to work with packages
How to create a package
How to drop a package
How to use SQL Developer
How to view and drop procedures, functions, and packages
How to edit and compile procedures and functions
How to grant and revoke privileges
How to debug procedures and functions
Chapter 17 How to create triggers
How to work with triggers
How to create a trigger for a table
A trigger that enforces data consistency
How to use conditional predicates
How to create a trigger for a view
How to create a system trigger
How to enable, disable, rename, or drop a trigger
Other skills for working with triggers
How to create a compound trigger
A trigger that causes the mutating-table error
How to solve the mutating-table problem
How to use SQL Developer
How to view, enable, disable, rename, or drop a trigger
How to edit a trigger
Appendices
Appendix A How to set up Windows for this book
How to install Oracle Database XE
How to install Oracle SQL Developer
How to download the files for this book
How to connect as the sysdba user
How to create the schemas for this book
How to import connections for the schemas
How to make sure your system is set up correctly
How to stop and start the database service
Appendix B How to set up macOS for this book
How to download the files for this book
How to install Oracle SQL Developer
How to set up the database for this book
How to create the connections for this book
How to make sure your system is set up correctly
BOOK EXAMPLES AND EXERCISES
This download includes:
- A script sets up the database for this book
- The scripts for every code example in the book
- Solutions to the exercises that are at the end of each chapter
SAMPLE PDFs
Not sure whether this is the book for you? Download a free PDF for a sample chapter and see for yourself how easy learning Oracle SQL and PL/SQL can be.
Appendix A: How to set up Windows for this book
This appendix shows how to set up a Windows computer for this book, including instructions for installing Oracle Database XE and SQL Developer. When you’re done, you’ll be able to work through the examples and exercises presented in this book.
Download Now
Appendix B: How to set up macOS for this book
This appendix shows how to set up a macOS computer for this book, including instructions for setting up the Oracle Cloud Autonomous Database service and installing SQL Developer. When you’re done, you’ll be able to work through the examples and exercises presented in this book.
Download Now
Chapter 3: How to retrieve data from a single table
This chapter shows you how to use the most important SQL statement, the SELECT statement, to retrieve data from a single table.
Download Now
We’ll be posting answers to the frequently asked questions (FAQs) for this book here. So if you have any questions, please email us. Thanks!
There are no book corrections that we know of at this time. But if you find any errors in this book, please email us so we can post the corrections here. Thank you!