Transcript Views, Algebra Temporary Tables

Views, Algebra
Temporary Tables
Definition of a view
• A view is a virtual table which does not physically
hold data but instead acts like a window into one
or more physical tables (known as base tables).
• To the end user, a view looks like a table and can
generally be treated as a table.
CREATE VIEW <view_name> [ (<column_list>) ]
AS
SELECT <statement>
[ (WITH CHECK OPTION); ]
Uses of views
• Save the user time if a complex query needs
to be re-executed.
• Restrict data that user can access. Such
restrictions might occur : – on records,
– on attributes (columns) or
– on both (security feature).
Notes
• The AS SELECT clause can use all variations of
the standard SELECT statement except cannot use
UNION or ORDER BY.
• This means that a view can be based on a
combination of tables and / or views.
• The 'data' in views can be listed by using a
SELECT statement.
• Mathematical functions can be used in creation of
views.
Non-base fields
•
•
•
•
•
Derived fields
Sum
Average
Count
Summing group fields
View from Northwind
Create view ProductIncome as
Select ProductID,
cast(sum((unitPrice*Quantity)-Discount) as
decimal(10,2))
as cost from [order details] group by ProductID
Deleting Views
• DROP VIEW <viewname>
• N.B If you have views created on tables,
don't delete the tables without first deleting
the views
• Note – create view must be the first line in a
query. If it is not, use ‘GO’ before it.
Queries and Relational Algebra
Databases
The Relational Algebra
• 5 main operations
–
–
–
–
–
Selection
(Unary operation – on one table)
Projection
(Unary operation – on one table)
Cartesian Product
(Binary operation >1 table)
Union
(Binary operation >1 table)
Set Difference
(Binary operation >1 table)
• Plus derivable:
– Join
– Intersection
Query essentials
• Selection
– This is where rows from a table are extracted on the
basis of the value of one or more of the fields
• Projection
– This is where columns from a table are extracted on the
basis of the value of one or more of the fields
• Join
– This is where rows and columns from more than one
table are extracted
Selection
– “The Selection operation works on a single relation R
and defines a relation that contains only tuples of R that
satisfy the specified condition (predicate)”
• Example:
Print ' '
Print ‘***************************************'
Print 'Selection on relation STOCK :predicate Stock_level > 30'
Print '***************************************'
Print ' '
Select * from stock where stock_level > 30
Projection
– “The projection operation works on a single relation R and
defines a relation that contains a vertical subset of R,
extracting the values of specified attributes and eliminating
duplicates.”
Print 'Projection on relation STOCK taking only stock code,
description and stock level'
Print '*************************************************'
Print ' '
Select Stock_code, Stock_Description, Stock_Level from Stock
Cartesian product
– The Cartesian product of
Order_Line x SupplierOrderLine
is the table whose attribute set contains the fully
qualified attribute names of all attributes from
both tables.
• Set up a query with * from both tables.
Select * from Order_Line, SupplierOrderLine
Equi-join
– Rows are combined if there are equal values in
certain selected columns from each table.
• Select * from Stock join Supplier on
Stock.Supplier_Id = Supplier.Supplier_Id
Natural join
• This is where two tables may have several
columns in common.
• ALL common columns are included in the join.
The tables need not be related.
Select * from Order_Line join SupplierOrderLine
on SupplierOrderLine.Stock_Code = Order_Line.Stock_Code
• Note: Natural joins can be implemented using
‘where’
Select * from Order_Line, SupplierOrderLine where
order_line.stock_code = SupplierOrderLine.stock_code
Intersection
• The intersection can be derived using a
natural or equi-join.
Select order_line.stock_code,order_line.quantity,
supplierorderline.stockrequired
from order_line join supplierorderline on
order_line.stock_code = supplierorderline.stock_code
Theta join
• This is similar to an equi-join but the condition
can be other than =. It can be:–
–
–
–
–
–
equal to
not equal to
less than
less than or equal to
greater than
greater than or equal to
Sample Theta Join
Select customer_amount_owing, customer_name,
payment_amount from jcustomer
join payment on
jcustomer.customer_amount_owing <
payment.payment_amount
Union
– S and T are tables with the same attributes
– union S  T includes all rows from S and all rows from
T
–
–
–
–
Print ' '
Print '*****************************************'
Print 'Union of relations Order_Line and SUPPLIEROrderLine '
Print '***Note that the SupplierOrderNo is coming out under the
Quantity heading'
– Print '*******************************************'
– Print ' ';
– Select * from Order_Line union Select * from
SupplierOrderLine
Null value
• Null represents a value for an attribute that
is currently unknown or is not applicable for
this tuple.
• When checking for null, you should not use
‘=’ or ‘like’
• When checking for null, use
– Is null
– Is not null
Semi joins
• This is where all of the rows from one table and
any matching rows from the other table are shown.
• In a join the left table is the table on the left – i.e
the one that is mentioned first, before the word
join.
• The right table is the table that comes after the
word join.
• An outer join can be a left join or a right join.
Semi joins contd.
• A left join returns all rows from the left table and
matching rows from the right.
• A right join returns all rows from the right table
and matching rows from the left.
• Where there are no matching rows, NULL values
are returned.
Select * from stock left join order_line on stock.stock_code
= order_line.stock_code;
Difference
• The difference between sets A and B is the
rows that appear in A, but not B.
• The difference between STOCK and
ORDER_LINE is the stock that has never
been ordered.
• Difference can be returned one of two ways:
– Using an outer join
– Using a sub-query
Difference with outer join
• Find all stock items and the orderlines for
them.
• Eliminate any rows that have a non-null
stock code.
Select stock.* from stock
left join order_line
on stock.stock_code = order_line.stock_code
where order_line.stock_code is null;
Difference with sub-query
• The inner query returns stock codes that
have been ordered.
• The outer query selects stock code that are
not in the returned set of ordered stock
codes.
Select * from stock where stock_code not in
(Select Stock_code from order_line)
Full Outer Join
• This is where the tables are joined, but if there is a
row in either table that has no corresponding row
in the other table, the attributes are shown as null.
• E.g. Match stock that was bought in from the
supplier against stock that was sold:
select order_line.stock_code as 'sold',order_line.quantity,
supplierorderline.stock_code as 'bought',
supplierorderline.stockrequired
from order_line full outer join supplierorderline on
order_line.stock_code = supplierorderline.stock_code