Date Functions in SQL

Select Getdate() as CurrentDate;

Select Getdate() -1 PreviousDate;

Select Getdate() +1 NextDate;

Select DATEADD(dd,1,getdate()) as NextDate;

Select DATEADD(mm,1,getdate()) as NextMonth;

Select DATEADD(yy,1,getdate()) as NextYear;

Select MONTH(getdate()) as CurrentMonth;

Select EOMONTH(getdate()) as MonthEndDate;

Select EOMONTH(getdate(),1) as NextMonthEndDate;

Select YEAR(getdate()) as CurrentYear

Select DATEDIFF(dd,getdate(),eomonth(getdate())) as NoOfDaysLeftThisMonth;

Select DATEPART(year, CURRENT_TIMESTAMP) as ExtractingYear;

Select DATEPART(year, getdate()) as ExtractingYear;

SUBQUERIES PRACTISE QUESTIONS

1. Write a SQL query to find those employees who receive a higher salary than the employee with ID 7369.

SELECT * FROM EMP WHERE SAL > 

(SELECT SAL FROM EMP WHERE EMPNO = 7369)

2. Write a SQL query to find out which employees have the same JOB as the employee whose ID is 7369. 

SELECT * FROM EMP WHERE JOB = 

(SELECT JOB FROM EMP WHERE EMPNO = 7369)

3. Write a SQL query to find those employees whose salary matches the lowest salary of any of the departments. 

SELECT * FROM EMP WHERE SAL = 

(SELECT MIN(SAL) FROM EMP )

4. Write a SQL query to find those employees who earn more than the average salary. 

SELECT * FROM EMP WHERE SAL > 

(SELECT AVG(SAL) FROM EMP )

5. Write a SQL query to find those employees who report to that manager whose ENAME is ‘JONES’. 

SELECT * FROM EMP WHERE JOB = 

(SELECT JOB FROM EMP WHERE ENAME = 'JONES')

6. Write a SQL query to find all those employees who work in the SALES department. 

SELECT * FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO

AND DNAME='SALES'

OR

SELECT * FROM EMP WHERE DEPTNO =

(SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES')

7. Write a SQL query to find the employee whose salary is 3000 and empno is 7902.

SELECT * FROM EMP WHERE SAL = 3000 AND empno = 7902

8. Write a SQL query to find those employees whose ID matches any of the numbers 7369,7499 and 7521. 

SELECT * FROM EMP WHERE EMPNO IN (7369,7499,7521)

9. Write a SQL query to find those employees whose salary is in the range of 1000, and 3000 (Begin and end values have included.). 

SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 3000

10. Write a SQL query to find those employees whose salary falls within the range of the smallest salary and 2500. 

SELECT * FROM EMP WHERE SAL BETWEEN 

(SELECT MIN(SAL) FROM EMP) AND 2500

(Note : AND operators are written always outside of the subquery or closed brackets )

11. write a SQL query to find those employees who do not work in the departments where managers’ IDs are between 100 and 200 (Begin and end values are included.). Return all the fields of the employeess

SELECT * FROM EMP 

WHERE department_id NOT IN

(SELECT department_id FROM DEPT 

WHERE manager_id BETWEEN 100 AND 200);

12. From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees. 

SELECT * FROM EMP WHERE SAL  =

(SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP))

OR

select * from

(select empno, ename, sal, deptno, dense_rank() over (order by deptno) as Hsal from emp ) as E

where Hsal = 2;

(Note :- Group by will not work while check 2nd highest salary based on deptno, because operators like '=,<,<=>,>=' is not permitted. So we need to used order by dense_rank to get 2nd highest sal. An extra or external where query should be given to check Hsal )

13. From the following tables, write a SQL query to find those employees who work in the same department as ‘Clara’. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.

select first_name, last_name, hire_date from emp where department_id =

(select department_id from emp where first_name = 'clara') AND first_name <> 'clara'

(Note : AND operators are written always outside of the subquery or closed brackets )

14. From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains the letter 'T'. Return employee ID, first name and last name.

select * from emp where department_id =

(select department_id from emp where first_name  like 'T%')'

15. From the following tables, write a SQL query to find those employees who earn more than the average salary and work in the same department as an employee whose first name contains the letter 'J'. Return employee ID, first name and salary. 

SELECT employee_id, first_name , salary  

FROM employees  

WHERE salary > 

(SELECT AVG (salary)  

FROM employees ) 

AND  department_id IN 

( SELECT department_id  

FROM employees  

WHERE first_name LIKE '%J%');

16. write a SQL query to find those employees whose department is located at ‘dallas’. 

select * from emp where deptno =

(select deptno from dept where loc = 'dallas')

17. write a SQL query to find those employees whose salary is lower than that of employees whose job title is ‘CLERK’. 

Select * from emp where sal < ANY

(select sal from emp where job = 'CLERK');

Note:- ANY means that the condition will be true if the operation is true for any of the values in the range.)

18. write a SQL query to find those employees whose salary is lower than that of employees whose job title is "clerk". 

select * from emp where sal < ANY

(select sal from emp where job = 'clerk') AND job <> 'clerk'

Note:- ANY means that the condition will be true if the operation is true for any of the values in the range.)

19. write a SQL query to find those employees whose salary exceeds the salary of all those employees whose job title is "salesman". 

select * from emp where sal > ANY

(select sal from emp where job = 'salesman') and job <> 'salesman'

(Note:- ALL means that the condition will be true only if the operation is true for all values in the range)

20. write a SQL query to find those employees whose salaries are higher than the average for all departments. 

select * from emp where sal > ALL

(select avg(SAL) from emp group by deptno )

(Note:- ALL means that the condition will be true only if the operation is true for all values in the range)

Teradata

Teradata is used for massive parllel processing(MPP). There are 4 different components of teradata i,e Node, Parsing engine, Message passing unit or Bynet, Access module processor(AMP).

Node :

Parsing engine : It receives queries from clients, checks for syntax errors, checks user privilages, prepares execution plan and pass it to Bynet, lastly receives results from AMP's and sends to client.

Bynet : Message passing layer is also called as Bynet. It allows communication between PE and AMP. It receives execution plan from PE and pass it to AMP, similarly it receives result from AMP and pass it to PE.

Amp : These are called as virtual processors, they actually stores and receives the data.


Teradata supports different types of tables :-
---------------------------------------------

Permanent table : This is a default table, it contains inserted data and stored data permanently.

Volatile table : The data inserted into a volatile table is retained only during the user session. Table and data is dropped at the end of the session. These tables are used to hold the data during data transmission.

Global temporary table : Table is delete at the end of user session.

Derived table :  Derived table holds the intermediate results in a query. Their lifetime is within the query in which they are created, used and dropped.


Set Versus Multiset :-
---------------------
Teradata classifies the tables as SET or MULTISET tables based on how the duplicate records are handled. A table defined as SET table doesn’t store the duplicate records, whereas the MULTISET table can store duplicate records.

Vi Editor

VI EDITOR :-
--------------
VI editor is case sensitive, used in two modes Command mode and Insert mode. By default it starts in command mode, to enter text you must enter into the insert mode. Press letter 'i' to enter into the insert mode. Press 'esc' key to come out of the insert mode, back to the command mode.


COMMANDS :-
---------------

vi Filename : Opens existing file and creates new file, if no file exists.

vi -R Filename : Opens existing file in the read only mode.

vi Filename : Opens existing file in the read only mode.

:w : This command is used to save content of the file.

:q : This command is used to come out of vi editor.

:wq :  This command is used to save and quit from the content of the file.

:q! : This command is used to come out of vi editor, if the file is modified without saving it.  

ctrl + zz : This command is used to save and quit from the content of the file.

:w filename : This command is used to save the specific file.


MOVE WITHIN THE FILE :-
-----------------------------

To move the within the file, you must be in command mode. So press Esc key twice followed by the below words.

k : Moves the cursor one line up.

j : Moves the cursor one line down.

h : Moves the cursor to the left one character position.

i : Moves the cursor to the right one character position.


EDITING THE FILES :-
------------------------

To edit file you need to be in insert mode. Many ways to enter the data into the files are mentioned below.

i : Used to enter into the insert mode and type data as per our requirement.

l : Used to enter text at the begining of the current line.

A : Used to enter text at the end of the current line.

o : Used to create new line below the cursor location.

O : Used to create new line above the cursor location.


DELETING THE CHARACTERS :-
----------------------------------

x : Used to delete the characters under the cursor location.

X : Used to delete the characters before the cursor location.

dw : Used to delete current cursor location to the next word.

d^ : Used to delete current cursor location to the begining of the line.

d$ : Used to delete current cursor location to the end of the line.

D : Used to delete current cursor position to the end of the current line.

dd : Used to delete the line the cursor is on.

Unix Interview Questions

* Command to get Header in unix 
'Head' is used to get header in a file

* Command to check the status of your job 
'Jobs' Command

* Command to stop current task 
'Ctrl+z' Command 

* Command to get current task to back ground
'Bg Jobname' Command

* Command to bring back ground process to fore ground
'Fg Jobname' Command

* Command used to search a string in a file
'Grep' is the command used to search a string in a file.

* SED, AUK command in unix ?

* Have you worked on shell script ?

* Unix command used to save 
Ctrl+D is the command used to save file

* Unix command to clear screen
Ctrl+L command is used to clear screen

* Unix command to display unique line
uniq myfile.txt

* Unix command to display duplicates
uniq -D filename is used to display on duplicates

* 'Touch filename' command result if file already exist
When you run touch on an already existing file, the file's access and modification timestamps are updated to the current time. 

* How to display hidden files
Ls -a

* Command used to kill the running task
Kill or Kill -9

* How to find PID of the process 
PID means process id, unique number of the process. 
PS -EF is used to find pid of the process.

* Command used to transfer files from one server to another server  
FTP command is used

* Command used to know how long server is running
UPTIME command is used

* How do you truncate a file in unix ?
truncate -s 0 filename is used to truncate a file. Here -s means size, 0 is the value of the file to be set. 

* Which unix command is used to enter text at the end of the file?

$ echo "Hello World" >> file.txt 

$ cat file.txt

This is the first line. 

Hello World

* IF FILE IN UNIX CAN'T OPEN, BECAUSE OF NO PERMISSION. HOW TO  RECTIFY IT ?
 
* IF FILE IN UNIX CAN'T OPEN BECAUSE ITS A BIG FILE. HOW TO RECTIFY IT ?
 

Basic unix commands

Basic unix commands :-  
--------------------------

* Man command:

  It will gives DESCRIPTION and options how to use any command.

  eg:- man ls

         man cat

* Pwd : 

   It displays Present working directory or current directory.

* Ls Command :

  The 'ls' command is used to list files and directories.

  ls -a list all files including hidden file starting with '.'.

  ls -d list directories - with ' */'.

  ls -l list with long format - show permissions.

  ls -F Append indicator (one of */=>@|) to entries.

  ls -lh This command will show you the file sizes in human readable format.

  ls -r list in reverse order.

  ls -i list file's inode(index) number.

  ls -ltr View Reverse Output Order by Date.

  ls -t sort by time & date.

  ls -n It is used to print group ID and owner ID instead of their names.

  ls -m A list of entries separated by commas should fill the width.

  ls -g This allows you to exclude the owner and group information columns.

  ls -q Force printing of non-graphic characters in file names as the character `?';.

  ls -Q Place double quotations around the entry names.

Cd directory_name : To change the current directory to another directory.
Cd .. : It brings you out from your current directory but takes you only 1 directory ahead.
Cd .. /.. : It brings you out from your current directory but takes you 2 directories ahead.
Cd ~ : It is used to go back to your default directory or location.
Cd - : It is used to go back to your current directory or location.

Who : It displays how many users are currently logged in.
Who -q : It displays how number of users are currently logged in.
Whoami : It displays user who is logged in.

Cal :- It displays current month calender.
Cal -y :- It displays current year calender.

Date : It displays day, month, time with minutes and seconds along with year.

Cat : It is used to create, display and append the files.
Cat > filename : It is used to create a new file.
(Cat > filename)

Cat filename : It is used to display a file.
(Cat filename)

Cat -n filename : It is used to display lines along with line numbers.
(Cat -n filename)

Cat filename1 > filename2 : It is used to create file2 and inserts file1 data into it 
(Cat filename1 > filename2)

Cat >> filename : It is used to append or combine files.
(Cat >> filename
or
Cat file1 file2 >> file3
Appending data to the existing file)

Touch : It's a command used to create empty file.
Touch file1 file2 file3 : It create 3 empty files.
Touch -a : It is used to display last accessed time.
Touch -m : It is used to display last modified time.

Stat filename : It is used to display last accessed, modified and changed details of a file.

Mkdir directory_name : It is used to create directories.
Rmdir directory_name : It is used to remove only empty directories.
Rmdir directory_name1, directory_name2, directory_name3 : It is used to remove only all the 3 empty directories.
Rmdir -i directory_name : It is used to remove directories with interactive mode.
Rmdir -r directory_name : It is used to remove non empty directories.

Mv : It is used to move or rename the files.
Mv oldname newname : It is used to rename the file.
(Mv filename filename1)

Mv file1 /user/ file1 : It is used to move file1 from current directory to user directory file1.

Cp : It is used to copy the file. By default it supports only files not directories.
Cp shell perl : It is used to copy the file shell into the file perl. Perl contains all the contents in the shell file.
Cp -r direct1 direct2 :  It is used to copy directory1 to directory2.

Wc -l filename : It is used to print line count.
Wc -w filename : It is used to print word count. 

* Cut Command :

  Extracts parts of each line of a file. It cuts out a set of bytes or characters or fields from each row of the file based on the delimiter 

  Process the file vertically as result of which it produces a single or multiple coulums in the output.

  Acts like a filter that processes the file and extracts columns from it

  -b Bytes; selects only these bytes

  -c Characters; select ony these characters

  -d Delimiter; use the mentioned delimiter

  -f Fields; select only these fields

 

   Syntax1:- cut -d ',' -f1 file1;

   Syntax2:- cut -d ',' -f2 file1;

   Syntax3:- cut -b1-5 file1;

  

   Output1:

   apple

  

   Output2:

   ball

   

   Output3:

   apple,b

* Paste Command: 

  It is used to merge contents of two files vertically.

   

  -d To change the delimiter specified character instead of default delimiter tab

  -s Used to join the multiple lines of the file(in parallel) using the delimiters specified.

 

   Syntax1:- paste file1 file2

   Syntax2:- paste -d "|" file1 file2

   Syntax3:- paste -s file1 file2

    

   Output1:

 apple,ball,cat,dog,elephant,fish,goat,hen,icecream,jeep apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream

  

   Output2:

   apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream,jeep|apple,ball,cat,dog,elephant,fish,goat,hen,ice-  cream

  

   Output3:

   apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream,jeep

   apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream

* Comm Command: 

  It compares two sorted files line by line and produces three column output, when no option were passed to it. Column one contains lines unique to file1, column two contains lines unique to file2 and column three contains lines unique to both the files.

  -1 Do not display column 1 (Lines unique to first file)

  -2 Do not display column 2 (Lines unique to second file)

  -3 Do not display column 3 (Lines unique to both file)

  

  --check-order Verify files are in sorted order

  --nocheck-order Ignore even if files are not in sorted order

    Syntax1:- comm -1 file1 file2

    Syntax2:- comm -2 file1 file2  

    Syntax3:- comm -3 file1 file2

   

  Output1:

  apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream

  

  Output2:

  apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream,jeep

  

  Output3:

  apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream

  apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream,jeep

* Diff Command:  

  It compare files line by line. 

  This command is used to get uncommon records between both the files.

  eg:- diff file1 file2

  

  Syntax1: 

  diff file1 file2

  Syntax2:

  diff Proverbs.txt Proverbs2.txt

    

  Output1:

  < apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream,jeep

  ---

  > apple,ball,cat,dog,elephant,fish,goat,hen,ice-cream

  

  Output2:

  >  He who hesitates is lost

  >  Like father like son

  >  Fortune favours the brave

* Sort Command: 

  Sort rearranges the lines of a file such that lines appear in nummerical or alphabetical order. 

  If no options are given sorting is done as:

  Lines that egin with number will come before lines that begin with a letter

  Lines that begin with the lowercase letter will come before lines that begin with same letter in uppercase.  

  

  -t To specify the delimiter

  -k To specify starting and ending columns for sorting

  -r To sort in revers order

  -n To sort numerical data

  -u To remove duplicate lines

  -c To check the file is in sorted order or not (It will not sort file contents)

  

   Syntax1:- sort Proverbs.txt  

   Syntax2:- sort -r Proverbs.txt 

   Syntax3:- sort -u Proverbs.txt

   Syntax4:- sort -c Proverbs.txt

    

   Output1:

   Action speaks louder than words

   An apple a day keeps the doctor away

   An apple a day keeps the doctor away

   Better late than never

   Practise makes man perfect

   Sun sets in the west

   Sun rises in the east

 

   Output2:

   Sun rises in the east

   Sun sets in the west

   Practise makes man perfect

   Better late than never

   An apple a day keeps the doctor away

   An apple a day keeps the doctor away

   Action speaks louder than words

 

   Output3:

   Action speaks louder than words

   An apple a day keeps the doctor away

   Better late than never

   Practise makes man perfect

   Sun sets in the west

   Sun rises in the east

   

   Output4:-

   sort: Proverbs.txt:2: disorder:  Sun sets in the west

* Unique Command:

  It is used to eliminate or remove the repeated occurance of consecutive lines from the file. If same line is repeated more than once, but not consecutive, it willbe considered as two unique lines and hence not removed. If all the repeated lines of the file has to be removed, then the file has to be sorted before passing it to uniq command.

  -u Selecting non repeated lines 

  -d Selecting the duplicate lines

  -c    Counting the frequency of occurance.

   Syntax1:- uniq Proverbs.txt (one occurance of every records will appear here) 

   Syntax2:- uniq -u Proverbs.txt 

   Syntax3:- uniq -d Proverbs.txt

   Syntax4:- uniq -c Proverbs.txt

    

   Output1:

   Sun rises in the east

   Sun sets in the west

   An apple a day keeps the doctor away

   Practise makes man perfect

   Action speaks louder than words

   Better late than never

   

   Output2:

   Sun rises in the east

   Sun sets in the west

   Practise makes man perfect

   Action speaks louder than words

   Better late than never

 

   Output3:

   An apple a day keeps the doctor away

   

   Output4:-

   1 Sun rises in the east

   1  Sun sets in the west

   2  An apple a day keeps the doctor away

   1  Practise makes man perfect

   1  Action speaks louder than words

   1  Better late than never

* tr Command: Translating characters

  The tr command manipulates individual character in a line. It translates character by character.

  It translates each character of expression1 to its corresponding mapped character in expression2.

  It takes input only from standard input but not filename as an argument.

  -d deleting the characters

  -s compressing multiple consecutive characters (Squeeze Repeating Characters)

  -c complementing values of expression

   The following command is used to translate double quotes into single quotes.

Syntax1:- cat Proverbs.txt | tr -d apple

    Syntax2:- cat Proverbs.txt | tr [a-z] [A-Z] (It converts all the character to capital letters)

Syntax3:- echo 'TODAYYYY IIIS SOOO COOOLD ~' | tr -s 'A-Z' 'a-z'  

   The multiple consecutive characters 'T' in log file logs.txt can be squeezed to one character using option -s of tr command as shown below.

   tr -s 'T' < logs.txt

   

   Output1:

   Sun riss in th st

   Sun sts in th wst

   An   dy ks th doctor wy

   An   dy ks th doctor wy

   Prctis mks mn rfct

   Action sks oudr thn words

   Bttr t thn nvr

 

   Output2: 

   SUN RISES IN THE EAST

   SUN SETS IN THE WEST

   AN APPLE A DAY KEEPS THE DOCTOR AWAY

   AN APPLE A DAY KEEPS THE DOCTOR AWAY

   PRACTISE MAKES MAN PERFECT

   ACTION SPEAKS LOUDER THAN WORDS

   BETTER LATE THAN NEVER

   

   Output3:

   today is so cold 

* Rev Command : reversing the given text/line

  It is used to reverse the text/line character wise. It reverses the order of the characters in each line and prints to the standard output.

  

  Syntax1:- cat file1 | rev

  Syntax2:- rev Proverbs.txt

  

  Output1:- peej,maerc-eci,neh,taog,hsif,tnahpele,god,tac,llab,elppa

  

  Output2:

  tsae eht ni sesir nuS

  tsew eht ni stes nuS

  yawa rotcod eht speek yad a elppa nA

  yawa rotcod eht speek yad a elppa nA

  tcefrep nam sekam esitcarP

  sdrow naht reduol skaeps noitcA

  reven naht etal retteB 

* Grep Command :   

  The content of the file is processed line by line, to search for the pattern and the lines matching the pattern are displayed on the terminal.

  It is used to search for lines of text that match one or many regular expressions.

  

  -i

  -V

  -n

  -x

  -c

  -v 

  -l

  -f

  -E

  -P

  -A

  -B 

  -C

  

  Syntax:- grep apple Proverbs.txt

  

  Output:-

  An apple a day keeps the doctor away

  An apple a day keeps the doctor away

* FIND Command : 

  To find a file in a directory.

  

  Syntax:- find -name s1.sh

  

* Sed Command :

  It is used to replace any character in a file.

  

  Syntax:- sed -i 's/ /,/g' filename.txt

           sed -i 's/,/ /g' Proverbs.txt

  Without using '-i' in sed command, file will not change it's delimiter or character. Just result will be displayed with 2nd character. 

 

Unix Filters

List of Filters in Unix:-
-----------------------
$HEAD
$TAIL
$NL
$CUT
$PASTE
$SORT
$TR
$TEE
$SED
$GREP
$FGREP
$EGREP


$HEAD :- (These are line filters)
-------- 
Head filename : It always displays first 10 lines from a file 
Head -3 file1 : It displays first 3 lines.
Head -5 file1 : It displays first 5 lines.
Head -5v file1 : It displays first 5 lines along with the filename (here v indicates verbose).
Head -5 file1 file2 : It displays first 5 lines from both the files along with the filenames.


$TAIL :- (These are line filters)
-------
Tail filename : It always displays last 10 lines from a file 
Tail -3 file1 : It displays last 3 lines.
Tail -5 file1 : It displays last 5 lines.
Tail -5v file1 : It displays last 5 lines along with the filename (here v indicates verbose).
Tail -5 file1 file2 : 
Head -5 file1 | Tail -1 file1 :  It displays first 5 lines and then displays last line from the result of 5.


NL :- (Number lines of files)
------
Nl file1 : It displays line numbers in file1
Nl file1 > file2 : It redirects all the file1 information to file2.


CUT :- (It removes sections from each line of files)
------
Cut -d ',' -f 3 file1 : It displays only the 3rd field from the file1 (complete 3rd column is display). For delimiter separated fields, the -d option is used.
Cut -d ' ' -f 3 file1 : It displays only the 3rd field from the file1 (complete 3rd column is display). The default delimiter is the tab character.
Cut -d ',' -f 2,3 file1 : It displays only the 2nd and 3rd field from the file1 (complete 2nd and 3rd column is display).
Cut -d ',' -f 1,3 file1 : It displays only the 1st and 3rd field from the file1 (complete 1st and 3rd column is display).
Cut -d ' ' -f 1-3 file1 : It displays from 1st to 3rd field from the file1 (complete 1st to 3rd column is display).
Cut -c1,7 file1 : It displays first character and 7th character.
Cut -c1-7 file1 : It displays from first character to 7th character.


PASTE :- (It merges lines of files)
--------
Paste file1 file2 : It merges two files or it display both files side by side.
Paste -s file1 file2 : It merges two files or it display both files one by one.


SORT :-
-------
Sort file1 : It sorts fields from the file1.
Sort -r file1 : It sorts fields from the file1 in the reverse order.
Sort -n file1 : It sorts fields from the file1 in the numerical order, if the numeric data exits.
Sort -rn file1 : It sorts fields from the file1 in the numerical reverse order, if the numeric data exits.
Sort -ofile2 file1 : It sorts and outputs the fields of file1 in file2.


TR :- (Translate or delete characters)
-----
Tr 's' 'n' < file1 : It converts character S to N, where s is present from the file1.
Tr 'a-z' 'A-Z' < file1 : It converts all the small characters into Capital letters.
Tr 'aeiou' 'AEIOU' < file1 : It converts all the small letters vowels into capital letters vowels.


TEE :- (It reads from standard input and writes to standard output)
-----
Cal | Tee file1 : It takes input from cal and redirects to file1.
Wc -l file1.txt| tee file2.txt


SED :- 
------ 
(Sed command or Stream Editor is very powerful utility offered by Linux/Unix systems. It is mainly used for text substitution , find & replace but it can also perform other text manipulations like insertion, deletion, search etc. With SED, we can edit complete files without actually having to open it.)

Sed 2q file1 : It filters and displays only first two lines.
Sed -n 2p file1 : It filters and displays only second line from file1.
Sed -n -e 2p -e 5p file1 : It filters and displays only second line and 5th line from file1.
Sed 4d fruits : '4’ is the line number & option ‘d’ will delete the mentioned line number. 
Sed 3d fruits > newfile : '3’ is the line number & option ‘d’ will delete the mentioned line number and result will be display in newfile. 


GREP :- (Globally search for regular expressions)
-------
Grep Apple File1 : It displays lines with apple from the file1.
Grep column_name File1 : It displays column_name from the file1.
Grep -n column_name File1 : It displays number for column_name from the file1.


FGREP :- (Fast Globally search for regular expressions)
-------



EGREP :- (Extended Globally search for regular expressions)
-------