MIS 3353 Database Management Final exam with
| | | | | | |
precise detailed answers
| |
Order |of |Clauses |- |correct |answer | |SELECT
FROM
WHERE
GROUP |BY
HAVING
ORDER |BY
SELECT |clause: |Function |- |correct |answer | |•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 |- |correct |answer | |Tells |the |RDBMS |the |name |of |the |table |from |which |we |wish
|to |pull |data |for |our |output
WHERE |clause: |Function |- |correct |answer | |•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 |- |correct |answer | |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 |- |correct |answer | |•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 |- |correct |answer | |This |clause |tells |the |DBMS |the |order |in |which |to |rank |
the |results |to |be |returned |by |the |SELECT |statement
Examples |of |Analytical |Functions |- |correct |answer | |-TOP |1
- |DISTINCT
Examples |of |Aggregate |Functions |- |correct |answer | |-SUM
-COUNT
-AVG
LIKE |- |correct |answer | |•Another |option |when |evaluating |text |in |a |WHERE |clause
-Matches |text |strings |or |partial |text |strings
WHERE...LIKE |and |Wildc*rd |examples |- |correct |answer | |•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 |- |correct |answer | |Tell |the |RDBMS |to |compare |the |value |of |a |field |against |a |list |of |
values
WHERE...IN |example |- |correct |answer | |WHERE |MtnName |IN |('Mt. |Rainier', |'Mt. |Whitney', |'Mt. |Hood',
|'Mt. |Washington')
7 |Rules |for |ERDs: |Rule |#1 |- |correct |answer | |Entities |are |(singular) |nouns, |relationships |are |verbs.
7 |Rules |for |ERDs: |Rule |#2 |- |correct |answer | |Weak |entities |occur |when |removing |an |instance |in |one |
entity |results |in |removing |(an) |instance(s) |in |the |related |entity.
| | | | | | |
precise detailed answers
| |
Order |of |Clauses |- |correct |answer | |SELECT
FROM
WHERE
GROUP |BY
HAVING
ORDER |BY
SELECT |clause: |Function |- |correct |answer | |•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 |- |correct |answer | |Tells |the |RDBMS |the |name |of |the |table |from |which |we |wish
|to |pull |data |for |our |output
WHERE |clause: |Function |- |correct |answer | |•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 |- |correct |answer | |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 |- |correct |answer | |•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 |- |correct |answer | |This |clause |tells |the |DBMS |the |order |in |which |to |rank |
the |results |to |be |returned |by |the |SELECT |statement
Examples |of |Analytical |Functions |- |correct |answer | |-TOP |1
- |DISTINCT
Examples |of |Aggregate |Functions |- |correct |answer | |-SUM
-COUNT
-AVG
LIKE |- |correct |answer | |•Another |option |when |evaluating |text |in |a |WHERE |clause
-Matches |text |strings |or |partial |text |strings
WHERE...LIKE |and |Wildc*rd |examples |- |correct |answer | |•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 |- |correct |answer | |Tell |the |RDBMS |to |compare |the |value |of |a |field |against |a |list |of |
values
WHERE...IN |example |- |correct |answer | |WHERE |MtnName |IN |('Mt. |Rainier', |'Mt. |Whitney', |'Mt. |Hood',
|'Mt. |Washington')
7 |Rules |for |ERDs: |Rule |#1 |- |correct |answer | |Entities |are |(singular) |nouns, |relationships |are |verbs.
7 |Rules |for |ERDs: |Rule |#2 |- |correct |answer | |Weak |entities |occur |when |removing |an |instance |in |one |
entity |results |in |removing |(an) |instance(s) |in |the |related |entity.
