Explain the problem with the following SQL statement. select name, avg(credits)

Question

Explain the problem with the following SQL statement. select name, avg(credits) from student group by course having sum (credits) > 10 What is the difference of result and performance between count(1) and count(*) in a SQL query? For example, select count(1) from emp; select count (*) from emp; Provide explanation why the two following SQL statements produce different results. select distinct count (name) from student; select count (distinct name) from student; Is the following SQL correct? If no. please explain. If yes, what could be the return value? select distinct count (A. credits) from student A, student B group by A. course, B. course having sum(A. credits)=avg(B. credits)

Explanation / Answer

Ans.a. Displays the name and average credits of a a course which has more than 10 credits.

Ans.b. count(1) and count(*) display the same results and both perform equally well because it would make no sense for a DBMS to allow such a bug to remain unfixed. Hence you will find that the performance of both forms is identical in all major SQL DBMSs.

Ans.c.

then it counts for different values having name column which has certain values

now if i execute first query will get ans = 3, bcoz it counts only distinct values.

2)now,

if u execute this then u will get answer = 8

bcoz, it has 3 times amit, 3 times raj , and 2 times anand.

this count for all the records and give output, and does not give count for distinct value.

Ans.d. Yes, it is correct and the result will be:
     
   count of credits from student A (table) whose count of credits is same as average of credits of B and are grouped by credits of both the tables.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.