Murach college books and courseware since 1974
Customer Service 1-800-221-5528
Oracle SQL and PL/SQL (3rd Edition)
List price: $59.50
The more things change, the more they remain the same. Even with dramatic technological changes such as the rise of cloud computing and AI, Oracle Database remains a dominant database, especially for mission-critical enterprise systems.
The newly updated third edition of this book makes it easier than ever to teach a course that shows how to use SQL and PL/SQL to work with an Oracle database. It works equally well for beginners who have no coding experience or experienced programmers who need to develop applications that work with an Oracle database. Request your review copy today!
Now available as a Canvas course!
The Canvas course file contains all the objectives, quizzes, assignments, and slides that you need to run an effective course. It only takes a few clicks to import it into the Canvas LMS. Then, you can customize it for your course when it is available. Learn more.
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 intermediate users.”
David O’Meara, JavaRanch.com
- About this Book
- Table of Contents
- Courseware
- FAQs
- Corrections
Book description
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 students won’t have any trouble if they’re new to SQL. But these skills are also sure to raise your expertise even if you already have SQL experience.
Section 3: Database design and implementation
This section shows your students 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.
Section 4: The essential PL/SQL skills
This section shows your students 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.
Book features
Here are three features that will help your students 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 for your students to see results right away, and querying can provide immediate results with just a single line of code. Second, querying an existing database helps your students 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 for students 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. 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’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.
What courses this book can be used for
- As the main text for any SQL course.
- As a supplementary text for a traditional database course.
What software your students need
- 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.
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
The instructor’s materials that you can request from this site provide everything you need for an effective course.
Objectives
- Help your students focus on the skills that they should master.
Test banks
- Provide a way to test comprehension.
- Can be imported to all modern LMSs.
- Are designed to test the skills described by the objectives for each chapter.
- Use only multiple-choice test questions because they have the highest validity.
Extra exercises
- Give your students a chance to gain valuable hands-on experience.
- Can be used for assessing coding skills since the solutions aren’t available to students (unlike the exercises that are printed in the book).
PowerPoint slides
- Summarize the critical information presented in the book.
- Start with the instructional objectives for each chapter.
For a detailed description of all the materials, please see the Instructor’s Summary PDF when it is available.
If you use the Canvas LMS, we also provide a Canvas course file that you can use to import most of these materials with just a few clicks.
We’ll be posting answers to the frequently asked questions (FAQs) for this book here. So if you have any questions, please e-mail us. Thanks!
There are no book corrections that we know of at this time. But if you find any errors in this book, please e-mail us so we can post the corrections here. Thank you!
Contact Murach Books
Buy Murach Books
This is our site for college instructors. To buy Murach books, please visit our retail site.