Sunday 17 January 2016

Regular Expressions

What Are Regular Expressions?
A regular expression is a pattern template you define that a Linux utility Uses to filter text. A Linux utility (such as the sed editor or the gawk program)matches the regular expression pattern against data as that data flows Into the utility. If the data matches the pattern, it’s accepted for processing.
              If the data doesn’t match the pattern, it’s rejected. The regular expression pattern makes use of wildcard characters to represent one or more characters in the data stream.
Types of regular expressions:

There are two popular regular expression engines:
  • The POSIX Basic Regular Expression (BRE) engine
  • The POSIX Extended Regular Expression (ERE) engine
Defining BRE Patterns:
The most basic BRE pattern is matching text characters in a data stream.
Eg 1: Plain text

$ echo "This is a test" | sed -n ’/test/p’
This is a test.
$ echo "This is a test" | sed -n ’/trial/p’
$
$ echo "This is a test" | gawk ’/test/{print $0}’
This is a test.
$ echo "This is a test" | gawk ’/trial/{print $0}’
$
Eg 2: Special characters

The special characters recognized by regular expressions are:
.*[]^${}\+?|()
For example, if you want to search for a dollar sign in your text, just precede it with a backslash character:
$ cat data2
The cost is $4.00
$ sed -n ’/\$/p’ data2
The cost is $4.00
$
Eg 3: Looking for the ending

The dollar sign ($) special character defines the end anchor.

$ echo "This is a good book" | sed -n ’/book$/p’
This is a good book
$ echo "This book is good" | sed -n ’/book$/p’
$
Eg 4: Using ranges

You can use a range of characters within a character class by using the dash symbol.
Now you can simplify the zip code example by specifying a range of digits:
$ sed -n ’/^[0-9][0-9][0-9][0-9][0-9]$/p’ data8
60633
46201
45902
$
Extended Regular Expressions:

The POSIX ERE patterns include a few additional symbols that are used by some Linux applications and utilities. The gawk program recognizes the ERE patterns, but the sed editor doesn’t.
Eg 1: The question mark

The question mark indicates that the preceding character can appear zero or one time, but that’s all. It doesn’t match repeating occurrences of the character:
$ echo "bt" | gawk ’/be?t/{print $0}’
bt
$ echo "bet" | gawk ’/be?t/{print $0}’
Bet
$ echo "beet" | gawk ’/be?t/{print $0}’
$
$ echo "beeet" | gawk ’/be?t/{print $0}’
$
Eg 2: The plus sign

The plus sign indicates that the preceding character can appear one ormore times, but must be present at least once. The pattern doesn’t match if the character is not present:
$ echo "beeet" | gawk ’/be+t/{print $0}’
beeet
$ echo "beet" | gawk ’/be+t/{print $0}’
beet
$ echo "bet" | gawk ’/be+t/{print $0}’
bet
$ echo "bt" | gawk ’/be+t/{print $0}’
$
Eg 3: The pipe symbol

The pipe symbol allows to you to specify two or more patterns that the regular expression engine uses in a logical OR formula when examining the data stream. If any of the patterns match the data stream text, the text passes. If none of the patterns match, the data stream text fails.
The format for using the pipe symbol is:
expr1|expr2|...
Here’s an example of this:
$ echo "The cat is asleep" | gawk ’/cat|dog/{print $0}’
The cat is asleep
$ echo "The dog is asleep" | gawk ’/cat|dog/{print $0}’
The dog is asleep
$ echo "The sheep is asleep" | gawk ’/cat|dog/{print $0}’
$

Eg 4: Grouping expressions

When you group a regular expression pattern, the group is treated like a standard character. You can apply a special character to the group just as you would to a regular character.
For example:
$ echo "Sat" | gawk ’/Sat(urday)?/{print $0}’
Sat
$ echo "Saturday" | gawk ’/Sat(urday)?/{print $0}’
Saturday
$

Shell Scripts for beginners

Sample 1 :Interactive Shell scripts
#SS1
#Usage : SS1
# An Interactive Shell Scripts
echo What is Your name\?
read name
echo Hello $name. Happy Programming.
Instructions : Type the same in an Notepad and save the file with the extension (.sh)
./SS1
SS1:execute permission Denied (By default every file gets created with the permission 666 i.e., rw-rw-rw-).
the next thing that we got to do is
$umask
0022 ( with this we can read and write the file but we can execute it)
$ chmod 744 SS1(with this option we get the read,write,execute option open up for our file).

Sample 2 : Arithmetic in Shell Scripts
#SS2
#Usage : SS2
#Example of Arithmetic Operations
a=20 b=10
echo ‘expr $a + $b’
echo ‘expr $a - $b’
echo ‘exp $a * $b’
echo ‘expr $a /$b’
on execution of this shell scripts we get the following output :
30
10
200
2

Sample 3 :  if – then –else – fi  statement
#SS3
#usage : SS3
Enter the Source and target file names
read source target
if cp $ source $ target
then
echo file copied successfully
else
echo failed to copy the file
fi
for further Study on the Unix Shell Scripting one can contact us

Unix Related Data

Why do we Use Shell Scripting

We use Shell Scripts to obtain the following purposes.
1. Customizing your work environment.
2. Automating Your Daily Task.
3.Automating Repetitive Task.
4.Executing Important Procedures like shutting down the system,formatting a disk,Creating a file system on it,mounting the file system,letting the users use the floppy and finally un mounting the disk.
5.Performing the same operation on many files.

When Not to use Shell Scripting :
  1. Is too complex,such as writing an entire billing system.
  2. Requires a high degree of efficiency
  3. Requires a variety of Software tools.
Usage of Shell Scripts with in Informatica:
1.To run an batch via pmcmd.
2. To have header and footer in case we are goanna write to an flat file
3.To run the command task.
4. To update parameter file with session start time and end time.

============================================================================

Basic Commands Part1

CommandOptionDescription
1. Uname
uname -r
uname -n
To find the version of unix os
To find the machine name
2. Man
 
man grep
man -e grep
man -k copy
Print the user manual for any command.
To find the user manual for grep command.
One line definition of grep usage.
Helpful when we donot know which command will solve the purpose.e.g :- gives the different option available for copying.
3.Cal
 
cal 2009
cal dec
Print the calendar year or month
Print the calendar 2009 year
Print calender for current december month
4. Date
date
date +%m
date +%h
date +'%h%m'
date +%d
date +%y
H,M and S
Tue Mar 09 07:10:10 IST 2009
3
March
Mar-03
10
9
 
5.Who
who
who am I
who -H
who -u
Login details of all the users in system
Login details for only myself
Login details for all users with headers info
Login details for only active users
6.Passwd
To change the password
7.Lock
lock -45
lock the terminal for 45 minutes
8.Bc
To use the calculator
9.ls
 
ls -x
ls -F
ls -a
ls -r
ls -x abc
ls -R
ls -ld
ls -il testfile
ls -lt
ls -ltr
ls -lut
To Print the contents of current directory.
Prints Multi column output of files with their names.
To Print only directoris and executable files.< * > indicate all files containing executable code and </> indicate the directories.
Print all files including hidden files. <a> stands for all.
Reverse the sort order of all the files with their names.
Print the content of abc directory.
Print all files and sub-directories in directory TREE.
Print all the directories.
Prints the inode number of file.
Prints all the file with their last modification time keeping latest modified at top.
Prints all the file with their last modification time in reverse order keeping last modified at bottom.
prints last access time of files.
10.Cat
 
cat testfile
cat > testfile
Print the content of any file.
Create a new file with testfile name.
 
11.CP
 
cp -i testfile abc
cp -r abc newabc
To copy file and directories.
copying testfile to abc directory interactively.
copying entire directiry structure abc to newabc recursively.
12.rm
 
rm -f newabc
rm -r newabc
All the options of cp command is applicable for rm command.
Removing directory newabc forcefuly.
Recursive deletion will not remove the write protected files.
13.lp
Used for Printing the files
14.Split
 
split testfile
split -72 testfile
split testfile newfile
To split the big files in different small files of 1000 lines each<default>.
Split the files into small files.
change the default split line size to 72.
Renaming the testfile to new name as <newfile>.
15. Cmp
    comm
    Diff
These three command are used to find the difference in two files
16.wc
 
wc -w testfile
wc -c testfile
wc -l testfile
wc testfile
Print the numbers of words, lines and character in the file
17.PathPATH=$PATH:/home/
gagan:/home/deep
Set the path variable for SHELL to use these directory path to locate the executables commands.
18.stty
stty -a
 
stty intr \^c
stty eof \^a
Print current setting of terminal
To delete the character during backspacing
To change the interrupt key to Ctrl c instead of 'DELETE' default
To change the termination control of input during file creation using cat command from Ctrl d to Ctrl a.
19.Alias
alias
alias l ='ls -ltr'
alias showdir ='cd $1;ls -l'
unalias
Print all the alias set in the system.
To create short hand names of command.
Passing positional parameter ETL to showdir alias will take us to that directory.
To redefine and unset the alias.
20.History
 
history -5
history 10 15
HISTSIZE 1200
To find the previous command that has been used.
Print the last 5 command that has been used.
Every command has event number. Display event number between 10 an d15.
To change the defalult setting of command history saving feature.
21. r
      !!


r-2
r 20
Repeat previous command in Korn
Repeat previous command in Bash
Repeat previuos to prevoius command
Repeat the command with 20 event number.
22.Tilde(~)
Changes control to home directory
Changes absolute path home directory of user a_cmdb, the value of home directory can be seen by $HOME environment variable
23.cd -
To switch between current working directory and most recently used directory.
24.chmod
 
chmod u+rwx newfile
chmod g+x newfile
chmod 757 newfile
chmod 457 newfile
chmod -R a+x abc
chmod -R 001 .
Changing the permission of file for user <u>, group <g>, others <o> or all <a> with + as assign , - as remove and = as absolute permission.r read<4>, w write<2> and x execute<1>.
 
 
 
 
Recursively changes the permission to execute for all directory and sub directory in abc directory for all users.
. Indicate the current directory.
=================================================================================

Basic Commands Part2

CommandOptionsDescription
25.Head
 
head newfile
head -5 newfile
head -1 newfile | wc -c
vi 'ls -t | head -1'
head -c 512 newfile
head -c 1b newfile
head -c 2m newfile
Display top content of file
Display top 10 lines of file when used without argument
Display top 5 lines of file<newfile> when used without argument
To count the number of character present in first line.
To open the last modified file for editing.
To pick special no of character from file.
Print first 512 byte character
Print first 2 blocks (1 MB each)
26.Tail
 
tail -5
tail +10
tail -f 6_load.log
Display end of file
Display last 5 lines of file.
start dispplaying 10 line onward till the end.
Print the gworth of file
27.$$
Prints the Process id of current shell
28.ps
 
ps -f
ps -f -u a_cmdb
ps -a
ps -e
 
Prints the Processes associated with current user.
Prints the Processes associated with current user with their hierarchy.f stands for full.
Prints all the processes associated with a_cmdb user.
Prints all the users processes.
Prints system process.
prints all the ksh process running in the system by first searching for all the process.
29.Background job
 
ps -f
ps -f -u a_cmdb
ps -a
ps -e
 
Running job in background
Run the search job in background by printing its process id.Shell become the parent of all background process.
This command print all the jobs runnung in background with their sequence no of exceution.
Running fg will bring most recently started background process <LAST IN FIRST OUT>
First job will come into foreground.
30.nohup
 
nohup ps -eaf | grep 'ksh' &
Shell is parent of all background jobs and it dies when user logs out and ultimately all the child process also dies.To avoid dying of shell even when the user logs out run the background process using nohup command. 
System process init having PID as 1 is parent of all SHELL processes and when the user login this PID become the PPID of SHELL prrocess. Now after running the process in nohup mode kernel has reassigned the PPID of ps process with PID of system process which will not die even after SHELL dies through logging out.
31.Kill
 
kill 520
kill 640
kill 1
kill $!
kill 0
 
To kill the command.
Kill the process with 520 process id.
Kill the parent process id which inturn kill all the child process.
Killing system process init is not possible. A Process having process id as 1 is parent of all SHELL processes.
No need to remember Process id of last background procees its in $!
Kill all process in the system except login shell process.
$$ stores the PID of current login shell
32.Nice
 
nice who | wc - l &
nice -n 10 who | wc -l &
Running the command with low priority
Nice value range from 1 to 39, higher the nice value lower the priority.default nice value is 20.
nice value becomes 30.
33.At
 
at 2:10 load.ksh
To Run the job at specied time of the day.
Indicate the load.ksh will execute today by 2:10
34.Batch
batch load.ksh
batch command executes the process whenever the CPU is free.
35.Cron
cron executes process at regular intervals it keeps checking the control file at /user/spool/cron/crontabs for jobs.
36.Finger
finger
finger a_cmdb
Produces list of all logged users when run without any argument. The 2nd and last field of output is taken fron passwd file from /etc directory. 
Prints details about a_cmdb user.
37.Set
 
set 10 20 30
-x
set the posional parameter.
This will set $1, $2, $3 , $* and $# posional parameter
When this statement is used at start of ksh script it echoes each statement at the terminal
38.Shift
 
shift
shift 2
shift command transfer the contents of positional parameter to next lower number.
shift one position.
shift 2 places
39.Chown
 
chown jaspreet testfile
chown -R jaspreet testfile
To change the owner of file can only be done by owner or sys admin. 
change the owner from gagan to jaspreet for testfile. Now gagan cannot change the ownership of test file what he can do is just create the similar copy of file and then he can become the owner of file. 
Recursively changes the owner all files under current directory.
40.Chgrp
 
chgrp GTI test file
chgrp -R GTI test file
To change the group of file can only be done by owner or sys admin.
group changed to GTI from IB as user is still the same and he can again change the group of file to IB.
Recursively changes group all files under current directory.
41.Touch
 
touch 03161430 test file
touch -m 04161430 test file
touch -a 05161430 test file
Changing the time stamps of file
touch without any argument will change both modified time and access time.
Changes only modification time.
Changes only access time.
42.Linking in
 
ln 6_load.ksh 7_load.ksh
rm 6_load.ksh
Linking two file, doing this both the file will have same inode number and changes in one will reflect in another also. 
ls -li 6_load.ksh 7_load.ksh will give us same inode number. 
Drop the link between two files.
43.df
 
df -t /home/oracle
Prints the amount of free space available on the disc.
Prints the free and total space under oracle file system.
44.du
 
 
du -s
du -s /home/*
Prints the disk usage of specific directory tree.
By default du prints disk usage for each sub directory.
Prints summary of whole directory tree.
Prints disk usage for every user
==============================================================================

Zipping Files

CommandOptionsDescription
1. gzipgzip etl_code
eg:etl_code.gz
 
 gunzip etl_code.gz
eg: etl_code
 
 zip file*sql
eg: file.zip
 
 unzip file.zip
eg:file*sql
 

  
2. tar create backup of files recursively.
 -cvf
eg :tar -cvf /home/gagan/sqlbackup ./*.sql
 
 - x
eg:tar-xcvf /home/gagan/sqlbackup ./*.sql
files are restored using -x option.
 
 

3. Cut
-c <column start and no>

eg : cut -c -5,6-12 test file
column (by specifying position). C stands for column cut.
 -f <field start and end no>

eg:cut -f 1,5 test file
field (default deliminator tab). F stands for field cut.
 '-d -f <field start and end no>

eg: cut -d "|" -f 1,5 test file | new file
Cut the field b/w 1 and 5 and piped the output to new file.
   
4. sortsort test fileBy default the sorting starts with first character of each line. Priority1.space, tabs 2. numerals 3.uppercase letters 4. Lower case letters.
 -t
eg : sort -t "|" +2 test file
Sorting starts from 3rd field skipping 2nd field, overring the default.delimiter to distinguish b/w start and end of field.
 -r
eg : sort -t \| -r +2 test file
Reverse sort starting with 3rd field.
 eg : sort -t \|  +2r test fileThe above command can be written in another way.
 -o
eg : sort -o abc.txt abc_sort.txt
save sorted data in file.
 eg : sort -t "|" +1 -2 +3 <>Sorting based on different field as in case of order by clause.Sorting starts with 2nd field then with 4th field, -2 indicate to stop the sorting after 2nd field and resume it with 3rd field.
 -n
eg : sort -n <>
Numeric sort.
 -u
eg : sort -u <>
Unique sort.
   
5.paste-d

eg:paste -d "|" <> <> 
deliminator.

  
6.treg : tr '|\' '~-' <test.txttranslate all | with ~ and \ with - .
 eg : tr '[a-z]' '[A-Z] <test.txttranslate to upper case.
 -d
eg : tr -d '|' test.txt
delete all occurrence of | .
 
 
7.uniq Unique require sorted file as input.
-u

eg : cut -d "|" -f3 <>|sort|uniq -u
remove duplicate.
 -d

eg: cut -d "|" -f3 <>|sort|uniq -d
select only dup records.
 -c

eg : cut -d "|" -f3|sort|uniq -c
duplicate  count.
   
8.Changing time stamp touch mon date hrs mins <file>.
ls -lttime of last modificationtouch -m 01290430 <file>.
ls -lutime of last accesstouch -a 01290415 <file>.
  
9.Change Datedate 09181754 
   
10.wallwall -g dba "hello"to selectively send msg to dba group.
   
11.shutdownshutdown -g2power down after 2 mins.
 shutdown -y -g0immediate shutdown.
 shutdown -y -g0 -i6shutdown and reboot (init level 6).
 shutdown 17:30shutdown at 17:30.
 shutdown -r nowshutdown immediate and reboot.
   
12. du Disk usage.
 du /home/expimp/create_dbtree output for each directory inside.
 du -s //home/expimp/create_dbsummary.
   
13.findfind <loc> <option> <pattern> <action>find in root dir abc in emp.lst file
 '-mtime = mod time
eg: find . -mtime -2 -print
find file modi in less then 2 days.
 'atime = access time
eg: find . -atime +365 -print
find the file not accessed in last 1 year.
 ! –newer
eg: find / -name "*.pl" ! -newer last_backup -print
file modi before last_backup.
 -size.
eg: find . -size +2048 -print
files greater then x blocks.
 -a (and) -o (OR)
eg: find . \( -name "*.sh" -o -name "*.lst" \) -print
double quotes necessary.
 -exec
eg: find . -atime +180 -ok rm -f {} \;
remove the files which are not modi for last 20 days
 -ok
eg: find . -atime +180 -ok rm -f {} \;
before removing prompt for confirmation.
 xargs
eg: find . -mtime +20 | xargs rm -f
remove all file rm will be executed only once.
 xargs -n -p –t
eg:find . -mtime +20 | xargs -n20 -p -t rm -f
remove at max 20 files in batch and in interactive mode.
 -type
eg: find / -name log -type f -print
f for file and d for directory.
 -prune
eg: find . -name *.log -prune exe –print.
don't descend exe directory.
=============================================================================

Pattern Searching

CommandsOptionsDescription
//UnixForward search of Unix keyword in file
??UnixBackward search of Unix keyword in file
n Repeat the last search
. Repeat the previous command

  
:1, $s/<search string>/<Replace String >/g Pattern search and replacement.
 1,$Represent all lines in the file.
 gStands for globally
:3,10s/gagan/deep/g search between lines 3 and 10
: .s/gagan/deep/g Only the current line.
: $s/gagan/deep/g Only the last line.
: $s/gagan/deep/gccAsk for confirmation for replacement

  
grep  
 -ccounting occurrence.
 -ndisplay line number for record.
 -vskip records that contain directory.
 -ldisplay files containing record.
 -iignore case.
 [PQR]match any single character.
 [c1-c2]match char with ASCII range
 [^PQR]match single character which is not PQR.
 ^<pat>beginning with pattern.
 <pat>$ending with pattern.
 {a-Z A-Z 0-9}match any single character.
 ls –l |grep “^d”Prints only directories.

  
egrep  
 egrep '[aA]g+[ar][ar]wal' test1.txtmatch one or more occurrence matches ag & agg.
 egrep '[aA]gg?[ar][ar]wal' test1.txtmatch zero or one occurrence.
 egrep prashant|director test1.txtmatch eap1 or eap2  (finds prashant or line with director)
 egrep (das|sen)gupta test1.txtmatch exp x1x3 or x2x3 (like dasgupta & sengupta)
 egrep -f <pattern_file_name> test1.txtHuge list of pattern search can passed in the form of file name .pattern stored in file eg(prashant|admin|director)
 +Matches one or more occurrence of previous character.
 ?Matches zero or one occurrence of previous character.

  
Fgrep fgrep and egrep accepts multiple pattern both form command line and a file but unlike grep and egrep  does not accept regular expression.
 fgrep -f pattern file emp fileFaster than grep and egrep family
========================================================================

Pattern Matching

 

CommandOptionsDescription
* Matches any number of character including NONE. * does not match all files beginning with dot <.>.
 ls -l chap*Matches all the files which starts with chap.
 ls -x chap*Matches all the files which starts with chap and prints in multi column way.

  
? Matches single character.
 ls -l chap?Matches all the files with only 5 character name and should start with chap.
  
[ ijk ] Matches single character either i or j or k.
[ !ijk ] Matches single character that is not i or j or k
[ x - z ] Matches single character that is not within the ASCII range of character x and z.
[ ! x - z ] Matches single character that is not within the ASCII range of character x and z
   
 ls -l chap0[1 - 4]Range specification is also available.
 ls -l [ a – z A-Z]Matches all file names beginning with alphabet irrespective of case.
 ls -l chap[!0 - 9]Matches all file names beginning with alphabet chap and not ending with any number.
 ls *.istPrint all the files which end with ist extensions.
 cp chap?? abcCopy all files starts with chap to abc directory.
 cmp chap[12]Compares file chap1 and chap2.
 mv *  ../binMoves all the files to bin directory.
 cat chap[!0 - 9]Concatenates all the files beginning with chap and not ending with number.
 ls -l ** does not match all files beginning with dot <.>.
 ls -l .???*The above problem can be solved with specifying first few character using meta character <?> explicitly.
   
Escaping Backslash ( \ ) Playing with file names which uses meta character in their file name.
 ls - l chap*Print all files whose names start with chap but not the one whose name is chap*.
 ls - l chap\*The above problem can be solved by escaping the special character.
   
Pipe | To pass the standard output of one command as the standard input to another.
 who | wc -lOutput of who command <three users> passed as input to wc which count the number of lines present.
 
 
Teewho | tee users listTee saves the output of who command in user list as well as display it also.
 
 
Shell Variable Shell variables are initialized to null value < by default > so it returns null. All words starts with $ are considered as variable unless single quoted or escaped.
 a=ab, b=cd, z=$a$b. echo $zshell concatenate two variable.
 echo '$10'

eg: $10
All words starts with $ are considered as variable unless single quoted or escaped.
 echo "$10"

eg: 0
Shell is looking for $1 variable which is undefined so passes null for this. $1 is part of positional parameter.
=============================================================================

Control Structures






Operators

1. Relational Operators ( >,>=, <, <= , < > or != (not equal to) )
2. Logical Operators ( AND, OR, NOT )
3. Special Operators (IN, BETWEEN, LIKE and Is )
Queries using Where Clause( Relational & Logical ) 
  1. List The Employees Belonging To The Department 20
  • Select * from emp where deptno = 20 ;
  1. List The Name And Salary Of The Employees Whose Salary Is More Than 1000
  • Select ename, sal from emp where sal > 1000 ;
  1. List The Names Of The Clerks Working In The Department 20
  • Select ename, job from emp where job = ‘Clerk’ and deptno = 20 ;
  1. List The Names Of Analysts And Salesman
  • Select ename, job from emp where job = ‘ANALYST’ or job=‘SALESMAN’ ;
  1. List the details of the employees who have joined before the end of september 1981.
  • Select * from emp where hiredate <= ’30-Sep-1981’ ;
Using Special Operators
IN : The IN operator is used to determine if a given value matches any value in a sub query or in a list
Syntax : [ Not ] In <List of Values>
Examples :
  1. List the employees details whose employee id’s are 7369,7499,7788
  • select * from emp where empno=7369 or empno=7499 or empno=7788;

Using In Operator
  • select * from emp where empno in (7369,7499,7788);
  1. List the employees details whose employee id’s are not 7369,7499,7788
  • select * from emp where empno!=7369 and empno!=7499 and
empno!=7788;

Using In Operator
select * from emp where empno not in (7369,7499,7788);
Between
The Between operator is used to specify a range of values. It retrieves the values by depending on the condition, on the range of any column
Syntax : [ Not ] Between <Begin Expression And End Expression>
Note : Unlike in ordinary English, “Between” is an inclusive operator ie both the values will be included in the range. The Not Between is an Exclusion operator
Examples :
  1. List the employees details whose salary is >=3000 and <=5000
  • select * from emp where sal>=3000 and sal<=5000;
Using Between Operator
  • select * from emp where sal between 3000 and 5000;
  1. List the employees details whose salary is not between 3000 and 5000
  • select * from emp where not sal>=3000 and sal<=5000;
Using Between Operator
  • select * from emp where sal not between 3000 and 5000;
Like Operator
The Like Operators is only used with Char and Varchar2 data types to match a pattern of characters
It determines whether or not a given character string matches the specified pattern.
A pattern can include regular characters and wild card characters. During pattern matching regular characters must exactly match the characters specified in the character string.
Oracle supports two types of wild cards, they are
  1. _ ( underscore ) : Represents a Single Character
  2. % ( Percentage ) : Represents Multiple Characters
syntax : [ Not ] Like < Pattern >
Note : This Like Operator can only be used with Varchar2 and Char data types only.
Examples :
  1. List the details of the employees whose names starting letter is ‘S’
  • select * from emp where ename like ‘S%’;
  1. List the details of the employees whose names Ends with letter ‘N’
  • select * from emp where ename like '%N';
  1. List the details of the employees whose names Second Character is letter ‘A’
  • select * from emp where ename like '_A%';
  1. List the details of the employees whose names Exact Length is 5 characters only
  • select * from emp where ename like '_ _ _ _ _';
The special operator “is” is used with the key word “NULL” to locate Null Values
Is Operator
This operator is used to find or locate the Null values in the given column or expression
Examples :
  1. List the details of the employees who are not Eligible for Commission
  • select * from emp where comm is NULL;
  1. List the details of the employees who are Eligible for Commission
  • select * from emp where comm is not NULL;
  1. List the details of the Manager who are Eligible for Commission
  • select * from emp where job='MANAGER' and comm is null

Division of SQL

There are three major categories:
1. Data definition language (DDL): This is used a set of commands that defines data base objects. (Create, alter, drop and Rename).
(i) Create table Command : The Create Table Command defines each column of the table uniquely. Each column has a minimum of three attributes, a name, data type and size (i.e. column width).
Syntax :
Create table <table name> (<column1> <Data Type> [Size],
<column2> <Data Type> [Size],------------------------------<column N> <Data Type> [Size]);
Eg., Create Table Sample ( sno number(3), sname varchar2(10));
(ii) Alter Table :
It is used to change the structure of the Table i.e. adding new column, changing the data type and size. The alter command can have 3 types of sub commands. They are:

1)ADD: By using this command we can add new columns to the existing table.
Syntax:
Alter Table <Table_Name> add (Column1 <data_type>[<size>],
Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])
  1. Modify: It is used to change the Data Type and size of the existing columns. If you can change the Data Type and Size you must satisfy the following rules.
    1. By using Modify command in alter we cannot change the column name.
    2. We can not Change the Positions of the existing or new columns
    3. We cannot decrease the length (Size) of an existing column, if that column is having values. But we can increase the size of the existing column even if the data is present
Syntax:
Alter Table <Table_Name> Modify (Column1 <data_type>[<size>],Column2 <data_type>[<size>],- - - - -,Column(n) <data_type>[<size>])
3. Drop This command is introduced in Oracle 8i. It is used to remove the column of a table.
Syntax:
Alter Table <table_name> Drop column <column_name>
Adding single column :
  • Alter table students Add (total number(4));
Adding Multiple column :
  • Alter table students Add (average number(6,2),result char(4));
3. Drop Command
This command is used to drop or delete any table from the database.
Syntax: Drop table <table_name>
Example: Drop table students;
2.Data Manipulating Language (DML): These are used to view, update, adding record, and removing records (select, update, insert, delete).
(i) Select Statement :
The select statement is used to display the details of a table
Syntax :
Select [ * | Distinct | <column list>] from <table name>[ Where <condition > ][ Group by <column name(s) > ][ Having <Condition> ][ Order by <Expression > ]
Examples :
1. To display all Tables List
  • select * from tab;
2. To display a particular table details
  • select * from emp;
3. to display particular columns in a table
  • select empno, ename, job from emp;
(ii) Update Command :
This command is used to update or modify all or specified column values with new values.
Syntax:
Update < Table Name > set <column1> = <value1>, <column2>=<value2>, -------<column N>=<value N> where <Condition>
Examples:

(i)To update only one column value
  • Update sample set sno=500 where sno=100;
(ii)To update Multiple column values
  • Update sample set sno=111,sname=’ Nandhini’ where sno=500;
(iii)To update all values with same value
  • Update sample set sno=200;
(iii) Insert Command :
Syntax :
Insert into <table name>(columns list) values(sequencename.nextval,……);
eg : Insert into Student Values (100,’Naveen’) ;
(iv) Delete Command :
This Command Is Used To Delete All Or Specified Rows In A Table
Syntax : Delete From <table name> where <condition>

Eg: (i) To Delete A Single Row
  • Delete from sample where sno=109;
Eg : (2)To Delete Multiple Rows (More Than One Row)
  • Delete from sample where sno=106 or sno=108;
3.Data Control Language (DCL) or Transaction Control Language (TCL) : It supports grant, revoke , commit and role back commands.

(i) A commit statement guarantees all of the transactions, modifications are made permanent part of the data base. By default, all your transactions are temporarily stored in the database.
Syntax : commit;
(ii) This Rollback command is used to undo work done in the current transaction. Ie the user can continue with any number of inserts, updates and / or deletion, and still undo the work, issuing the Rollback Command.
Syntax : Rollback To [Savepoint] Savepoint_Name
optional
(iii) Save point command Sets a save point within a transaction or to identify a point in a transaction to which you can later roll back.
Syntax :
Savepoint savepoint_name;