Skip to main content
main-content
Top

About this book

Start developing with Oracle SQL. This book is a one-stop introduction to everything you need to know about getting started developing an Oracle Database. You'll learn about foundational concepts, setting up a simple schema, adding data, reading data from the database, and making changes. No experience with databases is required to get started. Examples in the book are built around Oracle Live SQL, a freely available, online sandbox for practicing and experimenting with SQL statements, and Oracle Express Edition, a free version of Oracle Database that is available for download.
A marquee feature of Beginning Oracle SQL for Oracle Database 18c is the small chapter size. Content is divided into easily digestible chunks that can be read and practiced in very short intervals of time, making this the ideal book for a busy professional to learn from. Even just a 15-20 minute block of free time can be put to good use.
Author Ben Brumm begins by helping you understand what a database is, and getting you set up with a sandbox in which to practice the SQL that you are learning. From there, easily digestible chapters cover, point-by-point, the different aspects of writing queries to get data out of a database. You’ll also learn about creating tables and getting data into the database. Crucial topics such as working with nulls and writing analytic queries are given the attention they deserve, helping you to avoid pitfalls when writing queries for production use.

What You'll LearnCreate, update, and delete tables in an Oracle database
Add, update, delete data from those database tables
Query and view data stored in your database
Manipulate and transform data using in-built database functions and features
Correctly choose when to use Oracle-specific syntax and features

Who This Book Is For
Those new to Oracle who are planning to develop software using Oracle as the back-end data store. The book is also for those who are getting started in software development and realize they need to learn some kind of database language. Those who are learning software development on the side of their normal job, or learning it as a college student, who are ready to learn what a database is and how to use it also will find this book useful.

Table of Contents

Setting Up

Frontmatter

Chapter 1. What is a Database?

Abstract
One of the main features of software is that it lets you interact with data in a certain way. This could be a grocery list app that lets you add items to a shopping list on your phone, a website that lets you write and publish articles, or a customer relationship management program where you store information about customers.
Ben Brumm

Chapter 2. Setting Up

Abstract
This chapter introduces you to the different tools we’ll use in this book, and shows you how to download and set them up. You’ll create a table and set up some sample data as well.
Ben Brumm

Viewing Data

Frontmatter

Chapter 3. Retrieving Data

Abstract
In this chapter, you’ll learn how to see the data that’s in your database. You’ll look at your first SQL statement, the SELECT statement, and see some examples of this on the table you just created.
Ben Brumm

Chapter 4. Selecting Specific Columns

Abstract
In this chapter, you’ll learn how to show specific columns from your table. It is not always necessary or desirable to return all columns as we did in Chapter 3. As you’ll recall, we executed the following query in that chapter: SELECT * FROM employee;
Ben Brumm

Chapter 5. Restricting the Results

Abstract
We have just learned how to display certain columns from a table. In this chapter, we’ll look at how to display certain rows from a table.
Ben Brumm

Chapter 6. Comparing Data

Abstract
You’ve just learned how to restrict which rows you want to display, using a range of methods such as the equal to or greater than. Each of these methods checks a column against another value. This other value is checked using an “exact match.” The exact value that you specified is used to perform the check.
Ben Brumm

Chapter 7. Applying Multiple Filters

Abstract
In the previous chapters, you’ve learned how to restrict the records you want to see. You looked at using symbols such as “equals to” and “greater than.” You also learned how to do partial matches using the LIKE keyword and wildcard characters.
Ben Brumm

Chapter 8. Working with NullsNulls

Abstract
In this chapter, you’ll learn all about a concept in SQL and databases called “null.” In the sample table you've been using, every record has a value for every column. However, when working with real databases, this is not always the case. The concept of “null” is used when data is not known.
Ben Brumm

Chapter 9. Removing Duplicate ResultsDuplicate results

Abstract
When you store data in a table, as you have learned so far, you can just enter the values and save it. The data is then saved into the table. As long as the values you enter match the type of column, then it should work. This means you can’t enter a text value into a number column, for example.
Ben Brumm

Chapter 10. Applying FiltersFilters on Lists and Ranges of Values

Abstract
In previous chapters, you learned about the WHERE clause and how you could use it to restrict the results you see. You can do this using a variety of symbols, called operators, such as “equal to” and “less than.” You can also specify multiple criteria using the AND/OR keywords.
Ben Brumm

Chapter 11. Ordering Your DataOrdering data

Abstract
In this chapter, you’ll learn how to sort your results in a specific order. In SQL, the data you select from a table is not guaranteed to be shown in a particular order. If you want to show it in a particular order, there is a command you can use.
Ben Brumm

Chapter 12. Applying Table and Column Aliases

Abstract
In all of the chapters so far, you’ve learned how to view data from our database tables. You’ve learned how to choose the columns and rows you want to display using many different methods.
Ben Brumm

Adding, Updating, Deleting Data

Frontmatter

Chapter 13. Understanding the Data Types

Abstract
In this chapter, you’ll learn what data types are, why you need them, and how the Oracle database handles different data types.
Ben Brumm

Chapter 14. Creating a Table

Abstract
Earlier in this book, you created a new table called employee and added data to it. You did this using the menu options within SQL Developer. There is another way to create tables, which we will learn about in this chapter.
Ben Brumm

Chapter 15. Adding Data to a Table

Abstract
The database we have been working on so far has three tables: employee, office, and sales_meeting. Only one of these tables has any data in it: the employee table. Adding data to a table is a separate step to creating the table.
Ben Brumm

Chapter 16. Updating and Removing Data

Abstract
In the last chapter, you learned how to add data to a table. SQL allows you to make changes to data that’s already in a table, and we’ll learn how to do that in this chapter.
Ben Brumm

Chapter 17. Updating or Deleting a Table

Abstract
You’ve learned how to work with tables and data in the last few chapters. Specifically, you’ve learned how to:
Ben Brumm

Joining Tables

Frontmatter

Chapter 18. Inner JoinInner Join

Abstract
Earlier in this book, we learned what a database was, and that it had several advantages over other ways of storing data. In this chapter, we’ll learn how to make the most of one of those advantages: the ability to view data from multiple tables at once.
Ben Brumm

Chapter 19. Outer JoinOuter join

Abstract
In the last chapter, we learned how to join tables together using the JOIN keyword. This was a type of join called an inner join, which displays records that exist in both tables being joined. The other type of join is an outer join.
Ben Brumm

Chapter 20. Other Join Types

Abstract
There are several other types of join syntax offered in Oracle SQL. These join types aren’t used as often, but it’s worth knowing about them and why you should or should not use them.
Ben Brumm

Chapter 21. Joining Many Tables

Abstract
In this chapter, we’ll learn how to use SQL to join many tables together in our query. So far, we’ve learned quite a bit about joins:
Ben Brumm

Functions

Frontmatter

Chapter 22. Using functions in SQL

Abstract
There’s more to SQL than reading the data that’s stored in the table. You’ll often need to manipulate it or perform calculations on it. That’s where functions come in.
Ben Brumm

Chapter 23. Writing Conditional Logic

Abstract
Many programming languages include the ability to perform conditional logic, or “if this is true, then do something, otherwise do something else.” SQL is different to other programming languages and doesn’t include a lot of features such as variables and loops. However, SQL does have the ability to use conditional logic.
Ben Brumm

Chapter 24. Understanding Aggregate FunctionsAggregate functions

Abstract
In the last chapter, you learned what a function was. You looked at a few examples of functions, such as LOWER, that performed a specific operation on a particular column’s value for every record. The LOWER function was used to convert every record’s last_name value into a lowercase version of the last_name. SELECT id, last_name, LOWER(last_name) FROM employee;
Ben Brumm

Chapter 25. Grouping Your Results

Abstract
In the last chapter, you learned about aggregate functions. These functions calculated a value, such as a sum or a count, over the values in a column and showed you a single row of results. In this chapter, you’ll learn how to write a query that groups your results. What does that mean?
Ben Brumm

Chapter 26. What Are IndexesIndexes?

Abstract
Indexes are a feature that’s worth knowing about in SQL. They are useful for making your queries perform better, and as you gain more experience in SQL, you’ll need to know how to create them and why they are needed.
Ben Brumm

Command Line

Frontmatter

Chapter 27. Using the Command LineCommand line

Abstract
So far, we’ve used an application called SQL Developer to run our SQL queries. At the start of the book, you downloaded this software from the Oracle website and used it to run queries.
Ben Brumm

Appendixes

Frontmatter

Appendix. How to Find and Navigate the Oracle SQLOracle SQL Reference

Abstract
There is a lot to learn and remember about Oracle SQL. We’ve learned a lot of different keywords in this book, such as:
Ben Brumm
Additional information