Views and Subqueries Views Cheat Sheet

 Views

Database: The database stores the sample data of an e-commerce aplication. Here, the database consists of

user

,

order_details

and

product

tables that store the information of products, orders placed, and the products on the platform.

Refer the tables in the code playground for a better understanding of the database.

View

A view can simply be considered as a name to a SQL Query

Create View

To create a view in the database, use the

CREATE VIEW

statement.

Example

Create

user_base_details

view with id, name, age, gender and pincode.

1

2

3

CREATE VIEW user_base_details AS

SELECT id, name, age, gender, pincode

FROM user;

SQL

Note

In general, views are read only.

We cannot perform write operations like updating, deleting & inserting rows in the base tables through views.

Try it Yourself!

Create

order_with_products

view with order_id, product_id, no_of_units, name, price_per_unit, rating, category, brand.

Querying Using View

We can use its name instead of writing the original query to get the data.

1

2

SELECT *

FROM user_base_details;

SQL

Output

id	name	age	gender	pincode
1	Sai	40	Male	400068
2	Boult	20	Male	30154
3	Sri	20	Female	700009
...	...	...	...	...

We can use same operations which are used on tables like WHERE clause, Ordering results, etc.

If we try to retrive data which is not defined in the view it raises an

error

.

Example

1

2

3

4

SELECT name, address

FROM user_base_details

WHERE gender = "Male";

ORDER BY age ASC;

SQL

Output

1

Error: no such column:address

SQL

Try it Yourself!

From the

order_with_products

view created above, get the name and no_of_units ordered in order_id = 802.

Expected Output

name	no_of_units
Oneplus 8 Pro	1
Gorilla Glass	1

List All Available Views

In SQLite, to list all the available views, we use the folowing query.

1

2

3

4

5

6

SELECT

name

FROM

sqlite_master

WHERE

TYPE = 'view';

SQL

Output

name
order_with_products
user_base_details

Delete View

To remove a view from a database, use the

DROP VIEW

statement.

Syntax

1

DROP VIEW view_name;

SQL

Example

Delete

user_base_details

view from the database.

1

DROP VIEW user_base_details;

SQL

Advantages

• Views are used to write complex queries that involves multiple joins, group by, etc., and can be used whenever needed.
• Restrict access to the data such that a user can only see limited data instead of a complete table.