SQL for everybody

from noob to aprentice

Lesson 1 short introduction

Introduction to SQL and its development
environments
The DESCRIBE table command
The Human Resource(HR) Schema

SQL
Structured query language (SQL) is:
The ANSI standard language for operating relational databases
Efficient, easy to learn and use
Functionally complete (using SQL, we can define, retrieve, and manipulate data
in the tables)

In a relational database, we do not specify the access route to the tables, and
we do not need to know how the data is arranged physically. To access the
database, we execute a structured query language (SQL) statement, which is the
American National Standards Institute (ANSI) standard language for operating
relational databases.
SQL is a set of statements with which all programs and users access data in an
Oracle Database. Application programs and Oracle tools often allow users access
to the database without using SQL directly, but these applications, in turn, must
use SQL when executing the users request.
SQL provides statements for a variety of tasks, including:
Querying data
Inserting, updating, and deleting rows in a table
Creating, replacing, altering, and dropping objects
Controlling access to the database and its objects
Guaranteeing database consistency and integrity
SQL unifies all of the preceding tasks in one consistent language and enables
you to work with data at a logical level.

DML
(Data Manipulation
Language)

DDL
(Data Definition
Language)

DCL
(Data Control
Language)

TCL
(Transaction Control
Language)

SELECT
UPDATE
INSERT
DELETE
MERGE

CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

GRANT
REVOKE

COMMIT
ROLLBACK
SAVEPOINT

Retrieves data from

the database, enters
new rows, changes
existing rows, and
removes unwanted
rows from tables in the
database, respectively.
Collectively
known as data
manipulation language
(DML)

Sets up, changes, and

removes data
structures from tables.
Collectively known as
data definition
language (DDL)

Provides or removes
access rights to both
the Oracle Database
and the structures
within it

Manages the changes

made by DML
statements. Changes to
the data can be
grouped together into
logical transactions

Development Environments for SQL

There are two development environments:
The primary tool is Oracle SQL Developer.
SQL*Plus command-line interface can also be used.

 Introduction to SQL and its development

environments
The DESCRIBE table command
The Human Resource(HR) Schema

We can display the structure of a table by using the DESCRIBE command. The
command displays the column names and the data types, and it shows you whether a
column must contain data (that is, whether the column has a NOT NULL constraint).
DESC[RIBE] tablename
In the syntax, tablename is the name of any existing table, view, or synonym that
is accessible to the user.

Or, select the table in the Connections tree and use the Columns tab to view the
table structure.

The example in the slide displays information about the structure of the
EMPLOYEES table using the DESCRIBE command or using the SQL Developer
functionality.
In the resulting display:
- NULL indicates that the values for this column may be unknown.
- NOT NULL indicates that a column must contain data.
- Type displays the data type for a column.
For the moment, we will discuss only about 3 data type for columns.

Numeric columns are often specified as NUMBER(p,s), where the first parameter
is precision and the second is scale. In our example, the SALARY column of the
EMPLOYEES table has a data type of: NUMBER(8,2). This means that the values
stored in this column can have at most 8 digits. Of these 8 digits, 2 must be to the right
of the decimal point. A SALARY value of 999999.99 is acceptable, but a SALARY
value of 9999999.9 is not, even though both these numbers contain 8 digits.

VARCHAR2(length) data type columns store variable length alphanumeric

character data, where length determines the maximum number of characters
a column can contain.
The FIRST_NAME column of the EMPLOYEES table has data type VARCHAR2(20),
which means it can store employees names of up to 20 characters.
Note that if this column contains no data or its content is less than 20 characters, it
will not necessarily use the same space as it would use to store a name that is 20
characters long.
The CHAR(size) column data type specifies fixed-length columns where row space
is pre-allocated to contain a fixed number of characters regardless of its contents.
CHAR is much less commonly used than VARCHAR2. Unless the length of the data
is predictable and constant, the CHAR data type utilizes storage inefficiently,
padding any unused components with spaces.
# VARCHAR is reserved by Oracle to support distinction between NULL and empty
string in future, as ANSI standard prescribes. VARCHAR2 does not distinguish between
a NULL and empty string, and never will.

DATE and TIMESTAMP column data types store date and time information.
DATE stores a moment in time with precision including day, month, year,
hours, minutes, and seconds.
TIMESTAMP(f) stores the same information as DATE but is also capable of
storing fractional seconds.

 Introduction to SQL and its development

environments
The DESCRIBE table command
The Human Resource(HR) Schema

Human Resources (HR) Schema Description

The Human Resources (HR) schema is a part of the Oracle Sample Schemas that can
be installed in an Oracle Database.

Table Descriptions
REGIONS contains rows that represent a region such as America, Asia, and so on.
COUNTRIES contains rows for countries, each of which is associated with a
region.
LOCATIONS contains the specific address of a specific office, warehouse, or
production site of a company in a particular country.
DEPARTMENTS shows details about the departments in which the employees
work. Each department may have a relationship representing the department
manager in the EMPLOYEES table.
EMPLOYEES contains details about each employee working for a department.
Some employees may not be assigned to any department.
JOBS contains the job types that can be held by each employee.
JOB_HISTORY contains the job history of the employees. If an employee
changes departments within a job or changes jobs within a department, a new
row is inserted into this table with the earlier job information of the employee.
EMPLOYEES gives details of all the employees
DEPARTMENTS gives details of all the departments
JOB_GRADES gives details of salaries for various grades

Practice
1. Start Oracle SQL Developer using the SQL Developer Desktop Icon
2. Create a new Oracle SQL Developer Database Connection
a. To create a new database connection, in the Connections Navigator, right-click
Connections and select New Connection from the context menu. The New/Select Database
Connection dialog box appears.
b. Create a database connection using the following information:
Connection Name: myconnection
Username: hr
Password: hr
Hostname: localhost
Port: 1521
SID: orcl
Ensure that you select the Save Password check box.
3. Testing the Oracle SQL Developer Database Connection and Connecting to the Database
a. Test the new connection.
b. If the status is Success, connect to the database using this new connection.

4. Browse the Tables in the Connections Navigator

a. In the Connections Navigator, view the objects that are available to you
in the Tables node.
Verify that the following tables are present:
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOB_GRADES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS
b. Browse the structure of the EMPLOYEES table.
c. View the data of the DEPARTMENTS table.

Lesson 2 Retrieving data using

the SQL SELECT statement
Capabilities of SQL SELECT statements
Arithmetic expressions and NULL values in the
SELECT statement
Column aliases
Use of concatenation operator, literal character
strings, alternative quote operator, and the
DISTINCT keyword

Relational database tables are built on a strong mathematical foundation called

relational theory. In this theory, relations, or tables, are operated on by a formal language
called relational algebra. SQL is a commercial interpretation of the relational algebraic
constructs.
Three concepts from relational theory encompass the capability of the SELECT
statement: projection, selection, and joining.
Projection: refers to the restriction of attributes (columns) selected from a relation or
table.
Selection: refers to the restriction of the tuples or rows selected from a relation (table).
Joining: as a relational concept, refers to the interaction of tables with each other in a
query. Third normal form, as discussed, presented the notion of separating different types
of data into autonomous tables to avoid duplication and maintenance anomalies and to
associate related data using primary and foreign key relationships. These relationships
provide the mechanism to join tables with each other.
A keyword refers to an individual SQL element
for example: SELECT and FROM are keywords.
A clause is a part of a SQL statement
for example: SELECT employee_id, last_name, and so on.
A statement is a combination of two or more clauses
for example: SELECT * FROM employees.

PROJECTION

SELECTION

Table 1

Table 1
JOIN

Table 1

Table 2

Basic SELECT Statement

SELECT {*|[DISTINCT] column|expression [alias],...}
FROM table;

In its simplest form, a SELECT statement must include the following:

A SELECT clause, which specifies the columns to be displayed
A FROM clause, which identifies the table containing the columns that are listed
in the SELECT clause
In the syntax:
SELECT
*
DISTINCT
column|expression
alias
FROM table

Is a list of one or more columns

Selects all columns
Suppresses duplicates
Selects the named column or the expression
Gives different headings to the selected columns
Specifies the table containing the columns

Selecting all columns

SELECT *
FROM departments;

We can display all columns of data in a table by following the SELECT

keyword with an asterisk (*).

In the example in the slide, the DEPARTMENTS table contains four columns:
DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID, and
many rows, one for each department.
We can also display all columns in the table by listing them after the SELECT
keyword. For example, the following SQL statement (like the example in the slide)
displays all columns and all rows of the DEPARTMENTS table:

SELECT department_id, department_name, manager_id,

location_id
FROM departments;

Selecting specific columns

SELECT department_id, location_id
FROM departments;

We can use the SELECT statement to display specific columns of the table by
specifying the column names, separated by commas. The example in the slide
displays all the department numbers and location numbers from the DEPARTMENTS
table.

In the SELECT clause, specify the columns that you want in the order in which
we want them to appear in the output. For example, to display location before
department number (from left to right), we can use the following statement:
SELECT location_id, department_id
FROM departments ;

Writing SQL Statements

By using the following simple rules and guidelines, you can construct valid
statements that are both easy to read and edit:
SQL statements are not case sensitive (unless indicated).
SQL statements can be entered on one or many lines.
Keywords cannot be split across lines or abbreviated.
Clauses are usually placed on separate lines for readability and ease of editing.
Indents should be used to make code more readable.
Keywords typically are entered in uppercase; all other words, such as table
names and columns names are entered in lowercase.

Executing SQL Statements

In SQL Developer, click the Run Script icon or press [F5] to run the command or
commands in the SQL Worksheet. You can also click the Execute Statement icon or
press [F9] to run a SQL statement in the SQL Worksheet. The Execute Statement
icon executes the statement at the mouse pointer in the Enter SQL Statement box
while the Run Script icon executes all the statements in the Enter SQL Statement
box. The Execute Statement icon displays the output of the query on the Results
tabbed page, whereas the Run Script icon emulates the SQL*Plus display and
shows the output on the Script Output tabbed page.
In SQL*Plus, terminate the SQL statement with a semicolon, and then press
[Enter] to run the command.

 Capabilities of SQL SELECT statements

Arithmetic operators and NULL values in the
SELECT statement
Column aliases
Use of concatenation operator, literal character
strings, alternative quote operator, and the
DISTINCT keyword

The general form of the SELECT statement introduced the notion that
columns and expressions are selectable. An expression is usually made up of
an operation being performed on one or more column values. The operators
that can act upon column values to form an expression depend on the data
type of the column.
They are the four cardinal arithmetic operators (addition, subtraction,
multiplication, and division) for numeric columns; the concatenation operator
for character or string columns; and the addition and subtraction operators
for date and timestamp columns.

Arithmetic operators
We may need to modify the way in which data is displayed, or we may
want to perform calculations, or look at what-if scenarios. All these are
possible using arithmetic expressions.
An arithmetic expression can contain column names, constant numeric
values, and the arithmetic operators.
Operator

Description

Add

Subtract

Multiply

Divide

We can use arithmetic operators in any clause of a SQL statement (except the
FROM clause).

Using arithmetic operators

SELECT last_name, salary, salary + 300
FROM employees;

The example in the slide uses the addition operator to calculate a salary increase of
$300 for all employees. The slide also displays a SALARY+300 column in the output.
Note that the resultant calculated column, SALARY+300, is not a new column in the
EMPLOYEES table; it is for display only. By default, the name of a new column comes
from the calculation that generated itin this case, salary+300.
Note: The Oracle server ignores blank spaces before and after the arithmetic operator.

Operator Precedence
If an arithmetic expression contains more than one operator, multiplication
and division are evaluated first. If operators in an expression are of the same
priority, evaluation is done from left to right.
We can use parentheses to force the expression that is enclosed by the
parentheses to be evaluated first.
Rules of Precedence
Multiplication and division occur before addition and subtraction.
Operators of the same priority are evaluated from left to right.
Parentheses are used to override the default precedence or to clarify the
statement.

SELECT last_name, salary, 12*salary+100

FROM employees;
The first example in the slide displays the last name,
salary, and annual compensation of employees.
It calculates the annual compensation by multiplying the
monthly salary with 12, plus a one-time bonus of $100.
Note that multiplication is performed before addition.

SELECT last_name, salary, 12*(salary+100)

FROM employees;

You can override the rules of precedence by using

parentheses to specify the desired order in which the
operators are to be executed.
The second example in the slide displays the last name,
salary, and annual compensation of employees.
It calculates the annual compensation as follows: adding a monthly bonus of $100
to the monthly salary, and then multiplying that subtotal with 12. Because of the
parentheses, addition takes priority over multiplication.

Precedence Level

Operator Symbol

Operation

Highest

()

Brackets or parentheses

Medium

Division

Medium

Multiplication

Lowest

Subtraction

Lowest

Addition

NULL value
Null is a value that is unavailable, unassigned, unknown or inapplicable.
Null is not the same as zero or a blank space.
SELECT last_name, job_id, salary, commission_pct
FROM employees;
If a row lacks a data value for a particular column,
that value is said to be null or to contain a null.
Is not the same as zero or a blank space - zero is a
number and blank space is a character.

Columns of any data type can contain nulls. However, some constraints (NOT NULL
and PRIMARY KEY) prevent nulls from being used in the column.
In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales
manager or sales representative can earn a commission. Other employees are not
entitled to earn commissions. A null represents that fact.

NULL value in arithmetic operators

SELECT last_name, 12*salary*commission_pct
FROM employees;

Arithmetic expressions containing a NULL

value evaluate to NULL.

If any column value in an arithmetic expression is null, the result is null. For
example, if you attempt to perform division by zero, you get an error. However, if
you divide a number by null, the result is a null or unknown.
In the example in the slide, employee Whalen does not get any commission.
Because the COMMISSION_PCT column in the arithmetic expression is null, the
result is null.

 Capabilities of SQL SELECT statements

Arithmetic expressions and NULL values in the
SELECT statement
Column aliases
Use of concatenation operator, literal
character strings, alternative quote operator,
and the DISTINCT keyword
DESCRIBE command

Defining a Column Alias

A column alias:
Renames a column heading
Is useful with calculations
Immediately follows the column name (There can also be the optional AS
keyword between the column name and the alias.)
Requires double quotation marks if it contains spaces or special characters, or
if it is case-sensitive
When displaying the result of a query, SQL Developer normally uses the name
of the selected column as the column heading. This heading may not be
descriptive and, therefore, may be difficult to understand. You can change a
column heading by using a column alias.
Specify the alias after the column in the SELECT list using blank space as a
separator. By default, alias headings appear in uppercase. If the alias contains
spaces or special characters (such as # or $), or if it is case-sensitive, enclose the
alias in double quotation marks ( ).

Using Column Aliases

SELECT last_name AS name, commission_pct comm
FROM employees;
The first example displays the names and the commission percentages of all the
employees. Note that the optional AS keyword has been used before the column alias
name. The result of the query is the same whether the AS keyword is used or not.
Also, note that the SQL statement has the column aliases, name and comm, in
lowercase, whereas the result of the query displays the column headings in
uppercase. As mentioned in the preceding slide, column headings appear in
uppercase by default.

SELECT last_name "Name" , salary*12 "Annual Salary"

FROM employees;

The second example displays the last names and annual salaries of
all the employees. Because Annual Salary contains a space, it has
been enclosed in double quotation marks.
Note that the column heading in the output is exactly the same as
the column alias.
Note: An alias cannot be referenced in the column list that contains
the alias definition.

 Capabilities of SQL SELECT statements

Arithmetic expressions and NULL values in the
SELECT statement
Column aliases
Use of concatenation operator, literal character
strings, alternative quote operator, and the
DISTINCT keyword
DESCRIBE command

Concatenation operator
SELECT last_name||job_id AS "Employees"
FROM employees;
A concatenation operator:
Links columns or character strings to other columns
Is represented by two vertical bars (||)
Creates a resultant column that is a character expression

We can link columns to other columns, arithmetic expressions, or constant values to

create a character expression by using the concatenation operator (||). Columns on
either side of the operator are combined to make a single output column.
In the example, LAST_NAME and JOB_ID are concatenated, and given the alias
Employees.
Note that the last name of the employee and the job code are combined to make a
single output column.
If we concatenate a null value with a character string, the result is a character string.
LAST_NAME || NULL results in LAST_NAME.

Literal character strings

A literal is a character, a number, or a date that is included in the
SELECT statement.
Date and character literal values must be enclosed within single
quotation marks.
Each character string is output once for each row returned.
A literal is a character, a number, or a date that is included in the SELECT
list. It is not a column name or a column alias. It is printed for each row
returned. Literal strings of free format text can be included in the query result
and are treated the same as a column in the SELECT list.
The date and character literals must be enclosed within single quotation
marks (''); number literals need not be enclosed in a similar manner.

Using literal character strings

SELECT last_name ||' is a '||job_id
FROM employees;

AS "Employee Details"

The example in the slide displays the last names and job codes of
all employees. The column has the heading Employee Details.
Note the spaces between the single quotation marks in the SELECT
statement. The spaces improve the readability of the output.

SELECT last_name ||': 1 Month salary = '||salary Monthly

FROM employees;
In this example, the last name and salary for each
employee are concatenated with a literal, to give the
returned rows more meaning.

What about character literals that contain single quotation marks? Plurals pose a particular
problem for character literal processing. Consider the following statement:

SELECT 'Plural's have one quote too many' FROM dual;

The Oracle server processes this segment to mean that the character literal 'Plural' is
aliased as column s. At this point, the interpreter expects a FROM clause, but instead finds
the word have. It then generates an error.
So, how are words that contain single quotation marks dealt with? There are
essentially two mechanisms available. The most popular of these is to add an
additional single quotation mark next to each naturally occurring single quotation
mark in the character string.

SELECT 'Plural''s have one quote too many' FROM dual;

Alternative Quote (q) operator

SELECT department_name || q'[ Department's Manager Id: ]'
|| manager_id AS "Department and Manager"
FROM departments;
Specify your own quotation mark delimiter.
Select any delimiter.
Increase readability and usability.

Many SQL statements use character literals in expressions or conditions. If the literal
itself contains a single quotation mark, you can use the quote (q) operator and select
your own quotation mark delimiter.
We can choose any convenient delimiter, single-byte or multibyte, or any of the
following character pairs: [ ], { }, ( ), or < >.
In the example shown, the string contains a single quotation mark, which is normally
interpreted as a delimiter of a character string. By using the q operator, however,
brackets [ ] are used as the quotation mark delimiters. The string between the brackets
delimiters is interpreted as a literal character string.

DUAL table
What about processing literals that have nothing to do with existing column data?
To ensure relational consistency, Oracle offers a clever solution to the problem of
using the database to evaluate expressions that have nothing to do with any tables or
columns. To get the database to evaluate an expression, a syntactically legal SELECT
statement must be submitted.
What if you wanted to know the sum of two numbers or two numeric literals?
These questions can only be answered by interacting with the database in a relational
manner. Oracle solves the problem of relational interaction with the database
operating on literal expressions by offering a special table called DUAL.
You can execute the query:
SELECT * FROM DUAL

and the data value X is returned as the contents of the DUMMY column.

The DUAL table allows literal expressions to be selected from it for processing and
returns the expression results in its single row. It is exceptionally useful since it enables
a variety of different processing requests to be made from the database.

You may want to know how many seconds there are in a year. The following
example demonstrates an arithmetic expression executed against the DUAL table.

Testing complex expressions during development, by querying the dual table, is an

effective method to evaluate whether these expressions are working correctly.

Duplicate Rows
SELECT department_id
FROM employees;
The default display of queries is all rows, including
duplicate rows.

Unless we indicate otherwise, SQL displays the results of a query without

eliminating the duplicate rows. The example displays all the department numbers
from the EMPLOYEES table. Note that the department numbers are repeated.

To eliminate duplicate rows in the result, include the DISTINCT keyword in the
SELECT clause immediately after the SELECT keyword. In the second example in the
slide, the EMPLOYEES table actually contains 20 rows, but there are only seven unique
department numbers in the table.
We can specify multiple columns after the DISTINCT qualifier. The DISTINCT
qualifier affects all the selected columns, and the result is every distinct combination
of the columns.

SELECT DISTINCT department_id, job_id

FROM employees;

A common technique employed by some exam question designers tests attention to

detail. A single missing punctuation mark like a semicolon may make the difference
between a correct answer and an incorrect one.
Incorrect spelling of object names further tests attention to detail.
You may be asked to choose the correct statement that queries the REGIONS table.
One of the options may appear correct but references the REGION table. This misspelling
can lead to an incorrect statement being chosen.
Expressions offer endless possibilities and are one of the fundamental constructs in
SELECT statements. As you practice SQL on your test database environment, you may
encounter two infamous Oracle errors: ORA-00923: FROM keyword not found where
expected and ORA-00942: table or view does not exist. These are indicative of spelling
or punctuation errors, such as missing enclosing quotes around character literals. Do not
be perturbed by these messages. Remember, you cannot cause damage to the database
if all you are doing is selecting data. It is a read-only operation and the worst you can do
is execute a nonperformant query.

The capabilities of the SELECT statement introduce the three fundamental theoretical
concepts of projection, selection, and joining. Practical examples that illustrate
selection include building the SELECT clause and using the DISTINCT keyword to limit
the rows returned. Projection is demonstrated in examples where columns and
expressions are restricted for retrieval. The second objective of executing a SQL
statement measures your understanding of the basic form of the SELECT statement.
The exam measures two aspects:
-First, syntax is measured: you are required to spot syntax errors. SQL syntax errors
are raised when the Oracle interpreter does not understand a statement. These errors
could take the form of statements missing terminators such as a missing semicolon, not
enclosing character literals in appropriate quote operators, or statements making use
of invalid reserved words.
-Second, the meaning of a statement is measured. You will be presented with a
syntactically legitimate statement and asked to choose between accurate and
inaccurate descriptions of that statement. The exam measures knowledge around the
certification objectives using multiple choice format questions. Your understanding of
column aliasing, arithmetic and concatenation operators, character literal quoting, the
alternative quote operator, SQL statement syntax, and basic column data types will be
tested.

TWO-MINUTE DRILL
1. List the Capabilities of SQL SELECT Statements
a. The three fundamental operations that SELECT statements are capable of are projection,
selection, and joining.
b. Projection refers to the restriction of columns selected from a table. Using projection,
you retrieve only the columns of interest and not every possible column.
c. Selection refers to the extraction of rows from a table. Selection includes the further
restriction of the extracted rows based on various criteria or conditions. This allows you
to retrieve only the rows that are of interest and not every row in the table.
d. Joining involves linking two or more tables based on common attributes. Joining allows
data to be stored in third normal form in discrete tables, instead of in one large table.
e. An unlimited combination of projections, selections, and joins provides the language to
extract the relational data required.
f. A structural definition of a table can be obtained using the DESCRIBE command.
g. Columns in tables store different types of data using various data types, the most
common of which are NUMBER, VARCHAR2, DATE, and TIMESTAMP.
h. The data type NUMBER(x,y) implies that numeric information stored in this column can
have at most x digits, but at least y of these digits must appear on the right hand side of
the decimal point.
i. The DESCRIBE command lists the names, data types, and nullable status of all columns in
a table.
j. Mandatory columns are also referred to as NOT NULL columns

2. Execute a Basic SELECT Statement

a. The syntax of the primitive SELECT clause is as follows:
SELECT *|{[DISTINCT] column|expression [alias],}
b. The SELECT statement is also referred to as a SELECT query and comprises at least two
clauses, namely the SELECT clause and the FROM clause.
c. The SELECT clause determines the projection of columns. In other words, the SELECT
clause specifies which columns are included in the results returned.
d. The asterisk (*) operator is used as a wildcard symbol to indicate all columns. So, the
statement SELECT * FROM ACCOUNTS returns all the columns available in the ACCOUNTS
table.
e. The FROM clause specifies the source table or tables from which items are selected.
f. The DISTINCT keyword preceding items in the SELECT clause causes duplicate
combinations of these items to be excluded from the returned results set.
g. SQL statements should be terminated with a semicolon. As an alternative, a new line can
be added after a statement and a forward slash can be used to execute the statement.
h. SQL statements can be written and executed in lowercase or uppercase. Be careful when
interacting with character literals since these are case-sensitive.
i. Arithmetic operators and the string concatenation operator acting on column and literal
data form the basis of SQL expressions.
j. Expressions and regular columns may be aliased using the AS keyword or by leaving a
space between the column or expression and the alias.

k. If an alias contains multiple words or the case of the alias is important, it must be
enclosed in double quotation marks.
l. Naturally occurring single quotes in a character literal can be selected by making use of
either an additional single quote per naturally occurring quote or the alternative quote
operator.
m. The DUAL table is a single column and single row table that is often used to evaluate
expressions that do not refer to specific columns or tables.
n. Columns which are not governed by a NOT NULL constraint have the potential to store
null values and are sometimes referred to as nullable columns.
o. NULL values are not the same as a blank space or zero. NULL values refer to an absence
of data. Null is defined as a value that is unavailable, unassigned, unknown, or
inapplicable.
p. Caution must be exercised when working with null values since arithmetic with a null
value always yields a null result.

Practice 1
1.

Identify the two SELECT statements that execute successfully.

a. SELECT first_name, last_name, job_id, salary*12 AS Yearly Sal

FROM employees;
b. SELECT first_name, last_name, job_id, salary*12 "yearly_sal"
FROM employees;
c. SELECT first_name, last_name, job_id, salary AS "yearly_sal"
FROM employees;
d. SELECT first_name+last_name AS name, job_Id, salary*12 yearly sal
FROM employees;
e. SELECT employee_id, last_name salary x 12 ANNUAL SALARY
FROM employees;

2. The HR department wants a query to display the last name, job ID, hire date, and
employee ID for each employee, with the employee ID appearing first. Provide an alias
STARTDATE for the HIRE_DATE column. Save your SQL statement to a file named
test1.sql so that you can dispatch this file to the HR department.
Note: After you have executed the query, make sure that you do not enter your next query
in the same worksheet. Open a new worksheet.

3.

The HR department wants a query to display all unique job IDs from the
EMPLOYEES table.
4. The HR department wants more descriptive column headings for its report on
employees. Copy the statement from test1.sql to a new SQL Worksheet. Name
the columns Emp #, Employee, Job and Hire Date, respectively. Then run
the query again.
5. The HR department has requested a report of all employees and their job IDs.
Display the last name concatenated with the job ID (separated by a comma and
space) and name the column Employee and Title.
6. To familiarize yourself with the data in the EMPLOYEES table, create a query
to display all the data from that table. Separate each column output by a comma.
Name the column THE_OUTPUT.

7. The HR department has requested a report with following info: employees, jobs,
start date, end date and the number of days for which staff were employed in a job.
The output should looks exactly like this:

8. Query the JOBS table and return a single expression of the form
The Job Id for the <job_titles> job is: <job_id>.
Take note that the job_title should have an apostrophe and an s appended to it
to read more naturally.
A sample of this output for the organization president is:
The Job Id for the Presidents job is: AD_PRES
Alias this column expression as Job Description using the AS keyword.

Practice 2
1. Which query creates a projection of the DEPARTMENT_NAME and LOCATION_ID columns from the
DEPARTMENTS table? (Choose the best answer.)
A. SELECT DISTINCT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;
B. SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;
C. SELECT DEPT_NAME, LOC_ID FROM DEPT;
D. SELECT DEPARTMENT_NAME AS LOCATION_ID FROM DEPARTMENTS;
2. After describing the EMPLOYEES table, you discover that the SALARY column has a data type of NUMBER(8,2).
Which SALARY value(s) will not be permitted in this column? (Choose all that apply.)
A. SALARY=12345678
B. SALARY=123456.78
C. SALARY=12345.678
D. SALARY=123456
E. SALARY=12.34
3. After describing the JOB_HISTORY table, you discover that the START_DATE and END_DATE columns
have a data type of DATE. Consider the expression END_DATE-START_DATE. (Choose two correct statements.)
A. A value of DATE data type is returned.
B. A value of type NUMBER is returned.
C. A value of type VARCHAR2 is returned.
D. The expression is invalid since arithmetic cannot be performed on columns with DATE data types.
E. The expression represents the days between the END_DATE and START_DATE less one day.

4. The DEPARTMENTS table contains a DEPARTMENT_NAME column with data type VARCHAR2(30).
(Choose two true statements about this column.)
A. This column can store character data up to a maximum of 30 characters.
B. This column must store character data that is at least 30 characters long
C. The VARCHAR2 data type is replaced by the CHAR data type.
D. This column can store data in a column with data type VARCHAR2(50) provided that the contents are at
most 30 characters long.
5.
A.
B.
C.
D.

Which statement reports on unique JOB_ID values from the EMPLOYEES table? (Choose all that apply.)
SELECT JOB_ID FROM EMPLOYEES;
SELECT UNIQUE JOB_ID FROM EMPLOYEES;
SELECT DISTINCT JOB_ID, EMPLOYEE_ID FROM EMPLOYEES;
SELECT DISTINCT JOB_ID FROM EMPLOYEES;

6.
A.
B.
C.
D.

Which expressions do not return NULL values? (Choose all that apply.)
select ((10 + 20) * 50) + null from dual;
select 'this is a '||null||'test with nulls' from dual;
select null/0 from dual;
select null||'test'||null as "Test" from dual;

7. Choose the two illegal statements. The two correct statements produce identical results. The two illegal
statements will cause an error to be raised:
A. SELECT DEPARTMENT_ID|| ' represents the '||
DEPARTMENT_NAME||' Department' as "Department Info"
FROM DEPARTMENTS;
B. SELECT DEPARTMENT_ID|| ' represents the ||
DEPARTMENT_NAME||' Department' as "Department Info"
FROM DEPARTMENTS;
C. select department_id|| ' represents the '||department_name||
' Department' "Department Info"
from departments;
D. SELECT DEPARTMENT_ID represents the DEPARTMENT_NAME Department as
"Department Info"
FROM DEPARTMENTS;
8. Choose the correct syntax to return all columns and rows of data from the EMPLOYEES table.
A. select all from employees;
B. select employee_id, first_name, last_name, first_name, department_id
from employees;
C. select % from employees;
D. select * from employees;
E. select *.* from employees;

9.

The following character literal expression is selected from the DUAL table:
SELECT 'Coda''s favorite fetch toy is his orange ring' FROM DUAL;
(Choose the result that is returned.)
A. An error would be returned due to the presence of two adjacent quotes
B. Coda's favorite fetch toy is his orange ring
C. Coda''s favorite fetch toy is his orange ring
D. 'Coda''s favorite fetch toy is his orange ring
10. There are four rows of data in the REGIONS table. Consider the following SQL statement:
SELECT '6 * 6' "Area" FROM REGIONS;
How many rows of results are returned and what value is returned by the Area column? (Choose the best answer.)
A. 1 row returned, Area column contains value 36
B. 4 rows returned, Area column contains value 36 for all 4 rows
C. 1 row returned, Area column contains value 6 * 6
D. 4 rows returned, Area column contains value 6 * 6 for all 4 rows
E. A syntax error is returned.

Practice 3
1. Create a new Oracle SQL Developer Database Connection using the following
credentials:
Connection Name: myconnection 2
Username: oe
Password: oe
Hostname: localhost
Port: 1521
SID: orcl
Ensure that you select the Save Password check box.
2. Obtain structural information for the PRODUCT_INFORMATION and ORDERS tables.
3. Select the unique SALES_REP_ID values from the ORDERS table. How many different
sales representatives have been assigned to orders in the ORDERS table?
4. Create a results set based on the ORDERS table that includes the ORDER_ID,
ORDER_DATE, and ORDER_TOTAL columns. Notice how the ORDER_DATE output is
formatted differently from the START_DATE and END_DATE columns in the HR.JOB_ID
table.

4. The PRODUCT_INFORMATION table stores data regarding the products available for
sale in a fictitious IT hardware store. Produce a set of results that will be useful for a
sales person.
Extract product information in the following format:
The product: <PRODUCT_NAME> with code: <PRODUCT_ID> has
status of: <PRODUCT_STATUS>. Alias the expression as Product.
The results should provide the following info:
- product information
- LIST_PRICE
- MIN_PRICE,
- the difference between LIST_PRICE and MIN_PRICE aliased as Max Actual
Savings
- an additional expression that takes the difference between LIST_PRICE and
MIN_PRICE and divides it by the LIST_PRICE and then multiplies the total by 100.
This last expression should be aliased as MaxDiscount %

5. Calculate the surface area of the Earth using the DUAL table.
Alias this expression as Earth's Area.
The formula for calculating the area of a sphere is: 4r2. Assume, for this example,
that the earth is a simple sphere with a radius of 3,958.759 miles and that is 22/7.