Tutorial SQLServer 06

SQL
SQL
Transact-SQL
False
True
Transact-SQL
Value1 = Value2
SQL
True
Value2
False
Value1
Value1 <> Value2
True
False
>
<
Transact-SQL
Value1 < Value2
Value1 > Value2
False
True
>=
<=
Value1 >= Value2
Value1 <= Value2
>
<
False
True
Value2
>
Value1
<
True
SQL
False
Transact-SQL
Keyword Expression
Statement
END
Statement SQL
Expression
BEGIN
SQL
BEGIN
END
SQL
BEGIN
END
Keyword Expression
BEGIN
Statement Line 1
Statement Line 2
Statement Line n
END
END
BEGIN
Keyword Expression
BEGIN
Statement
END
END BEGIN
IF
IF
Transact-SQL
IF Condition
Statement
IF
False
True
DECLARE @DateHired As DateTime,
@CurrentDate As DateTime
SET @DateHired = '1996/10/04'
SET @CurrentDate = '2007/04/11'
IF @DateHired < @CurrentDate
PRINT 'You have the experience required for a new promotion
in this job'
GO
IF..ELSE
IF
ELSE
DECLARE @DateHired As DateTime,
@CurrentDate As DateTime
SET @DateHired = '1996/10/04'
SET @CurrentDate = '1995/04/11'
IF @DateHired < @CurrentDate
PRINT 'You have the experience required for a new promotion
in this job'
ELSE
PRINT 'Sorry, you have NOT the experience required'
GO
CASE...WHEN...THEN
CASE
CASE Expression
WHEN Value1 THEN Result
WHEN Value2 THEN Result
WHEN Value_n THEN Result
END
M
CharGender
Femal
DECLARE @CharGender Char(1),
@Gender Varchar(20);
SET @CharGender = 'F';
SET @Gender =
CASE @CharGender
WHEN 'm' THEN
WHEN 'M' THEN
WHEN 'f' THEN
WHEN 'F' THEN
END;
F
'Male'
'Male'
'Female'
'Female'
SELECT 'Student Gender: ' + @Gender;
GO
CASE...WHEN...THEN...ELSE
Male
ELSE
CASE Expression
WHEN Value1 THEN Result
WHEN Value2 THEN Result
WHEN Value_n THEN Result
ELSE Alternative
END
ELSE
DECLARE @CharGender Char(1),
@Gender Varchar(20);
SET @CharGender = 'g';
SET @Gender =
CASE @CharGender
WHEN 'm' THEN 'Male'
WHEN 'M' THEN 'Male'
WHEN 'f' THEN 'Female'
WHEN 'F' THEN 'Female'
ELSE 'Unknown'
END;
SELECT 'Student Gender: ' + @Gender;
GO
ELSE
CASE
ELSE
NULL
WHILE
WHILE
SQL
WHILE Expression
Statement
WHILE
False
False
True
DECLARE @Number As int
WHILE @Number < 5
SELECT @Number AS Number
GO
WHILE
DECLARE @Number As int
SET @Number = 1
WHILE @Number < 5
BEGIN
SELECT @Number AS Number
SET @Number = @Number + 1
END
GO
FALSE TRUE
FALSE
Yes
1
TRUE
0
False
0
True
1
True
False
NULL
SQL
NULL
NULL
Transact-SQL
0
IS
IS
IS NULL
-- Square Calculation
DECLARE @Side As Decimal(10,3),
@Perimeter As Decimal(10,3),
@Area As Decimal(10,3);
SET
@Perimeter = @Side * 4;
SET
@Area = @Side * @Side;
IF @Side IS NULL
PRINT 'A null value is not welcome'
ELSE IF @Side > 0
BEGIN
SELECT @Side AS Side;
SELECT @Perimeter AS Perimeter ;
SELECT @Area AS Area;
END;
ELSE
PRINT 'You must provide a positive value';
GO
NULL
-- Square Calculation
DECLARE @Side As Decimal(10,3),
@Perimeter As Decimal(10,3),
@Area As Decimal(10,3);
SET
@Side = 48.126;
SET
@Perimeter = @Side * 4;
SET
@Area = @Side * @Side;
IF @Side IS NULL
PRINT 'A null value is not welcome'
ELSE IF @Side > 0
BEGIN
SELECT @Side AS Side;
SELECT @Perimeter AS Perimeter ;
SELECT @Area AS Area;
END;
ELSE
PRINT 'You must provide a positive value';
GO
NOT
NOT

3
30
1

