Example View
CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]
AS
select-statement;
1) Creating a simple view example
Statement uses the CREATE VIEW statement to create a view that represents
total sales per order.
CREATE VIEW salePerOrder
AS
SELECT
orderNumber,
SUM(quantityOrdered * priceEach) total
FROM
orderDetails
GROUP BY orderNumber
ORDER BY total DESC;
SHOW FULL TABLES;
SHOW TABLES;
2) Creating a view based on another view example
MySQL allows you to create a view based on another view.
Create a view called bigSalesOrder Query the data from the bigSalesOrder view
based on the salesPerOrder view to
show every sales order whose total is
greater than 60,000
SELECT * FROM bigSalesOrder;
CREATE VIEW bigSalesOrder
AS
SELECT
orderNumber,
ROUND(total,2) as total
FROM
salePerOrder
WHERE
total > 60000;
3) Creating a view with join example
Query returns data from both tables customers and payments using the inner join :
Creates a view customerPayments based on the above query above
CREATE VIEW customerPayments
AS
SELECT
customerName,
checkNumber,
paymentDate,
amount
FROM
customers
INNER JOIN
payments USING (customerNumber);
SELECT * FROM customerPayments;
Q1: Uses the CREATE VIEW statement to create a view based on multiple tables, and uses the INNER JOIN
clauses to join tables.
4) Creating a view with a subquery example
Q2: Uses the CREATE VIEW statement to create a view whose SELECT statement uses a subquery.
The view contains products whose buy prices are higher than the average price of all products
5) Creating a view with explicit view columns example
Q3: Create a new view based on the customers and orders tables with explicit view columns:
customerName
orderNumber
6) Create MySQL Updatable Views
Create an updatable view and update data in the underlying table through the view.
CREATE VIEW officeInfo UPDATE officeInfo
AS SET
SELECT officeCode, phone, city phone = '+33 14 723 5555'
FROM offices; WHERE
officeCode = 4;
SELECT * FROM officeInfo;
SELECT * FROM officeInfo
WHERE
officeCode = 4;
7) Checking updatable view information
You can check if a view in a database is updatable by querying the is_updatable column from the views table
in the information_schema database.
SELECT
table_name,
is_updatable
FROM
information_schema.views
WHERE
table_schema = 'classicmodels';
8) Removing rows through the view
Q4: Create a table named items, insert some rows into the items table, and create a view that contains items
whose prices are greater than 700.
Next, use the DELETE statement to remove a row with id value 3, and check the data through the view again
Finally, query the data from the base table items to verify
Table: items View: items_700
Q6: When does deleting a row from a view also delete the corresponding row in the base table, and when will
the system return an error? For example based on classicmodels database.
Q7: Self-study with show views, show create view, rename views, drop views. Student give a example for
each on classicmodels or sakila database.