- CUBE and ROLLUP select product_id, item_id, sum(qty) "SUM" from cubit group by product_id, item_id product_id item_id SUM --------------------------- 1 A 0.01 1 B 0.10 1 C 1.00 2 A 10.00 2 B 100.00 2 C 1000.00 3 A 10000.00 3 B 100000.00 select product_id, item_id, sum(qty) "SUM" from cubit group by product_id, item_id with cube product_id item_id SUM --------------------------- 1 A 0.01 1 B 0.10 1 C 1.00 1 1.11 2 A 10.00 2 B 100.00 2 C 1000.00 2 1110.00 3 A 10000.00 3 B 100000.00 3 110000.00 111111.11 A 10010.01 B 100100.10 C 1001.00 select product_id, item_id, sum(qty) "SUM" from cubit group by product_id, item_id with rollup product_id item_id SUM --------------------------- 1 A 0.01 1 B 0.10 1 C 1.00 1 1.11 2 A 10.00 2 B 100.00 2 C 1000.00 2 1110.00 3 A 10000.00 3 B 100000.00 3 110000.00 111111.11 The key point here is that the ROLLUP operator prioritizes columns based on the group by order, and the CUBE doesn't.
- Joins select title, pub_name from titles, publishers where titles.pub_id = publishers.pub_id Here is the ANSI-standard select title, pub_name from titles JOIN publishers ON titles.pub_id = publishers.pub_id
- What happens When You Fail to Specify a Common Key? The server returns all possible combinations of rows between the tables. select title, pub_name from titles, publishers -->>> Cartesian product It is equivalent to ANSI query select title, pub_name from titles CROSS JOIN publishers -->> Too costly.
- Recipe for Writing Queries Containing Joins . Build the select list, naming each column or expression. . Name the tables where the columns is the select list reside, as well as any tables required to connect those tables. . Provide the Join conditions to connect the tables. select pub_name, avg(price) from titles t, publishers p where t.pub_id = p.pub_id group by pub_name
- Dealing with more than two tables select au_lname, au_fname, title from authors a, titles t, titleauthor ta where ta.title_id = t.title_id and a.au_id = ta.au_id * When joining two tables, you must specify one join condition in order to avoid a Cartesian product. When joining three tables, you must specify two join conditions. As a general rule, when joining n tables, you must specify n-1 join conditions.
- The Meaning of * in a Multitable Query select * from titles t, publishers p where t.pub_id = p.pub_id * all columns from both tables select t.*, pub_name from titles t, publishers p where t.pub_id = p.pub_id * columns from a single table.
- Subqueries select title from titles where pub_id = (select pub_id from publishers where pub_name = "Algodata Infosystems") * the subquery must return a single row * The subquery must always appear in parentheses.
- Subqueries with IN select pub_name from publishers where pub_id in (select pub_id from titles where type = "business") * The server returns a list of valid publisher IDs to the main query.
- Subqueries Versus Joins select pub_name from publishers p, titles t where p.pub_id = t.pub_id and type = "business" * It gives similar result but it may contain duplicates.
- Subqueries with EXISTS There are two ways to avoid the duplicate problem. . Use DISTNICT (Extra work sorting) . Use Exists select pub_name from publishers p where exists (select * from titles t where p.pub_id = t.pub_id and type = "business") * Always use Exists rather than joining tables and using distinct. The performance benefits are dramatic.
- NOT EXISTS and NOT IN Certain results can be defined only with a subquery; for example, non-membership and non-existence can be expressed only in that way. select pub_name from publishers p where not exists (select * from titles t where t.pub_id = p.pub_id and type = "business") select pub_name from publishers p where pub_id not in (select pub_id from titles t where type = "business") * In most cases, NOT IN and NOT EXISTS are identical in their behavior. However, using NOT EXISTS enables you to compare tables where the join condition consists of multiple columns. You can't use NOT IN to compare tables like that.
- Subqueries with Aggregates in where clauses select type, price from titles where price < (select avg(price) from titles)
- Union select city, state from authors union select city, state from publishers * Each result set must have the same number of columns as the first set, and each column must be of the same datatype. select city, state from authors union select city, state from publishers order by state, city
- Using select with INTO * You can direct the select statement to create a table on-the-fly. select distinct type into type_lookup from titles
- Creating an empty table It is often useful to create an empty copy of an existing table. select * into new_salesdetail from salesdetail where 1 = 2
- Adding Rows with INSERT
- Inserting Specific Values insert authors (au_id, au_lname, au_fname, phone, contract) values ("123-45-6789","Jones", "Mary", "415 555-1212", 1)
- Inserting Several Rows with SELECT insert authors_archive (au_id, au_lname, au_fname, phone, city, state, zip) select au_id, au_lname, au_fname, phone, city, state, zip from authors where state = "CA"
- Omitting the Column List * The column list in an INSERT statement is optional, but when it is left out, you must provide values for each column in the table in the order in which the columns were defined. insert publishers values ("1235", "New World Books and Prints", "GA", "Atlanta") * Here is an important coding standard: All insert statements should name the columns to be inserted.
- Inserting from a Stored Procedure insert newtitles exec p1 "business"
- Modifying Rows with update * The update statement consists of three main components: . The table to be updated . The columns to be updated, with the new values . The rows to be updated, in the form of a WHERE clause update publishers set pub_name = "Joe's Press" where pub_id = "1234" update titles set price = price * 2 update publishers set name = "Joe's Books and Prints" * It changes every row in the table!!!!! * You can update only one table at a time. update contacts set address1 = "5 Main" address2 = "3D" city = "Valdosta" where contract_id = 12345
- Removing Rows with DELETE delete titles where type ="business" delete titles
- Clearing a Table with truncate table truncate table titles * It is fatser than delete titles, but hard to recover.
Last update January 13, 1998.