Sql Functions, Joins, Auto Increment Field and Sql Sequences

By | February 25, 2016

AUTO INCREMENT a Field

   Auto-increment allows a unique number to be generated when a new record is inserted into a table.

Syntax for MySQL
The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100

To insert a new record into the “Persons” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName) VALUES (‘Lars’,’Monsen’)

The SQL statement above would insert a new record into the “Persons” table. The “ID” column would be assigned a unique value. The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.

Syntax for SQL Server
The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the “ID” column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
To insert a new record into the “Persons” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName) VALUES (‘Lars’,’Monsen’);

The SQL statement above would insert a new record into the “Persons” table. The “ID” column would be assigned a unique value. The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.

Syntax for Access
The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.

Tip: To specify that the “ID” column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).

To insert a new record into the “Persons” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName) VALUES (‘Lars’,’Monsen’);

The SQL statement above would insert a new record into the “Persons” table. The “P_Id” column would be assigned a unique value. The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.

SQL View

A view in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

Syntax for creating a View,

CREATE or REPLACE view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Example of Creating a View

Consider following Sale table,

oid
order_name
previous_balance
customer
11
ord1
2000
Alex
12
ord2
1000
Adam
13
ord3
2000
Abhi
14
ord4
1000
Adam
15
ord5
2000
Alex

SQL Query to Create ViewCREATE or REPLACE view sale_view as select * from Sale where customer = ‘Alex’;

The data fetched from select statement will be stored in another object called sale_view. We can use create seperately and replace too but using both together works better.

Example of Displaying a View

Syntax of displaying a view is similar to fetching data from table using Select statement.SELECT * from sale_view;

Force View Creation

Force keyword is used while creating a view. This keyword force to create View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated.

Syntax for forced View is,

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Update a View

Update command for view is same as for tables.
Syntax to Update a View is,

UPDATE view-name
set value
WHERE condition;

If we update a view it also updates base table data automatically.

Read-Only View

We can create a view with read-only option to restrict access to the view.
Syntax to create a view with Read-Only Access

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition with read-only

The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.

Types of View

There are two types of view,

  • Simple View
  • Complex View
Simple View
Complex View
Created from one table
Created from one or more table
Does not contain functions
Contain functions
Does not contain groups of data
Contains groups of data

SQL Functions

SQL provides many built-in functions to perform operations on data. These functions are useful while performing mathematical calculations, string concatenations, sub-strings etc. SQL functions are divided into two catagories,

  • Aggregrate Functions
  • Scalar Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.
Useful 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.
Useful 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

Aggregrate Functions

These functions return a single value after calculating from a group of values.Following are some frequently used Aggregrate functions.

1) AVG()            Average returns average value after calculating from values in a numeric column.
Its general Syntax is,SELECT AVG(column_name) from table_name

Example using AVG()

Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000

SQL query to find average of salary will be,

SELECT avg(salary) from Emp;

Result of the above query will be,

avg(salary)
8200


2) COUNT()
Count returns the number of rows present in the table either based on some condition or without condition.

Its general Syntax is,SELECT COUNT(column_name) from table-name
Example using COUNT()

Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000

SQL query to count employees, satisfying specified condition is,

SELECT COUNT(name) from Emp where salary = 8000;

Result of the above query will be,

count(name)
2

Example of COUNT(distinct)

Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000

SQL query is,SELECT COUNT(distinct salary) from emp;

Result of the above query will be,

count(distinct salary)
4

3) FIRST()

First function returns first value of a selected column
Syntax for FIRST function is,SELECT FIRST(column_name) from table-name

Example of FIRST()

Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000
SQL query

SELECT FIRST(salary) from Emp;

Result will be,

first(salary)
9000

4) LAST()
LAST return the return last value from selected column
Syntax of LAST function is,SELECT LAST(column_name) from table-name

Example of LAST()

Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000

SQL query will be,SELECT LAST(salary) from emp;

Result of the above query will be,

last(salary)
8000

5) MAX() :

MAX function returns maximum value from selected column of the table.

Syntax of MAX function is,SELECT MAX(column_name) from table-name

Example of MAX()
Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000

SQL query to find Maximum salary is,

SELECT MAX(salary) from emp;

Result of the above query will be,

MAX(salary)
10000

6) MIN()

MIN function returns minimum value from a selected column of the table.

Syntax for MIN function is,SELECT MIN(column_name) from table-name

Example of MIN()

Consider following Emp table,

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000
SQL query to find minimum salary is,

SELECT MIN(salary) from emp;

Result will be,

MIN(salary)
8000

7) SUM()

SUM function returns total sum of a selected columns numeric values.

Syntax for SUM is,SELECT SUM(column_name) from table-name

Example of SUM()

Consider following Emp table

eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
10000
405
Tiger
35
8000

SQL query to find sum of salaries will be,

SELECT SUM(salary) from emp;

Result of above query is,

SUM(salary)
41000

Read

20 Cool funny Websites Kills Your Boredom-2016

18 Cool and Interesting Websites 2016 – Internet

The Art of Writing a Perfect Resume – Allinfi


Scalar Functions

Scalar functions return a single value from an input value. Following are soe frequently used Scalar Functions.

1) UCASE()

UCASE function is used to convert value of string column to Uppercase character.

Syntax of UCASE,

SELECT UCASE(column_name) from table-name

Example of UCASE()

Consider following Emp table

eid
name
age
salary
401
anu
22
9000
402
shane
29
8000
403
rohan
34
6000
404
scott
44
10000
405
Tiger
35
8000

SQL query for using UCASE is,

SELECT UCASE(name) from emp;

Result is,

UCASE(name)
ANU
SHANE
ROHAN
SCOTT
TIGER

2) LCASE()

LCASE function is used to convert value of string column to Lowecase character.

Syntax for LCASE is,SELECT LCASE(column_name) from table-name

Example of LCASE()

Consider following Emp table

eid
name
age
salary
401
anu
22
9000
402
shane
29
8000
403
rohan
34
6000
404
scott
44
10000
405
Tiger
35
8000

SQL query for converting string value to Lower case is,

SELECT LCASE(name) from emp;

Result will be,

LCASE(name)
anu
shane
rohan
scott
tiger

3) MID()

MID function is used to extract substrings from column values of string type in a table.

Syntax for MID function is,SELECT MID(column_name, start, length) from table-name

Example of MID()

Consider following Emp table

eid
name
age
salary
401
anu
22
9000
402
shane
29
8000
403
rohan
34
6000
404
scott
44
10000
405
Tiger
35
8000

SQL query will be,

select MID(name,2,2) from emp;

Result will come out to be,

MID(name,2,2)
nu
ha
oh
co
ig

4) ROUND()

ROUND function is used to round a numeric field to number of nearest integer. It is used on Decimal point values. Syntax of Round function is,

SELECT ROUND(column_name, decimals) from table-name

Example of ROUND()

Consider following Emp table

eid
name
age
salary
401
anu
22
9000.67
402
shane
29
8000.98
403
rohan
34
6000.45
404
scott
44
10000
405
Tiger
35
8000.01

SQL query is,

SELECT ROUND(salary) from emp;

Result will be,

ROUND(salary)
9001
8001
6000
10000
8000

Join in SQL :

SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. SQL Join is used for combining column from two or more tables by using values common to both tables. JoinKeyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is(n-1) where n, is number of tables. A table can also join to itself known as, Self Join.

Join Types:
There are different types of joins available in SQL:

  1. INNER JOIN: returns rows when there is a match in both tables.
  2. LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
  3. RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
  4. FULL JOIN: returns rows when there is a match in one of the tables.
  5. 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.
  6. CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
  7. Cross JOIN or Cartesian Product
    database-sql-joins-in-details-diagrams

This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table.

Cross JOIN Syntax is,

SELECT column-name-list
from table-name1
CROSS JOIN
table-name2;

Example of Cross JOIN

The class table,

ID
NAME
1
abhi
2
adam
4
alex

The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI

Cross JOIN query will be,

SELECT *
from class,
cross JOIN class_info;

The result table will look like,

ID
NAME
ID
Address
1
abhi
1
DELHI
2
adam
1
DELHI
4
alex
1
DELHI
1
abhi
2
MUMBAI
2
adam
2
MUMBAI
4
alex
2
MUMBAI
1
abhi
3
CHENNAI
2
adam
3
CHENNAI
4
alex
3
CHENNAI

INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query.

Inner Join Syntax is,

SELECT column-name-list
from table-name1
INNER JOIN
table-name2
WHERE table-name1.column-name = table-name2.column-name;

Example of Inner JOIN

The class table,

ID
NAME
1
abhi
2
adam
3
alex
4
anu

The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI

Inner JOIN query will be,

SELECT * from class, class_info where class.id = class_info.id;

The result table will look like,

ID
NAME
ID
Address
1
abhi
1
DELHI
2
adam
2
MUMBAI
3
alex
3
CHENNAI

Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural Join Syntax is,

SELECT *
from table-name1
NATURAL JOIN
table-name2;

Example of Natural JOIN

The class table,

ID
NAME
1
abhi
2
adam
3
alex
4
anu

The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

The result table will look like,

ID
NAME
Address
1
abhi
DELHI
2
adam
MUMBAI
3
alex
CHENNAI

In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.

Outer JOIN

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the lefttable and null for the right table’s column.

Left Outer Join syntax is,

SELECT column-name-list
from table-name1
LEFT OUTER JOIN
table-name2
on table-name1.column-name = table-name2.column-name;

Left outer Join Syntax for Oracle is,

select column-name-list
from table-name1,
table-name2
on table-name1.column-name = table-name2.column-name(+);

Example of Left Outer Join

The class table,

ID
NAME
1
abhi
2
adam
3
alex
4
anu
5
ashish

The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI
7
NOIDA
8
PANIPAT

Left Outer Join query will be,

SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id=class_info.id);

The result table will look like,

ID
NAME
ID
Address
1
abhi
1
DELHI
2
adam
2
MUMBAI
3
alex
3
CHENNAI
4
anu
null
null
5
ashish
null
null

Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.

Right Outer Join Syntax is,

select column-name-list
from table-name1
RIGHT OUTER JOIN
table-name2
on table-name1.column-name = table-name2.column-name;

Right outer Join Syntax for Oracle is,

select column-name-list
from table-name1,
table-name2
on table-name1.column-name(+) = table-name2.column-name;

Example of Right Outer Join

The class table,

ID
NAME
1
abhi
2
adam
3
alex
4
anu
5
ashish

The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI
7
NOIDA
8
PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info on (class.id=class_info.id);

The result table will look like,

ID
NAME
ID
Address
1
abhi
1
DELHI
2
adam
2
MUMBAI
3
alex
3
CHENNAI
null
null
7
NOIDA
null
null
8
PANIPAT

Full Outer Join

The full outer join returns a result table with the matched data of two table then remaining rows of both lefttable and then the right table.

Full Outer Join Syntax is,

select column-name-list
from table-name1
FULL OUTER JOIN
table-name2
on table-name1.column-name = table-name2.column-name;

Example of Full outer join is,

The class table,

ID
NAME
1
abhi
2
adam
3
alex
4
anu
5
ashish

The class_info table,

ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI
7
NOIDA
8
PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info on (class.id=class_info.id);

The result table will look like,

ID
NAME
ID
Address
1
abhi
1
DELHI
2
adam
2
MUMBAI
3
alex
3
CHENNAI
4
anu
null
null
5
ashish
null
null
null
null
7
NOIDA
null
null
8
PANIPAT
database-sql-joins-flow-chart-diagram

SQL Sequence:

Sequence is a feature supported by some database systems to produce unique values on demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence. AUTO_INCREMENT is applied on columns, it automatically increments the column value by 1 each time a new record is entered into the table. Sequence is also some what similar to AUTO_INCREMENT but its has some extra features.

Creating Sequence

Syntax to create sequences is,

CREATE Sequence sequence-name
start with initial-value
increment by increment value
maxvalue maximum-value
cycle|nocycle

initial-value specifies the starting value of the Sequence, increment-value is the value by which sequence will be incremented and maxvalue specifies the maximum value until which sequence will increment itself. cyclespecifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining. No cycle specifies that if sequence exceeds maxvalue an error will be thrown.

Example to create Sequence

The sequence query is following,

CREATE Sequence seq_1
start with 1
increment by 1
maxvalue 999
cycle ;

Example to use Sequence

The class table,

ID
NAME
1
abhi
2
adam
4
alex

The sql query will be,

INSERT into class value(seq_1.nextval,’anu’);

Result table will look like,

ID
NAME
1
abhi
2
adam
4
alex
1
anu

Once you use nextval the sequence will increment even if you don’t Insert any record into the table.

You can feel free to comment if you have any queries, Will be appreciated. Thank You..!!

Leave a Reply

Your email address will not be published. Required fields are marked *