April 20, 2012 at 10:56 pm
Write a query that returns a two column rowset:
-Column 1: contiguous integers from 1 - 102
-Column 2: if Column 1 is a multiple of 4 then "green"
if Column 1 is a multiple of 7 then "gate"
if Column 1 is a multiple of 4 and 7 then "greengate"
otherwise, NULL
Thanks,
Dayo
April 20, 2012 at 11:07 pm
Is this an Interview/Homework question?
Share what you have tried so far.
We will help you out from wherever you are stuck.
This will help you learn better.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 20, 2012 at 11:27 pm
deebabat (4/20/2012)
Write a query that returns a two column rowset:-Column 1: contiguous integers from 1 - 102
-Column 2: if Column 1 is a multiple of 4 then "green"
if Column 1 is a multiple of 7 then "gate"
if Column 1 is a multiple of 4 and 7 then "greengate"
otherwise, NULL
Thanks,
Dayo
It is a variation of FizzBizz.
April 20, 2012 at 11:48 pm
Thanks for your advice, this is what I have it's just that I am not sure it's right
WITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < 102
)
SELECT
CASE
WHEN Number % 4 = 0 AND Number % 7 = 0 THEN 'greengate'
WHEN Number % 4 = 0 THEN 'green'
WHEN Number % 7 = 0 THEN 'gate'
ELSE CONVERT(VARCHAR(3), Number)
END
FROM Numbers
ORDER BY Number
April 20, 2012 at 11:53 pm
deebabat (4/20/2012)
Thanks for your advice, this is what I have it's just that I am not sure it's rightWITH Numbers(Number) AS (
SELECT 1
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < 102
)
SELECT
CASE
WHEN Number % 4 = 0 AND Number % 7 = 0 THEN 'greengate'
WHEN Number % 4 = 0 THEN 'green'
WHEN Number % 7 = 0 THEN 'gate'
ELSE CONVERT(VARCHAR(3), Number)
END
FROM Numbers
ORDER BY Number
Well, first, your cte numbers table is inefficient as you are using a recursive cte to generate the numbers.
Second, you aren't generating a 2 column rowset, nor are you returning null if the N is divisible by 4 and/or 7.
Here is another option:
with
e1(n) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1), -- 10 rows
e2(n) as (select a.n from e1 a cross join e1 b), -- 100 rows
e3(n) as (select a.n from e2 a cross join e2 b), -- 10000 rows
tally(n) as (select ROW_NUMBER() over (order by (select null)) from e3)
select
n,
nullif(
case when n % 4 = 0 then 'green' else '' end +
case when n % 7 = 0 then 'gate' else '' end,
'')
from
tally
where
n <= 102;
April 21, 2012 at 12:13 am
Lynn, thank you so much!
Here is another method I tried and it's worked just fine.
DECLARE @num INT = 1000000
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)
SELECT TOP (@num)
Number,
CASE
WHEN Number % 28 = 0 THEN 'greengate'
WHEN Number % 4 = 0 THEN 'green'
WHEN Number % 7 = 0 THEN 'gate'
ELSE CONVERT(VARCHAR(7), Number)
END AS greengate
INTO #greengate
FROM Numbers
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT greengate FROM #greengate ORDER BY Number
April 21, 2012 at 12:20 am
deebabat (4/21/2012)
Lynn, thank you so much!Here is another method I tried and it's worked just fine.
DECLARE @num INT = 1000000
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)
SELECT TOP (@num)
Number,
CASE
WHEN Number % 28 = 0 THEN 'greengate'
WHEN Number % 4 = 0 THEN 'green'
WHEN Number % 7 = 0 THEN 'gate'
ELSE CONVERT(VARCHAR(7), Number)
END AS greengate
INTO #greengate
FROM Numbers
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT greengate FROM #greengate ORDER BY Number
Two things wrong with the above based on your requirements. You are not stopping at 102 and you aren't returning null when the number is not evenly divisible by 4 and/or 7.
April 21, 2012 at 12:23 am
Here is a better one
DECLARE @num INT = 102
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)
SELECT TOP (@num)
Number,
CASE
WHEN Number % 28 = 0 THEN 'greengate'
WHEN Number % 4 = 0 THEN 'green'
WHEN Number % 7 = 0 THEN 'gate'
ELSE NULL
END AS greengate
INTO #greengate
FROM Numbers
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT greengate FROM #greengate ORDER BY Number
Great thanks to Lynn again for the contribution.
Dayo
April 21, 2012 at 12:27 am
deebabat (4/21/2012)
Here is a better oneDECLARE @num INT = 102
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Number FROM L5)
SELECT TOP (@num)
Number,
CASE
WHEN Number % 28 = 0 THEN 'greengate'
WHEN Number % 4 = 0 THEN 'green'
WHEN Number % 7 = 0 THEN 'gate'
ELSE NULL
END AS greengate
INTO #greengate
FROM Numbers
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
SELECT greengate FROM #greengate ORDER BY Number
Great thanks to Lynn again for the contribution.
Dayo
You still are not meeting your own requirements. The final select is only returning a single column result set, not a two column result set. Also, curious why you are inserting everything into a temporay table instead of just returning it directly from your query?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply