(SQL/ SQLite database question) Consider you have the following tables: books (i

Question

(SQL/ SQLite database question)

Consider you have the following tables:

books (id (integer), name (text), ranking (integer));

writers (book_id (integer), person_id (integer), person_name (text));

Note that books.id corresponds to writers.book_id and can be used to do a join

I would like to create a view

collaboration( writer_id1, writer_id2, book_count, average_ranking));

which contains pairs of writers who have collaborated on 2 or more books and only include those pairs who have achieved an average ranking on books collaborated on of greater than or equal to 100. I would like to remove redundant rows so that if there is a row (person1, person2, count, avg_rank) and a row (person2, person1, count, avg_rank) that it only lists the row where writer_id1's id is less than writer_id2's id.

How would I write this in sqlite? The code must include an INNER JOIN.

CREATE VIEW collaboration ...

Explanation / Answer

CREATE VIEW collaboration AS

SELECT p1 as writer_id1,p2 as writer_id2,COUNT(bid) as book_count,AVG(brank) as average_ranking FROM

(SELECT w1.person_id as p1,w2.person_id as p2,w1.book_id as bid, b.ranking as brank FROM writers w1,writers w2, books b WHERE w1.person_id < w2.person_id AND w1.book_id = w2.book_id AND w1.book_id = b.id)

GROUP BY p1,p2 HAVING COUNT(bid)>=2 AND AVG(brank)>=100;

Explaination:

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.