BLACK FRIDAY EXTRA SAVINGS EVENT - EXTENDED
START FREE

Creating Views error

How exactly CREATE VIEW works?
If I udnerstand correctly the lesson then my code should works.
Screen Link:

%%sql
DROP VIEW IF EXISTS chinook.single_song;
CREATE VIEW chinook.single_song AS
                                    SELECT t.album_id
                                      FROM chinook.track AS t
                                     INNER JOIN chinook.invoice_line AS il ON il.track_id = t.track_id;

SELECT *
FROM chinook.single_song

But the output is:

…and my files looks line this:

BUT when I use this code ( it’s the same, but without chinook. part) it works:

%%sql
DROP VIEW IF EXISTS single_song;
CREATE VIEW single_song AS
                                    SELECT t.album_id
                                      FROM track AS t
                                     INNER JOIN invoice_line AS il ON il.track_id = t.track_id;

SELECT *
FROM chinook.single_song

Output:

This is clarified in the content -

If you’re working with SQLite on your local machine, or in one of our Jupyter projects, you don’t need to specify the database name

But, how the engine can guess from which file it should take the database when there is more than one file?

For ex: what would happen if I had chinook.db, chinook1.db, chinook2.db

I am assuming that’s something you would have added at the top of the Notebook to form the connection between the Notebook and the database.

I believe it would have been something like -

%%capture
%load_ext sql
%sql sqlite:///chinook.db

I think the Guided Project on the CIA data using SQL covers this as well.

I did that in cell n.1

The code in the first cell tells your Jupyter Notebook to connect to the Chinook database.

When you ask -

Nothing is being guessed here. You specified which database to connect to in your first cell, and any subsequent queries work with tables from that database.

I forgot and didn’t notice it! Thank you:)

So, if I want to specify my code as: CREATE VIEW chinook.single_song then can I just write in the first cell:

%%capture
%load_ext sql

WIthout %sql sqlite:///chinook.db?

1 Like

You can try running it yourself first to see if it works or not! :slight_smile:

It doesn’t work. So I don’t know how to establish CREATE VIEW chinook.single_song in Jupyter notebook.

It doesn’t work:

%%capture
%load_ext sql

It doesn’t work:

%%capture

It doesn’t work:

%%capture
%load_ext sql
%sql

Also, I’ve checked both CREATE VIEW chinook.single_song and CREATE VIEW chinook.db.single_song

You can’t/don’t need to do that in this case (when working locally on your system). That’s what the error is trying to point out.

You run it without the chinook. and it runs as intended as you already observed earlier -

I know that in this particular project is not necessary, but in other projects that might be important. So, how to recall the database when referring to a specific file?

After all, there may be a situation where, for example, we need to run two databases from two files.

That’s somewhat clarified in the content, but beyond that you will have to search for how to do that exactly (if their link doesn’t help much) because the content doesn’t seem to cover that -

We’ll be specifying the database name using [database name].[view or table name] syntax in instead of just [view or table name]. You’ll need to use this in conjunction with any views because we have manually attached the database.

There might be tutorials or resources online that could help with how to or when to manually attach databases.