DBMS Lab Manual. Jitendra Inc. Patel

this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

      We could also create an index with more than one field as in the example below:

      CREATE INDEX supplier_idx

      ON supplier (supplier_name, city);

      We could also choose to collect statistics upon creation of the index as follows:

      CREATE INDEX supplier_idx

      ON supplier (supplier_name, city)

      COMPUTE STATISTICS;

      Drop an Index

      The syntax for dropping an index is:

      DROP INDEX index_name;

      For example:

      DROP INDEX supplier_idx;

      In this example, we're dropping an index called supplier_idx.

      Practical 2: Create, alter and update views

      A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.

      Creating a VIEW

      The syntax for creating a VIEW is:

      CREATE VIEW view_name AS

      SELECT columns

      FROM table

      WHERE predicates;

      For example:

      CREATE VIEW sup_orders AS

      SELECT suppliers.supplier_id, orders.quantity, orders.price

      FROM suppliers, orders

      WHERE suppliers.supplier_id = orders.supplier_id

      and suppliers.supplier_name = 'IBM';

      This would create a virtual table based on the result set of the select statement. You can now query the view as follows:

      SELECT *

      FROM sup_orders;

      Updating a VIEW

      You can update a VIEW without dropping it by using the following syntax:

      CREATE OR REPLACE VIEW view_name AS

      SELECT columns

      FROM table

      WHERE predicates;

      For example:

      CREATE or REPLACE VIEW sup_orders AS

      SELECT suppliers.supplier_id, orders.quantity, orders.price

      FROM suppliers, orders

      WHERE suppliers.supplier_id = orders.supplier_id

      and suppliers.supplier_name = 'Microsoft';

      Dropping a VIEW

      The syntax for dropping a VIEW is:

      DROP VIEW view_name;

      For example:

      DROP VIEW sup_orders;