For each city, list number of customers from the city, who have placed order(s).

Question

 For each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order. Use IN and subquery.  CREATE TABLE customer (   cust_id  number(11,0)  not null,   cust_name  varchar2(25)  not null,   street  varchar2(30),   city  varchar2(20),   state  varchar2(2),   zipcode  varchar2(5), CONSTRAINT customer_pk PRIMARY KEY (cust_id) );  CREATE TABLE ordertable (   order_id  number(11,0)  not null,   order_date  date,   cust_id  number(11,0), CONSTRAINT order_pk PRIMARY KEY (order_id), CONSTRAINT order_fk FOREIGN KEY (cust_id) REFERENCES customer (cust_id));  CREATE TABLE product (   product_id  number(11,0)  not null,   product_name  varchar2(50),   product_price  number(6,2), CONSTRAINT product_pk PRIMARY KEY (product_id));  CREATE TABLE orderline (   order_id  number(11,0)  not null,   product_id  number(11,0)  not null,   quantity  number(11,0), CONSTRAINT orderline_pk PRIMARY KEY (order_id, product_id), CONSTRAINT orderline_fk1 FOREIGN KEY (order_id) REFERENCES ordertable (order_id), CONSTRAINT orderline_fk2 FOREIGN KEY (product_id) REFERENCES product (product_id)); 

Explanation / Answer

we can use COUNT, GROUPBY keywords to perform this action:

COUNT : returns the number of rows that matches a specified criteria.

GROUPBY : can be used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

-----------------------------------------------------------------------------------------------------------------------------

select count(*) from Customer as C, ordertable as O WHERE C.cust_id = O,order_fk GROUPBY C.city

---------------------------------------------------------------------------------------------------------------------------

Explanation :

We are first counting the customers those who have placed an order( C.cust_id = O.order_fk ). then we are grouping them by customer city ( C.city ) using GROUPBY keyword.

/* hope this helps */

/* if any questions, please comment*/

/* thannk you */

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.