Wednesday, January 9, 2019

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 to run standalone mock server on local laptop

 Please download the standalone wiremock server from Direct download section at the bottom of the page.  Download and installation Feel fre...