190-4, Even after using DROP to remove the customer_usa view, I am getting the below mentioned error

Hi @KatherineHeal,

Welcome to the Community!

Try the following 2 steps then:

  1. Restore Initial Code
  2. Insert the same code as @joshirohini2112 mentioned:
CREATE VIEW chinook.customer_gt_90_dollars AS
SELECT
c.*
FROM chinook.invoice i
INNER JOIN chinook.customer c ON i.customer_id = c.customer_id
GROUP BY 1
HAVING SUM(i.total) > 90;
SELECT * FROM chinook.customer_gt_90_dollars;

Don’t drop any table, neither before nor after restoring the initial code (because it seems that in your case there is nothing to drop).

@Elena_Kosourova

Thanks for the reply but unfortunately that isn’t helping. I am getting the same error no matter what code I put in (or no code!). With or without dropping any tables. I am doing the ‘restore initial code’ each time.

Uhm, that’s really strange! :frowning: Can you try then restarting your computer? It’s not my favorite approach to resolve issues, but sometimes it works against any logic. Also in this case don’t drop any table.

Yes, I have just tried to restart my computer with no different outcome.

Hey @Elena_Kosourova, I have also tried everything you asked for, but still no luck with the output.

@joshirohini2112, @KatherineHeal, @bhatn042

Could you please share the last version of the code each of you used (including the lines with DROP, in case you used it last time)? Also, please share the exact error message that you received when running the very last version of your code.

Isn’t that you forgot ; after the line (or lines) with the DROP statement?

So my request is the following:

  1. Restart the code using the Restore Initial Code (even without restarting the computer).
  2. Run your code (with the DROP line at the beginning or without it).
  3. Share your whole code from the last screen here.
  4. Share the last error that you received here.

Hey @Elena_Kosourova, I restored the initial code then wrote the DROP statement, even after dropping the error was same that customer_usa already exists.
The screenshot is attached for the same.

Dropped the customer_usa query only and running the code gave the error.

Hi @joshirohini2112,

Wait, but customer_usa is not a view, it’s a table! So in case you want to drop it, you should use the statement DROP TABLE. Anaway, forget for now about this customer_usa and try these steps then:

  1. Restore Initial Code (yes, again)
  2. Run this code:
DROP VIEW customer_gt_90_dollars;
CREATE VIEW chinook.customer_gt_90_dollars AS
SELECT
c.*
FROM chinook.invoice i
INNER JOIN chinook.customer c ON i.customer_id = c.customer_id
GROUP BY 1
HAVING SUM(i.total) > 90;
SELECT * FROM chinook.customer_gt_90_dollars;

I have just tried it in that mission screen, it works.

@Elena_Kosourova, I earlier tried it with DROP TABLE as well, but still no luck with it.
And I just tried the steps you asked to follow. However, the error is the same.

@joshirohini2112,

I’m quite sure that the issue with your code is exactly about dropping: something is going wrong. I mean, or you’re dropping a non-existing table or view, or at some point you forgot to delete an existing view. Let’s do the following steps:

  1. DROP VIEW customer_gt_90_dollars;
    (if it throws an error at this step - don’t worry, it’s ok, it means there was nothing to drop, so just continue with the next 2 steps).
  2. Press the button Restore Initial Code
  3. Now run this code (without any drops already)
CREATE VIEW chinook.customer_gt_90_dollars AS
SELECT
c.*
FROM chinook.invoice i
INNER JOIN chinook.customer c ON i.customer_id = c.customer_id
GROUP BY 1
HAVING SUM(i.total) > 90;
SELECT * FROM chinook.customer_gt_90_dollars;

If you want, at the 3rd step you can insert just the code from the solution. But there shouldn’t be any difference, since the solution code is absolutely the same as the piece of code above.

@Elena_Kosourova

I have tried all the suggestions in this thread and still have no luck (even after restarting). There’s some thing very strange going on. When I run any of the following (after restart and restoring intial code)

DROP VIEW customer_gt_90_dollars;
DROP VIEW customer_usa;
DROP TABLE customer_usa;
Code from the solution
or and empty query I still get the following error message

(sqlite3.OperationalError) table customer_usa already exists
[SQL: CREATE VIEW chinook.customer_usa AS SELECT * FROM chinook.customer WHERE country = “USA”;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

I have no idea why its throwing an error about the line “CREATE VIEW chinook.customer_usa” etc because I have not run that line ever nor am I even dealing with chinook.customer_usa in this instance.

I’ve just checked and I get this same error message on 190-5 and 190-6 screens of the mission, but by 190-7 the error message is gone and I’m able to complete the screen.

@KatherineHeal,

Yes, I was trying all my own suggestions also in my mission screen, and also was getting the same error about the customer_usa table which I had never created too. Have you tried my last approach starting from the step 2? I.e., without any drops at all.

Yes, I have tried your last approach, still the same error.

Uhm, Katherine, then I’m really very confused :exploding_head: The thing is that the code in the solution (which is the same @joshirohini2112 also mentioned in the first post) is actually absolutely correct. And it worked perfectly right yesterday on my mission screen, after several tries and error messages, idenical to those you had shared. Well, because I also made some experiments with dropping views and tables. Anyway, even despite all those experiments, when I restored the initial code (even without restarting the computer) and inserted the one directly from the solution, it worked and let me pass to the next screen.

My ultimate suggestion to you is to take a short break of a couple of hours with this mission, as @holly suggested in this post. Then return to this task, and, already without neither restoring nor dropping anything, select whatever code is written on that screen and press Delete. Next, just copy-paste the whole code from the solution to your cleaned screen. This code is the following:

CREATE VIEW chinook.customer_gt_90_dollars AS 
    SELECT
        c.*
    FROM chinook.invoice i
    INNER JOIN chinook.customer c ON i.customer_id = c.customer_id
    GROUP BY 1
    HAVING SUM(i.total) > 90;
SELECT * FROM chinook.customer_gt_90_dollars;

or you can copy it directly from the solution. After this copy-pasting, press Submit Answer.

If even these manipulations don’t work, then could you please create a ticket here? Alternatively, you can press Contact Us button in the upper-right part of this screen, it’s the same thing. In this case, the DQ team will help you to mark this mission screen as done, so you’ll be able to proceed with the learning path.

A good news is that in the subsequent screens these “views” are not used anymore, hence you won’t have this confusion with the results of dropping them anymore :slightly_smiling_face:

I’m having this same issue. No matter what I put in the code box, any combination of DROP VIEW, or DROP TABLE, no code, solution code, the same error is thrown:

Output
(sqlite3.OperationalError) table customer_usa already exists
[SQL: CREATE VIEW chinook.customer_usa AS SELECT * FROM chinook.customer WHERE country = “USA”;]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Hi @adamyars,

Kindly try this approach.

Thank you, hitting the ‘Submit Answer’ rather than ‘Run Code’ was the solution to get the old table to drop.

1 Like

@adamyars I just tried this approach and it did not work for me.

@joshirohini2112, could you please share your code?