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
