Views and Subqueries > Coding Practice

Let's try out the questions in this practice set to gain further grip on Views and Subqueries. Here you go!

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 users, orders placed, and the products on the platform.

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

1.

Create a view

user_details

to store the following information of the

user

Columns In View

id	name	age	gender	pincode
...	...	...	...	...

2.

Create a view

user_order_details

to store the following information of the users and their orders.

Columns In View

user_id	name	age	gender	pincode	order_id	total_amount
...	...	...	...	...

3.

Get the user_id and pincode of the customers who shopped for more than 50,000 rupees from the

location_order_details

view present in the database.

Data in location_order_details View

user_id	pincode	order_id	total_amount
...	...	...	...

Expected Output Format:

user_id	pincode	total_amount_spent
...	...	...

4.

Get the rating variance for every product in the database.

Note:

Rating variance is the difference between average rating and rating of a product

Expected Output Format:

name	rating_variance
...	...

5.

Let's now calculate the rating variance of products in the "MOBILE" category.

Note:

Rating variance is the difference between average rating and rating of a product

Expected Output Format:

name	rating_variance
...	...

6.

Get all the products from the watch category, where rating is greater than average rating

Expected Output Format:

name	rating
...	...

7.

Get the users where average amount spent by the user is greater than the average amount spent on all the orders on the platform

Expected Output Format:

customer_id	avg_amount_spent
...	...

8.

Get order ids in which order consists of mobile (product_ids: 291, 292, 293, 294, 296) but not screen guard (product_ids: 301, 302, 303, 304).

Expected Output Format:

order_id
...