もしも、これを読んでくれる人が英語嫌いだったら、ごめんさなさい。これはあくまでもビールねこの勉強ノートです。ビールねこは日本語も英語も不得手です。。。およよ。
#77
https://www.briefmenow.org/microsoft/solution-you-run-the-following-transact-sql-statement-13/
Solution: You run the following Transact-SQL statement:
customers by the area code of their phone number. The area code is defined as the first three characters of the
phone number.
The main page of the application will be based on an indexed view that contains the area and phone number
for all customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement:
SELECT @areaCode=LEFT(@phoneNumber,3)としたら『Schemabinding』なしでできるそうな。
＜答え＞NO *note:TVF can not be used in Indexed View, this is the reason
#74
これも『TOP 1』でリターンを一つにしてないから『NO』
#70
Produces a warning if the credit limit parameter is greater than 7,000
Propagates all unexpected errors to the calling process
＜答え＞
#68
これわかる人いますか？
Task level is defined using the following rules:
You need to determine the task level for each task in the hierarchy.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate
Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the
correct order.
Select and Place:
＜答え＞どうやらこれが答えらしいですが、ちょっと信じられません。。。
#64
You have the following Transact-SQL query:SELECT
City.CityID,
City.CityName,
TranslateName(Nearby.CityName) AS NearbyCity
FROM Cities AS City
CROSS APPLY NearbyCities(City.CityID) AS Nearby
What type of functions are used in the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
＜答え＞Scalar, Table-Vlaued
なんてこった。。。よく読めばわかる問題でした。
SELECT
City.CityID,
City.CityName,
TranslateName(Nearby.CityName) AS NearbyCity ⇐シングルバリューなので、スカラー値
FROM Cities AS City
CROSS APPLY NearbyCities(City.CityID) AS Nearby ⇐『APPLY』をつかっているのでもちろん『Talbe-ValuedFunction』、テーブルで返す値なので、Talbe-Valuedですな。してやられました。
#62
SIMULATION
You work for an organization that monitors seismic activity around volcanos. You have a table named
GroundSensors. The table stored data collected from seismic sensors. It includes the columns describes in the
following table:
The database also contains a scalar value function named NearestMountain that returns the name of the mountain that is nearest to the sensor.You need to create a query that shows the average of the normalized readings from the sensors for each mountain.
The query must meet the following requirements:
Include the average normalized readings and nearest mountain name.
Exclude sensors for which no normalized reading exists.
Exclude those sensors with value of zero for tremor.
Construct the query using the following guidelines:
Use one part names to reference tables, columns and functions.
Do not use parentheses unless required.
Do not use aliases for column names and table names.
Do not surround object names with square brackets.
もうね、ここまでくればちゃんと読まなあかんなぁ。。。めっちゃ『nearest mountain name』探してて、「なぃ～っ」ってね。
＜答え＞
SELECT Average(NormalizedReading), NearestMountain(SensorID)
FROM GroundSensors
GROUP BY NearestMountain(SensorID)
WHERE TREMOR IS NOT 0 AND NormalizedReading IS NOT NULL
＜ねこの間違った答え＞
Select ①AVG(NormalizedReading) ②as AverageOfNomalizedReading, NearestMountain(SensorID) ②as NearestMountain
from GroundSensors
where ③Tremor <> 0 and NormallizedReading IS NOT NULL
④Group By NormalizedReading, SensorId
handling the complaints if a person is assigned. The ComplaintID must be displayed first, followed by the
person name.
Construct the query using the following guidelines:Use two-part column names.
Use one-part table names.
FROM Persons
JOIN Contacts
ON Persons.PersonID=Contacts.PersonID
JOIN Complaints
ON Contacts.ComplaintID=Complaints.ComplaintID
FROM Complaints
LEFT OUTER JOIN Contacts ON Complaints.ComplaintID = Contacts.ComplaintID
LEFT OUTER JOIN Persons ON Contacts.PersonID = Persons.PersonID