Thursday, 11 October 2012
Describe Various Types of Functions Available in SQL
SQL functions are broadly divided into those that calculate and return a value for
every row in a data set and those that return a single aggregated value for all rows.
The following two areas are explored:
■ Defining a function
■ Types of functions
every row in a data set and those that return a single aggregated value for all rows.
The following two areas are explored:
■ Defining a function
■ Types of functions
Null Comparison with the IS NULL Operator
NULL values inevitably find their way into database tables. It is often required that
only those records that contain a NULL value in a specific column are sought. The
IS NULL operator selects only the rows where a specific column value is NULL.
Testing column values for equality to NULL is performed using the IS NULL
operator instead of the “is equal to” operator (=).
Consider the following query that fetches the LAST_NAME column from
the EMPLOYEES table for those rows which have NULL values stored in the
COMMISSION_PCT column:
select last_name from employees
where commission_pct is null;
This WHERE clause reads naturally and retrieves only the records which contain
NULL COMMISSION_PCT values. As Figure 3-13 shows, the query using the “is equal
to” operator does not return any rows, while the query using the IS NULL operator does.
only those records that contain a NULL value in a specific column are sought. The
IS NULL operator selects only the rows where a specific column value is NULL.
Testing column values for equality to NULL is performed using the IS NULL
operator instead of the “is equal to” operator (=).
Consider the following query that fetches the LAST_NAME column from
the EMPLOYEES table for those rows which have NULL values stored in the
COMMISSION_PCT column:
select last_name from employees
where commission_pct is null;
This WHERE clause reads naturally and retrieves only the records which contain
NULL COMMISSION_PCT values. As Figure 3-13 shows, the query using the “is equal
to” operator does not return any rows, while the query using the IS NULL operator does.
Use Of LIKE Operator In Oracle
Retrieve a list of DEPARTMENT_NAME values that end with the three letters
“ing” from the DEPARTMENTS table.
1. Start SQL*Plus and connect to the HR schema.
2. The SELECT clause is
SELECT DEPARTMENT_NAME
Limit the Rows Retrieved by a Query 125
3. The FROM clause is
FROM DEPARTMENTS
4. The WHERE clause must perform a comparison between the DEPARTMENT_
NAME column values and a pattern of characters beginning with zero or more
characters but ending with three specific characters, “ing.”
5. The operator enabling character pattern matching is the LIKE operator. The
pattern the DEPARTMENT_NAME column must conform to is '%ing'.
The percentage wildcard symbol indicates that zero or more characters may
precede the “ing” string of characters.
6. Thus, the WHERE clause is
WHERE DEPARTMENT_NAME LIKE '%ing'
7. Executing this statement returns the set of results matching this pattern as
shown in the following illustration:
Friday, 5 October 2012
SQL Commands
These are the 16 SQL commands, separated into commonly used groups:
The Data Manipulation Language (DML) commands:
■ SELECT■ INSERT
■ UPDATE
■ DELETE
■ MERGE
The Data Definition Language (DDL) commands:
■ CREATE■ ALTER
■ DROP
■ RENAME
■ TRUNCATE
■ COMMENT
The Data Control Language (DCL) commands:
■ GRANTThe Transaction Control Language (TCL) commands:
■ COMMIT■ ROLLBACK
■ SAVEPOINT
The first command, SELECT, is the main subject of Chapters 2 through 9.
The remaining DML commands are covered in Chapter 10, along with the TCL
commands. DDL is detailed in Chapters 11 and 12. DCL, which has to do with
security, is only briefly mentioned: it falls more into the domain of the database
administrator than the developers.
Subscribe to:
Posts (Atom)