Data

CS 308 Intro to Database Systems Prof Frankl Query Writing Tips When you write a query, it may be helpful to think about: Which attributes are involved? Which table(s) are they in? If multiple tablesare involved, what is the join condition? Does the query involve aggregation? If so, how are the groups formed? Which filtering conditions apply to individual tuples and which apply to the aggregateddata? Can the condition describing which tuples are included in the query result be checked by examining individual rows of some tables (or joined tables)? If so, you can probably write a reasonablysimple query, without subqueries. If not, more work may be needed in order to formulate a query comprising operations on sets relevant sets of tuples, expressed using subqueries. Testing Just like anyother program, your query might have bugs. These might be syntax errors or semantic errors. It’s usually a good idea to write the query in a text file to make it easier to keep track of changesyou’re making as you debug. (Then copy/paste into the MySQL GUI. Unfortunately, MySQL doesn’t give very useful error messages for syntax mistakes. Just like with any other program, you’re not finished whenyou get the syntax correct. You need to check that the query does the right thing. It’s probably best to use some small relations (i.e. tables with small numbers of rows) for which you can compute theexpected result by hand. Compare query results to expected results. Try testing the query on other relations. Think about what happens in situations where there are nulls, duplicates, and other“interesting” situations. Common mistakes Missing or incorrect join conditions Missing group_by in aggregations Incorrect use (or non-use) of set operations Making simple things unnecessarily complicated, forexample by o Including unneeded tables o Using subqueries when a simple query would work o Stating join condition twice (using JOIN and in WHERE clause) Using natural join when there’s an...