—————————————————
— I – Silly SELECTs. But Funny results.
—————————————————
USE [AdventureWorks2008R2]
GO
—————————————————
SELECT *
FROM [Person].[Address]
WHERE 1=1
Ans: It displays all the records from the [Person].[Address] table.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE 1 IS 1
Ans: It throws the error that Incorrect syntax near ‘1’
—————————————————
SELECT *
FROM [Person].[Address]
WHERE 1=0
Ans: It displays 0 records.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE AddressID = NULL
Ans: It displays 0 records.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE NULL = NULL
Ans: It displays 0 records.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE NULL IS NULL
Ans: It displays all the records from the [Person].[Address] table.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE NULL = 1
Ans: It displays 0 records.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE NULL IS 1
Ans: It throws the error that Incorrect syntax near ‘1’
—————————————————
SELECT *
FROM [Person].[Address]
WHERE NULL IS ‘1’
Ans: It throws the error that Incorrect syntax near ‘1’
—————————————————
SELECT *
FROM [Person].[Address]
WHERE [AddressLine1] LIKE ‘%[%]%’
Ans: It returns the all the records which contains % in the values of [AddressLine1] column.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE [AddressLine1] LIKE ‘%%%’
Ans: It returns all the records from [Person].[Address] table.
—————————————————
SELECT *
FROM [Person].[Address]
WHERE [AddressID] LIKE ‘[^1-4]’
Ans: It returns all the records from [Person].[Address] table except the records which contains the values 1,2,3,4 for the column [AddressID]
—————————————————
SELECT *
FROM [Person].[Address]
WHERE [AddressID] LIKE ‘[1-4]’
Ans: It returns all the records from [Person].[Address] table which contains the values 1,2,3,4 for the column [AddressID]
—————————————————
SELECT ‘SQLVersity’+1
Ans: It throws error as Conversion failed when converting the varchar value ‘SQLVersity’ to data type int.
—————————————————
SELECT (SELECT ‘SQLVersity’)
Ans: SQLVersity
—————————————————
SELECT SELECT ‘SQLVersity’
Ans: It throws the error as Incorrect syntax near the keyword ‘SELECT’.
—————————————————
SELECT * FROM ‘SQLVersity’
Ans: It throws the error as Incorrect syntax near ‘SQLVersity’.
—————————————————
SELECT ‘SQLVersity’ FROM [DatabaseLog]
Ans: It displays only one column with the values as SQLVersity and number of rows will be same as the number of records in [DatabaseLog] table.
—————————————————
— II – NULL makes your life blank.
—————————————————
SELECT NULL + 1
Ans: NULL
—————————————————
SELECT ‘NULL’ + 1
Ans: It throws the error as Conversion failed when converting the varchar value ‘NULL’ to data type int.
—————————————————
SELECT ‘NULL’ + ‘1’
Ans: NULL1
—————————————————
SELECT NULL + ‘1’
Ans: NULL
—————————————————
SELECT COALESCE(NULL,1)+1
Ans: 2
—————————————————
SELECT NULL = NULL
Ans: It throws the error as Incorrect syntax near ‘=’.
—————————————————
SELECT NULL IS NULL
Ans: It throws the error as Incorrect syntax near the keyword ‘IS’.
—————————————————
SELECT NULL = 1
Ans: It throws the error as Incorrect syntax near ‘=’.
—————————————————
SELECT 1=1
Ans: It throws the error as Incorrect syntax near ‘=’.
—————————————————
SELECT NULL + NULL
Ans: NULL
—————————————————
SELECT NULL – NULL
Ans: NULL
—————————————————
SELECT NULL * NULL
Ans: NULL
—————————————————
SELECT NULL \ NULL
Ans: It throws the error as Incorrect syntax near ‘\ ‘.
—————————————————
SELECT NULL / NULL
Ans: NULL
—————————————————
SELECT NULL % NULL
Ans: NULL
—————————————————

— III – Speciality of Special characters
—————————————————
SELECT !
Ans: It throws the error as Incorrect syntax near ‘!’.
—————————————————
SELECT @
Ans: It throws the error as Must declare the scalar variable “@”.
—————————————————
SELECT #
Ans: It throws the error as Invalid column name ‘#’.
—————————————————
SELECT $
Ans: 0.00
—————————————————
SELECT %
Ans: It throws the error as Incorrect syntax near ‘%’.
—————————————————
SELECT ^
Ans: It throws the error as Incorrect syntax near ‘^’.
—————————————————
SELECT &
Ans: It throws the error as Incorrect syntax near ‘&’.
—————————————————
SELECT *
Ans: It throws the error as Must specify table to select from.
—————————————————
SELECT (
Ans: It throws the error as Incorrect syntax near ‘(‘.
—————————————————
SELECT )
Ans: It throws the error as Incorrect syntax near ‘)’.
—————————————————
SELECT ”
Ans: one blank space
—————————————————
SELECT “”
Ans: It throws the error as An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
—————————————————
SELECT ‘?’
Ans: ?
—————————————————

— IV – TOP IS always at TOP
—————————————————
SELECT TOP 5.9 *
FROM [Person].[Address]
Ans: It throws the error as The number of rows provided for a TOP or FETCH clauses row count parameter must be an integer.
—————————————————
SELECT TOP ‘5’ *
FROM [Person].[Address]
Ans: It throws the error as Incorrect syntax near ‘5’.
—————————————————
SELECT TOP NULL *
FROM [Person].[Address]
Ans: It throws the error as Incorrect syntax near the keyword ‘NULL’.
—————————————————

— V – Conversion conflicts
—————————————————
SELECT 3+3
Ans: 6
—————————————————
SELECT 5+’SQLVersity’
Ans: It throws the error as Conversion failed when converting the varchar value ‘SQLVersity’ to data type int.
—————————————————
SELECT 5+’5′
Ans: 10
—————————————————
SELECT ‘SQLVersity’+5
Ans: It throws the error as Conversion failed when converting the varchar value ‘SQLVersity’ to data type int.
—————————————————
SELECT ‘SQLVersity’+’3’
Ans: SQLVersity3
—————————————————

— VI – The Crazy COUNT
—————————————————
SELECT COUNT(6)
Ans: 1
—————————————————
SELECT COUNT(*)
Ans: 1
—————————————————
SELECT COUNT(0)
Ans: 1
—————————————————
SELECT COUNT(‘6’)
Ans: 1
—————————————————
SELECT COUNT(‘SQLVersity’)
Ans: 1
—————————————————
SELECT COUNT(6+8)
Ans: 1
—————————————————
SELECT COUNT(6+’8′)
Ans: 1
—————————————————
SELECT COUNT(SV)
Ans: It throws the error as Invalid column name ‘SV’.
—————————————————
SELECT COUNT(‘SQLVersity’)
Ans: 1
—————————————————
SELECT TOP 5 COUNT(*)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT DISTINCT TOP 5 COUNT(*)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(9)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(9+9)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(9+’SQLVersity’)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(‘SQLVersity’)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(‘*’)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(‘$’)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(‘>’)
FROM [Person].[Address]
Ans: Displays the total number of roows in the [Person].[Address] table.
—————————————————
SELECT COUNT(SV)
FROM [Person].[Address]
Ans: It throws the error as Invalid column name ‘SV’.
—————————————————
SELECT COUNT(SELECT TOP 5 [AddressID]
FROM [Person].[Address])
Ans: It throws the error as Incorrect syntax near the keyword ‘SELECT’.
—————————————————
SELECT COUNT(SELECT COUNT(*)
FROM [Person].[Address])
Ans: It throws the error as Incorrect syntax near the keyword ‘SELECT’.
—————————————————
SELECT COUNT(*)
Ans: 1
—————————————————
SELECT COUNT(4)
Ans: 1
—————————————————
SELECT COUNT(*) + COUNT(*)
Ans: 2
—————————————————
— VII – Aggressive Aggregates.
—————————————————
SELECT SUM(1-3*4) Ans: -11
SELECT AVG(1-3*4) Ans: -11
SELECT MAX(1-3*4) Ans: -11
SELECT MIN(1-3*4) Ans: -11
SELECT COUNT(1-3*4) Ans: 1
—————————————————
SELECT SUM(1,2,3)
SELECT AVG(1,2,3)
SELECT MAX(1,2,3)
SELECT MIN(1,2,3)
SELECT COUNT(1,2,3)
Ans: All the above functions allows only one argument. But we are passing 3 arguments. So they throw the error as This function requires 1 argument(s)
—————————————————
SELECT SUM(’55’) Ans: It throws the error as Operand data type varchar is invalid for sum operator.
SELECT AVG(’45’) Ans: It throws the error as Operand data type varchar is invalid for avg operator.
SELECT MAX(’34’) Ans: 34
SELECT MIN(’14’) Ans: 14
SELECT COUNT(’13’) Ans: 1
—————————————————
SELECT SUM(‘SQLVersity’) Ans: It throws the error as Operand data type varchar is invalid for sum operator.
SELECT AVG(‘SQLVersity’) Ans: It throws the error as Operand data type varchar is invalid for avg operator.
SELECT MAX(‘SQLVersity’) Ans: SQLVersity
SELECT MIN(‘SQLVersity’) Ans: SQLVersity
SELECT COUNT(‘SQLVersity’) Ans: 1
—————————————————
SELECT SUM(‘SQLVersity,SV’) Ans: It throws the error as Operand data type varchar is invalid for sum operator.
SELECT AVG(‘SQLVersity,SV’) Ans: It throws the error as Operand data type varchar is invalid for avg operator.
SELECT MAX(‘SQLVersity,SV’) Ans: SQLVersity,SV
SELECT MIN(‘SQLVersity,SV’) Ans: SQLVersity,SV
SELECT COUNT(‘SQLVersity,SV’) Ans: 1
—————————————————
SELECT SUM(sv)
SELECT AVG(sv)
SELECT MAX(sv)
SELECT MIN(sv)
SELECT COUNT(sv)
Ans: All the above throws the error as Invalid column name ‘sv’.
—————————————————
— VIII – Magical Microsoft functions
—————————————————
— ASCII
—————————————————
SELECT ASCII(‘14533’)
Ans: 49
—————————————————
SELECT ASCII(14533)
Ans: 49
—————————————————
SELECT ASCII(”)
Ans: NULL
—————————————————
SELECT ASCII(NULL)
Ans: NULL
—————————————————
SELECT ASCII(‘NULL’)
Ans: 78
—————————————————
SELECT ASCII(0)
Ans: 48
—————————————————
SELECT ASCII(‘94533’)
Ans: 57
—————————————————
SELECT ASCII(‘X4533’)
Ans: 88
—————————————————
SELECT ASCII(0.0)
Ans: 48
—————————————————
SELECT ASCII(1.1)
Ans: 49
—————————————————
SELECT ASCII(1.x)
Ans: It throws the error as Incorrect syntax near ‘x’.
—————————————————
SELECT ASCII(‘1.x’)
Ans: 49
—————————————————
— Lovely Len.
—————————————————
SELECT LEN(‘ABC’)
Ans: 3
—————————————————
SELECT LEN(-456.123)
Ans: 8
—————————————————
SELECT LEN(-456.123 )
Ans: 8
—————————————————
SELECT LEN(-456.123 0)
Ans: It throws the error as Incorrect syntax near ‘0’.
—————————————————
SELECT LEN(0 -456.123 )
Ans: 8
—————————————————
SELECT LEN( -456.123 )
Ans: 8
—————————————————
SELECT LEN( -456. 123 )
Ans: It throws the error as Incorrect syntax near ‘123’.
—————————————————
SELECT LEN( – 456.123 )
Ans: 8
—————————————————
SELECT LEN( + – 456.123 )
Ans: 8
—————————————————
SELECT LEN( + – 456.123 + )
Ans: It throws the error as Incorrect syntax near ‘)’.
—————————————————
— ROUND
—————————————————
SELECT ROUND(123.456)
Ans: It throws the error as The round function requires 2 to 3 arguments.
—————————————————
SELECT ROUND(123.456,0)
Ans: 123.000
—————————————————
SELECT ROUND(123.456,-2)
Ans: 100.000
—————————————————
SELECT ROUND(123.456,-10)
Ans: 0.000
—————————————————
SELECT ROUND(123.456,2)
Ans: 123.460
—————————————————
SELECT ROUND(123.456,10)
Ans: 123.456
—————————————————
SELECT ROUND(-123.456)
Ans: It throws the error as The round function requires 2 to 3 arguments.
—————————————————
SELECT ROUND(-123.456,0)
Ans: -123.000
—————————————————
SELECT ROUND(-123.456,-2)
Ans: -100.000
—————————————————
SELECT ROUND(-123.456,-10)
Ans: 0.000
—————————————————
SELECT ROUND(-123.456,2)
Ans: -123.460
—————————————————
SELECT ROUND(-123.456,10)
Ans: -123.456
—————————————————
SELECT ROUND(‘SV’,0)
Ans: It throws the error as Error converting data type varchar to float.
—————————————————
SELECT ROUND(123.456,’SV’)
Ans: It throws the error as Argument data type varchar is invalid for argument 2 of round function.
—————————————————
— UPPER & LOWER
—————————————————
SELECT UPPER(‘abc’)
Ans: ABC
—————————————————
SELECT UPPER(abc)
Ans: It throws the error as Invalid column name ‘abc’.
—————————————————
SELECT UPPER(123)
Ans: 123
—————————————————
SELECT UPPER(‘123’)
Ans: 123
—————————————————
SELECT UPPER(‘abc123’)
Ans: ABC123
—————————————————
SELECT UPPER(‘abc!@#’)
Ans: ABC!@#
—————————————————
SELECT UPPER(‘!@#abc’)
Ans: !@#ABC
—————————————————
— LTRIM & RTRIM
—————————————————
SELECT LTRIM(‘abc’)
Ans: abc
—————————————————
SELECT LTRIM(‘abc .’)
Ans: abc .
—————————————————
SELECT LTRIM(‘O abc .’)
Ans: O abc .
—————————————————
SELECT LTRIM(123)
Ans: 123
—————————————————
SELECT LTRIM( 123)
Ans: 123
—————————————————
SELECT LTRIM(123 )
Ans: 123
—————————————————
SELECT LTRIM(123 9)
Ans: It throws the error as Incorrect syntax near ‘9’.
—————————————————
SELECT LTRIM(0 123 9)
Ans: It throws the error as Incorrect syntax near ‘123’.
—————————————————
— CONCAT
—————————————————
SELECT CONCAT(‘SQL’,’Versity’)
Ans: SQLVersity
—————————————————
SELECT CONCAT(123,’Versity’)
Ans: 123Versity
—————————————————
SELECT CONCAT(-123,’Versity’)
Ans: -123Versity
—————————————————
SELECT CONCAT(-123y,’Versity’)
Ans: It throws the error as Incorrect syntax near ‘y’.
—————————————————
SELECT CONCAT(-1#&^W23,’Versity’)
Ans: It throws the error as Incorrect syntax near ‘#’.
—————————————————
— LEFT & RIGHT
—————————————————
SELECT LEFT(‘SQLVersity’,3)
Ans: SQL
—————————————————
SELECT LEFT(‘SQLVersity’,0)
Ans: It returns nothing.
—————————————————
SELECT LEFT(‘SQLVersity’,-3)
Ans: It throws the error as Invalid length parameter passed to the left function.
—————————————————
SELECT LEFT(123456,3)
Ans: 123
—————————————————
— SPACE
—————————————————
SELECT SPACE(7)
Ans: Returns 7 blank spaces.
—————————————————
SELECT SPACE(-7)
Ans: NULL
—————————————————
SELECT SPACE(0)
Ans: Returns blank output
—————————————————
SELECT SPACE(NULL)
Ans: NULL
—————————————————
SELECT SPACE(‘7’)
Ans: Returns 7 blank spaces.
—————————————————
SELECT SPACE(‘SQL’)
Ans: It throws the error as Conversion failed when converting the varchar value ‘SQL’ to data type int.
—————————————————

Happy Reading & Happy New Year  :-)