Database Queries to One Table (Part 1)

• Querying Data (SELECT)
• General Structure of SELECT
• Elimination of Duplicates in the Result (DISTINCT)
• Query Data with Condition (WHERE)
• Logical Operators in the Selection Condition
• Comparison Operators in the Selection Condition
• Selected Operators for Strings and Dates

This is an excerpt from the Mondial database that is widely used for practice exercises in this article.

Querying Data (SELECT)

Querying data from a table in a database is a read-only operation, i.e., the data cannot be changed. The SQL command for a database query is SELECT. You can use SELECT to display the contents of one or more tables.

General Structure of SELECT

A SELECT command consists of the partially optional elements shown in Table 13. The example shown in the picture contains a simple SQL command (or SQL statement) to query all countries and their capitals from the Country example database.

A valid SELECT statement to query data from a table consists of at least the elements SELECT and FROM. All other elements are optional. The end of a SELECT statement is marked by a semicolon (;). The result of SELECT is a virtual table that contains all of the records in the found set. Which columns are contained in the result table is defined in the SELECT statement. For example, you can use the result table for nested SELECT statements such as a database table, but it is not stored in the database.

The statement SELECT expects a list of columns to be displayed in the result. If you want to output all columns, use the character *. The following statement outputs the entire contents of the Country table:

SELECT * FROM Country;

Elimination of Duplicates in the Result (DISTINCT)

It is often the case that data records occur several times in the result set of a database query. A typical example is the multiple occurrences of places or postal codes in a customer database. If only the number of locations from which the customers originate is relevant and not the number of customers from each location, duplicate occurrences will interfere with distilling this information. Identical data records in the result of an SQL query can be combined with DISTINCT. “Identical” here means that all attributes of a data set are identical. The following example displays all continents represented in the list of countries.

SELECT DISTINCT Continent FROM Country;

Without DISTINCT the Continent attribute would be output for each data record in the table. With DISTINCT, multiple records containing the same continent are reduced to one data record.

Query Data with Condition (WHERE)

With a simple SELECT statement, the result set basically includes all data records of the requested table. Often, however, the result should be limited to a certain set of values. For example, if only those customers in the customer base who live in the city of Hamburg are of interest, the result of the database query should not contain any customers from Berlin. To restrict the result set, the keyword WHERE must be added to the SELECT statement. The following example queries all countries in the Country table, but restricts the result to countries on the American continent:

SELECT Name FROM Country WHERE Continent=’America’;

As a selection condition of WHERE, the value of the Continent attribute is checked for equality with the character string America. Only records for which the selection condition is fulfilled are included in the result. If a restriction with WHERE is used in the SELECT statement, WHERE always follows the element FROM.

In the above example, a simple comparison for equal value was used as a selection condition. With SQL, however, several conditions can be linked together to create complex queries.

Logical Operators in the Selection Condition

One class of possible operators that can be used to formulate selection conditions are the logical operators NOT, AND, and OR. Without parentheses, these are evaluated in the order specified. Table 14 illustrates the use of logical operators in selection conditions.

Logical operators can also be used in combination. However, the correct use of parenthesis must be ensured here. The following examples illustrate this connection:

SELECT Name FROM Country WHERE Continent=’Europe’ AND NOT Percentage=100;

Result: Returns all countries in Europe whose base area is not 100 percent in Europe.

SELECT Name FROM Country WHERE NOT(Continent=’Europe’ AND Percentage=100);

Result: All countries that are not 100% located in Europe.

SELECT Name FROM Country WHERE NOT Continent=’Europe’ AND Percentage=100;

Result: All countries that are not located in Europe but with a base area on only one continent.

Comparison Operators in the Selection Condition

In addition to checking for equality with the operator “=,” other comparison operators can also be used in SQL to formulate selection conditions. Typical examples of such conditions would be a request for all customers whose last order was placed more than three months ago or a request for all customers whose highest invoice amount is greater than 1,000 euros. Similarities can also be found with comparison operators, such as a request for all customers whose surname begins with “S.”

SELECT * FROM Country WHERE Continent=’Africa’ AND Name LIKE ’M%’;

Result: Output of all countries that are located in Africa and whose name begins with the letter M.

SELECT Name FROM Country WHERE Continent=’Asia’ AND Name BETWEEN ’C’ AND ’I’;

Result: Output of all countries located in Asia whose name has an initial letter between C and I. China is included in the result quantity, but Indonesia and India are not.

Selected Operators for Strings and Dates

The following operators for strings and dates are supported by most DBMSs. In addition, DBMSs usually support a large number of other functions, some of which are very DBMS-specific. Therefore, the developers of SQL queries have to inform themselves about which functions are actually available and how they are implemented based on the documentation of the DBMS used.

Date functions

Date functions are functions that output the current date or time and are used either to filter records or to write date and time information. Table 16 presents CURRENT_DATE and CURRENT_TIMESTAMP as examples of two date functions.

In addition, relational DBMSs usually provide conversion functions for date and time values. The concrete details must be read in the documentation of the DBMS used in each case.

String functions

SQL provides a number of functions for editing character strings. Table 17 shows examples of functions that link character strings.

A concatenation of character strings is used, for example, to generate output texts (such as addresses) or to export data in structured text (such as XML) for technical interfaces.