Views, Algebra

1. Views, Algebra Temporary Tables

2. 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); ]

3. Uses of views • Save the user time if a complex query needsto be re-executed. • Restrict data that user can access. Such restrictions might occur : - • on records, • on attributes (columns) or • on both (security feature).

4. 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 creationof views.

5. Non-base fields • Derived fields • Sum • Average • Count • Summing group fields

6. 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

7. 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.

8. Queries and Relational Algebra Databases

9. 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

10. 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

11. 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

12. 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

13. 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

14. 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

15. 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

16. 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

17. 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

18. Sample Theta Join Select customer_amount_owing, customer_name, payment_amount from jcustomer join payment on jcustomer.customer_amount_owing < payment.payment_amount

19. 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

20. 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

21. 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.

22. 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;

23. 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

24. 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;

25. 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)

26. 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