How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Scott Your Own Question
Scott
Scott, MIT Graduate
Category: Homework
Satisfied Customers: 3040
Experience:  MIT Graduate (Math, Programming, Science, and Music)
3546829
Type Your Homework Question Here...
Scott is online now
A new question is answered every 9 seconds

I created these tables using the following:CREATE TABLE

Customer Question

I created these tables using the following:
CREATE TABLE Resort_type(
resort_type_id DECIMAL(12)PRIMARY KEY,
resort_type VARCHAR(64)NOT NULL
);
CREATE TABLE Resort(
resort_id DECIMAL(12)PRIMARY KEY,
name VARCHAR(12)NOT NULL,
resort_type_id DECIMAL(12)NOT NULL
);
ALTER TABLE Resort
ADD CONSTRAINT resort_fk
FOREIGN KEY(resort_type_id)
REFERENCES Resort_type(resort_type_id);
CREATE TABLE Accommodations(
accommodations_id DECIMAL(12)PRIMARY KEY,
description VARCHAR(255)NOT NULL,
cost_per_night DECIMAL(12),
resort_id DECIMAL(12)NOT NULL
);
ALTER TABLE Accommodations
ADD CONSTRAINT resort_id_fk
FOREIGN KEY(resort_id)
REFERENCES Resort(resort_id);
The problems start when the instructor said that we could assign our own primary key values and asked me to insert the following values:
into tale Resort_type values:
Ocean
Lakeside
Mountaintop
Country
I made up the resort_type_id for each of the above
111 for Ocean
222 for Lakeside
333 for Mountaintop
444 for Country
into table Resort:
name and resort_type:
Light of the Ocean with a resort_type of Ocean
Breathtaking Bahamas with a resort_type of Ocean
Mountainous Mexico with a resort_type of Mountaintop
Greater Lakes with a resort_type of Lakeside
I made up the resort_id for each of the above
100 Light of the Ocean
200 Breathtaking Bahamas
300 Mountainous Mexico
400 Greater Lakes
into Accommodations:
description:
Bungalow 1 in resort Light of the Ocean with a cost_per_night of $289
Bungalow 2 in resort Light of the Ocean with a cost_per_night of $289
Bungalow 3 in resort Light of the Ocean with a cost_per_night of $325
Suite 101 in resort Breathtaking Bahamas with a cost_per_night of $199
Suite 102 in resort Breathtaking Bahamas with a cost_per_night of $199
Suite 201 in resort Breathtaking Bahamas with a cost_per_night of $250
Suite 202 in resort Breathtaking Bahamas with a cost_per_night of $250
Room 10 in resort Mountainous Mexico no value for cost_per_night
Cabin A in resort Greater Lakes with a cost_per_night of $350
Some discussion around what kind of thinking to use for this would b be helpful
I cannot write the inserts, because they involve columns from other tables. The hole in my knowledge(I believe) is not understanding JOIN, WHEN or maybe UNION??
Submitted: 1 year ago.
Category: Homework
Expert:  the-pc-guy replied 1 year ago.
can you tell me the exact problem you are having is? and is this in MySql?
Customer: replied 1 year ago.
I am using SQL developer on Oracle database I had to create tables that I described in my question and then insert the values that I described in my question that I already submitted. I cannot figure out what to do...have you read the question that I submitted? I do not know how to word the insert commands
Customer: replied 1 year ago.
What is up with the 7 minutes left message?
Expert:  the-pc-guy replied 1 year ago.
i dont know how the timer works it is just something the site adds. anyway. i am familiar with MySql commands and the syntax should be similar in oracle so I will try to help you as best as I can I cannot make sense of the question because it came out all jumbled on my screen in a single paragraph and I cant tell what is what. Can you give me an example of 1 of your insert statements?
Customer: replied 1 year ago.
This page wont allow my attachment?????
Customer: replied 1 year ago.
I sent the instructions:
I will paste my commands again:
Customer: replied 1 year ago.
CREATE TABLE Resort_type(
resort_type_id DECIMAL(12)PRIMARY KEY,
resort_type VARCHAR(64)NOT NULL
);
Customer: replied 1 year ago.
CREATE TABLE Resort(
resort_id DECIMAL(12)PRIMARY KEY,
name VARCHAR(12)NOT NULL,
resort_type_id DECIMAL(12)NOT NULL
);
Customer: replied 1 year ago.
ALTER TABLE Resort
ADD CONSTRAINT resort_fk
FOREIGN KEY(resort_type_id)
REFERENCES Resort_type(resort_type_id);
Customer: replied 1 year ago.
CREATE TABLE Accommodations(
accommodations_id DECIMAL(12)PRIMARY KEY,
description VARCHAR(255)NOT NULL,
cost_per_night DECIMAL(12),
resort_id DECIMAL(12)NOT NULL
);
Customer: replied 1 year ago.
ALTER TABLE Accommodations
ADD CONSTRAINT resort_id_fk
FOREIGN KEY(resort_id)
REFERENCES Resort(resort_id);
Customer: replied 1 year ago.
These commands have been accepted and all tables and constraints created
Customer: replied 1 year ago.
The problem comes when I need to perform the inserts. Read the questions carefully and I think that a join is required or some kind of extra command prior to the insertion
Expert:  the-pc-guy replied 1 year ago.
first of all you need to do the fololowingCREATE TABLE Resort_type( resort_type_id NUMBER(12) PRIMARY KEY, resort_type VARCHAR(64) NOT NULL);
Expert:  the-pc-guy replied 1 year ago.
i am not familiar with decimal data type
Expert:  the-pc-guy replied 1 year ago.
change all DECIMAL to NUMBER for that matter
Customer: replied 1 year ago.
That will not help with the inserts. That is great to change it to number. What about the inserts? The tables created and altered fine....it's the inserts...do you know anything about how to perform JOIN or whatever I have to do to accomplish the inserts?
Expert:  the-pc-guy replied 1 year ago.
now for a generic insert it would be something like this INSERT INTO Resort_type (resort_type_id,resort_type) VALUES(111,'hotel');
Expert:  the-pc-guy replied 1 year ago.
you do not do a join in an insert statement
Customer: replied 1 year ago.
Prior to perhaps? I am out of ideas...do you have any?
Expert:  the-pc-guy replied 1 year ago.
ok, what are you stuck on?
Expert:  the-pc-guy replied 1 year ago.
did you try the insert statement i provided?
Expert:  the-pc-guy replied 1 year ago.
dont worry about joins yet, lets get the insert first
Customer: replied 1 year ago.
Error starting at line : 1 in command -
INSERT INTO Resort_type(RESORT_TYPE,RESORT_TYPE_ID)
VALUES('Ocean',111)
Error report -I just copied and pasted what you suggested that is the message it returned...I tried
SQL Error: ORA-00001: unique constraint (JENNIFER.SYS_C0011426) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Expert:  the-pc-guy replied 1 year ago.
um no your valuse are differentCREATE TABLE Resort_type( resort_type_id DECIMAL(12)PRIMARY KEY, resort_type VARCHAR(64)NOT NULL );
Expert:  the-pc-guy replied 1 year ago.
resort_type_id is the first field
Expert:  the-pc-guy replied 1 year ago.
INSERT INTO Resort_type(resort_type_id,resort_type) VALUES(111,'Ocean');this would be correct based on how you told me you had your tables defined
Customer: replied 1 year ago.
just a minute I may have copied the wrong error message..I have many from previous attempts
Expert:  the-pc-guy replied 1 year ago.
also remember that field names are ***** ***** if you are going to use them in your insert query you need to make sure to match them to the case they are defined as.
Customer: replied 1 year ago.
Not working
Expert:  the-pc-guy replied 1 year ago.
what is the error you get now?
Customer: replied 1 year ago.
I think that I have already inserted something and it is returning error message because of this. One moment while I SELECT * to review my progress. Please..i did mean this as a please..not a command
Expert:  the-pc-guy replied 1 year ago.
is there any data in the table.doSELECT * FROM Resort_type;
Customer: replied 1 year ago.
There is nothing in resort type except the columns that I created when I created the table.
Expert:  the-pc-guy replied 1 year ago.
do this TRUNCATE TABLE Resort_type;
Customer: replied 1 year ago.
okay,the reason that it was not accepting the command that you gave me is because the data was already in the table from a previous effort...I misspelled on the SELECT * Resort_type command to check this. The insert into Resort table is not working with the command:
INSERT INTO Resort(name, resort_type)
VALUES('Light of the Ocean', 'Ocean',);
Error starting at line : 1 in command -
INSERT INTO Resort(name, resort_type)
VALUES('Light of the Ocean', 'Ocean')
Error at Command Line : 1 Column : 26
Error report -
SQL Error: ORA-00904: "RESORT_TYPE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Customer: replied 1 year ago.
The reason I contacted this forum is for this type of mixed insert
If you see Resort_type isn't in the table Resort
Customer: replied 1 year ago.
Also in the insert commands for Accommodations table
resort is not an attribute in the table Accommodations
How do I perform these mixed inserts?
Customer: replied 1 year ago.
This is not working out. The reason I contacted the forum was for the mixed inserts. I can do simple inserts. The problem comes when I need to insert something that isn't an attribute in the table.
Customer: replied 1 year ago.
are you still there?
Expert:  Ingo U replied 1 year ago.
Hi,
did you need to move on, or are you still waiting/looking for help on this?
One problem may be that you use the identifier "resort_type"for the table name as well as a column within it. Not sure about Oracle on this (MSSQL is my fluency) but it's possible. Regardless, it's always a good idea to avoid choosing the same name for multiple entities in a database (or elsewhere, really)
Judging from the posted thread, at some point you tried to insert a row with a primary key that already existed in the table.. I think you figured that one out.
On the final "mixed" insert question, if I understand it correctly, the short answer may be "it just doesn't work that way". I can help you with that but please let me know if you're still interested in pursuing this topic.
Regards,
Ingo U

Related Homework Questions