Please answer 1,2,7,8,9 in detail In a SELECT query, what is the difference betw
ID: 3808238 • Letter: P
Question
Please answer 1,2,7,8,9 in detail In a SELECT query, what is the difference between a WHERE clause and a HAVING clause? 2. Explain why the following command would create an error and what changes could be made to fix the emor: SELECT V_CODE SUM(P_QOH) FROM PRODUCT; 3. What type of integrity is enforced when a primary key is declared? 4. 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. 5. What is the difference between a column constraint and a table constraint? 6. What are "referential constraint actions"? 7. Rewrite the following WHERE clause without the use of the IN special operator: WHERE V_STATE IN ('TN', 'FL', 'GA') Explain the difference between an ORDER BY clause and a GROUP BY clause. Explain why the following two commands produce different results: SELECT DISTINCT COUNT(V_ CODE) FROM PRODUCT; SELECT COUNT (DISTINCT V_CODE) FROM PRODUCT; What is the difference between the COUNT aggregate function and the SUM aggregate function? Explain why it would be preferable to use a DATE data type to store date data instead of a character data type. What is a recursive join?Explanation / Answer
1.
WHERE clause is used while fetching data rows from table, and data which doesn't pass the condition will not be fetched
HAVING clause is used to filter summarized data or grouped data.
Where clause will always execute before Having clause
For exmaple :
SELECT d.DEPT_NAME,avg(e.EMP_SALARY) as AVG_SAL FROM Employee e,
Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 2000 GROUP BY d.DEPT_NAME HAVING AVG_SALARY > 5000;
2. Error is there because an aggregate function is applied to the P_QOH attribute but V_CODE is neither in an aggregate function nor in a GROUP BY.
Error can be fixed: 1. placing V_CODE in an appropriate aggregate function
2.adding a GROUP BY clause to group by values of V_CODE
3.removing the V_CODE attribute from the select statment.
7. WHERE V_STATE='TN' OR V_STATE='FL' OR V_STATE='GA'
8.
ORDER BY changes the order in which items are returned.
GROUP BY will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns
for example:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
9.
The difference is in the order of operations.
The first query executes the Count function to count the number of values in V_CODE including duplicate values and then the Distinct keyword only allows one count of that value to be displayed .
The second query applies the Distinct keyword to the V_CODE before the count is taken so only unique values are counted.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.