what do you mean by sequence in structured query language [SQL]?

Problem statement: what do you mean by sequence in SQL, can you write a script for creating the sequence & using it into the database?

Sequence is a set of integers 1, 2, 3, 4, 5, ... that are generated on supported by database systems to produce unique values on demand.

1. A sequence is a user defined schema bound object that generates a sequence of numeric values.
2. Sequences are frequently used in many databases because many applications requires each row in a table to contain unique value & sequences provides an easy way to generate them.
3. The sequence of numeric values is generated in an ascending or descending order at defined intervals & can be configured to restart when exceeds max_value.

Syntax:

       CREATE SEQUENCE sequence_name

       START WITH initial_value

       INCREMENT BY increment_value

       MINVALUE minimum_value

       MAXVALUE maximum_value

       CYCLE | NOCYCLE ;

Description:

• sequence_name: Name of the sequence
• initial_value: starting value from where sequence starts. initial_value should be greater than      or equal to minimum_value AND less than equal to maximum_value.
• increment_value: value by which sequence will increment itself. increment_value can be positive or negative.
• minimum_value: minimum value of the sequence
• maximum_value: maximum value of the sequence
• CYCLE: when cycle reaches its set_limit it starts from beginning.
• NOCYCLE: An exception will be thrown if sequence exceeds its maximum_value

[a] Example: creating sequence in ascending order

      CREATE SEQUENCE sequence1

      start with 1

      increment by 1

      minvalue 0

      maxvalue 1000

      cycle ;

Illustrations: Above script will create a sequence by name sequence1. Sequence will starts from 1 & will be incremented by 1 having maximum value 100. Sequence will repeat itself from start value after exceeding 1000.

[b] Example: creating sequence in descending order

      CREATE SEQUENCE sequence2

      start with 100

      increment by -1

      minvalue 1

      maxvalue 100

      cycle ;

Illustration: Above query will create a sequence named sequence2. Sequence will starts from 100 & should be less than or equal to maximum value & will be incremented by -1 having minimum value 1

[c] Example to use sequence: create a table named employee with columns as id & name.

      CREATE TABLE employee

      (

         ID number(10),

         NAME varchar(20)

      );

     Let's insert values into the table:

     INSERT into employee VALUES(sequence1.nextval, 'Parth');

     INSERT into employee VALUES(sequence1.nextval, 'Madhu');

     INSERT into employee VALUES(sequence1.nextval, 'Rajnish');

     INSERT into employee VALUES(sequence1.nextval, 'Karthik');

      

Here sequence1.nextval will insert id's in id column in a sequence as defined in sequence1

Output:

           ID  |   NAME

           --- ---------------

           1    |   Parth

           2    |   Madhu

           3    |   Rajnish

           4    |   Karthik

      

How do you display 2nd max, 3rd max, 4th max, ... so on salary from a table? [Co-Related subquery]

Problem statement: display max salary based on certain row's id like 2nd rows, 3rd rows, 4th row, 5th row, ... etc. 

• CO-RELATED SubQuery: combination of subquery & join

// 2nd max salary:
mysql>SELECT * FROM EMPLOYEE A WHERE 1 = (SELECT COUNT(*) FROM EMP B WHERE A.SALARY < B.SALARY);
output: it will 2nd max salary, because row id begins with 0 & here i'm giving row's id as 1

// 3rd max salary:
mysql>SELECT * FROM EMPLOYEE A WHERE 2 = (SELECT COUNT(*) FROM EMP B WHERE A.SALARY < B.SALARY);

// 4th max salary:
mysql>SELECT * FROM EMPLOYEE A WHERE 3 = (SELECT COUNT(*) FROM EMP B WHERE A.SALARY < B.SALARY);

// Nth max salary:
mysql>SELECT * FROM EMPLOYEE A WHERE N-1 = (SELECT COUNT(*) FROM EMP B WHERE A.SALARY < B.SALARY);

• Using sub-query:(3rd max salary):

SELECT MIN(EmpSalary) from (
SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3
);

How do you reverse the order of letter in string using SQL?

Problem statement: How do you use reverse function in sql?
Syntax:
SELECT reverse(column_name) FROM Table;

e.g.
id | name | salary
01  ram      2000
02  rani      3000             ==> old_emp
03  lata       1500
04  mary    1700

mysql>SELECT reverse(name) FROM old_emp;
output:
reverse(name)
----------------
mar
inar
atal
yram

How do you insert old table record to new table?

Problem statement: How do you insert data of one table to other table for back up purpose?

Syntax:
INSERT new_table SELECT * FROM old_table;

e.g.

id | name | salary
01  ram      2000
02  rani      3000             ==> old_emp [old_table]
03  lata       1500
04  mary    1700

INSERT new_emp SELECT * FROM old_emp;

id | name | salary
01  ram      2000
02  rani      3000             ==> new_emp [new_table] 
03  lata       1500
04  mary    1700

How do you copy old table to new table within same database?

Problem statement: How do you copy Old table TO New table with whole structures (column name, data type, datatype size, constraints) within the same database !

Syntax:
CREATE TABLE new_table LIKE old_table

e.g.
mysql>show tables;
------------------
tables in my db|
------------------
old_emp

mysql> CREATE TABLE new_emp LIKE old_emp;
QUERY OK, 0 rows affected(0.33sec)

verify:
mysql>show tables;
------------------
tables in my db|
------------------
old_emp
new_emp
------------------

How do you find 2nd max salary from emp table?

Problem statement: how do you find 2nd maximum salary from employee table?

id | name | salary
01  ram      2000
02  rani      3000
03  lata       1500
04  mary    1700

[1] SELECT MAX(salary) FROM EMPLOYEE; 
//output (return): 3000

[2] SELECT MAX(salary) FROM EMPLOYEE where salary < (SELECT MAX(salary) FROM Employee); 
//output (return) - 2000 that is 2nd highest salary