Scribd
Upload
135 views4 pages

Database Views and Indexes Guide

This document is a lab manual from the Department of Computer Science & Information Technology at Khwaja Fareed University of Engineering & Information Technology. It discusses creating and manipulating databases, specifically views and indexes. It provides the syntax for creating views with the CREATE VIEW statement and modifying or deleting views. It also describes indexes as copies of table columns that can be searched efficiently, and discusses clustered and non-clustered indexes. The lab tasks involve creating a view on two tables from a database, creating an index on a table column, altering a view to filter on a condition, and dropping a view.

Uploaded by

Shahid Zikria
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
135 views4 pages

Database Views and Indexes Guide

This document is a lab manual from the Department of Computer Science & Information Technology at Khwaja Fareed University of Engineering & Information Technology. It discusses creating and manipulating databases, specifically views and indexes. It provides the syntax for creating views with the CREATE VIEW statement and modifying or deleting views. It also describes indexes as copies of table columns that can be searched efficiently, and discusses clustered and non-clustered indexes. The lab tasks involve creating a view on two tables from a database, creating an index on a table column, altering a view to filter on a condition, and dropping a view.

Uploaded by

Shahid Zikria
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Department of Computer Science & Information Technology

Khwaja Fareed University of Engineering & Information Technology

Course: Database Systems

Lab Instructor:
Ms. Hira Kanwal

Student Name

Student Roll #

Department

Batch/Year/Section

For Lab. Instructor

Marks Signature

KFUEIT Department of CS/IT


Lab Manual # 11 Creating & Manipulating Databases

11.1. Objective
1. Views
2. Indexes

11.2. VIEW

The SQL VIEW is, a virtual table that does not physically exist. Rather, it is created by a
SQL statement that joins one or more tables.

A view always shows up-to-date data. The database engine recreates the data, using the
view's SQL statement, every time a user queries a view.

Syntax:

CREATE VIEW view_name AS

SELECT columns

FROM table

[WHERE condition];

11.2.1. Modifying a View

To Modify a view use the following statement:

Alter VIEW myview AS


select * from Products where ProductCategory='A';

11.2.2. Delete a View

To delete a view , use the following command

DROP VIEW view_name

11.3. INDEX

An index is a copy of selected columns of data from a table that can be searched very
efficiently.

A database index is a data structure that improves the speed of data retrieval operations on
a database table at the cost of additional writes and storage space to maintain the index data
structure.

An Index can be Clustered or non-clustered

A clustered index actually describes the order in which records are physically stored on the
disk, hence the reason you can only have one. A clustered index is essentially a sorted copy
of the data in the indexed columns.

KFUEIT Department of CS/IT


85
Lab Manual # 11 Creating & Manipulating Databases

A Non-Clustered Index defines a logical order that does not match the physical order on
disk.

Notes:

1. A table that has a clustered index is referred to as a clustered table and is stored in a
B-tree structure. A table that has no clustered index is referred to as a heap.
2. A table can have only one clustered index, while non-clustered indexes can be more
than one. SQL server 2008 support upto 999 nonclustered indexes.

Syntax:

CREATE INDEX index_name


ON table_name (column_name)

To drop an existing index, use:

drop index index_name on table_name

11.4. LAB TASKS


11.4.1. Create a view on customers and packages table from ACDB database.

11.4.2. Create an index on column First Name of the customers table.

11.4.3. Alter the view created in 11.4.1 to include those customers data, whose Area is ‘LHR’

KFUEIT Department of CS/IT


86
Lab Manual # 11 Creating & Manipulating Databases

11.4.4. Drop the view created in 11.4.1 .

KFUEIT Department of CS/IT


87

You might also like