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
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.
Bore and Duct Spacers
Bore and duct spacers keep pipes, conduit, and cable racking supported and evenly spaced for underground encasement.
Data Security Software
Data security software restricts access to stored data and processes on a computer or server. These applications are designed to prevent data loss, corruption, or disclosure of sensitive information.

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...

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...

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,...

Product Announcements
High Performance Alloys, Inc.
Lee Co. (The)
Ellsworth Adhesives