Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View , Index using Client-Data sever(two tier)

Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View , Index using Client-Data sever(two tier)


                                                                     
                                           INDEX


********************************************************************************************************************************************
        INDEXING
********************************************************************************************************************************************

 * A database index is a data structure that improves the speed of operations in a table. Indexes can be
created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of
access to records.

 * While creating index, it should be considered that what are the columns, which will be used to make SQL queries
and create one or more indexes on those columns.
 * Practically, indexes are also type of tables, which keep primary key or index field and a pointer to each record into
the actual table.
 * The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search
Engine to locate records very fast.
 * INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become
fast on those tables. The reason is that while doing insert or update, database needs to insert or update index
values as well.


CREATING INDEX:
 
 UNIQUE & SIMPLE INDEX: 
  
  * A unique index means that two rows cannot have the same index value.
  * Simple index can have same values.
  
  CREATING UNIQUE INDEX:
   
   CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);
   
   eg, create unique index ind1 on books (book_id, book_name)
  
  CREATING SIMPLE INDEX:
   
   eg, create index index2 on books(book_id);



                                                        VIEWS

 


********************************************************************************************************************************************
        VIEWS
********************************************************************************************************************************************

 * In some cases, it is not desirable for all users to see the entire logical model (that is, all the actual columns stored in the database.).
 * Consider a person who needs to know a customers name, loan number and branch name, but has no need to see the loan amount.
 * A view provides a mechanism to hide certain data from the view of certain users.
 * Any relation that is not of the conceptual model but is made visible to a user as a virtual relation is called a view.
 
 
CREATING VIEW:
 
 create view V as select * from books; // V is view name
 
DELETING VIEW:
 
 drop view V;
 
SHOWING VIEWS:
 
 show tables;
 
SHOWING CONTAINTS OF VIEWS:
 
 select * from V;
 
 
 
NOTE:
 views are same as tables. so opertions on tables can be applicable on views too.

Post a Comment

Previous Post Next Post