We will learn about three different ways of creating views.
Views are virtual tables that are created as a result of a SELECT query. They offer a number of advantages such as showing only a subset of data that is meaningful to users or restricting the number of rows and columns shown for security reasons. A view containing columns from multiple tables can simplify queries by changing a multi-table query to a single-table query against a view. Views are stored in the database along with tables.
A view can be created from a single table, by joining two tables, or from another view.
Syntax
CREATE [OR REPLACE] VIEW view_name AS
SELECT col1, col2, ...coln
FROM tableWHERE < condition>-- Query 1
CREATE VIEW DigitalAssetCount AS SELECT ActorId, COUNT(AssetType) AS NumberOfAssets FROM DigitalAssets GROUP BY ActorId;
-- Query 2
SELECT * FROM DigitalAssetCount;
-- Query 3
CREATE VIEW ActorsTwitterAccounts AS SELECT FirstName, SecondName, URL FROM Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorID WHERE AssetType = 'Twitter';
-- Query 4
CREATE OR REPLACE VIEW ActorsTwitterAccounts AS SELECT CONCAT(FirstName, ' ', SecondName) AS ActorName, URL FROM Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorID WHERE AssetType = 'Twitter';
-- Query 5
CREATE VIEW RichActors AS SELECT FirstName, SecondName, Gender, NetWorthInMillions FROM Actors WHERE NetWorthInMillions > ( SELECT AVG(NetWorthInMillions) FROM Actors) ORDER BY NetWorthInMillions DESC;
-- Query 6
CREATE VIEW RichFemaleActors AS SELECT * FROM RichActors WHERE Gender = 'Female';
-- Query 7
CREATE VIEW ActorDetails (ActorName, Age, MaritalStatus, NetWorthInMillions) AS SELECT CONCAT(FirstName,' ',SecondName) AS ActorName, TIMESTAMPDIFF(YEAR, DoB, CURDATE()) AS Age, MaritalStatus, NetWorthInMillionsFROM Actors;
-- Query 8
SELECT ActorName, Age, NetWorthInMillions FROM ActorDetails ORDER BY Age DESC;This Articles Contents
CREATE VIEW ViewName AS SELECT column2, COUNT(column3) AS column4 FROM table2 GROUP BY column2;
A view can be created from a single table. The SELECT query specifies the columns in the view. Use the following query to create a DigitalAssetCount view from the DigitalAssets table.
CREATE VIEW DigitalAssetCount AS SELECT ActorId, COUNT(AssetType) AS NumberOfAssets FROM DigitalAssets GROUP BY ActorId;
This view shows the number of digital assets owned by an actor. A view can be queried in the same manner as a table. Run the following query on the view we just created:
SELECT * FROM DigitalAssetCount;
Views are stored as virtual tables and also appear in the list of tables when SHOW TABLES is executed.
MovieIndustry database has two tables and the DigitalAssetCount view is shown along with them. To find out which entities in the above image are tables and which are views, the SHOW FULL TABLES command is used. The Table_Type column in the result specifies whether the object is a view or a table as shown below:
CREATE VIEW ViewName AS SELECT column2, column3, column4 FROM table1 INNER JOIN table2 ON table1.Id = table2.column4 WHERE column5 = ‘Twitter’;
A view can be created from multiple tables using JOIN. Let’s suppose we want to create a view of Actors who have Twitter accounts. This can be done by joining the Actors and DigitalAssets tables as follows:
CREATE VIEW ActorsTwitterAccounts AS SELECT FirstName, SecondName, URL FROM Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorID WHERE AssetType = 'Twitter';
We can use the [OR REPLACE] clause to make changes to a view that we just created. If the view does not exist, the OR REPLACE clause has no effect.
SELECT CONCAT(FirstName, ' ', SecondName) AS ActorName, URL FROM Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorID WHERE AssetType = 'Twitter';
Here we have modified the view created above to show the first and last names in one column instead of two separate columns.
CREATE VIEW ViewName AS SELECT column1, column2, column3, column4 FROM table1 WHERE column4 > ( SELECT AVG(column4) FROM table1) ORDER BY column4 DESC;
The SELECT statement that creates a view can also have a nested subquery. If we want to create a table of those actors whose net worth is more than the average net worth, we can do so using a nested subquery as follows:
CREATE VIEW ViewName AS SELECT FirstName, SecondName, Gender, NetWorthInMillions FROM Actors WHERE NetWorthInMillions > ( SELECT AVG(NetWorthInMillions) FROM Actors) ORDER BY NetWorthInMillions DESC;
The view shows five rich actors whose net worth is more than the average of all actors in our database.
CREATE VIEW ViewName1 AS SELECT * FROM ViewName2 WHERE column1 = ‘Female’;
A view can also be created from another view. Let’s suppose we want to create a view, RichFemaleActors, based on the RichActors view we created in the last step.
CREATE VIEW RichFemaleActors AS SELECT * FROM RichActors WHERE Gender = 'Female';
CREATE VIEW ViewName (column1, column2, column3, column4) AS SELECT CONCAT(column5,’ ‘,column6) AS column1, TIMESTAMPDIFF(column7, column8, CURDATE()) AS column2, column3, column4 FROM table1;
In the previous examples, the SELECT statement specifies the columns of the view. We can explicitly define columns in a view bylisting them in parentheses after the view name. Run the following query to create the ActorDetails view where we define a column, Age, that is based on the DoB column in the Actors table:
CREATE VIEW ActorDetails (ActorName, Age, MaritalStatus, NetWorthInMillions) AS SELECT CONCAT(FirstName,' ',SecondName) AS ActorName, TIMESTAMPDIFF(YEAR, DoB, CURDATE()) AS Age, MaritalStatus, NetWorthInMillions FROM Actors;The following query lists the actors from the view created above according to age:
SELECT ActorName, Age, NetWorthInMillions FROM ActorDetails ORDER BY Age DESC;
Updateable Views
Views are not only used to query data; they can also be used to update data in the underlying tables. It is possible to insert or update rows in the base table, and in the same vein, delete rows from the table using an updatable view. In order for a view to become updatable, it must abide by certain conditions.
If the SELECT query that creates the view has aggregate functions (MAX, MIN, COUNT, SUM, etc.), DISTINCT keyword, LEFT JOIN or GROUP BY, HAVING, and UNION clauses, the resulting view will not be updatable. Similarly, a subquery that refers to the same table that appears in the FROM clause prohibits updates to the base table.
Syntax :
UPDATE view
SET col1 = value1, col2 = value2,...coln = valuen
WHERE <condition>-- The queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
CREATE VIEW ActorView AS SELECT Id, FirstName, SecondName, NetWorthInMillions FROM Actors;
-- Query 2
UPDATE ActorView SET NetWorthInMillions = 250 WHERE Id =1;
-- Query 3
SELECT Table_name, is_updatable FROM information_schema.views WHERE table_schema = 'MovieIndustry';
-- Query 4
DELETE FROM ActorView WHERE Id = 11;CREATE VIEW ViewName AS SELECT Id, FirstName, SecondName, NetWorthInMillions FROM Actors;
Let’s begin by creating a simple view to show the Actor names and their net worth.
CREATE VIEW ActorView AS SELECT Id, FirstName, SecondName, NetWorthInMillions FROM Actors;
We can query data from this view as follows:
UPDATE ViewName SET column1 = 250 WHERE column2 =1;
Say we want to update the net worth of Brad Pitt to 250 million dollars. This can be done with the following query:
UPDATE ActorView SET NetWorthInMillions = 250 WHERE Id =1;The change is visible in the view as well as the underlying Actors table.
SELECT Table_name, is_updatable FROM information_schema.views WHERE table_schema = ‘MovieIndustry’;
To find out which views in the database are updatable we can query the views table in the information_schema database. This table has a column is_updatable that indicates the type of view. Execute the following query to find out the updatable views in the MovieIndustry database:
SELECT Table_name, is_updatable FROM information_schema.views WHERE table_schema = 'MovieIndustry';
The database contains five views we created in the last lesson as well as the one created above. We can see that the DigitalAssetCount view is not updatable because an aggregate function was used in its creation. The same is true for the RichActors view. A view that refers to a non-updatable view also becomes non-updatable which is why the RichFemaleActors view is non-updatable. The ActorsTwitterAccounts and ActorDetails views created in the previous lesson and the ActorView view created in this lesson are updatable views.
DELETE FROM ActorView WHERE Id = 11;
Working with the view we created in step 1, we will now delete the actor details corresponding to Id number 11.
DELETE FROM ActorView WHERE Id = 11;
The operation is successful and one row is affected. We can check the view and the underlying table to confirm the deletion.
With Check Option
Views usually contain a subset of rows from a base table. It is possible to insert or update rows which are not visible through the view. The WITH CHECK OPTION clause is used at the time of creation of the view and is used to maintain consistency when updating a table through an updatable view. This clause forbids the user to insert or update rows that are not visible through the view.
Syntax :
CREATE [OR REPLACE] VIEW view_name AS
select_statement
WITH CHECK OPTION;-- Query 1
CREATE VIEW SingleActors AS SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillionsFROM Actors WHERE MaritalStatus = 'Single';
-- Query 2
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus,NetWorthInMillions VALUES ('Tom', 'Hanks', '1956-07-09', 'Male', 'Married', 350);
-- Query 3
CREATE OR REPLACE VIEW SingleActors AS SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions FROM Actors WHERE MaritalStatus = 'Single' WITH CHECK OPTION;
-- Query 4
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillion VALUES ('Matt', 'Damon', '1970-10-08', 'Male', 'Married', 160);
-- Query 5
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillion VALUES ('Charlize', 'Theron', '1975-08-07', 'Female', 'Single', 130);CREATE VIEW Viewname AS SELECT column1, column2, DoB, column3, column4, column5 FROM table WHERE column6 = ‘Single’;
Suppose we want a view to show details of only those actors who are single, we can create it using the following statement:
CREATE VIEW SingleActors AS SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions FROM Actors WHERE MaritalStatus = ‘Single’;
This view contains four rows as shown:
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) VALUES (‘Tom’, ‘Hanks’, ‘1956-07-09’, ‘Male’, ‘Married’, 350);
Since the SingleActors view is updatable, we can insert a row in the Actors table through it. To show the inconsistency that can arise, wewill insert a married actor in the table. Execute the following query to insert a new actor:
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions) VALUES ('Tom', 'Hanks', '1956-07-09', 'Male', 'Married', 350);The insert operation is successful as the row appears in the table.
But the newly inserted row doesn’t appear in the SingleActors view.
This is because the row we just added does not qualify to appear in the view. The purpose of this view was to display single actors only. However, the view can still be used to make unwanted changes to the Actors table.
CREATE OR REPLACE VIEW SingleActors AS SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions FROM Actors WHERE MaritalStatus = ‘Single’WITH CHECK OPTION;
The WITH CHECK OPTION clause restricts users to update or insert data which is visible through the view. We will update the SingleActors view created in step 1 using the following query:
CREATE OR REPLACE VIEW SingleActors AS SELECT FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions FROM Actors WHERE MaritalStatus = 'Single'WITH CHECK OPTION;If we omit the OR REPLACE clause from the above query, we will get an error because a view with this name already exists.
Now try to insert a row in the Actors table through the SingleActors view as follows:
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions)
VALUES ('Matt', 'Damon', '1970-10-08', 'Male', 'Married', 160);
We encounter the CHECK OPTION failed error message. The WITH CHECK OPTION clause ensures that only actors who are single can be inserted into the Actors table through this view. Now execute the following query:
INSERT INTO SingleActors (FirstName, SecondName, DoB, Gender, MaritalStatus, NetWorthInMillions)VALUES ('Charlize', 'Theron', '1975-08-07', 'Female', 'Single', 130);
The row is inserted in the table and is visible through the view as well.
Local & Cascaded Check
Local and cascaded check clauses are used to determine the scope of rule testing when a view is created based on another view. To summarize, Local check option restricts the rule checking to only the view being defined whereas the Cascaded check option checks the rules of all underlying views. In the absence of these keywords, cascaded check is used as default.
Syntax :
CREATE [OR REPLACE] VIEW view_name AS select_statement WITH [LOCAL | CASCADED] CHECK OPTION;-- CASCADED CHECK
-- Query 1
CREATE VIEW ActorsView1 ASSELECT * FROM Actors WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) > 40;
-- Query 2
INSERT INTO ActorsView1 VALUES (DEFAULT, 'Young', 'Actress', '2000-01-01', 'Female', 'Single', 000.00);
-- Query 3
CREATE OR REPLACE VIEW ActorsView2 AS SELECT * FROM ActorsView1 WITH CASCADED CHECK OPTION;
-- Query 4
INSERT INTO ActorsView2 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);
-- Query 5
CREATE OR REPLACE VIEW ActorsView3 AS SELECT * FROM ActorsView2 WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) < 50;
-- Query 6
INSERT INTO ActorsView3 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);
-- Query 7
INSERT INTO ActorsView3 VALUES (DEFAULT, 'Old', 'Actor', '1960-01-01', 'Male', 'Single', DEFAULT);
-- LOCAL CHECK
-- Query 8
ALTER VIEW ActorsView2 AS SELECT * FROM ActorsView1 WITH LOCAL CHECK OPTION;
-- Query 9
INSERT INTO ActorsView2 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);
-- Query 10
INSERT INTO ActorsView3 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);CREATE VIEW ViewName ASSELECT * FROM table1 WHERE TIMESTAMPDIFF(column1, column2, CURDATE()) > 40;
We will start by creating a view ActorsView1 which shows all actors who are older than 40.
CREATE VIEW ActorsView1 ASSELECT * FROM Actors WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) > 40;Seven rows from the Actors table satisfy the WHERE clause.
In the absence of the WITH CHECK OPTION clause there is no restriction on updates through ActorsView1. We can insert a 20- year-old actor to the Actors table using this view as follows:
INSERT INTO ActorsView1 VALUES (DEFAULT, 'Young', 'Actress', '2000-01-01', 'Female', 'Single', 000.00);The record is inserted in the table even though it does not satisfy the condition of the view (age > 40 years).
The record appears in the table but not in the view.
This is a disparity that can be handled using the WITH CHECK OPTION clause as discussed at length in the previous lesson.
CREATE OR REPLACE VIEW ActorsView2 AS SELECT * FROM ActorsView1 WITH CASCADED CHECK OPTION;
Next, create a view ActorsView2 based on ActorsView1 as follows:
CREATE OR REPLACE VIEW ActorsView2 AS SELECT * FROM ActorsView1 WITH CASCADED CHECK OPTION;Since ActorsView2 is based on ActorsView1, it also has seven rows. The view has a CASCADED check option which means that insert or update through ActorsView2 should not only be compatible with this view but also the underlying view, which is, ActorsView1. To see how it works, insert an actor to the Actors table using ActorsView2 whose age is 20 years.
INSERT INTO ActorsView2 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single', DEFAULT);
We encounter an error message, CHECK OPTION failed ‘MovieIndustry.ActorsView2’. The record is not inserted into the Actors table even though ActorsView2 did not impose any age restrictions. This is because the CASCADED CHECK OPTION clause also tests the rules of the underlying view, ActorsView1. Since ActorsView1 only allowed actors who are older than 40 years and ActorsView2 is based on ActorsView1 so we were unable to add a 20 year old actor.
CREATE OR REPLACE VIEW ViewName1 AS SELECT * FROM ViewName2 WHERE TIMESTAMPDIFF(column1, column2, CURDATE()) < 50;
Now we are going to demonstrate the scope of rule testing of the CASCADED check option. For this purpose, create a view ActorsView3 based on ActorsView2 which should only display actors who are younger than 50.
CREATE OR REPLACE VIEW ActorsView3 AS SELECT * FROM ActorsView2 WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) < 50;
There is only one row in this view. We should be able to insert a 20- year-old actor through this view as it satisfies the age < 50 rule. Try the following query:
INSERT INTO ActorsView3 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single' , DEFAULT);
When executed, we encounter the error, CHECK OPTION failed ‘MovieIndustry.ActorsView3’. This is because the CASCADED check option checks the rules of all underlying views before an update is allowed. ActorsView3 is based on ActorsView2 and ActorsView2 is based on ActorsView1 which only allows actors older than 40 so we were unable to add a 20-year-old actor through ActorsView3.
INSERT INTO ViewName VALUES (DEFAULT, ‘Old’, ‘Actor’, ‘1960-01-01’, ‘Male’, ‘Single’, DEFAULT);
Let s see if we can insert a 60-year-old actor with this view. Execute the query given below:
INSERT INTO ActorsView3 VALUES (DEFAULT, 'Old', 'Actor', '1960-01-01', 'Male', 'Single', DEFAULT);
Insert operation is successful even though an actor whose age is more than 50 years does not comply with the age restriction of ActorsView3. Since we did not mention the WITH CHECK OPTION clause when creating ActorsView3, the above insert operation was successful. Here the rules for ActorsView2 and ActorsView1 were checked because of the CASCADED check option and the insert was made as the row conforms with the rules of the underlying view (age should be more than 40 years).
Note that if ActorsView3 was created using the WITH CHECK OPTION clause then the above insert operation would fail.
The following image gives a pictorial explanation of the effects of using cascaded check option:
ALTER VIEW ActorsView2 AS SELECT * FROM ActorsView1 WITH LOCAL CHECK OPTION;
To limit the scope of rule checking let’s redefine ActorsView2 with a LOCAL check option as follows:
ALTER VIEW ActorsView2 AS SELECT * FROM ActorsView1 WITH LOCAL CHECK OPTION;To recap, this view is based on ActorsView1 which shows actors whoare older than 40 as shown:
Since ActorsView2 does not specify any age criterion, we can try inserting a 20-year-old actor using this view:
INSERT INTO ActorsView2 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single' , DEFAULT);
The row is successfully inserted. LOCAL check option in ActorsView2 means that insert operation should only conform to the age restriction of ActorsView2 (none in our case). When we used the CASCADED check in step 2, we got an error message when the above query was executed, because the rule of the underlying ActorsView1 was also checked.
However, the row just inserted into the Actors table is not visible through ActorsView2 because this view only shows actors who are older than 40.
INSERT INTO ViewName VALUES (DEFAULT, ‘Young’, ‘Actor’, ‘2000-01-01’, ‘Male’, ‘Single’ , DEFAULT);
Now that we have changed the scope of the check option for ActorsView2, we can see the effects on ActorsView3 as well (ActorsView3 is based on ActorsView2 and specifies a rule, age < 50). We can now insert a 20-year-old actor using ActorsView3 with the following query:
INSERT INTO ActorsView3 VALUES (DEFAULT, 'Young', 'Actor', '2000-01-01', 'Male', 'Single' , DEFAULT);Because of the LOCAL check, the insert operation is successful. Previously we had encountered an error in step 3 with the same query. At that time ActorsView2 had a CASCADED check option and MYSQL checked the age restriction of underlying ActorsView1. Now ActorsView2 has a LOCAL check option. Hence MYSQL inserts the record without checking the rule of ActorsView1 (age>40).
As can be seen from the image above, the newly inserted row does not appear in the view because the actor we just inserted is 20-years- old while this view only shows actors who are older than 40 but less than 50 years old.
The following image shows the effects of using local check option instead of cascaded check:
Drop, Show, & Rename Views
There are two ways to list all views in a database; one is the SHOW FULL TABLES command and the other is querying the information_schema database. The DROP VIEW command is used to delete a view from the database. A view can be renamed in two ways. One is by using the RENAME TABLE command and the other is by deleting and recreating.
Syntax :
SHOW FULL TABLES
{FROM | IN} db_name
WHERE table_type = ‘VIEW’
LIKE pattern;
DROP VIEW [IF EXISTS] view1, view2,...viewn;
RENAME TABLE old_name
TO new_name;
-- Query 1
SHOW FULL TABLES WHERE table_type = 'VIEW';
-- Query 2
SHOW FULL TABLES LIKE '%Actor%';
-- Query 3
SELECT table_name FROM information_schema.TABLES WHERE table_type = 'VIEW' AND table_schema = 'MovieIndustry';
-- Query 4
DROP VIEW DigitalAssetCount, ActorAssets;
-- Query 5
DROP VIEW IF EXISTS DigitalAssetCount, ActorAssets;
-- Query 6
CREATE VIEW ActorAge AS SELECT * FROM Actors WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) > 50;
-- Query 7
RENAME TABLE ActorAge TO ActorsOlderThan50;SHOW FULL TABLES WHERE table_type = ‘VIEW’;
In the previous lessons we have used the SHOW TABLES command to see the views created. Since the tables and views share the same namespace, this command lists both of them. The SHOW FULL TABLES command used with a WHERE clause can be used to show only the views in a database. The FROM | IN clause is optional and can be used to see the views from another database.
SHOW FULL TABLESWHERE table_type = 'VIEW';
The LIKE operator can be used to shortlist views based on a word or pattern.
SHOW FULL TABLES LIKE '%Actor%';
The query returns 10 rows containing the word ‘Actor’ of which 9 are views and 1 is a table in our database.
SELECT table_name FROM information_schema.TABLES WHERE table_type = ‘VIEW’ AND table_schema = ‘MovieIndustry’;
The information_schema database is a catalogue of all MYSQL databases and contains metadata such as database names, tables,privileges, and datatypes of columns, etc. A query against this database can also list all views of a particular database as follows:
SELECT table_name FROM information_schema.TABLES WHERE table_type = 'VIEW' AND table_schema = 'MovieIndustry';
DROP VIEW ViewName1, ViewName2;
We can delete one or more views at a time using the DROP VIEW statement. In the absence of the IF EXISTS clause, MYSQL gives an error if the view to be dropped does not exist. With this clause, a warning is generated if a view we wish to delete is not found in the database. Execute the following query:
DROP VIEW DigitalAssetCount, ActorAssets;
Mysql throws an error message. Re-run the above query with the IF EXISTS clause:
DROP VIEW IF EXISTS DigitalAssetCount, ActorAssets;
This time, we get a warning and the query is executed as can be seen from the list of views:
CREATE VIEW ViewName AS SELECT * FROM table1 WHERE TIMESTAMPDIFF(column1, column2, CURDATE()) > 50;
Views are stored in the same namespace as tables, hence, the RENAME TABLE command can be used for renaming views. To show how RENAME works, we will create a view as follows:
CREATE VIEW ActorAge AS SELECT * FROM Actors WHERE TIMESTAMPDIFF(YEAR, DoB, CURDATE()) > 50;
Next, we will change its name to ActorsOlderThan50.
RENAME TABLE ActorAge TO ActorsOlderThan50;The RENAME was successful as seen below:
There is another method to change the name of a view without using the RENAME clause. First, copy the query used to create the view,then drop the view, and lastly create a new one from the DDL copied in the first step. The SHOW CREATE VIEW query is used to show the DDL of the view.