Experiment No.
9
Aim: Implement the concepts of views and Indexes
Theory :
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 [Link] can add SQL statements and functions to a view and present the data
as if the data were coming from one single table.
Creating view
A view is created with the CREATE VIEW statement.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
We can query the view above as follows:
SELECT * FROM [Brazil Customers];
Updating a View
A view can be updated with the CREATE OR REPLACE VIEW statement.
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 = 'Brazil';
S.E. Computer Engg. (Sem IV) Database Management System Lab CSL402 Page 1
Dropping a View
A view is deleted with the DROP VIEW statement.
Syntax
DROP VIEW view_name;
The following SQL drops the “Brazil Customers” view:
Example:
DROP VIEW [Brazil Customers];
Index
The CREATE INDEX statement is used to create indexes in [Link] are used to retrieve
data from the database more quickly than otherwise. The users cannot see the indexes, they are
just used to speed up searches/queries. Updating a table with indexes takes more time than
updating a table without (because the indexes also need an update). So, only create indexes on
columns that will be frequently searched against.
Creating Index
Syntax:
CREATE INDEX index_name ON table_name;
Example:
CREATE INDEX idx_lastname
ON Persons (LastName);
Dropping Index
The DROP INDEX statement is used to delete an index in a table.
ALTER TABLE table_name
DROP INDEX index_name;
LAB PRACTICE ASSIGNMENT
S.E. Computer Engg. (Sem IV) Database Management System Lab CSL402 Page 2
[Link] the following queries.
1. Create view which contains emp_no, enema, salary from emp;
2. Change the name of employee whose id is 101 to vijay in above view;
3. Delete the record of employee whose emp_no is 105;
4. Delete above view;
5. Modify location of dept_no of dept_view;
[Link] output of following queries.
1. Create simple index dept_simple-index on dept table.
2. Create composite index dept_composite_index on dept table.
3. Drop index dept_simple_index and dept_composite_index.
4. Create index raj on emp (empno, ename).
[Link] below schema execute the queries:
1. Salesman{salesman_id,name,city,commission}
2. create a view to locate the salespeople in the city 'New York'.
3. create a view that counts the number of customers in each grade.
4. reate a view to count the number of unique customers, compute the average and the
total purchase amount of customer orders by each date.
5. create a view for those salespeople who belong to the city of New York.
Conclusion:Thus,we have studied to implement views and Indexes.
S.E. Computer Engg. (Sem IV) Database Management System Lab CSL402 Page 3
