SQL Code not working... Can someone why I keep getting errors putting data into
ID: 3707235 • Letter: S
Question
SQL Code not working... Can someone why I keep getting errors putting data into the payment and servc table?? Thank you.
CREATE TABLE STORE (
STORE_ID INTEGER PRIMARY KEY,
STORE_ADDRESS VARCHAR(35) NOT NULL,
STORE_CITY VARCHAR(35) NOT NULL,
STORE_STATE CHAR(2) NOT NULL,
STORE_ZIP CHAR(5) NOT NULL,
STORE_PHONE CHAR(10) NOT NULL);
--INSERT DATA INTO STORE TABLE
INSERT INTO STORE VALUES('101','34590 CLEVELAND DRIVE','OAKLAND','MI','48306','2488872340');
INSERT INTO STORE VALUES('102','23456 MAIN STREET','OAKLAND','MI','48084','2487123099');
INSERT INTO STORE VALUES('103','124 INDUSTRIAL DRIVE','TROY', 'MI','48083','2487661230');
INSERT INTO STORE VALUES('104','34190 VAN DYKE','UTICA', 'MI','48044','5867132255');
INSERT INTO STORE VALUES('105','555 KILMER DRIVE','OAKLAND','MI','48306','2480091221');
INSERT INTO STORE VALUES('106','48910 HILL DRIVE','TROY','MI','48083','2488546711');
INSERT INTO STORE VALUES('107','90 NICHOLAS ROAD','MACOMB','MI','48044','5869001200');
INSERT INTO STORE VALUES('108','7500 WILLIAM DRIVE','MACOMB','MI','48044','5868760123');
INSERT INTO STORE VALUES('109','8085 FREE DRIVE','DETROIT','MI','48183','3132226790');
INSERT INTO STORE VALUES('110','7845 PARK AVENUE','BLOOMFIELD HILLS','MI','48127','2480114488');
CREATE TABLE PRODUCT (
PRODUCT_ID INTEGER PRIMARY KEY,
PRODUCT_NAME VARCHAR(35) NOT NULL,
PRODUCT_TYPE VARCHAR(35) NOT NULL,
PRODUCT_COST VARCHAR(10) NOT NULL);
--INSERT DATA INTO PRODUCT
INSERT INTO PRODUCT VALUES('196','SHAMPOO','HAIR CARE','13.00');
INSERT INTO PRODUCT VALUES('842','NAIL POLISH','NAILS','7.00');
INSERT INTO PRODUCT VALUES('091','GUCCI COLOGNE','MENS FRAGRANCE','80.00');
INSERT INTO PRODUCT VALUES('856','SHAMPOO + CONDITIONER','HAIR CARE','17.00');
INSERT INTO PRODUCT VALUES('344','BODY LOTION','SKIN CARE','12.00');
CREATE TABLE BEAUTICIAN (
BEAUTICIAN_ID INTEGER PRIMARY KEY,
STORE_ID INTEGER NOT NULL,
BEAUTICIAN_FNAME VARCHAR(35) NOT NULL,
BEAUTUCAIN_LNAME VARCHAR(35) NOT NULL,
BEAUTICIAN_PHONE CHAR(10) NOT NULL,
BEAUTICIAN_ADDRESS VARCHAR(35) NOT NULL,
BEAUTICIAN_CITY VARCHAR(35) NOT NULL,
BEAUTICIAN_STATE CHAR(2) NOT NULL,
BEAUTICIAN_ZIP CHAR(5) NOT NULL,
BEAUTICIAN_HIRE_DATE DATE NOT NULL,
FOREIGN KEY (STORE_ID) REFERENCES STORE);
--INSERT DATA INTO BEAUTICIAN
INSERT INTO BEAUTICIAN VALUES('345','101','BETH','MARKS','2489009001','500 LILLY DRIVE','OAKLAND','MI','48306','10-OCT-2015');
INSERT INTO BEAUTICIAN VALUES('791','105','MARTHA','YORK','2483211934','900 HAVERFORD DRIVE','OAKLAND','MI','48306','25-JUN-2017');
INSERT INTO BEAUTICIAN VALUES('993','103','NATALIE','JONES','2489567123','1278 WATTLES','TROY','MI','48083','09-NOV-2012');
INSERT INTO BEAUTICIAN VALUES('779','104','MARK','FRITZ','5860983321','731 MAIN','UTICA','MI','48044','15-JAN-2014');
INSERT INTO BEAUTICIAN VALUES('297','109','BILL','MATZ','3135568431','900 WOODWARD','DETROIT','MI','48183','30-MAR-2017');
INSERT INTO BEAUTICIAN VALUES('588','102','PAM','EDDY','2487890123','670 MAIN','OAKLAND','MI','48306','12-JUN-2016');
INSERT INTO BEAUTICIAN VALUES('199','104','MICKEY','FRED','5868677770','1245 KITMER DRIVE','UTICA','MI','48044','17-JUL-2016');
INSERT INTO BEAUTICIAN VALUES('389','106','JIM','POLLARD','2483457721','2360 CROOKS','TROY','MI','48083','14-SEP-2014');
INSERT INTO BEAUTICIAN VALUES('885','107','KATIE','BOZA','5866661400','5690 FAIRWAY DRIVE','UTICA','MI','48044','01-MAR-2014');
INSERT INTO BEAUTICIAN VALUES('590','108','MICHAEL','BROWN','5866855831','1039 HAYES ROAD','WEST BLOOMFIELD','MI','48323','24-APR-2017');
INSERT INTO BEAUTICIAN VALUES('945','110','ASHTON','COOPER','2487651072','1340 LANGLEY DRIVE','DETROIT','MI','48127','19-AUG-2015');
CREATE TABLE CLIENT (
CLIENT_ID INTEGER PRIMARY KEY,
CLIENT_FNAME VARCHAR(15) NOT NULL,
CLIENT_LNAME VARCHAR(15) NOT NULL,
CLIENT_PHONE CHAR(10) NOT NULL,
CLIENT_ADDRESS VARCHAR(35) NOT NULL,
CLIENT_CITY VARCHAR(20) NOT NULL,
CLIENT_STATE CHAR(2) NOT NULL,
CLIENT_ZIP CHAR(5) NOT NULL,
CLIENT_TYPE VARCHAR(1) NOT NULL);
--INSERT DATA INTO CLIENT TABLE
INSERT INTO CLIENT VALUES('5425','Mark','Cuban','2488216615','5425 Pontiac Trl','West Bloomfield','MI','48323','M');
INSERT INTO CLIENT VALUES('5323','Michelle','Martinez','2483422997','4429 Amenia Dr','Bloomfield Hills','MI','48303','M');
INSERT INTO CLIENT VALUES('8849','Larry','Green','3132722555','8849 Marion','Redford','MI','48239','M');
INSERT INTO CLIENT VALUES('9120','Kimberly','Young','2486327743','536 Knight','Troy','MI','48007','M');
INSERT INTO CLIENT VALUES('7443','Pamela','Scott','3138023712','52342 Stratford','Macomb','MI','48042','M');
INSERT INTO CLIENT VALUES('1947','Elizabeth','Jones','3133849937','1101 N Lafeyette','Oakland','MI','48306','N');
INSERT INTO CLIENT VALUES('1017','Dawn','So','2483529934','732 Pioneer','Walled Lake','MI','48390','N');
INSERT INTO CLIENT VALUES('0932','Kevin','Robinson','3134685821','16044 Trinity St','Detroit','MI','48127','N');
INSERT INTO CLIENT VALUES('7888','Larry','Crawford','3138779002','410 Duck Ln','Walled Lake','MI','48392','N');
INSERT INTO CLIENT VALUES('3543','Jake','Evans','2482218940','643 Union Lake Rd','Commerce','MI','48382','N');
CREATE TABLE APPOINTMENT (
APPOINTMENT_ID INTEGER PRIMARY KEY,
BEAUTICIAN_ID INTEGER NOT NULL,
CLIENT_ID INTEGER NOT NULL,
APPOINTMENT_DATE DATETIME NOT NULL,
APPOINTMENT_TIME TIME NOT NULL,
FOREIGN KEY (BEAUTICIAN_ID) REFERENCES BEAUTICIAN,
FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT);
--INSERT DATA INTO APPOINTMENT TABLE
INSERT INTO APPOINTMENT VALUES('10','345','5425','11-JAN-2018','10:00AM');
INSERT INTO APPOINTMENT VALUES('20','791','5323','23-JAN-2018','2:15PM');
INSERT INTO APPOINTMENT VALUES('30','791','7443','23-JAN-2018','3:30PM');
INSERT INTO APPOINTMENT VALUES('40','779','0932','14-FEB-2018','11:45AM');
INSERT INTO APPOINTMENT VALUES('50','297','7888','29-MAR-2018','1:15PM');
CREATE TABLE MEMBER (
CLIENT_ID INTEGER PRIMARY KEY FOREIGN KEY REFERENCES CLIENT NOT NULL,
MEMBERSHIP_ID INTEGER,
PAID_DATE DATE NOT NULL,
RENEW_DATE DATE NOT NULL);
--INSERT CLIENTS INTO MEMBER TABLE
INSERT INTO MEMBER VALUES('5323','223','02-17-18','02-17-19');
INSERT INTO MEMBER VALUES('5425','674','06-25-18','06-25-19');
INSERT INTO MEMBER VALUES('8849','905','03-23-18','02-23-19');
INSERT INTO MEMBER VALUES('9120','774','03-02-18','03-02-19');
INSERT INTO MEMBER VALUES('7443','619','01-23-18','01-23-19');
CREATE TABLE NON_MEMBER (
CLIENT_ID INTEGER PRIMARY KEY FOREIGN KEY REFERENCES CLIENT NOT NULL);
--INSERT CLIENTS INTO NON_MEMBER TABLE
INSERT INTO NON_MEMBER VALUES('1947');
INSERT INTO NON_MEMBER VALUES('1017');
INSERT INTO NON_MEMBER VALUES('0932');
INSERT INTO NON_MEMBER VALUES('7888');
INSERT INTO NON_MEMBER VALUES('3543');
CREATE TABLE SALE (
SALE_ID INTEGER PRIMARY KEY NOT NULL,
CLIENT_ID INTEGER NOT NULL,
PRODUCT_ID INTEGER,
SERVICE_ID INTEGER,
MEMBER_DISCOUNT CHAR(1) NOT NULL,
SALE_DATE DATE NOT NULL,
SALE_TOTAL CHAR(5)
FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT);
--INSERT DATA INTO SALE TABLE
INSERT INTO SALE VALUES('1001','5425','196','162','Y','02/12/17','43');
INSERT INTO SALE VALUES('1004','3543','','162','N','01/23/17','30');
INSERT INTO SALE VALUES('1032','1017','856','160','N','03/04/17','62');
INSERT INTO SALE VALUES('1230','9120','091','','Y','01/26/17','80');
INSERT INTO SALE VALUES('1008','5323','344','164','N','05/19/17','27');
CREATE TABLE PROMOTION (
PROMO_ID INTEGER PRIMARY KEY,
CLIENT_ID INTEGER FOREIGN KEY REFERENCES CLIENT);
--INSERT DATA INTO PROMOTION TABLE
INSERT INTO PROMOTION VALUES('9848','5323');
INSERT INTO PROMOTION VALUES('1263','7743');
INSERT INTO PROMOTION VALUES('9923','9120');
INSERT INTO PROMOTION VALUES('4352','8849');
INSERT INTO PROMOTION VALUES('3211','1947');
CREATE TABLE CC_PAYMENT(
CC_NUMBER CHAR(16) PRIMARY KEY NOT NULL,
NAME VARCHAR (35) NOT NULL,
CCV_NUMBER NUMERIC(3) NOT NULL,
EXP_DATE CHAR(4) NOT NULL);
--INSERT DATA INTO CC PAYMENT TABLE
INSERT INTO CC_PAYMENT VALUES('3432883299283444','Kevin Robinson','343','0720');
INSERT INTO CC_PAYMENT VALUES('7834734862483247','Michelle Martinez','126','0720');
INSERT INTO CC_PAYMENT VALUES('6448853654565874','Jake Evans','420','0720');
INSERT INTO CC_PAYMENT VALUES('7555466924568951','Dawn So','699','0720');
INSERT INTO CC_PAYMENT VALUES('8394337834444347','Larry Green','732','0720');
CREATE TABLE CHK_PAYMENT(
CHK_NUMBER CHAR(4) PRIMARY KEY NOT NULL,
ACCOUNT_NUMBER CHAR(17) NOT NULL,
ROUTING_NUMBER CHAR(9) NOT NULL,
CHK_DATE DATE NOT NULL,
CHK_PAYEE_NAME VARCHAR(35) NOT NULL);
--INSERT DATA INTO CHECK PAYMENT TABLE
INSERT INTO CHK_PAYMENT VALUES('2436','32424324434','534667234','07-23-17','Larry Crawford');
INSERT INTO CHK_PAYMENT VALUES('1734','54634534324','943843723','06-17-17','Elizabeth Jones');
INSERT INTO CHK_PAYMENT VALUES('4366','74478535656','993424334','05-19-17','Kimberly Young');
INSERT INTO CHK_PAYMENT VALUES('9942','65437349234','876433234','02-23-17','Mark Cuban');
INSERT INTO CHK_PAYMENT VALUES('3022','13249387849','424332342','04-12-17','Pamela Scott');
CREATE TABLE PAYMENT (
SALE_ID INTEGER PRIMARY KEY NOT NULL,
CC_NUMBER CHAR(16) FOREIGN KEY REFERENCES CC_PAYMENT,
CHK_NUMBER CHAR(4) FOREIGN KEY REFERENCES CHK_PAYMENT);
--INSERT DATA INTO PAYMENT TABLE
INSERT INTO PAYMENT VALUES('43544','6448853654565874','');
INSERT INTO PAYMENT VALUES('34325','8394337834444347','');
INSERT INTO PAYMENT VALUES('76544','','1734');
INSERT INTO PAYMENT VALUES('94353','7555466924568951','2436');
INSERT INTO PAYMENT VALUES('53233','7834734862483247','');
CREATE TABLE SERVC (
SERVICE_ID INTEGER PRIMARY KEY NOT NULL,
SERVICE_TYPE VARCHAR(15) NOT NULL,
SERVICE_COST NUMERIC NOT NULL);
--INSERT DATA INTO SERVICE TABLE
INSERT INTO SERVC VALUES('160','Hair color change','45.00');
INSERT INTO SERVC VALUES('161','Hair blowout','16.00');
INSERT INTO SERVC VALUES('162','Haircut','30.00');
INSERT INTO SERVC VALUES('163','Trim','20.00');
INSERT INTO SERVC VALUES('164','Deep clean','17.00');
Explanation / Answer
--INSERT DATA INTO STORE TABLE----
Error because in STORE table we have STORE_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer
INSERT INTO STORE VALUES(101,'34590 CLEVELAND DRIVE','OAKLAND','MI','48306','2488872340');
--INSERT DATA INTO PRODUCT----
Error because in PRODUCT table we have PRODUCT_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer:
INSERT INTO PRODUCT VALUES(196,'SHAMPOO','HAIR CARE','13.00');
--INSERT DATA INTO BEAUTICIAN---
Error because in BEAUTICIAN table we have BEAUTICIAN_ID and STORE_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer:
INSERT INTO BEAUTICIAN VALUES(345,101,'BETH','MARKS','2489009001','500 LILLY DRIVE','OAKLAND','MI','48306','10-OCT-2015');
--INSERT DATA INTO CLIENT TABLE---
Error because in CLIENT table we have CLIENT_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer
INSERT INTO CLIENT VALUES(5425,'Mark','Cuban','2488216615','5425 Pontiac Trl','West Bloomfield','MI','48323','M');
--INSERT DATA INTO APPOINTMENT TABLE--
Error because in APPOINTMENT table we have APPOINTMENT_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer:
INSERT INTO APPOINTMENT VALUES(10,'345','5425','11-JAN-2018','10:00AM');
--INSERT CLIENTS INTO MEMBER TABLE--
Error because in MEMBER table we have CLIENT_ID and MEMBERSHIP_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer:
INSERT INTO MEMBER VALUES(5323,223,'02-17-18','02-17-19');
--INSERT CLIENTS INTO NON_MEMBER TABLE--
Error because in NON_MEMBER table we have CLIENT_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer:
INSERT INTO NON_MEMBER VALUES(1947);
--INSERT DATA INTO SALE TABLE--
Error because in SALE table we have SALE_ID,CLIENT_ID and PRODUCT_ID,SERVICE_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer:
INSERT INTO SALE VALUES(1001,5425,196,162,'Y','02/12/17','43');
--INSERT DATA INTO PROMOTION TABLE--
Error because in PROMOTION table we have PROMO_ID and CLIENT_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Answer
INSERT INTO PROMOTION VALUES(9848,5323);
--INSERT DATA INTO CC PAYMENT TABLE--
Error because in CC_PAYMENT table we have CCV_NUMBER as NUMARIC but your inserting using '' which is wrong for numbers you should not use ''
Correct Ans:
INSERT INTO CC_PAYMENT VALUES('3432883299283444','Kevin Robinson',343,'0720');
--INSERT DATA INTO PAYMENT TABLE--
Error because in MEMBER table we have SALE_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Ans:
INSERT INTO PAYMENT VALUES(43544,'6448853654565874','');
--INSERT DATA INTO SERVICE TABLE--
Error because in SERVICE table we have SERVICE_ID as Integer but your inserting using '' which is wrong for numbers you should not use ''
Correct Ans:
INSERT INTO SERVC VALUES(160,'Hair color change','45.00');
Note: If the type is int,decimal, number, numaric than we should not use '' while inerting that is the only issue I found in the statement, feel free comment here to discuss further
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.