SQL: Usage Information

This topic discusses how to use SQL with Oracle Database. It includes the following topics:


See Also:

  • Oracle Database SQL Language Reference for detailed information about SQL statements and other parts of SQL, such as operators, functions, and format models

  • Oracle Database Concepts for conceptual information on SQL

  • SQL*Plus User's Guide and Reference for information about SQL*Plus, Oracle's version of SQL

  • Oracle Database Sample Schemas for information on the HR sample schema that is used for examples


Overview of SQL

Structured Query Language (SQL) is a database access, nonprocedural language. Users issue SQL statements or commands to perform various tasks, such as retrieving data, and the SQL language compiler automatically handles how to navigate the database and perform the desired task. All database operations are performed using SQL.

This topic includes the following topics:

Features of SQL

With SQL statements you can perform the following:

  • Query, insert, update, and delete data in tables

  • Format, perform calculations on, store, and print from query results

  • Examine table and object definitions

  • Develop and run batch scripts

  • Perform database administration

Types of SQL Statements

All operations performed on the information in an Oracle database are run using SQL statements. A statement consists partially of SQL reserved words, which have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

A SQL statement is an instruction. The statement must be the equivalent of a complete SQL sentence, as in:

SELECT last_name, department_id FROM employees;

Oracle SQL statements are divided into several categories:

  • Data Manipulation Language (DML) Statements

    These statements query, insert, update, delete data in tables.

  • Transaction Control Statements

    These statements commit or rollback the processing of transactions. A group of changes that you make is referred to as a transaction.

  • Data Definition Language (DDL) Statements

    These create, alter, and drop database objects.

Retrieving Data With Queries

You can retrieve data from rows stored one or more database tables or views with a query using the SQL SELECT statement. The SELECT statement retrieves the all of or part of the column data from rows depending on the conditions that you specify in WHERE clauses. The group of columns that are selected from a table is referred to as the SELECT list.

Displaying Data Using the SELECT Statement

With the SQL SELECT statement, you can query and display data of tables in a database.

Example: Using SELECT to Retrieve Data shows how to use SELECT to retrieve data from the employees table. In this example, the data for all columns in a row (record) of the employees table is retrieved with the use of the wildcard (*) notation. Note the use of comments to document the SQL statements. The comments (or remarks) in this example begin with --.

Using SELECT to Retrieve Data

-- the following uses the wildcard * to retrieve all the columns of data in
-- all rows of the employees table
SELECT * FROM employees;

Example: Using SELECT to Retrieve Data From Specific Columns shows how to use SELECT to retrieve the data for specific columns of the employees table. In this example, you explicitly enter the column names in the SELECT statement.

Using SELECT to Retrieve Data From Specific Columns

-- the following retrieves the data in columns employee_id, last_name, first_name
SELECT employee_id, last_name, first_name FROM employees;

See Also:

Oracle Database SQL Language Reference for detailed information on the SQL SELECT statement

Using Character Literals in SQL Statements

Many SQL statements, functions, expressions, and conditions require you to specify character literal values. You can specify character literals with the following notations:

  • Character literals with the 'text' notation, as in the literals 'users01.dbf' and 'Muthu''s computer'.

  • National character literals with the N'text' or n'text' notation, where N or n specifies the literal using the national character set. For example, N'résumé' is a National character literal.

For information on unicode literals, see Unicode String Literals.

Quoting Character Literals

By default you must quote character literals in single-quotes, as in 'Hello'. This technique can sometimes be inconvenient if the text itself contains single quotes. In such cases, you can also use the Q-quote mechanism, which enables you to specify q or Q followed by a single quote and then another character to be used as the quote delimiter. For example, the literal q'#it's the "final" deadline#' uses the pound sign (#) as a quote delimiter for the string it's the "final" deadline.

The Q-quote delimiter can be any single- or multibyte character except space, tab, and return. If the opening quote delimiter is a [, {, <, or ( character, then the closing quote delimiter must be the corresponding ], }, >, or ) character. In all other cases, the opening and closing delimiter must be the identical character.

The following character literals use the alternative quoting mechanism:


q'(name LIKE '%DBMS_%%')'
q'<'Data,' he said, 'Make it so.'>'
q'"name like '['"'
nq'ïŸ1234ï'

See Also:

  • Oracle Database Globalization Support Guide to learn about national character sets

  • Oracle Database SQL Language Reference to learn about character literals


Using a Column Alias to Change Headings When Selecting Data

When displaying the result of a query, SQL normally uses the name of the selected column as the column heading. You can change a column heading by using a column alias to make the heading more descriptive and easier to understand.

You can specify the alias after the column name in the SELECT list using a space as a separator. If the alias contains spaces or special characters (such as # or $), or if it is case-sensitive, enclose the alias in double quotation marks (" ").

Example: Using a Column Alias shows the use of a column alias to provide more description for each heading of the columns selected in a query.

Using a Column Alias

-- the following retrieves the data in columns employee_id, last_name, first_name
-- and provides column aliases for more descriptive headings of the columns
SELECT employee_id "Employee Id number", last_name "Employee last name", 
  first_name "Employee first name" FROM employees;

Restricting Data Using the WHERE Clause

The WHERE clause uses comparison operators to identify specific rows in a table. When used with the SELECT statement, you can selectively retrieve rows from a table rather than retrieving all rows of a table.

Comparison operators include those listed in Table: Comparison Operators.

Comparison Operators

Operator Definition

=, !=, <>

test for equal, not equal, not equal

>, >=, <, <=

test greater than, greater than or equal to, less than, less than or equal to

BETWEEN ... AND ...

check for range between and including two values

LIKE

search for a match in string, using the wildcard symbols % (zero or multiple characters) or _ (one character)

IN ( )

test for a match in a specified list of values

IS NULL, IS NOT NULL

check whether is null (no value), is not null


Example: Using SELECT With a WHERE Clause shows how to use SELECT with a WHERE clause and several comparison operators to retrieve specific rows of data from the employees table.

Using SELECT With a WHERE Clause

-- the following retrieves data where the manager_id equals 122
SELECT * FROM employees WHERE manager_id = 122;

-- this retrieves data where the manager_id equals 122 and job_id is ST_CLERK
SELECT * FROM employees WHERE manager_id = 122 AND job_id = 'ST_CLERK';

-- this retrieves employees with managers with Ids between 122 and 125 inclusive
SELECT * FROM employees WHERE manager_id BETWEEN 122 AND 125;

-- this uses the wildcard % to retrieve employee data 
-- where the last name contains mar somewhere in the name
SELECT employee_id, last_name FROM employees WHERE last_name LIKE '%mar%';

-- this retrieves employees where the last name starts with Mar
SELECT employee_id, last_name FROM employees WHERE last_name LIKE 'Mar%';

-- this retrieves employees where the commission percentage is not null
SELECT employee_id, last_name FROM employees WHERE commission_pct IS NOT NULL;

-- the following retrieves data where the employee_id equals 125, 130, or 135
SELECT employee_id, last_name, first_name FROM employees
       WHERE employee_id IN (125, 130, 135);

See Also:

Oracle Database SQL Language Reference for detailed information on using the WHERE clause

Sorting Data Using the ORDER BY Clause

You can use SELECT with the ORDER BY clause to retrieve and display rows from a table ordered (sorted) by a specified column in the table. The specified column in the ORDER BY clause does not have to be in the select-list of columns that you want to display.

You can specify the sort order ASC for ascending or DESC for descending. The default sort order is ascending, which means:

  • Numeric values are displayed with the lowest values first, such as 1 to 999.

  • Character values are displayed in alphabetical order, such as A first and Z last.

  • Date values are displayed with the earliest value first, such as 01-JUN-93 before 01-JUN-95.

Null (empty) values are displayed last for ascending sequences and first for descending sequences.

Example: Using SELECT With ORDER BY shows how to use SELECT with the ORDER BY clause to retrieve and display rows from the employees table ordered (sorted) by specified columns.

Using SELECT With ORDER BY

-- the following retrieves rows with manager_id = 122 ordered by employee_id
-- the order is the default ascending order, lowest employee_id displays first
SELECT * FROM employees WHERE manager_id = 122 ORDER BY employee_id;

-- the following retrieves rows ordered by manager_id
-- the order is specified as descending, highest manager_id displays first
SELECT employee_id, last_name, first_name, manager_id FROM employees
      ORDER BY manager_id DESC;

See Example: Using SELECT With a WHERE Clause for the use of ORDER BY with the GROUP BY clause.


See Also:

Oracle Database SQL Language Reference for detailed information on using ORDER BY with SELECT

Displaying Data From Multiple Tables

You can use SELECT to display data from the multiple tables. This process is referred to as joining the tables. In a join, multiple tables share a similar column.

When you retrieve data from multiple tables, you can explicitly identify which table a column belongs to. This is important when tables contain columns with the same name. You can use the complete table name to explicitly identify a column, such as employees.employee_id, or a table alias. Note the use of the table aliases (d, e, and l) to explicitly identify the columns by table in the SQL statement. The alias is defined in the FROM clause of the SQL statement. A table alias is used to simply and reduce the size of the SQL code.

Example: Selecting Data From Multiple Tables With the ANSI Join Syntax is an example of querying data from joined tables using ANSI syntax. The first SELECT joins two tables and the second SELECT joins three tables.

Selecting Data From Multiple Tables With the ANSI Join Syntax

-- the following SELECT statements retrieve data from two tables
-- that have a corresponding column (department_id)
-- this join uses ANSI syntax, note the use of JOIN and ON
SELECT e.employee_id, e.last_name, e.first_name, e.department_id, 
  d.department_name  FROM employees e 
  JOIN departments d ON e.department_id = d.department_id;

-- the following SELECT retrieves data from three tables
-- two tables have the corresponding column department_id and 
-- two tables have the corresponding column location_id
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
  d.department_name, d.location_id, l.country_id FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  JOIN locations l ON d.location_id = l.location_id;

In Example: Using SELECT to Display Data From Multiple Tables the joins use the Oracle-proprietary syntax. There is no performance difference between the ANSI and Oracle syntax.

Using SELECT to Display Data From Multiple Tables

-- the following SELECT statements retrieve data from two tables
-- that have a corresponding column (department_id)
-- note that the employees table has been aliased to e and departments to d
SELECT e.employee_id, e.last_name, e.first_name, e.department_id, 
  d.department_name  FROM employees e, departments d 
  WHERE e.department_id = d.department_id;

-- the following SELECT retrieves data from three tables
-- two tables have the corresponding column department_id and 
-- two tables have the corresponding column location_id
SELECT e.employee_id, e.department_id, d.department_name, d.location_id,
  l.country_id FROM employees e, departments d, locations l
  WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

See Also:

"Joins" in Oracle Database SQL Language Reference for information on using SELECT with multiple tables

Manipulating Data With SQL Statements

Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:

DML statements are the most frequently used SQL statements.

Adding Data With the INSERT Statement

You can use the SQL INSERT statement to add a row of data to a table. The data inserted must be valid for the data type and size of each column of the table.

Example: Using the INSERT Statement shows how to use INSERT to add a row to the employees table. In the first INSERT statement, values are inserted into all columns in a row of the table. When you insert data into the columns, you must provide data values that are valid for the data type and size of the column.

In the second INSERT statement, values are inserted only into the specified columns of the table and the remaining columns are set to NULL. If the those remaining columns had been specified with a NOT NULL constraint for the table, an error would have been raised.

Using the INSERT Statement

-- the following inserts data for all the columns in a row
INSERT INTO employees VALUES 
  (300, 'Enrique', 'Belden', 'enrique.belden', '555.111.2222', 
   '01-AUG-05', 'AC_MGR', 9000, .1, 101, 110);

-- the following inserts data into the columns specified by name
-- NULLs are inserted in those columns not explicitly named 
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary)
  VALUES (301, 'Doe', 'john.doe', '31-AUG-05', 'SH_CLERK', 2400); 

-- the following shows the rows were inserted beginning with 300
SELECT employee_id, last_name FROM employees WHERE employee_id >= 300;

See Also:

Oracle Database SQL Language Reference for information on the INSERT statement

Updating Data With the UPDATE Statement

You can use the SQL UPDATE statement to update data in a row of a table. The updated data must be valid for the data type and size of each column of the table.

Example: Using the UPDATE Statement shows how to use UPDATE to update data in the employees table. Note the use of the use of multiplication operator * to calculate a new salary. For information on arithmetic operators, See "Using Arithmetic Operators".

Using the UPDATE Statement

SELECT salary FROM employees WHERE employee_id = 301;

-- update the salary for employee 301, multiply the salary by 105%
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 301;

-- the following should show a change in salary
SELECT salary FROM employees WHERE employee_id = 301;

See Also:

Oracle Database SQL Language Reference for information on the UPDATE statement

Deleting Data With the DELETE Statement

With the SQL DELETE statement you can delete all or specific rows in a table.

When you delete all the rows in a table, the empty table still exists. If you want to remove the entire table from the database, use the SQL DROP statement. See "Dropping a Table With SQL".

Example: Using the DELETE Statement shows how to use DELETE to delete selected rows in the employees table. Note the use of the WHERE clause. Without that clause, all the rows would be deleted.

Using the DELETE Statement

DELETE FROM employees WHERE employee_id = 300 OR employee_id = 301;

-- the following query should not find any records
SELECT * FROM employees WHERE employee_id = 300 OR employee_id = 301;

If you accidentally delete rows, you can restore the rows with the ROLLBACK statement. See "Rolling Back a Transaction".


See Also:

Oracle Database SQL Language Reference for information on the DELETE statement

Transaction Control Statements

Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to:

This topic includes the following topics:

Committing Transaction Changes

The SQL COMMIT statement saves any changes you have made to the database. When a COMMIT has been executed, all the recent changes since the last COMMIT, or since you logged on as the current user, are saved.

Example: Using the COMMIT Statement shows how to use COMMIT to commit (save) changes to the employees table in the database.

Using the COMMIT Statement

-- add a row and then update the data
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary)
  VALUES (301, 'Doe', 'john.doe', '31-AUG-05', 'SH_CLERK', 2400); 

UPDATE employees SET salary = salary*1.10 WHERE employee_id = 301;

-- commit (save) the INSERT and UPDATE changes in the database
COMMIT;

See Also:

Oracle Database SQL Language Reference for information on the COMMIT statement

Rolling Back a Transaction

You can use the SQL ROLLBACK statement to rollback (undo) any changes you made to the database before a COMMIT has been executed.

Example: Using the ROLLBACK Statement shows how to use ROLLBACK to rollback the deletions made to the employees table. Note that the ROLLBACK was issued before a COMMIT was executed.

Using the ROLLBACK Statement

-- delete a row (record)
DELETE FROM employees WHERE last_name = 'Doe';

-- rollback the delete statement because the previous DELETE was incorrect
ROLLBACK;

-- the following is valid
SELECT * FROM employees WHERE last_name = 'Doe';

See Also:

Oracle Database SQL Language Reference for information on the ROLLBACK statement

Using Pseudocolumns, Sequences, and SQL Functions

With SQL built-in functions you can manipulate character, numeric, and date data in SQL statements. You can also perform operations on a collection of data with the aggregate functions.

Pseudocolumns are built-in values that provide specific information with a query and are similar to functions without arguments. However, functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

This topic includes the following topics:


See Also:

Oracle Database SQL Language Reference for detailed information on SQL functions

Using Pseudocolumns With SQL

A pseudocolumns is similar to a table column, but is not actually stored in a table. A pseudocolumn returns a value so it is similar to a function without argument. Oracle Database provides several pseudocolumns, such as the ROWNUM, SYSDATE, and USER. The ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row in a query. SYSDATE returns the current date and time set for the operating system on which the database resides. USER returns the name of the user name that is currently logged in.

Example: Using Pseudocolumns show the use of the ROWNUM, SYSDATE, and USER pseudocolumns. Note the use of the table DUAL, which is automatically created by Oracle Database for use as a dummy table in SQL statements.

Using Pseudocolumns

-- the following statement displays the SYSDATE, which is the current system date
-- NOW is a column alias for display purposes
-- DUAL is a dummy table with one row simply used to complete the SELECT statement
SELECT SYSDATE "NOW" FROM DUAL;

-- display the name of the current user, the user name should be HR
SELECT USER FROM DUAL;

-- using ROWNUM < 10 limits the number of rows returned to less than 10
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10;

See Example: Using Date Functions for another example of the use of SYSDATE.

Using Sequences

A sequence is a database object similar to a pseudocolumn that generates unique sequential values, often used for primary and unique keys. You can refer to sequence values in SQL statements with the CURRVAL and NEXTVAL pseudocolumns.

To generate a sequence number, you call the sequence using the CURRVAL or NEXTVAL keywords. You must qualify CURRVAL and NEXTVAL with the name of the sequence, such as employees_seq.CURRVAL or employees_seq.NEXTVAL. Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

Example: Using Sequences shows an example of the use of the employees_seq sequence with the employee_id of the employees table. The employees_seq sequence is part of the HR schema and had been created for use with the employees table. When a sequence is intended to be used with a specific table, it is a good practice to include the name of the table in the sequence name.

Using Sequences

-- first initialize the employees_seq sequence with NEXTVAL
SELECT employees_seq.NEXTVAL FROM DUAL;

-- after initializing the sequence, use CURRVAL as the next value in the sequence
INSERT INTO employees VALUES 
  (employees_seq.CURRVAL, 'Belinda', 'Vernal', 'belinda.vernal', '555.111.2342', 
   '15-AUG-05', 'ST_CLERK', 6000, NULL, 124, 50);

-- query the employees table to check the current value of the sequence
-- which was inserted used as employee_id in the previous INSERT statement
SELECT employee_id, last_name FROM employees WHERE last_name = 'Vernal';

Using Character Functions

Oracle Database provides a set of character functions that you can use in your SQL statements to customize the character values. With character functions, you can perform operations that upper case, lower case, trim blanks from, and concatenate character data.

Example: Using Character Functions shows how to use character functions on character data.

Using Character Functions

-- you can use the UPPER function to display uppercase data, LOWER for lowercase
SELECT employee_id, UPPER(last_name), LOWER(first_name) FROM employees;

-- you can use CONCAT function to concatenate character data
SELECT CONCAT('Last name: ', last_name) FROM employees;

-- you can use RTRIM and LTRIM to remove spaces from the beginning or end of 
-- character data. Note the use of concatenation operator ||
SELECT employee_id, RTRIM(first_name) || ' ' || LTRIM(last_name) FROM employees;

-- you can TRIM to remove spaces from both the beginning and end
SELECT employee_id, TRIM(last_name) || ', ' || TRIM(first_name) FROM employees;

-- you can format the system date (SYSDATE) as a character string 
-- with various format masks and then display
-- the following displays September 21 2005
SELECT TO_CHAR(SYSDATE, 'fmMonth DD YYYY') "Today" FROM DUAL;

-- the following displays 21-SEP-2005 AD
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Today" FROM DUAL;

Using Arithmetic Operators

You can use arithmetic operators to create expressions for calculations on data in tables. The arithmetic operators include:

  • + for addition

  • - for subtraction

  • * for multiplication

  • / for divide

In an arithmetic expression, multiplication and division are evaluated first, then addition and subtraction. When operators have equal precedence, the expression is evaluated left to right. It is best to include parentheses to explicitly determine the order of operators and provide clarity in the expression.

Example: Using Arithmetic Operators shows the use of arithmetic operators in expressions with the data in the employees table. Note the use of a column alias to provide a more useful heading for the displayed output.

Using Arithmetic Operators

-- in the following query the commission is displayed as a percentate instead 
-- of the decimal that is stored in the database
SELECT employee_id, (commission_pct * 100) "Commission %" FROM employees;

-- in the following query, the proposed new annual salary is calculated
-- for employees who report to the manager with Id 145
SELECT employee_id, ((salary + 100) * 12) "Proposed new annual salary" 
  FROM employees WHERE manager_id = 145;

Using Numeric Functions

Oracle Database provides a set of numeric functions that you can use in your SQL statements to manipulate the numeric values. With numeric functions, you can perform operations that upper case, lower case, trim blanks from, and concatenate character data.

Example: Using Numeric Functions shows how to use numeric functions on numeric data in the employees table.

Using Numeric Functions

-- you can use the ROUND function to round off numeric data, in this case to
-- two decimal places
SELECT employee_id, ROUND(salary/30, 2) "Salary per day" FROM employees;

-- you can use the TRUNC function to truncate numeric data, in this case to
-- 0 decimal places; 0 is the default so TRUNC(salary/30) would be same
SELECT employee_id, TRUNC(salary/30, 0) "Salary per day" FROM employees;

Using Date Functions

Oracle Database provides various functions for calculating and converting datetime data.


See Also:

Oracle Database SQL Language Reference for details about the date functions

Performing Date Arithmetic

Oracle Database provides a number of features to help with date arithmetic, so that you do not need to perform your own calculations on the number of seconds in a day, the number of days in each month, and so on. Some useful features include the following:

  • ADD_MONTHS function, which returns the date plus the specified number of months.

  • MONTHS_BETWEEN function, which returns the number of months between two dates.

  • SYSDATE function, which returns the current date and time set for the operating system on which the database resides.

  • SYSTIMESTAMP function, which returns the system date, including fractional seconds and time zone, of the system on which the database resides.

  • TRUNC function, which when applied to a DATE value, trims off the time portion so that it represents the very beginning of the day (the stroke of midnight). By truncating two DATE values and comparing them, you can determine whether they refer to the same day. You can also use TRUNC along with a GROUP BY clause to produce daily totals.

  • Arithmetic operators such as + and -. For example, SYSDATE-7 refers to 7 days before the current system date.

  • INTERVAL data types, which enable you to represent constants when performing date arithmetic rather than performing your own calculations. For example, you can add or subtract INTERVAL constants from DATE values or subtract two DATE values and compare the result to an INTERVAL.

  • Comparison operators such as >, <, =, and BETWEEN.

Converting Between Datetime Types

Oracle Database provides several useful functions that enable you to convert to a from datetime data types. Some useful functions include:

  • EXTRACT, which extracts and returns the value of a specified datetime field from a datetime or interval value expression

  • NUMTODSINTERVAL, which converts a NUMBER or expression that can be implicitly converted to a NUMBER value to an INTERVAL DAY TO SECOND literal

  • NUMTOYMINTERVAL, which converts a NUMBER or expression that can be implicitly converted to a NUMBER value to an INTERVAL YEAR TO MONTH literal

  • TO_DATE, which converts character data to a DATE data type

  • TO_CHAR, which converts DATE data to character data

  • TO_DSINTERVAL, which converts a character string to an INTERVAL DAY TO SECOND value

  • TO_TIMESTAMP, which converts character data to a value of TIMESTAMP data type

  • TO_TIMESTAMP_TZ, which converts character data to a value of TIMESTAMP WITH TIME ZONE data type

  • TO_YMINTERVAL, which converts a character string to an INTERVAL YEAR TO MONTH type

Example: Using Date Functions shows how to use date functions on date data.

Using Date Functions

-- in the following statement you can use MONTHS_BETWEEN to compute months
-- employed for employees and then truncate the results to the whole month
-- note the use of the label (alias) "Months Employed" for the computed column
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed" 
  FROM employees;

-- the following displays the year hired for each employee id
SELECT employee_id, EXTRACT(YEAR FROM hire_date) "Year Hired" FROM employees;

Example: Using Date Functions With Format Masks shows how to use date functions with format masks.

Using Date Functions With Format Masks

-- use TO_DATE with a format mask to display or enter dates differently than the 
-- current default date format
-- the following displays 1998 with the 'DD-MON-RR' format mask
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL;
-- note that 'YY' in a format mask denotes the year in the current century
-- the following displays 2098 with the 'DD-MON-YY' format mask
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-YY') ,'YYYY') "Year" FROM DUAL;

-- the following displays the date and time with a datetime format mask
SELECT TO_TIMESTAMP ('10-Sep-05 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
  FROM DUAL;

-- the following displays the system date and time with a format mask
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Now" FROM DUAL;

Using Aggregate Functions

Group functions operate on sets of rows to give one result per group. These sets may comprise the entire table or the table split into groups.

Example: Using Aggregate Functions shows how to use aggregate functions on collections of data in the database. Aggregate functions include COUNT, MAX, MIN, and SUM. The GROUP BY clause is used to select groups of rows by a specified expression and returns one row of summary information for each group.

Using Aggregate Functions

-- you can use COUNT to count the employees with manager 122
-- note the use of a column alias Employee Count
SELECT COUNT(*) "Employee Count" FROM employees WHERE manager_id = 122;

-- count the employees grouped by manager, also sort the groups
SELECT COUNT(*) "Employee Count", manager_id  FROM employees 
  GROUP BY manager_id ORDER BY manager_id;

-- you can use MIN to find the minimum salary for employees with manager 122
SELECT MIN(salary) FROM employees WHERE manager_id = 122;

-- this computes the minimum and maximum salary by job_id groups
-- the job_ids groups are sorted in alphabetical order
SELECT MIN(salary), MAX(salary), job_id FROM employees 
  GROUP BY job_id ORDER BY job_id;

Using SQL Data Definition Language Statements

Data definition language (DDL) statements include CREATE, ALTER, and DROP for defining database objects. When managing database objects, SQL Developer provides a simple and easy-to-use interface that can be utilized instead of SQL DDL statements.

In this guide, some basic SQL DDL statements are used in the code examples and a brief description of some DDL statements are discussed here.

This topic includes the following topics:

Creating a Table With SQL

To create a database object, such as a table, use the SQL CREATE statement, as shown in Example: Creating a Simple Table. When you create a table, you need to provide data types for each column. For more information about tables, see Managing Tables.

Creating a Simple Table

-- create a simple table for keeping track of birthdays
CREATE TABLE my_birthdays
  ( first_name     VARCHAR2(20),
    last_name      VARCHAR2(25),
    bday_date      DATE
  ); 

Optionally, you can provide constraints, as shown in Example: Creating a Table With Constraints.

Creating a Table With Constraints

-- create a table similar to the employees table in the HR schema
CREATE TABLE my_employees
  ( employee_id    NUMBER(6),
    first_name     VARCHAR2(20),
    last_name      VARCHAR2(25) CONSTRAINT my_emp_last_name_nn NOT NULL,
    email          VARCHAR2(25) CONSTRAINT my_emp_email_nn NOT NULL,
    phone_number   VARCHAR2(20),
    hire_date      DATE DEFAULT SYSDATE CONSTRAINT my_emp_hire_date_nn NOT NULL,
    job_id         VARCHAR2(10) CONSTRAINT my_emp_job_nn NOT NULL,
    salary         NUMBER(8,2) CONSTRAINT  emy_mp_salary_nn NOT NULL,
    commission_pct NUMBER(2,2),
    manager_id     NUMBER(6), 
    department_id  NUMBER(4),
    CONSTRAINT     my_emp_salary_min CHECK (salary > 0),
    CONSTRAINT     my_emp_email_uk UNIQUE (email)
  ); 

Creating and Modifying an Index With SQL

To create, modify, or drop an index, use the SQL CREATE, ALTER, or DROP INDEX statement, as shown in Example: Creating, Modifying, and Dropping an Index.

Creating, Modifying, and Dropping an Index

-- create a new index on the employees table using the email column
CREATE INDEX email_ix 
  ON employees (email);

-- disable the index
ALTER INDEX email_ix 
  RENAME TO my_email_ix;

-- drop the index
DROP INDEX my_email_ix;

-- create an index on a single column to make queries faster on that column
CREATE INDEX emp_last_name_ix ON employees (last_name);
DROP INDEX emp_last_name_ix;

-- create an index on two columns to make queries faster on the first column
-- or both columns
CREATE INDEX emp_mgr_id_ix ON employees (employee_id, manager_id);
DROP INDEX emp_mgr_id_ix;

-- a function-based index precalculates the result and speeds up queries that
-- use the function for searching or sorting, in this case UPPER(last_name)
CREATE INDEX emp_upper_last_name_ix ON employees (UPPER(last_name));
DROP INDEX emp_upper_last_name_ix;

Creating and Modifying a Constraint With SQL

To add or a modify a constraint on a table, use the SQL ALTER statement, as shown in Example: Creating and Altering a Constraint.

Creating and Altering a Constraint

-- add a constraint a new constraint
ALTER TABLE my_employees 
  ADD CONSTRAINT ...

-- remove the constraint on email in the my_employees table
ALTER TABLE my_employees
  DROP UNIQUE (email); 

Altering a Table With SQL

To alter a database object, such as a table, use the SQL ALTER statement, as shown in Example: Altering a Table.

Altering a Table

-- add a new column to my_birthdays
ALTER TABLE my_birthdays
  ADD (age NUMBER(3));

-- rename the my_employees table
ALTER TABLE my_employees RENAME to temp_employees; 

Dropping a Table With SQL

To drop (remove completely) a table from the database use the SQL DROP statement, as shown in Example: Dropping a Table. Be very careful when using the DROP statement to remove database objects.

If you want to delete the rows in the table and keep the table, use the DELETE statement. See Deleting Data With the DELETE Statement.

Dropping a Table

-- drop tables from the database
-- use caution when use the DROP statement!
DROP TABLE my_birthdays;
DROP TABLE temp_employees;

Creating and Dropping a Sequence

Example: Creating a Sequence creates a sequence that can be used with the employees table. The sequence could also be used with other tables. For more information on sequences, see Managing Sequences.

Creating a Sequence

-- create a new sequence to use with the employees table
CREATE SEQUENCE new_employees_seq START WITH 1000 INCREMENT BY 1;

-- to use the sequence, first initialize the sequence with NEXTVAL
SELECT new_employees_seq.NEXTVAL FROM DUAL;

-- after initializing the sequence, use CURRVAL as the next value in the sequence
INSERT INTO employees VALUES 
  (new_employees_seq.CURRVAL, 'Pilar', 'Valdivia', 'pilar.valdivia',
  '555.111.3333', '01-SEP-05', 'AC_MGR', 9100, .1, 101, 110);

-- query the employees table to check the current value of the sequence
-- which was inserted used as employee_id in the previous INSERT statement
SELECT employee_id, last_name FROM employees WHERE last_name = 'Valdivia';

Example: Dropping a Sequence drops the sequence that you previously created.

Dropping a Sequence

-- drop the sequence
DROP SEQUENCE new_employees_seq;

Creating and Dropping a Synonym

Example: Creating a Synonym creates a synonym that is alias for the employees table. For more information on synonyms, see Managing Synonyms.

Creating a Synonym

-- create a synonym for the employees table
CREATE SYNONYM emps for HR.employees;

-- query the employees table using the emps synonym
SELECT employee_id, last_name FROM emps WHERE employee_id < 105;

Example: Dropping a Synonym drops a synonym.

Dropping a Synonym

-- drop the synonym
DROP SYNONYM emps;