Relational Database: Additional Operationson Relations; SQLGreg PlaxtonTheory in Programming Practice, Spring 2005Department of Computer ScienceUniversity of Texas at AustinOverview• The course packet describes several simple aggregation operators– The count operator– Operators for numerical data: sum, average, minimum, maximum• The course packet also describes the notion of grouping in the contextof aggregation• We will give a brief introduction to SQL in this lecture– We will present examples of some key constructs– As part of this introduction, we will see how aggregation and groupingare handled in SQLTheory in Programming Practice, Plaxton, Spring 2005Table Manipulation• SQL includes a number of basic commands creating, removing, andmaintaining tables– CREATE TABLE: Create a table with the specified attributes– DROP TABLE: Remove a table– TRUNCATE TABLE: Remove the contents of a table, but not thetable itself– INSERT INTO: Add specified rows to a table– UPDATE: Alter values within a table– DELETE FROM: Delete rows satisfying a specified conditionTheory in Programming Practice, Plaxton, Spring 2005SELECT• The SELECT command is arguably the most important command inSQL– It encompasses the selection, projection, and join operations that wediscussed earlier in the context of relational algebra• We will present a series of examples demonstrating some of the possibleforms of a SELECT command– Our examples will assume the existence of a “parts” relation withattributes “partnum” (integer), “desc” (string), “price” (float), and“quantity” (integer).– We will follow the convention of capitalizing all the letters in SQLkeywordsTheory in Programming Practice, Plaxton, Spring 2005Projection via SELECT• The following command performs a projection but retains duplicates:SELECT price, quantity FROM parts• The following variation yields the projection operator we discussedearlier in the context of relational algebra:SELECT DISTINCT price, quantity FROM parts• The asterisk symbol may be used as a shorthand to request allattributes:SELECT * FROM partsTheory in Programming Practice, Plaxton, Spring 2005WHERE Clause• A WHERE clause can be used to perform selection within a SELECTcommand– SELECT partnum, quantity FROM parts WHERE price > 10– SELECT partnum, 2 * quantity FROM parts WHERE price = 10– SELECT * FROM parts WHERE price > 10AND price * quantity <= 1000– SELECT * FROM parts WHERE price BETWEEN 5 AND 10– SELECT * FROM parts WHERE desc IN (’nut’, ’bolt’)– SELECT * FROM parts WHERE desc LIKE ’%nut%’Theory in Programming Practice, Plaxton, Spring 2005Specifying an Output Ordering• An ORDER BY clause can be used to specify the output ordering– SELECT * FROM parts ORDER BY partnum– SELECT * FROM parts ORDER BY partnum DESC, price– SELECT partnum, price * quantity FROM parts ORDER BY 2Theory in Programming Practice, Plaxton, Spring 2005Aggregation• The operators MIN, MAX, AVG, and SUM can be used to aggregatenumerical values– SELECT MIN(price) FROM parts– SELECT * FROM parts WHERE price > 0.25 * MAX(price)• The operator COUNT can be used on any type of data– SELECT COUNT(desc) FROM parts– SELECT COUNT(DISTINCT desc) FROM parts WHERE price > 10Theory in Programming Practice, Plaxton, Spring 2005Grouped Aggregation• Sometimes we would like to partition the tuples of a relation table intogroups based on the values of certain attributes, and then aggregateover the groups– SELECT desc, SUM(quantity) FROM parts GROUP BY descTheory in Programming Practice, Plaxton, Spring 2005HAVING Clause• A HAVING clause can be used to further filter the output of a groupedaggregation– SELECT desc, SUM(quantity) FROM parts GROUP BY descHAVING SUM(quantity)>20Theory in Programming Practice, Plaxton, Spring 2005Column Aliasing• We can rename an attribute of the output relation by aliasing• SELECT partnum "num", price * quantity "total" FROM partsTheory in Programming Practice, Plaxton, Spring 2005Operations Involving Multiple Relations• Up to this point we have discussed SELECT commands involving asingle relation (parts)• Suppose now that we have an orders database with attributes“customer” (string), “partnum” (integer), “quantity” (integer)Theory in Programming Practice, Plaxton, Spring 2005Cross Product• The cross product of two relations may be obtained using a SELECTcommand– SELECT * FROM parts, orders– SELECT customer, price, parts.quantity FROM parts, orders• The above examples are somewhat unnatural• More typically, we perform some form of a join on such a pair ofrelations• Remark: Complex queries involving multiple tables are sometimes easierto read if each attribute name is prefixed with the associated table name(e.g., “orders.customer” rather than “customer”)Theory in Programming Practice, Plaxton, Spring 2005Inner Join (a.k.a. Equijoin)• Quite commonly, a WHERE clause containing an equality constraint isused to join two relations– SELECT customer, price FROM parts, ordersWHERE parts.partnum = orders.partnum• Here is an alternative way to express the same query– SELECT customer, price FROM parts JOIN ordersON parts.partnum = orders.partnumTheory in Programming Practice, Plaxton, Spring 2005Table Aliasing• It is often convenient to alias the name of a table within an SQLcommand involving multiple tables– SELECT b.customer, a.price FROM parts a, orders bWHERE a.partnum = b.partnumTheory in Programming Practice, Plaxton, Spring 2005Natural Join• Natural join is also defined within SQL, along with other variants• Note that natural join can also be accomplished using an inner join• In the case of our parts/orders relations, natural join is somewhat“unnatural”; why?Theory in Programming Practice, Plaxton, Spring 2005Nested SELECT Commands• The tables passed as arguments to a SELECT command can beproduced by nested SELECT commands• Arbitrary nesting is allowed• Thus, a single SQL query can be quite complexTheory in Programming Practice, Plaxton, Spring