loading..
   English
18:02

Torus Planet page 2

Recently, because of appearance the torussians inside sql-ex.ru site, much information about Lorus Galaxy became known. It turned out that Torus 36x36 is not biggest planet of the system.  In the aims of cultural exchange some facts of historic chronicles of Torus was added to common and checking site databases (Painting, Ships, etc.).

The sql-ex task situations explains that Torus unit of length is called a torometer, and united currency is torobucks.  The Torus life base is water and its ice variety. Task situation contains other information on physical-chemical characteristics, economical, social and political systems of planets Torus MxN.

Now we will not be tied with our common databases and present the next universal task situation on Torus.

Neighbor states on Torus 3x7

For each state of the Torus 3x7:

T00    T01        T06
T10    T11        T16
T20    T21        T26
find a list of neighbor states, which have bounds of not non-zero length. Result must be outputted in the table with five columns: [State], [NN], [WW], [SS], [EE].

Hint.
This is the answer for common (non-toroidal) case:

State NN WW SS EE
T00 NULL NULL T10 T01
T01 NULL T00 T11 T02
T02 NULL T01 T12 T03
T03 NULL T02 T13 T04
T04 NULL T03 T14 T05
T05 NULL T04 T15 T06
T06 NULL T05 T16 NULL
T10 T00 NULL T20 T11
T11 T01 T10 T21 T12
T12 T02 T11 T22 T13
T13 T03 T12 T23 T14
T14 T04 T13 T24 T15
T15 T05 T14 T25 T16
T16 T06 T15 T26 NULL
T20 T10 NULL NULL T21
T21 T11 T20 NULL T22
T22 T12 T21 NULL T23
T23 T13 T22 NULL T24
T24 T14 T23 NULL T25
T25 T15 T24 NULL T26
T26 T16 T25 NULL NULL

This is solution for toroidal case:

Console
Execute
  1. SELECT
  2. 'T'+CAST(i AS varchar)+CAST(j AS varchar) [State]
  3. ,'T'+CAST(k1 AS varchar)+CAST(j1 AS varchar) [NN]
  4. ,'T'+CAST(i2 AS varchar)+CAST(k2 AS varchar) [WW]
  5. ,'T'+CAST(k4 AS varchar)+CAST(j4 AS varchar) [SS]
  6. ,'T'+CAST(i3 AS varchar)+CAST(k3 AS varchar) [EE]
  7. FROM (SELECT j=0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  8. SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6)J
  9. CROSS JOIN (SELECT i=0 UNION ALL SELECT 1 UNION ALL SELECT 2)I
  10. CROSS APPLY(SELECT i1=i-1,j1=j,i2=i,j2=j-1,i3=i,j3=j+1,i4=i+1,j4=j)D
  11. CROSS APPLY(SELECT
  12. k1=CASE WHEN i1<0 THEN 2 ELSE i1 END
  13. ,k2=CASE WHEN j2<0 THEN 6 ELSE j2 END
  14. ,k3=CASE WHEN j3>6 THEN 0 ELSE j3 END
  15. ,k4=CASE WHEN i4>2 THEN 0 ELSE i4 END
  16. )E;

Valid answer:

State NN WW SS EE
T00 T20 T06 T10 T01
T01 T21 T00 T11 T02
T02 T22 T01 T12 T03
T03 T23 T02 T13 T04
T04 T24 T03 T14 T05
T05 T25 T04 T15 T06
T06 T26 T05 T16 T00
T10 T00 T16 T20 T11
T11 T01 T10 T21 T12
T12 T02 T11 T22 T13
T13 T03 T12 T23 T14
T14 T04 T13 T24 T15
T15 T05 T14 T25 T16
T16 T06 T15 T26 T10
T20 T10 T26 T00 T21
T21 T11 T20 T01 T22
T22 T12 T21 T02 T23
T23 T13 T22 T03 T24
T24 T14 T23 T04 T25
T25 T15 T24 T05 T26
T26 T16 T25 T06 T20

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.