Tuesday, October 13, 2009

SQL Challange

Table A

code        aname
----------- ----------
1           Cat
2           Dog
3           Bird

Table B

code        bname

----------- ----------

1 aaa

1 bbb

2 ccc

2 ddd

Table C

code        cname

----------- ----------

1 xxx

1 yyy

1 zzz

2 www

The task is to write a query that produces the following output from the above tables. The query should run on SQL Server 2000 as well as on SQL Server 2005.


code        aname      bname      cname

----------- ---------- ---------- ----------

1 Cat aaa xxx

1 Cat bbb yyy

1 Cat
NULL zzz

2 Dog ccc www

2 Dog ddd

3 Bird

Sample Data

DECLARE @a TABLE (code INT, aname VARCHAR(10))
INSERT INTO @a(code, aname) SELECT 1,'Cat'
INSERT INTO @a(code, aname) SELECT 2,'Dog'
INSERT INTO @a(code, aname) SELECT 3,'Bird'
DECLARE @b TABLE (code INT, bname VARCHAR(10))
INSERT INTO @b(code, bname) SELECT 1,'aaa'
INSERT INTO @b(code, bname) SELECT 1,'bbb'
INSERT INTO @b(code, bname) SELECT 2,'ccc'
INSERT INTO @b(code, bname) SELECT 2,'ddd'
DECLARE @c TABLE (code INT, cname VARCHAR(10))
INSERT INTO @c(code, cname) SELECT 1,'xxx'
INSERT INTO @c(code, cname) SELECT 1,'yyy'
INSERT INTO @c(code, cname) SELECT 1,'zzz'
INSERT INTO @c(code, cname) SELECT 2,'www'

Write a Single Query that should run on SQL Server 2000 and SQL Server 2005


Post a Comment