WJy1FJtc

1.headers on
2.mode columns
3
4DROP TABLE IF exists AuthorOf;
5DROP TABLE IF exists BOOK;
6DROP TABLE IF exists PUBLISHER;
7DROP TABLE IF exists CATEGORY;
8DROP TABLE IF exists Author;
9
10create table if not exists Author(
11	id int primary key,
12	name varchar(30)
13);
14
15create table if not exists Category(
16	id int primary key,
17	name varchar(30)
18);
19
20create table if not exists Publisher(
21	id int primary key,
22	name varchar(30)
23);
24
25create table if not exists Book(
26	id int primary key, 
27	title varchar(30),
28	categoryID int references Category(id),
29	pubID int references Publisher(id),
30	year int
31);
32
33create table if not exists AuthorOf(
34	authorID int references Author(id),
35	bookID int references Book(id)
36);
37
38INSERT INTO author values(1, "Yrsa");
39INSERT INTO author values(2, "Arnaldur");
40INSERT INTO author values(3, "Laddi");
41
42INSERT INTO category values(1, "Glæpasaga");
43INSERT INTO category values(2, "Gamansaga");
44
45INSERT INTO publisher values(1, "Bjartur");
46INSERT INTO publisher values(2, "Vaka Helgafell");
47
48INSERT INTO book values(1, "Mýrin", 1, 2, 2000);
49INSERT INTO book values(2, "Sogið", 1, 1, 2015);
50INSERT INTO book values(3, "Kuldi", 1, 1, 2012);
51INSERT INTO book values(4, "Röddin", 1, 1, 2002);
52Insert into book values(5, "Þýska Húsið", 1, 2, 2015);
53insert into book values(6, "Laddi 6-tugur", 2, 1, 2006);
54
55INSERT INTO AuthorOf values(2, 1);
56INSERT INTO AuthorOf values(1, 2);
57INSERT INTO AuthorOf values(1, 3);
58INSERT INTO AuthorOf values(2, 4);
59insert into AuthorOf values(2, 5);
60insert into authorof values(3, 6);
61
62
63
64
65
66
67
68
69
70
71
72
73Select book.title
74from book, authorof, author
75where book.id = AuthorOf.bookid
76	and author.id = AuthorOf.authorID
77	and author.name = 'Yrsa';
78	
79	
80	
81	
82	
83	
84	
85	
86	
87	
88	
89create view if not exists bjartur2015 as
90	select author.name as name
91	from author, authorof, book, publisher
92	where year = 2015
93	and book.id = authorof.bookID
94	and author.id = authorof.authorID
95	and publisher.name = "Bjartur"
96	and publisher.id = book.pubID;
97
98
99select count(name) from bjartur2015;
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115select distinct publisher.name 
116from book b1, book b2, authorof a1, authorof a2, publisher
117where a1.authorID = a2.authorID
118and a1.bookid = b1.id
119and a2.bookid = b2.id
120and b1.pubID = b2.pubID
121and a1.bookid <> a2.bookID
122and publisher.id = b1.pubID;
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141create view if not exists authorCat as
142	select category.name as category, author.name as author, count(*) as count
143	from category, author, book, authorof
144	where category.id = book.categoryID
145	and book.id = authorof.bookid
146	and author.id = authorof.authorID
147	group by category.name, author.name;
148
149
150
151select category, author, count
152from authorCat cat1
153where count >= (
154	select max(count)
155	from authorCat cat2
156	where cat1.category = cat2.category
157	);