Week 18 CST 363: Intro to Database Systems

This week, in chapter 6 we worked with aggregate functions to do averages, sums, mins, maxes, and counts on selected rows. All of these ignore null values except for COUNT(*). The DISTINCT keyword can be used to omit duplicate values. GROUP BY is used to group a row based on an expression in ascending order and can accept multiple expressions with a comma. The HAVING clause is used to specify a search condition. The general order to use these in is SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. With WHERE the search condition is applied before it calculates; in HAVING, it is after. WHERE refers to any column and can't contain aggregate functions. HAVING can only refer to a column in the SELECT clause and can contain aggregate functions. WITH ROLLUP is used to add a summary row to the end specified by the GROUP BY clause. One cannot use the DISTINCT keyword or ORDER BY WITH ROLLUP.


In chapter 7, we learned about subqueries. Subqueries are a SELECT statement in another SQL statement. Subqueries can be introduced with WHERE, HAVING, FROM and SELECT. They cannot have an ORDER BY clause but can be nested in other subqueries. Subqueries are similar to joins and can replace them. JOINS are useful when columns from different tables are used referencing an existing relationship. Subqueries are good for passing an aggregate value to the main query and with informal relationships in long, complex subqueries. The IN operator is used to give a list that is tested against the test expression and returns one column of values. NOT IN can replace an outer join. The WHERE clause is used to compare an expression with the results of a subquery. IF the keywords ANY, SOME and ALL are used, they will return a single value or a list of values. The ANY/SOME keyword is used to test if a condition is true for one or more of the values, similar to OR. A correlated subquery is executed for each row in the main query and an uncorrelated subquery is executed one time. The EXISTS operator is used to test one or more rows returned by the subquery, NOT EXISTS tests that no rows are returned. You can use an * in these to specify any column. The SELECT clause subquery needs to return one value in a correlated subquery. Using a join is a more legible option. The FROM clause returns a set that is called inline view. An alias must be used to perform calculations and can be used as column names. A complex subquery can be made but it is difficult and confusing.