5. Views

Views are virtual tables that are created from some filters or relationships. They save us time and simplify certain queries.

To create a view we will use:

CREATE VIEW [nombre de vista] AS SELECT [Columnas] FROM [tabla] WHERE [condicionales] ORDER BY [columna] ASC/DESC LIMIT [posición] OFFSET [número de filas];

And to delete it.

DROP VIEW [nombre de vista];

In the following example I am going to create a view of the songs (Track) of the Jazz genre.

CREATE VIEW tracks_jazz AS SELECT * FROM Track WHERE GenreId = (SELECT GenreId FROM Genre WHERE Name = 'Jazz');

Now I can run queries.

SELECT * FROM tracks_jazz;

63  Desafinado  8   1   2
64  Garota De Ipanema   8   1   2
65  Samba De Uma Nota Só (One Note Samba)   8   1   2
66  Por Causa De Você   8   1   2
...

Or even applying filters. Let's show all the songs (Track) that start with S.

SELECT * FROM tracks_jazz WHERE Name LIKE 'S%';

65  Samba De Uma Nota Só (One Note Samba)   8   1   2       137273  4535401
70  Se Todos Fossem Iguais A Você (Instrumental)    8   1   2       134948  4393377
124 Snoopy's search-Red baron   13  1   2   Billy Cobham    456071  15075616
125 Spanish moss-"A sound portrait"-Spanish moss    13  1   2   Billy Cobham    248084  8217867
127 Stratus 13  1   2   Billy Cobham    582086  19115680
...

Solutions