use pubs
GO
SELECT
title,
price,
'classification'=CASE
WHEN price < 10.00 THEN 'Low Priced'
WHEN price BETWEEN 10.00 AND 20.00 THEN 'Moderately Priced'
WHEN price > 20.00 THEN 'Expensive'
ELSE 'Unknown'
END
FROM titles
GO
SELECT
title,
price,
'Type' = CASE
WHEN type = 'mod_cook' THEN 'Modern Cooking'
WHEN type = 'trad_cook' THEN 'Traditional Cooking'
WHEN type = 'psychology' THEN 'Psychology'
WHEN type = 'business' THEN 'Business'
WHEN type = 'popular_comp' THEN 'Popular Computing'
ELSE 'Not yet decided'
END
FROM titles
GO
SELECT
title,
price,
'Type' = CASE type
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'trad_cook' THEN 'Traditional Cooking'
WHEN 'psychology' THEN 'Psychology'
WHEN 'business' THEN 'Business'
ELSE 'Not yet decided'
END
FROM titles
-- In this example, reviews have been turned in and big salary
-- adjustments are due. A review rating of 4 will double the
-- worker's salary, 3 will increase it by 60 percent, 2 will
-- increase it by 20 percent, and a rating lower than 2 results
-- in no raise. A raise will not be given if the employee has
-- been at the company for less than 18 months.
IF EXISTS (SELECT * FROM sysobjects WHERE name='employee_salaries')
DROP TABLE employee_salaries
GO
CREATE TABLE employee_salaries
(
c1 int,
salary money,
review int,
hire_date datetime
)
GO
INSERT employee_salaries VALUES (1,20000,4,'1990/1/1')
INSERT employee_salaries VALUES (1,20000,3,'1990/1/1')
INSERT employee_salaries VALUES (1,20000,2,'1990/1/1')
INSERT employee_salaries VALUES (1,20000,1,'1990/1/1')
INSERT employee_salaries VALUES (1,20000,4,getdate())
GO
SELECT * FROM employee_salaries
GO
UPDATE employee_salaries
SET salary =
CASE
WHEN (review = 4 AND
(DATEDIFF(month, hire_date, GETDATE()) >= 18))
THEN salary * 2.0
WHEN (review = 3 AND
(DATEDIFF(month, hire_date, GETDATE()) >= 18))
THEN salary * 1.6
WHEN (review = 2 AND
(DATEDIFF(month, hire_date, GETDATE()) >= 18))
THEN salary * 1.2
ELSE salary
END
-- This second formulation is identical, but it's more
-- intuitive and clearer.
UPDATE employee_salaries
SET salary =
CASE review
WHEN 4 THEN salary * 2.0
WHEN 3 THEN salary * 1.6
WHEN 2 THEN salary * 1.2
ELSE salary
END
WHERE (DATEDIFF(month, hire_date, GETDATE()) > 18)
GO
SELECT * FROM employee_salaries
go
if exists (SELECT * FROM sysobjects where name='employee_salaries')
DROP TABLE employee_salaries
go
SELECT title, pub_id, ISNULL(price, (SELECT MIN(price)
FROM titles)) FROM titles
SELECT title, pub_id,
CASE WHEN price IS NULL THEN (SELECT MIN(price) FROM titles)
ELSE price
END
FROM titles
