MIS 3353 DATABASE MANAGEMENT FINAL
QUESTIONS AND ANSWERS
Order of Clauses - Answers - SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT clause: Function - Answers - •Indicates which fields should be displayed in the
output for those records that have passed any tests given in the WHERE clause
-All fields in this clause must be separated by a comma
FROM clause: Function - Answers - Tells the RDBMS the name of the table from which
we wish to pull data for our output
WHERE clause: Function - Answers - •Reduces the set of records that will be displayed
in the output.
-If all records in a table should be shown in the output, then this clause is not needed
GROUP BY clause: Function - Answers - This clause is required if there is an aggregate
function in the SELECT clause and any fields appear in the SELECT clause that are not
part of the aggregate function
HAVING clause: Function - Answers - •This clause is very similar to the WHERE clause,
except that it applies to grouped data formed as the result of an aggregate function
rather than individual records
-Unless there is a GROUP BY clause in the query, this clause shouldn't be used
ORDER BY clause: Function - Answers - This clause tells the DBMS the order in which
to rank the results to be returned by the SELECT statement
Examples of Analytical Functions - Answers - -TOP 1
- DISTINCT
Examples of Aggregate Functions - Answers - -SUM
-COUNT
-AVG
LIKE - Answers - •Another option when evaluating text in a WHERE clause
-Matches text strings or partial text strings
WHERE...LIKE and Wildc*rd examples - Answers - •WHERE FirstName LIKE 'Ge*
, -Returns any record where the FirstName value starts with the letters "Ge" (this would
include a value that was just "Ge" with nothing thereafter.
•WHERE FirstName LIKE '*nk'
-returns any record where the FirstName value ends with the letters "nk" (including any
record with a value of just "nk").
IN and NOT IN - Answers - Tell the RDBMS to compare the value of a field against a list
of values
WHERE...IN example - Answers - WHERE MtnName IN ('Mt. Rainier', 'Mt. Whitney',
'Mt. Hood', 'Mt. Washington')
7 Rules for ERDs: Rule #1 - Answers - Entities are (singular) nouns, relationships are
verbs.
7 Rules for ERDs: Rule #2 - Answers - Weak entities occur when removing an instance
in one entity results in removing (an) instance(s) in the related entity.
7 Rules for ERDs: Rule #3 - Answers - Primary keys are:
(1) unique
(2) not null
(3) include as few attributes as possible (not composite)
(4) doesn't change.
7 Rules for ERDs: Rule #4 - Answers - -Many-to-many relationship --> Associative
Entity; One-and-only-one to one-and-only one --> Something's Messed Up
7 Rules for ERDs: Rule #5 - Answers - The mandatory side donates its key to the many
side
7 Rules for ERDs: Rule #6 - Answers - Sub-types can exist when instances share
some, but not all attributes OR a set of instances have a unique relationship.
7 Rules for ERDs: Rule #7 - Answers - The same (non-key) data point should only exist
once and in one entity
Business Cycles: Threats - Answers - Anything that might keep an organization from
meeting its objectives
Examples of Threats - Answers - -Inventory theft
-Manufacturing defective products
-Over-ordering supplies
-Not generating sufficient customer demand
-Theft of company secrets
-Shipping incorrect quantities of products
-Failure to collect payment from customers
QUESTIONS AND ANSWERS
Order of Clauses - Answers - SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT clause: Function - Answers - •Indicates which fields should be displayed in the
output for those records that have passed any tests given in the WHERE clause
-All fields in this clause must be separated by a comma
FROM clause: Function - Answers - Tells the RDBMS the name of the table from which
we wish to pull data for our output
WHERE clause: Function - Answers - •Reduces the set of records that will be displayed
in the output.
-If all records in a table should be shown in the output, then this clause is not needed
GROUP BY clause: Function - Answers - This clause is required if there is an aggregate
function in the SELECT clause and any fields appear in the SELECT clause that are not
part of the aggregate function
HAVING clause: Function - Answers - •This clause is very similar to the WHERE clause,
except that it applies to grouped data formed as the result of an aggregate function
rather than individual records
-Unless there is a GROUP BY clause in the query, this clause shouldn't be used
ORDER BY clause: Function - Answers - This clause tells the DBMS the order in which
to rank the results to be returned by the SELECT statement
Examples of Analytical Functions - Answers - -TOP 1
- DISTINCT
Examples of Aggregate Functions - Answers - -SUM
-COUNT
-AVG
LIKE - Answers - •Another option when evaluating text in a WHERE clause
-Matches text strings or partial text strings
WHERE...LIKE and Wildc*rd examples - Answers - •WHERE FirstName LIKE 'Ge*
, -Returns any record where the FirstName value starts with the letters "Ge" (this would
include a value that was just "Ge" with nothing thereafter.
•WHERE FirstName LIKE '*nk'
-returns any record where the FirstName value ends with the letters "nk" (including any
record with a value of just "nk").
IN and NOT IN - Answers - Tell the RDBMS to compare the value of a field against a list
of values
WHERE...IN example - Answers - WHERE MtnName IN ('Mt. Rainier', 'Mt. Whitney',
'Mt. Hood', 'Mt. Washington')
7 Rules for ERDs: Rule #1 - Answers - Entities are (singular) nouns, relationships are
verbs.
7 Rules for ERDs: Rule #2 - Answers - Weak entities occur when removing an instance
in one entity results in removing (an) instance(s) in the related entity.
7 Rules for ERDs: Rule #3 - Answers - Primary keys are:
(1) unique
(2) not null
(3) include as few attributes as possible (not composite)
(4) doesn't change.
7 Rules for ERDs: Rule #4 - Answers - -Many-to-many relationship --> Associative
Entity; One-and-only-one to one-and-only one --> Something's Messed Up
7 Rules for ERDs: Rule #5 - Answers - The mandatory side donates its key to the many
side
7 Rules for ERDs: Rule #6 - Answers - Sub-types can exist when instances share
some, but not all attributes OR a set of instances have a unique relationship.
7 Rules for ERDs: Rule #7 - Answers - The same (non-key) data point should only exist
once and in one entity
Business Cycles: Threats - Answers - Anything that might keep an organization from
meeting its objectives
Examples of Threats - Answers - -Inventory theft
-Manufacturing defective products
-Over-ordering supplies
-Not generating sufficient customer demand
-Theft of company secrets
-Shipping incorrect quantities of products
-Failure to collect payment from customers
