FilipDeVos

DECLARE @wealth d_wealth = 1000000.00 -- SQL 2008 only

IF @wealth > 10000
  BEGIN
    PRINT 'Yes My lord!'
  END
ELSE
  BEGIN
    PRINT 'Go to work you peasant!'
  END

FilipDeVos

DECLARE @wealth d_wealth
SELECT @wealth = 1000000.00
GO
DECLARE @wealth d_wealth = 1000000.00 -- SQL 2008 only

FilipDeVos

CREATE PROCEDURE p_get_family_info (@family d_last_name = null)
AS
SELECT p.first_name
     , p.last_name
     , p.alias
     , r.name as region
     , f.wealth
  FROM t_person p
  JOIN t_family f
    ON p.last_name = f.name

FilipDeVos

CREATE VIEW v_person
AS
SELECT p.first_name, p.last_name, r.region 
  FROM t_person p
  JOIN t_family f
    ON p.last_name = f.name
  JOIN t_region r
    ON f.region_id = r.region_id
GO  

FilipDeVos

  
-- create table to store region info with primary key on id
  if type_id('d_region_id') is null
    create type d_region_id from int
  if type_id('d_population') is null
    create type d_population from int
  if type_id('d_surface') is null
    create type d_surface from numeric(14,2)
  GO
  if object_id('t_region') is null

FilipDeVos

SELECT CASE WHEN name = '' THEN 'There is no name'
            WHEN name = 'Snow' THEN 'The bastard'
            ELSE name
        END
FROM t_family

FilipDeVos

SELECT CHARINDEX('X', 
         'There is a hidden X in this string')

FilipDeVos

select UPPER('I''m in all caps!!!')
select LOWER('I''M ALL IN LOWERCASE!!!')

FilipDeVos

select SUBSTRING('aaaxxxbbb', 4, 3)
select LEFT('aaaxxxbbb', 3)
select RIGHT('aaaxxxbbb', 3)

FilipDeVos

SELECT REPLACE(name, 'a', 'o') 
  FROM t_family