Question 43 1. Rewrite the following query so that it uses a CTE instead. Select
ID: 3885458 • Letter: Q
Question
Question 43
1. Rewrite the following query so that it uses a CTE instead.
Select Country
From Customers
Where Country =
(Select Top 1 country
From Customers C Join Orders O on C.CustomerId = O.CustomerID
Group By country
Order By count(*)
)
With OrdersCTE As
(Select country, count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
)
Select Top 1 OrdersCTE.Country From OrdersCTE
Order By TotalCount
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
Order By Count(*)
)
Select Top 1 OrdersCTE.Country From OrdersCTE
Order By TotalCount
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
)
Select Max(OrdersCTE.Country) From OrdersCTE
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
Having Min(Count(*) = 6
)
Select OrdersCTE.Country From OrdersCTE
Order By TotalCount
Question 44
1. The following query is used for several of the questions that follow.
How many subqueries are in the following query?
--Query01
Select Top 10
OrderId
, Convert(char(10), OrderDate,121) Last_Paris_Order
, (Select Convert(char(10),max(OrderDate),121) from Orders) Last_OrderDate
, DateDiff(dd,OrderDate, (Select Max(OrderDate)from Orders)) Day_Diff
From Orders
Where ShipCity = 'Paris'
Order By OrderDate desc
0
3
Can't be determined
2
Question 45
1. Use Query01 from above.
If you only wanted to see the Paris order with the least number of days between the original order and the last order made, how would you change the above query?
Change the max(OrderDate) to min(OrderDate)
Change the Select Top 10 to Select Top 1
Change the Order By OrderDate desc to Order By OrderDate asc
Change the datadiff to shortestdiff
Question 46
1. Use Query01 from above.
What does the 121 mean in the Convert statement used in the previous query?
Convert(char(10), OrderDate,121)
yyyy-mm-dd hh:mi:ss.mmm (24h)
dd mon yyyy hh:mi:ss:mmm
mon dd yyyy hh:mi:ss:mmmAM (or PM)
mon dd yyyy hh:miAM (or PM)
Question 47
1. Use Query01 from above.
What is the latest date of an order?
Select Convert(char(10),min(OrderDate),121) from Orders
Select Convert(char(10),new(OrderDate),121) from Orders
Select Convert(char(10),latest(OrderDate),121) from Orders
Select Convert(char(10),max(OrderDate),121) from Orders
10 points
Question 48
1. Use Query01 from above.
What is the longest number of days that between the actual order date and the very last order date where the ShipCity equal Paris?
14
215
176
850
need help! Thanks
With OrdersCTE As
(Select country, count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
)
Select Top 1 OrdersCTE.Country From OrdersCTE
Order By TotalCount
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
Order By Count(*)
)
Select Top 1 OrdersCTE.Country From OrdersCTE
Order By TotalCount
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
)
Select Max(OrdersCTE.Country) From OrdersCTE
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
Having Min(Count(*) = 6
)
Select OrdersCTE.Country From OrdersCTE
Order By TotalCount
Question 44
1. The following query is used for several of the questions that follow.
How many subqueries are in the following query?
--Query01
Select Top 10
OrderId
, Convert(char(10), OrderDate,121) Last_Paris_Order
, (Select Convert(char(10),max(OrderDate),121) from Orders) Last_OrderDate
, DateDiff(dd,OrderDate, (Select Max(OrderDate)from Orders)) Day_Diff
From Orders
Where ShipCity = 'Paris'
Order By OrderDate desc
0
3
Can't be determined
2
Question 45
1. Use Query01 from above.
If you only wanted to see the Paris order with the least number of days between the original order and the last order made, how would you change the above query?
Change the max(OrderDate) to min(OrderDate)
Change the Select Top 10 to Select Top 1
Change the Order By OrderDate desc to Order By OrderDate asc
Change the datadiff to shortestdiff
Question 46
1. Use Query01 from above.
What does the 121 mean in the Convert statement used in the previous query?
Convert(char(10), OrderDate,121)
yyyy-mm-dd hh:mi:ss.mmm (24h)
dd mon yyyy hh:mi:ss:mmm
mon dd yyyy hh:mi:ss:mmmAM (or PM)
mon dd yyyy hh:miAM (or PM)
Question 47
1. Use Query01 from above.
What is the latest date of an order?
Select Convert(char(10),min(OrderDate),121) from Orders
Select Convert(char(10),new(OrderDate),121) from Orders
Select Convert(char(10),latest(OrderDate),121) from Orders
Select Convert(char(10),max(OrderDate),121) from Orders
10 points
Question 48
1. Use Query01 from above.
What is the longest number of days that between the actual order date and the very last order date where the ShipCity equal Paris?
14
215
176
850
need help! Thanks
Explanation / Answer
Ans:
43) B
With OrdersCTE As
(Select country, Count(*) As TotalCount
From Customers C Join Orders O On C.CustomerId = O.CustomerID
Group By country
Order By Count(*)
)
Select Top 1 OrdersCTE.Country From OrdersCTE
Order By TotalCount
44) D
we have 2 Sub queries
45) C
Change the Order By OrderDate desc to Order By OrderDate asc
46) A
yyyy-mm-dd hh:mi:ss.mmm
47) D
Select Convert(char(10),max(OrderDate),121) from Orders
Change the Order By OrderDate desc to Order By OrderDate asc
46) A
yyyy-mm-dd hh:mi:ss.mmm
47) D
Select Convert(char(10),max(OrderDate),121) from Orders
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.