Latest Posts

Modifying Views

In limited conditions it is possible to carry out an insertion, deletion, or update to a view. In the beginning, this idea makes no sense at all, since the view does not exist the way a base table (stored relation) does. What could it mean, say, to insert a new tuple into a view? Where

View Definitions

Relations that are defined with a CREATE TABLE statement in fact exist in the database. That is an SQL system stores tables in some physical organization. They are persistent, in the sense that they can be expected to exist indefinitely and not to change unless they are clearly told to

Introduction to Selection of Indexes

Selection of indexes requires a trade-off by the database designer, and in fact, this choice is one of the principal factors that influence whether a database design is acceptable. Two important factors to examine are:

Default Values / Indexes

When we create or change tuples, we sometimes do not have values for all components. For instance, we mentioned in "Simple Table Declarations" Example 2 that when we add a column to a relation schema, the existing tuples do not have a known value, and it was

Simple Table Declarations

The simplest form of declaration of a relation schema comprises the keywords CREATE TABLE followed by the name of the relation and a parenthesized list of the attribute names and their types.

Defining a Relation Schema in SQL

Now we shall begin a discussion of data definition, the portions of SQL that involve explaining the structure of information in the database. On the contrary, the aspects of SQL discussed previously - queries and modifications - are often called data manipulation.

Deletion / Updates

A deletion statement consists of: 1. The keywords DELETE FROM, 2. The name of a relation, say R, 3. The keyword WHERE, and 4. A condition. That is, the form of a deletion is DELETE FROM R WHERE ; The effect of executing this statement is that every tuple satisfying the condition (4) will be deleted from relation R.

Database Modifications

To this point, we have focused on the normal SQL query form: the select-from-where statement. There are a number of other statement forms that do not return a result, but rather change the state of the database. In this section, we shall focus on three types of statements that allow us to

Grouping / HAVING Clauses

To group tuples, we use a GROUP BY clause, following the WHERE clause. The keywords GROUP BY are followed by a list of grouping attributes. In the simplest situation, there is only one relation reference in the FROM clause, and this relation has its tuples grouped according

Full-Relation Operations

In this section we shall look at some operations that act on relations as a whole, rather than on tuples individually or in small numbers (as do joins of various relations, for example). First, we deal with the fact that SQL uses relations that are bags rather than sets, and a tuple can