Database Management II Test 2
A desk is in___________normal shape when it's far in ____________and there are no transitive
dependencies. - ANS-third, second ordinary shape.
Define and discusss the concept of transitive dependency - ANS-while there are useful
dependencies such that X→Y, Y→Z, and X is the primary key. X→Z is the transitive
dependency because X determines the price of Z thru Y.
Explain the difference between a ordinary subquery and a correlated subquery - ANS-A normal,
or uncorrelated subquery, executes before the outer query. It executes most effective once and
the result is held for use by way of the outer query. A correlated subquery is predicated in
element at the outer query, generally thru a WHERE criteria inside the subquery that references
an attribute within the outer question. Therefore, a correlated subquery will execute once for
each row evaluated by the outer query; and the correlated subquery can probably produce a
specific result for every row in the outer query
Explain the distinction between an ORDER BY clause and a GROUP BY clause - ANS-ORDER
BY: A SQL clause this is useful for ordering the output of a SELECT question (for instance, in
ascending or descending order).
GROUP BY: A SQL clause used to create frequency distributions whilst blended with any of the
mixture functions in a SELECT assertion
Explain why it is probably extra suitable to claim an attribute that contains simplest digits as a
man or woman facts kind as opposed to a numeric records type. - ANS-Sometimes various
series is unnecessary as quite a number. As an instance, you'll by no means perform a
mathematical function on a smartphone number or social safety variety, but you may need to
apply them as string types - for example to append or cast off a place code or some thing.
Explain why it'd be finest to use a DATE statistics type to keep date statistics as opposed to a
individual information kind. - ANS-The DATE records kind uses numeric values primarily based
on the Julian calendar to shop dates. This makes date arithmetic along with including and
subtracting days or fractions of days feasible
Explain why the subsequent command would create an blunders and what changes might be
made to restoration the error:
SELECT V_CODE, SUM(P_QOH) FROM PRODUCT; - ANS-Missing GROUP BY
SELECT V_CODE, SUM(P_QOH)
, FROM PRODUCT
GROUP BY V_CODE;
Explain why the following two commands to supply special effects:
SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT;
SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT; - ANS-The difference is inside the
order of operations.
The first command executes the Count function to rely the quantity of values in V_CODE (say
the matter returns "14," for example) along with replica values, and then the Distinct key-word
simplest permits one count number of that price to be displayed (only one row with the value
"14" seems because the end result).
The 2d command applies the Distinct keyword to the V_CODEs earlier than the count number is
taken so handiest particular values are counted.
Five SQL DDL records manipulation instructions - ANS-Alter, create, drop, rename, provide,
truncate
Five SQL DML data manipulation instructions - ANS-select, update, insert, merge, delete
Given the worker information in Question 47, what is the query output for the EXCEPT
question? (List the query output.) - ANS-This query can yield one-of-a-kind answers. If you
operate
SELECT * FROM EMPLOYEE
EXCEPT
SELECT * FROM EMPLOYEE_1
the solution is
Alice Cordoza
Ann McDonald
If you use
SELECT * FROM EMPLOYEE_1
EXCEPT
SELECT * FROM EMPLOYEE
the solution is
Mary Chen
Given the worker statistics in Question 47, what's the query output for the INTERSECT query?
(List the question output.) - ANS-The query output might be:
John Cretchakov
A desk is in___________normal shape when it's far in ____________and there are no transitive
dependencies. - ANS-third, second ordinary shape.
Define and discusss the concept of transitive dependency - ANS-while there are useful
dependencies such that X→Y, Y→Z, and X is the primary key. X→Z is the transitive
dependency because X determines the price of Z thru Y.
Explain the difference between a ordinary subquery and a correlated subquery - ANS-A normal,
or uncorrelated subquery, executes before the outer query. It executes most effective once and
the result is held for use by way of the outer query. A correlated subquery is predicated in
element at the outer query, generally thru a WHERE criteria inside the subquery that references
an attribute within the outer question. Therefore, a correlated subquery will execute once for
each row evaluated by the outer query; and the correlated subquery can probably produce a
specific result for every row in the outer query
Explain the distinction between an ORDER BY clause and a GROUP BY clause - ANS-ORDER
BY: A SQL clause this is useful for ordering the output of a SELECT question (for instance, in
ascending or descending order).
GROUP BY: A SQL clause used to create frequency distributions whilst blended with any of the
mixture functions in a SELECT assertion
Explain why it is probably extra suitable to claim an attribute that contains simplest digits as a
man or woman facts kind as opposed to a numeric records type. - ANS-Sometimes various
series is unnecessary as quite a number. As an instance, you'll by no means perform a
mathematical function on a smartphone number or social safety variety, but you may need to
apply them as string types - for example to append or cast off a place code or some thing.
Explain why it'd be finest to use a DATE statistics type to keep date statistics as opposed to a
individual information kind. - ANS-The DATE records kind uses numeric values primarily based
on the Julian calendar to shop dates. This makes date arithmetic along with including and
subtracting days or fractions of days feasible
Explain why the subsequent command would create an blunders and what changes might be
made to restoration the error:
SELECT V_CODE, SUM(P_QOH) FROM PRODUCT; - ANS-Missing GROUP BY
SELECT V_CODE, SUM(P_QOH)
, FROM PRODUCT
GROUP BY V_CODE;
Explain why the following two commands to supply special effects:
SELECT DISTINCT COUNT (V_CODE) FROM PRODUCT;
SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT; - ANS-The difference is inside the
order of operations.
The first command executes the Count function to rely the quantity of values in V_CODE (say
the matter returns "14," for example) along with replica values, and then the Distinct key-word
simplest permits one count number of that price to be displayed (only one row with the value
"14" seems because the end result).
The 2d command applies the Distinct keyword to the V_CODEs earlier than the count number is
taken so handiest particular values are counted.
Five SQL DDL records manipulation instructions - ANS-Alter, create, drop, rename, provide,
truncate
Five SQL DML data manipulation instructions - ANS-select, update, insert, merge, delete
Given the worker information in Question 47, what is the query output for the EXCEPT
question? (List the query output.) - ANS-This query can yield one-of-a-kind answers. If you
operate
SELECT * FROM EMPLOYEE
EXCEPT
SELECT * FROM EMPLOYEE_1
the solution is
Alice Cordoza
Ann McDonald
If you use
SELECT * FROM EMPLOYEE_1
EXCEPT
SELECT * FROM EMPLOYEE
the solution is
Mary Chen
Given the worker statistics in Question 47, what's the query output for the INTERSECT query?
(List the question output.) - ANS-The query output might be:
John Cretchakov
