ls Command

• The ls is the most widely used command in unix or linux.
• The ls command is a command-line utility for listing the contents of a directory or directories given to it via standard input.

CAST Function

CAST function converts one built-in data type into another built-in data type with the specified precision and length.

Syntax: CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

CAST Converts

Convert a string value to NUMBER leaving 2 digits after the decimal point:

SELECT CAST('345.213' AS NUMBER(5,2)) FROM DUAL;

CAST - Convert and Round Numbers:

SELECT CAST('246.206' AS NUMBER(5,2)) FROM DUAL;

CAST convert the date into a VARCHAR2(30) value:

 SELECT CAST( '22-Aug-2003' AS VARCHAR2(30) ) FROM DUAL;

TRUNC for Datetime

TO_DATE

TO_CHAR for Datetime

MONTHS_BETWEEN

MONTHS_BETWEEN function returns the number of months between two dates.

Syntax: MONTHS_BETWEEN(date_expression1,date_expression2);

Months Between Function Details

MONTHS_BETWEEN function returns an integer number if the days are the same.

SELECT MONTHS_BETWEEN (DATE '2012-02-12', DATE '2012-01-12') FROM DUAL; 

MONTHS_BETWEEN function returns an integer number if the both dates specify the last day of the month.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-01-31') FROM DUAL; 

MONTHS_BETWEEN function returns a decimal number if the days are different.

SELECT MONTHS_BETWEEN (DATE '2012-02-29', DATE '2012-02-01') FROM DUAL;

MONTHS_BETWEEN always calculates the fractional part as the difference in days divided by 31

In the example above, the fractional part is calculated as (29 - 1)/31 = 0.903225806, although there are 29 days in February 2012.

You can also see that MONTHS_BETWEEN does not return an integer result if you specify the first and last days of the same month.

EXTRACT Function

EXTRACT function gets the specified part (day, month, year, hours, minutes,second etc.) from a datetime value.

Syntax: EXTRACT(datetime_unit FROM datetime_expression1)

Although DATE contains time part but we can't extract the hour,minute and second from DATE.
HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

EXTRACT Function Details

Extract DAY from a specific DATE.

SELECT EXTRACT(DAY FROM DATE '2011-01-12') FROM dual;

Extract MONTH from a specific DATE.

SELECT EXTRACT(MONTH FROM DATE '2011-01-12') FROM dual;

Extract YEAR from a specific DATE.

SELECT EXTRACT(YEAR FROM DATE '2011-01-12') FROM dual;

We can't extract  hour,minute and second from DATE. We will get the error

SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;

HOUR, MINUTE and SECOND can be extracted from TIMESTAMP only.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2012-01-12 10:11:00') FROM dual;

ADD_MONTHS Function

The Oracle date function ADD_MONTHS returns a date with a given number of months added that you specify.This is most powerful Oracle function for computing future months and years.

Syntax: ADD_MONTHS(datetime_expression,N)

• It returns the date 'D' plus OR minus'N' month.
• The argument 'N' can be any positive OR negative integer.

How to Get Last Day Of Last Month

To find the last day of last month then we need to use ADD_MONTHS function

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1)) FROM DUAL;

Get The First Monday of Each Quarter Year

To find the first monday of each quarter year we need to use ADD_MONTHS

SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'q'), 4), 'monday') FROM DUAL;

LAST_DAY Function

• LAST_DAY function returns the last date of the specified month. 
• LAST_DATE function returns date.
• Mostly used to determine how many days are left in the current month.

Syntax:  LAST_DAY(datetime_expression)

How to Get Number of Days Left in Month

To find the number of days we need to use LAST_DAY function

SELECT LAST_DAY(SYSDATE) -SYSDATE FROM dual;

Or we can use the EXTRACT and LAST_DAY  function to find the number of days left in this month

SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) FROM dual;

Virtual Columns in Oracle 11g

Virtual Columns is a new feature of Oracle 11g. It appear to be normal table columns, but their values are derived rather than being stored on disc.

Syntax:

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

Features of Virtual Columns in Oracle:

1. You can define Indexes against virtual columns as well. However, please note that indexes defined against virtual columns are equivalent to function-based indexes.
2. Virtual columns can be referenced in the column list of SELECT statements and also in the WHERE clause of SELECT, UPDATE and DELETE statements.
3. Tables containing virtual columns can still be eligible for result caching.
4. Virtual columns can be used in the partition key in all basic partitioning schemes.

Limitations of Virtual Columns in Oracle:

1. DML operations on the virtual columns are not allowed.
2. While virtual columns are candidate for indexing, virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
3. The expressions used in creating virtual columns as following limitations:

• It cannot refer to any other column of another table, It can only refer to columns defined in the same table.
• It cannot refer to another virtual column with its name although the same expression can be used.
• The column expression for virtual columns can refer to a PL/SQL function if the function is designated DETERMINISTIC during its creation.

SQL Function Question

1. What is SQL function?
SQL function also we called as a Oracle Built in Function which is use for
Perform calculation on data
Modify individual data item
Manipulate output for group of rows.

2. How many type of SQL Function?
Single Row Functions :These functions return a single result for every row of a query table or view(this functions appear in SELECT,WHERE and ORDER BY).
Group Functions :These functions manipulate groups of rows and return one result per group of rows(this functions appear in SELECT and HAVING clauses).

3. What is SUBSTR function ?
SUBSTR is a oracle Built in Function which is return specific character , starting from specified position 'm' to 'n' characters long.

4. What is INSTR function ?
INSTR is a oracle Built in Function which is return the numeric position of a named charecter.

5. What is REPLACE function?

6. What is TRANSLATE function?

7. What is ROUNH function?

8. What is TRUNCATE function?

9. What is difference between ROUND and TRUNCATE?
Ans

10. What is difference between REPLACE and TRANSLATE?
Ans

11. What is the use of ADD_MONTHS function?

12. What is the use of MONTHS_BETWEEN function?

13. What is the use of NEXT_DAY function ?

Round Vs Truncate

TRUNC and ROUND function looks similar but not exactly.

ROUND function used to round the number to the nearest but TRUNC used to truncate/delete the number from some position.

Syntax:

TRUNC(number, precision);
ROUND(number, precision);

Some cases both returns same result.

SQL> select trunc(25.67),round(25.67) from dual;

TRUNC(25.67)	ROUND(25.67)
25	26

Below chart clearly explains the difference

Input	TRUNC	ROUND
25.67,0	25	26
25.67,1	25.6	25.7
25.34,1	25.3	25.3
25.34,2	25.34	25.34
25.67,-1	20	30

Constant In PL/SQL

•  We are using CONSTANT part of a PL/SQL declaration.
•  A constant is declared using the CONSTANT keyword.
•  CONSTANT requires an initial value.
•  CONSTANT remains unchanged throughout the program.

Syntax :
 constant_name CONSTANT datatype := VALUE;

Example:
 If you want to write a program which will calculate the interest of the bank. Bank interest we can declare  and we use it throughout in our program. If the interest got change then we need to change the CONSTANT value no need to change throughout the program.

CONSTANT remains unchanged throughout the program:

DECLARE   v_string  VARCHAR2(20);   n_number  NUMBER(10);   v_con_string CONSTANT VARCHAR2(20) := 'oracleguide4u';  BEGIN   v_string := 'oracleguide';   n_number := 1;   v_con_string := 'This will fail';   * ERROR at line 10: ORA-06550: line 10, column 3: PLS-00363: expression 'V_CON_STRING' cannot be used as an assignment target

DECLARE   v_string  VARCHAR2(20);   n_number  NUMBER(10);   v_con_string CONSTANT VARCHAR2(20) := 'oracleguide4u';  BEGIN   v_string := 'oracleguide';   n_number := 1;  END;

Data Type In PL/SQL

Every Variable has data type. Oracle provides many predefined data types or sub type.

PL/SQL supports four data type:

1. Scalar Data Type
2. Composite Data Type
3. Reference Data Type
4. LOB Data Type

1. Scalar Data Type:
It holds a single value and it supports Boolean type.

Scalar Data Types are classified into four categories :

Data Type	Description
Numeric	Numeric data type is used for arithmetic operations like PLS_INTEGER, BINARY_INTEGER,NUMBER etc.
Character	Alphanumeric value which represent single character or strings of characters or sub type like CHAR,VARCHAR2,NCHAR,NVARCHAR2 etc
Boolean	Boolean data type are used for logical operations like TRUE and FALSE.
Datetime	Date data type is used for store the date and time.

Example Of Sub type:

DECLARE
   SUBTYPE NAME IS char(20);
   SUBTYPE MSG IS varchar2(100);
   USER_NAME NAME;
   WEL_MSG MSG;
BEGIN
   USER_NAME := 'Reader ';
   WEL_MSG := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || USER_NAME||' '|| WEL_MSG);
END;

2. Composite Data Type:
In Composite datatype we can store whole row, if one row have three columns so we can store three values in a composite datatype variable at a time. 

Type Of Composite Data Type :

• RECORDS 
• COLLECTIONS

3. Reference Data Type:
It holds values , acting as pointers which pointers to other data items like REF CURSOR.


4. LOB Data Type:
LOB data type hold values called Locators, which specifying the location of large objects like graphic images, video clips, and sound wave forms.

Comments In PL/SQL

PL/SQL supports two type of comments

• Single line comments(-- comments)
• Multi line comments(/*comments*/).

Single line and Multi line comments example:

BEGIN     --This is oracle guide blogs [this is single line comments] null;     /* I am trying my level best to provide the right information.     If you find any thing contradictory please share with us.     We will review and take the corrective action.[this is multi line comments]*/ END;

• PL/SQL compiler ignores comments.
• Comments Should appear within a statements at the end of a line.
• Standard code always refer the Multi line comments . 

Anchored Variable Declarations(%TYPE)

• When we are declaring anchore data type variable in PL/SQL, that mean PL/SQL to set the datatype of that variable from the datatype of another element.
• The '%TYPE' variables is use to anchore PL/SQL variables to the database type columns directly.

Syntax:
<variable name> <type attribute>%TYPE ;
type attribute is nothing but previously declare PL/SQL variable or Table.column name.

Declarations with %TYPE:

Anchored Variable Declarations(%TYPE):

DECLARE /*V_EMPNAME is referring the table.column name*/ V_EMPNAME  EMP.ENAME%TYPE; /*V_EMPNAME is referring the previously declare PL/SQL variable*/ V_FULL_NAME    V_EMPNAME%TYPE;           BEGIN NULL; END;

Nesting Usages of the %TYPE Attribute:
See the below nest usages of %TYPE example

DECLARE    /* The base variable */    V_EMPNAME  EMP.ENAME%TYPE;    /* Anchored to V_EMPNAME */    V_FST_NAME    V_EMPNAME%TYPE;       /* Anchored to V_FST_NAME*/    V_FULL_NAME    V_FST_NAME%TYPE;    BEGIN null; END;

Why we will use %TYPE?
If the data type of a particular column is changing in the back end process of the serever,
either with data type or width, will never effect the PL/SQL blocks or it will not effect any operations.

Blocks In PL/SQL

A PL/SQL program are logical blocks and it can nested with one another.

The basic part of PL/SQL block are

• Declarative Part
• Executable Part
• Exception Handling Part

Type Of PL/SQL Block:

1. Anonymous Block
2. Named Block
3. Sub Program Block

1. Anonymous Block:

• Anonymous block known as un-named block.
• The anonymous block cannot be called by any other block.
• Anonymous block serve as scripts that execute PL/SQL statements .

Example Of Anonymous Block:

DECLARE /*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

2. Named Block:
They have all the features as specified for the Anonymous block but the only difference is that each block can be named if necessary.

Example Of Named Block:

<<FirstNameBlock>>  DECLARE /*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

3. Sub Program Block:

• Sub Program Blocks declare as procedure or function.
• It also called as Named block.
• The Sub Program block can be called by any other block.
• Sub Program Block helps developer to reuse the code(Code Reusability ).

Example Of  Sub Program Block:

	CREATE OR REPLACE PROCEDURE PROGRAM_BLOCK_EXAMPLE AS/*Declare all variable or cursor etc*/ BEGIN  /*Write your Executable logic*/ NULL;           /*Write your exception*/ EXCEPTIONWHEN OTHERS THEN NULL; END;

cat command

cat command is use to read the contents of files or displaying them, combining copies of  files and creating file .

See the use of cat command

Command	Details
cat [options] [filenames] [-] [filenames]	Syntax
cat file1	cat command is use to read the contents of files
cat file1 > file2	">" this is output redirection operator, cat file1 output is written to file2
cat >file1	To Write the data into file 1
cat > file2	To Write the data into file 2
cat file1 file2	concatenate copies of the contents of the two files file1 and file2
cat file1 file2 >file3	file1 and file2 data redirect to file3
cat >> file4	">>" the append operator , append the data in file4

touch command

In Unix and Linux touch command use for creating empty file,updating access time and modification time.

Command	Desc
syntax	touch [option] file_name(s)
touch file1 file2 file3	This command would create three new empty files.
touch -am file3	access(-a) and modification(-m) times to the current time
touch -r file4 file5	[-r] is use for reference , to use the times of fille4 for file5
touch -d '1 May 2005 10:22' file8	To change the last access time of file8 to 10:22 a.m. May 1, 2005
touch -d '14 May' file9	[-d] Partial date change Only the date need be provided.

Alias Command in Unix and Lunix

• Alias is a shortcut to reference a command. 
• It can be used to avoid typing long commands that are frequently used.
• For common patterns it can reduce keystrokes and improve efficiency. 

One best example why we will use alias ?
A user want to delete a file with confirm delete option for that we type
rm -i file.txt
remove file.txt? y
But some time we forgot to write -i , to avoid this such issue we use alias.

Command	Desc
Syntax Of alias	alias [-p] [name="value"]
Example:
alias	It will show you list of alish which is present in system
alias ls="ls -al"	lists the names of the files and directories within the current directory
alias df="df -h"	size, amount of space used, amount of space available (-h) in MB and GB
alias rm="rm -i"	increasing the safety of the system by making commands interactive. It is use for remove files
alias cp="cp -i"	increasing the safety of the system by making commands interactive .It is use for copy files
Removing Aliases
Syntax	unalias [-a] name(s)
Example:	unalias rm

Unix and Linux Basic Commands

Below are details of Basic Commands

Command	Description
date	date command is used to print or set the system date and time. Syntax: date options
cal	cal displays a simple calendar. If arguments are not specified, the current month is displayed. Syntax: cal options
clear	clear the terminal screen
pwd	pwd command is used to print name of working directory
uname	print system information. Generally used to know the flavor and version of the operating system
who	show who is logged on
uptime	Tell how long the system has been running.
ls	If you simply execute ls on the command prompt, then it will display the files and directories in the current directory.You can pass a directory as an argument to ls command. In this case, the ls command prints all the files and directories in the specific directory you have passed.Syntax: ls directory
touch	The touch command simply creates an empty file. The below touch command creates a file_name.txt in the current directory.Syntax: touch file_name.txt
cat	The cat command is used to display the contents in a file. Syntax: cat file_name.txt
head	The head command can be used to print the specified number of lines from the starting of a file. Syntax: head -3 file_name.txt
tail	The tail command can be used to print the specified number of lines from the end of a file. Syntax: tail -3 file_name.txt
mkdir	The mkdir command is used to create the specified directory.
cd	The cd command can be used to change from one directory to another directory. You need to specify the target directory where you want to go.
mv	The mv command is used to rename the files and it also used for moving the files from one directory into another directory.
cp	The cp command is used to copy the content of source file into the target file. If the target file already have data, then it will be overwritten.
rmdir	remove or delete directories. Syntax: rmdir directory_name
wc	wc command can be used to find the number of line, words and characters in a file.
cut	cut remove sections from each line of files. Syntax: cat new_file.txt|cut -c1
man	If you want to know more about all above command then go to man comman Syntaxman command_name.

File Attributes and Permissions

File Attributes

We can observe 7 attribute fields listed for each file. 

The attribute fields are

1. File name
2. Modification date
3. Size
4. Group:(associated group for the file)
5. Owner
6. Number of links(the number of other links associated with this file),
7. Permission modes: the permissions assigned to the file for the owner, the group and all   others.

File Permissions
The permission field for each file consists of 10 characters as described by the diagram below

Unix File System

The Unix file system has a hierarchical (tree-like) structure with its highest level 
directory called root ( /).
Immediately below the root level directory are several sub-directories, most of which contain system files. 

The following figure shows a typical organization of files in Unix system.

bin - short for binaries, this is the directory where many commonly used 
          executable commands reside
dev - contains device specific files
etc - contains system configuration files
home - contains user directories and files
lib - contains all library files
mnt - contains device files related to mounted devices
proc - contains files related to system processes
root - the root users' home directory (note this is different than /)
sbin - system binary files reside here. If there is no sbin directory on your system, these files most likely reside in etc
tmp - storage for temporary files which are periodically removed from the filesystem
usr - also contains executable commands

Unix File Types 

All files in the Unix file system can be loosely categorized into 3 types, specifically:

1. ordinary files
2. directory files
3. device files

Ordinary Files

Ordinary files hold data and executable programs. Executable programs are the commands

(ls) that you enter on the prompt. The data can be anything and there is no specific format

 enforced in the way the data is stored.

The regular files can be visualized as the leaves in the UNIX tree.

Directories

Directories are files that contain other files and sub-directories. Directories are used to

organize the data by keeping closely related files in the same place. The directories are 

just like the folders in windows operating system.

The kernel alone can write the directory file. When a file is added to or deleted from this directory, the kernel makes an entry. 
A directory file can be visualized as the branch of the UNIX tree.

Special Or Device Files

These files represent the physical devices. Files can also refer to computer hardware such as terminals and printers. These device files can also refer to tape and disk drives, CD-ROM players, modems, network interfaces, scanners, and any other piece of computer hardware. When a process writes to a special file, the data is sent to the physical device associated with it. Special files are not literally files, but are pointers that point to the device drivers located in the kernel. The protection applicable to files is also applicable to physical devices.

User Login Process

When the user is trying to logging into system then Kernel should know who is that user.

Kernel manage this user logging process by running below programs

1. getty    
2. loging 

       [getty is short for “get terminal”.]

• Kernel calls program ‘init’. ‘init’ calls program ‘getty’, which issues a login prompt in the monitor.

• User enters login and password.‘getty’ calls program ‘login’ which scans file /etc/passwd to match username and password. 

• After validation, control passes to session startup program /bin/sh .User gets a shell prompt. 

• If the user fails to provide valid password, the login program returns the control back to the getty program.

What is UNIX ?

UNIX is a operating system which is multi user (multiple user can access Unix system at a same
 time ) and  multitasking (One user can run multiple program). Operating System is an interface
 between hardware and applications software's.

Unix Architecture

1.Shell

• Human interface point for Unix
• Program layer – provides an environment for the user to enter commands to get desired results.
• Korn Shell, Bourne Shell, C Shell are various shells used by Unix users

2. Kernel

• Heart of The Unix OS.
• Collection of C programs directly communicating with hardware
• Part of Unix system loaded into memory when Unix is booted

        Manages:-

• System resources
• Allocates time between user and processes
• Decides process priorities

3.  Hardware

 Unix Boot Process Setps

1. BIOS    : Basic I/O System Executes MBR.
2. MBR : Master Boot Record Executes GRUB.
3. GRUB  : Grand Unified Boot loader executes Kernel.
4. Kernel : Kernal executes  sbin/Init.
5. Init          : Init executes run level programs .
6. Runlevel : Runlevel program executes from /etc/rc .d/rc*.d/rc*.

Data Type

Oracles supports the following categories of data types:
Oracle Built-in Datatypes.
User-Defined Data Types.

There are 20 Oracle built-in data types, divided into 6 groups:
1.Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
2.Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
3.Long and Row Datatypes - LONG, LONG RAW, RAW
4.Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
5.Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
6.Row ID Datatypes - ROWID, UROWID

Difference between  CHAR and NCHAR:
Both CHAR and NCHAR are fixed length character data types. But they have the following differences:
CHAR's size is specified in bytes by default.
NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
NCHAR stores characters in Unicode.

Difference between  CHAR and VARCHAR2 :
The main differences between CHAR and VARCHAR2 are
CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
VARCHAR2 stores values in variable lengths. Values are not padded with any characters.

Difference between  NUMBER and BINARY_FLOAT :
The main differences between NUMBER and BINARY_FLOAT are
NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
BINARY_FLOAT stores values as single precision floating-point numbers.

Difference between  DATE and TIMESTAMP :
The main differences between DATE and TIMESTAMP are
DATE stores values as century, year, month, date, hour, minute, and second.
TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

Difference between  INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND:
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:
INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.

Difference between  BLOB and CLOB :
The main differences between BLOB and CLOB are:
BLOB stores values as LOB (Large OBject) in bitstreams.
CLOB stores values as LOB (Large OBject) in character steams.