loading..
Ðóññêèé    English
18:44

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(AS varchar)+CAST(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.