SQL Joins

What is a join where and how is it used?

A join is a query that combines rows from two or more tables.whenever multiple tables appear in a query from clause then we can use joins to execute that query.

TYPES OF JOINS

1. EQUI JOIN
2. NON EQUI JOIN
3. OUTER JOIN
4. SELF JOIN

EQUI JOIN : In equi join more then one table are joined together with the help of common column that exists in both table. this is also called as simple join or inner join
Table aliases: These are used in place of table name for the select statement and valid only for current select statement.

NON EQUI JOIN :A non equi join is a join condition containing other then equal operator. condition such as <=,>=,<>,Between.

OUTER JOIN : a) left outer join b)right outer join
outer join

SQL Functions

SQL Functions : These functions can be used to perform calculation,comparison,modification of data

The two types of functions in sql are
1.Single-row functions: Operates on single row and returns one result for row
2.Multiple-row functions :operates on group of rows

The functions are
1.character function :accepts character data as input and return both character and numeric value
a.case manipulation
b.character manipulation

2.Number function: accepts numeric input and return numeric value

3.Date function :stores date in internal numeric format default display as DD-MON-YY.

4.Conversion function: convert the data from char to varchar, varchar to char, number to date, date to varchar.


5.General function :These function work with any data type
NVL(expr1, expr2) : Converts nall value to actual value.
NVL2(expr1, expr2, expr3) : If expr1 is null return expr2,not null return expr3.
NULLIF(expr1, expr2) : Compare two expressions and return null if they are equal or the
first if they are not equal.
COALESCE(expr1, expr2,........ exprn):returns the first not null expr.

6.Aggregate function : SUM,AVG,MAX,MIN,COUNT(*),COUNT(column name). these all are the aggregate functions.

GROUP BY Clause : group by clause is used to categorize the query result
HAVING CLAUSE : Used for restricting groups.

SQL operators

There are two general classes of sql operators are
Unary :unary operator operates only on one operand like
operator operand
Binary :Binary operator operates only on two operand like
operand1 operator operand2


Sorting of rows can be done by the ORDER BY clause it is used to arrange the output default is ascending order this should be the last in the select statement.

SQL Statements

As we discussed earlier there are different types of statements in sql like select,insert,update,delete,create,drop,alter..etc we discuss them in deatil.

DQL-select
Select is used to view the already created table in the database.

DDL-
Create-Create is used to create a new table or a database
Alter-Alter is used to change the column(add,modify,drop) of the already created table.
Drop-Drop is used to delete all data and structures in the table.
Rename-Rename is used to change the name of the table
Truncate-Truncate removes all data from the table and release storage space.
difference between drop and truncate is dropdoes not release the storage space but truncate does.

DML-
Insert-Insert statement is used to add new rows to the table.
Update-Update statement is used to modify or update a current row in the table
Delete-Delete is used to remove the existing row from the table.
Merge-Merge is used to update or insert a row conditionally.in place of multiple insert or update statements we can use merge statement.

TCL-
Commit-

Elements of SQL

The basic elements of SQL are DATATYPES different types of datatypes are..

















A data type is an attribute that specifies the type of data that the object can hold

Char Data type :This specify the character string and has a length specified by size.if you insert a shorter value the blank space will be counted and if you insert a longer value then it returns a error.Maximum length allowed is 2000 Bytes.

Varchar2 : this specify a variable length character string.maximum of 4000 bytes.

Numeric Data type : This number data type stores +ve & -ve numbers including zero.

Date :The date data type stores Date and time information.It can be represented in both character and number data type.

Time Stamp : This is extension of date data type it stores year,month,day,hour,minute, and second value it also stores fractional seconds.

Blob Data type : It stores unstructured binary data it can store upto 8terabytes of binary data.

Clob Data types : It stores character data it can store upto 8terabytes of character data

LITERALS : These are fixed data values similarly like constants.

Text Literals : Text,character and string these are always surrounded by single quotations mark.can have maximum length of 4000 bytes.

Numeric Literals : These are used to specify fixed and floating point number stores maximum of 38 digits of precision.

Date Literals : we can specify date as string literals or can convert char or num. value to date value.

Null Value : If a column in a table has no value then it is said to be Null. A column can have many null values unless its restricted by primary key or not null.in a column no value is equal to null or no two null values are same

Comments : comments are used to understand table clearly.this is optional.
begin with /* and end the statement with */.

SQL Introduction

There are set of statements in sql which contains set of commands for each statement

• DQL (Data query language),
• DDL(Data definition language),
• DML(Data manuplation language),
• DCL(Data control language),
• TCL(Transaction control language)

I will discuss about each statement clearly in the next blogs....

what is SQL

SQL ----> Structured Query Language

SQL (Structured Query Language) is used to communicate with the database.As the name
itself suggests that its a query language we use certain type of query's to communicate with the
database.
It is a set of statements with which the user access the database