|
아래와 같은 3개의 테이블이 있을 경우...
(물론 이외에도 더 많은 테이블이 있습니다.)
""""제품별 주문량이 언제 몇개인지 주문일자 순으로(ASC)...""""
를 group by 와 order by 에 의해 수행하려 합니다.
근데 자꾸 에러가 납니다.
SQL> select product.id, product.name, item.quantity, ord.date_ordered from s_ord ord, s_product prod
uct, s_item item group by product.id order by ord.date_ordered;
select product.id, product.name, item.quantity, ord.date_ordered from s_ord ord, s_product product,
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL>
// S_ORD
CREATE TABLE s_ord
(
id NUMBER(7)
CONSTRAINT s_ord_id_nn NOT NULL,
customer_id NUMBER(7)
CONSTRAINT s_ord_customer_id_nn NOT NULL,
date_ordered DATE,
date_shipped DATE,
sales_rep_id NUMBER(7),
total NUMBER(11, 2),
payment_type VARCHAR2(6),
order_filled VARCHAR2(1),
CONSTRAINT s_ord_id_pk PRIMARY KEY (id),
CONSTRAINT s_ord_payment_type_ck
CHECK (payment_type in ('CASH', 'CREDIT')),
CONSTRAINT s_ord_order_filled_ck
CHECK (order_filled in ('Y', 'N'))
)
;
ALTER TABLE s_ord
ADD CONSTRAINT s_ord_customer_id_fk
FOREIGN KEY (customer_id) REFERENCES s_customer (id);
ALTER TABLE s_ord
ADD CONSTRAINT s_ord_sales_rep_id_fk
FOREIGN KEY (sales_rep_id) REFERENCES s_emp (id);
// S_PRODUCT
CREATE TABLE s_product
(
id NUMBER(7)
CONSTRAINT s_product_id_nn NOT NULL,
name VARCHAR2(50)
CONSTRAINT s_product_name_nn NOT NULL,
short_desc VARCHAR2(255),
longtext_id NUMBER(7),
image_id NUMBER(7),
suggested_whlsl_price NUMBER(11, 2),
whlsl_units VARCHAR2(25),
CONSTRAINT s_product_id_pk PRIMARY KEY (id),
CONSTRAINT s_product_name_uk UNIQUE (name)
)
;
ALTER TABLE s_product
ADD CONSTRAINT s_product_image_id_fk
FOREIGN KEY (image_id) REFERENCES s_image (id);
ALTER TABLE s_product
ADD CONSTRAINT s_product_longtext_id_fk
FOREIGN KEY (longtext_id) REFERENCES s_longtext (id);
// S_ITEM
CREATE TABLE s_item
(
ord_id NUMBER(7)
CONSTRAINT s_item_ord_id_nn NOT NULL,
item_id NUMBER(7)
CONSTRAINT s_item_item_id_nn NOT NULL,
product_id NUMBER(7)
CONSTRAINT s_item_product_id_nn NOT NULL,
price NUMBER(11, 2),
quantity NUMBER(9),
quantity_shipped NUMBER(9),
CONSTRAINT s_item_ordid_itemid_pk PRIMARY KEY (ord_id, item_id),
CONSTRAINT s_item_ordid_prodid_uk UNIQUE (ord_id, product_id)
)
;
ALTER TABLE s_item
ADD CONSTRAINT s_item_ord_id_fk
FOREIGN KEY (ord_id) REFERENCES s_ord (id);
ALTER TABLE s_item
ADD CONSTRAINT s_item_product_id_fk
FOREIGN KEY (product_id) REFERENCES s_product (id);
|
