Exam AIP-210 Topic 4 Question 46 Discussion

Actual exam question for CertNexus's AIP-210 exam
Question #: 46
Topic #: 4

Below are three tables: Employees, Departments, and Directors.
Employee_Table

Department_Table

Director_Table
ID
Firstname
Lastname
Age
Salary
DeptJD
4566
Joey
Morin
62
$ 122,000
1
1230
Sam
Clarck
43
$ 95,670
2
9077
Lola
Russell
54
$ 165,700
3
1346
Lily
Cotton
46
$ 156,000
4
2088
Beckett
Good
52
$ 165,000
5
Which SQL query provides the Directors' Firstname, Lastname, the name of their departments, and the average employee's salary?

A. SELECT m.Firstname, m.Lastname, d.Name, AVG(e.Saiary) as Dept_avg_Saiary FROM Employee_Table as e LEFT JOIN Department_Table as d on e.Dept = d.Name LEFT JOIN Directorjable as m on d.ID = m.DeptJD GROUP BY m.Firstname, m.Lastname, d.Name B. SELECT m.Firstname, m.Lastname, d.Name, AVG(e.Salary) as Dept_avg_Salary FROM Employee_Table as e RIGHT JOIN Departmentjable as d on e.Dept = d.Name INNER JOIN Directorjable as m on d.ID = m.DeptJD GROUP BY d.Name C. SELECT m.Firstname, m.Lastname, d.Name, AVG(e.Salary) as Dept_avg_Salary FROM Employee_Table as e RIGHT JOIN Department_Table as d on e.Dept = d.Name INNER JOIN Directorjable as m on d.ID = m.DeptJD GROUP BY e.Salary D. SELECT m.Firstname, m.Lastname, d.Name, AVG(e.Salary) as Dept_avg_Salary FROM Employee_Table as e RIGHT JOIN Department_Table as d on e.Dept = d.Name INNER JOIN Directorjable as m on d.ID = m.DeptID GROUP BY m.Firstname, m.Lastname, d.Name

Suggested Answer: D Vote an answer

Explanation
This SQL query provides the Directors' Firstname, Lastname, the name of their departments, and the average employee's salary by joining the three tables using the appropriate join types and conditions. The RIGHT JOIN between Employee_Table and Department_Table ensures that all departments are included in the result, even if they have no employees. The INNER JOIN between Department_Table and Directorjable ensures that only departments with directors are included in the result. The GROUP BY clause groups the result by the directors' names and departments' names, and calculates the average salary for each group using the AVG function. References: SQL Joins - W3Schools, SQL GROUP BY Statement - W3Schools

by Catherine at Mar 29, 2024, 10:15 AM

Limited Time Offer

15%

Off

Get Premium AIP-210 Questions as Interactive Self Test Engine or PDF

Comments

Here are all the actual test exam dumps for IT exams. Most people prepare for the actual exams with our test dumps to pass their exams. So it's critical to choose and actual test pdf to succeed.

RECENT DISCUSSIONS

Useful Links

Contact Us

Our Working Time: ( GMT 0:00-15:00 )
From Monday to Saturday

Support: Contact now

If you have any question please leave me your email address, we will reply and send email to you in 12 hours.

Disclaimer:
Actual4test doesn't offer Real SANS and GIAC Exam Questions.
Oracle and Java are registered trademarks of Oracle and/or its affiliates
Actual4test material do not contain actual actual Oracle Exam Questions or material.
Actual4test doesn't offer Real Microsoft Exam Questions.
Microsoft®, Azure®, Windows®, Windows Vista®, and the Windows logo are registered trademarks of Microsoft Corporation
Actual4test Materials do not contain actual questions and answers from Cisco's Certification Exams. The brand Cisco is a registered trademark of CISCO, Inc
CFA Institute does not endorse, promote or warrant the accuracy or quality of these questions. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.
Actual4test does not offer exam dumps or questions from actual exams. We offer learning material and practice tests created by subject matter experts to assist and help learners prepare for those exams. All certification brands used on the website are owned by the respective brand owners. Actual4test does not own or claim any ownership on any of the brands.