SQL Aggregate/ Scalar Functions

SQL Aggregate/ Scalar Functions

SQL built-in functions Used in calculations on data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

SQL aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

SQL scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

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.