Union intersect and except minus work properly only

This preview shows page 24 - 27 out of 30 pages.

UNION, INTERSECT, and EXCEPT (MINUS) work properly only if relations are union-compatible, which means that the number of attributes must be the same and their corresponding data types must be alike.UNIONThe UNION statement combines rows from two or more queries without including duplicate rows.The UNION statement combines the output of two SELECT queries.The UNION statement can be used to unite 2+ queries.UNION ALL
The UNION ALL statement can be used to unite 2+ queriesINTERSECTThe INTERSECT statement can be used to combine rows from 2 queries, returning only the rows that appear in both sets.Can be used to generate additional useful information.EXCEPT (MINUS)The EXCEPT statement in SQL combines rows from two+ queries and returns only the rows that appear in the first set and not the second.Syntax AlternativesYou can use IN and NOT IN subqueries to obtain similar results.4.5 Virtual Tables: Creating a ViewA view is a virtual table based on a SELECT query.The query can contain columns, computed columns, aliases, and aggregate functions from one+ tables.The tables on which the view is based are called base tables.Can be created with the CREATE VIEW command.A relational view has several special characteristics:oCan use the name of a view anywhere a table name is expected in a SQL statement.oViews are dynamically updated, the view is re-created on demand each time it is invoked.oViews provide a level of security in the database because they can restrict users to seeing only specified columns and rows in a table.oViews may also be used as the basis for reports.Updateable ViewsOne of the most common operations in production database environments is to use batch update routines to update a master table attribute with transaction data.A batch update routine is commonly used to update a product’s quantity on hand based on summary sales transactions.Typically run as overnight batch jobs to update the quantity on hand products in inventory.An updateable view can be used to update attributes in any base table used in the view.Not all views are updateable.The most common updateable views are:oGROUP BY expressions or aggregate functions cannot be used.oYou cannot use set operators such as UNION, INTERSECT, and MINUS.oMost restrictions are based on the use of JOINS or group operators in views.
Lesson 5: IndexesObjectives Covered:Explain how and why Indexes are used for query optimizationDemonstrate the ability to create indexes.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture