Database Application Developer and DBA
SQL 101 BY MELANIE CAFFREY
Get Your Information in Order
Part 1 in a series on the basics of the relational
database and SQL
ORACLE DATABASE
Ask most seasoned professionals working with Oracle Database instances today
what their chief complaint regarding performance issues is, and 9 times out of 10, they
will answer with responses along the lines of
“lack of SQL expertise,” “poorly written SQL
statements,” or “poorly trained database
programmers.” As relational databases have
become a necessary part of everyday business
life, knowledge of structured query language
(SQL) has become paramount. Paradoxically,
however, learning good SQL programming
techniques has taken a backseat to creating,
for example, user-friendly, attractive interfaces written in database-agnostic programming languages such as Java. Programmers
may spend a great deal of time learning their
chosen or assigned interface language and
very little to no time learning SQL.
This series of SQL 101 articles is for
those new to or not yet completely familiar
with relational database concepts and SQL
coding constructs. It is for anyone learning
SQL, tasked with teaching SQL to others
within a workgroup, or managing programmers who write database access code.
This first article in the series begins with
information about the basic building blocks
that all programmers (or DBAs/designers/
managers) should know when writing their
first set of SQL statements.
LINDY GROENING
HOW IS DATA ORGANIZED IN A
RELATIONAL DATABASE?
Being able to visualize how data is organized in a database is key to retrieving that
data quickly and easily. Whenever you withdraw money from an ATM, you are reading
and manipulating data. Whenever you
purchase anything online, you are changing
data. Whether you are banking, shopping,
or performing one of many business activities, you are likely interacting with a
relational database.
A relational database stores data in a two-dimensional matrix known as a table, and
tables generally consist of multiple columns
and rows. (I say generally here because it is
possible to have a table with just one column
and no rows, although it is not common. I
will cover that exception in later articles in
this series.) Relational databases employ
relational database management system
(RDBMS) software to help manage the
task of giving a user the ability to read and
manipulate data without knowing the exact
file and/or drive storage device location
where a particular piece of information can
be found. (Oracle Database is, among other
things, an RDBMS.) Users need only know
which tables contain the information they
seek. The RDBMS relies on SQL constructs
and keywords, provided by users, to access
the tables and the data contained within the
tables’ columns and rows.
HOW IS DATA REPRESENTED IN A
RELATIONAL DATABASE?
Each table in a relational database usually
contains information about a single type of
data and has a unique name, distinct from
all other tables in that schema. A schema
is typically a grouping of objects (such as
tables) that serve a similar business function.
For example, three tables that contain data
about employees, departments, and payroll
details, respectively, may exist together
inside a schema named HR. There can be
only one table named EMPLOYEE inside the
HR schema (for the purposes of this intro-
ductory explanation, discussions regarding
features that support the coexistence of
tables with the same name, such as Oracle
Database 11g’s editions and Edition-Based
Redefinition, are beyond the scope of this
article). Now suppose the information in an
EMPLOYEE table includes the structure and
content shown in Figure 1.
First_Name
Frances
Donald
Last_Name
Newton
Newton
Hire_Date
14-SEP-2005
24-SEP-2006
Manager
28
ROW
COLUMN
Figure 1: The EMPLO YEE table
ORACLE MAGAZINE SEP TEMBER/OC TOBER 2011
