Constraints

Constraints:

Constraints are useful for preventing the invalid data and also for data security. Constraints are as follows…

• Primary Key Constraint.
• Foreign key ( aka. reference key)
• Not NULL
• Unique
• Check

Not NULL:

As name says, this constraint will not allow the column as empty column. If any column define as NOT NULL constraint that column should contain the data. If we try to insert NULL. It will throw an error that states “Integrity constraint error: NOT NULL constraint violated”.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have null values.

Note: Space is NOT a null value.

Unique:

Unique constraint will not allow duplicate value in the column. But, it will allow null values. (‘N’ number of null values).

We can add this constraint my modifying the table (alter table modify….) but, that column should not have duplicate values.

Check

While inserting the record, using check constraint we can check the data before insert. It will easy to understand in example.

 

Primary key

Primary key is a combination of NOT NULL and UNIQUE constraint key. This also called as parent key. The column defined as PRIMARY will not allow NULL values and also duplicate values.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have null and duplicate values.

 

We can’t drop the primary column or table contains primary column before disable or drop foreign key constraint column or table contains foreign key defined table.

If we tried to drop the table or column we will get the error.

 

Foreign key

Foreign key is same like as unique constraint. It will not allow the duplicate values but, will allow the null values. This also called as a reference key. The data which are in the reference key column should have the same value as PRIMARY key. We can’t insert the values which are not in primary column.

We can add this constraint my modifying the table (alter table modify….) but, that column should not have duplicate values and also data should be available in primary key.

Characteristics:

Any time we can “enable” or “disable” the constraints but, whenever we are doing that, all criteria should be valid for the constraints.

Example for creating table:

Create table Arun (Id number (10) constraint pk_id Primary key,

Name Varchar2 (32) constraint NOT NULL,

age number (2) constraint age_check check (age>18) ,

contact_number number (12) constraint unique);

Create table Vars (Id number(2) constraint fk_id references arun(id));

Lesson : 19 - Character type Functions

1) ASCII

Syntax :

ASCII( string)

Returns ASCII values of first character of given string. ASCII stands for Americal Standard Code For Information Interchange.

Examples :

SQL>select ASCII('A') from dual;

65

SQL>select ASCII('America') from dual;

65

SQL>select ASCII(5) from dual;

53

2) CHR

Syntax :

CHR(ASCII value)

Retuns character equivalent to given ASCII values.

Exmaples :

SQL>select CHR(65) from dual;

A

SQL>select CHR(75) from dual;

K

3) CONCAT

Syntax :

CONCAT(string1, string2)

CONCAT returns String1 concatenated with String2. Both String1 and String2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its data type depends on the data types of the arguments.

If one of the arguments is a national data type, then the returned value is a national data type. For example:

•CONCAT(CLOB, NCLOB) returns NCLOB

•CONCAT(NCLOB, NCHAR) returns NCLOB

•CONCAT(NCLOB, CHAR) returns NCLOB

•CONCAT(NCHAR, CLOB) returns NCLOB

Examples :

SQL>select concat('Good','Work') from dual;

CONCAT('

--------

GoodWork

SQL>select concat('Employee name is : ', First_name) "Example " from emp;

Example

---------------------------------------

Employee name is : Arun

Employee name is : Sri

4) INITCAP

Syntax :

INITCAP (char type data)

Returns first letter of each word in uppercase, all other letters in lowercase.

Examples :

SQL>select initcap('This is nothing but testing') from dual;

INITCAP('THISISNOTHINGBUTTE

---------------------------

This Is Nothing But Testing

5) LOWER

Syntax :

Lower(char type data)

Returns lower case of given string.

Examples :

SQL>select lower('TESTING LOWER') from dual;

LOWER('TESTIN

-------------

testing lower

SQL>select lower('Lary Wills') from dual;

LOWER('LAR

----------

lary wills

SQL>select lower(first_name) from emp;

LOWER(FIRST_NAME)

--------------------

arun

sri

kamesh

6) INSTR

Syntax :

INSTR(string1, substring, starting position, nth occurence)

Search the substring in string1 and returns the starting position of substrong in the string1.

3rd parameter is optional, if it is specified, it denotes search starting position in the string1

4th parameter is also optional, if it mentioned, it denotes nth occurance , where n is positive number.

Examples :

SQL> select instr('Welcome to world of oracle','o') from dual;

5

SQL> select instr('Welcome to world of oracle','o',1) from dual;

5

SQL> select instr('Welcome to world of oracle','o',7) from dual;

10

SQL> select instr('Welcome to world of oracle','o',1,2) from dual;

10

SQL> select instr('Welcome to world of oracle','o',1,3) from dual;

13

7) LENGTH

Syntax :

LENGTH (char type data)

Returns length(number of characters) in given string.

Examples :

SQL> select length('oracle') from dual;

6

SQL> select length('oracle test') from dual;

11

Note : It counts space also.

8) LPAD

Syntax :

LPAD( string1, length, string to use pad )

It pads left side of the string1 with third parameter. 2nd parameter is the total length.

3rd parameter is optional and default is space.

Examples :

SQL> select lpad('Oracle',3) from dual;

Ora

SQL> select lpad('Oracle',10) from dual;

Oracle

SQL> select lpad('Oracle',10,'*') from dual;

****Oracle

SQL> select lpad('Oracle',10,'*$') from dual;

*$*$Oracle

SQL> select lpad(12345,10,'#') from dual;

#####12345

9) LTRIM

Syntax :

LTRIM( string1, string2 )

- Removes space in the left side of string1 if 2nd parameter is not specified.

string1 is the string to trim the characters from the left-hand side.

string2 is optional paramers, is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the ltrim function will remove all leading spaces from string1.

Example :

SQL> select ltrim(' SQl*Plus') from dual;

SQl*Plus

SQL> select ltrim(' SQl*Plus','*') from dual;

SQl*Plus

SQL> select ltrim('*****SQl*Plus','*') from dual;

SQl*Plus

SQL> select ltrim('$234.50','$') from dual;

234.50

10) REPLACE

Replaces a sequence of characters in a string with another set of characters

Syntax :

REPLACE( string1, string_to_replace, string used for replace )

3rd parameter is optional.

Example :

SQL> select replace('abcdabcd','b') from dual;

acdacd

SQL> select replace('abcdabcd','b','x') from dual;

axcdaxcd

11) RPAD

Syntax :

LPAD( string1, length, string to use pad )

It pads right side of the string1 with third parameter. 2nd parameter is the total length.

3rd parameter is optional and default is space.

SQL> select rpad('Oracle',10) from dual;

Oracle

SQL> select rpad('Oracle',10,'*') from dual;

Oracle****

Lesson 18 - Arithmetic Functions

Function Syntax Explanation Example ABS abs(number) returns the absolute value abs(-6) is 6 abs(6) is 6 ACOS acos(number) Returns arc cosine of given number .  The argument must be in the range of -1 to 1 ACOS(.3) is  1.26610367 ASIN asin(number) Returns the arc sine of given argument  which  must be in the range of -1 to 1. ASIN(.3) is  .304692654 ATAN ATAN(number) Returns the arc tangent ATAN(.3) is  .291456794 CEIL CEIL(number) Returns the smallest integer value that is greater than or equal to a number. ceil(46.72) is 47 ceil(-46.72) is -46. FLOOR FLOOR(number) Returns the greatest number that is less than or equal to given number. floor(2.8) is 2 floor(5.2) is 5 floor(-7.9) is -8 LOG Log(x,y) Returns the logarithm of  x base y.  LOG(5,2) is .430676558 MOD mod( x, y ) Returns modulus value,  the remainder of x divided by y.  MOD(6,3) is 0 MOD(6,4) is 2 POWER Power( m, n ) Returns x raised to the y th power. Power(6,2) is 36 REMAINDER REMAINDER(x,y) Remainder function returns the remainder of  x divided by y. remainder(25, 6)  is  1 remainder(25, 5) is 0 ROUND ROUND(x,y) Round off the x to the precison to y ROUND(2345.124,2) is  2345.12 ROUND(2345.125,2) is   2345.13 ROUND(2345.125,-1) is  2350 ROUND(2345.125,-2) is  2300 SIGN sign(number) If number < 0, then sign returns -1. If number = 0, then sign returns 0. If number > 0, then sign returns 1.  SIGN(46) is 1  SIGN(-46) is -1 SQRT SQRT(number) Returns square root sqrt(36) is 6 TRUNC trunc(number, decimal_places) Returns a number truncated to a certain number of decimal places trunc(675.265) is 675 trunc(675.265, 0) is 675 trunc(675.265, 1) is 675.2 trunc(675.265, 2) is 675.26 trunc(675.265, 3) is 675.265 trunc(-675.265, 2) is -675.26 trunc(675.265, -1) is 670 trunc(675.265, -2) is 600

Lesson : 17 : TRUNCATE Statement

Syntax :

 TRUNCATE TABLE <tablename>  DROP / RESUSE  STORAGE;

 

·         Removes all rows/records of given table

·         Is a DDL command, so Commit is NOT required

·         Can’t rollback after remove rows by truncate

·         Deallocates all space used by the removed rows

·         Faster than delete command

·         DROP STORAGE option is to deallocate all space from the deleted rows from the table

·         REUSE STORAGE option is to retain the space from the deleted rows allocated to the table

 

Examples :

1)      SQL> TRUNCATE TABLE emp;

-          Removes all records from emp table

2)      SQL> TRUNCATE TABLE emp REUSE STORAGE;

               -          Removes all records from emp table and it retains the space from the deleted rows allocated to the emp table.

 

Lesson : 16 DELETE Statement

Lesson : 16  DELETE Statement

 

Syntax :

   DELETE  FROM   <tablename>  WHERE condition;

DELETE statement is used to delete records/rows from  table. Note that, commit is required to make the delete permanently from database.

Examples  :

1)      To delete all records from table

SQL> delete  from emp;

SQL> Commit;

2)      To delete  employee records whose age is more than 60

SQL> delete from emp where age > 60;

SQL> COMMIT;

3)      To delete all employee records who joined before ’01-01-1950’

SQL> delete from emp where hiredate < ’01-Jan-1950’;

SQL>  COMMIT