Database Reference
In-Depth Information
Example: Delete Many Rows from Student_result table where Marksobt less than equal to
300
Sol:
DELETE FROM Student_result
WHERE MarksObt <= 300; Example: Delete All Rows of Student_result table. Sol:
DELETE FROM Student_result Example: Delete table Student_result
Sol:
DROP TABLE Student_result;
7.3.4 Maintaining Database object
Creating and Using Views
A view is a saved query that can function in many of the ways a table does. In
other words views are virtual tables. Views can be used to control security. We can even
create a view that comprises more than one table. views must have unique name. CREATE
VIEW statement is used to form a view. To remove a view, the DROP statement is used.
Here are some of the more common reasons for creating a view:
Security: Users are permitted to use the view, but not the base tables.
Simplicity: a view can be created by combining tables that have complex relationships so
users writing queries do not need to understand the relationships.
Complex Joins: Sometimes queries cannot be done without great difficulty unless you cre-
ate a view in something like a temporary table first.
The general syntax is pretty simple.
CREATE VIEW
view_name
A S
s e l e c t _ s t a t e m e n t
Example: Form a view
CustomerList
of all customers who have ordered any product. Sol:
CREATE VIEW CustomerList AS
SELECT Customer_name, Customer_address, Product_ID
FROM Customer, Order, OrderItem
WHERE Customer. Customer_id = Orders. Customer_id
AND OrderItem. Order_no = Order. Order_no;
Understanding Indexes
