Question

I can't imagine how much more info you require to answer this. It is MySQL if that helps but even that info is here. If you don't know how to answer, then don't. There is more information here than anyone should need to answer the questions.

The structure and contents of the Ch08_SimpleCo database are shown in Figure P8.16. Use this database to answer the following problems. Database Schema The schema for the Ch08_SimpleCo database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab.

Instructions

Given the structure of the Ch08_SimpleCo database shown above, use SQL commands to answer the problems in the following steps.

Write your SQL statement in the editor on the right, then click the Run Query button to execute your statement in the interactive MySQL shell.

Problem 18

Write the set of SQL commands necessary to insert the data into the CUSTOMER table you created in Problem 16, as illustrated in Figure P8.16.

My code: INSERT INTO CUSTOMER VALUES(1,"bhavana","wali",12345678.45);

My error: ERROR 1062 (23000) at line 1: Duplicate entry '1' for key 'PRIMARY'

18.a

Write the set of SQL commands necessary to insert the data into the INVOICE table you created in Problem 17, as illustrated in Figure P8.16.

Use YYYY-MM-DD format when inserting dates.

my code: INSERT INTO INVOICE VALUES(11,'2015/12/1',12312388.12,1);

my error: ERROR 1062 (23000) at line 1: Duplicate entry '11' for key 'PRIMARY'

18.b

Using MySQL, populate the CUST_LNAME, CUST_FNAME, and CUST_BALANCE fields with the following customers:

CUST_LNAME	CUST_FNAME	CUST_BALANCE
Smith	Jeanne	1050.11
Ortega	Juan	840.92

my code:

INSERT INTO CUST_MYSQL(CUST_LNAME,CUST_FNAME,CUST_BALANCE) VALUES ("Smith","Jeanne",1050.11);
INSERT INTO CUST_MYSQL(CUST_LNAME,CUST_FNAME,CUST_BALANCE) VALUES ("Ortega","Juan",840.920);

SELECT * FROM CUSTOMER;
SELECT * FROM INVOICE;
SELECT * FROM CUST_MYSQL;

My results:

CUST_NUM	CUST_FNAME	CUST_LNAME	CUST_BALANCE
1	bhavana	wali	12345678.45
INV_NUM	INV_DATE	INV_AMOUNT	CUST_NUM
11	2015-12-01	12312388.12	1
CUST_NUM	CUST_FNAME	CUST_LNAME	CUST_BALANCE	CUST_DOB
2000	Smith	Jeanne	1050.11	NULL
2001	Ortega	Juan	840.92	NULL
2002	Jeanne	Smith	1050.11	NULL
2003	Juan	Ortega	840.92	NULL

the results I need:

SELECT * FROM CUST_MYSQL

Expected Results

CUST_NUM	CUST_LNAME	CUST_FNAME	CUST_BALANCE
2000	Smith	Jeanne	1050.11
2001	Ortega	Juan	840.92

18.c

Populate the CUST_NUM, INV_DATE, and INV_AMOUNT fields with the following customers:

CUST_NUM	INV_DATE	INV_AMOUNT
1000	2016-03-23	235.89
1001	2016-03-23	312.82
1001	2016-03-30	528.10
1000	2016-04-12	194.78
1000	2016-04-23	619.44

my code:

insert into INVOICE(CUST_NUM , INV_DATE, INV_AMOUNT)
values(1000,'2016-03-23', 235.89), (1001, '2016-03-23', 312.82),
(1001, '2016-03-30', 528.10), (1000, '2016-04-12', 194.78), (1000, '2016-04-23', 619.44);

select * from INVOICE;

My error:

ERROR 1364 (HY000) at line 1: Field 'INV_NUM' doesn't have a default value

18.d

Insert the following customer into the CUST_MYSQL table, allowing the AUTO_INCREMENT attribute set up in Problem 20.a and Problem 20.b to generate the customer number automatically:

CUST_LNAME	CUST_FNAME	CUST_BALANCE
Powers	Ruth	500

my code:

insert into CUSTOMER(CUST_LNAME, CUST_FNAME, CUST_BALANCE)
values('Powers', 'Ruth', 500);

select * from CUSTOMER;

my error:

ERROR 1364 (HY000) at line 1: Field 'CUST_NUM' doesn't have a default value

18.e

Modify the CUSTOMER table to include the customer’s date of birth (CUST_DOB), which should store date data

my code:

insert into CUSTOMER(CUST_LNAME, CUST_FNAME, CUST_BALANCE)
values('Powers', 'Ruth', 500);

select * from CUSTOMER;

my error:

ERROR 1364 (HY000) at line 1: Field 'CUST_NUM' doesn't have a default value

18.f

Modify customer 1000 to indicate the date of birth on March 15, 1989.

Use YYYY-MM-DD format for inserting dates.

my code:

UPDATE CUST_MYSQL SET CUST_DOB='1989-03-15' WHERE CUST_NUM=1000;

my results state "NO DATA"

ERROR 1054 (42S22) at line 1: Unknown column 'CUST_DOB' in 'field list'

The wrong headers were returned.

Some expected rows were missing (shown in red below).

Test Query

SELECT CUST_NUM, CUST_DOB FROM CUSTOMER WHERE CUST_NUM = 1000

Expected Results

CUST_NUM	CUST_DOB
1000	1989-03-15

18.g

Problem 25

Modify customer 1001 to indicate the date of birth on December 22, 1988.

Use YYYY-MM-DD format for inserting dates.

my code:

ALTER TABLE CUSTOMER ADD(DOB DATE);
UPDATE CUSTOMER SET DOB='1988-12-22' WHERE CUST_NUM=1001;
Select * from Customer;

My error:

ERROR 1146 (42S02) at line 3: Table 'Ch08_SimpleCo.Customer' doesn't exist

Help! Talk to me like I'm really stupid. Thank you.

Answer 1

Problem 18 In the CUSTOMER table, CUST_NUM is primary key. Error is coming as one record is already present with the Primary key 1

18.a In the INVOICE table, INV_NUM is primary key. Error is coming as one record is already present with the primary key 11

18.b In the CUST_MYSQL table, there are four records present. If you want to see only first two records and the desired columns then you can use the limit clause. Also, the query should be like

select CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE from CUST_MYSQL limit 2

18.c In the Invoice table, you are only passing values for CUST_NUM , INV_DATE, INV_AMOUNT columns. But INV_NUM is the primary key and it is not set to auto increment so you can not miss the value. So, in order to avoid this error you have to pass value for INV_NUM also.

18.d, 18.e In the CUSTOMER table, as CUST_NUM is the primary key. You have to pass the value for primary key. Because primary key can not be null or duplicate.

18.f Reason for the error is that you might not have added the column CUST_DOB in to the CUST_MYSQL table. First alter the table add the column CUST_DOB and then try the same query, It will work fine.

18.g Correct syntax for adding a new column in mysql is
ALTER TABLE table_name
ADD column_name datatype;

So, if you follow this syntax, new column will be added and you will not see errors.