Murach college books and courseware since 1974
Customer Service 1-800-221-5528
Murach’s Oracle SQL and PL/SQL for Developers (2nd Edition)
List price: $54.50
Over the years, Oracle Database has gained a reputation for being expensive and difficult to teach. Today, however, there’s a free Express Edition and a free IDE called SQL Developer that makes it easier for students to enter, edit, and run SQL and PL/SQL statements on their own computers. Better yet, this book makes it easier than ever for your students to master the SQL and PL/SQL skills for working with an Oracle database.
Buy this book
You are currently on the Murach site for instructors. To buy this book, please visit our retail site.
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 Oracle Database and Oracle SQL Developer to run SQL statements.
Section 2: The essential SQL skills
This section presents all the 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 reach a professional level of competence.
Section 3: Database design and implementation
Here, your students learn how to design a database, how to implement that design by using DDL (Data Definition Language) statements, how to create views, and how to manage database security.
Section 4: The essential PL/SQL skills
This section shows how to use Oracle’s procedure language, PL/SQL, to create stored procedures, functions, and triggers. It also shows how to manage transactions and locking.
Section 5: Advanced data types
Last, this section shows how to work with the timestamp, interval, and large object data types. These data types provide features that are critical for storing data in today’s global and digital world.
Book features
Like all our books, this one has all of the Murach features that help your students learn faster and better. But here are a couple of features that are unique to this book.
It shows how to use a database before how to create one
Unlike most SQL books, this one starts by showing how to query an existing database rather than showing how to create a new database. Why?
For one thing, it’s motivating to see results right away, and querying provides that for your students; database design requires more study and background before you see results. But querying also helps to provide that background, giving your students insight into the kind of decisions that have to be made in the design phase and the effects they have down the road.
As a result, doing querying first makes it easier for students to learn how to design and implement a database in section 3. And it also prepares them for using database features like stored procedures in section 4.
It shows how to use Oracle SQL Developer
Unlike most Oracle books, this one shows how to use Oracle SQL Developer to enter and run SQL statements. SQL Developer is a graphical tool that’s an intuitive and user-friendly replacement for SQL*Plus, an arcane command prompt tool that has been around since the early days of Oracle. By using SQL Developer instead of SQL*Plus, your students will get more practice in less time and learn more quickly.
Real-world examples range from the simple to the complex
Like all our books, this one includes hundreds of examples. In this case, though, the examples are SQL statements that range from the simple to the complex. That way, your students can quickly get the idea of how a SQL feature works from the simple examples, but they’ll also see examples that illustrate real-world complexity...a feature that’s often missing in competing books.
It continues to help your students long after the course is over
Instructors tell us that students tend to keep our books to have as references on the job. So even if you don’t have time to cover everything in this book, they’ll still get their money’s worth.
What’s new for this edition
From a didactic point of view, the two biggest improvements are:
- The solutions to the exercises in the book are included in the student download so your students will be able to solve their own problems as they do the exercises.
- The instructor’s materials include a second set of exercises and solutions that you can use to test the skills of your student, and you will control the access to these solutions.
In terms of content, the book is thoroughly updated in terms of SQL Developer, but it includes just a few Oracle 12c additions:
- Top-N queries and pagination, which let you use the FETCH and OFFSET clauses to limit the number of rows returned by a query and to return a specified range of rows.
- Automatic ID generation, which lets you define a primary key column so its ID is automatically generated instead of having to use triggers to do that.
What courses this book can be used for
- This book and its instructional materials work great for any SQL course.
- This book is also an excellent supplementary text for a more traditional database course.
Presentation options
Like all our books, the sections in this one are modular. That means that you can assign sections 3, 4, or 5 any time after your students complete sections 1 and 2. In other words, you don’t have to assign the sections in sequence.
In fact, with this book, you can take that one step further. Because once you teach chapters 1 through 4, you can skip to any of the other sections. For instance, you could skip to chapter 9 (database design) and chapter 10 (how to create tables, indexes, and sequences) after the first four chapters. Then, you could go back to chapter 7 (how to insert, update, and delete data) and chapter 8 (data types and functions), followed by a jump to chapter 13 (how to write PL/SQL code), chapter 14 (transactions and locking), and chapter 15 (stored procedures).
In short, you can design your course to fit your teaching priorities. Your course doesn’t have to be driven by the sequence of the book. And you don’t have to assign all of the chapters in the book. That’s the way the book was designed to be used.
What software your students need
To run SQL statements with an Oracle database using the techniques in this book, we recommend:
- The Express Edition of Oracle Database 11g
- Oracle SQL Developer
Both of these products can be downloaded for free from Oracle’s website. And appendix A of this book provides complete instructions for installing them.
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 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 Oracle, DB2, and Microsoft SQL Server
The SQL statements
An introduction to the SQL statements
How to work with database objects
How to query a single table
How to join data from two or more tables
How to add, update, and delete data in a table
How to work with views
SQL coding guidelines
An introduction to PL/SQL
How to work with stored procedures
How to work with user-defined functions
How to work with triggers
How to use SQL from an application program
Three data access models
Java code that retrieves data from an Oracle database
Chapter 2 How to use Oracle SQL Developer and other tools
How to work with an Oracle database
How to start and stop the database service
How to use the Database Home Page
How to use SQL*Plus
How to use SQL Developer to work with a database
How to create a database connection
How to export or import database connections
How to navigate through the database objects
How to view the column definitions for a table
How to view the data for a table
How to edit the column definitions
How to use SQL Developer to run SQL statements
How to enter and execute a SQL statement
How to work with the Snippets window
How to handle syntax errors
How to open and save SQL statements
How to enter and execute a SQL script
How to use the SQL Reference manual
How to view the manual
How to look up information
Section 2 The essential SQL skills
Chapter 3 How to retrieve data from a single table
An introduction to the SELECT statement
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 the Dual table
How to use the DISTINCT keyword to eliminate duplicate rows
How to use the ROWNUM pseudo column to limit the number of 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 operator
How to use the BETWEEN operator
How to use the LIKE operator
How to use the IS NULL condition
How to code the ORDER BY clause
How to sort a result set by a column name
How to sort a result set by an alias, an expression, or a column number
How to code the row limiting clause
How to limit the number of rows
How to return a range of rows
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 work with tables from different schemas
How to use compound join conditions
How to use a self-join
Inner joins that 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 Oracle extensions
How to use the ROLLUP operator
How to use the CUBE operator
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 the result of 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
Two more skills for working with subqueries
How to code a subquery factoring clause
How to code a hierarchical query
Chapter 7 How to insert, update, and delete data
How to create test tables
How to create the tables for this book
How to create a copy of a table
How to commit and rollback changes
How to commit changes
How to rollback changes
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
Chapter 8 How to work with data types and functions
The built-in data types
Data type overview
The character data types
The numeric data types
The temporal data types
The large object data types
How to convert data from one type to another
How to convert characters, numbers, and dates
Common number format elements
Common date/time format elements
How to convert characters to and from their numeric codes
How to work with character data
How to use the common character functions
How to parse a string
How to sort a string in numerical sequence
How to sort mixed-case columns in alphabetical sequence
How to work with numeric data
How to use the common numeric functions
How to search for floating-point numbers
How to work with date/time data
How to use the common date/time functions
How to parse dates and times
How to perform a date search
How to perform a time search
Other functions you should know about
How to use the CASE function
How to use the COALESCE, NVL, and NVL2 functions
How to use the GROUPING function
How to use the ranking functions
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 tables, indexes, and sequences
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
The script used to create the AP tables
An introduction to scripts
How the DDL statements work
How to automatically generate ID values
How to use a sequence as a default
How to use the GENERATED clause
How to use SQL Developer
How to work with the columns of a table
How to work with the data of a table
How to work with the constraints of a table
How to work with indexes
How to work with sequences
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 the WITH CHECK OPTION clause
How to insert or delete rows through a view
How to alter or drop a view
How to use SQL Developer
How to get information about a view
How to drop a view
How to alter or create a view
Chapter 12 How to manage database security
An introduction to database security
How to create an admin user
How to use SQL Developer to view database objects for a schema
How to create end users
How to use SQL*Plus to test end users
System privileges and object privileges
How to manage database security
How to create, alter, and drop users
How to create and drop roles
How to grant privileges
How to revoke privileges
How to work with private synonyms
How to work with public synonyms
A script that creates roles and users
How to view the privileges for users and roles
How to use SQL Developer
How to work with users
How to grant and revoke roles
How to grant and revoke system privileges
Section 4 The essential PL/SQL skills
Chapter 13 How to write PL/SQL code
An introduction to PL/SQL
An anonymous PL/SQL block in a script
A summary of 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 use collections
How to handle exceptions
A list of predefined exceptions
Other scripting and PL/SQL skills
How to drop database objects without displaying errors
How to use bind variables
How to use substitution variables
How to use dynamic SQL
How to run a script from a command line
Chapter 14 How to manage transactions and locking
How to work with transactions
How to commit and rollback transactions
How to work with save points
How to work with concurrency and locking
How concurrency and locking are related
The four concurrency problems that locks can prevent
How to set the transaction isolation level
How to prevent deadlocks
Chapter 15 How to create stored procedures
How to code stored procedures
How to create and call a stored procedure
How to code input and output parameters
How to code optional parameters
How to raise errors
A stored procedure that inserts a row
A stored procedure that drops a table
How to drop a stored procedure
How to code user-defined functions
How to create and call a function
A function that calculates balance due
How to drop a function
How to work with packages
How to create a package
How to drop a package
Advantages of packages
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 16 How to create triggers
How to work with triggers
How to create a BEFORE trigger for a table
How to use a trigger to enforce data consistency
How to use a trigger to work with a sequence
How to create an AFTER trigger for a table
How to use an INSTEAD OF trigger for a view
How to use a trigger to work with DDL statements
How to use a trigger to work with database events
How to enable, disable, rename, or drop a trigger
Other skills for working with triggers
How to code 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, rename, or drop a trigger
How to enable or disable a trigger
How to edit a trigger
How to debug a trigger
Section 5 Advanced data types
Chapter 17 How to work with timestamps and intervals
An introduction to time zones
Database time zone vs. session time zone
How to change the default date format
How to change the default time zone
Session settings vs. database settings
How to use functions to work with time zones
How to work with timestamps
An introduction to the TIMESTAMP types
How to work with the TIMESTAMP type
How to work with the TIMESTAMP WITH LOCAL TIME ZONE type
How to work with the TIMESTAMP WITH TIME ZONE type
Common format elements for timestamps
How to use functions to work with timestamps
How to work with intervals
An introduction to the INTERVAL types
How to work with the INTERVAL YEAR TO MONTH type
How to work with the INTERVAL DAY TO SECOND type
How to use functions to work with intervals
Chapter 18 How to work with large objects
An introduction to large objects
The LOB types
APIs for working with LOBs
How to use SQL to work with large objects
How to work with CLOBs
How to work with NCLOBs
How to work with BLOBs
How to work with BFILEs
How to specify LOB storage options
How to migrate to the new LOB types
How to use Java to work with large objects
The main method for the sample application
How to write an image to a table
How to read an image from a table
A utility class for working with databases
How to use PL/SQL to work with large objects
The methods of the DBMS_LOB package
An example that uses the DBMS_LOB package
Appendixes
Appendix A How to install the software and source code for this book
How to install the software from oracle.com
How to install the Oracle Database Express Edition
How to install Oracle SQL Developer
How to install the SQL documentation
How to install the PL/SQL documentation
How to install the software from murach.com
How to install the source files for this book
How to create the tables and users for this book
How to restore the tables and users
Appendix B How to install the Standard or Enterprise Edition of Oracle Database
How to install the Personal Edition of Oracle Database
How to create the tables and users for this book for Oracle 12c and later
How to restore the tables and users
How to connect to Oracle 12c and later
How to start and stop database services
If you aren’t already familiar with the supporting courseware that we provide for a book, please go to About our Courseware. As you will see, our courseware consists of the end-of-chapter activities in the book, the files in the student download at our retail site, and the instructor’s materials. These components provide everything that other publishers provide in a way that delivers better results.
If you are familiar with our courseware, here’s a quick summary of the courseware for this book. For a detailed description in PDF format, please read the Instructor’s Summary.
End-of-chapter activities in the book
- Term lists
- Practice exercises
Student download at our retail site
- Script files that create the databases used by the examples and exercises in the book
- Scripts for the examples in the book
- Scripts for the solutions to the exercises in the book so the students can resolve their problems on their own
Appendix A in the book gives your students complete instructions for downloading and installing these items on their PCs.
Instructor’s materials
- Instructional objectives by chapter
- PowerPoint slides for classroom presentations
- Test banks in multiple formats
- A second full set of chapter-by-chapter exercises that aren’t in the book, plus their solutions
- The files that students can download at our retail site: the scripts for creating the databases, for the examples in the book, and for the solutions to the exercises in the book
Extra exercises and solutions that aren’t available in the student download. As a result, you can use these exercises to test your students, knowing that they won’t have access to the solutions
To view the "Frequently Asked Questions" for this book in a PDF, just click on this link: View the questions
Then, if you have any questions that aren't answered here, please email us. Thanks!
There are no book corrections that we know of at this time. But if you find any, please e-mail us so we can post any corrections that affect the technical accuracy of the book right 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.