Chapter 4

1.   Why do some people pronounce SQL as “sequel”?
Ans: Because of its naming history, SQL is developed from SEQUEL language, so some people pronounce SQL as “sequel”.

2.   Why are the manipulation statements of SQL more widely used than the definition and control statements?
Ans: Because only the database administrator uses the definition and control statements, while power users and analysts, who are the majority, use the manipulation statements.

3.   How many levels do the SQL-92, SQL:1999, and SQL:2003 standards have?
Ans: The SQL-92 specification contained three levels: Entry SQL, Intermediate SQL, and Full SQL. The SQL:1999 and SQL:2003 standards contains a single level of conformance called Core SQL. Features outside of Core SQL are grouped into packages. SQL:2003 has 14 packages.

4.   Why is conformance testing important for the SQL standard?
Ans: Conformance tests provide assurance that software can be ported among conforming DBMSs. Without the assurance of conformance testing, careful attention must be paid to identify non portable SQL coding practices.

5.   In general, what is the state of conformance among major DBMS vendors for the SQL:2003 standard?
Ans: The weakness of the SQL standards is the lack of conformance testing. Since 1996, DBMS vendor claims have substituted for independent conformance testing. Even for Core SQL, the major vendors lack support for some features and provide proprietary support for other features. With the packages, conformance has much greater variance.

6.   What is standalone SQL?
Ans: In standalone SQL, the user submits SQL statements with the use of a specialized editor.  The editor alerts the user to syntax errors and sends the statements to the DBMS.

7.   What is embedded SQL?
Ans: In the embedded context, an executing program submits SQL statements, and the DBMS sends results back to the program.  The program includes SQL statements along with statements of the host programming language such as COBOL or Visual Basic.  There are additional statements that allow other SQL statements (such as SELECT) to be used inside a computer program.

8.   What is an expression in the context of database languages?
Ans: An expression is a combination of constants, column names, functions, and operators that produces a value. In conditions and result columns, expressions can be used in any place that column names can appear.

9.   From examples and discussion in Chapter 4, what parts of the SELECT statement are not supported by all DBMSs?
Ans: The different parts are pattern-matching characters, case sensitivity in string matching, date constants, the inequality symbol, join operator style, and difference operations.

10. Recite the rule about the GROUP BY and HAVING clauses.
Ans: The GROUP BY clause must contain every column in the Select clause except for aggregate expressions. The HAVING clause must be preceded by the GROUP BY clause.

11. Recite the rule about columns in SELECT when a GROUP BY clause is used.
Ans: The columns in the SELECT clause must either be in the GROUP BY clause or be part of a summary calculation with an aggregate function.

12. How does a row condition differ from a group condition?
Ans: A condition in a row (WHERE clause) cannot involve aggregate functions whereas a condition in a group (HAVING clause) involves aggregate functions.

13. Why should row conditions be placed in the WHERE clause rather than the HAVING clause?
Ans: Often, execution speed will improve if row conditions are tested in the WHERE clause rather than the HAVING clause. In the conceptual evaluation process, testing of conditions in the WHERE clause precedes testing of group conditions in the HAVING clause. The HAVING clause should be used for conditions that can be tested only on groups.

14. Why are most DBMS not case sensitive when matching on string conditions?
Ans: Because case non-sensitive promotes user-friendliness.

15. Explain how working with sample tables can provide insight about difficult problems.
Ans: Working with sample tables helps users understand the conceptual evaluation process of SQL statements and visualize the result of a query, so that users can imitate the statement in samples table and apply it to the real problem.

16. When working with date columns, why is it necessary to refer to documentation of your DBMS?
Ans: Because the date constants are different among DBMS. For example, in Access SQL, pound symbols enclose date constants; while in Oracle SQL, single quotation marks enclose date constants.

17. How do exact and inexact matching differ in SQL?
Ans: Exact matching is used to find an identical string, but inexact matching supports conditions that match just some part of the string. Along with wildcard characters, inexact matching could be used to find values having a common prefix (or suffix) or match strings containing a substring.  Whereas inexact matching uses operators such as LIKE, exact matching uses the equality comparison operator (=).

18. How do you know when the output of a query relates to groups of rows as opposed to individual rows?
Ans: In a problem statement, you should look for computations involving aggregate functions.  For example, the problem “list the name and average grade of students” contains an aggregate computation.  Problems referencing an aggregate function indicate that the output relates to groups of rows. In a SELECT statement, the GROUP BY and HAVING keywords indicate that the output relates to groups of rows rather than individual rows.
             
19. What tables belong in the FROM statement?
Ans: The FROM clause should contain all tables that were mentioned in the problem statement. You should match the columns listed in the problem statement with columns from various tables.  Include columns that are needed for output as well as for conditions.  You also should include tables that are needed only to connect other tables.

20. Explain the cross product style for join operations.
Ans: The cross product style lists tables in the FROM clause and join conditions in the WHERE clause.

21. Explain the join operator style for join operations.
Ans: The join operator style lists join operations in the FROM clause using the INNER JOIN and ON keywords.

22. Discuss the pros and cons of the cross product versus the join operator styles. Do you need to know both the cross product and the join operator styles?
Ans: The cross product style is easy to read but does not support outer join operations. The join operator style can be difficult to read but supports outer join operations. You need to know both to ensure that you can understand statements written by others.

23. What is a self-join? When is a self-join useful?
Ans: A self-join is a join between a table and itself (two copies of the same table). Self-join is useful for finding relationships among rows of the same table.

24. Provide a SELECT statement example in which a table is needed even though the table does not provide conditions to test or columns to show in the result.
Ans: SELECT  Student.*
        FROM Student, Faculty
WHERE  StdSSN = FacSSN
       (This SELECT statement lists students who are the faculty.)         

25. What is the requirement when using the traditional set operators in a SELECT statement?
Ans: Tables must be union compatible (table must have the same number of columns and each corresponding column must have the same data type).

26. When combining joins and grouping, what conceptually occurs first, joins or grouping?
Ans: Joins always occur before grouping.

27. How many times can grouping occur in a SELECT statement?
Ans: Grouping can occur only one time.

28. Why is the SELECT statement more widely used than the modification statements INSERT, UPDATE, and DELETE?
Ans: Because end users can use data entry forms, which are easier than using the modification statements.

29. Provide an example of an INSERT statement that can insert multiple rows.
Ans: INSERT INTO ISFaculty
            SELECT * FROM Faculty WHERE FacDept = 'IS'
(This INSERT statement adds rows into the ISFaculty table.)

30. What is the relationship between the DELETE statement and the rules about deleting referenced rows?
Ans: By the referential integrity constraint, the DELETE statement is subject to the actions on referenced rows. A row cannot be deleted if related rows exist and the deletion action is restrict.

31. What is the relationship between the UPDATE statement and the rules about updating the primary key of referenced rows?
Ans: The UPDATE statement is subject to the actions on updating the primary key of referenced rows. Update rules on referenced rows may not allow the operation when changing the primary key of a parent table.

32. How does COUNT(*) differ from COUNT(ColumnName)?
Ans: COUNT(*) computes the number of rows. COUNT(ColumnName) computes the number of non null column values. If a column does not have null values, COUNT(*) and COUNT(ColumnName) are identical.

33. How does COUNT(DISTINCT ColumnName) differ from COUNT(ColumnName)?
Ans: COUNT(DISTINCT ColumnName) computes the number of unique column values. COUNT(ColumnName) computes the number of non null column values. If a column has unique values such as a primary key or candidate key, COUNT(DISTINCT ColumnName) and COUNT(ColumnName) are identical.

34. W hen mixing AND and OR in a logical expression, why is it a good idea to use parentheses?
Ans: When mixing AND and OR in a logical expression, it is a good idea to use parentheses.  Otherwise, the reader of the SELECT statement may not understand how the AND and OR conditions are grouped.  Without parentheses, you must depend on the default way that AND and OR conditions are grouped. Even if you know the way that the SQL compiler will group the conditions, someone reading your statement may not understand the order.

35. What are the most important lessons about the conceptual evaluation process?
Ans: The lessons are more important to remember than the specific details about the conceptual process. The first lesson is that GROUP BY conceptually occurs after WHERE.  If you have an error in a SELECT statement involving WHERE or GROUP BY, the problem is most likely in the WHERE clause. The second lesson is that grouping occurs only one time in the evaluation process.  If your problem involves more than one independent aggregate calculation, you may need more than one SELECT statement. The third lesson is that using sample tables can help you analyze difficult problems. It is often not necessary to go through the entire evaluation process.  Rather, use sample tables to understand only the difficult part.

36. What are the mental steps involved in query formulation?
Ans: Query formulation involves a conversion from a problem statement into a statement of a database language such as SQL. In between the problem statement and the database language statement, you convert the problem statement into a database representation. Typically, the difficult part is to convert the problem statement into a database representation. This conversion involves a detailed knowledge of the tables and relationships and careful attention to possible ambiguities in the problem statement.

37. What kind of join queries often have duplicates in the result?
Ans: When you join a parent table to a child table and show only columns from the parent table in the result, duplicate rows can appear in the result. To eliminate duplicate rows, you can use the DISTINCT keyword.

38. What mental steps in the query formulation process are addressed by the conceptual evaluation process and critical questions?
Ans: The critical questions provide help with converting a problem statement into a database representation. The conceptual evaluation process provides help with converting a database representation into a database language statement.