im having some issues with inserting values into a table
CREATE TABLE COMPANY_AND_SUPPLIERS ( COMPANY_NAME VARCHAR (40) NOT NULL DEFAULT 'EMPTY', PRODUCT_NAME VARCHAR(40) NOT NULL DEFAULT 'EMPTY' , TOTAL_PRODUCTS VARCHAR(40) NOT NULL DEFAULT 'EMPTY', CONSTRAINT SUPPLIER_PKEY PRIMARY KEY(COMPANY_NAME) , CONSTRAINT SUPPLIER_FKEY FOREIGN KEY (COMPANY_NAME) REFERENCES SUPPLIER(COMPANY_NAME) ); INSERT INTO COMPANY_AND_SUPPLIERS (COMPANY_NAME) SELECT COMPANY_NAME FROM SUPPLIER; INSERT INTO COMPANY_AND_SUPPLIERS(PRODUCT_NAME) SELECT PRODUCT_NAME FROM PRODUCT; UPDATE COMPANY_AND_SUPPLIERS SET TOTAL_PRODUCTS = (SELECT COUNT(*) AS TOTALPRODUCTS FROM PRODUCT);
The given question is "Create a new relational table to store information about the company names of all suppliers and the total number of products supplied by each supplier. Enforce, the appropriate consistency constraints on the new table. Next, copy into the new table information about the company names of all suppliers and the total number of products supplied by each supplier. "
My understanding is that when Working The Query returns the number of products sold by each Company, Examle Company X sells 10 of Product Y. So far my qurery prints the 3 Columns but PRODUCT_NAME is 0 and TOTALPRODUCTS is 1. Im wondering what is wrong with my count? The expected results should be a total of 9 items equal to a quantity of 29.
Another issue i am also having is a foreign key constraint when inserting these values
INSERT INTO COMPANY_AND_SUPPLIERS(PRODUCT_NAME) SELECT PRODUCT_NAME FROM PRODUCT;
Below are the tables im using
CREATE TABLE SUPPLIER ( COMPANY_NAME VARCHAR(40) NOT NULL, CONTACT_NAME VARCHAR(30), CONTACT_TITLE VARCHAR(30), ADDRESS VARCHAR(60), CITY VARCHAR(15), REGION VARCHAR(15), POSTAL_CODE VARCHAR(10), COUNTRY VARCHAR(15), PHONE VARCHAR(24), FAX VARCHAR(24), HOME_PAGE VARCHAR(500), CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME) );
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), UNIT_PRICE DECIMAL(10,2) NOT NULL DEFAULT 0, UNITS_IN_STOCK DECIMAL(9) NOT NULL DEFAULT 0, UNITS_ON_ORDER DECIMAL(9) NOT NULL DEFAULT 0, REORDER_LEVEL DECIMAL(9) NOT NULL DEFAULT 0, DISCONTINUED CHAR(1) NOT NULL 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')) );
If anyone can help it would be much appreciated! :)