Java Thread of an Applet.

Threading an Applet

public class RenewApplet extends java.applet.Applet
    implements Runnable
{
    Thread thread;
    boolean running;
    int renewInterval = 500;

    public void run() {
        while ( running ) {
            redoAction ();
            try {
                Thread.sleep( renewInterval );
            }
            catch ( InterruptedException e) {
                System.out.println( "Interrupted..." );
                return;
            }
        }
    }

    public void start() {
        if ( !running ) {
            running = true;
            thread = new Thread (this);
            thread.start();
        }
    }

    public void stop() {
        thread.interrupt();
        running = false;
    }
}

C++ Compile, Link Process and Characters.

C++ compilation is a two-step process. First, the source code is compiled into an object file that contains the machine code equivalent of the source file. Secondly, the linker combines the object files for a program into a file containing the complete executable program. The linker will also integrate any functions from the Standard Library used in the second step.

Imagining the intermediate object files from each .cpp source file are similar to the Java .class files, which you then run with JVM. However, the Java compiler interprets the source code into bytecode that is OS and platform independent and without saying, is not machine code.

Similar to Java, you can compile each source file independently in separate compiler runs. This is convenient since in the coding process, there will be typographical and other errors to be coded iteratively. Even if it compiles, it may have logical errors to be revised.

Regarding Characters

Talking about computer characters, ASCII was defined in 1960s as 7-bit code so that there are 128 code values. ASCII values 0 to 31 represent non-printing control characters such as carriage return (0x0F) and line feed (0X0C). Code value 65 yo 90 are the uppercase letters A to Z and 141 to 172 correspond to the lowercase a to z. The codes for uppercase and lowercase letters are only different in the sixth bit.

Enter Universal Character Set (UCS) around 1990s to overcome the limitations of ASCII codes and extend it to include codes for foreign languages. UCS is defined to code up to 32 bits.

However, it is very inefficient to use four bites when one byte can do the job.

UCS defines a mapping between characters and integer code values, called "code points". The code point is not the same as an encoding. It is an integer that can be represented in different ways of bytes or words in an computer system.

Unicode is a standard that defines the characters with the code points derived from UCS. Remember, with the same identical code point, you can have different encodings. Unicode standards provide such flexibility by dividing the codes into 17 code planes, each of which contains 65,536 code values.

Code plane 0 contains codes from 0x0 to 0xffff and code plane 1 with 0x10000 to 0x1ffff. Naturally code plane 0 contains most national languages.

As mentioned, Unicode provides more than one encoding method. The most commonly used are UTF-8 and UTF-16.

UTF-8 represents a character as a variable length of 1 to 4 bytes with ASCII character set appears in UTF-8 as single byte codes.

UTF-16 represents a character as one or two 16-bit values. UTF-16 includes UTF-8.

Java use UTF-16 unicode to represent internal text.

In C++, the default size of 'char' is 8-bit ASCII code and you can declare it as 'signed char' to have value -128 to 127. You also have wchar_t, char16_t and char32_t to store unicode characters.

Cucumber and Gherkin.

• Gherkin Related Jars
• Gherkin
• cucumber-jvm-deps
• cucumber-java
• cucumber-core

Maven 101.

mvn clean install
mvn clean test

Useful Links:

• Maven in 5 Min
• Maven Tutorial
• Maven JUnit
• Maven Cucumber

Phases:

• validate 
• compile
• test 
• clean 
• package 
• integration-test 
• verify 
• install 
• deploy 
• site

C++ Reference.

Create Tests

#include 
#include 
using namespace std;

void fa();
void fb();
void fc();
void func  ( const int & i );
void func  ( const string & fs );
void func2 ( const string * fs );
void func3 ( const string * fs );
const char * prompt();
int jump   ( const char * );
void (*funcs[])() = { fa, fb, fc, nullptr };


int main( int argc, char ** argv )
{
    int x = 24;
    string s = "Hello";
    puts ("this is main()");
    func(x);

    x = 73;
    printf ("x is %d\n", x);

    func(&s);
    printf ("string is %s\n", s.c_str());
    func2(&s);
    printf ("string2 is %s\n", s.c_str());
    printf ("returned string is %s\n", func3().c_str());

    // function pointer *fp
    void (*fp)() = func4;
    void (*fp)(&s) = &func4; // same as above
    fp(); // or (*fp)();

     while ( jump (prompt()) );
     puts ("\nDone\n");

    fflush(stdout);
    return 0;
}

void func( const int & i )
{
    // would result in error if you try to change i in function
    printf ("value is %d\n", i);
}

void func( const string & fs )
{
    printf ("String is %s\n", fs.c_str());
}

void func2 (const string * fs )
{
    printf ("String2 is %s\n", fs->c_str());
}

// declare to be const so you can't change the string
const string & func3 (const string * fs )
{
    // declare to be static storage so the stack for function won't 
    //    overflow and create security problem
    // auto is deprecated, because it's default and stored in stack
    // stack is created fresh for each function
    //
    // also if you have to return a reference, declare it to be static
    //     so it can be stored in static storage space
    //     auto storage on stack is small. Use reference if you have
    //     to return big object and return the reference in static storage
    static string s = "This is static";
    return s;
}

void func4()
{
    printf ("String2 is %s\n", fs->c_str());
    puts ("a string");
}

void func4(const string * fs)
{
    printf ("String2 is %s\n", fs->c_str());
    puts ("a string");
}

const char * prompt() {

    puts ("Choose an option:");
    puts ("1. do fa()");
    puts ("2. do fb()");
    puts ("Q. quit");
    puts ("Choose an option:");
    printf(">> ");

    fflush(stdout);                // flush after prompt
    const int buffsz = 16;         // constant for buffer size
    static char response [buffsz]; // static storage for response buffer
    fgets(response, buffsz, stdin);// get response from console
    return response;
}

int jump ( const char * rs ) {
    char code = rs[0];
    if (code == 'q' || code == 'Q') return 0;
    // count the length of the funcs array
    int func_length = 0;
    while ( funcs[func_length] != Null ) func_length++;

    int i = (int) code - '0'; // convert ASCII numeral to int
    i--; // list is zero-based
    if ( i < 0 || i >= func_length ) {
        puts ("invalid choice");
        return 1;
    } else {
        funcs[i]();
        return 1;
    }

}

Python 102.

Stddraw

import math
import stddraw

x0 = 0.0
y0 = 0.0
x1 = 1.0
y1 = 0.0
t = math.sqrt (3.0) / 2.0
stddraw.line (x0, y0, x1, y1)
stddraw.point(0.5, t/3.0)
stddraw.setXscale(x0, x1)
stddraw.setYscale(y0, y1)
stddraw.show()

# stddraw.setCanvasSize(w, h)
# stddraw.setXxcale (x0, x1)
# stddraw.setYsclae (y0, y1)
# stddraw.setPenRadius(r)

stddraw.setXscale (0,n)
stddraw.setYscale (0,n)
for i in range (n+1)
    stddraw.line (0, n-i, i, 0)
stddraw.show()

xd = [x-r, x, x+r, x]
yd = [y, y+r, y, y-r]
stddraw.polygon(xd, yd)

# stddraw.circle(x, y, r)
# stddraw.square(x, y, r)
# stddraw.rectangle (x, y, w, h)
# stddraw.polygon(x, y)
# stddraw.text (x,y,s)
# stddraw.setPenColor(color)
# stddraw.setFontFamily(font)
# stddraw.setFontSize(size)
# stddraw.clear(color)

SQL Tips

Guidelines

• Document as you go.
• Leave bread crumbs on the trail.
• Keep it simple.
• Use prefixes or suffixes to make it obvious.
• Use consistent coding style.
• Add comments when it's not obvious.
• Anticipate disasters proactively.
• Testing
• DELETE
• INSERT
• SELECT
• UPDATE
• DELETE

Terms

• DDL - Data Definition Language
• DML - Data Manipulation Language
• PL/SQL - Procedural Language for SQL
• RDBMS - Relational DataBase Management System

Example

CREATE TABLE author (
id          number,
name        varchar2(100),
birth_date  date,
gender      varchar2(30)
);

Using IJ

% ./bin/ij
ij version 10.8
ij> connect 'jdbc:derby:myDB;create=true'; 

CREATE TABLE Products
     (
         ProductCode VARCHAR(10),
         Description VARCHAR(40),
         Price DOUBLE
);
0 rows inserted/updated/deleted
ij>

INSERT INTO Products
     VALUES ('candy', 'chocolate''s flavor', 5.25);
1 row inserted/updated/deleted
ij> disconnect;
ij> exit;

Running Script

% ./bin/ij
ij version 10.8
ij> connect 'jdbc:derby:myDB';
ij> run 'buildMyDB.sql';
ij> disconnect;
exit;

# to run script from command line
% java org.apache.derby.tools.ij myDBCreate.sql

# to start the Derby server
% java org.apache.derby.drda.NetworksServerControl start

# to stop the Derby server
% java org.apache.derby.drda.NetworksServerControl shutdown

Links

• Beginning PL/SQL
• Apache Derby Manual
• Apache Derby FAQ
• Apache Derby Tutorial
• Apache Derby Install Page

SQL 102.

Concepts

• Primary Key vs Foreign Key
• Scale
• Schema
• Records/Rows
• Fields/Columns
• Using Alias to shorten SQL syntax

SELECT RTrim(name) + ' ( ' + RTrim(country) + ')' AS 
title
FROM Students
ORDER BY name;

SELECT name, contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'THISID';

Using JOIN

// to retrieve all order from the customer who ordered
// '123' 
SELECT id, name
FROM orderlist
WHERE id = (SELECT id
            FROM orderlist
            WHERE id = '123');

// Using JOIN for the same query
SELECT p1.id, p1.name
FROM orderlist AS p1, orderlist AS p2
WHERE p1.id = p2.id
  AND p2.id = '123';

// Standard Join/Inner Join vs Outer Join
// Natural Joins eliminate repeated columns from the inner join
SELECT c.*, o.order_num, o.order_date, oi.prod_id, 
       oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems as oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

// Use outer Join to include rows that have no related rows
// INNER JOIN example
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id

// OUTER JOIN
//    must use RIGHT or LEFT keyword to specify 
//    which table to include all rows
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON orders.cust_id = customers.cust_id;

// Simplified OUTER JOIN
SELECT customers.cust_id, orders.order_num
FROM customers, orders
WHERE customers.cust_id *= orders.cust_id;

// Using JOIN with Aggregate Functions
SELECT customers.cust_name, 
       customers.cust_id,
       Count(orders.order_num) AS num_order
FROM customers LEFT INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name, 
         customers.cust_id;

SELECT customers.cust_name, 
       customers.cust_id,
       Count(orders.order_num) AS num_order
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_name, 
         customers.cust_id;

SubQuery and Combined Queries Using UNION

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price >= 5 OR vend_id IN (1001,1002);

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price >= 5 
// without UNION ALL, SQL eliminate duplicate rows
UNION     
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
// ORDER BY vend_id, prod_price;

Full-Text Searching

EXEC sp_fulltext_database 'enable';
CREATE FULLTEXT CATALOG catalog_my;
CREATE FULLTEXT INDEX ON productnotes (note_text)
KEY INDEX key_productnotes
ON catalog_my;
// KEY INDEX is used to provide the name of the table's primary key.

ALTER FULLTEXT CATALOG catalog_my REBUILD;
SELECT * FROM sys.fulltext_catalogs;

// FulltextCatalogProperty() function
//    accepts a catalog name and the property to be checked
//    IndexSize
//    PopulateStatus

// FREETEX - simple search, matching by meaning or exact text match
// CONTAINS - search for phrases, synonyms
SELECT note_id, note_text
FROM notes
WHERE note_text LIKE '%bird food%';

SELECT note_id, note_text
FROM notes
WHERE FREETEXT(note_text, 'bird food'); // look for anything that
                                        // could mean bird food

// WHERE CONTAINS (note_text, '"iron*"'); // match anything with iron
// WHERE CONTAINS (note_text, 'bird food');
//    CONTAINS is functionally identical to LIKE note_text = '%match%'
//    CONTAINS search typically is quicker, especially as table size
//        increases.

// More CONTAIN examples
WHERE CONTAINS (note_text, 'safe AND sound');
WHERE CONTAINS (note_text, 'bird AND NOT food');
WHERE CONTAINS (note_text, 'grass NEAR cheese');
// look for any words that share the same stem as 'vary', such as 'varies'
WHERE CONTAINS (note_text, 'FORMSOF (INFLECTIONAL, vary)');

// Ranking
// The following query use FREETEXTTABLE function to return a table that
//     contain words meaning bird and food and gives the table an alias 
//     of 'f'
SELECT f.rank, note_id, note_text
FROM notes,
    FREETEXTTABLE (notes, note_text, 'bird food') f
WHERE notes.note_id = f.[key]
ORDER BY rank DESC;

JAVA IO and NIO.

java.io.file

• path interface
• get(String, String)
• getFileName()
• getName(int)
• getNameCount()
• getParent(), getRoot()
• toAbsolutePath()
• toFile()
• paths class
• exists(path)
• notExists(path)
• isReadable(path)
• isWritable(path)
• isDirectory(path)
• isRegularFile(path)
• size(path)
• newDirectoryStream(path)
• createFile(path)
• createDirectory(path)
• createDirectories(path)
• files class
• Exceptions
• IOException
• EOFException
• FileNotFoundException
• FileAlreadyExistsException
• DirectoryNotEmptyException

import java.io.*;
import java.nio.file.*;

Path myp = Paths.get("./mydir");

String dir = "./src/db/files";
String filename = "db.txt";
Path dirPath = Paths.get(dir);
Path fPath = Paths.get(dir, filename);
File fName = fPath.toFile();

if (Files.notExists(fPath)) {
    Files.createDirectories(fPath);
}

// fPath.getFileName() -> return String
// fPath.toAbsolutePath() -> return String
// Files.isWritable(fPath) -> return boolean
// Files.exists(dirPath) -> return boolean
// Files.isDirectory (dirPath) -> return boolean

DirectoryStream dirs = Files.newDirectoryStream(dirPath);
for (Path p: dirs) {
    if (Files.isRegularFile (p) {
        System.out.println (" " + 
            p.getFileName());
    }
}

// Write data to file
// layered approach to get an object using constructor
try (PrintWriter out = new PrintWriter (
                       new BufferedWriter (
                       new FileWriter (productsFile))))
{
    out.println ("printout line");
}
catch (IOException e)
{
    System.out.println(e);
}

//Read data from the file
try (BufferedReader in = new BufferedReader(
                         new FileReader(myFile)))
{
    String line = in.readLine();
    while (line != null)
    {
        System.out.println(line);
        line = in.readLine();
        String[] columns = line.split("\t");
        String name = columns[0];
        String value = columns[1];
        int type = Integer.parseInteger(columns[2]);
        System.out.println(type);
    }
}
catch (IOException e)
{
    System.out.println(e);
}
// flush the buffer and close the input stream
in.close();

// Code that handles I/O exceptions
Path productsPath = Paths.get("products.txt");
if (Files.exists(productsPath)){  //defensive prog.
    //prevent the FileNotFoundeException
    File productsFile = productsPath.toFile();
    try (BufferedReader in = new BufferedReader(
                             new FileReader(productsFile)))
    {
        String line = in.readLine();
        //prevent EOFException
        while(line != null){
            System.out.println(line);
            line = in.readLine();
        }
    }
    catch (IOException e){
        System.out.println(e);
    }
} else{
    System.out.println(
              productsPath.toAbsolutePath() + " doesn't exist");
}

// ProductDAO interface
public interface ProductDAO
          extends ProductReader, ProductWriter,
          ProductConstants {}

import java.util.ArrayList;
public interface ProductReader
{
    Product getProduct(String code);
    ArrayList getProducts();
}

//The ProductWriter interface
public interface ProductWriter
{
    boolean addProduct(Product p);
    boolean updateProduct(Product p);
    boolean deleteProduct(Product p);
}
//The ProductConstants interface
public interface ProductConstants
{
    int CODE_SIZE = 4;
    int DESCRIPTION_SIZE = 40;
}

java.nio.file (JDK 1.7+)

• Threads and NIO

JAVA Threads.

Basics

• Threading in JAVA is built-in.
• Java supports threading natively and at a high level.
• Java concurrency utilities address common patterns and practices in multithreaded applications and raise them to the level of tangible Java APIs.
• Not all applications need explicit use of threads or concurrency but most will use some features that is impacted by multithreading.
• Threads are integral to client side Java APIs
• GUI, sound.
• Ex. using separate thread within JVM for drawing.
• APIs with lots of I/O activities which are slow in nature.
• Threads are not common and are discouraged on the server side in the context of application servers and web applications.
• Server environment should control the allocation of CPU time/resources.
• java.util.concurrent
• You don't want to put the run() method directly in the object often time.
• Make an adapter class that serves as the Runnable Object with the run() method and use that to call any method it wants to after the thread is started.

Concept

• All execution in Java is associated with a Thread object, beginning with "main" thread.
• New thread is create by java.lang.Thread class.
• Thread Methods:
• start
• run
• wait
• sleep
• notify
• notifyAll
• stop is deprecated and don't use it anymore.
• Since Java has no pointer system to the method to tell it to run, we can't specify one directly. Instead, we use java.lang.Runnable interface to create or mark an object that contains a "runnable" method, which is run().
• Thread begin its life by executing the run() method in a Runnable object (the target object) that was passed to the thread's constructor.
• run() must be public, return void, takes no arguments and throws no checked exceptions.
• Any class that contains an run() method can declare that it implements the Runnable interface.
• An instance of this class is a runnable object that can serve as the target of a new thread.

Thread States

• New
• Runnable
• Blocked
• Waiting
• Terminated

Example

class MyClass implements Runnable {
   boolean choice = ture;

   public void run() {
      while (choice) {
         // do what myClass has to...
      }
   }
}

MyClass item = new MyClass ("message");
thread myThread = new Thread (item);
myThread.start(); // This will cause run() in MyClass to execute

// To make an object to create and handle its own threads so to fit
// OOP concept, the following shows to have the actions in its
// constructor

class MyClass implements Runnable {
   boolean choice = ture;

   Thread myThread;
   public void MyClass (String name) {
      myThread = new Thread(this);
      myThread.start();
   }

   public void run() {
      while (choice) {
         // do what myClass has to...
      }
   }
}

Natual Born Thread Example

class Runner extends Thread {
    boolean running = true;

    public void run() {
        while (! isInterrupted()) {
            // by default, the Thread executes its own run() method when
            // we call the start() method
        }
    }
}

// to call Runner
Runner horse = new Runner ("horse");
horse.start();

// alternatively,
class Runner extends Thread {
    Runner (String name) {
        start();
    }
}

// Use adapter
class Runner {
    public void startRunner() {
        Thread myThread = new Thread (new Runnable () {
            public void run() { doAction(); }
        } );
        myThread.start();
    }

    private void doAction () {
        // do something...
    }
}

// Another way to write the code
new Thread () {
    public void run() { doAction(); }
}.start();

Thread Methods

• Thread.sleep() -> require try/catch (InterruptedException e)
• myThread.wait()
• myThread.join()
• myThread.interrupt()
• stop(), suspend() and resume()

More about interrupt() method

Any thread that is not running (hard loop) must be in one of three states - sleep(), wait() or lengthy I/O operation - where it can be flagged to stop by interrupt() method. When a thread is interrupted, its interrupt status flag is set and this can happen at any time. Use isInterrupted() method to test this status like in the example above. You can also use isInterrupted(boolean toClear) as a flag and a signal to clear the interrupt status.

That said, this is historically a weak spot and it may not work correctly in all cases in early JVM, and more often with interrupting I/O calls blocked in a read() or write() method, moving bytes from a file or network. To address this in Java 1.4, a new I/O framework (java.nio) was introduced with one of its goals to address these problems. When the thread associated with an NIO operation is interrupted, the thread wakes up and the I/O stream (called a "channel") is automatically closed. (Check about the NIO package for more information.)

JAVA GUI 101.

• import javax.swing.JFrame;
• import javax.swing.ImageIcon;
• import javax.swing.JLabel;

import javax.swing.JFrame;
import javax.swing.ImageIcon;
import javax.swing.JLabel;

class showPicture {
   public static void main (String args[]) {
      JFrame frame = new JFrame();
      ImageIcon icon = new ImageIcon ("some.jpg");
      JLabel label = new JLabel (icon);
      frame.add(label);
      frame.setDefaultCloseOperation 
         (JFrame.EXIT_ON_CLOSE);
      frame.pack();
      frame.setVisible(true);
   }
}

import java.awt.Font;
import java.awt.GridLayout;

import javax.swing.JFrame;
import javax.swing.JLabel;

public class myGui {
   public static void myGui {
      JFrame frame = new JFrame();
      JLabel label = new JLabel (icon);

      JLabel labels[] = {
         new JLabel ("Name"), new JLabel("Phone"),
         new JLabel ("Alice"), new JLabel("555-1234"),
         new JLabel ("Bob"), new JLabel("222-9876") 
      };

      frame.add(label[0]);
      frame.add(label[1]);

      JLabel boldLabel = new JLabel("Name");
      Font boldFont = boldLabel.getFont();
      Font plainFont = new Font(boldFont.getName(),
         Font.PLAIN, boldFont.getSize() );

      for (int i=2; i<8; i++) {
         labels[i].setFont(plainFont);
         frame.add(labels[i]);
      }
      frame.pack();
      frame.setvisible(true);
   }
}

• openJFX
• Gluon Scene Builder
• OpenJDK Wiki on OpenJFX
• e(fx)clipse Add-on

package application;

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

public class myJFXApp extends Application {
   @Override
   public void start (Stage primaryStage) {
      try {
         // BorderPane root = new Borderpane();
         Parent root = FXMLLoader.load (getClass().
                        getResource ("Root.fxml"));
         Scene scene = new Scene (root, 400, 400);
         scene.getStylesheets().
            add(getClass().getResource("application.css").
                             toExternalForm());
         primaryStage.setScene(scene);
         primaryStage.show();
      }
      catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main (String[] args) {
      launch (args);
   }
}

import javafx.fxml.FXML;
import javafx.event.ActionEvent;
import javafx.scene.control.Textfield;

public class myJFXApp2...
{
   @FXML
   private TextField textfield;

   @FXML
   protected void onClick (ActionEvent event) {
      textField.setText (textField.getText().
         toUpperCase());
   }
}

SQL 101.

COMMANDS

• SELECT <column-1> [, column-2] ...
• FROM <table-1>
     { INNER | LEFT OUTER | RIGHT OUTER} JOIN table-2
     ON table-1.column-1 {=|<|>|<=|>=|<>} table-2.column-2
• WHERE selection-criteria
• ORDER BY column-1 [ASC | DESC] [, column-2 [ASC | DESC]] ...
• INSERT INTO <table-name> [(column-list)]
• VALUES (value-list)
• UPDATE <table-name>
• SET  <expression-1> [, expression-2] ...
• WHERE selection-criteria
• DELETE FROM <table-name>

SELECT

SELECT ProductCode, Description, Price
FROM Products
WHERE Price > 50
ORDER BY ProductCode ASC

SELECT * FROM PRODUCTS

SELECT p.ProductCode, p.Price, li.Quantity,
            p.Price * li.Quantity AS Total
     FROM Products p
           INNER JOIN LineItems li
           ON p.ProductCode = li.ProductCode
     WHERE p.Price > 50
     ORDER BY p.ProductCode ASC;

SELECT p.ProductCode, p.Price, li.Quantity,
             p.Price * li.Quantity AS Total
     FROM Products p, LineItems li
     WHERE p.ProductCode = li.ProductCode AND p.Price > 50
     ORDER BY p.ProductCode ASC;

SELECT TOP(5) p.prod_name FROM products p, LineItem li
TABLESAMPLE (3 ROWS)
ORDER BY p.prod_price;

SELECT products.prod_name FROM products
ORDER By products.prod_price DESC, products.prod_name
TABLESAMPLE (50 PERCENT);

// Use [] to delimit the column name when there is space btwn
// and alias it using 'AS' to be 'LastName'
SELECT [Last Name] AS LastName

INSERT

INSERT INTO Products (ProductCode, Description, Price)
VALUES ('casp', 'ASP.NET Web Programming with C#', 54.50)

// without the column list
INSERT INTO Products
VALUES ('casp', 'ASP.NET Web Programming with C#', 54.50)

UPDATE

// Update a single row
UPDATE Products
SET Description =
       'Murach''s ASP.NET Web Programming with C#',
    Price = 49.50
WHERE ProductCode = 'casp'
// Update multiple rows
UPDATE Products
SET Price = 49.95
WHERE Price = 49.50

DELETE

DELETE FROM Products WHERE ProductCode = 'casp'
DELETE FROM Invoices WHERE AmountDue = 0
DELETE FROM Invoices

WHERE, IN, NOT

SELECT prod_name, prod_price
FROM products p, LineItem li
TABLESAMPLE (3 ROWS)
WHERE prod_price <> 10, prod_name = 'fuses'
ORDER BY price;

...
WHERE price BETWEEN 5 AND 10;
WHERE vend_id = 1002 AND price <= 10;
WHERE vend_id = 1002 OR vend_id = 2001 AND price <= 10;
WHERE prod_name LIKE 's%e'; // % is wildcard, 
WHERE prod_name LIKE 'jet%'; // matching any 'jet' in the beginning
WHERE prod_name LIKE '%anvil%'; // %...%, match anywhere

WHERE name LIKE '_ley'; // _ match one char
WHERE name LIKE '[EJ]%'; // match E or J as the start char
WHERE name LIKE '[^EJ]%'; // ^ NOT match, any name not begin with E or J
WHERE NOT name LIKE '[EJ]%'; // same as '[^EJ]%'

// WHERE OPERATOR:
// =, <>, !=, <, <=, !<, >, >=, !>, BETWEEN, IS NULL

Fields/Column Concatenation

SELECT name + ' (' + age + ')'
FROM students
ORDER BY name;

// T-SQL RTrim: trim all space from the right
//       LTrim: trim all space from the left
SELECT RTrim(name) + ' (' + LTrim(age) + ')' AS
student_title
FROM students
ORDER BY name;

Using MATH

SELECT id, quantity, price,
    quantity * price AS total_price
FROM orderlist
WHERE order_num > 2000;
// T-SQL math operators: + - * / %

Using Function

// Common Text Functions
// CharIndex()
// Left(), Right()
// Len()
// Lower(), Upper()
// LTrim(), RTrim()
// Replace()
// Soundex() : soundex value when they sound similar
// Str()
// SubString()

SELECT name, UPPER(name) AS name_upper
FROM orderlist
WHERE order_num > 2000;

// Common Data and Time functions
// DateAdd()
// DateDiff()
// DateName()
// DatePart()
// Day()
// GetData()
// Month()
// Year()

// Supported Date Parts:
// day (dd or d)
// dayofyear (dy or y)
// hour (hh)
// millisecond (ms)
// minute (mi or n)
// month (m or mm)
// quarter (q or qq)
// second (ss or s)
// week (wk or ww)
// weekday (DatePart() only), (dw)
// year (yy or yyyy)

SELECT name, 
    DatePart(weekday, orderdate) AS weekday
FROM orderlist;

// to return named weekdays instead of numbered day
SELECT name, 
    DateName(weekday, DatePart(weekday, orderdate) ) AS weekday
FROM orderlist;

// Day(),          Month(),         and Year() are shortcuts for 
// DatePart(day,), DatePart(month,) and DatePart(year,)

// when comparing dates, always use DateDiff()
SELECT name, id
FROM orderlist
WHERE DateDiff (month, order_date, '2005-09-01') = 0;

...
Where Year(order_date) = 2005 AND Month(order_date) = 9;

// Numeric Functions
// Abs()
// Cos()
// Exp()
// Pi()
// Rand()
// Round()
// Sin()
// Sqrt()
// Square()
// Tan()

// SQL Aggregate Functions
// Avg(), Count(), Max(), Min(), Sum()
SELECT Avg(price) AS avg_price // Null columns are ignored by Avg()
FROM products
WHERE id = 1003;

SELECT Count(*) AS num_customers // count all rows
FROM customers;
SELECT Count(customer_email) AS num_customers // count only those with emails
FROM customers;
SELECT Sum(price*quantity) AS total_price
FROM orderlist
WHERE order_num = 2000;

// Use ALL (default) or DISTINCT for unique items
SELECT Avg(DISTINCT price) AS avg_price_ofunique
FROM products
WHRE id = 1002;

Filtering Groups

// group by vend_id and calcute all products from that
// vendor
SELECT vend_id, Count(*) AS num_prods
FROM products
GROUP BY vend_id;

SELECT cust_id, Count(*) AS orders
FROM orderlist
WHERE price >= 10
GROUP BY cust_id
HAVING Count(*) >= 2;

// HAVING support all WHERE operators
// HAVING filters after data is grouped.
// WHERE filters before data is grouped 
//    (rows eliminated by WHERE are not included in the group)
//    (This can change the calculated values used in HAVING clause.

// ORDER BY
//    sorts generated output, can use any column, not required
// GROUP BY
//    groups rows but may not be in order,
//    only selected columns or expression columns may be used
//    every selected column expression must be used
//    required if using columns (or expressions) with Count/Avg/Sum/Min/Max

SELECT order_num, Sum(quantity*price) AS ordertotal
FROM orderlist
GROUP BY order_num
HAVING Sum(quantity*price) >= 50;
// ORDER BY ordertotal; // optional

SQL Servre 2005

% USE mydatabase;
% sp_databases;
% sp_tables;
% sp_tables NULL, dbo, mydatabase, "'TABLE'";
% sp_columns customers;
% sp_server_info;
% sp_space_used;
% sp_statistics;
% sp_helpuser;
% sp_helplogins;