Additional Features of Advance SQL Assignment Help
Additional Features of SQL are described below with examples.
- SQL DECODE: SQL Decode is a function in Oracle which is used as if-then-else type logic in SQL. It is not used in MySQL or SQL Server.
Syntax:
{` SELECT DECODE ("column_name", "search_value_1", "result_1",Example: There is one table product_detail with fields prod_id and prod_name
["search_value_n", "result_n"],
{"default_result"} ); `}
{`
SELECT DECODE (prod_name,
'shoes', 's',
'belts', 'b') prod_id
FROM product_detail;
`}
- SQL AUTO INCREMENT: If we applied a field in a table as primary key and auto increment then when we inserts value then the server will automatically insert numerical primary key in the field.
Syntax:
{` CREATE TABLE TABLE_NAME (PRIMARY_KEY_COLUMN INT PRIMARY KEY IDENTITY ( ["Initial_Value"], ["Interval"] ), ...); `}Example: We are going to create a table named customer with fields ID, NAME, AGE in which the ID is primary key and auto incremented field.And when we insert value.
SQL_Advance: SQL AUTO INCREMENT
- SQL IDENTITY: SQL Identity is similar to auto increment command. It is used to make a field auto incremented.
Syntax:
{` CREATE TABLE TABLE_NAME (PRIMARY_KEY_COLUMN INT PRIMARY KEY IDENTITY ( ["Initial_Value"], ["Interval"] ), ...);Example: Create a table named customer with fields ID, NAME, AGE in which the ID is primary key and given identity start from 2. And when we insert value then it will start the auto increment from 2.
`}
{`
CREATE TABLE customer
(id int PRIMARY KEY IDENTITY(2,1),
name nvarchar(50),
age int);
`}
- SQL SEQUENCE and NEXTVAL: SQL LIMIT is used for limiting the result..
Syntax:
{` CREATE SEQUENCE SEQUENCE_NAME [START WITH {"Initial_Value"}] [INCREMENT BY {"interval"}];Example: We have created a table user_detail with fields userid, username, age
`}
{`
CREATE SEQUENCE SEQ_USER START WITH 5 INCREMENT BY 10;
INSERT INTO user_detail values (SEQ_USER.NEXTVAL, 'JOHN', 45)
INSERT INTO user_detail values (SEQ_USER.NEXTVAL, 'SAM', 41)
`}
Table Name:user_detail
| ID | User Name | Age |
|---|---|---|
| 10 | JOHN | 45 |
| 20 | SAM | 41 |
To submit Advanced SQL assignment click here
Embed this Infographic on your site(Copy this Code)
Courtesy of: AssignmentHelpNet
