Projection in SQL

Projection in SQL

We can, if we wish, remove some of the components of the selected tuples; that is, we can project the relation created by an SQL query onto some of its attributes. In place of the * of the SELECT clause, we may list some of the attributes of the relation mentioned in the FROM clause. The result will be projected onto the attributes listed.

Example 1 : Assume we wish to customize the query of "The Database Language SQL" Example 1 to create only the movie title and length. We may write

SELECT title, length
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

The result is a table with two columns, headed title and length. The tuples in this table are pairs, each consisting of a movie title and its length, such that the movie was produced by Disney in 1990. For example, the relation schema and one of its tuples looks like:



Often, we wish to produce a relation with column headers different from the attributes of the relation mentioned in the FROM clause. We may follow the name of the attribute by the keyword AS and an alias, which becomes the header in the result relation.  Keyword AS is optional. That is, an alias can instantly follow what it stands for, without any intervening punctuation.

Example 2 : We can customize Example 1 to produce a relation with attributes name and duration in place of title and length as follows;

SELECT title AS name, length AS duration
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

The result is the same set of tuples as in Example 1, but with the columns headed by attributes name and duration. For instance, the result relation might begin:



Another option in the SELECT clause is to use an expression in place of an attribute. Put another way, the SELECT list can function like the lists in an extended projection, which we considered in "Extending the Projection Operator". We shall see in "Full-Relation Operations" that the SELECT list can also contain aggregates as in the  operator of "Grouping".

Example 3 : Assume we wanted output as in Example 2, but with the length in hours. We might replace the SELECT clause of that example with

SELECT title AS name, length*0.016667 AS lengthInHours

Then the same movies would be produced, but lengths would be calculated in hours and the second column would be headed by attribute lengthInHours, as:



Example 4 : We can even permit a constant as an expression in the SELECT clause. It might seem pointless to do so, but one application is to put some useful words into the output that SQL displays. The following query:

SELECT title, length*0.016667 AS length, 'hrs.' AS inHours
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

produces tuples such as



We have arranged that the third column is called inHours, which fits with the column header length in the second column. Every tuple in the answer will have the constant hrs. in the third column, which gives the false impression of being the units attached to the value in the second column.


Tags