Creating Groups: Part 1

Dividing Data into Groups

You might need to divide a table of information into groups to produce meaningful results when using group functions. For example, you might want to find the average salary for each department or the oldest hire in each job category.

GROUP BY Clause

You use the GROUP BY clause to organize rows in a table into groups. You can then use the group functions to return information for each group. For example, you can group data in the EMPLOYEES table by department number and then return the average salary for each department.

Using the Grouping Functions

Help Sheila find the following information:

* Number of rows in the EMPLOYEES table
* Number of employees who earn a commission
* Total salary budget allocation
* Average salary

Group Functions: Part 2

Syntax

To specify a group function, you write the name of the function followed by an argument in parentheses. The argument can be a column name, an expression, or a constant.



COUNT Function

The COUNT function returns the number of non-null values in a column. If you count the values of a primary key column, you will find the number of rows in a table because a primary key column cannot contain nulls.

Group Functions: Part 1

Grouping Functions

Sheila has learned that there are several group functions that are available for use. All are ANSI-standard SQL functions.

* You can find the average of a group of values by using the AVG function.
* You can find the total of a group of values by using the SUM function.
* MAX finds a largest value. MIN returns a smallest value.
* STDDEV returns a standard deviation. VARIANCE returns the statistical variance.
* AVG , SUM , STDDEV , and VARIANCE can be used with numerical data only. You can use MAX and MIN on character, numeric, and date data types.
* You can use the COUNT function to return the number of values in a column.

Sheila will use the SQL GROUP functions in her SELECT statements to:

* Find the number of rows in a table
* Add the salaries
* Find the average, maximum, and minimum salaries
* Analyze salary information by department

Group Functions: Part 1

Grouping Functions

Sheila has learned that there are several group functions that are available for use. All are ANSI-standard SQL functions.

* You can find the average of a group of values by using the AVG function.
* You can find the total of a group of values by using the SUM function.
* MAX finds a largest value. MIN returns a smallest value.
* STDDEV returns a standard deviation. VARIANCE returns the statistical variance.
* AVG , SUM , STDDEV , and VARIANCE can be used with numerical data only. You can use MAX and MIN on character, numeric, and date data types.
* You can use the COUNT function to return the number of values in a column.

Sheila will use the SQL GROUP functions in her SELECT statements to:

* Find the number of rows in a table
* Add the salaries
* Find the average, maximum, and minimum salaries
* Analyze salary information by department

Working with Groups of Data

Grouping Data

You can use group functions in a SQL statement to display information about groups of rows in the database. A group function performs an operation on a set of data.

Group functions operate on sets of rows and return one result per group. Group functions are also called multiple-row functions (in contrast with single-row functions, which return one result for each row).

You can use a group function to operate on a set of data. The set may be an entire table or only part of the table.

In this section, Sheila creates reports that summarize data.

Using the NVL Function

Sheila needs to produce a report to find employees who do not have a manager.

* Display the value of “No Manager” where the manager ID column is null.

Using the NVL Function

The Null Value

An empty field is said to contain NULL. A NULL is defined as a value that is unavailable, unassigned, unknown, or inapplicable. A NULL is not the same as a zero or a space. Zero is a number, and a space is a character. The COMMISSION_PCT column in the EMPLOYEES table contains NULLs.

Columns of any data type can contain NULLs unless the column was defined as NOT NULL when the table was created. Also, primary key columns cannot have NULLs.
NVL Function

You can use the NVL function to convert a NULL to another value.

NVL Syntax

NVL ( expr1, expr2)

* expr1 is the source value or expression.
* expr2 is the target or resulting value to substitute.

Formatting Dates

Sheila wants to format her dates differently. She wants to produce a report that shows the employee's start date in the format of Month + Day + Year: the date 06-OCT-05 should appear as "October 6th, 2005."

For the birthdates in the DEPENDENTS table, she wants only the year to appear.

Formatting Dates

TO_CHAR Function

You can use the TO_CHAR function to customize the display of dates that are returned from a SQL SELECT statement.

The TO_CHAR function has two parameters. The first parameter is the date that you want to convert. The second parameter is the format model, which specifies the way that you want the date to look when displayed.

Many format models are available. A few are shown in the table below:

Using Date Functions

Sheila needs a report that displays the ages of the employee's dependents. She also wants to see how many years an employee has been with the company. Each employee is rewarded with a gift when longevity with the company reaches 10 years.

She needs to use the SYSDATE and MONTHS_BETWEEN functions to determine this information

Date Functions: Part 2

Common Date Functions

Date functions enhance your ability to calculate information about dates. Common DATE functions are listed in the table below. These functions operate on DATE data types and return date, date-time, or number values.



SYSDATE Function

SYSDATE is a special date function that returns the current date and time. You can use the function in any SQL statement.

SYSDATE is a function that contains no arguments. You can use SYSDATE just as you use a column name. The SYSDATE is displayed for every row in the table used.

Date Functions: Part 1

Characteristics

Oracle XE stores dates in an internal format representing the century, year, month, day, hours, minutes, and seconds. For example, if today is October 6, 2005, at 07:10 in the morning, the current date is stored internally as October 6, 2005, 07:10:06.

The default display and input format for any date in XE is DD-MON-YY. DD stands for the day of the month, MON stands for the first three letters of the month name, and YY stands for the year (which also includes the century). Valid Oracle dates are between January 1, 4712 B.C., and December 31, A.D. 9999.

Using Numeric Functions

Sheila wants to determine salaries rounded to the nearest thousandth. She then wants to compare those values to the values of salaries truncated to the thousandth position. She has looked up the syntax for the numeric functions and created a small sheet about them:



Help Sheila find this information.

Number Functions

Characteristics

You use number functions to accept numeric input. The number functions return numeric values.

Function Names

The three most commonly used number functions are the following:

* ROUND: Rounds a column, an expression, or a value to n decimal places
* MOD: Returns the remainder of x divided by y
* TRUNC: Truncates a column, an expression, or a value to n decimal places

Using Character Functions

Sheila needs to add more functionality to her reports. Her employee report needs to display the first name, followed by a space, followed by the last name, all in proper case. In addition, she needs the e-mail data to appear in lowercase.

She has looked up the syntax for functions and created a small sheet on them:

Character Functions: Part 2

Case-Manipulation Functions

You use case-manipulation functions to change the case of character strings. The types of case-manipulation functions are LOWER , UPPER , and INITCAP .

Character-Manipulation Functions

You use character-manipulation functions to manipulate character strings. The types of character-manipulation functions are CONCAT , SUBSTR , INSTR , LPAD , RPAD , TRIM , and REPLACE .

Character Functions: Part 1

Characteristics

Single-row character functions accept character data as input and return either character or numeric values. Character functions that return character values return values of the same data type as their input argument. Character functions that return number values can take any character data type as their input argument.

Types

There are two types of single-row character functions:

* Case-manipulation functions
* Character-manipulation functions

Using Functions to Customize Reports

A SQL function is a program that performs an operation on data. SQL functions provide a powerful way to perform operations when retrieving data from a table. For example, you may need to display employee names in uppercase in a report. Or you may need to display employees' hire dates with the name of the month spelled out.

Many functions work on specific data types. Each value manipulated by Oracle XE has a data type. Data types provide a way to define the behavior of data. When you create a table, you must specify a data type for each of its columns.

The three main types of data that functions work on are:

*
Character
*
Number
*
Date

Joining Multiple Tables

Help Sheila build the following reports:

* A listing of each employee's first name, last name, dependent names, and the name of the department where the employee works
* A listing of each employee and the name of that employee's manager

Joining Multiple Tables

Joining Multiple Tables

A three-way join is a join of three tables. You can join as many tables as needed to retrieve information. For example, you might want to find employees, their depedents' names, and the department names for those emloyees. This requires accessing three tables:- EMPLOYEES, DEPENDENTS, and DEPARTMENTS.

Syntax

In the FROM clause, you identify the tables you want to join.

FROM table1
JOIN table2 ON conditon_x
JOIN table3 ON condition_y

Example

SELECT e.last_name, d.first_name, w.department_name
FROM employees e
JOIN dependents d
ON d.relative_id = e.employee_id
JOIN departments w
ON w.department_id = e.department_id

Self-Joining Tables

The ON clause can also be used to join columns that have different names (in the same table or in a different table). For example, you can perform a self-join of the EMPLOYEES table based on the EMPLOYEE_ID and MANAGER_ID columns.

Applying Additional Conditions to a Join

Help Sheila build a report to find the hire date and start date of all employees who earn more than 10,000.00. She can find the hire date in the EMPLOYEES table and the start date in the JOB_HISTORY table.

Applying Additional Conditions to a Join

Additional Conditions

You can apply additional conditions to the join. For example, you may want to join the EMPLOYEES and DEPARTMENTS tables and, in addition, display only employees who have a manager ID of 149. To add additional conditions to the ON clause, you can add AND clauses. Alternatively, you can use a WHERE clause to apply additional conditions.

Using the AND Clause

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149


Using the WHERE clause

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149

Aliases

In the examples shown, aliases are used to identify the table names. In the FROM clause, an abbreviation is provided after the table name. This is called an alias. After the alias is set up in the FROM clause, you can refer to it throughout the statement.

Joining Tables and Identifying Columns

Help Sheila build a report to find the hire dates and start dates of all employees. She can find the hire dates in the EMPLOYEES table and the start dates in the JOB_HISTORY table.

Joining Tables and Identifying Columns

ON Clause

You use the ON clause to specify the join condition when joining two tables or joining a table to itself. This enables you to separate the join condition from any search or filter conditions in the WHERE clause. The column names need not match between the tables; however, the data types must match.

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table1 JOIN table2
ON col_name_1 = col_name_2

For example, Shelia might need to evaluate the hire dates and start dates of all employees. She can find the hire dates in the EMPLOYEES table and the start dates in the JOB_HISTORY table. These two columns are named differently. The ON clause is as follows:

FROM employees JOIN job_history
ON employees.hire_date = job_history.start_date

Simple Joins

Sheila needs to build a report that displays the city locations of the departments.

Watch Sheila create these reports.

Simple Joins

Natural Joins

A natural join enables you to display data from two tables when a value in one column of one table corresponds directly to a value in another column in the second table.

In a natural join, the two tables include one or more columns that have the same name and data types. A natural join retrieves all rows from the two tables that have equal values in all matched columns. Frequently, this type of join involves primary key and foreign key columns.

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table NATURAL JOIN ;

Joining Two Tables with a USING Clause

The USING clause enables you to specify the columns to be used for a join between two tables. The column names must be the same for both tables and must have compatible data types. Use the USING clause if your tables contain more than one column whose names match to explicitly identify the name of the columns that you want to join.

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table1 JOIN table2
USING common_col_name;

Joining Tables

Sometimes you need to display data from more than one table. To do this, you use SELECT statements. The FROM clause of your query contains the names of the tables from which you are retrieving data. Because information comes from more than one table, this is called a JOIN between the tables involved.

For example, in the EMPLOYEES table, the DEPARTMENT_ID column represents the department number for an employee. In the DEPARTMENTS table, there is a DEPARTMENT_ID column as well as a DEPARTMENT_NAME column. You can join the EMPLOYEES and DEPARTMENTS tables by using the DEPARTMENT_ID column to produce a report that shows the employees' names and department names.

Retrieving Rows

Sheila needs to build reports that display the following information:

* Employees in a specified department
* Employees who work for a specified manager
* Employees who earn less than 8,000.00

Watch Sheila create these reports.

Retrieving Rows

You can use the selection capability of SQL to choose the rows that you want to retrieve from a table. You can specify various criteria to select the rows that you want to see.

You can restrict the number of rows that are retrieved from the database by using a WHERE clause in a SQL statement. By inserting a WHERE clause into a SQL statement, you can specify a condition that must be met, and only the rows that meet the condition are returned.

When using a WHERE clause:

* The WHERE clause directly follows the FROM clause in the SQL statement syntax
* The WHERE clause consists of the WHERE keyword and a condition or conditions.
* The condition in a WHERE clause specifies a comparison of values that limits the rows that are returned by a query

Retrieving Columns

Sheila needs to build the following report:

It is time for benefits review. To gather information on each employee, HR needs a report with the employees' first names, last names, IDs, salaries, and e-mail addresses. The data needs to be sorted by employee salary.

Watch Sheila build this report...

Retrieving Columns

You can use the projection capability of SQL to choose the columns in a table that you want to retrieve. You can retrieve selected columns or all columns from a table.

Writing a Basic Query

Sheila wants to see the data in the HR tables. She would like to view the data in the EMPLOYEES and DEPARTMENTS tables.

Watch Sheila retrieve data from the DEPARTMENTS and EMPLOYEES tables:


Sorting Data

Now that Sheila can see the data, she wants to sort it. She would like to see the data in the EMPLOYEES table sorted by last names.

Writing a Basic Query

Writing SELECT Statements

When Sheila writes a basic query, two mandatory clauses in the SELECT statement syntax are required: a SELECT clause and a FROM clause.

The SELECT statement syntax is displayed. The SELECT clause specifies the columns that you want to display. The FROM clause specifies the tables that contain those columns.

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

Building Queries

Sheila's manager has asked her to write several reports for human resources.

Sheila starts by accessing the interface where she can build her queries. She can either:

* Use the SQL Workshop tool to type her SELECT statements
* Use the Query Builder tool to build the query graphically

Watch Sheila navigate to the SQL Workshop and Query Builder tools.

Building Queries: Part 2

Sheila will use the SQL SELECT statement to extract data from a database. With the SELECT statement, you can choose to see all or some of the data in a table.
SELECT Statement Types

SELECT statements have three capabilities:
* Selection: Identifying rows
* Projection: Identifying columns
* Join: Retrieving data from multiple tables

Building Queries

All operations on information in an Oracle database are performed by using SQL statements. A SQL statement is a string of SQL text. The SQL SELECT statement is the command that retrieves data from the XE tables.

Sheila will use the SQL SELECT statement to:

* Write queries
* Create SELECT statements that restrict rows
* Sort output
* Display data from many tables
* Customize output with functions embedded in the SELECT statement

Accessing Data

In this module, you learn how to retrieve data from tables. The SQL SELECT statement is used to access and report data back from the XE tables. This is known as "querying" the data. In XE, you can either write SELECT statements using the SQL Workshop tool, or you can use the Query Builder tool to build queries with a GUI interface.

You will watch Sheila write several reports for the Human Resources department. Sheila will use both the SQL SELECT statement in the SQL Workshop tool and the Query Builder tool to access data in the Human Resources tables.

Removing a Copy of a Table

Sheila realizes that she does not need copies of the LOCATIONS and EMPLOYEES tables. She decides to remove them.

Watch Sheila remove the EMPLOYEES_COPY table by using the Object Browser.

Removing Tables

You can discard a table if you no longer find it useful. The DROP TABLE statement removes the definition of the table from the database.

Deleting all of the records in a table is different from DROP TABLE. After deleting all the records, the column and constraint information still remains. But DROP TABLE results in the removal of the table definition along with the rows.

In Oracle Database XE, you can remove a table in either of the following ways:

* Select Object Browser > Browse > Tables. Click the table name, and then click DROP.
* Select SQL > SQL commands > Enter command. Type the SQL statement, and then click Run.

Implementing Business Rules

Sheila's manager wants to ensure that data integrity is handled on the new tables and columns that she has created. The manager has asked Sheila to add constraints with the following rules:

* In the DEPENDENTS table, have a primary key constraint on the Id column
* Allow the Gender column to hold the value of M or F
* Tie the DEPENDENTS table to the EMPLOYEES table so that the RelativeId column must hold a valid value from the EMPLOYEES Id column
* Disable the constraint between the DEPENDENTS and EMPLOYEES tables

Watch Sheila manage the constraints on the DEPENDENTS table by using the Object Browser.

Creating Constraints

Constraints can be enforced at two levels:

* Column level
* Table level

A constraint can be created with either of the following statements:

* CREATE TABLE
* ALTER TABLE

With the ALTER TABLE statement, you can disable or enable the imposed constraint without dropping it or re-creating it:

* Disable a constraint by using the DISABLE clause.
* Enable a constraint by using the ENABLE clause.

Identifying Constraints

# PRIMARY KEY

The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces uniqueness of the column or column combination. It ensures that no column that is part of the primary key can contain a null value. A null value is a value that does not exist.


For example, in the DEPENDENTS table, the column ID is the primary key.This column will not allow either a duplicate value or a null value.


# FOREIGN KEY

The FOREIGN KEY constraint designates a column or a combination of columns as a foreign key. It establishes a relationship between a primary key or a unique key in the same table or different table. A foreign key enforces that the value within the column matches the value in the relationship column.

For example, the RelativeId column in the DEPENDENTS table refers to the EMPLOYEES table. You cannot delete a record in the EMPLOYEES table whose RelativeId is used in the DEPENDENTS table. Also, with a non-existing RelativeId in the EMPLOYEES table, you cannot insert a record into the DEPENDENTS table.



# CHECK

The CHECK constraint enforces integrity by restricting the values to be inserted in a column. It defines a condition that each row must sastisfy. You can define multiple check constraints on a single column. Also, you can apply a single check constraint to multiple columns.

For example, when you impose the CHECK constraint on the Gender column in the DEPENDENTS table, you can specify that the only valid values are either M or F.




# UNIQUE

The UNIQUE constraint requires that no two rows of a table can have duplicate values in a specified column or a set of columns. A table can have more than one unique key. If the UNIQUE constraint comprises more than one column, then the group of columns is called a coomposite key.

For example, you can impose the UNIQUE constraint on the Email column of the EMPLOYEES table. You ensure that each employee has a unique email ID.

Managing Constraints

What Are Constraints?

Data integrity ensures the consistency and correctness of data stored in a database. Such integrity can be enforced by incorporating business rules. Constraints are the rules that are enforced on data stored in a table.

Why Should I Use Constraints?

You can use constraints to do the following:

* Enforce rules on the data in a table whenever a row is updated, inserted, or deleted from that table
* Prevent the deletion of a table if there are dependencies from other tables

Adding a New Column

Sheila's company wants to keep a record of employee birthdates. To do this, Sheila needs to add a new column, BirthDate, to the EMPLOYEES table. This column will have the DATE data type.

Watch Sheila add a new column to the EMPLOYEES table by using the Object Browser.

Modifying Tables

You can modify tables using the SQL ALTER TABLE statement. You may need to change the table structure due to any of the following reasons:

* You omitted a column.
* Your column definition needs to be changed.
* You need to remove columns.

The ALTER TABLE statement is used to:

* Add a new column
* Modify an existing column
* Define a default value for the new column
* Drop a column
* Manage constraints

In Oracle Database XE, you can modify tables:

* Using the Object Browser
* Using the SQL Workshop tool

Creating a Copy of a Table

Sheila wants to copy the EMPLOYEES table so that she can practice without affecting the real data. Watch Sheila create a copy of the table by using the Object Browser.


Sheila wants to have a copy the LOCATIONS table. Watch Sheila create a copy of the table by using the appropriate SQL statement.

Creating Tables Using SQL

Sheila needs to create the AUDIT_RECORD_TB1 table. This table will contain two columns. The user_value column is of the data type varchar2, and the date_recorded column is of the data type timestamp. Later, Sheila will use this table to record audit information when the salary column in the EMPLOYEES table changes.

Watch Sheila create the table AUDIT_RECORD_TB1 by using the SQL CREATE statement.

Creating Tables by Using the Object Browser

Sheila needs to create the DEPENDENTS table, which will contain the following columns: Id, FirstName, LastName, BirthDate, Relation, Gender, Benefits, and RelativeId.

In the DEPENDENTS table, no two rows have the same ID. The Gender column holds only one value of M or F. Also, the Benefits column stores large blocks of character data.

Watch Sheila create the DEPENDENTS table using the Object Browser.

Creating Tables

You create tables with the SQL CREATE TABLE statement. With Oracle Database XE, you have two options for creating tables.

* Use the graphical interface that generates the SQL statement
* Enter the CREATE TABLE statement in the SQL Workshop tool

When creating tables, you must provide:

* Table name
* Column name(s)
* Data types for each column

Guidelines for creating tables:

* Table and column naming rules

Must start with a letter, which is followed by a sequence of letters, numbers, _, #, or $

Must be 1 to 30 characters long

Must not be an Oracle server reserved password


* Most common data types

VARCHAR2
NUMBER
DATE
TIMESTAMP
CHAR



You can also set up constraints on your columns to control the data in them.

Working with Database Objects

Oracle Database XE provides an organized mechanism for storing, managing, and retrieving information.Tables are the basic storage structure for holding business data. In this module, you learn how to create tables and work with them.

You may want to modify data entered in tables. You may also want to maintain integrity with the data. Sometimes, you may want to remove tables that are no longer useful.

Now that Sheila has the Oracle Database XE software installed and working, and has familiarized herself with the tables in the HR schema, her next task is to build some tables and database objects. In the demonstrations, you watch Sheila create and modify tables, manage constraints, and remove tables.

Relational data base

What Is a Relational Database?

The concept of a relational database was originally developed back in 1970 by Dr. E.F. Codd. He
laid down the theory of relational databases in his seminal paper entitled “A Relational Model of
Data for Large Shared Data Banks,” published in Communications of the ACM (Association for
Computing Machinery), Vol. 13, No. 6, June 1970.
The basic concepts of a relational database are fairly easy to understand. A relational database
is a collection of related information that has been organized into tables. Each table stores data in
rows; the data is arranged into columns. The tables are stored in database schemas, which are
areas where users may store their own tables. A user may grant permissions to other users so they can access their tables. Most of us are familiar with data being stored in tables

Introducing the Structured Query Language (SQL)

Structured Query Language (SQL) is the standard language designed to access relational
databases. SQL should be pronounced as the letters “S-Q-L.”

NOTE
“S-Q-L” is the correct way to pronounce SQL according to the American National Standards Institute. However, the single word “sequel” is frequently used instead.

SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of
SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as System R, and SQL was born from that project. Later, in 1979, a company then known as Relational Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL. SQL is now fully standardized and recognized by the American National Standards Institute. SQL uses a simple syntax that is easy to learn and use. You’ll see some simple examples of its use in this chapter. There are five types of SQL statements, outlined in the following list:

Query statements retrieve rows stored in database tables. You write a query using the

SQL SELECT statement.
Data Manipulation Language (DML) statements modify the contents of tables. There are
three DML statements:
INSERT adds rows to a table.
UPDATE changes rows.
DELETE removes rows.

Data Definition Language (DDL) statements define the data structures, such as tables,
that make up a database. There are five basic types of DDL statements:

CREATE creates a database structure. For example, CREATE TABLE is used to create
a table; another example is CREATE USER, which is used to create a database user.

ALTER modifies a database structure. For example, ALTER TABLE is used to modify
a table.

TRUNCATE deletes all the rows from a table.