CH 8

SQL

Why should you join tables together?

The true power of a relational database is its ability to combine records from different tables into a new virtual record. The process of combining multiple tables is called joining.

How do you join tables together?

Records are joined or matched through the use of common columns.These columns are usually the primary and foreign-key columns in the joined tables.

Two steps to join tables

Place the names of the tables in the FROM clause. The table names must be separated by commas.
Add a condition to the where clause, describing how the tables are related.

SQL Join Operators

Join operation merges rows from two tables and returns the rows with one of the following:

Have common values in common columns (Natural join)
Meet a given join condition (Equality or inequality)
Have common values in common columns or have no matching values (Outer join)
Only returns rows meeting criteria (Inner join)

Types of joins

1.Equi Join
Natural Join
Returns all rows with matching values in the matching columns
Eliminates duplicate columns
Used when tables share one or more common attributes with common names

Syntax: 
SELECT column-list FROM table1 NATURAL JOIN table2
SELECT column-list FROM table 1, table 2 WHERE table1.column = table2.column

JOIN USING Clause

Returns only rows with matching values in the column indicated in the USING clause

Syntax:
SELECT column-list FROM table1 JOIN table2 USING (common-column)

JOIN USING operand does not require table qualifiers
Oracle returns error if table name is specified

JOIN ON Clause

Used when tables have no common attributes
Returns only rows that meet the join condition
Typically includes equality comparison expression of two columns

Syntax: 
SELECT column-list FROM table1 JOIN table2 ON join-condition

What about duplicate column names?

Fully qualify column names when tables share the same column names (e.g. cus_code)
Is this invoice cus_code or customer cus_code?
Need to fully qualify the column so the analyzer knows which column to display (e.g. invoice.cus_code and customer.cus_code)
Isn’t that a lot of typing?
To minimize typing, use aliases
Invoice i and customer c
i.cus_code and c.cus_code

2.Non-Equi Join

Joins records from two tables that do not have common columns and values.
An operator rather than the equal sign is used.
Same as Cross Join

Syntax: 
SELECT column-list FROM table 1, table 2 WHERE table1.column [between, >, <] table2.column
Outer Joins

Returns rows matching the join condition
Also returns rows with unmatched attribute values for tables to be joined

Three types

Left
Right
Full

Left and right designate order in which tables are processed

Outer Joins

Left outer join

Returns rows in left side table with unmatched values
Returns rows matching the join condition

Syntax: SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition

Right outer join

Returns rows matching join condition
Returns rows in right side table with unmatched values
Outer Joins
Full outer join
Returns rows matching join condition
Returns all rows with unmatched values in either side table

Syntax:
SELECT  column-list
FROM    table1 FULL [OUTER] JOIN table2
            ON join-condition

SELF Joins

Returns rows from the same table.
Example: employee table that contains a column for the employee’s supervisor.
When joining a table to itself you are listing the same table multiple times in the FROM clause
Requires a table alias

Syntax:
SELECT  column-list
FROM    table1 alias, table2 alias
WHERE   alias.1column_name = alias2.column_name

Cross Join

Performs relational product of two tables
Also called Cartesian product
Same as Non-Equi Join
Joins each record in the first table to every record in the second

Syntax:
SELECT column-list FROM table1 CROSS JOIN table2
SELECT column-list FROM table 1, table 2 (no where clause specified)

Quick tip

Always check your WHERE clause.
You should have one less join condition than the number of tables listed in the FROM clause.
Good practice to test your join expressions before adding additional expressions and clauses to your statement.

Aliases

Column Alias

Allow you to change the column’s heading in the output.
SELECT columnname AS alias

Table Alias

Allow you to avoid lengthy table names, helpful when joining tables.
Table aliases can be used as qualifiers for column names in the select clause

SELECT column-list FROM tablename alias
Example: 
SELECT name FROM employee e
SELECT e.name, e.salary FROM employee e, payroll p 
WHERE e.id = p.id 

Powerful tool for enhancing basic queries.
Takes 0 or more arguments and returns a single value.
Used for performing calculations, converting data, modifying data, manipulating rows, formatting columns.

Types of Built-in Functions

Single-row functions
Work on columns from each row and return one result per row.
Group functions or Aggregate functions
Manipulate data in a group of rows and return a single result.

SQL Functions

Generating information from data often requires many data manipulations
SQL functions are similar to functions in programming languages
Functions always use numerical, date, or string value
Value may be part of a command or attribute in a table
Function may appear anywhere in an SQL statement

Single-Row Functions

Work on data items from each row
Return one value for each row
Types of single-row functions

Character Functions
Number Functions
Date Functions
Conversion Functions
General Functions
Character Functions

String manipulations are the most used functions in programming
String manipulation function examples:
Concatenation
Printing in uppercase or lowercase
Finding length of an attribute

CONCATENATION
UPPER/LOWER
SUBSTR
LENGTH

Numeric Functions

Numeric functions take numeric values and return a numeric value.
Common numeric functions

TRUNC
POWER
ABS
MOD
SIGN
FLOOR
CEIL
ROUND
CEIL/FLOOR

Date and Time Functions

All SQL-standard DBMSs support date and time functions
Date functions take one parameter and return a value
Date/time data types are implemented differently by different DBMS vendors
ANSI SQL standard defines date data types, but not how data types are stored
Oracle stores dates internally with day, month, year, century, hour, minute, and second information. The default date display format is DD-MON-YY.
SYSDATE – common date function, doesn’t take any arguments, returns system’s current date.

TO_CHAR
TO_DATE
ADD_MONTHS
LAST_DAY

Conversion Functions

Take a value of given data type and convert it to the equivalent value in another data type
Oracle conversion functions:
TO_CHAR: takes a date value, converts to character string
TO_DATE: takes character string representing a date, converts it to actual date in Oracle format
SQL Server uses CAST and CONVERT functions

TO_NUMBER

General Functions

NVL: converts a null value to an actual value supplied as an argument

SYNTAX: NVL(column, value)

NVL2: checks for null as well as not null values

SYNTAX: NVL2(column, notnullvalue, nullvalue)

DECODE: conditional statement, tests a column or expression and for each of tis matching value, provides an action.

Group or Aggregate Functions

Perform an operation on a group of rows and return one result.
Multi-row functions.

Aggregate Functions

AVG compute average value for a table or group

COUNT function tallies number of non-null values of an attribute

Takes one parameter: usually a column name

MAX and MIN find highest (lowest) value in a table or group

Compute MAX value in inner query

Compare to each value returned by the query

STDDEV computes the standard deviation for a table or group

SUM computes total sum for any specified attribute

VARIANCE computes variance of the table or group

Grouping Data

Works on data that is grouped.
Tell the database how to group or categorize the data with ###a GROUP BY clause.
Used with Aggregate functions.

Syntax:
SELECT  columnlist
FROM    tablelist
[WHERE  conditionlist]
[GROUP BY   columnlist]
[HAVING conditionlist]
[ORDER BY   columnlist [ASC | DESC] ] ;

Having Clause
Restricts groups.
The WHERE clause restricts rows, the GROUP BY clause groups remaining rows.
The GROUP function works on each group, and the HAVING clause keeps the groups that match the group condition.
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Grouping Data
Important tips:

• When using a group function and the GROUP BY clause, the individual column(s) in the SELECT must also appear in the GROUP BY.

• The WHERE clause can still be used to restrict data before grouping.

• The WHERE clause cannot be used to restrict groups.
  A column alias cannot be used in a GROUP BY clause.

• The GROUP BY column does not have to appear in a SELECT query.

• When a column is used in the GROUP BY clause, the result is sorted in ascending order by that column by default. GROUP BY has an implied ORDER BY.

subqueries

Subqueries and Correlated Queries
Often necessary to process data based on other processed data
Subquery is a query inside a query, normally inside parentheses

• First query is the outer query

• Inside query is the inner query
  Inner query is executed first

• Output of inner query is used as input for outer query
  Sometimes referred to as a nested query

Subqueries Can be used within a WHERE, HAVING, FROM, or SELECT

2 types of subqueries

Single-row: a subquery that returns only one row of data
Multiple-row: a subquery that returns more than one row of data

Single-Row subquery
Subquery must be enclosed within parentheses.
Returns one column form one row, of no row is returned the value is NULL. If more than one row returned an error occurs.
ORDER BY clause cannot be used in a subquery.

Common uses of subqueries:
As a conditional expression in the WHERE clause or HAVING clause
Create a table using a subquery: create table and populate with data
Insert data into a table using a subquery
Update data in a table using a subquery: update table set columnname = value where operator (select subquery)
Delete date from a table using a subquery

WHERE Subqueries
Most common type uses inner SELECT subquery on right side of WHERE comparison
Requires a subquery that returns only one single value
Value generated by subquery must be of comparable data type
Can be used in combination with joins
syntax 补充

HAVING Subqueries
HAVING clause restricts the output of a GROUP BY query
Applies conditional criterion to the grouped rows
Syntax 补充

Multirow Subquery
Returns more than one row
Uses special operators
IN

IN Subqueries
Used when comparing a single attribute to a list of values
Syntax 补充

FROM Subqueries
Specifies the tables from which the data will be drawn
Can use SELECT subquery in the FROM clause
View name can be used anywhere a table is expected
The subquery is similar to a virtual table or temporary table.
The subquery is executed first, creating a temporary instance of the table.
The temporary or virtual table can be joined with other tables.
syntax 补充

Attribute List Subquery
SELECT statement uses attribute list to indicate columns to include in the result set.
Columns can be attributes of base tables
Result of aggregate function
Attribute list can also include subquery expression: inline subquery
Must return one value; otherwise an error code is raised.
Good for computations on each row.
Cannot use an alias in the attribute list
SQL Example File
Syntax

SELECT standard_disclaimer FROM company_stuff WHERE meaningful_content = 'NONE'; 
SELECT * FROM management WHERE clue > 0; 
SELECT * FROM client WHERE clue > 0; 
SQL> select count(*) from developer_brain; 
 select count(*) from developer_brain 
                      * 
ERROR at line 1: ORA-00942: table or view does not exist 

Correlated Subqueries
Inner query can reference columns form the outer query.
Inner query executes once for each row in the outer query.
Correlated because inner query is related to the outer query
Inner query references column of outer subquery
Similar to a nested loop.
Can also be used with the EXISTS special operator

Syntax

Tips for Writing SQL

• Make sure tables are joined together properly
• Execute subqueries on their own, make sure they are returning the correct result set
• Add the where clause only after the tables are joined together properly
• When using group by, make sure the group by statement works before adding the having clause
• Add the order by clause last

Quick tip

• Using a function as an argument in a WHERE clause can affect performance.
• Indexes are often placed on table columns used for selection criteria to help Oracle identify records more efficiently.
• If a query uses a function on a table column, it is changing its format.
• This means Oracle cannot use the table column’s index.

Relational Set Operators

Keywords that are used to combine records from different result sets.

SELECT statements produce the record sets that the set operators combine.

Relational Set Operators

UNION
INTERSECT
MINUS

Work properly if relations are union-compatible
Names of relation attributes must be the same and their data types must be identical

SET Operator rules

The SET operator is placed between the two SELECT statements.
Each of the SELECT clauses must have the same number of expressions.
Each expression in the SELECT clause must have the same data type as the corresponding expression in the other SELECT clauses.
Each SELECT statement is a part of one overall statement.

UNION

Combines rows from two or more queries without including duplicate rows

//Example:
SELECT  CUS_LNAME, CUS_FNAME,           CUS_INITIAL, CUS_AREACODE, 
FROM        CUSTOMER
UNION
SELECT  CUS_LNAME, CUS_FNAME,           CUS_INITIAL, CUS_AREACODE, 
FROM        CUSTOMER_2
//Can be used to unite more than two queries

UNION ALL

Produces a relation that retains duplicate rows

Example query:
   SELECT   CUS_LNAME, CUS_FNAME,               CUS_INITIAL, CUS_AREACODE, FROM     CUSTOMER
UNION ALL
SELECT   CUS_LNAME, CUS_FNAME,               CUS_INITIAL, CUS_AREACODE, FROM         CUSTOMER_2; 
//Can be used to unite more than two queries

INTERSECT

Combines rows from two queries, returning only the rows that appear in both sets

//Syntax:
query INTERSECT query
Example query:
  SELECT    CUS_LNAME, CUS_FNAME,               CUS_INITIAL, CUS_AREACODE, FROM     CUSTOMER
INTERSECT
SELECT   CUS_LNAME, CUS_FNAME,               CUS_INITIAL, CUS_AREACODE, FROM         CUSTOMER_2

MINUS

Combines rows from two queries
Returns only the rows that appear in the first set but not in the second

Syntax: query MINUS query
Example:
   SELECT   CUS_LNAME, CUS_FNAME,               CUS_INITIAL, CUS_AREACODE, FROM     CUSTOMER
MINUS
    SELECT  CUS_LNAME, CUS_FNAME,               CUS_INITIAL, CUS_AREACODE, FROM         CUSTOMER_2

Oracle Sequences

MS Access AutoNumber data type fills a column with unique numeric values
Independent object in the database
Named, used anywhere a value expected
Not tied to a table or column
Generate numeric values that can be assigned to any column in any table
Created and deleted at any time

Create Sequence
View sequences (Table)
Inserting a new row into a table that uses a sequence
Delete (DROP) a Sequence

Views

Oracle object that gives the user a logical view of data.
Logical representation of a subset of data from one or more tables.
Users can view data from multiple tables without using complex join conditions.
Hide the names of the underlying tables so the user doesn’t know where the data came from.
Stored as a SELECT statement in the Data Dictionary.

Views: Why uses views?

Mask complexity
Enhance the confidentiality of data: the SELECT and WHERE clauses can filter the expressions and data available to the user.
Some information can only be produced through views (joining views)
Can be used for reports

Views

Can use the name of a view anywhere a table name is expected in a SQL statement.
Dynamically updated view is re-created on demand each time it is invoked.

Virtual Tables: Creating a View
View: Virtual table based on a SELECT query
Base tables: Tables on which the view is based

Syntax:
      CREATE VIEW viewname AS SELECT query

See all the views and the SQL query associated with that view.

SELECT * FROM ALL_VIEWS
SQL Example File
Create Views
Select data from the view

PL/SQL

Procedural Language SQL
- Makes it possible to use and store procedural code with SQL statements.
- Merge SQL and traditional programming constructs such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR WHILE) and error trapping.
- Used to create Anonymous PL/SQL blocks, Triggers, Stored Procedures, PL/SQL functions

PL/SQL guidelines

• The executable portion of a PL/SQL code block starts with the keyword BEGIN and is terminated with the keyword END.
• Each statement ends with a semicolon.
• Code blocks are followed by a slash (/) in the first position of the following line. This causes the code block statements to be executed
• The END keyword is the only PL/SQL code block keyword that is followed by a semicolon.

Pl/sql block

Why use Pl/SQL?
Isolate critical code
All applications access shared code
Better maintenance and logic control

Persistent stored module (PSM) is a block of code containing:
Standard SQL statements
Procedural extensions
Stored and executed at the DBMS server
Procedural SQL (PL/SQL) enables you to:
Store procedural code and SQL statements in database
Merge SQL and traditional programming constructs

Stored Procedures
Named collection of procedural and SQL statements.
Advantages?
Substantially reduce network traffic and increase performance.
No transmission of individual SQL statements over network
Reduce code duplication by means of code isolation and code sharing.
Minimize chance of errors and cost of application development and maintenance

Stored Procedures: Guidelines

Follows same rules as Anonymous PL/SQL block.

• Argument specifies the parameters that are passed to the stored procedure.

• IN/OUT indicates whether the parameter is for input, output, or both.

• Data-type procedural SQL datatype.
• Variables can be declared between the keywords IS and BEGIN. You must specify the variable name, datatype, and optionally an initial value.
• Stored Procedures: syntax
• Create the stored procedure.
• CREATE or REPLACE PROCEDURE procedure name AS…

To execute the stored procedure:

EXEC procedure_name(parameter_list);

Stored Procedures
Stored Procedure

PL/SQL Processing with Cursors

What if the SQL statement returns more than one value?
It will return an error unless you use a cursor.
Cursor: special construct in procedural SQL to hold data rows returned by SQL query (similar to an array)
Implicit cursor: automatically created when SQL returns only one value
Explicit cursor: holds the output of an SQL statement that may return two or more rows
Cursor-style processor retrieves data from cursor one row at a time
Current row is copied to PL/SQL variables

triggers

Block of PL/SQL code stored within the database.
Fired by a database transaction.
Cannot be called directly.

Triggers: syntax
Requires the following parts:
Triggering timing: BEFORE, AFTER
Triggering event: on INSERT, UPDATE, or DELETE
Triggering level: statement-level versus row-level
statement-level: executed once, before or after the triggering statement is completed.
row-level: executed once for each row affected by the triggering statement; if you update 10 rows, the trigger executes 10 times.
Triggering action: code enclosed between the BEGIN and END keywords.

Uses of triggers

When would a trigger be useful?
Generating values for derived columns.
Performing validity checks.
Implementing business rules.
Maintaining referential integrity.
Enforce constraints.
Used to provide auditing features.
Can be used to update, insert, and call other stored procedures.

##
Embedded SQL 

SQL statements contained within an application programming language such as C# or Java.
Run-time mismatch: SQL is a nonprocedural, interpreted language; each instruction is parsed, its syntax is checked, and it is executed on instruction at a time. All of the processing takes place at the server.
Data type mismatch: SQL provides several data types, but some of them might not match data types used in different host languages.

Embedded SQL framework defines:
Standard syntax to identify embedded SQL code within host language
Standard syntax to identify host variables
Communication area exchanges status and error information between SQL and host language
Embedded SQL
Static SQL
Embedded SQL in which programmer uses predefined SQL statements and parameters.
End users of programs are limited to actions that were specified in application programs
SQL statements will not change while application is running.
Embedded SQL

Dynamic SQL
SQL statement is not known in advance, but instead is generated at run time
Program can generate SQL statements at run-time that are required to respond to ad hoc queries
Attribute list and condition are not known until end user specifies them
Tends to be much slower than static SQL
Requires more computer resources

Summary

PL/SQL can be used to create triggers, stored procedures, and PL/SQL functions
A stored procedure is a named collection of SQL statements
When SQL statements return more than one value inside the PL/SQL code, cursor is needed
Embedded SQL uses SQL statements within an application programming language