PROFESSOR(faculty, department, Smith Electrical Eng Mechanical Eng hannes Computer Scl computer sci 7523 Kenneth Mechanical Eng. $40,000 FIGURE 94.7 Instance of the relation professor In the above statement, the seleCt clause specifies the attribute names that are to be retrieved, FROM gives the list of the relations involved, and Where is a Boolean predicate that completely specifies the tuples to be retrieved Consider the database of Fig 94.4, and suppose that we want the name of all suppliers that supply either beds or desks. In SELECT supplier name PRO INFO product name The result of an SQL command may contain duplicate values and is therefore not always a true relation. In fact, the result of the above query, shown below, has duplicate entries. supplier name Martin Martin The entry Martin appears twice in the result, because the supplier Martin supplies both beds and sofas Removal of duplicates is usually a computationally intensive operation. As a result, duplicate entries are not automatically removed by SQL. To ensure uniqueness, the command DISTINCT should be used In the above query, if we want the supplier names to be listed only once, the above query should be modified as follows: ELEC DISTINCT suppller name roduct name=“bed" OR product name=“sofa In SQL, a query can involve more than one relation. Suppose that we want the list of all suppliers from Metairie who supply beds. Such a query, shown below, involves both PRO_INFO and SUP_LOC. SELECT supplier name FROM WHERE PRO INFO. supplier name SUP LOC. supplier name AND When an SQL expression, such as the one above, involves more than one relation, it is sometimes necessary to qualify attribute names, that is, to precede an attribute name by the relation(a period is placed between the two)it belongs to. Such a qualification removes possible ambiguities. In SQL, it is possible to have several levels of query nesting; this is done by including a SElECt query statement within the where clause The output data can be presented in sorted order by using the SQL ORDER BY clause followed by the ttribute name(s)according to which the output is to be sorted. In database management applications it is often desirable to categorize the tuples of a relation by the values of a set of attributes and extract an aggregated characteristic of each category. Such database management tasks are referred to as aggregation functions. For instance, SQL includes the following built-in aggregation function SUM, COUNT, AVERAGE, MIN, MAX. The attribute names used for the categorization are referred to as c2000 by CRC Press LLC© 2000 by CRC Press LLC In the above statement, the SELECT clause specifies the attribute names that are to be retrieved, FROM gives the list of the relations involved, and WHERE is a Boolean predicate that completely specifies the tuples to be retrieved. Consider the database of Fig. 94.4, and suppose that we want the name of all suppliers that supply either beds or desks. In SQL, this query can be expressed as: SELECT supplier_name FROM PRO_INFO WHERE product_name = “bed” OR product_name = “sofa” The result of an SQL command may contain duplicate values and is therefore not always a true relation. In fact, the result of the above query, shown below, has duplicate entries. supplier_name Martin Martin Rudd The entry Martin appears twice in the result, because the supplier Martin supplies both beds and sofas. Removal of duplicates is usually a computationally intensive operation. As a result, duplicate entries are not automatically removed by SQL. To ensure uniqueness, the command DISTINCT should be used. In the above query, if we want the supplier names to be listed only once, the above query should be modified as follows: SELECT DISTINCT supplier_name FROM PRO_INFO WHERE product_name = “bed” OR product_name = “sofa” In SQL, a query can involve more than one relation. Suppose that we want the list of all suppliers from Metairie who supply beds. Such a query, shown below, involves both PRO_INFO and SUP_LOC. SELECT supplier_name FROM PRO_INFO, SUP_LOC WHERE PRO_INFO.supplier_name = SUP_LOC.supplier_name AND product_name = “bed” When an SQL expression, such as the one above, involves more than one relation, it is sometimes necessary to qualify attribute names, that is, to precede an attribute name by the relation (a period is placed between the two) it belongs to. Such a qualification removes possible ambiguities. In SQL, it is possible to have several levels of query nesting; this is done by including a SELECT query statement within the WHERE clause. The output data can be presented in sorted order by using the SQL ORDER BY clause followed by the attribute name(s) according to which the output is to be sorted. In database management applications it is often desirable to categorize the tuples of a relation by the values of a set of attributes and extract an aggregated characteristic of each category. Such database management tasks are referred to as aggregation functions. For instance, SQL includes the following built-in aggregation functions: SUM, COUNT, AVERAGE, MIN, MAX. The attribute names used for the categorization are referred to as FIGURE 94.7 Instance of the relation PROFESSOR