How are virtual tables in SQL used? Virtual Tables in SQL | Nested and complex queries in SQL

Welcome back MechanicaLEi, did you know that virtual tables help convert phrases like the ones we use to interact with our Alexa or Google home device into retrival keys that are possessed by a database? This makes us wonder, How are virtual tables in SQL used?
Before we jump in check out the previous part of this series to learn about What data manipulation commands in SQL are?
In SQL, a view is a virtual table based on the result-set of an SQL statement. It contains both rows and columns, just like a real table and hence the name virtual table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions: WHERE, and JOIN to a view and present the data as if the data were coming from one single table. There are three major commands when it comes to using virtual tables or views in SQL: First is the CREATE VIEW command. It creates a new virtual table and it always shows up-to-date data. The database engine recreates the data, using the view's SQL statement, every time a user queries a view. The syntax for create view involves a view_name, column names to be viewed, table name and a condition. For example This SQL command creates a view that shows all customers from India. Second comes updating a view. It can be carried out using the CREATE OR REPLACE VIEW command. The syntax involves a view_name, columns, table name and the condition which needs to be updated. This example adds the city column to the Indian Customers view. Third is the DROP VIEW command. A view is deleted with the DROP VIEW command. It's syntax is relatively simple and involves only the view name. Continuing from the previous examples, The following command simply drops the "Indian Customers" View..
A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used. Subqueries are most frequently used with the SELECT statement. The following statement will return the details of only those customers whose order value in the orders table is more than 5000 dollars. Subqueries can also be used with INSERT statements. The above statement will insert the records of premium customers into a table called premium_customers, by using the data returned from subquery. Here the premium customers are the customers who had placed order worth more than 5000 dollar. You can also use the subqueries in conjunction with the UPDATE statement to update the single or multiple columns in a table. The above statement will update the order value in the orders table for those customers who live in the area whose postal code is 100001, by increasing the current order value by 10 dollars. Similarly, you can use the subqueries in conjunction with the DELETE statement to delete the single or multiple rows in a table. The SQL statement in the example above will delete those orders from the orders table that contains the product whose product_id is 3.
Hence, we first saw what virtual tables in SQL are and then went on see what Nested queries and complex queries are?
In the next episode of MechanicaLEi find out What transaction concept is?

Timecode:
0:00 - Introduction
0:30 - Virtual Tables in SQL
1:53 - Nested and complex queries
3:14 - Conclusion

Attributions:
[1]. Ikuko Takanashi et al., Mitsubishi Denki Kabushiki Kaisha, Tokyo, Japan. NATURAL LANGUAGE DATABASE RETREVAL SYSTEM USING VIRTUAL TABLES TO CONVERT PARSED INPUT PHRASES INTO RETRIEVAL KEYS. U.S. Patent 5,442,780 filed July 08, 1992, and issued August 15, 1995

[MechanicalEI]

Hey MechanicaLEi,
Check out this video of what "Virtual Tables in SQL" and "Nested and complex queries" are, then tell us what you think. We've added timecodes to help you navigate between relevant parts of the video.