SQL: Groupby and Having

FULL NAME

E-Mail ID

Student ID

From the table Which SQL JOIN type would you use to retrieve a list of students along with the courses they're enrolled in?

• LEFT JOIN

• INNER JOIN

• RIGHT JOIN

• FULL OUTER JOIN

What will be the result of the query:
SELECT Students.student_name, Courses.course_name FROM Students INNER JOIN Courses ON Students.student_id = Courses.students_enrolled;

• List of student names and their respective course names

• An error will occur

• No results will be returned

• List of Student name with thier respective Course ID

If a student from the 'Students' table needs to be displayed even if they are not enrolled in any course, which JOIN should be used?

• LEFT JOIN

• RIGHT JOIN

• INNER JOIN

• UNION

What happens if a RIGHT JOIN is performed between the 'Students' table and the 'Courses' table?

• Only students who are enrolled in courses will be displayed

• All students along with their enrolled courses will be displayed

• Only courses with enrolled students will be displayed

• All students will be displayed, and courses without students will have NULL values

Which SQL query retrieves the total sales amount for each product?

• SELECT ProductID, Sum(SaleAmount) AS TotalSales FROM Sales order BY ProductID;

• SELECT ProductID, SUM(SaleAmount) AS TotalSales FROM Sales GROUP BY ProductID;

• SELECT * ,Sum(SaleAmount) AS TotalSales FROM Sales GROUP BY TrascationID;

• SELECT ProductID, count(SaleAmount) AS TotalSales FROM Sales GROUP BY ProductID;

Which query finds the products with an average sale amount above $200 and a total count of sales transactions greater than 3?

• SELECT ProductID, AVG(SaleAmount) AS AvgSale FROM Sales GROUP BY ProductID HAVING AVG(SaleAmount) > 200 or COUNT(*) > 3;

• SELECT ProductID, AVG(SaleAmount) AS AvgSale FROM Sales GROUP BY ProductID WHERE AVG(SaleAmount) > 200 AND COUNT(*) > 3;

• SELECT ProductID, AVG(SaleAmount) AS AvgSale FROM Sales GROUP BY ProductID HAVING AVG(SaleAmount) > 200 HAVING COUNT(*) > 3;

• SELECT ProductID, AVG(SaleAmount) AS AvgSale FROM Sales GROUP BY ProductID HAVING AVG(SaleAmount) > 200 AND COUNT(*) > 3;

What does the following query do?

SELECT PaymentMode, COUNT(*) AS TotalTransactions FROM Sales GROUP BY PaymentMode ORDER BY TotalTransactions DESC;

• Counts the total number of transactions for each payment mode in each month.

• Retrieves the total sales amount for each payment mode and each month.

• Groups sales transactions by payment mode only

• Calculates the average sale amount for each payment mode in each month.