Question

For each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order. Use equal join for this query.

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));

Answer 1

Answer: select city,count(city) from customer c, ordertable o where c.cust_id = o.cust_id group by city order by city asc;

This query will display for each city, list number of customers from the city, who have placed order(s). Cities are listed in ascending alphabetical order