CIS 3050 Chapter 6

Join

A relational operation that causes two tables with a common domain to be combined into a single table or view.

Equi-join

A join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table.

Natural join

A join that is the same as an equi-join except that one of the duplicate columns is eliminated in the result table.

Outer join

A join that returns all the values in one of the tables included in the join, regardless of whether or not a match exists in the other table.

Self join

A join that joins a table with itself

Subquery (technique)

A subquery in which processing the inner query depends on data from the outer query.

involves placing an inner query (SELECT . . . FROM . . . WHERE) within a WHERE or HAVING clause of another (outer) query. Can be noncorrelated or correlated.

Noncorrelated subquery

• Do not depend on data from the outer query

• Execute once for the entire outer query

Correlated subquery

• Makes use of data from the outer query

• Executes once for each row of the outer query

• Can use the EXISTS and ALL operators

UNION join/clause

Used to combine the output from multiple queries together into a single result table. returns a table that includes all data from each table that was joined.

CAST( ) function

Command that converts a value (of any type) into a specified datatype

CASE expression

It allows you to choose what will appear in a column of the result set, depending on a condition. It acts like an if-else statement.

Derived table

A subquery in the FROM clause of the outer query

Views

a mechanism that allows the creation of virtual tables on the foundation of the base tables.

Base table

A table in the relational data model containing the inserted raw data. These tables correspond to the relations that are identified in the database's conceptual schema

Virtual table

A table constructed automatically as needed by a DBMS. These tables are not maintained as real data.

Dynamic view

A “virtual table” created dynamically on request by a user. No data actually stored; instead data from base table made available to user. Based on SQL SELECT statement on base tables or other views

Materialized view

Copy or replication of data, data actually stored. Must be refreshed periodically to match corresponding base tables

Tips for Developing Queries

• Be familiar with the data model (entities and relationships)

• Understand the desired results

• Know the attributes desired in results

• Identify the entities that contain desired attributes

• Review ERD

• Construct a WHERE equality for each link

• Fine tune with GROUP BY and HAVING clauses if needed

• Consider the effect on unusual data

Trigger

user-defined functions that run automatically when specific conditions are fulfilled at the time when records are INSERTed, UPDATEd, or DELETEd.

Guidelines for Better Query Design

• Understand how indexes are used in query processing

• Keep optimizer statistics up to date

• Use compatible data types for fields and literals

• Write simple queries

• Break complex queries into multiple simple parts

• Don’t nest one query inside another query

• Don’t combine a query with itself (if possible avoid selfjoins)

• Create temporary tables for groups of queries

• Combine update operations

• Retrieve only the data you need

• Don’t have the DBMS sort without an index

• Learn!

• Consider the total query processing time for ad hoc queries

Advantages of Dynamic Views

• Simplify query commands

• Assist with data security

• Enhance programming productivity

• Contain most current base table data

• Use little storage space

• Provide customized view for user

• Establish physical data independence

• Use processing time each time view is referenced

• May or may not be directly updateable

• As with all SQL constructs, you should use views with discretion

Routines

Program modules that execute on demand

Functions

routines that return values and take input parameters

Procedures

routines that do not return values and can take input or output parameters, called by name.

Persistent Stored Modules (SQLPSM)

Extensions defined originally in SQL:1999 that include the capability to create and drop modules of code stored in the database schema across user sessions.

Advantages of SQL-invoked routines

• Flexibility

• Efficiency

• Sharability

• Applicability

User-defined data type (UDT)

A data type that a user can define by making it a subclass of a standard type or an object type

Analytical functions (for OLAP)

• CEILING, FLOOR, SQRT, RANK, DENSE_RANK, ROLLUP, CUBE, SAMPLE

• WINDOW – improved numerical analysis capabilities

New Data Types

BIGINT, MULTISET (collection), XML

CREATE TABLE LIKE

create a new table similar to an existing one