Rollup Vs Cube

Rollup		Cube
1	The ROLLUP operator is useful for generating reports that contain subtotals and totals	The Cube operator is useful for generating reports that contain totals
2	ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.	CUBE generates a result set showing aggregates for all combinations of values in the selected columns.
3	Example : SELECT DEPTNO,JOB,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPTNO,JOB WITH ROLLUP It will give u total and subtotal of all department as well as job.	Example : SELECT DEPTNO,JOB,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPTNO,JOB WITH CUBE It will give u total of all departments.

Charindex Vs Patindex

CharIndex		PatIndex
1	Returns the Starting position of specified Expression in Character String.	Returns the Starting position of First Occurrence of a pattern in Specified Expression, Or Zeros if Pattern is not found.
2	In Charindex you can specify Start location.	In PatIndex you can not specify starting location. It will return you First Occurrence of a pattern
3	SELECT CHARINDEX(‘;’,’KRISHNA;RADHA’) Output : 8 It will give u position of Semicolon.	Example : SELECT PATINDEX(‘%A%’,’KRISHNA;RADHA’) Output : 7 It will give u position of First occurrence of A.

System Datatype Vs User Defined Datatype

System Datatype		User Defined Datatype
1	System Data type is defined by the System.	User Defined Data type defined by the User.
2	There are more than 25 System Data type available in SQL Server.	User Defined Data type is also System data type, just name of that Data type is Changed by the User.
3	Example : Col1 BIGINT In this Example BIGINT is a System Data type	Example : SP_ADDTYPE ‘pk’, BIGINT In this Example ‘PK’ is User Defined Data type. You can use PK Anywhere instead of BIGINT.

Truncate Vs Delete

Truncate		Delete
1	It is DDL(Data Definition Language) Statement.	It is DML(Data Manipulation Language) Statement.
2	It Delete all rows from table.	You Can Delete All rows as well as specified rows using Delete.
3	You can not Rollback all raw.	Using Rollback you can retrieve all rows from the point of last commit.
4	It release all raw including Memory space, Just Structure remains there.	It delete all raw but, not Memory space.
5	Example : Truncate Table Emp	Example : Delete From Emp Where Empno=7369

Small DateTime vs DateTime

Small DateTime		DateTime
1	It Occupies 4 byte Size	It Occupies 8 byte Size
2	The precision of small datetime is one minute	The precision of datetime is 3.33 milli Seconds
3	It Stores Date From 1st January 1900 through 6th June 2079. Which is usually more than enough	It Stores Date From 1st January 1753 through 31st December 9999.

Matching Data Between Rows and Columns

DECLARE @Cols TABLE(Col INT)

INSERT INTO @Cols VALUES (1)
INSERT INTO @Cols VALUES (2)
INSERT INTO @Cols VALUES (3)
INSERT INTO @Cols VALUES (4)
INSERT INTO @Cols VALUES (5)
INSERT INTO @Cols VALUES (6)
INSERT INTO @Cols VALUES (7)
INSERT INTO @Cols VALUES (8)
INSERT INTO @Cols VALUES (9)

DECLARE @Rows TABLE(Row INT)

INSERT INTO @Rows VALUES (100)
INSERT INTO @Rows VALUES (104)
INSERT INTO @Rows VALUES (101)
INSERT INTO @Rows VALUES (99)
INSERT INTO @Rows VALUES (77)
INSERT INTO @Rows VALUES (20)
INSERT INTO @Rows VALUES (10)

Col
-----------
1
2
3
4
5
6
7
8
9

Row
-----------
100
104
101
99
77
20
10

The challenge is to mark a coordinate, with a value of X,
if and only if the row value is divisible by the col value,
i.e. it has a modulo of zero. The additional requirements are:
the final query must work with random row values.

Row         1    2    3    4    5    6    7    8    9   Total
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
10          x    x              x                          3
20          x    x         x    x                          4
77          x                             x                2
99          x         x                             x      3
100         x    x         x    x                          4
101         x                                              1
104         x    x         x                   x           4
----------------------------------------------------------------
Total       7    4    1    3    3     0    1   1     1     21

Challenge Requirements Summary

1. Only coordinates where the column value modulo the row value equals zero should be marked, with an "X"
2. Number of rows in the table is not fixed. The query should work with variable number of rows in the table.

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.

Output

code        aname      bname      cname

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

1           Cat        aaa        xxx

1           Cat        bbb        yyy

1           Cat        NULL       zzz

2           Dog        ccc        www

2           Dog        ddd        NULL

3           Bird       NULL       NULL

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