Joins In SQL

The SQL Joins is used to combine records from two or more tables in a database. A JOIN is a means for joining fields from two tables by using values common to each. It is visible that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available in SQL:

·        INNER JOIN: returns rows when there is a match in both tables.

·        LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

·        RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

·        FULL JOIN: returns rows when there is a match in one of the tables.

·   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.

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

Useful Functions in SQL

Following is the list of all useful SQL built-in functions:

•    COUNT - The SQL COUNT aggregate function is used to count the number of rows in a database table.
•    SQRT - This is used to generate a square root of a given number.
•    RAND - This is used to generate a random number using SQL command.
•    MAX - The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
•    MIN - The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
•    CONCAT - This is used to concatenate any string inside any SQL command.
•    Numeric - Complete list of SQL functions required to manipulate numbers in SQL.
•    String - Complete list of SQL functions required to manipulate strings in SQL.
•    AVG - The SQL AVG aggregate function selects the average value for certain table column.
•    SUM - The SQL SUM aggregate function allows selecting the total for a numeric column.

CREATE INDEX IN SQL

Indexes allow the database application to find data fast without reading the entire table.

The CREATE INDEX statement is used to create indexes in tables

Indexes

An index can be created in a table to find data more rapidly and efficiently.users cannot see the indexes, they are just used to speed up searches/queries.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name ON table_name (column_name)

CREATE INDEX Example

The SQL statement below creates an index named "EmpIndex " on the "Emp_id" column in the "EmpMaster" table:

CREATE INDEX EmpIndex ON EmpMaster (Emp_id)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX EmpIndex ON EmpMaster(Emp_id, Emp_Name)

For More details on SQL INDEX please check

http://mysqlfacts.blogspot.in/2015/12/sql-index.html

Keys in SQL server.

Key is a sole or mixture of multiple fields in a table. It is used to fetch records/data-rows from data table according to the requirement. Keys are also used to create relationship among different database tables or views.

Types of SQL Keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

1. Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.

2. Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

 

3. Primary Key

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.

4. Alternate key

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

5. Composite/Compound Key

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

6. Unique Key

Unique key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it cannot have duplicate values.

7. Foreign Key

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. 

What is an Operators in SQL

What is an Operators in SQL?

An operator is a reserved word or a character used mainly in an SQL statement's WHERE clause to perform operation, like comparisons and arithmetic. Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

•         Arithmetic operators
•          Comparison operators
•          Logical operators

SQL Arithmetic Operators:

•          +  Addition - Adds values on either side of the operator
•          - Subtraction - Subtracts right hand operand from left hand operand
•          * Multiplication - Multiplies values on either side of the operator             
•          / Division - Divides left hand operand by right hand operand      
•          % Modulus - Divides left hand operand by right hand operand and returns remainder   

SQL Comparison Operators:

•          = Checks if the values of two operands are equal or not, if yes then condition becomes true.
•          != Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.     
•          <> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
•          > Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
•          < Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
•          >= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
•           <= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
•          !< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
•          !> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

SQL Logical Operators:

•          ALL: The ALL operator is used to compare a value to all values in another value set.
•          AND: The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
•          ANY: The ANY operator is used to compare a value to any applicable value in the list according to the condition.
•          BETWEEN: The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
•          EXISTS: The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
•          IN: The IN operator is used to compare a value to a list of literal values that have been specified.
•          LIKE: The LIKE operator is used to compare a value to similar values using wildcard operators.
•          NOT: The NOT operator reverses the meaning of the logical operator with which it is used.
•          OR: The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
•         IS NULL: The NULL operator is used to compare a value with a NULL value.
•          UNIQUE: The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).