What is Self-Join in SQL

What is Self-Join in SQL?

A self-join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table it-self means that each row of the table is combined with itself and with every other row of the table.

The self-join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

The syntax of the command for joining a table to itself is almost same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have same name. Table name aliases are defined in the FROM clause of the SELECT statement. See the syntax:

SELECT a.column_name, b.column_name…..

FROM table1 a, table1 b

WHERE a.common_filed = b.common_field;

Example of SQL SELF JOIN

In the following example we will use the table EMPLOYEE twice and in order to do this we will use the alias of the table.

To get the list of employees and their Manager the following sql statement has used:

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "EmployeeName", 

b.emp_id AS "Manager ID",b.emp_name AS "ManagerName" 

FROM employee a, employee b 

WHERE a.emp_supv = b.emp_id;

CAST Function in SQL

CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Table Student

StudentID	First_Name	Marks
1	Anil	95.9
2	Sunil	93.5
3	Ram	80
4	sumit	70.1

Example 

SELECT First_Name, CAST(Marks AS Integer) marks FROM Student_Score;

OutPut:

First_Name	Int_Marks
Anil	96
Sunil	94
Ram	80
sumit	70

CAST in SQL

CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CAST function. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Table Student

StudentID	First_Name	Marks
1	Anil	95.9
2	Sunil	93.5
3	Ram	80
4	sumit	70.1

Example 

SELECT First_Name, CAST(Marks AS Integer) marks FROM Student_Score;

OutPut:

First_Name	Int_Marks
Anil	96
Sunil	94
Ram	80
sumit	70

SQL Server CONVERT() Function

SQL Server CONVERT() Function

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)

Value	Description
data_type(length)	Specifies the target data type (with an optional length)
expression	Specifies the value to be converted
style	Specifies the output format for the date/time (see table below)

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-14
11-04-2014
04 Nov 14
04 Nov 2014
04 Nov 2014 11:45:34:243

What is SQL Indexes? Type of SQL Indexes.

What is SQL Indexes? Type of SQL Indexes.

Indexes are lookup tables that the database search engine can use to speed up data retrieval. Put, an index is a pointer to data in a table. An index in a database is very similar to an index in a book

An index helps speed up SELECT command and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements.

Indexes can be created or dropped with no effect on the data

Creating an index need the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order

The CREATE INDEX Command:

  CREATE INDEX index_name ON table_name;

Single-Column Indexes:

CREATE INDEX index_name on table_name (column_name);

Composite Indexes:

A composite index is an index on two or more columns of a table

CREATE INDEX index_name on table_name (col1, col2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very often in a query's WHERE clause.

Implicit Indexes:

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints

The DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved

DROP INDEX index_name

We should avoid indexes in.

Though indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered

• It should not be used on small tables.
• Tables that have frequent, large batch update or insert operations.
• It should not be used on columns that contain a high number of NULL values.
• Columns that are frequently manipulated should not be indexed.