SQL in Oracle Database Management System

SQL in Oracle Database Management System

SQL in Oracle Database Management System : This section deals with SQL in Oracle. All commands are Oracle Specific and may not run in other DBMS softwares. Consult your teacher for further clarification.

Note: The tables used in this demo are the Tables given with Oracle default installation. One more “Locations” table have been added which contains location no. and location_ld. You can populate it with data yourself.

Relating Multiple Tables

Each table contains data that describes exactly one entity. For example, the EMP table contains information about emp. Categories of data are listed across the top of each table, and individual cases are listed below. Using a table format, you can readily visualize, understand, and use information. Because data about different entities is stored in different tables, you may need to combine two or more tables to answer a particular question. For example, you may want to know the location of the dept where an employee works. In this scenario, you need information from the EMP table (which contains data about emp) and the DEPT table (which contains information about dept). With an RDBMS you can relate the data in one table to the data in another by using the foreign keys. A foreign key is a column or a set of columns that refer to a primary key in the same table or another table.

You can use the ability to relate data in one table to data in another to organize information in separate, manageable units. Employee data can be kept logically distinct from dept data by storing it in a separate table.

Guidelines for Primary Keys and Foreign Keys:

  1. You cannot use duplicate values in a primary key.
  2. Primary keys generally cannot be changed.
  3. Foreign keys are based on data values and are purely logical, not physical, pointers.
  4. A foreign key value must match an existing primary key value or unique key value, or else be null.
  5. A foreign key must reference either a primary key or unique key Column.

Relational Database Properties

A relational database:

  1. Can be accessed and modified by executing structured query language (SQL) statements
  2. Contains a collection of tables with no physical pointers
  3. Uses a set of operators

Structured Query language

Using SQL, you can Communicate with the Oracle server. SQL has the following advantages:

  1. Efficient
  2. Easy to learn and use
  3. Functionally complete (With SOL, you can define, retrieve, and manipulate data in the tables).

Data Types in Oracle

Data type Description

VARCHAR2 [size) : Variable-length character data (a maximum size must be specified: Minimum size is 1; maximum size is 4000).

CHAR [(size)] :  Fixed-length character data of length size bytes (default and minimum size is 1; maximum size is 2000).

NUMBER [(ps)] : Number having precision p and scales (The precision is the total number of decimal digits, and the scale is the number of digits to the right of the decimal point; the precision can range from 1 to 38 and the scale can range from -84 to 127).

DATE : Date and time values to the nearest second between January 1, 4712. B.C., and December 31, 9999 A.D.

LONG : Variable-length character data up to 2 gigabytes

CLOB : Character data up to 4 gigabytes

RAW (size): Raw binary data of length size (a maximum size must be specified maximum size is 2000)

LONG RAW : Raw binary data of variable length up to 2 gigabytes

BLOB: Binary data up to 4 gigabytes

BFILE: Binary data Stored in an external file; up to 4 gigabytes

ROWID: A 64 base number System representing the unique address of a row in its table.

Types of SQL Statements/Commands in Oracle

 SQL in Oracle Database Management System

Basic SELECT Statement

SELECT *|{[DISTINCT] column/(expression [alias],…} FROM table ;     

  • SELECT identifies what columns
  • FROM identifies which table

Basic SELECT Statement

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

  • A SELECT clause, which specifies the columns to be displayed
  • A FROM clause, which specifies the table containing the Columns listed in the SELECT clause In the Syntax:

SELECT is a list of one or more Columns

*Selects all Columns

DISTINCT suppresses duplicates

Column/expression selects the named column or the expression

alias gives selected columns different headings

FROM table specifies the table containing the columns

A keyword refers to an individual SQL element.

For example, SELECT and FROM are keywords.

A Clause is a part of a SQL statement.

For example, SELECT empno, Ename, …is a clause.

A Statement is a combination of two or more clauses.

For example, SELECT* FROM emp is a SQL statement.

Selecting All Columns

SELECT * FROM dept;

Selecting All Columns of All Rows

SELECT deptno, dname, mgr, loc

FROM dept;

Writing SQL Statements

Using the following simple rules and guidelines, you can Construct valid Statements that are both easy to read and easy to edit:

  • SOL Statements are not case Sensitive, unless indicated.
  • SQL statements can be entered on one or many lines.
  • Keywords cannot be split across lines or abbreviated.
  • Clauses are usually placed on separate lines for readability and ease of editing.
  • Indents should be used to make code more readable.
  • Keywords typically are entered in upperCase, all other words, such as table names and Columns.

Arithmetic Expressions

Create expressions with number and date data by using arithmetic operators.

十,一, *,

Using Arithmetic Operators

SELECT Ename, sal, sal + 600 FROM emp;

Operator Precedence

  • Multiplication and division take priority over addition and subtraction.
  • Operators of the same priority are evaluated from left to right.
  • Parentheses are used to force prioritized evaluation and to clarify statements.

Using Parentheses

You can override the rules of precedence by using parentheses to specify the order in which operators are executed.

Defining a Null Value

  • A null is a value that is unavailable, unassigned, unknown, or inapplicable.
  • A null is not the same as zero or a blank space.

SELECT Ename, job, sal, commisionission_pct FROM emp;       

Null Values

If a row lacks the data value for a particular column, that value is said to be null, or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as zero or a Space. Zero is a number, and a space is a character. Columns of any data type can contain nulls. However, Some Constraints, NOT NULL and PRIMARY KEY, prevent nulls from being used in the Column.

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to null.   

If any Column value in an arithmetic expression is null, the result is null. For example, if you attempt to perform division with zero, you get an error. However, if you divide a number by null, the result is a null or unknown.

Defining a Column Alias      

A column alias :

  • Renames a column heading
  • ls useful with calculations
  • Immediately follows the Column name – there can also be the optional AS keyword between the Column name and alias
  • Requires double quotation marks if it contains spaces or special characters or is case Sensitive

Column Aliases

Specify the alias after the column in the SELECT list using a space as a separator. By default, alias headings appear in uppercase. If the alias contains spaces or Special characters (such as # or R.S.), or is case sensitive, enclose the alias in double quotation marks (” “).

Using Column Aliases

SELECT Ename”Name”, sal*3 “Quarter Sal” FROM emp;

SELECT Ename AS name, commission_pct commision FROM emp;

 

Concatenation Operator

You can link Columns to other columns, arithmetic expressions, or Constant values to Create a character expression by using the concatenation operator (). Columns on either side of the operator are combined to make a single output column.

Literal Character Strings

  • A literal is a character, a number, or a date included in the SELECT list.
  • Date and character literal values must be enclosed within single quotation marks.
  • Each character String is output once for each row returned.
  • Concatenation Operator.
  • A Concatenation operator
  • Concatenates Columns or character strings to other Columns
  • Is represented by two vertical bars (||)
  • Creates a resultant Column that is a character Expression

SELECT Ename || ‘is a’ ||  job AS “Employee Details’’ FROM emp;

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

SELECT DISTINCT deptno FROM emp;

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword

You Can Specify multiple Columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the Selected Columns, and the result is every distinct combination of the columns.

SELECT DISTINCT deptno, job FROM emp;

Features of SQL

  • Can be used by a range of users, including those with little or no programming experience
  • Is a nonprocedural language
  • Reduces the amount of time required for Creating and maintaining Systems
  • ls an English-like language

Restricting and Sorting Data Limiting the Rows Selected     

  • Restrict the rows returned by using the WHERE clause.
  • The WHERE clause follows the FROM clause

SELECT *|{[DISTINCT] column/expression (alias],…} FROM table WHERE condition(s)];

Limiting the Rows Selected       

You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. If the condition is true, the row meeting the condition is returned. In the syntax: WHERE restricts the query to rows that meet a condition.

Condition is composed of Column names, expressions, constants, and a comparison operator.

The WHERE clause can Compare values in columns, literal values, arithmetic expressions, or functions.

It consists of three elements:

  • Column name
  • Comparison Condition
  • Column name, constant, or list of values

Using the WHERE Clause

SELECT empno, Ename, job, deptno FROM emp

WHERE deptno = 90;

Character Strings and Dates

Character Strings and dates in the WHERE clause must be enclosed in single quotation marks (”). All character Searches are case Sensitive. In the following example, no rows are returned because the EMP table Stores all the last names in mixed case:

SELECT Ename, job, deptno FROM emp WHERE Ename=”WHALEN’;

Oracle databases Store dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and Seconds. The default date display is DD-MON-RR.

SQL Operators

Comparison Conditions

SQL in Oracle Database Management System

Comparison Conditions

Comparison Conditions are used in conditions that compare one expression to another value or expression. They are used in the WHERE clause in the following format :

Syntax

… WHERE expr operator value

For Example

… WHERE Hiredate=’O1-JAN-95’

SELECT Ename, sal FROM emp WHERE sal <= 3000;

Other Comparison Conditions

SQL in Oracle Database Management System

The BETWEEN Condition

You can display rows based on a range of values using the BETWEEN range Condition. The range that you Specify contains a lower limit and an upper limit.

The SELECT statement on the slide returns rows from the EMP table for any employee whose sal is between R2,500 and R3,500.

Values specified with the BETWEEN Condition are inclusive. You must specify the lower limit first.

Using the IN Condition

Use the IN membership condition to test for values in a list.

The IN Condition

To test for values in a specified set of values, use the IN condition. The IN condition is also known as the membership condition.

The Slide example displays employee numbers, last names, Salaries, and manager’s employee numbers for all the emp whose manager’s employee number is 100, 101, or 201.

The IN Condition can be used with any data type. The following example returns a row from the EMP table for any employee whose last name is included in the list of names in the WHERE clause:

SELECTempno, mgr, deptno FROM emp

WHERE Enamel N (‘Hartstein’, ‘Vargas’);

If characters or dates are used in the list, they must be enclosed in single quotation marks (”).

SELECT empno, Ename, Sal, mgrFROM emp WHEREmgr|N (100,101, 201); Using the LIKE Condition

  • Use the LIKE condition to perform wildcard searches of valid search string values.
  • Search conditions can contain either literal characters or numbers:

– % denotes zero or many characters.

– denotes one character.

SELECT Ename FROM emp WHERE Ename LIKE ‘S%;

Symbol Description

% Represents any Sequence of zero or more characters

_Represents any single character

Using the NULL Conditions

Test for nulls with the IS NULL operator.

SELECT Ename, mgr FROM emp WHERE mgir IS NULL;       

Logical Conditions

SQL in Oracle Database Management System

A logical condition combines the result of two component conditions to produce a single result based on them or inverts the result of a single condition. A row is returned only if the overal result of the condition is true. Three logical operators are available in SQL :

  • AND
  • OR
  • NOT

by NOT All the examples so far have specified only one condition in the WHERE clause. You can use several conditions in one WHERE clause using the AND and OR operators.

Using the NOT Operator

SELECT Ename, job FROM emp WHERE job

NOT IN (CLERK’, ‘MANAGER’);

Note:  The NOT operator can also be used with other SOL operators, such as BETWEEN, LIKE, and NULL.

… WHERE job NOT IN (‘CLERK’, ‘ANALYST)

… WHERE sal NOT BETWEEN BOOO AND 5OOO

… WHERE Ename NOT LIKE ‘%A%’

… WHERE Comm IS NOT NULL

ORDER BY Clause

  • Sort rows with the ORDER BY clause

ASC: ascending order, default

  • DESC: descending order

The ORDER BY clause comes last in the SELECT

SELECT Ename, job, deptno, Hiredate FROM emp

ORDER BY Hiredate;                                                

Sorting in Descending Order

SELECTEname, job, deptno, Hiredate FROM emp

ORDER BY Hiredate DESC;

Default Ordering of Data

The default sort order is ascending:

  • Numeric values are displayed with the lowest values first-for example, 1-999.
  • Date values are displayed with the earliest value first-for example, O1-JAN-92 before 01-JAN-95.
  • Character values are displayed in alphabetical order-for example, A first and Z last.
  • Null values are displayed last for ascending sequences and first for descending sequences.

Reversing the Default Order

To reverse the order in which rows are displayed, specify the DESC keyword after the column name in the ORDER BY clause. The slide example sorts the result by the most recently hired employee.

Sorting by Multiple Columns

The order of ORDER BY list is the order of sort.

  • You can sort by a column that is not in the SELECT list.

SELECT Ename, deptno, sal FROM emp

ORDER BY deptno, sal DESC;

Single-Row Functions

SQL Functions (continued)

There are two distinct types of functions:

  • Single-row functions
  • Multiple-row functions

Single-Row Functions

These functions operate on single rows only and return one result per row. There are different types of single-row functions. This lesson covers the following ones:

  • Character
  • Number
  • Date
  • Conversion

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are known as group functions. This is covered in a later lesson.

Character Functions

Single-row character functions accept character data as input and can return both character and numeric values. Character functions can be divided into the following:

  • Case-manipulation functions
  • Character-manipulation functions

SQL in Oracle Database Management System

SQL in Oracle Database Management System

Case Manipulation Functions

LOWER UPPER, and INITCAP are the three case-conversion functions.

  • LOWER Converts mixed case or uppercase character strings to lowercase
  • UPPER Converts mixed case or lowercase character strings to uppercase
  • INITCAP Converts the first letter of each word to uppercase and remaining letters to lowercase

SELECT empno, UPPER(Ename), deptno

FROM emp WHERE INITCAP(Ename) = ‘Smith’;

Character-Manipulation Functions

CONCAT SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character manipulation functions Covered in this lesson.

  • CONCAT_Joins values together (You are limited to using two parameters with CONCAT).
  • SUBSTR_Extracts a string of determined length
  • LENGTH_Shows the length of a string as a numeric value
  • INSTR_Finds numeric position of a named character
  • LPAD_Pads the character value right-justified
  • RPAD : Pads the character value left-justified
  • TRIM: Trims heading or trailing characters (or both) from a character string (If trim character or trim_source is a character literal, you must enclose it in single quotes.)

Number Functions

Number functions accept numeric input and return numeric values. This section describes some of the number functions.

Number Functions

  • ROUND: Rounds value to specified decimal ROUND(45.926, 2) 45.93
  • TRUNC: Truncates value to specified decimal

TRUNC(25.926, 2) 25.92

  • MOD: Returns remainder of division

MOD : (1300, 300) 100

Function Purpose

ROUND(column expression, n) Rounds the column, expression, or value to n decimal places, or, if n is omitted, no decimal places. (If n is negative, numbers to left of the decimal point are rounded.)

TRUNC(column expression, n) Truncates the column, expression, or valueton decimal places, Or, if n is omitted, then n defaults to zero.

MOD(m,n) Returns the remainder of m divided by n.

ROUND Function

The ROUND function rounds the Column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is -2, the value is rounded to two decimal places to the left.

The ROUND function can also be used with date functions.

The DUAL Table

The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X. The DUAL table is useful when you want to return a value once only, for instance, the value of a constant, pseudocolumn, or expression that is not derived from a table with user data. The DUAL table is generally used for SELECT clause syntax completeness, because both SELECT and FROM clauses are mandatory, and Several calculations do not need to Select from actual tables.

TRUNC Function

The TRUNC function truncates the column, expression, or valueton decimal places.

The TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argumentis-2, the value is truncated to two decimal places to the left.

Like the ROUND function, the TRUNC function can be used with date functions.

MOD Function

The MOD function finds the remainder of value1 divided by value2.

The MOD function is often used to determine if a value is odd or even.

Working with Dates

  • Oracle database Stores dates in an internal numeric format: Century, year, month, day, hours, minutes, Seconds.
  • The default date display format is DD-MON-RR.

The SYSDATE Function

SYSDATE is a date function that returns the Current database server date and time. You can use

SYSDATE just as you would use any other column name. For example, you can display the current date by Selecting SYSDATE from a table. It is customary to select SYSDATE from a dummy table called DUAL.

Example

Display the current date using the DUAL table.

SELECTSYSDATE FROMDUAL ;

Date Functions

Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

  • MONTHS_BETWEEN (date1, date2). Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month
  • ADD MONTHS(date, n). Adds n number of calendar months to date. The value of n must be an integer and can be negative.
  • NEXT DAY(date, ‘char”) Finds the date of the next specified day of the week
  • (‘char) following date. The value of charmay be a number representing a day or a character String.
  • LAST DAY(date) Finds the date of the last day of the month that contains date.
  • ROUND (date[,’fmt’])_Returns date rounded to the unit specified by the format model frnt. If the format model frnt is omitted, date is rounded to the nearest day.
  • TRUNC(dates[,’fmt’])_Returns date with the time portion of the day truncated to the unit specified by the format modelfmt. If the format model frntis omitted, date is truncated to the nearest day.

Date Functions (continued)

The ROUND and TRUNC functions can be used for number and date values. When used with dates, these functions round or truncate to the specified format model. Therefore, you can round dates to the nearest year or month.

Displaying a Date in a Specific Format

Previously, all Oracle date values were displayed in the DD-MON-YY format. You can use the TO CHAR function to convert a date from this default format to one specified by you.

Guidelines

  • The format model must be enclosed in single quotation marks and is case sensitive.
  • The format model can include any valid date format element. Be Sure to separate the date value from the format model by a commisiona.
  • The names of days and months in the output are automatically padded with blanks.
  • To remove padded blanks or to suppress leading zeros, use the fill mode firm element.

SELECT empno, TO CHAR(Hiredate, ‘MM/YY”) Month_Hired FROM emp

WHERE Ename=”ADAMS’;

Using the TO_CHAR Function with Dates

SELECT Ename, TO CHAR(Hiredate, ‘fmDD Month YYYY)

AS HIREDATE FROM emp;

Nesting Functions

SELECT Ename, NVL(TO_CHAR(mgr), ‘No Manager’)

FROM emp WHERE mgr IS NULL;

General Functions

These functions work with any data type and pertain to using nulls.

  • NVL (expr1, expr2)
  • NVL2 (expr1, expr2, expr3)
  • NULLIF (expr1, expr2)

Function Description

NVL Converts a null value to an actual value

NVL2 if expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2

returns expr3. The argument expr1 can have any data type.

NULLIF Compares two expressions and returns null if they are equal, or the first expression if they are not equal.

NVL Function Converts a null to an actual value.      

  • Data types that can be used are date, character, and number.
  • Data types must match.

— NVL (commisionission_pct,0)

— NVL (Hiredate, ‘01-JAN-97’)

— NVL(job, ‘No Job Yet’)

The NVL Function

To convert a null value to an actual value, use the NVL function.

Syntax

NVL (expr1, expr2)

In the Syntax:

expr1 is the source value or expression that may contain a null

expr2 is the target value for converting the null

You can use the NVL function to convert any data type, but the return value is always the same as the

data type of expr1.          

NVL Conversions for Various Data Types

Data Type Conversion Example

NUMBER NVL(number Column,9)

DATE NVL (date_column, ‘01-JAN-95’)

CHAR or VARCHAR2 NVL(character column, ‘Unavailable’)

The NVL2 Function

The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned.

Syntax

NVL(expr1, expr2, expr3)

In the syntax:

expr1 is the source value or expression that may contain null’

expr2 is the value returned if expr1 is not null

expr3 is the value returned if expr2 is null

In the example shown, the COMMISIONISSION PCT Column is examined.

The NULLIF Function

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. You cannot specify the literal NULL for first expression.

Syntax

NULLIF (expr1, expr2)

In the syntax:

expr1 is the Source value compared to expr2.

expr2 is the Source value Compared with expr1. (If it is not equal to expr1 expr1 is returned.)

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

DECODE(col| expression, search1, result1

[, search2, result2,…, ][, default])

The DECODE Function

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as Search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.

SELECT Ename, job, sal, DECODE(job, “IT_PROG’, 1.10*sal, “ST_CLERK’, 1.15*sal, “SA_REP”, 1.20*sal,sal)

REVISED SAL

FROM emp;

Displaying Table Structure

DESC[RIBE] tablename

Creating and Managing Tables

Naming Rules

Name database tables and columns according to the standard rules for naming any Oracle database object :

  • Table names and column names must begin with a letter and be 1-30 characters long.
  • Names must contain only the characters A-Z, a-Z, 0-9, (underscore), RS., and # (legal characters, but their use is discouraged).
  • Names must not duplicate the name of another object owned by the same Oracle server user.
  • Names must not be an Oracle Server reserved Word.

Naming Rules

Table names and column names:

  • Must begin with a letter
  • Must be 1-30 characters long.
  • Must Contain only A-Z, a-z, 0-9,_, RS., and #
  • Must not duplicate the name of another object owned by the same user
  • Must not be an Oracle server reserved word

CREATE TABLE [schema.] table (column datatype [DEFAULT expr], …]);

The CREATE TABLE Statement

Create tables to store data by executing the SOL CREATE TABLE statement. This statement is one of the data definition language (DDL) statements, that are covered in subsequentlessons. DDL statements area subset of SOLStatements used to create, modify, or remove Oraclesi database structures. These statements have an immediate effect on the database, and they also record information in the data dictionary.

To create a table, a user must have the CREATE TABLE privilege and a storage area in which to create objects. The database administrator uses data control language (DCL) statements, which are covered in a later lesson, to grant privileges to users.

In the syntax:

Schema  is the same as the owner’s name

Table  is the name of the table

DEFAULT expr specifies a default value if a value is omitted in the INSERT

Statement

Column  is the name of the Column

Datatype  is the column’s data type and length

Creating Tables

CREATE TABLE dept

(deptno NUMBER(2), dname VARCHAR2(14), locVARCHAR2(13));

DESCRIBE dept                                                                         

Creating a Table by Using a Subquery Syntax

Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.

  • Match the number of specified columns to the number of subquery Columns.
  • Define Columns with Column names and default values.

CREATE TABLE table

[(column, column…)] As subquery;

The ALTERTABLE Statement

Use the ALTERTABLE statement to:

  • Add a new column
  • Modify an existing column
  • Define a default value for the new Column
  • Drop a column

ALTER TABLE table

ADD (column datatype [DEFAULT expr], [, column data type]…);

ALTER TABLE table

MODIFY (column data type [DEFAULT expr] II, column data type]…);

ALTER TABLE table DROP (column);

Dropping a Table

  • All data and structure in the table is deleted.
  • Any pending transactions are commisionitted.
  • All indexes are dropped.
  • You cannot roll back the DROP TABLE statement.

Dropping a Table

The DROP TABLE Statement removes the definition of an Oracle table. When you drop a table, the database loses all the data in the table and all the indexes associated with it.

Syntax

DROP TABLE table

In the Syntax:

table is the name of the table

Guidelines

  • All data is deleted from the table.
  • Any views and Synonyms remain but are invalid.
  • Any pending transactions are commisionitted.
  • Only the Creator of the table or a user with the DROPANY TABLE privilege can remove a table. As with all DDL statements, DROP TABLE is Commisionitted automatically.

The TRUNCATE TABLE statement :

-Removes all rows from a table

-Releases the storage space used by that table

TRUNCATE TABLE dept;

Truncating a Table

Another DDL statement is the TRUNCATE TABLE statement, which is used to remove all rows from a table and to release the storage space used by that table. When using the TRUNCATE TABLE statement, you cannot rollback row removal.

Syntax

TRUNCATE TABLE table;

In the Syntax:

table is the name of the table

You must be the owner of the table or have DELETE TABLE System privileges to truncate a table. The DELETE statement can also remove all rows from a table, but it does not release storage Space. The TRUNCATE command is faster. Removing rows with the TRUNCATE statement is faster than removing them with the DELETE statement for the following reasons:

  • The TRUNCATE statement is a data definition language (DDL) statement and generates no rollback information.
  • Truncating a table does not fire the delete triggers of the table.
  • If the table is the parent of a referential integrity constraint, you cannot truncate the table.
  • Disable the constraint before issuing the TRUNCATE statement.
  • What are Constraints?
  • Constraints enforce rules at the table level.
  • Constraints prevent the deletion of a table if there are dependencies.
  • The following constraint types are valid:

-NOT NULL

-UNIOUE

-PRIMARY KEY

-FOREIGN KEY

-CHECK

Constraint Description

NOT NULL Specifies that the Column cannot contain a null value

UNIOUE Specifies a Column or combination of Columns whose values must be unique for all rows in the table

PRIMARY KEY Uniquely identifies each row of the table

FOREIGN KEY Establishes and enforces a foreign key relationship between the column and a column of the referenced table

CHECK Specifies a condition that must be true

Defining Constraints

  • Column constraint level
  • Table constraint level

CREATE TABLE [schema.]table (column datatype [DEFAULT expr]

[column constraint],

[table_constraint][,…]);

CREATE TABLE emp (empno NUMBER (6), Ename VARCHAR2(20),

job VARCHAR2(10) NOT NULL,

CONSTRAINTemp emp_id_pk PRIMARY KEY (EMPNO));

Constraints are usually Created at the same time as the table. Constraints can be added to a table after its creation..

The NOT NULL Constraint

CREATE TABLE emp( empno NUMBER(6),

EnameWARCHAR2(25) NOT NULL, sal NUMBER(8,2),

commisionission pct NUMBER(2,2), Hiredate DATE

CONSTRAINTemp Hiredate_nn

NOT NULL,

…)

The NOT NULL Constraint is defined at the column level:

The UNIOUE Constraint

A UNIOUE key integrity constraint requires that every value in a column or set of columns (key) be unique-that is, no two rows of a table can have duplicate values in a specified Column or set of Columns. The column (or set of columns) included in the definition of the UNIOUE key constraint is called the unique key, if the UNIOUE constraint comprises more than one column, that group of columns is called a composite unique key.

UNIOUE constraints allow the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A nullina Column (or in all Columns of a composite UNIOUEkey) always satisfies a UNIOUE Constraint.

CREATE TABLE emp(

empno NUMBER(6),

EnameVARCHAR2(25) NOT NULL,

email VARCHAR2(25),

sal NUMBER (8,2),

commisionission pct NUMBER(2,2),

Hiredate DATE NOT NULL,

…                                               

CONSTRAINT emp_email_uk. UNIOUE(email));

The PRIMARY KEY Constraint

A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or set of columns that uniquely identifies each row in a table. This constraint enforces uniqueness of the column or column combination and ensures that no Column that is part of the primary key can contain a null value.

The PRIMARY KEY Constraint

Defined at either the table level or the column level: CREATE TABLE dept(

deptno NUMBER(4),                                    

dname VARCHAR2(30)

CONSTRAINT dnamenn NOT NULL,

mgr NUMBER(6),

loc NUMBER(4),

CONSTRAINT deptno pk PRIMARY KEY(deptno));

The FOREIGN KEY Constraint

The FOREIGN KEY, or referential integrity Constraint, designates a column or combination of Columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table. In the example on the slide, DEPTNO has been defined as the foreign key in the EMP table

(dependent or child table); it references the DEPTNO Column of the DEPT table (the referenced or parent cable).

A foreign key value must match an existing value in the parent table or be NULL.

Foreign keys are based on data values and are purely logical, not physical, pointers.

CREATE TABLE emp (

empno NUMBER(6),

EnameVARCHAR2 (25) NOT NULL,

email VARCHAR2 (25),

sal, NUMBER ( 8 , 2 ) ,

commisionission_pct NUMBER(2, 2),

Hiredate DATE NOT NULL,

deptno NUMBER(4),

CONSTRAINT emp_dept _fk FOREIGN KEY (deptno)

REFERENCES dept (deptno),

CONSTRAINT emp_email_uk. UNIQUE (email));

The FOREIGN KEY Constraint (continued)      

FOREIGN KEY constraints can be defined at the column ortable constraint level. A composite foreignkey must be Created by using the table-level definition.

The example on the slide defines a FOREIGN KEY constraint on the DEPTNO column of the EMP table, using table-level syntax. The name of the Constraint is EMP_DEPTID_FK.

The foreign key can also be defined at the Column level, provided the Constraint is based on a single Column. The Syntax differs in that the keywords FOREIGN KEY do not appear. For example:

CREATE TABLE emp

(…

deptno NUMBER(4) CONSTRAINT emp_deptid_fk

REFERENCES dept(deptno),

)

The CHECK Constraint

  • Defines a condition that each row must satisfy
  • The following expressions are not allowed:

– References to CURRVAL NEXTVAL LEVEL, and ROWNUM pseudocolumns

– Calls to SYSDATE, UID, USER, and USERENV functions

– Queries that refer to other values in other rows …, sal NUMBER(2)

CONSTRAINT emp_sal_min CHECK (sal > 0),…

Adding a Constraint Syntax

Use the ALTERTABLE Statement to:

  • Add or drop a constraint, but not modify its structure
  • Enable Ordisable Constraints
  • Add a NOT NULL constraint by using the MODIFY clause

ALTER TABLE table

ADD CONSTRAINT constraint type (column);

Adding a Constraint

Add a FOREIGN KEY constraint to the EMP table indicating that a manager must already exist as a valid employee in the EMP table.

ALTER TABLE emp

ADD CONSTRAINTemp manager fk

FOREIGN KEY(mgr)

REFERENCES emp(empno);