Question

You’ve been hired to work on a web site that maintains customer reviews of products. The main data is stored in the following tables: Product(Product ID, Product Name, Description) Reviewer(Reviewer ID, Reviewer Name, City) Review(Reviewer ID, Product ID, Rating, Comment) The tables contain the following information:
Product: unique product id (Product ID), product name (Product Name), and product description. All strings. • Reviewer: unique reviewer id (Reviewer ID), and reviewer name (Reviewer Name) and city, also all strings. • Review: One entry for each individual review giving the reviewer and product ids, an integer rating in the range 0‐5, and the reviewer comment, which is a string.
A) Write a SQL query that returns the number of reviewers in each distinct city. The results should list the city name and the number of reviewers in that city, and should be sorted alphabetically by city name.
B) Write a SQL query that returns the average of the reviews for each reviewer and names of the reviewers for all reviewers that have an average review (of all their reviews) of less than or equal to 2.

Answer 1

Answer:

See explaination

Explanation:

a.

//to create product table

CREATE TABLE PRODUCT

(

PRODUCTID VARCHAR2(50) NOT NULL

, PRODUCTNAME VARCHAR2(50) NOT NULL

, DESCRIPTION VARCHAR2(50) NOT NULL

, CONSTRAINT PRODUCT_PK PRIMARY KEY

(

PRODUCTID

)

);

//to create Reviewer table

CREATE TABLE REVIEWER

(

REVIEWERID VARCHAR2(50) NOT NULL ,

REVIEWERNAME VARCHAR2(50) NOT NULL ,

CITY VARCHAR2(50) NOT NULL ,

CONSTRAINT REVIEWER_PK PRIMARY KEY ( REVIEWERID )

);

// to create Review table

CREATE TABLE REVIEW

(

REVIEWERID VARCHAR2(50) NOT NULL ,

PRODUCTID VARCHAR2(50) NOT NULL ,

RATING NUMBER(5, 0) NOT NULL ,

COMMENTS VARCHAR2(50) NOT NULL ,

CONSTRAINT REVIEW_PK PRIMARY KEY ( REVIEWERID , PRODUCTID )

);

b.

SELECT CITY,COUNT( REVIEWERID) NOOFREVIEWERS FROM REVIEWER GROUP BY CITY ORDER BY CITY ;

EXPLANATION

GROUP BY clause classifies the data in the table it avoids duplicates in the table

ORDER BY clause by default sort the table in ascending order

in the select clause city is the single column count is group function so we must need to write group by clause

otherwise we will get

SQL Error: ORA-00937: not a single-group group function