Constraints

SQL constraints are used to specify rules for the data in a table. These are used to limit the type of data that can go into a table. We use constraints for

* Uniqueness of values in certain column.
* Matching of column values across columns (primary key and foreign key)
* Values in certain columns with specified range.
* This ensures the accuracy and reliability of the data .

Types of constraints:

Constraints could be column level or table level.
Column level:-
If the constraints are defined with the column definition, it is called as column level constraint. Column level constraint can be applied to any one column at a time.

Table level:-
A constraint given at the table level is called as Table Constraint. It may refer to more than one column of the table. A typical example is PRIMARY KEY constraint that is used to define composite primary key. A column level constraint can be given even at the table level, but a constraint that deals with more than one column must be given only at the table level.

Commonly used Constraints:
Following are commonly used constraints available in SQL.
NOT NULL Constraint: Ensures that a column cannot have NULL value.
UNIQUE Constraint: Ensures that all values in a column are different.
PRIMARY Key: Uniquely identified each rows/records in a database table.
FOREIGN Key: Uniquely identified a rows/records in any another database table.
CHECK Constraint: The CHECK constraint ensures hat all values in a column satisfy certain conditions.

Not Null:

NOT NULL constraint restricts a column from having a NULL value.
Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column.
It enforces a column to contain a proper value.
One important point to note about NOT NULL constraint is that it cannot be defined at table level

Syntax:

SQL> CREATE TABLE CUSTOMERS(
Tname dataype     NOT NULL,
Tname dataype     NOT NULL,
Tname dataype    NOT NULL,
Tname dataype);

Example:

SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25));

To add not null constraint on existing tables column.
Syntax:

SQL> ALTER TABLE table_name MODIFY
column_name NOT NULL;

Example:

SQL> alter table test modify Age not null;
UNIQUE Constraint:

The UNIQUE Constraint prevents two records from having identical values in a particular
column. In the CUSTOMERS table, for example, you might want to prevent two or more
people from having identical age.

Syntax:
SQL> CREATE TABLE CUSTOMERS(
Tname datatype        NOT NULL,
Tname datatype       NOT NULL,
Tname datatype          NOT NULL UNIQUE,
Tname datatype);

Example:

SQL> CREATE TABLE CUSTOMERS(
ID INT                                  NOT NULL,
NAME VARCHAR (20)      NOT NULL,
AGE INT                          NOT NULL UNIQUE,
ADDRESS CHAR (25) );
If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following:

SQL> ALTER TABLE CUSTOMERS MODIFY
          AGE INT NOT NULL UNIQUE;You can also use following syntax, which supports naming the constraint in multiple columns as well:

SQL> ALTER TABLE CUSTOMERS ADD CONSTRAINT conname UNIQUE(AGE, SALARY);
           DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

Syntax:
SQL> alter table table_name drop constraint constraint_name;

Example:
SQL> ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint;

Primary key Constraint:

A primary key is a field in a table which uniquely identifies each row/record in a database table.
Primary keys must contain unique values.
A primary key column cannot have NULL values.
A table can have only one primary key.
primary key When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you can not have two records having the same value of that field(s).

Create Primary Key:
syntax :
SQL> CREATE TABLE table_name (
Column_name dataype,
Column_name dataype,
Column_name dataype,
PRIMARY KEY (ID) );
Example:
SQL> CREATE TABLE CUSTOMERS(
ID INT       NOT NULL,
NAME VARCHAR (20)           NOT NULL,
AGE INT           NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Constraint cust_const PRIMARY KEY (ID) );
To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL

Syntax:
SQL> ALTER TABLE table_name ADD CONSTRAINT PK_dept PRIMARY KEY (column_name);
Example:
SQL> alter table customers add constriant  Ps_dept primary key (ID);

Delete Primary Key:
You can clear the primary key constraints from the table.

Syntax:
SQL> ALTER TABLE table_name DROP PRIMARY KEY

Example:
SQL> alter table dept drop primary key;
                               or
SQL> alter table dept drop constraint PK_dept ;

foreign key:

A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

syntax :
SQL> CREATE TABLE table_name (
Column_name dataype,
Column_name dataype,
Column_name dataype,
PRIMARY KEY (column_name) );

SQL> create table table_name (
Column_name datatype,
Column_name datatype,
Constraint cons_name foreign_key prefremces
pri_table_name (column_name);
Example:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
Constraint cust_const PRIMARY KEY (ID) );
SQL> create table order_det (
order_id number(20),
order_name varchar2(20),
constraints for_ord foreign key(order_id) references cust(id))
Add foreign key on existing table.

Syntax:
SQL> alter table table_name add contraint
Const_name foreign key (column_name)
References pri_table_name(p_columname);
Example:
SQL> alter table oreder_details add constraint
Ord_forg foreing_key(order_id)
References cust(id);
DROP a FOREIGN KEY Constraint:
To drop a FOREIGN KEY constraint, use the following SQL:

Syntax:
SQL> ALTER TABLE table_name DROP FOREIGN KEY;

Example:
SQL> alter table order_det drop foreign key;

CHECK Constraint:

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Syntax:
SQL> CREATE TABLE table_name (
Cname datatype    NOT NULL,
Cname datatype    NOT NULL,
Cname datatype    NOT NULL CHECK (AGE >= 18));
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years:

SQL> CREATE TABLE CUSTOMERS(
ID INT    NOT NULL,
NAME VARCHAR(20)    NOT NULL,
AGE INT NOT    NULL CHECK (AGE >= 18));
If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column

SQL> ALTER TABLE table_name MODIFY column_name NOT NULL CHECK (column_name >= 18 );You can also use following syntax, which supports
naming the constraint in multiple columns as well:

SQL> ALTER TABLE table_name ADD CONSTRAINT constraintname CHECK(colname >= 18);
To drop a CHECK constraint, use the following SQL.

Syntax:
SQL> ALTER TABLE table_name DROP CONSTRAINT const_name;

Example:
SQL> alter table cust drop constraint check_my;

Basic Linux Commands II

File Related Commands:

Touch:

Create single and multiple file at a time

# touch <file name>
# touch <file1> <file2> <file3>
# touch {1..100}

cat:

Can create a file.
Appending text to a file.
View or Display or Read.
Can Combine two files data into third file.
# cat <file name>           (to view or read file)
# cat > (file name)           (to create a file) and for saving “ctrl+ d”.)
# cat >> <file name>           (to append text to existing file)
# cat <f1> <f2> >> <f3>           (to add f1 f2 to f3)
# cat -n <file name>           (view file with line no.)
# cat <f1> <f2>           (view multiple files)

vi (editor):

You can use vi editor to edit an existing file or to create a new file from scratch. You can also use this editor to just read a text file.

vi editor modes:

Insert mode
command mode
Execute mode

Insert mode or write mode:

Used for entering test.
When you first enter the editor, you are in the command mode.
To enter the write mode, type the letter ”I” for inserting text.

Command mode:

This mode enables you to perform administrative tasks such as saving files, finding and replacing. In this mode, whatever you type is interpreted as a command.
To enable this mode press esc+shift+; 

Execute mode:

In this mode you can delete, copy, paste, replace etc. words or lines in a file.
For enabling the mode press “esc” button.

Commands in vi:

• To copy a line           “esc yy”
• To copy 3 lines           “esc 3yy”
• To paste a line           “esc p”
• To page down           “esc+shift+g”
• To page up           “esc+shift+h”
• To insert a new line below           “esc+o”
• To insert a new line above           “esc+shift+o”
• To delete a line           “esc+dd”
• To delete a multiple lines           “esc+4dd”
• To delete a character           “esc+x”
• To delete a word           “esc+dw”
• To undo changes           “esc+u”
• To replace a character       “esc+r <new character>”
• To replace a word in a line      “shift+:s/search/newword/g”
• To find and replace word in file  “shift:%s/search/newword/g”
• To search a word           “shift+:/word”
• To go to a particular line           “shift+:15”
• To set line number           “shift+: set nu”
• To unset number           “shift+:nonu”
• To save a file without quite           “shift+:w”
• To quite without save           “shift+:q”
• To save and quite           “shift+:wq”
• For forcefully save, quite, save and quite  “shift+:w! or q! or wq!”

For deletion “rm”:

This command used to delete a file or directory.
# rm <file name>
# rmdir <directory name>          (delete empty dir.)
# rm -rf <directory name>               (force fully delete dir)
# rm –rf *           (delete all files or directories)

Copy “cp”:

This command is used to copy any file or directory.
# cp <file name> <destination>
eg: 

# cp test.txt /u01

# cp -r <directory name> <destination>

eg:

# cp -r /u01/testdir  /u02
# cp –r * <destination>
# cp <f1> <f2> <f3> <destination>

Cut or Move “mv”:

This command is used to cut/move any file or directory.
# mv <file name> <destination>
# mv –r <file name> <destination>
# mv * <destination>
# mv <f1> <f2> <f3> <destination>

Grep:

This command used to find particular word in a files.
# grep word <f1> <f2>
# grep –i word <f>          (for ignoring case)

File viewing Commands:

Head

This command is used to view top line of a file.
The default is 10 line
We can view more numbers of line by using option.

# head <file name>
#head -50 <file name>            (to view 50 lines)

Tail:

This command used to view bottom lines of any file.
The default is 10 line
We can view more numbers of line by using option.

# tail <file name>
# tail -50 <file name>           (to see last 50 lines)

Listing files/directories “ls”:

This command is used to list files or directories.
# ls
# ls –a            (lists hidden files)
# ls –ltr              (list files with details)

Pwd:

This command used to see present working directory.
# pwd

RPM (Radehat Package Manager)

Package Management

RPM (Redhat Package Manager)

BY using RPM we can
•Install new applications
•Upgrade existing applications
•Remove installed applications
•Query package. RPM is both installation method and package format.

RPM Label Pattern

RPM Installation:

•To install package
# rpm <options> <package>
# rpm –ivh bind-9.2.4.28.el4-i386.rpm

Options: 

-i To install
-u To update existing package
-h Display progress in hash.
-v verbose

To install RPM forcefully. 

# rpm –ivh bind-9.2.4.28.el4-i386.rpm - - force

•To install RPM without dependency. 

•# rpm –ivh bind-9.2.4.28.el4-i386.rpm - - nodeps

Querying RPM

# rpm <options> <packagename>
•#rpm -qa # rpm –ivh bind-9.2.4.28.el4-i386.rpm

Options:
- q availability of package
-qa display all install package
-qi display complete info of package

Remove RPM

•To remove RPM we use
# rpm <options> <packagename>
#rpm -e bind-9.2.4-28.el4
•To remove RPM without dependancy:
# rpm –e bind-9.2.4-28.el4 - - nodpes