Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we create a MySQL view with a subquery?
To illustrate the making of MySQL view with subquery we are using the following data from the table ‘Cars’ −
mysql> select * from cars; +------+--------------+---------+ | ID | Name | Price | +------+--------------+---------+ | 1 | Nexa | 750000 | | 2 | Maruti Swift | 450000 | | 3 | BMW | 4450000 | | 4 | VOLVO | 2250000 | | 5 | Alto | 250000 | | 6 | Skoda | 1250000 | | 7 | Toyota | 2400000 | | 8 | Ford | 1100000 | +------+--------------+---------+ 8 rows in set (0.08 sec)
Now, the following query will create a view named ‘cars_avgprice’ by using a subquery that will supply the values to the view. The subquery must be enclosed within parentheses.
mysql> Create view cars_avgprice AS SELECT NAME, Price FROM Cars WHERE price > (SELECT AVG(Price) from cars); Query OK, 0 rows affected (0.12 sec) mysql> Select * from cars_avgprice; +--------+---------+ | NAME | Price | +--------+---------+ | BMW | 4450000 | | VOLVO | 2250000 | | Toyota | 2400000 | +--------+---------+ 3 rows in set (0.03 sec)
If we will run the above subquery individually we can understand how view got its values −
mysql> Select AVG(Price) from cars; +--------------+ | AVG(Price) | +--------------+ | 1612500.0000 | +--------------+ 1 row in set (0.00 sec)
That is why the view ‘cars_avgprice’ has the list of cars having price more than the average of price i.e. 1612500.
Updated on: 22-Jun-2020
1K+ Views
Advertisements