Database Application Developer and DBA
SQL 101 BY MELANIE CAFFRE Y
Getting Answers
with SELECT
Part 3 in a series on the basics of the relational
database and SQL
ORACLE DATABASE
Part 2 in this series, “Modeling and Accessing Relational Data” (Oracle
Magazine, November/December 2011),
introduced readers to the ways data entities
(tables) can relate to one another in a relational database. When your logical models
and physical implementations use meaningful entities and well-chosen datatypes,
you have multiple options for accessing
the data. This article focuses on the
purpose and anatomy of the SQL SELECT
statement—also called a query—and
explains how to use Oracle SQL Developer
and Oracle Application Express to construct
queries and view their results. (Although I’ll
briefly review the concepts covered in Part
2, I encourage you to read that installment
before starting this one.)
I T ALL BEGINS WI TH A QUERY
The goal of writing a SQL query is usually to
get the answer from the database to a question or questions. For example, you might
want to ask
•;How many employees work in the
accounting department?
•;Of those employees, which ones are currently working on multiple projects?
•;Which employees working on multiple
projects in the accounting department
have received a salary increase between
their date of hire and today, and which
employees haven’t?
LINDY GROENING
You obtain the answers to these questions by using a SQL SELEC T statement. A
SELEC T statement has at least two parts:
the SELEC T list and the FROM clause. The
SELEC T list specifies one or more columns
(or expressions, to be explained in subsequent installments of this series)—selected
from one or more tables—that you want to
display. The FROM clause lists the table(s)
from which your desired column data
should be obtained.
KNOW YOUR DATA
Before you write a SELECT statement,
you must determine which table or tables
contain the information of interest. For
example, if you want to know all employees’
hire dates, you must first determine which
table contains employee information.
Perusal of your schema diagram reveals
that employee data is in a table called
Code Listing 1: SELECT statement result for three columns
SELECT first_name, last_name, hire_date
FROM employee
FIRST_NAME LAST_NAME HIRE_DATE
—————————————————————————————————————————— ——————————————————————————————————————————— —————————————
Frances Newton 14-SEP-05
Emily Eckhardt 07-JUL-04
Donald Newton 24-SEP-06
Matthew Michaels 16-MAY-07
Code Listing 2: SELECT statement result for all columns
SELECT
FROM employee
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID
———————————————— —————————————— ————————————— —————————————— ———————— —————————— ———————————————————
37 Frances Newton 2005-09-1475000
28 Emily Eckhardt 2004-07-07 100000
4 rows selected
Code Listing 3: DESCRIBE result for the EMPLOYEE table
describe employee
Name Null Type
————————————————————————————————————————— ——————— —————————————————
EMPLOYEE_ID NUMBER
FIRST_NAME VARCHAR2( 30)
LAST_NAME VARCHAR2( 30)
HIRE_DATE DATE
SALARY NUMBER( 9, 2)
MANAGER NUMBER
DEPARTMENT_ID NUMBER
