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 that received any rating below 5.
assistant
SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id = T2.i_id WHERE T2.rating < 5
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 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
Find the name and id of the item with the highest average rating.
assistant
SELECT T1.title , T1.i_id FROM item AS T1 JOIN review AS T2 ON T1.i_id = T2.i_id GROUP BY T2.i_id ORDER BY avg(T2.rating) DESC LIMIT 1
human
List the titles of all items in alphabetic order .
assistant
SELECT title FROM item ORDER BY title
human
List all information in the item table.
assistant
SELECT * FROM item ;