Scribd Logo
Upload

Welcome to Scribd!

  • 27 views

    DMA Chapter No3

    Uploaded by

    Kiran Kshirsagar

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    DMA Chapter No3

    Uploaded by

    Kiran Kshirsagar
    27 views26 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    27 views26 pages

    DMA Chapter No3

    Uploaded by

    Kiran Kshirsagar

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 26

    Amrutvahini Polytechnic Sangamner

    Program – IF Class – IF4I

    Course – Database Management Code – 22416

    Staff – Navale N.D


    Chapter No 3
    Advanced SQL (12 M)

    •View
    •Sequence
    •Index
    •Snapshot
    •Synonyms
    View
    • In SQL, a view is a virtual table based on the
    result-set of an SQL statement.
    • A view contains rows and columns, just like a
    real table. The fields in a view are fields from
    one or more real tables in the database.
    • View is logical part of table which can contain
    all rows of a table or select some rows from a
    table.
    Syntax
    • CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
    Operation on View
    • Create View
    • Update View
    • Drop View
    Create View

    • CREATE VIEW [Brazil Customers] AS


    SELECT CustomerName, ContactName
    FROM Customers
    WHERE Country = 'Brazil';
    Update View
    • Syntax
    • CREATE OR REPLACE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name WHERE condition;
    • Example
    • CREATE OR REPLACE VIEW [Brazil Customers] AS
    SELECT CustomerName, ContactName, City
    FROM Customers
    WHERE Country = ‘INDIA';
    Drop View
    • SQL DROP VIEW Syntax

    • DROP VIEW view_name;

    Example

    • DROP VIEW [Brazil Customers];


    Index
    • Indexes are used to retrieve data from the
    database more quickly.
    • The users cannot see the indexes, they are just
    used to speed up searches/queries.
    CREATE INDEX Syntax

    • CREATE INDEX index_name


    ON table_name (column1, column2, ...);
    •
    Create index I1 on employee(emp_id);
    Types of Index
    • Simple Index
    • Unique Index
    • Composite Index
    Unique Index
    • Duplicate values are not allowed:
    Syntax
    • CREATE UNIQUE INDEX index_name
    ON table_name (column1, column2, ...);

    Example
    Create unique index UI on employee(emp_id);
    Simple Index

    • This index is applicable on single column or attribute.

    • CREATE INDEX idx ON Persons (LastName);

    • DROP INDEX index_name;


    • Drop index idx;
    Composite Index
    This index is applicable for more than one
    column/attribute.

    • CREATE INDEX idx ON Persons (LastName,


    FirstName);
    Sequence
    • Sequence is a set of integers 1, 2, 3, … that are generated and
    supported by some database systems to produce unique
    values on demand.
    • A sequence is a user defined schema bound object that
    generates a sequence of numeric values.
    • Sequences are frequently used in many databases because
    many applications require each row in a table to contain a
    unique value and sequences provides an easy way to generate
    them.
    • The sequence of numeric values is generated in an ascending
    or descending order at defined intervals and can be
    configured to restart when exceeds max_value.
    Syntax:
    • CREATE SEQUENCE <sequence_name> [INCREMENT BY increment_value] [START
    WITH initial_value][MINVALUE minimum value] [MAXVALUE maximum value]
    [CYCLE|NOCYCLE] ;

    • sequence_name: Name of the sequence.


    • initial_value: starting value from where the 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 sequence reaches its set_limit it starts from beginning.
    • nocycle: An exception will be thrown if sequence exceeds its max_value.
    • Example 1:
    • CREATE SEQUENCE sequence_1 start with 1
    increment by 1 minvalue 0 maxvalue 100
    cycle;
    • Example to use sequence :
    • CREATE TABLE students ( ID number(10), NAME
    char(20) );
    • Now insert values into table
    • INSERT into students
    VALUES(sequence_1.nextval,'Ramesh'); INSERT
    into students
    VALUES(sequence_1.nextval,'Suresh');
    Synonym
    • Synonym is a alternate name for
    table,view,sequence,index.

    • Create synonym for object_name;

    • Create synonym for student;


    • Create synonym for index1;
    • Create synonym for view1;
    • Create synonym for sequence1;
    SNAPSHOT
    • Snapshot is a recent copy of the table from
    the database or a subset of rows/columns of a
    table. The SQL statement that creates and
    subsequently maintains a snapshot normally
    reads data from the database residing server.
    A snapshot is created on the destination
    system with the create snapshot SQL
    command. The remote table is immediately
    defined and populated from the master table.
    • Two types of snapshots are available.
    • Simple snapshots
    • Complex snapshots
    • Simple snapshot :
    In simple snapshot, each row is based on a single row in a
    single remote table. This consists of either a single table or
    a simple SELECT of rows from a single table.
    • Syntax
    • Create snapshot snapshot_name as select columns from
    table

    • Example –
    • CREATE SNAPSHOT emp_snap as select * from emp;
    • Complex snapshot :
    In complex snapshot, a row may be based on
    more than one row in a remote table via
    GROUP BY operation or result of Multi-Table
    Join. This consists of joined tables, views, or
    grouped and complex SELECT statement
    queries.
    • Example –
    • CREATE SNAPSHOT sampleSnps1 AS SELECT
    student.rollno, student.name FROM student
    UNION ALL SELECT new_student.rollno,
    new_student.name FROM new_student;
    • Advantages :
    • Response time is improved when local read-only copy of table
    exists.
    • Once snapshot is built on remote database, if node containing
    data from which the snapshot is built is not available. Snapshot
    can be used without need to access the unavailable database.
    • Ease network loads.
    • Data subsetting.
    • Disconnected computing.
    • Mass deployment.
    • Disadvantages :
    • Snapshots are not reachable when primary database goes offline.
    • It does not support full text indexing.
    • Snapshot runs out of disk if data changes frequently faster.
    • As no.of snapshots increases, disk space becomes problematic.
    • Applications :
    • Protects data.
    • Maintains history of data.
    • Used in testing application software.
    • Used in data mining.
    • Recovers data when information is lost because of human error or corruption of
    data.

    You might also like