5>
6>
7> CREATE TABLE titles(
8> title_id varchar(20),
9> title varchar(80) NOT NULL,
10> type char(12) NOT NULL,
11> pub_id char(4) NULL,
12> price money NULL,
13> advance money NULL,
14> royalty int NULL,
15> ytd_sales int NULL,
16> notes varchar(200) NULL,
17> pubdate datetime NOT NULL
18> )
19> 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>
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 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SET FORCEPLAN ON
4>
5>
6>
7> SELECT au_lname, title
8> FROM titles t
9> JOIN titleauthor ta ON ta.title_id = t.title_id
10> JOIN authors a ON a.au_id = ta.au_id
11> WHERE au_lname = "Green"
12> SET FORCEPLAN OFF
13> GO
au_lname title
---------------------------------------- --------------------------------------------------------------------------------
(0 rows affected)
1>
2>
3> drop table titles;
4> drop table titleauthor;
5> GO
1>
2>
3>