3> CREATE TABLE authors(
4> au_id varchar(11),
5> au_lname varchar(40) NOT NULL,
6> au_fname varchar(20) NOT NULL,
7> phone char(12) NOT NULL DEFAULT ('UNKNOWN'),
8> address varchar(40) NULL,
9> city varchar(20) NULL,
10> state char(2) NULL,
11> zip char(5) NULL,
12> contract bit NOT NULL
13> )
14> GO
1> insert authors values('1', 'Joe', 'Abra', '111 111-1111', '6 St.', 'Berkeley', 'CA', '11111', 1)
2> insert authors values('2', 'Jack', 'Majo', '222 222-2222', '3 St.', 'Oakland' , 'CA', '22222', 1)
3> insert authors values('3', 'Pink', 'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4', 'Blue', 'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5', 'Red', 'Anne', '555 555-5555', '6 Av.', 'Regina', 'SK', '55555', 1)
6> insert authors values('6', 'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina', 'SK', '66666', 1)
7> insert authors values('7', 'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8', 'Yellow','Heather','888 888-8888', '3 Pu', 'Vacaville', 'CA', '88888', 0)
9> insert authors values('9', 'Gold', 'Dep', '999 999-9999', '5 Av.', 'Oakland', 'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean', '000 000-0000', '4 Av.', 'Oakland', 'CA', '00000', 1)
11> GO
1>
2>
3>
4> CREATE TABLE titles(
5> title_id varchar(20),
6> title varchar(80) NOT NULL,
7> type char(12) NOT NULL,
8> pub_id char(4) NULL,
9> price money NULL,
10> advance money NULL,
11> royalty int NULL,
12> ytd_sales int NULL,
13> notes varchar(200) NULL,
14> pubdate datetime NOT NULL
15> )
16> 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> CREATE TABLE titleauthor(
3> au_id varchar(20),
4> title_id varchar(20),
5> au_ord tinyint NULL,
6> royaltyper int NULL
7> )
8> GO
1>
2> insert titleauthor values('1', '2', 1, 60)
3> insert titleauthor values('2', '3', 1, 100)
4> insert titleauthor values('3', '4', 1, 100)
5> insert titleauthor values('4', '5', 1, 100)
6> insert titleauthor values('5', '6', 1, 100)
7> insert titleauthor values('6', '7', 2, 40)
8> insert titleauthor values('7', '8', 1, 100)
9> insert titleauthor values('8', '9', 1, 100)
10> GO
1>
2>
3> DECLARE @au_id char(11), @au_lname varchar(40), @title_id char(6),
4> @au_id2 char(11), @title_id2 char(6), @title varchar(80)
5>
6> DECLARE au_cursor CURSOR FOR
7> SELECT au_id, au_lname FROM authors ORDER BY au_id
8>
9> DECLARE au_titles CURSOR FOR
10> SELECT au_id, title_id FROM titleauthor ORDER BY au_id
11>
12> DECLARE titles_cursor CURSOR FOR
13> SELECT title_id, title FROM titles ORDER BY title
14>
15> OPEN au_cursor
16> FETCH NEXT FROM au_cursor INTO @au_id, @au_lname
17>
18> WHILE (@@FETCH_STATUS=0)
19> BEGIN
20> OPEN au_titles
21> FETCH NEXT FROM au_titles INTO @au_id2, @title_id
22>
23>
24> WHILE (@@FETCH_STATUS=0)
25> BEGIN
26>
27>
28> IF (@au_id=@au_id2)
29> BEGIN
30> OPEN titles_cursor
31> FETCH NEXT FROM titles_cursor INTO
32> @title_id2, @title
33>
34> WHILE (@@FETCH_STATUS=0)
35> BEGIN
36>
37> IF (@title_id=@title_id2)
38> SELECT @au_id, @au_lname, @title
39>
40> FETCH NEXT FROM titles_cursor INTO
41> @title_id2, @title
42> END
43> CLOSE titles_cursor
44> END
45> FETCH NEXT FROM au_titles INTO @au_id2, @title_id
46> END
47>
48> CLOSE au_titles
49> FETCH NEXT FROM au_cursor INTO @au_id, @au_lname
50> END
51> CLOSE au_cursor
52>
53> DEALLOCATE titles_cursor
54> DEALLOCATE au_titles
55> DEALLOCATE au_cursor
56> GO
----------- ---------------------------------------- --------------------------------------------------------------------------------
1 Joe The
----------- ---------------------------------------- --------------------------------------------------------------------------------
1 Joe The
----------- ---------------------------------------- --------------------------------------------------------------------------------
1 Joe The
----------- ---------------------------------------- --------------------------------------------------------------------------------
2 Jack Emotional
----------- ---------------------------------------- --------------------------------------------------------------------------------
2 Jack Emotional
----------- ---------------------------------------- --------------------------------------------------------------------------------
2 Jack Emotional
----------- ---------------------------------------- --------------------------------------------------------------------------------
3 Pink Prolonged
----------- ---------------------------------------- --------------------------------------------------------------------------------
3 Pink Prolonged
----------- ---------------------------------------- --------------------------------------------------------------------------------
3 Pink Prolonged
----------- ---------------------------------------- --------------------------------------------------------------------------------
4 Blue With
----------- ---------------------------------------- --------------------------------------------------------------------------------
4 Blue With
----------- ---------------------------------------- --------------------------------------------------------------------------------
4 Blue With
----------- ---------------------------------------- --------------------------------------------------------------------------------
5 Red Valley
----------- ---------------------------------------- --------------------------------------------------------------------------------
5 Red Valley
----------- ---------------------------------------- --------------------------------------------------------------------------------
5 Red Valley
----------- ---------------------------------------- --------------------------------------------------------------------------------
6 Black Any?
----------- ---------------------------------------- --------------------------------------------------------------------------------
6 Black Any?
----------- ---------------------------------------- --------------------------------------------------------------------------------
6 Black Any?
----------- ---------------------------------------- --------------------------------------------------------------------------------
7 White Fifty
----------- ---------------------------------------- --------------------------------------------------------------------------------
7 White Fifty
----------- ---------------------------------------- --------------------------------------------------------------------------------
7 White Fifty
1>