2> CREATE TABLE titles(
3> title_id varchar(20),
4> title varchar(80) NOT NULL,
5> type char(12) NOT NULL,
6> pub_id char(4) NULL,
7> price money NULL,
8> advance money NULL,
9> royalty int NULL,
10> ytd_sales int NULL,
11> notes varchar(200) NULL,
12> pubdate datetime NOT NULL
13> )
14> 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>
2>
3> CREATE VIEW ranked_sales (rank, title_id, ytd_sales, title)
4> AS
5> SELECT
6> (SELECT COUNT(DISTINCT T2.ytd_sales) FROM titles AS T2
7> WHERE T2.ytd_sales >= T1.ytd_sales ) AS rank,
8> title_id,
9> ytd_sales,
10> title
11> FROM titles AS T1 WHERE ytd_sales IS NOT NULL
12> GO
1>
2> SELECT * FROM ranked_sales WHERE rank <= 10 ORDER BY rank
3> GO
rank title_id ytd_sales title
----------- -------------------- ----------- --------------------------------------------------------------------------------
1 1 4095 Secrets
1 2 4095 The
1 7 4095 Any?
2 4 4072 Prolonged
3 5 3876 With
4 3 3336 Emotional
5 6 2032 Valley
6 8 1509 Fifty
1>
2> drop VIEW ranked_sales;
3> GO
1>
2>
3> drop table titles;
4> GO