2>
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 rows affected)
(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 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> CREATE TABLE sales(
3> stor_id char(4) NOT NULL,
4> ord_num varchar(20) NOT NULL,
5> ord_date datetime NOT NULL,
6> qty smallint NOT NULL,
7> payterms varchar(12) NOT NULL,
8> title_id varchar(80)
9> )
10> GO
1> insert sales values('1', 'QA7442.3', '09/13/94', 75, 'ON Billing','1')
2> insert sales values('2', 'D4482', '09/14/94', 10, 'Net 60', '1')
3> insert sales values('3', 'N914008', '09/14/94', 20, 'Net 30', '2')
4> insert sales values('4', 'N914014', '09/14/94', 25, 'Net 30', '3')
5> insert sales values('5', '423LL922', '09/14/94', 15, 'ON Billing','3')
6> insert sales values('6', '423LL930', '09/14/94', 10, 'ON Billing','2')
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3>
4> --CREATE our view
5> CREATE VIEW vSalesCount
6> AS
7> SELECT au.au_id,
8> au.au_lname + ', ' + au.au_fname AS au_name, au.address,
9> au.city + ', ' + au.state + ' ' + zip AS address2,
10> SUM(s.qty) As SalesCount
11> FROM authors au
12> JOIN titleauthor ta
13> ON au.au_id = ta.au_id
14> JOIN sales s
15> ON ta.title_id = s.title_id
16> GROUP BY au.au_id,
17> au.au_lname + ', ' + au.au_fname,
18> au.address,
19> au.city + ', ' + au.state + ' ' + zip
20> GO
1>
2>
3> SELECT au_name, address, Address2 FROM vSalesCount
4> WHERE SalesCount > 25
5> GO
au_name address Address2
-------------------------------------------------------------- ---------------------------------------- ------------------------------
Joe, Abra 6 St. Berkeley, CA 11111
Jack, Majo 3 St. Oakland, CA 22222
(2 rows affected)
1>
2> drop view vSalesCount;
3> GO
1>
2> drop table authors;
3> GO
1> drop table titleauthor;
2> drop table sales;
3> GO
1>
2>