2008-2009 TLE IV - BLACK BOARD

SQL Statement Guide for Activity #30 - 33

2009-02-17T23:33:00.000-08:00

select job_id, max(salary) "Maximum", min(salary) "Minimum", sum(salary) "Sal", avg(salary) "Average"
from employees
group by job_id
order by 1;

select e.last_name, e.hire_date
from employees e join employees f
on e.hire_date > f.hire_date
and f.last_name = 'Davies';

select last_name, department_id
from employees
where employee_id = '176';

select e.last_name, e.hire_date, f.last_name, f.hire_date
from employees e join employees f
on e.hire_date < f.hire_date
and e.manager_id = f.employee_id;

select last_name, hire_date
from employees
where hire_date like ('%94');

select e.last_name, e.job_id, d.department_id, d.department_name
from locations l join departments d
on l.location_id = d.location_id
join employees e
on e.department_id = d.department_id
and l.city = 'Toronto';

select last_name, job_id, hire_date
from employees
where last_name in ('Matos','Taylor');

select initcap(last_name) "Name", length(last_name) "Length"
from employees
where substr(last_name,1,1) IN ('J','A','M')
order by last_name asc;

select last_name as "Employee", salary as "Monthly Salary"
from employees
where salary between 5000 and 12000
and department_id in ('20','50')
order by last_name asc;

select job_id,
sum(decode(nvl(department_id,'0'),'20',salary,null)) as "Dept20",
sum(decode(nvl(department_id,'0'),'50',salary,null)) as "Dept50",
sum(decode(nvl(department_id,'0'),'80',salary,null)) as "Dept80",
sum(decode(nvl(department_id,'0'),'90',salary,null)) as "Dept90",
sum(salary) "Total"
from employees
group by job_id

select f.department_id, e.last_name, f.last_name "COLLEAGUE"
from employees e join employees f
on e.department_id = f.department_id
and f.last_name <> e.last_name;
desc job_grades

select last_name, job_id
from employees
where manager_id is null;

select last_name, salary, commission_pct
from employees
where commission_pct is not null;

select last_name, salary
from employees
where salary > &salary;

select employee_id, last_name, salary, department_id
from employees
where manager_id = &manager_id
order by &order_by_column;

select last_name
from employees
where last_name like ('__a%');

select last_name
from employees
where last_name like ('%e%')
and last_name like ('%a%');

select last_name, salary "Monthly Salary", commission_pct
from employees
where commission_pct is not null
and commission_pct = 0.2;

select last_name, salary
from employees
where salary > 12000;

select sysdate
from dual;

select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>6000
order by 2 desc;

select employee_id, last_name, salary, salary+salary*0.155 as "New Salary"
from employees;

select employee_id, last_name, salary, salary+salary*0.155 as "New Salary",
(salary+salary*0.155) - salary "Increase"
from employees;

select initcap(last_name) "Name", length(last_name) "Length"
from employees
where substr(last_name,1,1) = '&first_letter'
order by last_name asc;

select last_name, salary
from employees
where salary not between 5000 and 12000;

select last_name||' '||'earns'||' '||salary||' '||'monthly but wants'||' '||'$'||salary*3
from employees;

select last_name, lpad(salary,15,'$')
from employees;

select last_name,
hire_date,
to_char(
next_day(add_months(hire_date,6),'Monday'),
'fmDay ", the" fmDdthsp "of" fmMonth "," fmYYYY')
as "REVIEW"
from employees;

select last_name, hire_date, to_char(hire_date, 'Day') DAY
from employees
order by to_char(hire_date-1, 'D');

select last_name, nvl(to_char(commission_pct),'No Commission') COMM
from employees;

select substr(last_name,1,8)||rpad('*', trunc(salary/1000),'*')
from employees
order by salary desc;

select last_name, trunc(months_between(sysdate,hire_date)) months_worked
from employees;

select job_id, decode(job_id,
'AD_PRES' , 'A',
'ST_MAN' , 'B',
'IT_PROG' , 'C',
'SA_REP' , 'D',
'ST_CLERK' , 'E'
, '0'
) GRADE
from employees;

select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>6000
order by 2 desc;

select job_id, case when job_id = 'AD_PRES' then 'A'
when job_id = 'ST_MAN' then 'B'
when job_id = 'IT_PROG' then 'C'
when job_id = 'SA_REP' then 'D'
when job_id = 'SA_CLERK' then 'E'
else '0'
end as GRADE
from employees;

select max(salary) "Maximum", min(salary) "Minimum", sum(salary) "Sal", avg(salary) "Average"
from employees;

select job_id, count(*)
from employees
group by job_id
order by 1;

select count(*) "Total",
sum(decode(to_char(hire_date,'yyyy'),'1995',1,0)) "1995",
sum(decode(to_char(hire_date,'yyyy'),'1996',1,0)) "1996",
sum(decode(to_char(hire_date,'yyyy'),'1997',1,0)) "1997",
sum(decode(to_char(hire_date,'yyyy'),'1997',1,0)) "1998"
from employees

select count(distinct manager_id) "Number of Managers"
from employees
where manager_id is not null;

select (max(salary)-min(salary)) "Difference"
from employees;

select location_id, street_address, city, state_province, country_name
from locations l natural join countries c
order by 1 asc

select e.last_name, e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

select e.last_name, e.employee_id, f.last_name "Manager", e.manager_id
from employees e join employees f
on e.manager_id = f.employee_id;

select e.last_name, e.job_id, d.department_name,e.salary, j.grade_level grade
from employees e join departments d
on e.department_id = d.department_id
join job_grades j
on e.salary between lowest_sal and highest_sal;

select last_name, department_id
from employees
where department_id in ('20','50')
order by last_name asc;

select last_name, job_id, salary
from employees
where job_id in ('SA_REP','ST_CLERK')
and salary not in ('2500','3500','7000');

select e.last_name, e.employee_id, f.last_name "Manager", e.manager_id
from employees e left outer join employees f
on e.manager_id = f.employee_id;

Coverage Lessons for Final Exam 2009

2009-02-12T18:12:00.000-08:00

CASE AND CHARACTER MANIPULATION

What Will I Learn?

In this lesson, you will learn to:

Differentiate between operations of single-row functions and multiple-row functions

Select and apply single-row functions that perform case conversion and/or character manipulation

Select and apply character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query

Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query

Why Learn It?

Have you ever thought about the different ways in which we present ourselves? We dress up for special occasions, dress casually for play, and put on uniforms for sports events and band concerts. Being able to change the way we look for different situations is important. How would you choose to present yourself for a job interview?

Being able to change the way in which data is presented is important when dealing with data from a database. Most of the time in SQL, we need to change the way that data appears depending on the requirements of the task we are trying to accomplish.

In this section, you will learn several ways in which to transform data to fit a particular situation.

Tell Me/Show Me

DUAL TABLE

The DUAL table has one row called "X" and one column called "DUMMY." The DUAL table is used to create SELECT statements and execute commands not directly related to a specific database table. Queries using the DUAL table return one row as a result. DUAL can be useful to do calculations such as the following example and also to evaluate expressions that are not derived from a table.

DUAL will be used to learn many of the single-row functions.

SELECT (319/29) +12

FROM DUAL;

SINGLE-ROW CHARACTER FUNCTIONS

Single-row character functions are divided into two categories: functions that convert the case of character strings and functions that can join, extract, show, find, pad, and trim character strings.

Single-row functions can be used in the SELECT, WHERE, and ORDER BY clauses.

Case-manipulation functions are important because you may not always know which case (upper, lower, or mixed) the data is stored in in the database. Case manipulation allows you to temporarily convert the database data to a case of your choosing. Mismatches between database case storage and query case requests are avoided.

CASE-MANIPULATION FUNCTIONS

LOWER(column | expression) converts alpha characters to lower-case.

SELECT title

FROM d_cds

WHERE LOWER(title) = 'carpe diem';

UPPER(column | expression) converts alpha characters to upper-case.

SELECT title

FROM d_cds

WHERE UPPER(title) = 'CARPE DIEM';

INITCAP( column | expression) converts alpha character values to uppercase for the first letter of each word.

SELECT title

FROM d_cds

WHERE INITCAP(title) = 'Carpe Diem';

CHARACTER-MANIPULATION FUNCTIONS

CONCAT: Joins two values together.

SUBSTR: Extracts a string of a determined length.

LENGTH: Shows the length of a string as a number value.

INSTR: Finds the numeric position of a named character.

LPAD: Pads the left side of a character, resulting in a right-justified value.

RPAD: Pads the right-hand side of a character, resulting in a left- justified value.

TRIM: Removes all specified characters from either the beginning or the ending of a string. The syntax for the trim function is: trim ( [ leading | trailing | both [character(s) to be removed] ] string to trim )

If none of these is chosen (i.e., leading, trailing, both), the trim function will remove [the character(s) to be removed ] from both the front and end of [string to trim].

REPLACE: Replaces a sequence of characters in a string with another set of characters. The syntax for the REPLACE function is:

replace (string1, string_to_replace, [replacement_string] )

string1 is the string that will have characters replaced in it. string_to_replace is the string that will be searched for and taken out of string1.

[replacement_string] is the new string to be inserted in string1.

All functions operate on values that are in parentheses, and each function name decides its purpose, which is helpful to remember when constructing a query. Also, note the use of column aliases for columns with functions. In the following examples, the alias "User Name" has replaced the function syntax. By default, the column name appears as the column heading. In this query, however, there is no column in the table for the results produced, so the query syntax is used instead.

SELECT LOWER (last_name)||LOWER(SUBSTR(first_name,1,1)) AS "User Name"

FROM f_staffs;

NUMBER FUNCTIONS

What Will I Learn?

In this lesson, you will learn to:

Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query

Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value

State the implications for business when applying TRUNC and ROUND to numeric values

Why Learn It?

One of the reasons we put our money in a bank is to take advantage of the interest it accumulates over time. Banks adjust the interest rate with various economic indicators such as inflation and the stock market. Typically, interest rates are expressed as a percent such as 3.45%.

What if a bank decided to round the percentage rate to 3.5%? Would it be to your advantage? What if they decided to just drop the decimal values and calculate the interest at 3%, would you be happy then?

Rounding and truncating numbers play an important part in business and in turn with the databases that support these businesses as they store and access numeric data.

Tell Me/Show Me

As you might expect, the number functions accept numeric input and return numeric values.

The three number functions are:

ROUND: Used to round numbers to a specified number of decimal places. ROUND can also be used to round numbers to the left of the decimal point. ROUND can also be used with dates.

TRUNC: Used to terminate the column, expression, or value to a specified number of decimal places. When TRUNC is used, if the number of decimal places is not specified, then the specified number defaults to zero. TRUNC can also be used with dates.

MOD: Used to return the remainder when one number is divided by another.

ROUND

The syntax for the ROUND function is:

ROUND(column|expression, decimal places)

Note that if the number of decimal places is not specified or is zero, the number will round to no decimal places.

ROUND(45.926) 46

ROUND(45.926, 0)

If the number of decimal places is a positive number, the number is rounded to that number of decimal places.

ROUND(45.926, 2) 45.93

If the number of decimal places is a negative number, numbers to the left of the decimal are rounded.

ROUND(45.926, -1) 50

TRUNC

The TRUNC function terminates or cuts off the number to the number of decimal places specified. The syntax for the TRUNC function is:

TRUNC(column|expression, decimal places)

TRUNC (45.926, 2) 45.92

As with ROUND, if the TRUNC expression does not specify the number of decimal places or specifies a zero, the number is truncated to zero decimal places.

TRUNC (45.926, 0) 45

TRUNC (45.926)

Remember that TRUNC does not round the number. It simply terminates the number at a given point.

MOD

The MOD function finds the remainder of one value divided by another value.

For example, the MOD of 5 divided by 2 = 1.

MOD can be used to determine whether a value is odd or even. If you divide a value by 2, and there is no remainder, the number must be an even number. Using the MOD function with 2, as in 12%2 and there is no remainder, the number must have been an even number.

MOD( 1600 / 300) 100 remainder

SELECT last_name, salary, MOD(salary, 2)

As "Mod Demo"

FROM f_staffs

WHERE staff_type IN(‘Order Taker’, ’Cook’, ‘Manager');

DATE FUNCTIONS

What Will I Learn?

In this lesson, you will learn to:

Select and apply the single-row functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC that operate on date data

Explain how date functions transform Oracle dates into date data or a numeric value

Demonstrate proper use of the arithmetic operators with dates

Demonstrate the use of SYSDATE and date functions

State the implications for world businesses to be able to easily manipulate data stored in date format

Why Learn It?

Have you ever wondered how many days remain in the school year or how many weeks there are until graduation? Because the Oracle database stores dates as numbers, it's easy to perform calculations on dates using addition and subtraction.

Businesses depend on being able to use date functions to schedule payrolls and payments, track employee performance reviews and years of service, or keep track of orders and shipments. All of these business needs are easily handled using simple SQL date functions.

Tell Me/Show Me

DATE

The default display format for dates is DD-MON-RR -- that is, 02-DEC-99.

However, the Oracle database stores dates internally with a numeric format, representing the century, year, month, day, hours, minutes, and seconds.

The default display and input format for any date is DD-MON-RR. Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999 A.D. This represents the range of dates that you can store successfully in an Oracle database.

When a record with a date column is inserted into a table, the century information is picked up from the SYSDATE function. SYSDATE is a date function that returns the current database server date and time.

SYSDATE

To display the current date, use the DUAL table.

SELECT SYSDATE

FROM DUAL;

The DATE data type always stores year information as a four-digit number internally: two digits for the century and two digits for the year. For example, the Oracle database stores the year as 1996 or 2004, not just as 96 or 04.

Although the internal storage keeps track of the complete date, when the date column is displayed on the screen, the century component is not displayed by default.

Subtract two dates

SELECT last_name, (SYSDATE - hire_date)/ 7 AS "Weeks"

FROM employees;

SELECT (SYSDATE - hire_date) / 365 AS "YEARS WORKED"

FROM employees;

Add a number to a date

SELECT hire_date + 90 AS "FIRST EVALUATION"

FROM employees;

Subtract a number from a date

SELECT SYSDATE - 180 AS "QUARTERLY REVIEW"

FROM DUAL;

Explain the term "data type" as a standard form of data. In SQL, as in other computer languages, dates, numbers, and character data represent types or kinds of information being processed. A more in-depth study of data types is presented in Section 8, Lesson 4 "Creating Tables."

The date functions shown in the table operate on Oracle dates. All of the date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

The following query shows how these functions are used.

SELECT employee_id, hire_date,

MONTHS_BETWEEN(SYSDATE, hire_date) AS TENURE,

ADD_MONTHS (hire_date, 6) AS REVIEW,

NEXT_DAY(hire_date, 'FRIDAY'),

LAST_DAY(hire_date)

FROM employees

WHERE MONTHS_BETWEEN (SYSDATE, hire_date) > 36;

The result set from this query returns 20 rows including:

CASE AND CHARACTER MANIPULATION

What Will I Learn?

In this lesson, you will learn to:

Differentiate between operations of single-row functions and multiple-row functions

Select and apply single-row functions that perform case conversion and/or character manipulation

Select and apply character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query

Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query

Why Learn It?

In this section, you will learn several ways in which to transform data to fit a particular situation.

Tell Me/Show Me

DUAL TABLE

DUAL will be used to learn many of the single-row functions.

SELECT (319/29) +12

FROM DUAL;

SINGLE-ROW CHARACTER FUNCTIONS

Single-row character functions are divided into two categories: functions that convert the case of character strings and functions that can join, extract, show, find, pad, and trim character strings.

Single-row functions can be used in the SELECT, WHERE, and ORDER BY clauses.

CASE-MANIPULATION FUNCTIONS

LOWER(column | expression) converts alpha characters to lower-case.

SELECT title

FROM d_cds

WHERE LOWER(title) = 'carpe diem';

UPPER(column | expression) converts alpha characters to upper-case.

SELECT title

FROM d_cds

WHERE UPPER(title) = 'CARPE DIEM';

INITCAP( column | expression) converts alpha character values to uppercase for the first letter of each word.

SELECT title

FROM d_cds

WHERE INITCAP(title) = 'Carpe Diem';

CHARACTER-MANIPULATION FUNCTIONS

CONCAT: Joins two values together.

SUBSTR: Extracts a string of a determined length.

LENGTH: Shows the length of a string as a number value.

INSTR: Finds the numeric position of a named character.

LPAD: Pads the left side of a character, resulting in a right-justified value.

RPAD: Pads the right-hand side of a character, resulting in a left- justified value.

If none of these is chosen (i.e., leading, trailing, both), the trim function will remove [the character(s) to be removed ] from both the front and end of [string to trim].

REPLACE: Replaces a sequence of characters in a string with another set of characters. The syntax for the REPLACE function is:

replace (string1, string_to_replace, [replacement_string] )

string1 is the string that will have characters replaced in it. string_to_replace is the string that will be searched for and taken out of string1.

[replacement_string] is the new string to be inserted in string1.

SELECT LOWER (last_name)||LOWER(SUBSTR(first_name,1,1)) AS "User Name"

FROM f_staffs;

NUMBER FUNCTIONS

What Will I Learn?

In this lesson, you will learn to:

Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query

Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value

State the implications for business when applying TRUNC and ROUND to numeric values

Why Learn It?

Rounding and truncating numbers play an important part in business and in turn with the databases that support these businesses as they store and access numeric data.

Tell Me/Show Me

As you might expect, the number functions accept numeric input and return numeric values.

The three number functions are:

ROUND: Used to round numbers to a specified number of decimal places. ROUND can also be used to round numbers to the left of the decimal point. ROUND can also be used with dates.

MOD: Used to return the remainder when one number is divided by another.

ROUND

The syntax for the ROUND function is:

ROUND(column|expression, decimal places)

Note that if the number of decimal places is not specified or is zero, the number will round to no decimal places.

ROUND(45.926) 46

ROUND(45.926, 0)

If the number of decimal places is a positive number, the number is rounded to that number of decimal places.

ROUND(45.926, 2) 45.93

If the number of decimal places is a negative number, numbers to the left of the decimal are rounded.

ROUND(45.926, -1) 50

TRUNC

The TRUNC function terminates or cuts off the number to the number of decimal places specified. The syntax for the TRUNC function is:

TRUNC(column|expression, decimal places)

TRUNC (45.926, 2) 45.92

As with ROUND, if the TRUNC expression does not specify the number of decimal places or specifies a zero, the number is truncated to zero decimal places.

TRUNC (45.926, 0) 45

TRUNC (45.926)

Remember that TRUNC does not round the number. It simply terminates the number at a given point.

MOD

The MOD function finds the remainder of one value divided by another value.

For example, the MOD of 5 divided by 2 = 1.

MOD( 1600 / 300) 100 remainder

SELECT last_name, salary, MOD(salary, 2)

As "Mod Demo"

FROM f_staffs

WHERE staff_type IN(‘Order Taker’, ’Cook’, ‘Manager');

DATE FUNCTIONS

What Will I Learn?

In this lesson, you will learn to:

Select and apply the single-row functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC that operate on date data

Explain how date functions transform Oracle dates into date data or a numeric value

Demonstrate proper use of the arithmetic operators with dates

Demonstrate the use of SYSDATE and date functions

State the implications for world businesses to be able to easily manipulate data stored in date format

Why Learn It?

Tell Me/Show Me

DATE

The default display format for dates is DD-MON-RR -- that is, 02-DEC-99.

However, the Oracle database stores dates internally with a numeric format, representing the century, year, month, day, hours, minutes, and seconds.

SYSDATE

To display the current date, use the DUAL table.

SELECT SYSDATE

FROM DUAL;

Although the internal storage keeps track of the complete date, when the date column is displayed on the screen, the century component is not displayed by default.

Subtract two dates

SELECT last_name, (SYSDATE - hire_date)/ 7 AS "Weeks"

FROM employees;

SELECT (SYSDATE - hire_date) / 365 AS "YEARS WORKED"

FROM employees;

Add a number to a date

SELECT hire_date + 90 AS "FIRST EVALUATION"

FROM employees;

Subtract a number from a date

SELECT SYSDATE - 180 AS "QUARTERLY REVIEW"

FROM DUAL;

The date functions shown in the table operate on Oracle dates. All of the date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

The following query shows how these functions are used.

SELECT employee_id, hire_date,

MONTHS_BETWEEN(SYSDATE, hire_date) AS TENURE,

ADD_MONTHS (hire_date, 6) AS REVIEW,

NEXT_DAY(hire_date, 'FRIDAY'),

LAST_DAY(hire_date)

FROM employees

WHERE MONTHS_BETWEEN (SYSDATE, hire_date) > 36;

The result set from this query returns 20 rows including:

CARTESIAN PRODUCT AND THE JOIN OPERATIONS

What Will I Learn?

In this lesson, you will learn to:

Describe the purpose of join conditions

Construct and execute a SELECT statement that results in a Cartesian product

Construct and execute SELECT statements to access data from more than one table using an equijoin

Construct and execute SELECT statements that add search conditions using the AND operator

Apply the rule for using column aliases in a join statement

Provide evidence to answer the question "Why is it important, from a business perspective, for a language to be able to combine information from multiple data sources?"

Why Learn It?

Up to now, your experience using SQL has been limited to querying and returning information from one database table at a time. This would not be a problem if all data in the database were stored in only one table. But you know from data modeling that separating data into individual tables and being able to associate the tables with one another is the heart of relational database design. Fortunately, SQL provides join conditions that enable information to be queried from separate tables and combined in one report.

Tell Me/Show Me

There are two sets of commands or syntax which can be used to make connections between tables in a database:

- Oracle proprietary joins

- ANSI/ISO SQL 99 compliant standard joins

In this course, you will learn to use both sets of join commands.

ORACLE PROPRIETARY JOINS

To query data from more than one table using the Oracle proprietary syntax, use a join condition in the WHERE clause.

The basic format of a join statement is:

SELECT table1.column, table2.column

FROM table1, table2

WHERE table1.column1 = table2.column2;

For clarity and to enhance database access, it is a good practice to preface the column name with the table name. This is called "qualifying your columns." The combination of table name and column name helps eliminate ambiguous names when two tables contain a column with the same column name. Note: When the same column name appears in both tables, the column name must be prefaced with the name of the table. It's similar to having two students in the same class with the same last name. When needing to speak to "Jackson," the teacher clarifies which "Jackson" by prefacing the last name with the first name.

In the example at right, which two tables are being joined? Which identical columns do these tables share?

If you wanted to join three tables together, how many joins would it take? How many bridges are needed to join three islands?

EQUIJOIN

Sometimes called a "simple" or "inner" join, an equijoin is a table join that combines rows that have equivalent values for the specified columns. In the example shown, the what, where, and how are required for the join condition.

What? The SELECT clause specifies the column names to retrieve.

Where? The FROM clause specifies the two tables that the database must access.

How? The WHERE clause specifies how the tables are to be joined.

CARTESIAN PRODUCT JOIN

If two tables in a join query have no join condition specified in the WHERE clause or the join condition is invalid, the Oracle Server returns the Cartesian product of the two tables. This is a combination of each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows! This may not be what you were trying to retrieve.

To avoid a Cartesian product, always include a valid join condition in a WHERE clause.

As with single-table queries, the WHERE clause can be used to restrict the rows considered in one or more tables of the join. The query shown uses the AND operator to restrict the rows returned. Compare this result with the previous query.

SELECT d_play_list_items.song_id, d_play_list_items.event_id, d_track_listings.cd_number

FROM d_play_list_items, d_track_listings

WHERE d_play_list_items.song_id = d_track_listings.song_id

AND d_play_list_items.event_id <>

ALIASES

Working with lengthy column and table names can be cumbersome. Fortunately, there is a way to shorten the syntax using aliases. To distinguish columns that have identical names but reside in different tables, use column aliases. Take a look at how column aliases were used in the following query. When there are no shared column names between two tables, there is no need to add the table name to it.

SELECT d_track_listings.song_id AS TRACK , d_play_list_items.song_id AS " PLAY LIST"

FROM d_play_list_items, d_track_listings

WHERE d_play_list_items.song_id = d_track_listings.song_id;

Table aliases precede the column name. However, if a table alias is used in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.

SELECT p.song_id , t.song_id

FROM d_play_list_items p, d_track_listings t

WHERE p.song_id = t.song_id;

NON EQUIJOINS

What Will I Learn?

In this lesson, you will learn to:

Construct and execute a SELECT statement to access data from more than one table using a nonequijoin

Why Learn It?

What happens if you want to retrieve data from a table that has no corresponding column in another table? For instance, your math percentage grade of 92 is stored in the GRADES column in one table; the letter grade is stored in the LETTER_GRADE column in another table. How can we join the number grade with the letter grade? When data is recorded using a range, retrieving it is the job of a nonequijoin.

Tell Me/Show Me

NONEQUIJOIN

To join your number

grade (or %)

in math with its corresponding letter grade, a nonequijoin is needed. Since there is no exact match between the two columns in each table, the equality operator = can't be used. Although comparison conditions such as < = and > = can be used, BETWEEN...AND is the simplest.

The query shown joins the D_EVENTS cost column with the D_PACKAGES low_range and high_range columns using BETWEEN...AND

SELECT d_packages.code, d_events.cost

FROM d_packages, d_events

WHERE d_events.cost BETWEEN d_packages.low_range AND d_packages.high_range

OUTER JOINS

What Will I Learn?

In this lesson, you will learn to:

Create and execute a SELECT statement to access data from more than one table using an outer join

Build positive associations between learning and work

Why Learn It?

The joins you've studied so far returned rows that either had a matching value in both tables or a value that in one table was between two values in a different table. Those rows that didn't satisfy these conditions were just left out. Sometimes, however, you want all the data from one of the tables even if there is no matching data in the other table. If you wanted to know all the girls or boys who attended a dance, would simply counting the couples dancing work? In Oracle SQL, the missing data can be returned using an outer join.

Tell Me/Show Me

An outer join is used to see rows that have a corresponding value in another table plus those rows in one of the tables that have no matching value in the other table. To indicate which table may have missing data use, a plus sign (+) after the table's column name in the query. The query below uses the plus sign to indicate the table whose column is missing data. The variations of the outer join are shown.

SELECT table1.column, table2.column

FROM table1, table

WHERE table1.column(+) = table2.column;

WHERE table1.column = table2.column(+);

NEVER table1.column(+) = table2.column(+);

An outer join cannot use the IN operator or be linked to another condition by the OR operator.

SELF JOINS

What Will I Learn?

In this lesson, you will learn to:

Construct and execute a SELECT statement to join a table to itself using a self-join

Why Learn It?

In data modeling, it was sometimes necessary to show an entity with a relationship to itself. For example, an employee can also be a manager. We showed this using the "pig’s ear" relationship. Once we have a real employees table, a special kind of join called a self-join is required to access this data. You probably realize by now the importance of a data model once it becomes a database. It’s no coincidence that the data model looks a lot like the tables we now have in the database.

Tell Me/Show Me

SELF-JOIN

To join a table to itself, the table is given two names or aliases. This will make the database “think” that there are two tables. Choose alias names that relate to the data's association with that table. In this example of a band, we have members of the band who play an instrument and members of the band who play an instrument and are their section's lead player or chair. A readable way to show this self-join is:

SELECT chair.last_name || ' is the section chair of ' ||

player.last_name

FROM band_members chair, band_members player

WHERE player.chair_id = chair.member_id;

PUTTING IT ALL TOGETHER

What Will I Learn?

In this lesson, you will learn

Identify factors that contribute to the changing nature of work

Incorporate into a career plan skills to stay up-to-date in anticipation of the changing nature of work

Why Learn It?

One certainty in life is that things change. Think about the video games, computer CRT monitors, cameras, or cell phones that were available two years ago. Are they still the latest technology? What if you had a job manufacturing floppy disks or installing and repairing pay telephones? Could you count on doing the same job ten years from now? In today’s world, staying abreast of change is critical. Having a plan to keep skills up-to-date is an important step in planning for your future.

Tell Me/Show Me

Think of jobs that are now extinct or types of jobs that you think will be obsolete in the next few years. Be prepared to explain why you feel the job will no longer exist.

CROSS JOINS AND NATURAL JOINS

What Will I Learn?

In this lesson, you will learn to:

Compose and execute a natural join using SQL join syntax

Create a Cartesian product using SQL join syntax

Define the relationship between a cross-join and a Cartesian product

Define the relationship between a natural join and an equijoin

Explain why it is important to have a standard for SQL as defined by ANSI

Why Learn It?

The join statements you learned in the last few lessons have a corresponding statement when using ANSI/ISO SQL: 1999 syntax. Being able to use both kinds of joins effectively is the responsibility of any person using SQL. You may join a project someday when a programmer before you used only ANSI/ISO SQL: 1999. What would you do if you knew only the Oracle proprietary methods? How would you debug their code?

Tell Me/Show Me

NATURAL JOIN

Recall the equijoin from a previous lesson. An equijoin returns all rows whose values match in both tables. The ANSI/ISO SQL: 1999 join that accomplishes the same result is called a natural join. A natural join is based on all columns in the two tables that have the same name and selects rows from the two tables that have equal values in all matched columns. As shown in the sample code, when using a natural join, it is possible to join the tables without having to explicitly specify the columns in the corresponding table. However, the names and data types in both columns must be the same.

SELECT event_id, song_id, cd_number

FROM d_play_list_items NATURAL JOIN d_track_listings

WHERE event_id = 105;

The WHERE clause was added to apply additional restrictions to limit the rows of output.

Cross-Join - The ANSI/ISO SQL: 1999 SQL equivalent of the Cartesian product is the cross-join. The results returned from both types of joins are the same. The results set represents all possible combinations of columns from both tables. This could potentially be very large!

JOIN CLAUSES

What Will I Learn?

In this lesson, you will learn to:

Compose and execute a join with the USING and ON clauses

Compose and execute an ANSI/ISO SQL: 1999 query that joins three tables

Name the Oracle proprietary joins and their ANSI/ISO SQL: 1999 counterparts

Why Learn It?

As you add more commands to your database vocabulary, you will be better able to design queries that return the desired result. The purpose of a join is to bind data together, across tables, without repeating all of the data in every table. Why ask for more data than you really need?

Tell Me/Show Me

USING CLAUSE

In a natural join, if the tables have columns with the same names but different data types, the join causes an error. To avoid this situation, the join clause can be modified with a USING clause. The USING clause specifies the columns that should be used for the equijoin. The query shown is an example of the USING clause. The columns referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

ON CLAUSE

The ON clause can be used to specify columns to join. The advantage of the ON clause is the ability to specify the join conditions separate from the WHERE clause. The WHERE clause can then be used for search or filter conditions.

SELECT c.client_number, c.first_name, c.last_name, e.event_date

FROM d_clients c JOIN d_events e

ON (e.client_number = c.client_number);

The ON clause can also be used to specify arbitrary conditions such as joining columns that have different names. In the example, the ON clause is used in a self-join where the same table is given two different references. In the employees table, some employees are also managers. The self-join is used to select those employees who are also managers. Note: If the query omitted the WHERE clause, all employees and their managers would be returned. In this case, using ON allows us to use WHERE to restrict rows.

SELECT e.last_name as "EMP", w.last_name as "MGR"

FROM employees e JOIN employees w

ON (e.manager_id = w.employee_id)

WHERE e.last_name like 'H%';

INNER VS OUTER JOINS

What Will I Learn?

In this lesson, you will learn to:

Compare and contrast an inner and an outer join

Construct and execute a query to use a left outer join

Construct and execute a query to use a right outer join

Construct and execute a query to use a full outer join

Construct and execute a query to use an inner join

Why Learn It?

Up to now, all of the joins returned data that matched the join condition. Sometimes, however, it is desirable not only to retrieve data that meets the join condition, but also to retrieve data that doesn’t meet a join condition. This should sound familiar! The outer joins in ANSI SQL allow this functionality.

Tell Me/Show Me

LEFT AND RIGHT OUTER JOINS

In ANSI SQL, joins of two or more tables that return only matched rows are referred to as inner joins. When a join returns the matched rows as well as unmatched rows, it is called an outer join. Outer join syntax uses the terms “left, full, and right.” These names are associated with the order of the table names in query. In the example shown of a left outer join, note that the table name listed to the left of the words "left outer join" is referred to as the "left table." This query will return all matched rows as well as all employee last names even if they aren’t assigned a department.

The right outer join would return all department IDs and department names even if no employees were assigned to them.

FULL OUTER JOIN

It is possible to create a join condition to retrieve all matching rows and all unmatched rows from both tables in a join. Recall the Oracle proprietary outer join syntax that used the (+) to indicate the column with missing data. Remember, in this form of an outer join, it was not possible to put a (+) on both sides of the WHERE clause.

Using a full outer join solves this problem. The results set of a full outer join includes all rows in both tables even if there is no match in the other table.

The example shown is a full outer join.

Construct a join to display a list of Global Fast Foods customers whether or not they have placed an order as yet and all the customers who have placed orders.

GROUP FUNCTIONS

What Will I Learn?

In this lesson, you will learn to:

Define and give an example of the seven group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE

Construct and execute a SQL query using SELECT, FROM, WHERE, GROUP BY, ORDER BY syntax using group functions

Construct and execute group functions that operate only with numeric data types

Construct and execute group functions that operate to produce a single value

Compare and contrast the result set obtained from single-row functions versus group functions

Provide evidence to support the assigned topic "Why is it important, from a business perspective, to be able to easily aggregate data (group). What business problems does this solve?"

Why Learn It?

What if you were writing an article for the school newspaper and, to make a point, you wanted to know the average age of the students at your school? What would you have to do to get this information? You could ask all students for their age in years, months, and days, add up all of these numbers, and then divide by the number of students in your school. That would be one way -- a very difficult way -- to find this information. What if you needed to know this now so that you could meet a 3:00 p.m. deadline? You might have a problem!

What if all of the students’ birthdays were in a school database in the STUDENT table? It would be so easy then! In this lesson, you are going to learn about the power of group functions in SQL.

Tell Me/Show Me

A quick review question:

In the DJ on Demand table, there is a D_TYPES table listing the types of music available and a D_SONGS table listing song titles. Write a SELECT statement to show the type of music and the type of each song in the D_SONGS table as shown in the graphic example. Include all types of songs even if they don't have songs associated with them.

GROUP FUNCTIONS

In SQL, the following group functions can operate on a whole table or on a specific grouping of rows. Each function returns one result.

AVG: Used with columns that store numeric data to compute the average, ignoring null values.

COUNT: Returns the number of rows.

MIN: Used with columns that store any data type to return the minimum value, ignoring null values.

MAX: Used with columns that store any data type to return the maximum value, ignoring null values.

SUM Used with columns that store numeric data to find the total or sum of values, ignoring null values.

VARIANCE: Used with columns that store numeric data to calculate the spread of data around the mean. For example, if the average grade for the class on the last test was 82% and the student's scores ranged from 40% to 100%, the variance of scores would be greater than if the student's scores ranged from 78% to 88%.

STDDEV: Similar to variance, standard deviation measures the spread of data. For two sets of data with approximately the same mean, the greater the spread, the greater the standard deviation.

Group functions are written in the SELECT clause:

SELECT column, group_function(column_), ..

FROM table

WHERE condition

GROUP BY column

ORDER BY column;

Before getting started using group functions, there are a few important things you should know about them:

- The keyword DISTINCT can be used with all group functions. Using DISTINCT makes the function consider only nonduplicate values. The statement below uses DISTINCT to select the nonduplicate type codes in the DJ on Demand table D_SONGS.

SELECT DISTINCT type_code

FROM d_songs;

- The keyword ALL makes the function consider every value, including duplicates. ALL is the default, so it need not be specified.

- Group functions cannot be used in the WHERE clause.

- Group functions ignore NULL values. In the example below, the (null) values were not used to find the average overtime rate.

- Group functions require that any column listed in the SELECT clause that is not part of the group function must be listed in a GROUP BY clause.

Because name was listed in the SELECT statement and not encapsulated in the AVG function, you must use a GROUP BY clause.

SELECT name, AVG(cost)

FROM d_events

GROUP BY name;

The Rules for Group Functions

- The keyword DISTINCT can be used with all group functions.

- The keyword ALL makes the function consider every value, including duplicates.

- Group functions cannot be used in the WHERE clause.

- Group functions require that any column listed in the SELECT clause that is not part of the group function must be listed in a GROUP BY clause.

Two group functions, MIN and MAX, can be used with any data type. Using these functions, it is possible to find the name of the last person in a list, the smallest salary, or the earliest hire date. For example, it is easy to find the person whose name is last in an alphabetical list of staff at Global Fast Foods.

COUNT, DISTINCT, NVL

What Will I Learn?

In this lesson, you will learn to:

Construct and execute a SQL query applying COUNT, DISTINCT, NVL group functions

Being able to aggregate data using SQL functions enables businesses to do calculations that would otherwise have to be done by hand. Remember the example of having to count all the students in your school? What if our government needed to count all the people in the country to collect census data or employment data? A daunting task! There just aren't enough hands to accomplish it manually. Fortunately, there are group functions that can quickly process these types of requests.

Why Learn It?

Three additional group functions that add more functionality to process aggregate data are:

COUNT: Returns the number of rows in a table. There are three formats of COUNT.

DISTINCT: Returns nonduplicate values.

NVL: Forces group functions to include null values.

COUNT

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement. For example, to find out how many of DJs on Demand's CDs were produced before 2001, COUNT can be used in the SELECT statement.

COUNT(expression) returns the number of nonnull values in the expression column

COUNT(DISTINCT expression) returns the number of unique nonnull values in the expression column.

Tell Me/Show Me

Why are null values returned in the query shown? There are six comments listed, but the count function returned only five. Why?

DISTINCT

The keyword DISTINCT is used to return only nonduplicate values or combinations of nonduplicate values in a query.

Examine the query shown at right. Without using the keyword DISTINCT, the query returned all of the year values from the DJs on Demand D_CDS table.

To eliminate nonduplicate rows, use the DISTINCT keyword as shown here. Using the DISTINCT keyword returned all the CD years with no duplicate values.

The keyword DISTINCT, when used in a query selecting more than one column, will return nonduplicate combinations of the columns. Examine the two results sets shown here. Can you tell which query used the DISTINCT keyword?

In this case, it's hard to tell, isn't it? The results set on the left was returned using the DISTINCT keyword. In both examples, there are no duplicate combinations of year and title even though there are duplicate years.

When using DISTINCT with a group function such as COUNT, the results set will return the number of nonduplicate rows or combinations of rows. The following query of the DJs on Demand D_CDs table produced the results shown here.

NVL

Sometimes it is desirable to include null values in group functions. For example, knowing the average number of customer orders served each day could be used to judge how much food to order each month. Some days the restaurant is closed and there are no customers, but the owner has found that computing the average including every day is a better indicator than just counting the days with customers. The SELECT statement to include null values could be written:

SELECT AVG(NVL(customer_orders, 0))

GROUP BY and HAVING clauses

What Will I Learn?

In this lesson, you will learn

Construct and execute a SQL query applying GROUP BY

Construct and execute a SQL query applying HAVING

State the purpose of the GROUP BY clause for aggregate functions

State the purpose of the HAVING clause for aggregate functions

Why Learn It?

What if you wanted to know the average height of all students? You could write a query that looks like this:

SELECT AVG(height)

FROM students;

But what if you wanted to know the average height of the students based on their year in school? Right now, you would have to write a number of different SQL statements to accomplish this:

SELECT AVG(height)

FROM students

WHERE year_in_school = 10;

SELECT AVG(height)

FROM students

WHERE year_in_school = 11;

SELECT AVG(height)

FROM students

WHERE year_in_school = 12;

And so on! What you’ll learn in this lesson is how to simplify problems like this with just one statement. It relies on the GROUP BY and HAVING clauses.

Tell Me/Show Me

Because aggregate functions (like SUM) return the aggregate of all column values every time they are called, it would be impossible to find the sum for each individual group of column values. The GROUP BY clause enables aggregate data to be applied to groups of column values.

To restrict rows returned with aggregate functions, you must use a HAVING clause. Aggregate functions cannot appear in the WHERE clause. The HAVING clause was added to SQL so the returned rows could be restricted when using an aggregate function.

GROUP BY

You use the GROUP BY clause to divide the rows in a table into smaller groups. You can then use the group functions to return summary information for each group.

Important guidelines to remember when using a GROUP BY clause are:

- If you include a group function (AVG, SUM,COUNT, MAX, MIN, STDDEV, VARIANCE) in a SELECT clause and any other individual columns, each individual column must also appear in the GROUP BY clause.

- You cannot use a column alias in the GROUP BY clause.

- The WHERE clause excludes rows before they are divided into groups.

- A GROUP BY clause can be used in a SELECT statement without having a group function in the SELECT statement.

In the SELECT statement shown, the rows are being grouped by department_id. The AVG function is then applied to each group

GROUPS WITHIN GROUPS

Sometimes you need to divide groups into smaller groups. For example, you may want to group all music cds by type of music; then, within each type of music, group them by artist and then by title. Using the DJs on Demand database, the following query groups the CDs by description, then by artist, and then by title.

If you were to buy The Shoo Bugs’ new New Age CD titled "What a Day," where would it appear in the table if you ran the query again?

HAVING

In the same way you used the WHERE clause to restrict the rows that you selected, you can use the HAVING clause to restrict groups. In a query using a GROUP BY and HAVING clause, the rows are first grouped, group functions are applied, and then only those groups matching the HAVING clause are displayed.

Although the HAVING clause can precede the GROUP BY clause in a SELECT statement, it is recommended that you place each clause in order shown. The ORDER BY clause is always last!

SELECT column, group_function

FROM table

WHERE

GROUP BY

HAVING

ORDER BY

NESTING GROUP FUNCTIONS

Group functions can be nested to a depth of two.

SELECT max(avg(salary))

FROM employees

GROUP by department_id;

SUBQUERIES

What Will I Learn?

In this lesson, you will learn to:

Define and explain the function of subqueries for retrieving data

Construct and execute a single-row subquery in the WHERE clause

Provide evidence to support the assigned topic: what types of questions can be answered using a subquery that can't be answered by running multiple queries?

Develop skills for locating, evaluating, and interpreting IT career information

Identify professional organizations in information-technology fields

Why Learn It?

Has a friend asked you to go to a movie, but before you could answer "yes" or "no," you first had to check with your parents? Have you wanted to buy someone a birthday present, but first needed to know what the person wanted? Has someone asked you the answer to a math problem, but before you can give the answer, you had to do the problem yourself? Asking parents, finding what someone wants, or doing the math problem are examples of subqueries. In SQL, subqueries enable us to find the information we need so we can get the information we want. In this lesson, you will learn to identify when a subquery can help solve a problem and how to write subqueries that return values needed in a main query.

Tell Me/Show Me

Throughout this course, you have written queries to extract data from a database. What if you wanted to write a query only to find out you didn't have all the information you needed to construct it? You can solve this problem by combining two queries, placing one query inside the other query. The inner query is called the "subquery." The subquery executes to find the information you don’t know. The outer query uses that information to find out what you need to know. Being able to combine two queries into one can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement.

Subqueries can be placed in a number of SQL clauses, including the WHERE clause, the HAVING clause, and the FROM clause.

Later in this course, you will learn how to use subqueries in the CREATE VIEW statement, CREATE TABLE statement, UPDATE statement, INTO clause of an INSERT statement, and SET clause of an UPDATE statement.

Guidelines for using subqueries are:

- The subquery is enclosed in parentheses.

- The subquery is placed on the right side of the comparison condition.

- The outer and inner queries can get data from different tables.

- Only one ORDER BY clause can be used for a SELECT statement; and, if specified, it must be the last clause in the main SELECT statement.

- The only limit on the number of subqueries is the buffer size the query uses.

There are two types of subqueries:

- Single-row subqueries that use single-row operators (>, =, >=, < <>, <=) and return only one row from the inner query

- Multiple-row subqueries that use multiple-row operators (IN, ANY, ALL) and return more than one row from the inner query

In this lesson, you will learn to construct single-row subqueries. Multiple-row subqueries will be covered in the next section.

What if you wanted to find out the names of the Global Fast Foods staff members that were born after Monique Tuttle? What is the first thing you need to know? When was Monique born? Once you know her birth date, then you can select those staff members whose birth dates are after hers.

What do you think would happen if the subquery returned a null value or no rows?

If single-row subqueries can return only one row to the outer query, would adding a GROUP BY clause in the subquery make sense?

"IT careers -- they're not just in computer companies anymore!"

Information technology has been defined as "all matters concerned with the furtherance of computer science and technology and with the design, development, installation, and implementation of information systems and applications." Often when someone uses the terms "IT" or "information technology," we immediately think of people employed by major software companies or systems developers.

The reality is that nine out of ten IT workers are found in banks, insurance companies, manufacturing plants or other non-IT business. They are found in places such as the automotive industry, health-care industry, banking, social services, airlines, accounting firms, police work, and law offices. (Source: ITAA 2003 IT Workforce Survey)

In this lesson, you will have an opportunity to explore different types of IT jobs and to identify national and state organizations for IT professionals.

SINGLE-ROW SUBQUERIES

What Will I Learn?

In this lesson, you will learn to:

Construct and execute a single-row subquery in the WHERE clause or HAVING clause

Construct and execute a SELECT statement using more than one subquery

Construct and execute a SELECT statement using a group function in the subquery

Why Learn It?

As you have probably realized, subqueries are a lot like Internet search engines. They are great at locating the information needed to accomplish another task. In this lesson, you will learn how to create even more complicated tasks for subqueries to do for you. Keep in mind that subqueries save time in that you can accomplish two tasks in one statement.

Tell Me/Show Me

Remember these facts about single-row subqueries.

They:

- Return only one row

- Use single-row comparison operators (=, >,>=, <, <=, <>)

Always:

- Enclose the subquery in parentheses.

- Place the subquery on the right side of the comparison condition.

Also remember that:

- The outer and inner queries can get data from different tables.

- Only one ORDER BY clause can be used for a SELECT statement, and if specified, it must be the last clause in the main SELECT statement.

- The only limit on the number of subqueries is the buffer size that the query uses.

SUBQUERIES FROM DIFFERENT TABLES

Subqueries are not limited to just one inner query. As the example illustrates, there can be more than one subquery returning information to the outer query. The outer and inner queries can get data from different tables.

SELECT last_name, job_id, salary, department_id

FROM employees

WHERE job_id =

(SELECT job_id

FROM employees

WHERE employee_id = 141)

AND department_id =

(SELECT department_id

FROM departments

WHERE location_id =1500);

GROUP FUNCTIONS IN SUBQUERIES

Group functions can be used in subqueries because they return a single row. The subquery is in parentheses and is placed after the comparison condition.

GROUP FUNCTIONS IN THE HAVING CLAUSE

Subqueries can also be placed in the HAVING clause. As you may recall, the HAVING clause is similar to the WHERE clause, except the HAVING clause is used to restrict groups and always has a group condition (such as MIN, MAX, AVG) stated.

To understand what is happening, it is sometimes easiest to deconstruct the query starting from the inner query (subquery) and working up to the outer query. In this example, the subquery selects and returns the lowest salary in department 50. The outer query uses this value to select the department ID and salaries of all the employees whose minimum salary is greater than that number. Remember the department ID had to be placed in a GROUP BY clause because it is not part of the aggregate function MIN. The HAVING clause eliminated those departments whose MIN salary was less than department 50.

2009-02-09T01:04:00.001-08:00

Oracle/PLSQL Topics

Oracle is a relational database technology.

PLSQL stands for "Procedural Language extensions to SQL", and can be used in Oracle databases. PLSQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL.

We've categorized Oracle and PLSQL into the following topics:

Data Types	SELECT Statement
Literals (Constants)	DISTINCT
Declaring Variables	COUNT / SUM / MIN / MAX
Is Null / Is Not Null
	WHERE Clause
Loops and Conditional Statements	"AND" Condition
Sequences (Autonumber)	"OR" Condition
Transactions	Combining "AND" with "OR"
Cursors
Functions (Built-In) (By Category)	"LIKE" Condition
Functions (Built-In) (Alphabetical)	"IN" Function
Oracle System Tables	BETWEEN Condition
	EXISTS Condition
Primary Keys	GROUP BY
Foreign Keys	HAVING
Unique Constraints	ORDER BY (sort by)
Check Constraints
Indexes	JOINS (inner, outer)
	Subqueries
Creating Functions
Creating Procedures	UNION Query
Creating Triggers	UNION ALL Query
Exception Handling	INTERSECT Query
Oracle Error Messages	MINUS Query

Grant/Revoke Privileges	UPDATE Statement
Roles (set of privileges)	INSERT Statement
Change Password	DELETE Statement

Synonyms (create, drop)	Tables (create, alter, drop, temp)
	Views
Question & Answer

SQL: SELECT Statement

The SELECT statement allows you to retrieve records from one or more tables in your database.

The syntax for the SELECT statement is:

SELECT columns
FROM tables
WHERE predicates;

Example #1

Let's take a look at how to select all fields from a table.

SELECT *
FROM supplier
WHERE city = 'Newark';

In our example, we've used * to signify that we wish to view all fields from the supplier table where the supplier resides in Newark.

Example #2

You can also choose to select individual fields as opposed to all fields in the table.

For example:

SELECT name, city, state
FROM supplier
WHERE supplier_id > 1000;

This select statement would return all name, city, and state values from the supplier table where the supplier_id value is greater than 1000.

Example #3

You can also use the select statement to retrieve fields from multiple tables.

SELECT orders.order_id, supplier.name
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id;

The result set would display the order_id and suppier name fields where the supplier_id value existed in both the supplier and orders table.

SQL: DISTINCT Clause

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax for the DISTINCT clause is:

SELECT DISTINCT columns
FROM tables
WHERE predicates;

Example #1

Let's take a look at a very simple example.

SELECT DISTINCT city
FROM supplier;

This SQL statement would return all unique cities from the supplier table.

Example #2

The DISTINCT clause can be used with more than one field.

For example:

SELECT DISTINCT city, state
FROM supplier;

This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

SQL: COUNT Function

The COUNT function returns the number of rows in a query.

The syntax for the COUNT function is:

SELECT COUNT(expression)
FROM tables
WHERE predicates;

Note:

The COUNT function will only count those records in which the field in the brackets is NOT NULL.

For example, if you have the following table called Suppliers:

Supplier_ID	Supplier_Name	State
1	IBM	CA
2	Microsoft
3	NVidia

The result for this query will return 3.

Select COUNT(Supplier_ID) From Suppliers;

While the result for the next query will only return 1, since there is only one row in the Suppliers table where the State field is NOT NULL.

Select COUNT(State) From Suppliers;

Simple Example

For example, you might wish to know how many employees have a salary that is above $25,000 / year.

SELECT COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000;

In this example, we've aliased the count(*) field as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

Example using DISTINCT

You can use the DISTINCT clause within the COUNT function.

For example, the SQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year.

SELECT COUNT(DISTINCT department) as "Unique departments"
FROM employees
WHERE salary > 25000;

Again, the count(DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.

Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the COUNT function.

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

SQL: SUM Function

The SUM function returns the summed value of an expression.

The syntax for the SUM function is:

SELECT SUM(expression )
FROM tables
WHERE predicates;

expression can be a numeric field or formula.

Simple Example

For example, you might wish to know how the combined total salary of all employees whose salary is above $25,000 / year.

SELECT SUM(salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

In this example, we've aliased the sum(salary) field as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.

Example using DISTINCT

You can use the DISTINCT clause within the SUM function. For example, the SQL statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year.

SELECT SUM(DISTINCT salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

If there were two salaries of $30,000/year, only one of these values would be used in the SUM function.

Example using a Formula

The expression contained within the SUM function does not need to be a single field. You could also use a formula. For example, you might want the net income for a business. Net Income is calculated as total income less total expenses.

SELECT SUM(income - expenses) as "Net Income"
FROM gl_transactions;

You might also want to perform a mathematical operation within a SUM function. For example, you might determine total commission as 10% of total sales.

SELECT SUM(sales * 0.10) as "Commission"
FROM order_details;

Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the SUM function.

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

SQL: MIN Function

The MIN function returns the minimum value of an expression.

The syntax for the MIN function is:

SELECT MIN(expression )
FROM tables
WHERE predicates;

Simple Example

For example, you might wish to know the minimum salary of all employees.

SELECT MIN(salary) as "Lowest salary"
FROM employees;

In this example, we've aliased the min(salary) field as "Lowest salary". As a result, "Lowest salary" will display as the field name when the result set is returned.

Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the MIN function.

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the MIN function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

SQL: MAX Function

The MAX function returns the maximum value of an expression.

The syntax for the MAX function is:

SELECT MAX(expression )
FROM tables
WHERE predicates;

Simple Example

For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) as "Highest salary"
FROM employees;

In this example, we've aliased the max(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned.

Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the MAX function.

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the MAX function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Frequently Asked Questions

Question: I'm trying to pull some info out of a table. To simplify, let's say the table (report_history) has 4 columns:

user_name, report_job_id, report_name, report_run_date.

Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last.

My initial query:

SELECT report_name, max(report_run_date)
FROM report_history
GROUP BY report_name

runs fine. However, it does not provide the name of the user who ran the report.

Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I don't want that....I just want to know who ran a particular report the last time it was run.

Any suggestions?

Answer: This is where things get a bit complicated. The SQL statement below will return the results that you want:

SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

Let's take a few moments to explain what we've done.

First, we've aliased the first instance of the report_history table as rh.

Second, we've included two components in our FROM clause. The first is the table called report_history (aliased as rh). The second is a select statement:

(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults

We've aliased the max(report_run_date) as maxdate and we've aliased the entire result set as maxresults.

Now, that we've created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table. So we've joined the report_name and report_run_date fields between the tables called rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name.

Question: I need help in an SQL query. I have a table in Oracle called orders which has the following fields: order_no, customer, and amount.

I need a query that will return the customer who has ordered the highest total amount.

Answer: The following SQL should return the customer with the highest total amount in the orders table.

select query1.* from
(SELECT customer, Sum(orders.amount) AS total_amt
FROM orders
GROUP BY orders.customer) query1,

(select max(query2.total_amt) as highest_amt
from (SELECT customer, Sum(orders.amount) AS total_amt
FROM orders
GROUP BY orders.customer) query2) query3
where query1.total_amt = query3.highest_amt;

This SQL statement will summarize the total orders for each customer and then return the customer with the highest total orders. This syntax is optimized for Oracle and may not work for other database technologies.

Question: I'm trying to retrieve some info from an Oracle database. I've got a table named Scoring with two fields - Name and Score. What I want to get is the highest score from the table and the name of the player.

Answer: The following SQL should work:

SELECT Name, Score
FROM Scoring
WHERE Score = (select Max(Score) from Scoring);

SQL: WHERE Clause

The WHERE clause allows you to filter the results from an SQL statement - select, insert, update, or delete statement.

It is difficult to explain the basic syntax for the WHERE clause, so instead, we'll take a look at some examples.

Example #1

SELECT *
FROM supplier
WHERE supplier_name = 'IBM';

In this first example, we've used the WHERE clause to filter our results from the supplier table. The SQL statement above would return all rows from the supplier table where the supplier_name is IBM. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2

SELECT supplier_id
FROM supplier
WHERE supplier_name = 'IBM'
or supplier_city = 'Newark';

We can define a WHERE clause with multiple conditions. This SQL statement would return all supplier_id values where the supplier_name is IBM or the supplier_city is Newark.

Example #3

SELECT supplier.suppler_name, orders.order_id
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id
and supplier.supplier_city = 'Atlantic City';

We can also use the WHERE clause to join multiple tables together in a single SQL statement. This SQL statement would return all supplier names and order_ids where there is a matching record in the supplier and orders tables based on supplier_id, and where the supplier_city is Atlantic City.

SQL: "AND" Condition

The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax for the AND condition is:

SELECT columns
FROM tables
WHERE column1 = 'value1'
and column2 = 'value2';

The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.

Example #1

The first example that we'll take a look at involves a very simple example using the AND condition.

SELECT *
FROM supplier
WHERE city = 'New York'
and type = 'PC Manufacturer';

This would return all suppliers that reside in New York and are PC Manufacturers. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2

Our next example demonstrates how the AND condition can be used to "join" multiple tables in an SQL statement.

SELECT order.order_id, supplier.supplier_name
FROM supplier, order
WHERE supplier.supplier_id = order.supplier_id
and supplier.supplier_name = 'IBM';

This would return all rows where the supplier_name is IBM. And the supplier and order tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: order.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the supplier and order tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).

SQL: "OR" Condition

The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax for the OR condition is:

SELECT columns
FROM tables
WHERE column1 = 'value1'
or column2 = 'value2';

The OR condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' OR column2 has to equal 'value2'.

Example #1

The first example that we'll take a look at involves a very simple example using the OR condition.

SELECT *
FROM supplier
WHERE city = 'New York'
or city = 'Newark';

This would return all suppliers that reside in either New York or Newark. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2

The next example takes a look at three conditions. If any of these conditions is met, the record will be included in the result set.

For example:

SELECT supplier_id
FROM supplier
WHERE name = 'IBM'
or name = 'Hewlett Packard'
or name = 'Gateway';

This SQL statement would return all supplier_id values where the supplier's name is either IBM, Hewlett Packard or Gateway.

SQL: Combining the "AND" and "OR" Conditions

The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.

When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.

Example #1

The first example that we'll take a look at an example that combines the AND and OR conditions.

SELECT *
FROM supplier
WHERE (city = 'New York' and name = 'IBM')
or (city = 'Newark');

This would return all suppliers that reside in either New York whose name is IBM, all supplies that reside in Newark. The brackets determine what order the AND and OR conditions are evaluated in.

Example #2

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM supplier
WHERE (name = 'IBM')
or (name = 'Hewlett Packard' and city = 'Atlantic City')
or (name = 'Gateway' and status = 'Active' and city = 'Burma');

This SQL statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway and the city is Burma.

SQL: LIKE Condition

The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose from are:

% allows you to match any string of any length (including zero length)

_ allows you to match on a single character

Examples using % wildcard

The first example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.

SELECT * FROM supplier
WHERE supplier_name like 'Hew%';

You can also using the wildcard multiple times within the same string. For example,

SELECT * FROM supplier
WHERE supplier_name like '%bob%';

In this example, we are looking for all suppliers whose name contains the characters 'bob'.

You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,

SELECT * FROM supplier
WHERE supplier_name not like 'T%';

By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.

Examples using _ wildcard

Next, let's explain how the _ wildcard works. Remember that the _ is looking for only one character.

For example,

SELECT * FROM supplier
WHERE supplier_name like 'Sm_th';

This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Here is another example,

SELECT * FROM supplier
WHERE account_number like '12317_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.

Examples using Escape Characters

Next, in Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.

Please note that you can define an escape character as a single character (length of 1) ONLY.

For example,

SELECT * FROM supplier
WHERE supplier_name LIKE '!%' escape '!';

This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.

Here is another more complicated example:

SELECT * FROM supplier
WHERE supplier_name LIKE 'H%!%' escape '!';

This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.

You can also use the Escape character with the _ character. For example,

SELECT * FROM supplier
WHERE supplier_name LIKE 'H%!_' escape '!';

This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.

SQL: "IN" Function

The IN function helps reduce the need to use multiple OR conditions.

The syntax for the IN function is:

SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);

This SQL statement will return the records where column1 is value1, value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1

The following is an SQL statement that uses the IN function:

SELECT *
FROM supplier
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the supplier table would appear in the result set.

It is equivalent to the following statement:

SELECT *
FROM supplier
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the IN function makes the statement easier to read and more efficient.

Example #2

You can also use the IN function with numeric values.

SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);

This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

It is equivalent to the following statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example #3 - "NOT IN"

The IN function can also be combined with the NOT operator.

For example,

SELECT *
FROM supplier
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SQL: BETWEEN Condition

The BETWEEN condition allows you to retrieve values within a range.

The syntax for the BETWEEN condition is:

SELECT columns
FROM tables
WHERE column1 between value1 and value2;

This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1 - Numbers

The following is an SQL statement that uses the BETWEEN function:

SELECT *
FROM suppliers
WHERE supplier_id between 5000 AND 5010;

This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;

Example #2 - Dates

You can also use the BETWEEN function with dates.

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).

It would be equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

Example #3 - NOT BETWEEN

The BETWEEN function can also be combined with the NOT operator.

For example,

SELECT *
FROM suppliers
WHERE supplier_id not between 5000 and 5500;

This would be equivalent to the following SQL:

SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;

In this example, the result set would exclude all supplier_id values between the range of 5000 and 5500 (inclusive).

SQL: EXISTS Condition

The EXISTS condition is considered "to be met" if the subquery returns at least one row.

The syntax for the EXISTS condition is:

SELECT columns
FROM tables
WHERE EXISTS ( subquery );

The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.

Example #1

Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:

SELECT *
FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);

This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.

Example #2 - NOT EXISTS

The EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

Example #3 - DELETE Statement

The following is an example of a delete statement that utilizes the EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS
(select *
from orders
where suppliers.supplier_id = orders.supplier_id);

UPDATE supplier

SET supplier_name = ( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id)

WHERE EXISTS
( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id);

Example #4 - UPDATE Statement

The following is an example of an update statement that utilizes the EXISTS condition:

Example #5 - INSERT Statement

The following is an example of an insert statement that utilizes the EXISTS condition:

INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

SQL: GROUP BY Clause

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

The syntax for the GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;

SQL: HAVING Clause

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

The syntax for the HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with at least 25 employees will be returned.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) <>

SQL: ORDER BY Clause

The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.

The syntax for the ORDER BY clause is:

SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;

The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, the system assumed ascending order.

ASC indicates ascending order. (default)
DESC indicates descending order.

Example #1

SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;

This would return all records sorted by the supplier_city field in ascending order.

Example #2

SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;

This would return all records sorted by the supplier_city field in descending order.

Example #3

You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;

This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.

Example #4

SELECT supplier_city, supplier_state
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;

This would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.

SQL: Joins

A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement.

There are different kinds of joins. Let's take a look at a few examples.

Inner Join (simple join)

Chances are, you've already written an SQL statement that uses an inner join. It is is the most common type of join. Inner joins return all rows from multiple tables where the join condition is met.

For example,

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how inner joins work:

We have a table called suppliers with two fields (supplier_id and supplier_ name).
It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	Nvidia

We have another table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we ran the SQL statement below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and Nvidia from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.

Outer Join

Another type of join is called an outer join. This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met).

For example,

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

The (+) after the orders.supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as in the result set.

The above SQL statement could also be written as follows:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where orders.supplier_id(+) = suppliers.supplier_id

Let's look at some data to explain how outer joins work:

We have a table called suppliers with two fields (supplier_id and name).
It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	Nvidia

We have a second table called orders with three fields (order_id, supplier_id, and order_date).
It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we ran the SQL statement below:

select suppliers.supplier_id, suppliers.supplier_name, orders.order_date
from suppliers, orders
where suppliers.supplier_id = orders.supplier_id(+);

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft
10003	Nvidia

The rows for Microsoft and Nvidia would be included because an outer join was used. However, you will notice that the order_date field for those records contains a value.

Oracle/PLSQL: Subqueries

What is a subquery?

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

select * from all_tables tabs

where tabs.table_name in

(select cols.table_name

from all_tab_columns cols

where cols.column_name = 'SUPPLIER_ID');

Limitations: Oracle allows up to 255 levels of subqueries in the WHERE clause.

FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

select suppliers.name, subquery1.total_amt
from suppliers,
(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;

In this example, we've created a subquery in the FROM clause as follows:

(select supplier_id, Sum(orders.amount) as total_amt
from orders
group by supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

Limitations: Oracle allows an unlimited number of subqueries in the FROM clause.

SELECT clause

A subquery can also be found in the SELECT clause.

For example:

select tbls.owner, tbls.table_name,
(select count(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
and cols.table_name = tbls.table_name) subquery2
from all_tables tbls;

In this example, we've created a subquery in the SELECT clause as follows:

(select count(column_name) as total_columns
from all_tab_columns cols
where cols.owner = tbls.owner
and cols.table_name = tbls.table_name) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.

SQL: UNION Query

The UNION query allows you to combine the result sets of 2 or more "select" queries. It removes duplicate rows between the various "select" statements.

Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION query is:

select field1, field2, . field_n
from tables
UNION
select field1, field2, . field_n
from tables;

Example #1

The following is an example of a UNION query:

select supplier_id
from suppliers
UNION
select supplier_id
from orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear once in your result set. The UNION removes duplicates.

Example #2 - With ORDER BY Clause

The following is a UNION query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

Frequently Asked Questions

Question: I need to compare two dates and return the count of a field based on the date values. For example, I have a date field in a table called last updated date. I have to check if trunc(last_updated_date >= trun(sysdate-13).

Answer: Since you are using the COUNT function which is an aggregate function, we'd recommend using a UNION query. For example, you could try the following:

SELECT a.code as Code, a.name as Name, count(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
and a.status = 1
and b.status = 1
and b.Ncode <> 'a10'
and trunc(last_updated_date) <= trunc(sysdate-13)
group by a.code, a.name
UNION
SELECT a.code as Code, a.name as Name, count(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
and a.status = 1
and b.status = 1
and b.Ncode <> 'a10'
and trunc(last_updated_date) > trunc(sysdate-13)
group by a.code, a.name;

The UNION query allows you to perform a COUNT based on one set of criteria.

trunc(last_updated_date) <= trunc(sysdate-13)

As well as perform a COUNT based on another set of criteria.

trunc(last_updated_date) > trunc(sysdate-13)

SQL: UNION ALL Query

The UNION ALL query allows you to combine the result sets of 2 or more "select" queries. It returns all rows (even if the row exists in more than one of the "select" statements).

Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.

The syntax for a UNION ALL query is:

select field1, field2, . field_n
from tables
UNION ALL
select field1, field2, . field_n
from tables;

Example #1

The following is an example of a UNION ALL query:

select supplier_id
from suppliers
UNION ALL
select supplier_id
from orders;

If a supplier_id appeared in both the suppliers and orders table, it would appear multiple times in your result set. The UNION ALL does not remove duplicates.

Example #2 - With ORDER BY Clause

The following is a UNION query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION ALL
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

The supplier_name / company_name fields are in position #2 in the result set.

SQL: INTERSECT Query

The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.

The syntax for an INTERSECT query is:

select field1, field2, . field_n
from tables
INTERSECT
select field1, field2, . field_n
from tables;

Example #1

The following is an example of an INTERSECT query:

select supplier_id
from suppliers
INTERSECT
select supplier_id
from orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

Example #2 - With ORDER BY Clause

The following is an INTERSECT query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
INTERSECT
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

The supplier_name / company_name fields are in position #2 in the result set.

SQL: MINUS Query

The MINUS query returns all rows in the first query that are not returned in the second query.

Each SQL statement within the MINUS query must have the same number of fields in the result sets with similar data types.

The syntax for an MINUS query is:

select field1, field2, . field_n
from tables
MINUS
select field1, field2, . field_n
from tables;

Example #1

The following is an example of an MINUS query:

select supplier_id
from suppliers
MINUS
select supplier_id
from orders;

In this example, the SQL would return all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.

Example #2 - With ORDER BY Clause

The following is an MINUS query that uses an ORDER BY clause:

select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
MINUS
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

The supplier_name / company_name fields are in position #2 in the result set.

SQL: UPDATE Statement

The UPDATE statement allows you to update a single record or multiple records in a table.

The syntax the UPDATE statement is:

UPDATE table
SET column = expression
WHERE predicates;

Example #1 - Simple example

Let's take a look at a very simple example.

UPDATE supplier
SET name = 'HP'
WHERE name = 'IBM';

This statement would update all supplier names in the supplier table from IBM to HP.

Example #2 - More complex example

You can also perform more complicated updates.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.

For example:

UPDATE supplier

SET supplier_name = ( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id)

WHERE EXISTS
( SELECT customer.name
FROM customers
WHERE customers.customer_id = supplier.supplier_id);

Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.

SQL: INSERT Statement

The INSERT statement allows you to insert a single record or multiple records into a table.

The syntax for the INSERT statement is:

INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);

Example #1 - Simple example

Let's take a look at a very simple example.

INSERT INTO supplier
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');

This would result in one record being inserted into the supplier table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.

Example #2 - More complex example

You can also perform more complicated inserts using sub-selects.

For example:

INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a "select" in the insert statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement before performing the insert.

SELECT count(*)
FROM customers
WHERE city = 'Newark';

Frequently Asked Questions

Question: I am setting up a database with clients. I know that you use the "insert" statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

SQL: DELETE Statement

The DELETE statement allows you to delete a single record or multiple records from a table.

The syntax for the DELETE statement is:

DELETE FROM table
WHERE predicates;

Example #1 - Simple example

Let's take a look at a simple example:

DELETE FROM supplier
WHERE supplier_name = 'IBM';

This would delete all records from the supplier table where the supplier_name is IBM.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL statement before performing the delete.

SELECT count(*)
FROM supplier
WHERE supplier_name = 'IBM';

Example #2 - More complex example

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.

For example:

DELETE FROM supplier
WHERE EXISTS
( select customer.name
from customer
where customer.customer_id = supplier.supplier_id
and customer.customer_name = 'IBM' );

This would delete all records in the supplier table where there is a record in the customer table whose name is IBM, and the customer_id is the same as the supplier_id.

Learn more about the EXISTS condition.

If you wish to determine the number of rows that will be deleted, you can run the following SQL statement before performing the delete.

SELECT count(*) FROM supplier
WHERE EXISTS
( select customer.name
from customer
where customer.customer_id = supplier.supplier_id
and customer.customer_name = 'IBM' );

Frequently Asked Questions

Question: How would I write an SQL statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

Answer: You could try something like this:

DELETE FROM TableA
WHERE NOT EXISTS
( select *
from TableB
where TableA .field1 = TableB.fieldx
and TableA .field2 = TableB.fieldz );

SQL Topics: Tables

CREATE Table

CREATE Table from another table

ALTER Table

DROP Table

Global Temporary tables

Local Temporary tables

SQL: CREATE Table

The basic syntax for a CREATE TABLE is:

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
);

Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

For example:

CREATE TABLE supplier

( supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50)

)

SQL: CREATE Table from another table

You can also create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax #1 - Copying all columns from another table

The basic syntax is:

CREATE TABLE new_table
AS (SELECT * FROM old_table);

For example:

CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);

This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The basic syntax is:

CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n FROM old_table);

For example:

CREATE TABLE suppliers
AS (SELECT id, address, city, state, zip
FROM companies
WHERE id > 1000);

This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.

Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The basic syntax is:

CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);

For example:

CREATE TABLE suppliers
AS (SELECT companies.id, companies.address, categories.cat_type
FROM companies, categories
WHERE companies.id = categories.id
AND companies.id > 1000);

This would create a new table called suppliers based on columns from both the companies and categories tables.

Acknowledgements: We'd like to thank Dave M. for contributing to this solution!

SQL: ALTER Table

The ALTER TABLE command allows you to add, modify, or drop a column from an existing table.

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name

ADD ( column_1 column-definition,

column_2 column-definition,

...

column_n column_definition );

For example:

ALTER TABLE supplier

ADD (supplier_name varchar2(50),

city varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY column_name column_type;

For example:

ALTER TABLE supplier
MODIFY supplier_name varchar2(100) not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name

MODIFY (column_1 column_type,

column_2 column_type,

...

column_n column_type );

For example:

ALTER TABLE supplier

MODIFY (supplier_name varchar2(100) not null,

city varchar2(75));

This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
DROP COLUMN column_name;

For example:

ALTER TABLE supplier
DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1

Starting in Oracle 9i Release 2, you can now rename a column.

To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

Acknowledgements: Thanks to Dave M., Craig A., and Susan W. for contributing to this solution!

SQL: DROP Table

The basic syntax for a DROP TABLE is:

DROP TABLE table_name;

For example:

DROP TABLE supplier;

This would drop table called supplier.

SQL: Global Temporary tables

Global temporary tables are distinct within SQL sessions.

The basic syntax is:

CREATE GLOBAL TEMPORARY TABLE table_name ( ...);

For example:

CREATE GLOBAL TEMPORARY TABLE supplier

( supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50)

)

This would create a global temporary table called supplier .

SQL: Local Temporary tables

Local temporary tables are distinct within modules and embedded SQL programs within SQL sessions.

The basic syntax is:

DECLARE LOCAL TEMPORARY TABLE table_name ( ...);

SQL: VIEWS

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.

Creating a VIEW

The syntax for creating a VIEW is:

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;

For example:

CREATE VIEW sup_orders AS
SELECT supplier.supplier_id, orders.quantity, orders.price
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id
and supplier.supplier_name = 'IBM';

This would create a virtual table based on the result set of the select statement. You can now query the view as follows:

SELECT *
FROM sup_orders;

Updating a VIEW

You can update a VIEW without dropping it by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;

For example:

CREATE or REPLACE VIEW sup_orders AS
SELECT supplier.supplier_id, orders.quantity, orders.price
FROM supplier, orders
WHERE supplier.supplier_id = orders.supplier_id
and supplier.supplier_name = 'Microsoft';

Dropping a VIEW

The syntax for dropping a VIEW is:

DROP VIEW view_name;

For example:

DROP VIEW sup_orders;

Frequently Asked Questions

Question: Can you update the data in a view?

Answer: A view is created by joining one or more tables. When you update record(s) in a view, it updates the records in the underlying tables that make up the view.

So, yes, you can update the data in a view providing you have the proper privileges to the underlying tables.

Oracle/PLSQL: Data Types

The following is a list of datatypes available in Oracle and PLSQL. We've tried to differentiate between datatypes available in Oracle 8i versus Oracle 9i.

Data Type Syntax	Oracle 8i	Oracle 9i	Explanation (if applicable)
dec(p, s)	The maximum precision is 38 digits.	The maximum precision is 38 digits.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p, s)	The maximum precision is 38 digits.	The maximum precision is 38 digits.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
double precision
float
int
integer
numeric(p, s)	The maximum precision is 38 digits.	The maximum precision is 38 digits.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
number(p, s)	The maximum precision is 38 digits.	The maximum precision is 38 digits.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
real
smallint
char (size)	Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 8i.	Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 9i.	Where *size* is the number of characters to store. Fixed-length strings. Space padded.
varchar2 (size)	Up to 32767 bytes in PLSQL. Up to 4000 bytes in Oracle 8i.	Up to 32767 bytes in PLSQL. Up to 4000 bytes in Oracle 9i.	Where *size* is the number of characters to store. Variable-length strings.
long	Up to 2 gigabytes.	Up to 2 gigabytes.	Variable-length strings. (backward compatible)
raw	Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 8i.	Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 9i.	Variable-length binary strings
long raw	Up to 2 gigabytes.	Up to 2 gigabytes.	Variable-length binary strings. (backward compatible)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	Not supported in Oracle 8i.	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	Not supported in Oracle 8i.	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	Not supported in Oracle 8i.	*fractional seconds precision* must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	Not supported in Oracle 8i.	*year precision* must be a number between 0 and 9. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	Not supported in Oracle 8i.	*day precision* must be a number between 0 and 9. (default is 2) *fractional seconds precision* must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid [size]	Up to 2000 bytes.	Up to 2000 bytes.	Universal rowid. Where *size* is optional.
boolean	Valid in PLSQL, but this datatype does not exist in Oracle 8i.	Valid in PLSQL, but this datatype does not exist in Oracle 9i.
nchar (size)	Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 8i	Up to 32767 bytes in PLSQL. Up to 2000 bytes in Oracle 9i.	Where *size* is the number of characters to store. Fixed-length NLS string
nvarchar2 (size)	Up to 32767 bytes in PLSQL. Up to 4000 bytes in Oracle 8i.	Up to 32767 bytes in PLSQL. Up to 4000 bytes in Oracle 9i.	Where *size* is the number of characters to store. Variable-length NLS string
bfile	Up to 4 gigabytes.	Up to 4 gigabytes.	File locators that point to a read-only binary object outside of the database
blob	U p to 4 gigabytes.	Up to 4 gigabytes.	LOB locators that point to a large binary object within the database
clob	Up to 4 gigabytes.	Up to 4 gigabytes.	LOB locators that point to a large character object within the database
nclob	Up to 4 gigabytes.	Up to 4 gigabytes.	LOB locators that point to a large NLS character object within the database

Oracle/PLSQL: Literals

A literal is the same as a constant. We'll cover three types of literals - text literals, integer literals, and number literals

Text literals are always surrounded by single quotes ('). For example:

'Hewlett Packard'
'28-MAY-03'

Integer literals can be up to 38 digits. Integer literals can be either positive numbers or negative numbers. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid integer literals:

23
+23
-23

Number literals can be up to 38 digits. Number literals can be either positive or negative numbers. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid number literals:

25
+25
-25
25e-04
25.607

Oracle/PLSQL: Declaring Variables

The syntax for declaring variables is:

variable_name datatype [CONSTANT] [NOT NULL] [:= | DEFAULT initial_value]

For example:

Declaring a variable:

LDescription varchar2(40);

Declaring a constant:

LTotal constant numeric(8,1) := 8363934.1;

Declaring a variable with an initial value (not a constant):

LType varchar2(10) := 'Example';

Oracle/PLSQL: IS NULL

In other languages, a null value is found using the = null syntax. However in PLSQL to check if a value is null, you must use the "IS NULL" syntax.

To check for equality on a null value, you must use "IS NULL".

For example,

IF Lvalue IS NULL then

END IF;

If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.

You can also use "IS NULL" in an SQL statement. For example:

select * from suppliers
where supplier_name IS NULL;

This will return all records from the suppliers table where the supplier_name contains a null value.

To learn how to check for a value that is not null, click here.

Oracle/PLSQL: IS NOT NULL

In other languages, a not null value is found using the != null syntax. However in PLSQL to check if a value is not null, you must use the "IS NOT NULL" syntax.

For example,

IF Lvalue IS NOT NULL then

END IF;

If Lvalue does not contain a null value, the "IF" expression will evaluate to TRUE.

You can also use "IS NOT NULL" in an SQL statement. For example:

select * from suppliers
where supplier_name IS NOT NULL;

This will return all records from the suppliers table where the supplier_name does not contain a null value.

To learn how to check for a value that is null, click here.

https://iacademy.oracle.com

2009-02-08T18:08:00.000-08:00

Below are your Application Express accounts for SQL:

School name: PH_CC771A

School Administrator Name: PH_CC771A_ADMIN

Class: SQL01
Users
--------------

PH_CC771A_SQL01_S50
(student)

To login, go to https://iacademy.oracle.com and fill in the following:

School: PH_CC771A
Your Username: teacher/admin/student username from above (example:
PH_CC771A_SQL01_S01)
Password: oracle

Oracle Accounts

2009-02-08T17:59:00.000-08:00

Account name	PH_CC771A_SQL01_S01
	PH_CC771A_SQL01_S02
	PH_CC771A_SQL01_S03
	PH_CC771A_SQL01_S04
	PH_CC771A_SQL01_S05
	PH_CC771A_SQL01_S06
	PH_CC771A_SQL01_S07
	PH_CC771A_SQL01_S08
	PH_CC771A_SQL01_S09
	PH_CC771A_SQL01_S10
	PH_CC771A_SQL01_S11
	PH_CC771A_SQL01_S12
	PH_CC771A_SQL01_S13
	PH_CC771A_SQL01_S14
	PH_CC771A_SQL01_S15
	PH_CC771A_SQL01_S16
	PH_CC771A_SQL01_S17
	PH_CC771A_SQL01_S18
	PH_CC771A_SQL01_S19
	PH_CC771A_SQL01_S20
	PH_CC771A_SQL01_S21
	PH_CC771A_SQL01_S22
	PH_CC771A_SQL01_S23
	PH_CC771A_SQL01_S24
	PH_CC771A_SQL01_S25
	PH_CC771A_SQL01_S26
	PH_CC771A_SQL01_S27
	PH_CC771A_SQL01_S28
	PH_CC771A_SQL01_S29
	PH_CC771A_SQL01_S30
	PH_CC771A_SQL01_S31
	PH_CC771A_SQL01_S32
	PH_CC771A_SQL01_S33
	PH_CC771A_SQL01_S34
	PH_CC771A_SQL01_S35
	PH_CC771A_SQL01_S36
	PH_CC771A_SQL01_S37
	PH_CC771A_SQL01_S38
	PH_CC771A_SQL01_S39
	PH_CC771A_SQL01_S40
	PH_CC771A_SQL01_S41
	PH_CC771A_SQL01_S42
	PH_CC771A_SQL01_S43
	PH_CC771A_SQL01_S44
	PH_CC771A_SQL01_S45
	PH_CC771A_SQL01_S46
	PH_CC771A_SQL01_S47
	PH_CC771A_SQL01_S48
	PH_CC771A_SQL01_S49
	PH_CC771A_SQL01_S50