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
Find the titles of items that received both a rating higher than 8 and a 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 > 8 INTERSECT SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id = T2.i_id WHERE T2.rating < 5
human
List all information in the item table.
assistant
SELECT * FROM item
human
List the titles of all items in alphabetic order .
assistant
SELECT title FROM item ORDER BY title
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
Find the id of the item whose title is 'orange'.
assistant
SELECT i_id FROM item WHERE title = "orange" ;