From Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design

11.5 SQL: Choosing Columns, Rows, and Order

Recall that relational algebra includes the following eight table operations: union, intersection, difference, Cartesian product, selection, projection, join, and division. All of these operations (as well as others) can be expressed using SQL s powerful select statement. This section discusses how SQL is used to perform relational projection and selection, as well as bag projection and row ordering.

First let s see how to choose columns. Consider a small UoD where people are identified by their first name. Table 11.8 provides sample data for the table scheme: Person ( firstname , sex, starsign, birthyr). The whole table may be retrieved by projecting on all its columns. In relational algebra this may be formulated as Person, or as Person [ firstname, sex, starsign, birthyr ] . In SQL, this is expressed as follows:

<b class="bold">select</b> * <b class="bold">from</b> Person
Table 11.8: A relational table storing personal details.

Here the asterisk * means all columns and may be read as everything or all . The table named after from indicates the table from which the data is to be retrieved. When this command is executed, the result is an unnamed table with the same column names and contents as the original Person table.

If only some of the columns are required, then instead of * the relevant columns should be listed (separated by commas). If the columns include a key, no duplicate rows can occur in the result, so the result corresponds...

Products & Services
Columns are vertical structural elements that use compression to transmit the weight of a structure above to other structural elements below.
Database Tools Software
Database tools software is used to create and manage databases, structured collections of records that are stored in a computer system.
Computer Power Supplies
Computer power supplies convert alternating current (AC) line to direct current (DC) line, needed by the computer.
Switching Power Supplies
Switching power supplies are DC sources that use a switching element (normally a power transistor) to generate the desired voltage. Switching power supplies are also called switch-mode products or switching mode power supplies (SMPS).
Bore and Duct Spacers
Bore and duct spacers keep pipes, conduit, and cable racking supported and evenly spaced for underground encasement.

Topics of Interest

11.6 SQL: Joins We have seen how to choose, and order, columns and rows from a single table. But suppose the information we need from a single query is spread over many tables. SQL provides two main...

11.8 SQL: Union and Simple Subqueries This section discusses two ways in which select-queries may be used as components within a larger select-query. First we examine how to specify the operations of...

11.10 SQL: Grouping Sometimes we wish to partition the rows of a table into a number of groups and display properties that apply to each group as a whole. SQL provides a group by clause that may be...

6.2 RELATIONAL DATABASE The relational database model was developed by E. F. Codd in 1970, which is an effective means to store and manage data. It is able to eliminate redundant data representation,...

3.11 Managing Data An Introduction to Lists You will often need to modify or transform the data with which you started. For instance, you might begin with a large table of data and wish only to work...