I'm having issues with the ALTER TABLE function

Question

I'm attempting to use the tables trip, guide and reservation in order to make a normalized table. I'm getting the error "ORA-00933: SQL command not properly ended."

I'm not certain what is wrong with ALTER TABLE.

SELECT trip.TRIP_ID, trip.State, trip.Max_grp_size, trip.type, trip.season, guide.Guide_num, guide.last_name, guide.First_name, guide.address, guide.city, guide.Hire_date, trip_guides.guide_num, reservations.trip_price
FROM trip
JOIN trip_guides ON trip.TRIP_ID = trip_guides.trip_id
JOIN Guide ON trip_guides.guide_num = guide.Guide_num
ALTER TABLE trip ADD (price_trip CHAR)
JOIN reservations ON trip.price_trip = reservations.trip_price
ORDER BY trip.trip_ID;

Well for one, that's not in any capacity how `ALTER TABLE` is used... — Siyual, Aug 25 '17 at 18:44
Could you please direct me to something describing how `ALTER TABLE` is properly used and what I'm missing? I looked at the SQL Oracle FAQ and must not have understood. — MPimentel, Aug 25 '17 at 18:47
You can't use Alter table in the middle of a select statement. — HLGEM, Aug 25 '17 at 18:53
This makes no sense at all... maybe you want to make a select into? That would take your select with joins and create a new table with the structure and content of the select. — Carlos Alves Jorge, Aug 25 '17 at 18:58
ALTER is for CHANGING an existing table. Like adding a new column to an existing table. It sounds like you are wanting to run a SELECT statement and maybe INSERT INTO a new table? Can you explain more about what are you trying to do and we can guide you in that direction. As it stands, it's not at all clear what you are attempting to do. — JNevill, Aug 25 '17 at 19:01
Ok, what I'm trying to do is extract data from a 3rd level normalized database and make it look like a 1st level normalized database. The assignment is asking me to show examples of a 1st level and 2nd level normalized database. Where I'm having issues is that the data that I have to work with is in 3rd level. — MPimentel, Aug 25 '17 at 19:07

JNevill · Accepted Answer · 2017-08-25T20:03:37.670

0

It sounds like what you want is a "CREATE TABLE ... AS (SELECT...)" type of statement. Essentially creating a NEW table based on the result set of a SQL statement.

This would look like:

CREATE TABLE your_1st_level_normalized_table AS
(
    SELECT trip.TRIP_ID,
        trip.STATE,
        trip.Max_grp_size,
        trip.type,
        trip.season,
        guide.Guide_num,
        guide.last_name,
        guide.First_name,
        guide.address,
        guide.city,
        guide.Hire_date,
        trip_guides.guide_num,
        reservations.trip_price,
        NULL as PRICE_TRIP /*new field in the new table set to NULL*/
    FROM trip
    JOIN trip_guides ON trip.TRIP_ID = trip_guides.trip_id
    JOIN Guide ON trip_guides.guide_num = guide.Guide_num
    JOIN reservations ON trip.price_trip = reservations.trip_price
);

I've removed the ORDER BY since that's not allowed in this statement.

edited Aug 25 '17 at 20:03

answered Aug 25 '17 at 19:13

JNevill

46,980
4
38
63

Thank you. However, after running your code I get the error "ORA-00942: table or view does not exist." How should I fix that? Or is it too off topic for the question? – MPimentel Aug 25 '17 at 19:36
Does the trip, trip_guides, guide, and reservations table exist? Can you run that SELECT statement all by itself and get records back? – JNevill Aug 25 '17 at 19:40
Yes. However, I did find out that I misspelled 'reservations' meaning 'reservation' I apologize, but in fixing that I found another two errors. One being "ORA-00957: duplicate column name" and the other being "ORA-00904: "TRIP"."PRICE_TRIP": invalid identifier" How would I make create the column PRICE_TRIP? – MPimentel Aug 25 '17 at 19:52
Basically what we are doing is writing a working SELECT statement, then just wrapping that up in a CREATE TABLE statement to materialize the thing, so if the SELECT statement doesn't output what want in your table, you need to fix it up. As for your first error, you have defined the field name `guide_num` twice in your SELECT statement. Just alias one of those like... `trip_guides.guide_num as trip_guides_guide_num,` As for your new Price_Trip column, how do you derive that value? I don't know your data so I can't suggest how would you would add it here. – JNevill Aug 25 '17 at 19:56
Price_Trip doesn't have a value, and I am only aware of the `JOIN` statement for fusing multiple tables. – MPimentel Aug 25 '17 at 20:00
Then perhaps you are just intending that field to be set to NULL in your new table? If that's the case then add to your SELECT statement the new field with NULL as a value. I will update the answer. – JNevill Aug 25 '17 at 20:03
Now I once again have an error. The "ORA-00942: table or view does not exist" error. We will need to delete this once it's resolved. – MPimentel Aug 25 '17 at 20:21

I'm having issues with the ALTER TABLE function

1 Answers1