SQL Quiz Review

What are "referential constraint actions?"

(1) Deleting primary records (2) Updating of primary records (3) Inserting of dependent records

What is the difference between the COUNT aggregate function and the SUM aggregate function?

COUNT aggregate function: Returns the number of items in a group. SUM aggregate function: Returns the sum of all the values

What is the difference between a column constraint and a table constraint?

Column level constraints: They are inserted after the declaration of the data type. They are for one column only Table level constraints: They are inserted at the end of the table. They are for multiple columns

What type of integrity is enforced when a primary key is declared?

Entity integrity

Explain why the following command would create an error and what changes could be made to fix the error: SELECT V_CODE, SUM(P_QOH) FROM PRODUCT;

Missing GROUP BY SELECT V_CODE, SUM(P_QOH) FROM PRODUCT GROUP BY V_CODE;

Explain the difference between an ORDER BY clause and a GROUP BY clause

ORDER BY: A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order). GROUP BY: A SQL clause used to create frequency distributions when combined with any of the aggregate functions in a SELECT statement

Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.

Sometimes a number sequence is pointless as a number. for instance, you would never perform a mathematical function on a phone number or social security number, but you might want to use them as string types - for instance to append or remove an area code or something.

Explain why it would be preferable to use a DATE data type to store date data instead of a character data type.

The DATE data type uses numeric values based on the Julian calendar to store dates. This makes date arithmetic such as adding and subtracting days or fractions of days possible

In a SELECT Query, what is the difference between a WHERE clause and a HAVING clause?

The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

Explain why the following two commands to produce different results: SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT;

The difference is in the order of operations. The first command executes the Count function to count the number of values in V_CODE (say the count returns "14," for example) including duplicate values, and then the Distinct keyword only allows one count of that value to be displayed (only one row with the value "14" appears as the result). The second command applies the Distinct keyword to the V_CODEs before the count is taken so only unique values are counted.

Rewrite the following WHERE clause without the use of the IN special operator: WHERE V_STATE IN ('TN', 'FL', 'GA')

WHERE V_STATE = 'TN' OR V_STATE = 'FL' OR V_STATE = 'GA'

What is a recursive join?

when a table must be joined to itself