23>
24> CREATE TABLE project (project_no CHAR(4) NOT NULL,
25> project_name CHAR(15) NOT NULL,
26> budget FLOAT NULL)
27>
28> insert into project values ('p1', 'Search Engine', 120000.00)
29> insert into project values ('p2', 'Programming', 95000.00)
30> insert into project values ('p3', 'SQL', 186500.00)
31>
32> select * from project
33> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 120000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1>
2> -- Local variable must be defined using the DECLARE statement
3>
4> DECLARE @avg_budget MONEY, @extra_budget MONEY
5>
6> SET @extra_budget = 15000
7> SELECT @avg_budget = AVG(budget) FROM project
8>
9> IF (SELECT budget FROM project WHERE project_no='p1') < @avg_budget
10> BEGIN
11> UPDATE project SET budget = budget + @extra_budget WHERE project_no = 'p1'
12> PRINT 'Budget for p1 increased by'
13> PRINT @extra_budget
14> END
15> ELSE
16> PRINT 'Budget for p1 unchanged'
17> GO
(1 rows affected)
(1 rows affected)
Budget for p1 increased by
(1 rows affected)
15000.00
1> select * from project
2> GO
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 135000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1> drop table project
2> GO
1>
|