Views Flashcards
what is the syntax for creating a view in sql?
create view
True or False
The CREATE VIEW clause does not return a query?
True
It creates a what can be described as a common query that gets used often
True or False
VIEWS in sql can be treated and queried much like tables themselves
True
In the FROM clause
True or False
VIEW clause store actually data like the table itself
False
VIEW don’t store data, if the table data changes the VIEW gets updated. VIEW is a virtual table.
VIEW exercise
create view ‘Client Balance’ as
SELECT
c.client_id,
c.name,
sum(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i using(client_id)
GROUP BY client_id, name
What is the SUM clause performing in this query?
SUM(invoice_total - payment_total) as balance
summing the balance for each group (group by) then summing the entire table column
Write the syntax to drop a view
drop view sales_by_client
Which is better to drop or replace a view?
replace view clause can be used to update a view over recreating a complex view from scratch.
What is the syntax for updating a view?
CREATE OR REPLACE VIEW ‘view_name’ AS
True or False
It’s a good policy to save views for future use in source control much like GitHub
True
Why can’t you use an alias in the WHERE clause?
because the alias is not an actual table, must use the calculation that generated the alias.
True or False
a VIEW with DISTINCT, Agg Funcs, GROUP BY /
HAVING, UNION are not updatable (in mysql)
True
True or False
You can delete records from view much like a table
DELETE from invoice_with_balance
WHERE invoice_id = 1
True
True or False
Sometimes altering a VIEW will remove rows from that VIEW
True
How do you prevent rows from being removed in a VIEW update?
WITH OPTION CHECK clause
prevents row from being removed or deleted
