SQLite - Viewing Data

In this article at OpenGenus, we learn about streamlining database queries by using views. By creating a view that combines data from multiple tables, we can simplify complex queries and improve query readability. Views provide a logical representation of the data and make querying more efficient and maintainable.

Table of Content:

1. Introduction
2. Setting up tables with schema file
   • schema file example
3. Conventional way of data retrieval
4. Streamlining database queries
5. Simplifying Query Results with database views
6. Comparison and Conclusion

Introduction

Up until now you've been learning how to design database and how to add data to the like how to insert update and delete some values now I have added some complexity that is going from one table to mulitple and we had relationship too among all those tables as well.

Setting up tables with schema file

sqlite3 longlist.db

Let's open the database first with this command.

Let's have a book database as an example.

.schema

check the tables structure with this commnad.
schema file examplle

---

Conventional way of data retrieval

SELECT * FROM "authored";

Here the list if of who wrote which book in terms of id.
let's say i want to write a query to find which book fernanda melkor wrote we probaly first need to find fernanda's ID.

SELECT "id" FROM "authors"
WHERE "name" = "Fernanda Melchor';

This will show id of Fernanda and we can use this id in rest of my query but we could probaly do better than this. We could use what we saw before something called a sub query to take this query I just wrote and put it inside some other one here so let's take the next step and find the book IDs that Fernanda wrote so let's try looking in the authored table for that whcih has author IDs and book IDs side by side.

SELECT "book_id" FROM "authored"
WHERE "author_id" = (
	SELECT "id" FROM "authors"
	WHERE "name" = 'Fernanda Melchor'	
);

We should see now the book IDs that Fernanda has written but there's still one more step here what do I now need to do I have book IDs that Fernando wrote but how do you think I could find the titles from those book IDs.

SELECT "title" FROM "books"
WHERE "id" IN (
	SELECT "book_id" FROM "authored"
	WHERE "author_id" = (
		SELECT "id" FROM "authors"
		WHERE "name" = 'Fernanda Melchor'
	)
);

Again the order here is first find fernanda's ID then find the book IDs associated with that ID then find the titles associated with those book IDs themselves now I should see all those book that Fernanda has written .
as we talked about this is a lot of complexity a lot of tables to go through and there's probably a better way to do this let me open up a new terminal and try out a different way of going about this we'll make a new connection to my database.

---

Streamlining Database Queries

.quit 

sqlite3 longlist.db

now we should see that we have a brand new connection to my database and we could try to find a better way to do this let me try to do what we did visually and like join these tables together.

SELECT "name", "title" FROM "authors"
JOIN "authored" ON "authors"."id" = "authored"."author_id"
JOIN "books" ON "books"."id" = "authored"."book_id";

well I could try select name and title from the author's table but notice how author's table doesn't have titles in it it does have the name of the author but it doesn't have book title so to get titles I have to join in.
we will have Author name and book titles all in one table but what if we wanted to have this stored as a way to see my tables I want to be able to query this kind of table i see right here.

---

Simplifying Query Results with Database Views

well for that I could try to introduce a view now A View starts with a query as we just saw I wrote a query to select names and titles from three different tables joining them together but if I want to save the results of that query so I can query them later on I could use this syntax here.

I will try to write the very same query but now store it so I can use it later on I'll come back and I'll say create me a view and let's call this one just long list here just long list as we saw in our very first table

CREATE VIEW "longlist" AS
SELECT "name", "title" FROM "authors"
JOIN "authored" ON "author"."id" = "authored"."author_id"
JOIN "books" ON "books"."id" = "authored"."book_id";

I'll hit enter and I don't see the results but if I type let's say dot schema I should actually see down below here a new view that's part of my schema in this view I can actually see author names and author titles right next to each other let's try this I could query this view I could now say select star from longlist that name I gave it before semicolon hit enter and now I see the very same results author names and titles right next to each other I could limit to five.

SELECT * FROM "longlist"

It will show same results author name and titles right next to each other.

SELECT * FROM "longlist" LIMIT 5;

I can limit the search result to 5 and now we can only see 5 results.

SELECT "title" FROM "longlist"
WHERE "name" = 'Gernanda Melchor';

now what if I wanted to find which books Fernanda has written well becomes much more simple to find this I could say Select Title from long list where what happens now where name is equal to Fernanda let me go on a new line here from long list where name equals Fernanda melcore semicolon I'll hit enter and I should see the same results and just to show you side by side now here is our query with a view on the left hand side here we should see our query without a view

---

Comparison and Conclusion

Just to demonstrate the effectiveness of using views, let's compare the original query without a view to the simplified query using the "longlist" view.

On the left-hand side, we have the original query, which was long and involved multiple subqueries. It required us to navigate through various tables, perform join operations, and use subqueries to find the desired information. The complexity of the original query made it harder to read, understand, and maintain.

On the right-hand side, we have the simplified query using the "longlist" view. With the view in place, the query becomes significantly more straightforward. It eliminates the need for complex joins and subqueries. Instead, we can simply query the view and apply any necessary filters or conditions using a single WHERE statement.

This side-by-side comparison clearly demonstrates the advantages of using views. By encapsulating the complex join operations within a view, we can create a logical representation of the data that combines information from multiple tables. This simplifies the querying process and enhances the readability of our queries.

Not only does using views save us from writing lengthy and convoluted queries each time, but it also improves the maintainability of our code. If the underlying tables or relationships change, we only need to update the view's definition, and all queries using the view will automatically reflect the changes. This reduces the risk of introducing errors or inconsistencies when modifying complex queries manually.

In summary, the introduction of the "longlist" view has allowed us to transform a complex and lengthy query into a concise and easy-to-understand one. By utilizing views, we can abstract away the complexity of join operations, simplify our queries, and improve the overall efficiency and maintainability of our database interactions.