CODEWITHSUNDEEP
sqloracle-database
bean
bean

Reputation: 337

Using SQL to find the total number of times a customer has ordered a product

I need to modify the structure of the sample database such that after the modifications it is possible to store information about the total number of times each product has been ordered by the customers.

Please note that some products may have not been ordered at all. It is important to find the best design. I need to enforce appropriate consistency constraints.

After this, the script saves in the sample database information about the total number of times each product has been ordered by the customers.

I am using oracle SQL developer.

Here is what I have tried so far:

CREATE TABLE TOTAL_NUM(
CUSTOMER_CODE   VARCHAR(30) NOT NULL, 
PRODUCT_NAME    VARCHAR(30) NOT NULL,
TOTAL_ORDERED   DECIMAL(2)  NOT NULL,
CONSTRAINT TOTALO_PKEY1 PRIMARY KEY(CUSTOMER_CODE),
CONSTRAINT TOTALO_FKEY1 FOREIGN KEY(CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),
CONSTRAINT TOTALO_UNIQUE_NAME UNIQUE(PRODUCT_NAME), 
CONSTRAINT TOTALO_FKEY2 FOREIGN KEY(PRODUCT_NAME) REFERENCES PRODUCT(PRODUCT_NAME),
CONSTRAINT TOTALO_CHECK CHECK(TOTAL_ORDERED > 0));

INSERT INTO TOTAL_NUM(
SELECT ORDERS.CUSTOMER_CODE, COUNT(*) AS TOTAL_ORDERED
FROM ORDERS
LEFT JOIN ORDER_DETAIL ON ORDERS.ORDER_ID = ORDER_DETAIL.ORDER_ID);

I have created a seperate table to store this information, however when I try to insert the values into the table, I get an error "not enough values".

Here are the other tables in the database:

CREATE TABLE CATEGORY
(
CATEGORY_NAME   VARCHAR(30) NOT NULL,
DESCRIPTION     VARCHAR(2000)   NOT NULL,
PICTURE         VARCHAR(255)    NOT NULL,
CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORY_NAME)
);

CREATE TABLE CUSTOMER
(
CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
COMPANY_NAME    VARCHAR(40) NOT NULL,
CONTACT_NAME    VARCHAR(30)     NOT NULL,      
CONTACT_TITLE   VARCHAR(30)     NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)         NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
PHONE       VARCHAR(24)     NOT NULL,
FAX         VARCHAR(24)         NULL,
CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_CODE)
);
        
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID     NUMBER(9)   NOT NULL,
LASTNAME        VARCHAR(20)     NOT NULL,
FIRSTNAME       VARCHAR(10)     NOT NULL,
TITLE       VARCHAR(30)     NOT NULL,
TITLE_OF_COURTESY   VARCHAR(25)     NOT NULL,
BIRTHDATE       DATE            NOT NULL,
HIREDATE        DATE            NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)     NOT NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
HOME_PHONE      VARCHAR(24)     NOT NULL,
EXTENSION       VARCHAR(4)      NOT NULL,
PHOTO       VARCHAR(255)    NOT NULL,
NOTES       VARCHAR(2000)   NOT NULL,
REPORTS_TO      NUMBER(9)           NULL,
CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
);

CREATE TABLE SUPPLIER
(
COMPANY_NAME    VARCHAR(40) NOT NULL,
CONTACT_NAME    VARCHAR(30)     NOT NULL,
CONTACT_TITLE   VARCHAR(30)     NOT NULL,
ADDRESS         VARCHAR(60)     NOT NULL,
CITY        VARCHAR(15)     NOT NULL,
REGION      VARCHAR(15)         NULL,
POSTAL_CODE     VARCHAR(10)     NOT NULL,
COUNTRY         VARCHAR(15)     NOT NULL,
PHONE       VARCHAR(24)     NOT NULL,
FAX         VARCHAR(24)         NULL,
HOME_PAGE       VARCHAR(500)        NULL,
CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)  
);

CREATE TABLE SHIPPER
(
COMPANY_NAME    VARCHAR(40) NOT NULL,
PHONE       VARCHAR(24)         NULL,
CONSTRAINT PK_SHIPPER PRIMARY KEY (COMPANY_NAME),
CONSTRAINT CK_SHIPPER UNIQUE (PHONE)
);

CREATE TABLE PRODUCT
(
PRODUCT_NAME    VARCHAR(40)     NOT NULL,
SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
CATEGORY_NAME   VARCHAR(30) NOT NULL,
QUANTITY_PER_UNIT   VARCHAR(20)         NULL,
UNIT_PRICE      NUMBER(10,2)    DEFAULT 0,
UNITS_IN_STOCK  NUMBER(9)   DEFAULT 0,
UNITS_ON_ORDER  NUMBER(9)   DEFAULT 0,
REORDER_LEVEL   NUMBER(9)   DEFAULT 0,
DISCONTINUED    CHAR(1)     DEFAULT 'N',
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

CREATE TABLE ORDERS
(
ORDER_ID        NUMBER(9)   NOT NULL,
CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
EMPLOYEE_ID     NUMBER(9)   NOT NULL,
ORDER_DATE      DATE        NOT NULL,
REQUIRED_DATE   DATE            NOT NULL,
SHIPPED_DATE    DATE            NOT NULL,
SHIP_VIA        VARCHAR(40)     NOT NULL,
FREIGHT         NUMBER(10,2)    DEFAULT 0,
SHIP_NAME       VARCHAR(40)     NOT NULL,
SHIP_ADDRESS    VARCHAR(60)     NOT NULL,
SHIP_CITY       VARCHAR(15)     NOT NULL,
SHIP_REGION     VARCHAR(15)         NULL,
SHIP_POSTAL_CODE    VARCHAR(10)         NULL,
SHIP_COUNTRY    VARCHAR(15)     NOT NULL,
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),  
CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  
CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)  
);

CREATE TABLE ORDER_DETAIL
(
ORDER_ID         NUMBER(9)  NOT NULL,
PRODUCT_NAME         VARCHAR(40)    NOT NULL,
UNIT_PRICE       NUMBER(10,2)   DEFAULT 0.0,
QUANTITY         NUMBER(9)  DEFAULT 1,
DISCOUNT         NUMBER(4,2)    DEFAULT 0.0,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_ID, PRODUCT_NAME),
CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
CONSTRAINT FK_PRODUCT_NAME FOREIGN KEY (PRODUCT_NAME) REFERENCES PRODUCT (PRODUCT_NAME),
CONSTRAINT CK_ORDER_DETAIL_UNIT_PRICE CHECK (UNIT_PRICE >= 0), 
CONSTRAINT CK_ORDER_DETAIL_QUANTITY CHECK (QUANTITY > 0),
CONSTRAINT CK_ORDER_DETAIL_DISCOUNT CHECK (DISCOUNT between 0 and 1) 
);

Upvotes: 0

Views: 296

Answers (1)

Barmar
Barmar

Reputation: 781096

You're only inserting two columns into TOTAL_NUM, but it has three columns. You skipped PRODUCT_NAME. You need to use GROUP BY, otherwise it will aggregate all orders into a single row to insert.

INSERT INTO TOTAL_NUM (CUSTOMER_CODE, PRODUCT_NAME, TOTAL_ORDERED)
SELECT ORDERS.CUSTOMER_CODE, ORDER_DETAIL.PRODUCT_NAME, COUNT(*) AS TOTAL_ORDERED
FROM ORDERS
INNER JOIN ORDER_DETAIL ON ORDERS.ORDER_ID = ORDER_DETAIL.ORDER_ID
GROUP BY ORDERS.CUSTOMER_CODE ORDER_DETAIL.PRODUCT_NAME

You also should probably use INNER JOIN rather than LEFT JOIN. Otherwise you'll create a row with a null PRODUCT_NAME for any orders with no products.

Upvotes: 1

Related Questions