You have a database that stored information about servers and application errors. The database contains the
following tables.
Servers
Errors
You need to return all error log messages and the server where the error occurs most often.
Which Transact-SQL statement should you run?
A.
B.
C.
D.
WRONG ANSWER ! this will not return all error log messages
A is the right answer.
Statement C returns all error messages for all servers, so it does not consider to show only the server where error message happens most often.
Statement A returns one record per error message and shows the server the error message happens most often … but it will fail in case the highest number of occurences is the same for two servers, then this record will miss.
See example code below. 1st select shows result of Statement C. 2nd select shows result of Statement A when all occurences are different. 3rd select shows result of Statement A when two servers have the same highest number of occurences.
So I tend to say Statement A is correct. At least it fulfills the requirement the most.
Statement B will only return a record in case a error message occurs only once.
Statement D will throw a syntax error as the order by condition needs to be part of the selected attributes.
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Errors’)
BEGIN
DROP TABLE dbo.Errors;
END;
CREATE TABLE Errors (ErrorID INT PRIMARY KEY, ServerID INT, Occurences INT, LogMessage NVARCHAR(100));
INSERT INTO Errors VALUES
(1,1,5,’Message1′),(2,2,3,’Message1′),(3,3,15,’Message1′),
(4,1,19,’Message2′),(5,2,20,’Message2′),(6,3,15,’Message2′),
(7,1,120,’Message3′),(8,2,220,’Message3′);
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
WHERE LogMessage IN (
SELECT TOP 1 e2.LogMessage FROM Errors AS e2
WHERE e2.LogMessage = e1.LogMessage AND e2.ServerID e1.ServerID
ORDER BY e2.Occurences
);
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
WHERE Occurences > ALL (
SELECT e2.Occurences FROM Errors AS e2
WHERE e2.LogMessage = e1.LogMessage AND e2.ServerID e1.ServerID
);
INSERT INTO Errors VALUES (9,4,20,’Message2′);
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
WHERE Occurences > ALL (
SELECT e2.Occurences FROM Errors AS e2
WHERE e2.LogMessage = e1.LogMessage AND e2.ServerID e1.ServerID
);
I agree. Opt A is correct.
A is correct!
IF EXISTS (SELECT TOP(1) 42 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ and TABLE_NAME = ‘Errors’)
BEGIN
DROP TABLE dbo.Errors;
END;
CREATE TABLE Errors (ErrorID INT PRIMARY KEY, ServerID INT, Occurences INT, LogMessage NVARCHAR(100));
INSERT INTO Errors VALUES
(1,1,5,’Message1′),(2,2,3,’Message1′),(3,3,15,’Message1′),
(4,1,19,’Message2′),(5,2,20,’Message2′),(6,3,15,’Message2′),
(7,1,120,’Message3′),(8,2,220,’Message3′);
select * from Errors
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
WHERE LogMessage IN (
SELECT TOP 1 e2.LogMessage FROM Errors AS e2
WHERE e2.LogMessage = e1.LogMessage AND e2.ServerID e1.ServerID
ORDER BY e2.Occurences
);
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
WHERE Occurences > ALL (
SELECT e2.Occurences FROM Errors AS e2
WHERE e2.LogMessage = e1.LogMessage AND e2.ServerID e1.ServerID
);
INSERT INTO Errors VALUES (9,4,20,’Message2′);
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
WHERE Occurences > ALL (
SELECT e2.Occurences FROM Errors AS e2
WHERE e2.LogMessage = e1.LogMessage AND e2.ServerID e1.ServerID
);
SELECT DISTINCT ServerID, LogMessage, Occurences FROM Errors AS e1
GROUP BY ServerID, LogMessage, Occurences
having MAX(Occurences) =1