Customer Service 1-800-221-5528

Murach’s Oracle SQL and PL/SQL for Developers (2nd Edition)

by Joel Murach
18 chapters, 648 pages, 272 illustrations
Published October 2014
ISBN 978-1-890774-80-6
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

 

On this page, we’ll be posting answers to the questions that come up most often about this book. So if you have any questions that you haven’t found answered here at our site, please e-mail 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!

Murach college books and courseware since 1974