CREATE TABLE item (
    i_id NUMBER PRIMARY KEY,
    title TEXT
);
CREATE TABLE review (
    a_id NUMBER PRIMARY KEY,
    u_id NUMBER,
    i_id NUMBER,
    rating NUMBER,
    rank NUMBER,
    FOREIGN KEY ("i_id") REFERENCES item("i_id"),
    FOREIGN KEY ("u_id") REFERENCES useracct("u_id")
);
CREATE TABLE useracct (
    u_id NUMBER PRIMARY KEY,
    name TEXT
);
CREATE TABLE trust (
    source_u_id NUMBER,
    target_u_id NUMBER,
    trust NUMBER,
    FOREIGN KEY ("target_u_id") REFERENCES useracct("u_id"),
    FOREIGN KEY ("source_u_id") REFERENCES useracct("u_id")
);

Question: Find the id of the item whose title is "orange".
SQL: SELECT i_id FROM item WHERE title  =  "orange"

Question: List all information in the item table.
SQL: SELECT * FROM item

Question: Find the number of reviews.
SQL: SELECT count(*) FROM review

Question: How many users are there?
SQL: SELECT count(*) FROM useracct

Question: Find the average and maximum rating of all reviews.
SQL: SELECT avg(rating) ,  max(rating) FROM review

Question: Find the highest rank of all reviews.
SQL: SELECT min(rank) FROM review

Question: How many different users wrote some reviews?
SQL: SELECT count(DISTINCT u_id) FROM review

Question: How many different items were reviewed by some users?
SQL: SELECT count(DISTINCT i_id) FROM review

Question: Find the number of items that did not receive any review.
SQL: SELECT count(*) FROM item WHERE i_id NOT IN (SELECT i_id FROM review)

Question: Find the names of users who did not leave any review.
SQL: SELECT name FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)

Question: Find the names of goods that receive a rating of 10.
SQL: SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  =  10

Question: Find the titles of items whose rating is higher than the average review rating of all items.
SQL: 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)

Question: Find the titles of items that received any rating below 5.
SQL: SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rating  <  5

Question: Find the titles of items that received both a rating higher than 8 and a rating below 5.
SQL: 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

Question: Find the names of items whose rank is higher than 3 and whose average rating is above 5.
SQL: SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id WHERE T2.rank  >  3 INTERSECT SELECT T1.title FROM item AS T1 JOIN review AS T2 ON T1.i_id  =  T2.i_id GROUP BY T2.i_id HAVING avg(T2.rating)  >  5

Question: Find the name of the item with the lowest average rating.
SQL: SELECT T1.title 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) LIMIT 1

Question: List the titles of all items in alphabetic order .
SQL: SELECT title FROM item ORDER BY title

Question: Find the name of the user who gives the most reviews.
SQL: SELECT T1.name FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id GROUP BY T2.u_id ORDER BY count(*) DESC LIMIT 1

Question: Find the name and id of the item with the highest average rating.
SQL: 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

Question: Find the name and id of the good with the highest average rank.
SQL: 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.rank) DESC LIMIT 1

Question: For each user, return the name and the average rating of reviews given by them.
SQL: SELECT T1.name ,  avg(T2.rating) FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id GROUP BY T2.u_id

Question: For each user, find their name and the number of reviews written by them.
SQL: SELECT T1.name ,  count(*) FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id GROUP BY T2.u_id

Question: Find the name of the user who gave the highest rating.
SQL: SELECT T1.name FROM useracct AS T1 JOIN review AS T2 ON T1.u_id  =  T2.u_id ORDER BY T2.rating DESC LIMIT 1

Question: Find the name of the source user with the highest average trust score.
SQL: SELECT T1.name FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id  =  T2.source_u_id GROUP BY T2.source_u_id ORDER BY avg(trust) DESC LIMIT 1

Question: Find each target user's name and average trust score.
SQL: SELECT T1.name ,  avg(trust) FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id  =  T2.target_u_id GROUP BY T2.target_u_id

Question: Find the name of the target user with the lowest trust score.
SQL: SELECT T1.name FROM useracct AS T1 JOIN trust AS T2 ON T1.u_id  =  T2.target_u_id ORDER BY trust LIMIT 1

Question: Find the names of the items that did not receive any review.
SQL: SELECT title FROM item WHERE i_id NOT IN (SELECT i_id FROM review)

Question: Find the names of users who did not leave any review.
SQL: SELECT name FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)

Question: Find the number of users who did not write any review.
SQL: SELECT count(*) FROM useracct WHERE u_id NOT IN (SELECT u_id FROM review)

Question: Find the number of items without any review.
SQL: SELECT count(*) FROM item WHERE i_id NOT IN (SELECT i_id FROM review)