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 inserted into the table.
Sequence is also some what similar to AUTO_INCREMENT but it has some additional features too.
Creating a Sequence
Syntax to create a sequence is,
CREATE SEQUENCE sequence-name
START WITH initial-value
INCREMENT BY increment-value
MAXVALUE maximum-value
CYCLE | NOCYCLE;
The initial-value specifies the starting value for the Sequence.
The increment-value is the value by which sequence will be incremented.
The maximum-value specifies the upper limit or the maximum value upto which sequence will increment itself.
The keyword CYCLE specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining.
And, NO CYCLE specifies that if sequence exceeds MAXVALUE value, an error will be thrown.
Using Sequence in SQL Query
Let’s start by creating a sequence, which will start from 1, increment by 1 with a maximum value of 999.
CREATE SEQUENCE seq_1
START WITH 1
INCREMENT BY 1
MAXVALUE 999
CYCLE;
Now let’s use the sequence that we just created above.
Below we have a class table,
ID
NAME
1
abhi
2
adam
4
alex
The SQL query will be,
INSERT INTO class VALUE(seq_1.nextval, 'anu');
Resultset 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.
Alias is used to give an alias name to a table or a column, which can be a resultset table too. This is quite useful in case of large or complex queries. Alias is mainly used for giving a short alias name for a column or a table with complex names.
Syntax of Alias for table names,
SELECT column-name FROM table-name AS alias-name
Following is an SQL query using alias,
SELECT * FROM Employee_detail AS ed;
Syntax for defining alias for columns will be like,
SELECT column-name AS alias-name FROM table-name;
Example using alias for columns,
SELECT customer_id AS cid FROM Emp;
Example of Alias in SQL Query
Consider the following two tables,
The class table,
ID
Name
1
abhi
2
adam
3
alex
4
anu
5
ashish
and the class_info table,
ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI
7
NOIDA
8
PANIPAT
Below is the Query to fetch data from both the tables using SQL Alias,
SELECT C.id, C.Name, Ci.Address from Class AS C, Class_info AS Ci where C.id = Ci.id;
and the resultset table will look like,
ID
Name
Address
1
abhi
DELHI
2
adam
MUMBAI
3
alex
CHENNAI
SQL Alias seems to be quite a simple feature of SQL, but it is highly useful when you are working with more than 3 tables and have to use JOIN on them.
SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. It is used for combining column from two or more tables by using values common to both tables.
JOIN Keyword 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, which is known as, Self Join.
Types of JOIN
Following are the types of JOIN that we can use in SQL:
Inner
Outer
Left
Right
Cross JOIN or Cartesian Product
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
Following is the class table,
ID
NAME
1
abhi
2
adam
4
alex
and 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 resultset 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
As you can see, this join returns the cross product of all the records present in both the tables.
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 SQL 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
Consider a class table,
ID
NAME
1
abhi
2
adam
3
alex
4
anu
and the class_info table,
ID
Address
1
DELHI
2
MUMBAI
3
CHENNAI
Inner JOIN query will be,
SELECT * from class INNER JOIN class_info where class.id = class_info.id;
The resultset 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.
The syntax for Natural Join is,
SELECT * FROM
table-name1 NATURAL JOIN table-name2;
Example of Natural JOIN
Here is the class table,
ID
NAME
1
abhi
2
adam
3
alex
4
anu
and 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 resultset 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 resultset table with the matched data from the two tables and then the remaining rows of the left table and null from the right table’s columns.
Syntax for Left Outer Join is,
SELECT column-name-list FROM
table-name1 LEFT OUTER JOIN table-name2
ON table-name1.column-name = table-name2.column-name;
To specify a condition, we use the ON keyword with Outer Join.
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
Here is the class table,
ID
NAME
1
abhi
2
adam
3
alex
4
anu
5
ashish
and 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 resultset 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 resultset table with the matched data from the two tables being joined, then the remaining rows of the right table and null for the remaining left table’s columns.
Syntax for Right Outer Join 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
Once again the class table,
ID
NAME
1
abhi
2
adam
3
alex
4
anu
5
ashish
and 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 resultant 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 resultset table with the matched data of two table then remaining rows of both left table and then the right table.
Syntax of Full Outer Join 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
and 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 AND and OR operators are used with the WHERE clause to make more precise conditions for fetching data from database by combining more than one condition together.
AND operator
AND operator is used to set multiple conditions with the WHERE clause, alongside, SELECT, UPDATE or DELETE SQL queries.
Example of AND operator
Consider the following Emp table
eid
name
age
salary
401
Anu
22
5000
402
Shane
29
8000
403
Rohan
34
12000
404
Scott
44
10000
405
Tiger
35
9000
SELECT * FROM Emp WHERE salary < 10000 AND age > 25
The above query will return records where salary is less than 10000 and age greater than 25. Hope you get the concept here. We have used the AND operator to specify two conditions with WHERE clause.
eid
name
age
salary
402
Shane
29
8000
405
Tiger
35
9000
OR operator
OR operator is also used to combine multiple conditions with WHERE clause. The only difference between AND and OR is their behaviour.
When we use AND to combine two or more than two conditions, records satisfying all the specified conditions will be there in the result.
But in case of OR operator, atleast one condition from the conditions specified must be satisfied by any record to be in the resultset.
Example of OR operator
Consider the following Emp table
eid
name
age
salary
401
Anu
22
5000
402
Shane
29
8000
403
Rohan
34
12000
404
Scott
44
10000
405
Tiger
35
9000
SELECT * FROM Emp WHERE salary > 10000 OR age > 25
The above query will return records where either salary is greater than 10000 or age is greater than 25.
The distinct keyword is used with SELECT statement to retrieve unique values from the table. Distinct removes all the duplicate records while retrieving records from any table in the database.
Syntax for DISTINCT Keyword
SELECT DISTINCT column-name FROM table-name;
Example using DISTINCT Keyword
Consider the following Emp table. As you can see in the table below, there is employee name, along with employee salary and age.
In the table below, multiple employees have the same salary, so we will be using DISTINCT keyword to list down distinct salary amount, that is currently being paid to the employees.
eid
name
age
salary
401
Anu
22
5000
402
Shane
29
8000
403
Rohan
34
10000
404
Scott
44
10000
405
Tiger
35
8000
SELECT DISTINCT salary FROM Emp;
The above query will return only the unique salary from Emp table.
Having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group by based SQL queries, just like WHERE clause is used with SELECT query.
Syntax for HAVING clause is,
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name condition
GROUP BY column_name
HAVING function(column_name) condition
Example of SQL Statement using HAVING
Consider the 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
Suppose we want to find the customer whose previous_balance sum is more than 3000.
We will use the below SQL query,
SELECT *
FROM sale GROUP BY customer
HAVING sum(previous_balance) > 3000
Result will be,
oid
order_name
previous_balance
customer
11
ord1
2000
Alex
The main objective of the above SQL query was to find out the name of the customer who has had a previous_balance more than 3000, based on all the previous sales made to the customer, hence we get the first row in the table for customer Alex.
Group by clause is used to group the results of a SELECT query based on one or more columns. It is also used with SQL functions to group the result from one or more tables.
Syntax for using Group by in a statement.
SELECT column_name, function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name
Example of Group by in a Statement
Consider the following Emp table.
eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
9000
405
Tiger
35
8000
Here we want to find name and age of employees grouped by their salaries or in other words, we will be grouping employees based on their salaries, hence, as a result, we will get a data set, with unique salaries listed, along side the first employee’s name and age to have that salary. Hope you are getting the point here!
group by is used to group different row of data together based on any one column.
SQL query for the above requirement will be,
SELECT name, age
FROM Emp GROUP BY salary
Result will be,
name
age
Rohan
34
Shane
29
Anu
22
Example of Group by in a Statement with WHERE clause
Consider the following Emp table
eid
name
age
salary
401
Anu
22
9000
402
Shane
29
8000
403
Rohan
34
6000
404
Scott
44
9000
405
Tiger
35
8000
SQL query will be,
SELECT name, salary
FROM Emp
WHERE age > 25
GROUP BY salary
Result will be.
name
salary
Rohan
6000
Shane
8000
Scott
9000
You must remember that Group By clause will always come at the end of the SQL query, just like the Order by clause.
Order by clause is used with SELECT statement for arranging retrieved data in sorted order. The Order by clause by default sorts the retrieved data in ascending order. To sort the data in descending order DESC keyword is used with Order by clause.
Syntax of Order By
SELECT column-list|* FROM table-name ORDER BY ASC | DESC;
Using default Order by
Consider the 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
SELECT * FROM Emp ORDER BY salary;
The above query will return the resultant data in ascending order of the salary.
eid
name
age
salary
403
Rohan
34
6000
402
Shane
29
8000
405
Tiger
35
8000
401
Anu
22
9000
404
Scott
44
10000
Using Order by DESC
Consider the Emp table described above,
SELECT * FROM Emp ORDER BY salary DESC;
The above query will return the resultant data in descending order of the salary.
WHERE clause is used to specify/apply any condition while retrieving, updating or deleting data from a table. This clause is used mostly with SELECT, UPDATE and DELETEquery.
When we specify a condition using the WHERE clause then the query executes only for those records for which the condition specified by the WHERE clause is true.
Syntax for WHERE clause
Here is how you can use the WHERE clause with a DELETE statement, or any other statement,
DELETE FROM table_name WHERE [condition];
The WHERE clause is used at the end of any SQL query, to specify a condition for execution.
Time for an Example
Consider a table student,
s_id
name
age
address
101
Adam
15
Chennai
102
Alex
18
Delhi
103
Abhi
17
Banglore
104
Ankit
22
Mumbai
Now we will use the SELECT statement to display data of the table, based on a condition, which we will add to our SELECT query using WHERE clause.
Let’s write a simple SQL query to display the record for student with s_id as 101.
SELECT s_id,
name,
age,
address
FROM student WHERE s_id = 101;
Following will be the result of the above query.
s_id
name
age
address
101
Adam
15
Noida
Applying condition on Text Fields
In the above example we have applied a condition to an integer value field, but what if we want to apply the condition on name field. In that case we must enclose the value in single quote ' '. Some databases even accept double quotes, but single quotes is accepted by all.
SELECT s_id,
name,
age,
address
FROM student WHERE name = 'Adam';
Following will be the result of the above query.
s_id
name
age
address
101
Adam
15
Noida
Operators for WHERE clause condition
Following is a list of operators that can be used while specifying the WHERE clause condition.
SELECT query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete table data, or partial by specifying conditions using the WHERE clause.
Syntax of SELECT query
SELECT query is used to retieve records from a table. We can specify the names of the columns which we want in the resultset.
SELECT
column_name1,
column_name2,
column_name3,
...
column_nameN
FROM table_name;
Time for an Example
Consider the following student table,
s_id
name
age
address
101
Adam
15
Chennai
102
Alex
18
Delhi
103
Abhi
17
Banglore
104
Ankit
22
Mumbai
SELECT s_id, name, age FROM student;
The above query will fetch information of s_id, name and age columns of the student table and display them,
s_id
name
age
101
Adam
15
102
Alex
18
103
Abhi
17
104
Ankit
22
As you can see the data from address column is absent, because we did not specif it in our SELECT query.
Select all records from a table
A special character asterisk* is used to address all the data(belonging to all columns) in a query. SELECT statement uses * character to retrieve all records from a table, for all the columns.
SELECT * FROM student;
The above query will show all the records of student table, that means it will show complete dataset of the table.
learngram.wordpress.com 