Database Systems with SQL - Full Course - Database Systems - View Tables

Databases are designed to be optimal, with fast queries, provide minimal storage. This design, however, may not be very useful for people using the database or programmers trying to get data to present it a certain way on a website. Let’s say you had a list of contact names and addresses in one table, but all their phone numbers were in a different table, a view table would solve this problem by creating results that combine both tables. Views can restructure table columns and data types without changing the underlying database design. A view table is the table name from a SELECT statement (The SELECT statement is also called a view query). It is not stored in the database, but provides useful information to the user to view. A base table is the table or tables that are specified in the view query’s FROM clause. A table specified in the view query's FROM clause is called a base table. Views have advantages of protecting your sensitive data, by allowing you to exclude columns with sensitive information like your SSN or credit card numbers. They also allow you to save complex or optimized queries, by querying view tables instead of base tables for future queries, saving time. To create a view, use the CREATE VIEW statement, followed by AS, and then your SELECT query. CREATE VIEW Contacts AS SELECT r.Name, p.Phone AS MobilePhone FROM Rappers r, Phones p WHERE r.ID = p.ContactID AND p.PhoneType = ‘Mobile’; Updating views can be problematic and many relational databases don’t allow inserts, updates, and deletes on view tables. Rather than updating a view table, just create a new view. Issues cause by updating a view includes: Primary keys. Many views don’t include the primary keys from base tables, so when you try to insert may generate a NULL primary key value. So inserts are not allowed. Aggregate values. If a view query contains functions like SUM() or AVG(), then one aggregate value corresponds to many base table values. An update/insert to the view could create a new aggregate value, which needs to be converted to many base table values. The conversion is undefined, so the insert/update is not allowed. Join views. In a join view, foreign keys of one base table may match primary keys of another. A delete from a view could delete foreign key rows only, or primary key rows only, or both the primary and foreign key rows. The effect of the join view delete is undefined and is not allowed. If you are using a database that allows view updates, you may encounter an issue where a view insert or update creates a row that does not satisfy the view query WHERE clause. The inserted or updated row does not appear in the view table. From the perspective of the database user, it appears to fail even though the base tables have changed. Use the WITH CHECK OPTION clause at the end of you SQL create view statement, which causes the database to reject inserts and updates that do not satisfy the view query WHERE clause. An error message is shown instead, which explains the violation. Although not common, some databases can have a materialized view in which view data is always stored. Whenever a base table changes, the materialized view table refreshes and updates the corresponding view tables. MySQL and most other relational databases do not support materialized views. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!