6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> GO
1>
2> insert titles values ('1', 'Secrets', 'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94')
3> insert titles values ('2', 'The', 'business', '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91')
4> insert titles values ('3', 'Emotional', 'psychology', '0736', $7.99, $4000.00, 10, 3336,'Note 3','06/12/91')
5> insert titles values ('4', 'Prolonged', 'psychology', '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91')
6> insert titles values ('5', 'With', 'business', '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91')
7> insert titles values ('6', 'Valley', 'mod_cook', '0877', $19.99, $0.00, 12, 2032,'Note 6','06/09/91')
8> insert titles values ('7', 'Any?', 'trad_cook', '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91')
9> insert titles values ('8', 'Fifty', 'trad_cook', '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91')
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE FUNCTION dbo.AveragePrice()
2> RETURNS money
3> WITH SCHEMABINDING
4> AS
5> BEGIN
6> RETURN (SELECT AVG(Price) FROM dbo.Titles)
7> END
8> GO
1>
2> CREATE FUNCTION dbo.PriceDifference(@Price money)
3> RETURNS money
4> AS
5> BEGIN
6> RETURN @Price - dbo.AveragePrice()
7> END
8> GO
1> SELECT Title,
2> Price,
3> dbo.AveragePrice() AS Average,
4> dbo.PriceDifference(Price) AS Difference
5> FROM Titles
6> WHERE Type='popular_comp'
7> GO
Title Price Average Difference
-------------------------------------------------------------------------------- --------------------- --------------------- ---------------------
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
Secrets 20.0000 15.8562 4.1438
(7 rows affected)
1>