Decode SQL

Usage : 

Decodes the values of a column with the one specified

How the Decode SQL works????

Consider  table "Participant_List" as shown below

Case :

List all the rows in a table with location specifying the full name

Syntax:

Select decode(<column_name>,
                       value 1,decode_value 1,
                       value 2,decode_value 2,
                       ...
                                   ,default_value)
from <table_name>

Sample Query:

Select participant_id,participant_name,participant_age,
           decode(location,
                        'IN','India',
                        'AU','Australia',
                        'SG','Singapore',
                        'US','United States'
                               ,'Unknown')
from Participant_List

Result:    

      

Continue reading →

Merge SQL

Usage : 

Merging tables - Update the existing rows and insert the new rows in base table from new table

How the Merge SQL works????

Consider two tables "Student_List" and "Student_New"  as shown below

Case :

Merge the table "Student_New" with "Student_List"

Syntax:

Merge into <base_table_name>

using <new_table_name>

on <condition>

when matched then

<update_query>

when not matched then

<insert_query>

Sample Query:

Merge into Student_List A
using Student_New B
on (A.student_id = B.student_id and 
      A.student_name = B.student_name)
when matched then
update set A.student_age = B.student_age 
                ,A.student_grade = B.student_grade
                 ,A.skills = B.skills
when not matched then
insert (A.student_id,A.student_name,A.student_age,
           A.student_grade,A.skills)
values(B.student_id,B.student_name,B.student_age,
            B.student_grade,B.skills)

Result:          

Continue reading →

Alias in SQL

Usage : 

Assign temporary alias name to columns and table

How the Alias works????

Consider the table "Student_List"  as shown below

Case 1:

Retrieve all the columns in the table "Student_List" replacing student with participant for all the columns

Syntax:

Select <column name> as <alias name> from table <table_name>

Sample Query:

select student_id as "Participant_id",
student_name as "Participant_name",
student_age as "Participant_age",
student_grade as "Participant_grade",
skills as "participant_skill" from student_list

Result:

Note: we can have atmost 30 characters for alias name

Continue reading →

SELECT with WHERE clause

Usage : 

Extract the data from the database which matches desired criteria

General Syntax:

Select * from table <table_name> where <condition>

How the SELECT with WHERE clause works????

Consider the table "Student_List"  as shown below

Case 1:

Retrieve all the rows in the table "Student_List" with "Student_Age" equals 10

Syntax:

Select * from table <table_name> where <column_name> = <value>

Actual Query:

Select * from table Student_List where Student_Age = 10

Result:

Note: we can use all relational operation such as >,<,>=,<=,<> in where condition

Case 2:

Retrieve all the rows in the table "Student_List" with "Skills" matching 'Singing' or 'Dancing'

Syntax:

Select * from table <table_name> where <column_name> in (<value 1>,<value 2>...<value n>)

Actual Query:

Select * from table Student_List where Skills in ('Singing','Dancing')

Result:

Note: Strings should be enclosed within single quotes (like 'Singing','Dancing' in above case)

Case 3:

Retrieve all the rows in the table "Student_List" with "Student_Age" between 6 to 10

Syntax:

Select * from table <table_name> where <column_name> between <value 1> and <value 2>

Actual Query:

Select * from table Student_List where Student_Age between 6 and 10

Result:

Continue reading →

SELECT SQL

Usage : 

Extract the data from the database

How the SELECT query works????

Consider the table "Student_List"  as shown below

Case 1:

Retrieve all the columns in the table "Student_List"

Syntax:

Select * from table <table_name>

Sample Query:

Select * from table Student_List

Result:

Case 2:

Retrieve only desired columns in the table "Student_List". Lets say (Student_ID,Student_Name) in this case

Syntax:

Select <column 1>,<column 2>...<column n> from table <table_name>

Sample Query:

Select Student_Id,Student_Name from table Student_List

Result:

Continue reading →

SQL - Database Language

What is SQL?????

SQL stands for Structured Query Language. We are storing large volume of data in databases. So how the human interact with the database to store and retrieve the data, There comes the language which helps us to communicate with database called Structured Query Language (SQL). SQL is known as the standard language for relational database management systems.

Types of SQL 

There are two main types of SQL statement 

• DDL - Data Definition Language
• DML - Data Manipulation Language

Data Definition Language - DDL

As the name suggests it is mainly used to describe how the data resides in the database. Under DDL we have the following

• Create
• Alter
• Drop

Data Manipulation Language - DML

DML is mainly used for manipulating the data stored in the database. Under DML we have the following

• Select
• Insert 
• Update

Continue reading →