Not logged in - Login

SQL 2008 Delighters samples

Some samples of Delighters that were introduced in SQL 2008

/*
 
CREATE TABLE PayRates (
employee_nbr INT PRIMARY KEY,
pay_rate DECIMAL(15, 2),
performance_score INT);
 
*/
 
-- Declare and initialize variable
DECLARE @count INT = 1;
 
-- Compound assignments
SET @count += 1; -- result = 2
SET @count /= 2; -- result = 1
SET @count *= 5; -- result = 5
SET @count %= 3; -- result = 2
SET @count -= 1; -- result = 1


SELECT @count;
 
-- insert initial values into table.
INSERT INTO PayRates VALUES 
(1, 40.00, 5)
, (2, 45.50, 4)
, (3, 39.50, 6);
 
-- Update the Pay_Rate column
UPDATE PayRates SET pay_rate *= performance_score * 0.25;
 
SELECT employee_nbr, pay_rate, performance_score
FROM PayRates;
 
-- get the maximum pay rate
DECLARE @max_pay_rate DECIMAL(15, 2) = (SELECT MAX(pay_rate) FROM PayRates);
 
SELECT  @max_pay_rate AS max_pay_rate;
 

/*
 
DROP TABLE PayRates; 

*/