Note: This question is part of a series of questions that present the same scenario. Each question in
the series contains a unique solution that might meet the stated goals. Some question sets might have
more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these
questions will not appear in the review screen.
You have a database that includes the tables shown in the exhibit (Click the Exhibit button.)
You need to create a Transact-SQL query that returns the following information:
the customer number
the customer contact name
the date the order was placed, with a name of DateofOrder
a column named Salesperson, formatted with the employee first name, a space, and the employee last
name
orders for customers where the employee identifier equals 4
The output must be sorted by order date, with the newest orders first.
The solution must return only the most recent order for each customer.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
A.
Yes
B.
No
Explanation:
We should use a WHERE clause, not a HAVING clause. The HAVING clause would refer to aggregate data.
answer is YES it does meet the goal !
13
13
Answer is Yes. Having is used with a group by, which is ok. Also being used with an aggregate MAX.
5
11
No is correct!
Running this script will result in an syntax error:
“Column ‘o.empid’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.”
If it would be HAVING MAX(o.empid) = 4 than it would run successfully but this is not given.
Please see also example code below:
13
7
o.empid is in group by clouse.
Answer is Yes.
21
3
Answer should A Yes. o.empid is in group by clouse.
11
4
o.empid is not in the select statement. this will fail.
2
10
the logical query processing order is
1- FROM
2- WHERE
3- GROUP BY
4- HAVING
5- SELECT
6- ORDER BY
so it’s legal to have o.empid in the HAVING clause while it’s not in the select clause.
the answer is YES
3
0
I have latest dumps mslatestdumps100@gmail.com for 70-761, 70-762, 70-764, 70-767
0
2
THE CORRECT ANSWER IS YES.
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql?view=sql-server-2017
HAVING,Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.
SELECT c.custid,contactname,max(orderdate)as dateOrder ,
e.firstname+”+e.lastname as salesPerson
FROM SALES.CUSTOMERS as c
INNER JOIN SALES.ORDERS AS o
ON C.custid=O.custid
INNER JOIN SALES.EMPLOYEES AS e
ON O.empid=E.empid
group by c.custid,contactname,e.firstname,e.lastname ,O.EMPID
HAVING o.empid=4
ORDER BY dateOrder DESC
3
1
SELECT c.custid,contactname,max(orderdate)as dateOrder ,
e.firstname+”+e.lastname as salesPerson
FROM SALES.CUSTOMERS as c
INNER JOIN SALES.ORDERS AS o
ON C.custid=O.custid
INNER JOIN SALES.EMPLOYEES AS e
ON O.empid=E.empid
group by c.custid,contactname,e.firstname,e.lastname
where o.empid=4
ORDER BY DateofOrder DESC
0
3
Sorry the previous one is incorrect, Where –> a filtering of the grouping clause no longer refers, and order –> alias is correct
SELECT c.custid,contactname,max(orderdate)as dateOrder ,
e.firstname+”+e.lastname as salesPerson
FROM SALES.CUSTOMERS as c
INNER JOIN SALES.ORDERS AS o
ON C.custid=O.custid
INNER JOIN SALES.EMPLOYEES AS e
ON O.empid=E.empid
where o.empid=4
group by c.custid,contactname,e.firstname,e.lastname
ORDER BY DateofOrder DESC
1
0