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

My Code:
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;

Error:
(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)

4 Likes

I got the same error. Dropping the table doesn’t work either. Did you find a way around it?

1 Like

No, not yet, even pressing the “run code” button on the blank screen is still giving the same error. And due to this the next codes in this course are also not working and giving the same error.

Hi @joshirohini2112 and @bhatn042,

Welcome to the Community!

I had the same issue on that mission screen. The approach that worked in my case was to press the Restore Initial Code button (two rounded arrows to the right of the green button Sumbit Answer) and then insert the same code as @joshirohini2112 mentioned. To be more sure, you can add first the following code line (after pressing the Restore Initial Code button):

DROP VIEW customer_gt_90_dollars;

I am still having this issue restarting browser, restoring initial code, dropping both tables (the customer_usa table as mentioned in the error message and the customer_gt_90_dollars mentioned by @Elena_Kosourova), and running a very simple line of code

SELECT *
FROM customer
LIMIT 3

1 Like

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: