Paul Mark’s Blog

Internet Technology Coverage and Pointers

Select 

SELECT [DISTINCT | ALL]
  {* | [columnExpression [AS newName]] [,…] }
FROM  TableName [alias] [, …]
[WHERE  condition]
[GROUP BY  columnList]  [HAVING  condition]
[ORDER BY  columnList]

 

FROM        Specifies table(s) to be used.
WHERE        Filters rows.
GROUP BY    Forms groups of rows with same
                column value.
HAVING        Filters groups subject to some
                condition.
SELECT        Specifies which columns are to
                appear in output.
ORDER BY     Specifies the order of the output.
Order of the clauses cannot be changed.
Only SELECT and FROM are mandatory.

Use of DISTINCT 
List the property numbers of all properties that have been viewed.
Use DISTINCT to eliminate duplicates:

Calculated Fields
To name column, use AS clause:

Range Search Condition 

Also a negated version NOT BETWEEN.
BETWEEN does not add much to SQL’s expressive power.
Useful, though, for a range of values.

SELECT Statement - Aggregates

ISO standard defines five aggregate functions:

COUNT returns number of values in specified column.
SUM    returns sum of values in specified column.
AVG    returns average of values in specified column.
MIN    returns smallest value in specified column.
MAX    returns largest value in specified column.

Each operates on a single column of a table and returns a single value.
COUNT, MIN, and MAX apply to numeric and non-numeric fields, but SUM and AVG may be used on numeric fields only.
Apart from COUNT(*), each function eliminates nulls first and operates only on remaining non-null values. 

COUNT(*) counts all rows of a table, regardless of whether nulls or duplicate values occur.
Can use DISTINCT before column name to eliminate duplicates.
DISTINCT has no effect with MIN/MAX, but may have with SUM/AVG.

Aggregate functions can be used only in SELECT list and in HAVING clause.

If SELECT list includes an aggregate function and there is no GROUP BY clause, SELECT list cannot reference a column out with an aggregate function. For example, the following is illegal:

SELECT Statement - Grouping

All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function.
If WHERE is used with GROUP BY, WHERE is applied first, then groups are formed from remaining rows satisfying predicate.
ISO considers two nulls to be equal for purposes of GROUP BY.

Restricted Groupings – HAVING clause

HAVING clause is designed for use with GROUP BY to restrict groups that appear in final result table.
Similar to WHERE, but WHERE filters individual rows whereas HAVING filters groups.
Column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function.

 Subqueries

Some SQL statements can have a SELECT embedded within them.
A subselect can be used in WHERE and HAVING clauses of an outer SELECT, where it is called a subquery or nested query.
Subselects may also appear in INSERT, UPDATE, and DELETE statements.

Subquery Rules

ORDER BY clause may not be used in a subquery (although it may be used in outermost SELECT).

Subquery SELECT list must consist of a single column name or expression, except for subqueries that use EXISTS.

By default, column names refer to table name in FROM clause of subquery. Can refer to a table in FROM using an alias.

When subquery is an operand in a comparison, subquery must appear on right-hand side.

A subquery may not be used as an operand in an expression.

ANY and ALL

ANY and ALL may be used with subqueries that produce a single column of numbers.

With ALL, condition will only be true if it is satisfied by all values produced by subquery.

With ANY, condition will be true if it is satisfied by any values produced by subquery.

If subquery is empty, ALL returns true, ANY returns false.

SOME may be used in place of ANY.

Multi-Table Queries

Can use subqueries provided result columns come from same table.

If result columns come from more than one table must use a join.

To perform join, include more than one table in FROM clause.

Use comma as separator and typically include WHERE clause to specify join column(s).

Also possible to use an alias for a table named in FROM clause.

Alias is separated from table name with a space.

Alias can be used to qualify column names when there is ambiguity.

Also possible to use an alias for a table named in FROM clause.

Alias is separated from table name with a space.

Alias can be used to qualify column names when there is ambiguity.
 
Alternative JOIN Constructs

SQL provides alternative ways to specify joins:

    FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
    FROM Client JOIN Viewing USING clientNo
    FROM Client NATURAL JOIN Viewing

In each case, FROM replaces original FROM and WHERE. However, first produces table with two identical clientNo columns.

Computing a Join

Procedure for generating results of a join are:

1. Form Cartesian product of the tables named in  FROM clause.

2. If there is a WHERE clause, apply the search condition to each row of the product table, retaining those rows that satisfy the condition.

3. For each remaining row, determine value of each item in SELECT list to produce a single row in result table.

4. If DISTINCT has been specified, eliminate any duplicate rows from the result table.

5. If there is an ORDER BY clause, sort result table as required.

Outer Joins

If one row of a joined table is unmatched, row is omitted from result table.
Outer join operations retain rows that do not satisfy the join condition.

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS are for use only with subqueries.

Produce a simple true/false result.

True if and only if there exists at least one row in result table returned by subquery.

False if subquery returns an empty result table.

NOT EXISTS is the opposite of EXISTS.

As (NOT) EXISTS check only for existence or non-existence of rows in subquery result table, subquery can contain any number of columns.

Common for subqueries following (NOT) EXISTS to be of form:

        (SELECT * …)

Union, Intersect, and Difference (Except)

Can use normal set operations of Union, Intersection, and Difference to combine results of two or more queries into a single result table.
Union of two tables, A and B, is table containing all rows in either A or B or both.
Intersection is table containing all rows common to both A and B.
Difference is table containing all rows in A but not in B.
Two tables must be union compatible.

Format of set operator clause in each case is:

op [ALL] [CORRESPONDING [BY {column1 [, …]}]]

If CORRESPONDING BY specified, set operation performed on the named column(s).
If CORRESPONDING specified but not BY clause, operation performed on common columns.
If ALL specified, result can include duplicate rows.

INSERT

INSERT INTO TableName [ (columnList) ]
VALUES (dataValueList)

columnList is optional; if omitted, SQL assumes a list of all columns in their original CREATE TABLE order.
Any columns omitted must have been declared as NULL when table was created, unless DEFAULT was specified when creating column.

dataValueList must match columnList as follows:
number of items in each list must be same;
must be direct correspondence in position of items in two lists;
data type of each item in dataValueList must be compatible with data type of corresponding column.

UPDATE

UPDATE TableName
SET columnName1 = dataValue1
        [, columnName2 = dataValue2…]
[WHERE searchCondition]

TableName can be name of a base table or an updatable view.
SET clause specifies names of one or more columns that are to be updated.

WHERE clause is optional:
if omitted, named columns are updated for all rows in table;
if specified, only those rows that satisfy searchCondition are updated.
New dataValue(s) must be compatible with data type for corresponding column.

DELETE 

searchCondition]

TableNDELETE FROM TableName
[WHERE ame can be name of a base table or an updatable view.
searchCondition is optional; if omitted, all rows are deleted from table. This does not delete table. If search_condition is specified, only those rows that satisfy condition are deleted.

Introduction to .NET

Common Language Runtime (CLR)

◘ NET runtime execution environment
◘ Code running under the control of the CLR is often termed managed code

Before any code can be executed by the CLR, it needs to be compiled first
Compilation of code occurs in two steps:
Compilation of source code to IL
Compilation of IL to platform-specific code by the CLR

◘Advantages of Managed Code

Platform independence
Performance improvement
IL is Just-In-Time (JIT) compiled
Language interoperability
Code compiled in IL by one language is interoperable with code compiled to IL by another language
Common languages aside from C# that are interoperable with .NET include Visual Basic .NET, Visual C++ .NET, Visual J# .NET

◘A Closer Look at Intermediate Language (IL)

Important features
Object-orientation and use of interfaces
Strong distinction between value and reference types
Strong data typing
Error handling through the use of exceptions
Use of attributes

◘C# Basics

Sample C# Program

using System;

namespace MyApplication
{
    class Program
    {
        static void Main(string args[])‏
        {
            Console.WriteLine(“Another Hello World example”);
        }
    }
}

◘Application Startup

The application’s entry point is the Main method which can take on one of the four signatures:
static void Main() { … }
static void Main(string[] args { … }
static int Main() { … }
static int Main(string[] args { … }

◘Variables

What is a Variable?
A variable represents a storage location and has a type that determines what values can be stored in it
A local variable is a variable declared in function members (for example, methods, properties, and indexers)‏
A field is a variable associated with a class or struct or an instance of a class or struct.

◘Declaration
A variable is declared as follows:

[<modifiers>] <type-name> <variable-name> [= <value>];

Example:

int i;
int x = 1, y = 2;            // valid: x and y are int
int x = 1, bool isOK = true; // invalid: different types

◘Initialization
Field variables, if not initialized explicitly, are zeroed out during creation

class Book
{
    int pages;    // automatically initialized to 0
}

Local variables must be explicitly initialized

static void Main(string[] args)‏
{
    int x;

    Console.WriteLine(x);  // invalid: x is not initialized
}

◘Scope
A field is in scope for as long as its enclosing class
A local variable is in scope within the block statement or method to which it was declared
A local variable declared in a for, while, or similar statement is in scope within the body of the loop

Local variables with the same name can’t be declared twice in the same scope‏
public class Program
{
    static void Main(string[] args)‏
    {
        int j = 20;

        for (int i = 0; i < 10; i++)‏
        {
            int j = 30;   // invalid: j already declared
            Console.WriteLine(j + i);
        }
    }
}

◘C# makes a fundamental distinction between variables declared at the type level (fields) and variables declared within methods (local variables)‏

public class Book
{
    int pages;

    public Book(int pages)‏
    {
        this.pages = pages;   // ‘this’ refers to the field
    }
}

◘Constants
A constant is a class member that, as the name suggests, is used to represent a constant value

Must be initialized during creation and the value cannot be overridden once assigned

const int MaximumValue = 100;  // value cannot be changed

The value must be computable at compile time.
If initializing with a value taken from a variable, use a readonly field

int x = 100;
const int MaximumValue = x;  // invalid

Always implicitly static

static const int MaximumValue = 100;  // invalid

◘Types

What is a Type?
A type is how a programming language classifies different values and expression
There are two categories of data types:
Value types
Reference types

◘Value Types

Stores value directly
Stored in the stack

Example:

  int i = 20;
  int j = i;  // results in two locations in memory that
              // contain the value 20

◘Reference Types
Stores a reference to the value
Stored in the managed heap

Example:

  Vector x, y;
  x = new Vector();  // creates a Vector object in memory
  x.Value = 30;
  y = x;             // y points to the same Vector object
                     // referenced by x
  y.Value = 50;      // modifies Vector object referenced by
                     // x and y

◘Enumerations

What is an Enumeration?
An enumeration user-defined integer type that specify a set of acceptable values

◘Declaration
An enum declaration is used to declare new enum types

  [<modifiers>] enum <enum-name> [: <type-name>]
  {
      <enum-member1> [= <constant-value>],
      …
      <enum-membern> [= <constant-value>]
  }

  *type-name defaults to int if not explicitly defined

Example:

  enum BookType
  {
      None,
      Fiction = 10,
      NonFiction = 20,
      History = 30,
      Science = 40
  }

◘Operations

  static void Main(string[] args)‏
  {
      // Simple value assignment
      BookType bookType = BookType.NonFiction;

      // Output the string representation of an enum value
      Console.WriteLine(bookType.ToString());

      // Assign a value expressed as a string to an enum
      bookType = (BookType) Enum.Parse(
          typeof(BookType),
          “nonfiction”,
          true);
  }

◘Arrays

What is an Array?
An array is a data structure used to hold a number of variables accessed through an index.
This index is a number that corresponds to the position of the data within the array

◘Arrays

Declaration:

   int[] integers;
   int[] integers = new int[5];
   int[] integers = new int[] {1,2,3,4,5};

Accessing individual elements:

   integers[2] = 25;
   Console.WriteLine(integers[2]);

◘Flow Control

Conditional Statements(if statement)

Similar to the C, C++ and Java if…else construct

if ( condition )‏
{
    statement(s);
}
else
{
    statement(s);
}

◘Conditional Statements(switch statement)

Similar to the C, C++ and Java construct with the exception that it prohibits fall-through conditions in almost all cases

switch (variable)‏
{
    case value1:
        // Fall-through is allowed here since there is
        // no implementation
    case value2:
        statement(s);
        // Fall-through not allowed and should use goto
        goto case value3;
    case value3:
        statement(s);
        break;
}

◘Loop Statements(for loop)

Similar to the C++ and Java construct

for (initializer; condition; iterator)
    statement(s);
}

◘Loop Statements(while loop)

Similar to the C++ and Java construct

while (condition)
{
    statement(s);
}

◘Loop Statements(do…while loop)

Similar to the C++ and Java construct
 
  do
{
    statement(s);
}
while (condition)

◘Loop Statements(foreach loop)

New construct that allows iterating through each element in a collection
The value of the element cannot be changed

foreach (<data-type> <variable> in <collection>)‏
{
    statement(s);
}

Example:

string[] stringArray = new string[] {
    “string1”, “string2”, “string3”, “string4”
}

foreach (string s in stringArray)
{
    Console.WriteLine(s);
}

◘Jump Statements

goto statement
Jump directly to another specified line in the program indicated by a label
break statement
Exit from a case in a switch statement or from an innermost loop
continue statement
Exit from the current iteration of the loop
return statement
Exit a method of a class, returning control to the caller of the method
‏