Tuesday, August 16, 2016

Unix Basics - My Learnings

Unix (trademarked as UNIX) is a family of multitasking, multi-user computer operating systems that derive from the original AT&T Unix, developed in the 1970s at the Bell Labs research center by Ken Thompson, Dennis Ritchie, and others.

Unix..?
The UNIX operating system is a set of programs that act as a link between the computer and the user.
The computer programs that allocate the system resources and coordinate all the details of the computer's internals is called the operating system or kernel.

Users communicate with the kernel through a program known as the shell. The shell is a command line interpreter; it translates commands entered by the user and converts them into a language that is understood by the kernel.

Few Characteristics Shared by Unix:-
1. Simplicity
2. Focus
3. Reusable Components
4. Filters
5. Open file formats
6. Flexibility

H/W Requirements:-
--> 80MB Hard Disk
--> Atleast 4MB of RAM on a 16-bit microprocessor
--> 4/8/16 port controller card

In 80MB Hard Disk --> 40MB used by unix Operating System
 --> 10-20MB as Swap Space
 --> 0.75 to 1MB for terminal
 --> Unix requires amount of human support

Sailent Features of unix:-
Multiuser Capability - In Multi user system, the same computer resources like HD, Memory etc,. are accessible to many users
Dumb terminals - these terminals consist of a keyboard and a display unit with no memory or disk of its own
Terminal Emulation - The S/W that makes PC work like dumb terminal is call terminal emulation, eg: VTERM and XTALK are popularly used such S/W
Dial-in Terminals - These terminals use telephone lines to connect with host machine these uses modem
Multitaksing - It is capable of carrying at more than one job at the same time. In MS-Dos, multitasking is called serial multitasking
Communication
Security - It allows sharing of data,but not indiscriminality
- Read, Write and Excute Permissions
- File Encryption
- Assigning Password and Login
Portability - unix is H/W transperancy. Infact it is almost written entirely in C.

Types of Shell:-
Bourne Shell
C Shell
Korn Shell

Command Syntax:-

Command [options] [argu], Note: Option is generally precided with hyphen(-)


Unix Architecture

File Hierarchy

Unix Command Categories - Basics with examples
1. General Purpose Commands
a. Clear Syn:- $clear
b. WHO Syn:- $WHO[argu]
c. Date Syn:- $date[+format]
d. Cal Syn:- $cal[argu]
e. Type Syn:- $type command_name
f. PWB Syn:- $pwd
g. MAN Syn:- $man
2. File and Directory Commands
a. ls Syn:- $ls [options] dir/file_name
Options:- -a hidden files
 -l display list in long format
 -r list recursively
 -i list with inode values
b. touch Syn:- $touch file1[file2...]  -- used to create empty files
c. cat Syn:- Create File: $cat >filename
 Display File: $cat filename
 Append File: $cat >>filename
Used to create files and save content in file, Display files and append the files with new extra content
d. more Syn:- $more   -- used to display/prints content in page wise
e. mkdir Syn:- $mkdir dir_name --used to create directories
f. cd Syn:- $cd dir_name --used to change directory
g. rmdir Syn:- $rmdir dir_name --used to remove directory
h. rm Syn:- $rm file/dir_name 
 $rm file1[file2..]  --remove all the files
 $rm -r file1  --remove file recur 
 $rm -i file --remove file interactively
i. mv Syn:- $mv src trgt --used to rename file name or move files from src to trgt
j. cp Syn:- $cp src trgt --used to copy files from src to trgt
k. ln Syn:- $ln -s src trgt --makes soft or symbolic links between files.
 $ln src trgt  --used to create hard links between files

Hard link and Soft link


Hard Link
Soft Link
Inode number is same for original and link file
Inode number is different for the original and link file
link will be valid if original is deleted
link is not valid if original is deleted
cannot be established for directories
can be established for the directories
for every hard link created, value of the link field is incremented by 1 for original as well as link file
Link field value would be 1 for link file and no change in link field value of original field

Data Streams

In computer programming, standard streams are preconnected input and output communication ... Unix eliminated this complexity with the concept of a data stream: an ordered sequence of data bytes which can be read until the end of file.

"stdin" --> used to input to any program or process, input streams: <, 0<

"stdout" --> output to any program or process, output streams: >, 1>
"stderr" --> error thrown by any program or process, error streams: 2>

A Stream is a sequence of data bytes used to channel data into or from program or process.
eg:- input:- $cat < file1
output:- $cat > file1
error:- $cat < file1 if file is not available

Shell Metacharacters/ wild card pattern matching
? --> used to match any single charater
* --> used to match zero or many characters
[xyz] --> used to match either x or y or z
[!xyz] --> used to match any one character except x,y,z
[s-z]  --> used to match any character with in a range

3. Text Processing - These commands are used to process the text
a. wc Syn:- $wc [-lwc] filename --used to display total no of lines,words and characters in any data
Options:- -l -->No of lines
 -w -->No of words
 -c -->No of characters

b. Pipe sym: | --> used to send data from 1st command to 2nd command
eg: $ls|wc -l
c.head --used to display a set of lines of the given data from start
Syn: $head [-n] file_name
d. tail --used to display a set of lines of the given data from last
Syn: $tail [-n/+n] file_name
Options -n: No of positions from last
+n: No of positions from nth position to last
e. cut --used to retrieve specific fields or characters from the data
--used on O/P of some other cmd or file
Syn: $cmd -o/p|cut [Options..]
eg: cut [options...] file
options: -c: character set
-f: field set
-d: delimeter for field set
f. tr --used to translate characters used on o/p of some other command
Syn: $cmd -o/p|tr[Options..] src_charset trgt_charset

g. sort --used to order the data
syn: $sort[options..]file
options: -r used to reverse
-u used to remove duplicate
-n numerical sort
h. grep --used to search a pattern in given data
Syn: $grep[options..] pattern_data
Options: -i ignoring case
-c print only count of no of lines
-v print not containing
^pattern print starting with any pattern

pattern$ print ending with any pattern

File Permissions
File ownership is an important component of UNIX that provides a secure method for storing files. Every file in UNIX has the following attributes/users -
Owner permissions - The owner's permissions determine what actions the owner of the file can perform on the file.
Group permissions - The group's permissions determine what actions a user, who is a member of the group that a file belongs to, can perform on the file.
Other (world) permissions - The permissions for others indicate what action all other users can perform on the file.

While using ls -l command it displays various information related to file permission as follows -
$ls -l /home/amrood
-rwxr-xr--  1 amrood   users 1024  Nov 2 00:10  myfile
drwxr-xr--- 1 amrood   users 1024  Nov 2 00:10  mydir

Here first column represents different access mode ie. permission associated with a file or directory.
The permissions are broken into groups of threes, and each position in the group denotes a specific permission, in this order: read (r), write (w), execute (x) The first three characters (2-4) represent the permissions for the file's owner. For example -rwxr-xr-- represents that owner has read (r), write (w) and execute (x) permission.
The second group of three characters (5-7) consists of the permissions for the group to which the file belongs. For example -rwxr-xr-- represents that group has read (r) and execute (x) permission but no write permission.
The last group of three characters (8-10) represents the permissions for everyone else. For example -rwxr-xr-- represents that other world has read (r) only permission.

File Access Modes
The basic building blocks of Unix permissions are the read, write, and execute permissions, which are described below -
1. Read -- Grants the capability to read ie. view the contents of the file.
2. Write --Grants the capability to modify, or remove the content of the file.
3. Execute --User with execute permissions can run a file as a program.

Directory Access Modes
Directory access modes are listed and organized in the same manner as any other file. There are a few differences to be mentioned:
1. Read --Access to a directory means that the user can read the contents. The user can look at the filenames inside the directory.
2. Write --Access means that the user can add or delete files to the contents of the directory.
3. Execute --Executing a directory doesn't really make a lot of sense so think of this as a traverse permission.

A user must have execute access to the bin directory in order to execute ls or cd command.

Changing Permissions
To change file or directory permissions, you use the chmod (change mode) command. There are two ways to use chmod: symbolic mode and absolute mode.

Using chmod in Symbolic Mode
The easiest way for a beginner to modify file or directory permissions is to use the symbolic mode. With symbolic permissions you can add, delete, or specify the permission set you want by using the operators in the following table.
Syn:- chmod [ugoa][+-=][rwx] filename

Using chmod in Absolute Mode
Syn:- chmod [0-7][0-7][0-7] filename

4. File Utility
File Utility is to find the file in unix system

Syn:- find [path][criteria][action] filename

Options:
[path] can be provided as follows
. current directory
.. parent directory
~ default home directory
Any absoolute path
Any relative path

[criteria] can be given like this
-name used to find using name of file
-type used to find using type of file
-links used to find using links of file
-perm used to find using perm of file
-size used to find using size of the file

[action] used to perform following action on file after searching
-print used to print the file
-exec used to find and execute the command
-ok used to find and execute the command interactively

5.Compression Utilities - used to compress and decompress the files or directories
a. gzip -used to compress and decompress the files
syn:- $gzip file1,file1,file3... to compress the file
Syn:- $gzip -d file1 to de-compress the file
b. zcat -used to view compressed file
c. zmore -used to view compressed file page-wise
d. gunzip -used to decompress
f. tar -used to archieve the files/directories
g. tring -used to backup the directory/directories

6. Process Commands
Whenever you issue a command in UNIX, it creates, or starts, a new process. When you tried out the ls command to list directory contents, you started a process. A process, in simple terms, is an instance of a running program.
The operating system tracks processes through a five digit ID number known as the pid or process ID . Each process in the system has a unique pid.
Pids eventually repeat because all the possible numbers are used up and the next pid rolls or starts over. At any one time, no two processes with the same pid
exist in the system because it is the pid that UNIX uses to track each process.

Starting a Process
When you start a process (run a command), there are two ways you can run it -
Foreground Processes
Background Processes

a. ps --used to list all the processes
Syn:- $ps will return the processes which are running our own process
Syn:- $ps -f will return the processes with more information

b. sleep --used to provide the delay of the process
c. fg --used to bring background process to foreground
d. kill --used to kill the process
e. prstat --use to see all the processes with memory details
Syn:- prstat <interval><count> -p <pid>

Operators in Unix
There are various operators supported by each shell. Our tutorial is based on default shell (Bourne) so we are going to cover all the important Bourne Shell operators in the tutorial.
There are following operators which we are going to discuss -
Arithmetic Operators.
Relational Operators.
Boolean Operators.
String Operators.
File Test Operators.
Numeric Operators.

The Bourne shell didn't originally have any mechanism to perform simple arithmetic but it uses external programs, either awk or the must simpler program expr.
Here is simple example to add two numbers -
#!/bin/sh
val=`expr 2 + 2`
echo "Total value : $val"
This would produce following result -
Total value : 4
There are following points to note down -
There must be spaces between operators and expressions for example 2+2 is not correct, where as it should be written as 2 + 2.
Complete expression should be enclosed between ``, called inverted commas.

Relational Operators
-eq "equals to" Syn:$test Value1 -eq Value2
-lt "less than"
-gt "greater than"
-le "less than or equal to"
-ge "greater than or equal to"
-ne "not equals to"

Arithmetic Operators
+ --Addition Syn:- expr $a + $b
- --Subtraction
* --Multiplication
/ --Division
% --Modulus
= --Assignment
== --Equality
!= --Not Equality

Boolean Operators
! --Logical negation
-o --Logical OR
-a --Logical AND
&& --Conditional and operator
|| --Conditional OR operator

String Operators
= --String equals to or not
!= --String not equals
-z --given string operand size is zero. If it is zero length then it returns true.
-n --given string operand size is non-zero. If it is non-zero length then it returns true.
str --if str is not the empty string. If it is empty then it returns false.

File Test Operators
-b --if file is a block special file if yes then condition becomes true.
-c --if file is a character special file if yes then condition becomes true.
-d --if file is a directory if yes then condition becomes true.
-f --if file is an ordinary file as opposed to a directory or special file if yes then condition becomes true.
-g --if file has its set group ID (SGID) bit set if yes then condition becomes true.
-k --if file has its sticky bit set if yes then condition becomes true.
-p --if file is a named pipe if yes then condition becomes true.
-t --if file descriptor is open and associated with a terminal if yes then condition becomes true.
-u --if file has its set user id (SUID) bit set if yes then condition becomes true.
-r --if file is readable if yes then condition becomes true.
-w --if file is writable if yes then condition becomes true.
-x --if file is execute if yes then condition becomes true.
-s --if file has size greater than 0 if yes then condition becomes true.
-e --if file has size greater than 0 if yes then condition becomes true.

Program Contructs
Selectional -- If and Case 
Iterational -- for, while and until

If Statement
Syn:- if <control command>
 then
<commands>
 fi

 
if-else Statement
Syn:- if <control command>
 then
<commands>
 else
<commands>
 fi

 
else-if statement
Syn:- if <control command>
 then
<commands>
 elif <control command>
 then
<commands>
 fi

case statement
Syn:- case value in
 c1) commands ;;
 c2) commands ;;
 c3) commands ;;
 ...
 ...
 esac

Note: value can be any expression also

for loop statement
Syn:- for variable_name in v1,v2,v3,v4...vn
do
<commands>
done

While loop statement
Syn:- while <control commands>
do
<commands>
done

until loop statment
Syn:- until <control commands>
do
<commands>
done







Wednesday, June 8, 2016

About Ugadi Festival and Preparation of Ugadi Pachadi

Ugadi is the New Year's Day for the people of the Telugu,and Kannada communities. This holiday is one of the most auspicious days for Telugus and Kannadigas. The Saka calendar begins with the month of Chaitra (March–April) and Ugadi marks the first day of the new year. Chaitra is the first month in Panchanga which is the Indian calendar. In some parts of India it is known as Vikram Samvat or Bhartiya Nav Varsh. This holiday is mostly prevalent in Andhra PradeshTelanganaKarnataka, and MaharashtraGudi Padwa, which is the Marathi new year, is also celebrated on the same day.

Preparations for the festival begin a week ahead. Houses are given a thorough wash. Shopping for new clothes and buying other items that go with the requirements of the festival are done with a lot of excitement.
On Ugadi Day, people wake up before the break of dawn and take a head bath after which they decorate the entrance of their houses with fresh mango leaves. The significance of tying mango leaves relates to a legend. It is said that Kartik (or Subramanya or Kumara Swamy) and Ganesha, the two sons of Lord Siva and Parvathi were very fond of mangoes. As the legend goes Kartik exhorted people to tie green mango leaves to the doorway signifying a good crop and general well-being.
It is noteworthy that we use mango leaves and coconuts (as in a Kalasam, to initiate any pooja) only on auspicious occasions to propitiate gods. People also splash fresh cow dung water on the ground in front of their house and draw colorful floral designs. This is a common sight in every household. People perform the ritualistic worship to God invoking his blessings before they start off with the new year. They pray for their health, wealth and prosperity and success in business too.Ugadi is also the most auspicious time to start new ventures.
The celebration of Ugadi is marked by religious zeal and social merriment. Special dishes are prepared for the occasion. In Andhra Pradesh and Telangana, eatables such as "pulihora, bobbatlu (Bhakshalu/ polelu/ oligalu) and Pachadi" and preparations made with raw mango go well with the occasion.
Ugadi pachchadi is a dish synonymous with Ugadi. It is made of new jaggery, raw mango pieces, neem flowers, and new tamarind which truly reflect life — a combination of six different tastes (sweet, sour, spice, salt, tangy, and bitter) symbolizing happiness, disgust, anger, fear, surprise, and sadness.

Ingredients and preparation as below

Ingredients (Mandatory)
1 ½ cup water
2 tbsp. peeled and chopped raw mango pieces
Few neem sprigs with flowers or 1 tbsp. flowers
Generous pinch salt or as needed
3 tbsp. Jaggery as needed
One generous pinch pepper powder
Tamarind as needed or 1 tsp tamarind pulp


Ingredients (Optional)
fried gram
Cashews chopped
Raisins
Grapes chopped
Chunks of ripe banana

Preparation:
1. Wash tamarind and soak it in ½ cup warm water till it softens.
2. Meanwhile, pluck the flowers from neem sprigs. Or alternate quick method: add the neem sprigs to a thin cloth, bring the edges of the cloth together so that they don’t fall off from the cloth. Hold the edges tightly and hit the cloth on to the kitchen counter several times. Neem flower or petals get separated from the sprigs and fall of in the cloth, collect them and set aside.
Add grated jaggery to another one cup water, stir till it melts off. Filter it through a coffee strainer to another bowl, filter the tamarind pulp or water as well to the same bowl.

Add rest of the ingredients. Mix well.

Wednesday, January 27, 2016

Algorithm my understanding


In computer programming, algorithm are the set of well defined instruction in sequence to solve a program. An algorithm should always have a clear stopping point.


Qualities of a good algorithm
•Precision – the steps are precisely stated(defined).
•Uniqueness – results of each step are uniquely definedand only depend on the input and the result of the precedingsteps.
•Finiteness – the algorithm stops after a finite number ofinstructions are executed.
•Input – the algorithm receives input.
•Output – the algorithm produces output.
•Generality – the algorithm applies to a set ofinputs.


Eg: Google Maps, Define from and to positions and click get directions, clear steps will be provided.


In Simple terms
1.Inputs and outputs should be defined precisely.
2.Each steps in algorithm should be clear and unambiguous.
3.Algorithm should be most effective among many different ways to solve a problem.
4.An algorithm shouldn't have computer code. Instead, the algorithm should be written in such a way that, it can be used in similar programming languages.
Every algorithm should have the following five characteristics:
1. Input
2. Output
3. Definiteness
4. Effectiveness
5. Termination

Examples Of Algorithms In Programming


Write an algorithm to add two numbers entered by user.
Step 1: Start
Step 2: Declare variables num1, num2 and sum.
Step 3: Read values num1 and num2.
Step 4: Add num1 and num2 and assign the result to sum.
        sum←num1+num2
Step 5: Display sum
Step 6: Stop


Write an algorithm to find the largest among three different numbers entered by user.
Step 1: Start
Step 2: Declare variables a,b and c.
Step 3: Read variables a,b and c.
Step 4: If a>b
           If a>c
              Display a is the largest number.
           Else
              Display c is the largest number.
        Else
           If b>c
              Display b is the largest number.
           Else
              Display c is the greatest number. 
Step 5: Stop


Write an algorithm to find the factorial of a number entered by user.
Step 1: Start
Step 2: Declare variables n,factorial and i.
Step 3: Initialize variables
          factorial←1
          i←1
Step 4: Read value of n
Step 5: Repeat the steps until i=n
     5.1: factorial←factorial*i
     5.2: i←i+1
Step 6: Display factorial
Step 7: Stop


Write an algorithm to check whether a number entered by user is prime or not.
Step 1: Start
Step 2: Declare variables n,i,flag.
Step 3: Initialize variables
        flag←1
        i←2 
Step 4: Read n from user.
Step 5: Repeat the steps until i<(n/2)
     5.1 If remainder of n÷i equals 0
            flag←0
            Go to step 6
     5.2 i←i+1
Step 6: If flag=0
           Display n is not prime
        else
           Display n is prime
Step 7: Stop


Write an algorithm to find the Fibonacci series till term≤1000.
Step 1: Start
Step 2: Declare variables first_term,second_term and temp.
Step 3: Initialize variables first_term←0 second_term←1
Step 4: Display first_term and second_term
Step 5: Repeat the steps until second_term≤1000
     5.1: temp←second_term
     5.2: second_term←second_term+first term
     5.3: first_term←temp
     5.4: Display second_term
Step 6: Stop


Write an algorithm for a for loop.
for (/*EXPRESSION 1*/;/*EXPRESSION 2*/;/*EXPRESSION 3*/) {
 /*STATEMENT 4*/
 }
would be:
1.EXPRESSION 1
2.If EXPRESSION 2 is false go to step 6
3.STATEMENT 4
4.EXPRESSION 3
5.Go to step 2
6.End

https://en.wikipedia.org/wiki/Algorithm

Tuesday, January 19, 2016

Database Basics


Basics of SQL

SQL is standard language for database. SQL is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).

DBMS processes the SQL request and then retrieves the data from DB and updates the Data in DB

 

Datatypes – SQL data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL.
Char,Varchar2, Number, Date etc

DDL – Data Definition Language: create, alter, drop, truncate

DML – Data Manipulation Language: select, insert, delete, update

DCL – Data Control Language: Grant, revoke

TCL – Transaction Control Language: commit, rollback

 

RDBMS Concepts - RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

Table: The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows

Field: Every table is broken up into smaller entities called fields, A field is a column in a table that is designed to maintain specific information about every record in the table

Row: A record, also called a row of data, is each individual entry that exists in a table. A record is a horizontal entity in a table

NULL – missing/unknown/inapplicable data, its not datavalue, it indicates value is not known, A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation

Constraints: Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table

Types of Constraints:

Not Null, Default, Unique, Primary key, Foreign Key, Check

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

Entity Integrity: There are no duplicate rows in a table.

Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity: Rows cannot be deleted, which are used by other records.

User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.

 

SQL - Syntax:

SQL is followed by unique set of rules and guidelines called Syntax. All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Important point to be noted is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements, but MySQL makes difference in table names.

 

Create Table – used to create tables in databases

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY( one or more columns )

);

 

Alter - Can be used for rename, add, modify or drop columns in an existing table

ALTER TABLE table_name

ADD column_name datatype;

 

ALTER TABLE table_name

DROP column_name datatype;

 

ALTER TABLE table_name

ALTER column_name datatype;

Drop

Rename – Can rename the table name

RENAME TABLE {tbl_name} TO {new_tbl_name};

Truncate – removes all rows from table and also releases storage space used by table

TRUNCATE TABLE  table_name;

Delete – used to delete the data from table and also can have where condition,

DELETE FROM table_name WHERE some_column=some_value;

OR

DELETE * FROM table_name;

OR

DELETE FROM table_name;

 

Difference between Delete and Truncate

  • Data can be recoved
  • DML/DDL stmt
  • Release memory

Retrieval of records

Select – Used to retrieve data from the specified table

SELECT column1, column2....columnN

FROM   table_name;

Or

SELECT * FROM   table_name;

Select with DISTINCT Clause – Used to retrieve distinct data from the table

SELECT DISTINCT column1, column2....columnN

FROM   table_name;


Select with WHERE Clause – used to retrieve data from table depending on user condition

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION;

 

Select with AND/OR Clause – Used to retrieve data from table using multiple conditions

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION-1 {AND|OR} CONDITION-2;

 

Select with IN Clause - Used to specify multiple values in where clause

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name IN (val-1, val-2,...val-N);

 

Select with BETWEEN Clause - used to select values within a user sepcified range

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name BETWEEN val-1 AND val-2;

 

Select with LIKE Clause - Used in where clause to search for a specified pattern

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name LIKE { PATTERN };

 

Select with COUNT Clause - used to count of the rows that matches specified criteria

SELECT COUNT(column_name)

FROM   table_name

WHERE  CONDITION;

 

Select with ORDER BY Clause - used with select statement to sort the result set

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION

ORDER BY column_name {ASC|DESC};

 

Select with GROUP BY Clause - Used with select statement to grouped the selection and aggregate functions are used in selection

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name;

 

Select with HAVING Clause - Having Clause used along with Group By Clause where aggregate functions are used for filtering the selection

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name

HAVING (arithematic function condition);

 

Alias name – Used to assign a temporary column/table name eg: select empno AS ”Employeeid“ from emp

All – Used to retrieve all the data from a column from a table eg: select ALL custname from emp

Relational operators - >,>=,<,<=,!=,,<>

Insert into – Used to insert new rows into table with null values, and also from different tables,

1st Form: INSERT INTO table_name  VALUES (value1,value2,value3,...);

2nd Form: INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

 

Update syntax – Used to update the data in table with or without where conditions
UPDATE table_name

 SET column1=value1, column2=value2,...

 WHERE some_column=some_value;

Aggregate functions – aggregate functions return a single value, calculated from values in a column, useful aggregate functions are as below

AVG() - Returns the average value, syntax: SELECT AVG(column_name) FROM table_name

COUNT() - Returns the number of rows, syntax: SELECT COUNT(column_name) FROM table_name;

FIRST() - Returns the first row of the table, syntax: SELECT FIRST(column_name) FROM table_name;

LAST() - Returns the last row of the table, syntax: SELECT LAST(column_name) FROM table_name;

MAX() - Returns the largest value in a column, syntax: SELECT MAX(column_name) FROM table_name;

MIN() - Returns the smallest value in a column, syntax: SELECT MIN(column_name) FROM table_name;

SUM() - Returns the sum of all the numeric values in a column, syntax: SELECT SUM(column_name) FROM table_name;

 

Scalar Functions - scalar functions return a single value, based on the input value, useful scalar functions are as below

UCASE() - Converts a field to upper case, syntax: SELECT UCASE(column_name) FROM table_name;

LCASE() - Converts a field to lower case, syntax: SELECT LCASE(column_name) FROM table_name;

MID() - Extract characters from a text field, syntax: SELECT MID(column_name,start,length) AS some_name FROM table_name;

LEN() - Returns the length of a text field, syntax: SELECT LEN(column_name) FROM table_name;

ROUND() - Rounds a numeric field to the number of decimals specified, syntax: SELECT ROUND(column_name,decimals) FROM table_name;

NOW() - Returns the current system date and time, syntax: SELECT NOW() FROM table_name;

FORMAT() - Formats how a field is to be displayed, syntax: SELECT FORMAT(column_name,format) FROM table_name;

 

Joins - The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each

Types of joins:

INNER JOIN: An SQL INNER JOIN returns all rows from multiple tables where the join condition is met. Also known as equijoin

Syntax: SELECT column_name(s)

 FROM table1

 INNER JOIN table2

 ON table1.column_name=table2.column_name;

 

OR

 

SELECT column_name(s)

 FROM table1

 JOIN table2

 ON table1.column_name=table2.column_name;

 

Returns all rows for matching rows from both the tables

 

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

Syntax: SELECT column_name(s)

                FROM table1

                LEFT JOIN table2

                ON table1.column_name=table2.column_name;

 

OR

 

SELECT column_name(s)

 FROM table1

 LEFT OUTER JOIN table2

 ON table1.column_name=table2.column_name;

 

Note: we can use “A= B(+)” on columns using in where condition for Left Outer Join (in Oracle)

 

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

Syntax: SELECT column_name(s)

                FROM table1

                RIGHT JOIN table2

                ON table1.column_name=table2.column_name;

 

OR

 

 

SELECT column_name(s)

                FROM table1

                RIGHT OUTER JOIN table2

                ON table1.column_name=table2.column_name;

 

Note: we can use “A(+)= B” on columns using in where condition for Right Outer Join(in Oracle)

 

FULL JOIN: Return all rows from both the tables along with the matching records

Syntax:  SELECT column_name(s)

                FROM table1

                FULL OUTER JOIN table2

                ON table1.column_name=table2.column_name;

 

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax: SELECT a.column_name, b.column_name

FROM table1 a, table1 b

WHERE a.common_field = b.common_field;

 

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

Syntax: SELECT table1.column1, table2.column2

FROM  table1, table2 [, table3 ]

 

SUBQuery: A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

There are a few rules that subqueries must follow:

Subqueries must be enclosed within parentheses.

A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.

An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.

Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.

The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.

A subquery cannot be immediately enclosed in a set function.

The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

 

Syntax: SELECT column_name [, column_name ]

FROM   table1 [, table2 ]

WHERE  column_name OPERATOR

                                (SELECT column_name [, column_name ]

                                FROM table1 [, table2 ]

                                [WHERE])

 

Independent Subquery – executed first, inner Query is not dependent on Outer query

e..g display all cut details having loan amount < cust id = 102

can be used with IN

Correlated subqueries – inner query executed for each n every row, Outer query needs to be executed before inner query. One of the most common example of correlated subquery is using keywords exits and not exits

e.g select * from cust_Fd where amount_doller < (Select max(amount_doller) from cust_loan whereloan_custid = cust_fd.custid)

 

 

Exists/Non Exists

Exists – returns true if subquery returns atleast one row

Non Exists – returns false if subquery returns 0 rows

 

Views – logical representation of subset of data from one or more tables, view is a virtual table

Create View - a view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. We can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Syntax: CREATE VIEW view_name AS

 SELECT column_name(s)

                FROM table_name

                WHERE condition

 

Updating a View - We can update a view by using the following syntax

Syntax: CREATE OR REPLACE VIEW view_name AS

 SELECT column_name(s)

                FROM table_name

                WHERE condition;

 

Syntax: CREATE OR REPLACE VIEW view_name AS

 SELECT column_name(s)

                FROM table_name

                WHERE condition

                WITH CHECK OPTION; -- With Check Option used if user tries to insert or delete the data from the view.

 

Syntax: CREATE OR REPLACE VIEW view_name AS

 SELECT column_name(s)

                FROM table_name

                WHERE condition

                WITH READ ONLY; -- With Read Only used and user tries to insert or delete the data from the view then system won’t allow.

 

Complex View – Query with group functions, from multiple tables

Drop view – Used to drop the view created

Syntax: DROP VIEW view_name

 

Why View

  • Retrict Access
  • Makes complex query easy
  • Present different view from same data table

Index – Speed up the retrival, automatically gets created for primary and unique

Manually can be done create index index_name on emp(lastname)

create index index_name on emp(upper(lastname))

Drop index index_name

  • Not to use index whn it is constantly updated
  • With lot of null values
  • Poor distribution of data
  • Having repeated values like Yes/No, Male/Female etc

SELECT max(salary) FROM emptable WHERE salary < (SELECT max(salary)   FROM emptable);

select max(Emp_Sal) from Employee a where 1 = ( select count(*)  from Employee b  where b.Emp_Sal > a.Emp_Sal)

Set operations - Set operators are used to join the results of two (or more) SELECT statements.The SET operators available are UNION,UNION ALL,INTERSECT,and MINUS.

Intersect – As name suggest common data available in two tables will be displayed, common parts

Syntax: SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions]

INTERSECT

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions];

 

Minus(Oracle)/Except(sql server) - The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

Syntax: SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions]

MINUS

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions];

 

Union – The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements.

Syntax: SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions]

UNION

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions];

 

Union ALL - The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements.

Syntax: SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions]

UNION ALL

SELECT expression1, expression2, ... expression_n

FROM tables

[WHERE conditions];

 

What is the difference between UNION and UNION ALL?

•UNION removes duplicate rows.

•UNION ALL does not remove duplicate rows.

 

Case Select - The SQL Server (Transact-SQL) CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement.

Syntax: CASE expression

                 WHEN value_1 THEN result_1

                 WHEN value_2 THEN result_2

                ...

                WHEN value_n THEN result_n

                ELSE result

END

OR

CASE

                 WHEN condition_1 THEN result_1

                WHEN condition_2 THEN result_2

                ...

                WHEN condition_n THEN result_n

                ELSE result

END

 

ISNULL/NVL – check null n replace, Replaces NULL with the specified replacement value.

Syntax: ISNULL ( check_expression , replacement_value )

 

Rank – Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question

Syntax: RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

Convert - The CONVERT() function is a general function that converts an expression of one data type to another.The CONVERT() function can be used to display date/time data in different formats.

Syntax: CONVERT(data_type(length),expression,style)

Eg: CONVERT(VARCHAR(10),GETDATE(),10)