Difference between DDL, DML, DCL and TCL

DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

DCL
Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Difference between Varchar, nVarchar, Char and nChar

VARCHAR and NVARCHAR
VARCHAR and NVARCHAR data types are both character data types that are variable-length. Below is the summary of the differences between these 2 data types:

You would use NVARCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.

CHAR and VARCHAR
CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).
You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

NCHAR and NVARCHAR
NCHAR and NVARCHAR data types are both Unicode character data types with a maximum length of 4,000 characters. The main difference between these 2 data types is that an NCHAR data type is fixed-length while an NVARCHAR is variable-length. If the number of characters entered in an NCHAR data type column is less than the specified column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for NCHAR is two times n bytes while for NVARCHAR is two times the number of characters entered (in bytes).
You should use NCHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use NVARCHAR when the data values in a column are expected to vary considerably in size.

CHAR and NCHAR
CHAR and NCHAR data types are both character data types that are fixed-length. Below is the summary of the differences between these 2 data types:

You would use NCHAR data type for columns that store characters from more than one character set or when you will be using characters that require 2-byte characters, which are basically the Unicode characters such as the Japanese Kanji or Korean Hangul characters.

ACID Property in Database

ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. An example of a transaction is a transfer of funds from one bank account to another, even though it might consist of multiple individual operations (such as debiting one account and crediting another).

Atomicity
Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are. For example, the transfer of funds from one account to another can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won't be debited if the other is not credited.

Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic” if when one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintains the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.

Either all Transactions are carried out or none are. The meaning is the transaction cannot be subdivided, and hence, it must be processed in its entirety or not at all. Users should not have to worry about the effect of incomplete Transactions in case of any system crash occurs. Transactions can be incomplete for three kinds of reasons: 1)Transaction can be aborted, or terminated unsuccessfully. This happens due to some anomalies arises during execution. If a transaction is aborted by the DBMS for some internal reason, it is automatically restarted and executed as new. 2)Due to system crash. This may be happen due to Power Supply failure while one or more Transactions in execution. 3)Due to unexpected situations. This may be happen due to unexpected data value or be unable to access some disk. So the transaction will decide to abort. (Terminate it).

Consistency
The consistency property ensures that the database remains in a consistent state; more precisely, it says that any transaction will take the database from one consistent state to another consistent state.

The consistency property does not say how the DBMS should handle an inconsistency other than ensure the database is clean at the end of the transaction. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction could be rolled back to the pre-transactional state - or it would be equally valid for the DBMS to take some patch-up action to get the database in a consistent state. Thus, if the database schema says that particular field is for holding integer numbers, the DBMS could decide to reject attempts to put fractional values in there, or it could round the supplied values to the nearest whole number: both options maintain consistency.

A DBMS that claims to enforce consistency is only responsible for those rules that are known to it. Thus, if a DBMS allows fields of a record to act as references to another record, then consistency implies the DBMS should enforce referential integrity: by the time any transaction ends, each and every reference in the database must be valid. If a transaction consisted of an attempt to delete a record referenced by another, each of the following mechanisms would maintain consistency:

• abort the transaction, rolling back to the consistent, pre-transactional state;

• delete all records that point at the deleted record (this is known as cascaded deletes); or,

• clear the relevant fields for all records that point at the deleted record.

Isolation
Isolation refers to the requirement that other operations cannot access or see the data in an intermediate state during a transaction. This constraint is required to maintain the performance as well as the consistency between transactions in a DBMS. Thus, each transaction is unaware of other transactions executing concurrently in the system. In a DBMS, many transactions may be executed simultaneously. These transactions should be isolated from each other. One’s execution should not affect the execution of other transactions. To enforce this concept DBMS has to maintain certain scheduling algorithms.

Durability
Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Many databases implement durability by writing all transactions into a transaction log that can be played back to recreate the system state right before a failure. A transaction can only be deemed committed after it is safely entered in the log.
Durability does not imply a permanent state of the database. Another transaction may overwrite any changes made by the current transaction without hindering durability.

Examples

Atomicity failure
Assume that a transaction attempts to subtract 10 from A and add 10 to B. If it were to succeed, this would be a valid transaction because A+B would still be 100. However, assume there is a problem with the system. After 10 is removed from A, the attempt to add 10 to B fails. The problem might be network failure, disk failure, power outage, program bug, etc. Atomicity requires that both parts of this transaction complete or none at all. There are two options: Attempt to add 10 to B again or undo the change to A. By undoing the change to A (adding 10 back to A), atomicity is accomplished.

Consistency failure
Consistency is a very general term that demands the data meets all validation rules that the overall application expects - but to satisfy the consistency property a database system only needs to enforce those rules that are within its scope. In the previous example, one rule was a requirement that A + B = 100; most database systems would not allow such a rule to be specified, and so would have no responsibility to enforce it - but they would be able to ensure the values were whole numbers. Example of rules that can be enforced by the database system are that the primary keys values of a record uniquely identify that record, that the values stored in fields are the right type (the schema might require that both A and B are integers, say) and in the right range, and that foreign keys are all valid.

Validation rules that cannot be enforced by the database system are the responsibility of the application programs using the database.

Isolation failure
To demonstrate isolation, at least two transactions must be executed at the same time. Isolation is easy to achieve if only one transaction is executed at a time. However, an extremely long transaction will block access to the database if it must run to completion before other transactions may begin. Therefore, the independent actions of each transaction are run in an interleaved manner.

Consider two transactions. One will transfer 10 from A to B. The other will transfer 10 from B to A. There are four actions. The first transaction will subtract 10 from A and add 10 to B. The second transaction will subtract 10 from B and add 10 to A. By interleaving the transactions, the actual order of actions will be: A-10, B-10, B+10, A+10. If isolation is maintained, the result after the first transaction is finished adding 10 to B will be identical to the result if the second transaction is not run. However, B will be 10 less due to the first action of the second transaction. This is known as a write-write failure because two transactions attempted to write to the same data field.

Durability failure
Assume that a transaction transfers 10 from A to B. It removes 10 from A. It then adds 10 to B. At this point, a "success" message is sent to the user. However, the changes are still queued in the disk buffer waiting to be committed to the disk. Power fails and the changes are lost. The user assumes that the changes have been made, but they are lost.

To satisfy the durability constraint, the database system must ensure the success message is delayed until the transaction is safely on disk. (Depending on the architecture of the database system, it may be enough to ensure that a transaction log has been fully written to disk; in the event of a crash and restart, the log will be replayed as far as possible before allowing applications to query or update the database.)

Explain Trigger

Trigger
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made ussing INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity
and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.

Following sql statements are not allowed in a trigger they are:-
ALL CREATE statements
ALL DROP statements
ALTER TABLE & ALTER DATABASE
TRUNCATE TABLE
GRANT AND REVOKE
UPDATE STATISTICS
RECONFIGURE
LOAD DATABASE AND LOAD TRANSACTION
ALL DISK statements
SELECT INTO

Types of Trigger
Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.

SQL Server 2000 has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type

After Triggers
Triggers that run after an update, insert, or delete can be used in several ways:

* Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
* Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
* Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.
Note: An AFTER trigger can be created only on tables, not on views.

Example
CREATE TRIGGER DelhiDel ON [Customers]
FOR DELETE
AS
IF (SELECT state FROM deleted) = Delhi
BEGIN
PRINT Can not remove customers from Delhi
PRINT Transaction has been canceled
ROOLBACK
END

Multiple After Triggers
More than one trigger can now be defined on a table for each Insert/Update/Delete. Although in general, you might not want to do this (it's easy to get confused if you over-use triggers), there are situations where this is ideal. One example that springs to mind is that you can split your triggers up into two categories:

* Application based triggers (cascading deletes or validation, for example).
* Auditing triggers (for recording details of changes to critical data).

This would allow you to alter triggers of one type without fear of accidentally breaking the other.

If you are using multiple triggers, it is of course essential to know which order they fire in. A new stored procedure called sp_settriggerorder allows you to set a trigger to be either the "first" or "last" to fire.

If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise. I would not recommend relying on this though.

Instead Of Triggers
Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) .

Creation of Triggers
Triggers are created with the CREATE TRIGGER statement. This statement specifies that the on which table trigger is defined and on which events trigger will be invoked.
To drop Trigger one can use DROP TRIGGER statement.

CREATE TRIGGER [owner.]trigger_name
ON[owner.] table_name
FOR[INSERT/UPDATE/DELETE] AS
IF UPDATE(column_name)
[{AND/OR} UPDATE(COLUMN_NAME)...]
{ sql_statements }

Explain Indexes in SQL Server

Indexes
Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

Here is how to use CREATE INDEX SQL statement to create an index on column Model in the Product table, called idxModel:

CREATE INDEX idxModel
ON Product (Model)

The syntax for creating indexes varies greatly amongst different RDBMS, that’s why we will not discuss this matter further.

There are some general rules which describe when to use indexes. When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins. Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval. Use indexes for columns that have many different values (there are not many repeated values within the column). Although indexes improve search performance, they slow the updates, and this might be something worth considering.

Types of Index
1. Cluster Index
2. Non Cluster Index

Cluster Index
A book index stores words in order with a reference to the page numbers where the word is located. This type of index for a database is a nonclustered index; only the index key and a reference are stored. In contrast, a common analogy for a clustered index is a phone book. A phone book still sorts entries into alphabetical order. The difference is, once we find a name in a phone book, we have immediate access to the rest of the data for the name, such as the phone number and address.

For a clustered index, the database will sort the table's records according to the column (or columns) specified by the index. A clustered index contains all of the data for a table in the index, sorted by the index key, just like a phone book is sorted by name and contains all of the information for the person inline. The nonclustered indexes created earlier in the chapter contain only the index key and a reference to find the data, which is more like a book index. You can only create one clustered index on each table.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.

Example:
CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID)

Non Cluster Index
A non-clustered index has the indexed columns and a pointer or bookmark pointing to the actual row. In the case of our example it contains a page number. Another example could be a search done on Google or another of the search engines. The results on the page contain links to the original web pages. The thing to remember about non-clustered indexes is that you may have to retrieve part of the required information from the rows in the table. When using a book index, you will probably have to turn to the page of the book. When searching on Google, you will probably have to click the link to view the original page. If all of the information you need is included in the index, you have no need to visit the actual data.

Nonclustered indexes have a structure completely separate from the data rows. The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key.

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or are clustered. For a heap, a row locator is a pointer to the row. For a table with a clustered index, the row locator is the clustered index key.

Example:
CREATE NONCLUSTERED INDEX sample_nonclust ON emp_sample(emp_name)

Advantages of Indexes
1. Searching For Records
2. Sorting Records
3. Grouping Records
4. Maintaining a Unique Column

Disadvantages of Indexes
1. Disk Space
2. Data Modification

Few Examples:
EXEC sp_helpindex EMPNAME

CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID)

CREATE UNIQUE CLUSTERED INDEX PK_Order_Details
ON [Order Details] (OrderID, ProductID)

CREATE INDEX IX_UnitPrice_ProductName ON Products(UnitPrice, ProductName)
EXEC sp_rename 'Products.IX_UnitPrice', 'IX_Price'

DROP Index Products.IX_Price

Diffence between function and stored procedure

Below is the major differences between function and stored procedure

1. Functions must return a value while procedure may or may not return any value.
2. Function can return only a single value at a time while procedure can return one, many or none.
3. Function can be called from sql statements while procedures can't.
4. Execution of procedure is faster then function
5. Function can be used in select Queries statement but in stored procedure it's not possible.
ex. Select max(salary) from tbl_salary

Useful SQL queries

Retrive the primary key/Identity column value after the insertion of records.
INSERT INTO tbl_adminuser (name,pass) VALUES ('Amit1', 'pass1');SELECT id FROM tbl_adminuser WHERE id = @@IDENTITY


Select rows where all the characters in a column are uppercase/lowercase
select * from tbl_adminuser where binary_checksum(pass)=binary_checksum('Pal')

How to select Duplicate Rows from a table
select email,count(*) from finaldatabase_Amit group by email having count(*)>1

Insert rows/records from one table to another table
insert into tbl_logo_Info_event (ImageURL_link,ImageURL_Show, Image_Name,Image_Title, Image_Profile) select ImageURL_link,ImageURL_Show,Image_Name,Image_Title,Image_Profile from tbl_logo_Info where ImageID='16'

Select two random rows from SQL server database table
Select top 2 * from tbl_news order by newid()

Select desired rows from table by IN Keyword
select * from tbl_logo_Info where ImageID IN(4,6,10) order by image_Title asc

Searching in a column of any table by a substring
Example : Searching 'Independence day' text in column name leave_desc for 'epen' substring.
SELECT leave_desc FROM tbl_calendar WHERE substring(leave_desc, 4, 4) = 'epen'

Retrieve Second highest salary from EMP table
SELECT MIN(Salary) FROM EMP WHERE
Salary IN (SELECT distinct TOP 2 Salary FROM EMP ORDER BY Salary DESC)

OR

SELECT Salary from EMP e
where 2 =(select count(Salary) Salary from EMP
where e.Salary <= Salary) as well as.... Third highest salary
SELECT MIN(Salary) FROM EMP WHERE
Salary IN (SELECT distinct TOP 3 Salary FROM EMP ORDER BY Salary DESC)

OR

SELECT TOP 1 ID FROM (SELECT DISTINCT TOP 2 ID FROM EMPNAME ORDER BY ID desc) a ORDER BY ID asc


Fourth Highest salary
SELECT MIN(Salary) FROM EMP WHERE
Salary IN (SELECT distinct TOP 4 Salary FROM EMP ORDER BY Salary DESC)

Creating new table from other tables
Creating new table(without records) from other tables
SELECT tbl_adminuser.pass,tbl_test.id,tbl_test.address INTO third_tab FROM tbl_adminuser,tbl_test WHERE 1 = 2

Creating new table(with records) from other tables
SELECT tbl_adminuser.pass,tbl_test.id,tbl_test.address INTO third_tab FROM tbl_adminuser,tbl_test WHERE tbl_test.id > 20

Insert rows/records to new table from two or more table's query result set
Select * into approved_nominations from (select * from Approved_DL_bestSchool union all select * from Approved_DL_bestuniversity union all select * from Approved_DL_bstEnggCollege) as alias_tbl_name

Join query in detail for SQL Server

We have three types of Joins in sql.

1. Inner join
2. Outer join
3. Cross join

1. Inner join:
Inner join is the default type of join, it will produces the result set, which contains matched rows only.

We have three types of Joins in INNER JOIN

a. Self Join
b. Equi Join
c. Natural join

a. Self Join:
A join joins with itself is called self join, working with self joins we use alias tables.

b. Equi Join:
DISPLAYS ALL THE MATHCING ROWS FROM JOINED TABLE. AND ALSO DISPLAYS REDUNDANT VALUES. IN THIS WE USE * SIGN TO JOIN THE TABLE.

Example.
Create table EMPNAME (ID int, EMPNAME varchar(20))
CREATE table EMPADDRESS (ID int, ADDRESS varchar(20))

INSERT INTO EMPNAME VALUES(1, 'DEEPAN')
INSERT INTO EMPNAME VALUES(2, 'RAMESH')
INSERT INTO EMPNAME VALUES(3, 'PRADEEP')
INSERT INTO EMPADDRESS VALUES(1, 'BANGALORE')
INSERT INTO EMPADDRESS VALUES(2, 'DELHI')
INSERT INTO EMPADDRESS VALUES(4, 'DELHI')

SELECT * FROM EMPNAME
ID EMPNAME
----------- --------------------
1 DEEPAN
2 RAMESH
3 PRADEEP

SELECT * FROM EMPADDRESS
ID ADDRESS
----------- --------------------
1 BANGALORE
2 DELHI
4 DELHI

Equi Join example
SELECT * FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

c. Natural Join
DISPLAYS ALL THE MATHCING ROWS FROM
JOINED TABLE.IT RESTRICT
REDUNDANT VALUES.

Example:
SELECT A.*, E.ID, E.EMPNAME FROM EMPADDRESS A
INNER JOIN
EMPNAME E ON E.ID = A.ID

This natural join query will return all the columns of categories table and prodcutId and productName from products table. We can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.

Inner Join Query Example by specifying column names:
SELECT A.ID, A.ADDRESS, E.ID, E.EMPNAME FROM EMPADDRESS A INNER JOIN
EMPNAME E ON E.ID = A.ID

This inner join query will display only the specified column names of both the tables.

Example : INNER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

OR

SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
INNER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID


Output of the above JOIN Query (i.e Inner Query)
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI

2. Outer join:
Outer join produces the results, which contains matched rows and unmatched rows.

Outer join is further classified as three types.

a. Left outer join
b. Right outer join
c. Full outer join.

a. Left outer join:
Left outer join produces the results, which contains all the rows from left table and matched rows from right table.

Example : LEFT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
LEFT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above LEFT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL

b.Right outer join:
Right outer join produces the resultset, which contains all the rows from right table and matched rows from left table.

Example : RIGHT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
RIGHT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above RIGHT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
NULL NULL DELHI

c. Full outer join:
Full outer join produces the resultset, which contains all the rows from left table and all the rows from right table.

Example : FULL OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
FULL OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above FULL OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL
NULL NULL DELHI

3. Cross join:
A join without having any condition is known as cross join, in cross join every row in first table is joins with every row in second table. Cross join is nothing but cartesian product.

Example : CROSS JOIN
===================================================================
SELECT * FROM EMPNAME CROSS JOIN EMPADDRESS