human
Schema:
item (i_id, title)
review (a_id, u_id, i_id, rating, rank)
useracct (u_id, name)
trust (source_u_id, target_u_id, trust)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the titles of items whose rating is higher than the average review rating of all items.
assistant
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id = T2.i_id WHERE T2.rating > (SELECT avg(rating) FROM review)
human
How many different items were reviewed by some users?
assistant
SELECT count(DISTINCT i_id) FROM review
human
Find the number of users who did not write any review.
assistant
SELECT count(*) FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)
human
Find the number of reviews.
assistant
SELECT count(*) FROM review
human
Find the names of the items that did not receive any review.
assistant
SELECT title FROM item WHERE i_id NOT IN (SELECT i_id FROM review)
human
Find the number of items without any review.
assistant
SELECT COUNT( * ) FROM item WHERE i_id NOT IN ( SELECT i_id FROM review ) ;