April 19, 2016 at 6:05 am
Ed Wagner (4/19/2016)
Brandie Tarvin (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?No.
If people are going to cheat, let them figure out the consequences of their cheating the hard way. Also, the smart people who are close to the answer but are missing that one thing will figure out that the answer is wrong and will figure out why.
I ran into this when I was doing my first certification. A friend and I co-purchased what we thought was a real test trainer that turned out to be a braindump (didn't know that until later). But then I realized too many of the answers were wrong based on the question being asked. So I dumped the dump (so to speak) and found a Microsoft allowed test trainer. I was feeling fairly confident when I realized I could recognize a bad answer without having to research it.
That poster didn't even try to cover it up or deny it. At least he isn't telling lies while cheating. Maybe if he spent more time following Gail's advice (like say, creating a table) and less time posting, he might make some progress.
While I know I haven't been a very active poster, I've gotten into the habit of simply skipping any topic that has the faintest whiff of homework about it...
I suppose it would be one thing if there was some sign of the poster actually *trying* to answer the questions, but when they throw in the "I need a query to do X, given data Y, Z, O and it has to use a CROSS JOIN, can you give me the answer" sort of thing? Next topic, please!
April 19, 2016 at 6:35 am
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?
That would be too easy for them. Just keep saying their guesses are wrong without even checking them. Maybe they'll eventually test them to prove they're right.
April 19, 2016 at 8:18 am
Luis Cazares (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?That would be too easy for them. Just keep saying their guesses are wrong without even checking them. Maybe they'll eventually test them to prove they're right.
Oh, I like that idea.
Assuming they bother to have guesses as to the answer, which many of them don't.
April 19, 2016 at 10:16 am
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?
Do it!
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2016 at 10:31 am
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */
DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
April 19, 2016 at 11:07 am
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
Now where's that apostrophe gone? Eirikur, you need to fix this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 19, 2016 at 11:19 am
Phil Parkin (4/19/2016)
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
Now where's that apostrophe gone? Eirikur, you need to fix this.
Huh. Maybe I did it wrong, but when I tried to add it:
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0013) which is not allowed in XML...
I did a convert to VARBINARY(17) on the phrase with the apostrophe.
April 19, 2016 at 12:06 pm
Brandie Tarvin (4/19/2016)
Phil Parkin (4/19/2016)
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
Now where's that apostrophe gone? Eirikur, you need to fix this.
Huh. Maybe I did it wrong, but when I tried to add it:
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0013) which is not allowed in XML...
I did a convert to VARBINARY(17) on the phrase with the apostrophe.
I saw that he had posted a question and I spent some time looking at it before I ran it. I have to admit that he got me with it - it was a beautiful thing. You know that "DOH!" moment that hits you in the head? I got hit that day. 😀
April 19, 2016 at 3:18 pm
Brandie Tarvin (4/19/2016)
Phil Parkin (4/19/2016)
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
Now where's that apostrophe gone? Eirikur, you need to fix this.
Huh. Maybe I did it wrong, but when I tried to add it:
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0013) which is not allowed in XML...
I did a convert to VARBINARY(17) on the phrase with the apostrophe.
It's not quite that straightforward. You have to change the order of the characters in the string as well as the characters themselves to match the obfuscating ASCII shifts and repositioning he does.
Just use this to get the apostrophe:
DECLARE @VERSION_STRING VARBINARY(16)=0x4D552345545560584F564C5E2D355359;
Or in general, with some data type changes to handle longer strings and code to generate the VARBINARY:
DECLARE @desired_string varchar(max);
DECLARE @VERSION_STRING varbinary(max);
SET @VERSION_STRING=CONVERT(varbinary,'');
SET @desired_string='What a clever guy that Eirikur is!';
WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(LEN(@desired_string)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2),
ordered_nums AS (SELECT N,mod_n=ROW_NUMBER() OVER (ORDER BY N%(LEN(@desired_string)/4)) FROM NUMS)
SELECT @VERSION_STRING=@VERSION_STRING+CONVERT(varbinary,CHAR(ASCII(SUBSTRING(@desired_string,mod_N,1))+N))
FROM ordered_nums
ORDER BY n;
SELECT @VERSION_STRING;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(max)') AS VERSION_NAME;
Cheers!
April 19, 2016 at 3:42 pm
Personally, this strikes me as more interesting!
/* Version number binary from daily registy */
DECLARE @VERSION_STRING VARBINARY(max) = 0x6E7976777926807C71767771798078697977337C35778938398D3B8B8F80408F9594848597949C8E9B978F929293A2A5A05253A19A9AA6999E9FA05CA1B0A4B261A3;
--DECLARE @VERSION_STRING VARBINARY(max) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(MAX)') AS VERSION_NAME;
GO
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2016 at 6:20 pm
Luis Cazares (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?That would be too easy for them. Just keep saying their guesses are wrong without even checking them. Maybe they'll eventually test them to prove they're right.
My vote here is to lead them on a path. 2-3 people try to help them learn how to test things, or learn, without giving a solution. Unless they have tried something and show results that don't seem to match.
However, after giving them info, if they don't want to try, cease to help. I'd also prefer more people (4, 5, 6) not pile on. Just let the thread drop off.
April 20, 2016 at 1:22 am
Phil Parkin (4/19/2016)
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
Now where's that apostrophe gone? Eirikur, you need to fix this.
Sorry about that, here's a quick spelling correction
😎
/* Version number binary from daily registy */
DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(7)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
STUFF((
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)'),DATALENGTH(@version_string) ^ 4,0,CHAR((POWER(3,DATALENGTH(@VERSION_STRING)) & 0x28) - 1)) AS VERSION_NAME;
GO
Edit: Typo
April 20, 2016 at 1:44 am
Jacob Wilkins (4/19/2016)
Brandie Tarvin (4/19/2016)
Phil Parkin (4/19/2016)
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this? Full credit to Eirikur for the query - it's all him.
* Version number binary from daily registy */DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(24)') AS VERSION_NAME;
Now where's that apostrophe gone? Eirikur, you need to fix this.
Huh. Maybe I did it wrong, but when I tried to add it:
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x0013) which is not allowed in XML...
I did a convert to VARBINARY(17) on the phrase with the apostrophe.
It's not quite that straightforward. You have to change the order of the characters in the string as well as the characters themselves to match the obfuscating ASCII shifts and repositioning he does.
Just use this to get the apostrophe:
DECLARE @VERSION_STRING VARBINARY(16)=0x4D552345545560584F564C5E2D355359;
Or in general, with some data type changes to handle longer strings and code to generate the VARBINARY:
DECLARE @desired_string varchar(max);DECLARE @VERSION_STRING varbinary(max);
SET @VERSION_STRING=CONVERT(varbinary,'');
SET @desired_string='What a clever guy that Eirikur is!';
WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(LEN(@desired_string)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2),
ordered_nums AS (SELECT N,mod_n=ROW_NUMBER() OVER (ORDER BY N%(LEN(@desired_string)/4)) FROM NUMS)
SELECT @VERSION_STRING=@VERSION_STRING+CONVERT(varbinary,CHAR(ASCII(SUBSTRING(@desired_string,mod_N,1))+N))
FROM ordered_nums
ORDER BY n;
SELECT @VERSION_STRING;
/* Inline Tally for parsing the binary string */
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
/* Human readable output */
SELECT
(
SELECT
CHAR(ASCII(SUBSTRING(@VERSION_STRING,NM.N,1)) - NM.N)
FROM NUMS NM
ORDER BY (NM.N % (DATALENGTH(@VERSION_STRING) / 4)) % (DATALENGTH(@VERSION_STRING) / 3)
FOR XML PATH(''),TYPE
).value('.[1]','VARCHAR(max)') AS VERSION_NAME;
Cheers!
0x2163665B736B286D782A6D7871587E7C
😎
April 20, 2016 at 3:09 am
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this?
No, I was thinking more something like
SELECT a.article_id, a.title, SUM(ISNULL(av.page_views)) AS TotalViews
FROM articles a OUTER JOIN article_views av on articles.article_id = article_views.article_id
WHERE latest_comment IS NULL
GROUP BY article_id
ORDER BY page_views DESC;
Looks legit at a first glance. If someone doesn't know SQL it's near-impossible to see where the errors are, but will not run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2016 at 5:28 am
GilaMonster (4/20/2016)
Ed Wagner (4/19/2016)
SQLRNNR (4/19/2016)
GilaMonster (4/19/2016)
And more homework in the forums. Is it wrong to want to post a sensible-looking but completely incorrect query as answers?Do it!
Are you thinking something along the lines of this?
No, I was thinking more something like
SELECT a.article_id, a.title, SUM(ISNULL(av.page_views)) AS TotalViewsFROM articles a OUTER JOIN article_views av on articles.article_id = article_views.article_id
WHERE latest_comment IS NULL
GROUP BY article_id
ORDER BY page_views DESC;
Looks legit at a first glance. If someone doesn't know SQL it's near-impossible to see where the errors are, but will not run.
Kind-of a pseudo-code using SQL syntax. More along the lines of showing the logic without giving the answer. I don't know if it would illicit the normal "that won't run. please fix." comment or if it would help them see what you're trying to say. I guess it's worth a shot if they don't respond to anything else.
The "I can't figure it out" comment on that thread, which really means "do my homework for me"...I didn't post. Too reminiscent of WC.
Viewing 15 posts - 53,626 through 53,640 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply