Identify common relational database objects

Question 1

What is a view?

Answer 1

A view is a virtual table based on the results of a SELECT query. It can be thought of as a window on specified rows in one or more underlying tables.

Example:
CREATE VIEW Deliveries
AS
SELECT o.OrderNo, o.OrderDate,
c.FirstName, c.LastName, c.Address, c.City
FROM ORDER AS o JOIN Customer AS c
ON o.Customer = c.ID

To view and filter data
SELECT OrderNo, OrderDate, LastName, Address FROM Deliveries
WHERE City = “Seattle”;

Question 2

Whats is a stored procedure?

Answer 2

A stored procedure defines SQL statements that can be run on command. They are used to encapsulate programmatic logic in a database for actions that applications need to perform when working with data.

Example:
CREATE PROCEDURE RenameProduct
@ProductID INT.
@NewName VARCHAR(20)
AS
UPDATE Product SET NAME = @NewName
WHERE ID = @ProductID

To rename a product
EXEC RenameProduct 201, “Spanner”;

Question 3

What is an index?

Answer 3

An index helps search for data in a table. When creating an index in a database, specify a column from the table. When users run a query that specifies this column in the WHERE clause, the database system can use this index to fetch data quickly.

Example:
CREATE INDEX idx_ProductName
ON Product(Name);