Adobe Flash Player 10 Unable to authenticate installer Problem

There are many people having problem with installation of the new adobe flash player version 10. Mostly the problem is caused by the date/time setting of the operating system or the proxy. The best solution for both cases is to download the software separately rather then installing directly with in the browser as a browser patch. Click here to download.

How To Change Your Windows XP Serial Key

Method 1:

Note Microsoft recommends that you run System Restore to create a new restore point before you follow these steps. For information about how to create a restore point by using System Restore, see the "To Create a Restore Point" help topic in Help and Support.

1.	Click Start, and then click Run.
2.	In the Open box, type regedit, and then click OK.
3.	In the left pane, locate and then click the following registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\WindowsNT\Current Version\WPAEvents
4.	In the right pane, right-click OOBETimer, and then click Modify.
5.	Change at least one digit of this value to deactivate Windows.
6.	Click Start, and then click Run.
7.	In the Open box, type the following command, and then click OK. %systemroot%\system32\oobe\msoobe.exe /a
8.	Click Yes, I want to telephone a customer service representative to activate Windows, and then click Next.
9.	Click Change Product key.
10.	Type the new product key in the New key boxes, and then click Update. If you are returned to the previous window, click Remind me later, and then restart the computer.
11.	Repeat steps 6 and 7 to verify that Windows is activated. You receive the following message: Windows is already activated. Click OK to exit.
12.	Click OK.
13.	Install Windows XP SP1 or a later version of Windows XP. If you cannot restart Windows after you install Windows XP SP1 or a later version of Windows XP, press F8 when you restart the computer, select Last Known Good Configuration, and then repeat this procedure.

Method 2:

Download the following file from here. Just select your OS (Vista or XP), agree to the terms and hit download.

VertrigoServ - Freeware web environment

VertrigoServ has been developed as a highly professional, easy to install package consisting of Apache (HTTP web server), PHP (reflective programming language), MySQL (multithreaded, multi-user, SQL Database Management System), SQLite (ACID-compliant relational database management system), SQLiteManager (multilingual web based tool to manage SQLite database), PhpMyAdmin (tool written in PHP intended to handle the administration of MySQL) and Zend Optimizer (which increases runtime performance up to 40%) for Windows platform. With a convenient all-in-one installer, all components are installed in a single directory and can be used immediately after the installation process has completed. An uninstaller allows you to remove the VertrigoServ from hard disc. It is designed to be as small and flexible as possible and is therefore highly suitable for internet distribution. VertrigoServ is excellent both for beginners and for advanced users.

Major features:

• small size (LZMA compressor)
• secure because it contains the up-to-date packages versions
• very to install and use
• no configuration and no manual installation required
• compatible with all major Windows versions
• completely free for any use
• Polish and English translation
• FREE
  

Remove AVG 8.5 Notifications on The Free Edition

Follow the instructions exactly and you should not have any issues.

1. Open Notepad or another plain text editor.

2. Copy and paste the following three lines from this page into the text editor:
@echo off
ren avgmwdef_us.mht avgmwdef_us.mht.bak
ren avgresf.dll avgresf.dll.bak
ren avgfree_us.mht avgfree_us.mht.bak

3. Save the text document containing the above three lines in the location where AVG 8 is installed. By default, this will be C:\Program Files\AVG\AVG8. Save the file as something like “AVGNotifyOff.bat”, without the quotes. It really doesn’t matter how you name the file, so long as you can identify it, and it ends in the “.bat” extension, so Windows sees it as a batch file.

4. Once the file’s saved, close the text editor, navigate in Windows Explorer or My Computer to C:\Program Files\AVG\AVG8, locate the batch file document you saved in step 3 above, and you should be able to run the file by pressing and releasing the Enter key. All the batch file does is rename two files with a “.bak” (backup) extension, so the files are still there, it’s just that AVG can’t see them any more, and you should find that the bottom line of the AVG user interface window no longer displays the “Hide notification, button” item.

FoxyProxy

FoxyProxy is a free, open-source advanced proxy management tool that completely replaces Firefox's limited proxying capabilities.FoxyProxy automatically switches an internet connection across one or more proxy servers based on URL patterns. Put simply, FoxyProxy automates the manual process of editing Firefox's Connection Settings dialog. Proxy server switching occurs based on the loading URL and the switching rules you define. Animated icons show you when a proxy is in use. Advanced logging shows you which proxies were used and when. QuickAdd makes it a snap to create new URL patterns on-the-fly. FoxyProxy is fully compatible with Portable Firefox, has better support for PAC files than Firefox itself, and is translated into more than 25 languages.

• Want fine-grained control over which websites load through proxies?
• Live in Belarus, Burma, China, Cuba, Egypt, Iran, North Korea, Saudi Arabia, Syria, Thailand, Tunisia, Turkmenistan Uzbekistan, Vietnam, or one of the other nations who censor the internet?
• Can't get to MySpace from school?
• Can't get to GMail at work?

Then foxyproxy is for you!

FoxyProxy does not inherently make you anonymous

Please read this important note if you require thorough privacy and anonymity.

Some FoxyProxy Features

• Animated statusbar/toolbar icons show you when a proxy is in use
• Define multiple proxies
• Define which proxy to use (or none!) for arbitrary URLs using wildcards, regular expressions, whitelists, blacklists, and other conveniences
• No more wondering whether a URL loaded through a proxy or not: FoxyProxy optionally logs all URLs, including which proxy was used (if any), which pattern was matched, timestamps, etc.
• Out-of-the-box support for Tor with the unique Tor Wizard - zero configuration!
• Temporarily or permanently dedicate all URLs to go through a particular proxy
• Temporarily or permanently disable use of a proxy
• Automatically add blocked sites to a proxy and have them reload through the proxy
• Optional status bar information about which proxy is currently in use
• Complete Proxy Auto-Config (PAC) support
• Download a link using one of your defined proxies with a simple right-click (coming soon)
• Unobtrusive presence, stable execution, premier support
• Optionally force Firefox to perform DNS lookups through a SOCKS4a/5 server. Note that Firefox without FoxyProxy always performs DNS lookups through a SOCKS5 server if you've defined one. Only with FoxyProxy can you instruct Firefox to not use defined SOCKS5 servers for DNS lookups.

Source: FoxyProxy
Download: Extension

Zotero

Zotero [zoh-TAIR-oh] is a free, easy-to-use Firefox extension to help you collect, manage, and cite your research sources. It lives right where you do your work — in the web browser itself.

Features

• Automatic capture of citation information from web pages
• Storage of PDFs, files, images, links, and whole web pages
• Flexible notetaking with autosave
• Fast, as-you-type search through your materials
• Playlist-like library organization, including saved searches (smart collections) and tags
• Platform for new forms of digital research that can be extended with other web tools and services
• Runs right in your web browser
• Formatted citation export (style list to grow rapidly)
• Free and open source
• Integration with Microsoft Word and OpenOffice
• Saves records and notes in any language; interface available in Arabic Austrian German Brazilian Portuguese Bulgarian Catalan Chinese (Simplified) Chinese (Traditional) Czech Danish Finnish French Galician German Hungarian Hebrew Italian Japanese Korean Dutch Norwegian Polish Portuguese Romanian Russian Serbian Slovak Slovenian Spanish Swedish Swiss German Thai Turkish Vietnamese US English with more on the way
• Integration with WordPress and other blogging software

Source: Zotero
Download: Extension

DExposE2

If you're looking for an Expose emulator to port that cool app-switching feature from the Mac to Windows, DExposE2 is probably about as close as you can get to a direct copy for either Windows XP or Vista. It gives them three choices for jumping between open programs, and it doesn't slurp up more RAM than it should.

(Credit: Devrexter/DExposE2)

Using the F9 key, you'll be able to minimize your applications to fit on the screen, then choose one to bring to the front by hitting Tab or left-clicking your mouse. F10 highlights only the active windows belonging to the application currently running in the foreground. F11 reveals the desktop by shunting all active windows off to the right side of your screen, visible only as a thin and unidentifiable bar.

DExposE2 lets you set up other hot keys or move your mouse to the corner of the screen to activate, and there's also a Windows Taskbar icon you can use to access it's features--but the hot keys are more than adequate. Other customizations include setting a background image, determining the behavior of previously minimized windows, and customizing program labels. Three levels of graphical quality should allow those with less RAM to scale the program to their needs. Although some crashes have been reported, DExposE2 worked flawlessly for me.

Source: Seth Rosenblat

Download: Dexpose2

Quick and Basic Oracle Guide

Create Table:

You use the CREATE TABLE SQL command to create a new table. The general syntax for the CREATE TABLE command is:

CREATE TABLE tablename

(columnname1 data_type,

columnname2 data_type, ...);

VARCHAR2 Data Type

The VARCHAR2 data type stores variable-length character data up to a maximum of 4000 characters. You use the following syntax to declare a VARCHAR2 data column:

Columnname VARCHAR2(maximum_size)

s_last VARCHAR2(30);

CHAR Data Type

The CHAR data type stores fixed-length character data up to a maximum of 2000 characters.

Columnname CHAR[(maximum_size)]

s_class CHAR(2);

NVARCHAR2 and NCHAR Data Types

The NVARCHAR2 and NCHAR data types store variable-length and fixed-length data just as their VARCHAR2 and CHAR counterparts do, except that they use Unicode coding.

Columnname NVARCHAR2(maximum_size)

Columnname NCHAR[(maximum_size)]

Number Data Types

The NUMBER data type stores negative, positive, fixed, and floating-point numbers between 10-130 and 10125, with precision up to 38 decimal places.

Columnname NUMBER [([precision,] [scale])]

Integer Numbers

An integer is a whole number with no digits on the right side of the decimal point.

Columnname NUMBER(precision)

f_id NUMBER(5);

Fixed-point Numbers

A fixed-point number contains a specific number of decimal places, so the column declaration specifies both the precision and scale values

price NUMBER(5,2);

Floating-point Numbers

A floating-point number contains a variable number of decimal places. The decimal point can appear anywhere, from before the first digit to after the last digit, or can be omitted entirely.

Columnname NUMBER

s_gpa NUMBER;

DATE Data Type

The DATE data type stores dates from December 31, 4712 BC to December 31,AD 4712. The DATE data type stores the century, year, month, day, hour, minute, and second. The default date format is DD-MON-YY, which indicates the day of the month, a hyphen, the month (abbreviated using three capital letters), another hyphen, and the last two digits of the

year.The default time format is HH:MI:SS AM, which indicates the hours, minutes, and seconds using a 12-hour clock. If the user does not specify a time when he or she enters a DATE data value, the default time value is 12:00:00 AM. If the user does not specify the date when he or she enters a time value, the default date value is the first day of the current month.

To declare a DATE data column, use the following general syntax:

Columnname DATE

s_dob DATE;

TIMESTAMP Data Type

The TIMESTAMP data type stores date values similar to the DATE data type, except it also stores fractional seconds in addition to the century, year, month, day, hour, minute, and second. An example of a TIMESTAMP data value is 15-AUG-06 09.26.01.123975 AM. You use the TIMESTAMP data type when you need to store precise time values. The following general syntax declares a TIMESTAMP data column:

Columnname TIMESTAMP (fractional_seconds_precision)

For example, suppose you want to store a date value that includes the fractional seconds. You would declare the SL_DATE_RECEIVED columnas follows:

sl_date_received TIMESTAMP(2);

Large Object (LOB) Data Types

Sometimes databases store binary data, such as digitized sounds or images, or references to binary files from a word processor or spreadsheet. In these cases, you can use one of the Oracle10g large object (LOB) data types.Table 2-1 summarizes the four LOB data types. Previous versions of Oracle supported the RAW and LONG RAW data types for storing binary data. Oracle Corporation recommends storing binary data using the large object data types, so this book does not use the RAW and LONG RAW data types.

You declare an LOB data column using the following general syntax:

Columnname LOB_data_type

Large Object (LOB) Data Type Description

· BLOB Binary LOB, storing up to 4 GB of binary data in the database

· BFILE Binary file, storing a reference to a binary file located outside the database in a file maintained

· by the operating system

· CLOB Character LOB, storing up to 4 GB of character data in the database

· NCLOB Character LOB that supports 2-byte character codes, stored in the database—up to a maximum

· of 4 GB

You declare an LOB data column using the following general syntax:

Columnname LOB_data_type

In this syntax, LOB_data_type is the name of the LOB data type, and can have the value BLOB, CLOB, BFILE, or NCLOB.

f_image BLOB

Alternately, you could store a reference to the location of an external image file using the BFILE data type by making the following declaration:

f_image BFILE

CONSTRAINTS

Constraints are rules that restrict the data values that you can enter into a column in a database table.There are two types of constraints: integrity constraints, which define primary and foreign keys; and value constraints, which define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL.There are two levels of constraints: table constraints and column constraints. A table constraint restricts the data value with respect to all other values in the table. An example of a table constraint is a primary key constraint, which specifies that a column value must be unique and cannot appear in this column in more than one table row. A column constraint limits the value that can be placed in a specific column, irrespective of values that exist in other table rows. Examples of column constraints are value constraints, which specify that a certain value or set of values must be used, and NOT NULL constraints, which specify that a value cannot be NULL.A value constraint might specify that the value of a GENDER column must be either M (for male) or F (for female).You might place a NOT NULL constraint on a student ADDRESS column, to ensure that users always enter address information when they create a new customer row.

You can place constraint definitions at the end of the CREATE TABLE command, after you declare all of the table columns. Or, you can place each constraint definition within the column definition, so it immediately follows the data column declaration for the column associated with the constraint. Every constraint in a user schema must have a unique constraint name.

Integrity Constraints

An integrity constraint defines primary key columns, and specifies foreign keys and their corresponding table and column references. The following paragraphs describe how to define primary keys, foreign keys, and composite primary keys.

Primary Keys

The general syntax for defining a primary key constraint within a column declaration is:

CONSTRAINT constraint_name PRIMARY KEY

The syntax for defining a primary key constraint at the end of the CREATE TABLE command, after the column declarations, is:

CONSTRAINT constraint_name PRIMARY KEY (columnname)

CREATE TABLE location

(loc_id NUMBER(6),ƒ

bldg_code VARCHAR2(10),

room VARCHAR2(6),

capacity NUMBER(5),

CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));

Foreign Keys

A foreign key constraint is a column constraint that specifies that the value a user inserts in a column must exist as a primary key in a referenced table.

CONSTRAINT constraint_name

FOREIGN KEY (columnname)

REFERENCES primary_key_tablename (primary_key_columnname)

Composite Keys

Recall that a composite key is a primary key composed of two or more data columns.

CONSTRAINT constraint_name

PRIMARY KEY (columnname1, columnname2,…)

CREATE TABLE enrollment

(s_id NUMBER(5) CONSTRAINT enrollment_s_id_fk

REFERENCES student(s_id),

c_sec_idƒNUMBER(8) CONSTRAINT enrollment_c_sec_id_fk

REFERENCES course_section(c_sec_id),

CONSTRAINT enrollment_s_id_c_sec_id_pk

PRIMARY KEY (s_id, c_sec_id));

Value Constraints

Value constraints are column-level constraints that restrict the data values that users can enter into a given column. Commonly used value constraints include:

· _ CHECK conditions—This enables you to specify that a column value must be a specific value or fall within a range of values.

· _ NOT NULL constraint—Specifies whether a column value can be NULL _ DEFAULT constraint—Specifies that a column has a default value that the DBMS automatically inserts for every row, unless the user specifies an alternate value

· _ UNIQUE constraint—Specifies that a column must have a unique value for every table row

Check Conditions

Check conditions specify that a column value must be a specific value (such as M), from a set of allowable values (such as M or F), or fall within a specific range (such as greater than zero but less than 1000).You should create check condition constraints only when the number of allowable values is limited and not likely to change.

As an example of a check condition, consider the S_CLASS column in the Northwoods University STUDENT table, in which the values are restricted to FR, SO, JR, or SR (freshman, sophomore, junior, or senior). The syntax to define this check condition is:

CONSTRAINT student_s_class_cc CHECK

((s_class = ‘FR’) OR (s_class = ‘SO’)

OR (s_class = ‘JR’) OR (s_class = ’SR’))

You can also use a check condition to validate a range of allowable values. An example of a range check condition is in the CREDITS column in the Northwoods COURSE table, where the allowable values must be greater than 0 and less than 12.The constraint definition is:

CONSTRAINT course_credits_cc

CHECK((credits > 0) AND (credits <>

NOT NULL Constraints

The NOT NULL constraint specifies whether the user must enter a value for a specific row, or whether the value can be NULL (absent or unknown)

s_last VARCHAR2(30)

CONSTRAINT student_s_last_nn NOT NULL

Default Constraints

A default constraint specifies that a particular column has a default value that the DBMS automatically inserts for every table row.

s_state CHAR(2) DEFAULT ‘FL’

The DBMS will insert the default only if the user inserts a NULL value into the S_STATE column.

UNIQUE Constraints

A UNIQUE constraint is a table constraint that specifies that a column must have a unique value for every table row. It is basically the same as a primary key constraint, except NULL values are allowed in the column. NULL values are not allowed in a column that is referenced by a primary key constraint.

CONSTRAINT term_term_desc_uk UNIQUE (term_desc)

Creating SQL table using SQL*Plus

CREATE TABLE customer

(c_id NUMBER(5),

c_last VARCHAR2(30),

c_first VARCHAR2(30),

c_mi CHAR(1),

c_birthdate DATE,

c_address VARCHAR2(30),

c_city VARCHAR2(30),

c_state CHAR(2),

c_zip VARCHAR2(10),

c_dphone VARCHAR2(10),

c_ephone VARCHAR2(10),

c_userid VARCHAR2(50),

c_password VARCHAR2(15),

CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));

VIEWING INFORMATION ABOUT TABLES

To view the column names and data types of an individual table, you use the DESCRIBE command, which has the following syntax:

DESCRIBE tablename

The following command retrieves the names of all of a user’s database tables by retrieving the TABLE_NAME column from the USER_TABLES view:

SELECT table_name

FROM user_tables;

Similarly, the following command uses the ALL prefix and retrieves the names of all database tables that a user has either created or has been given object privileges to manipulate:

SELECT table_name

FROM all_tables;

Type SELECT table_name FROM user_tables; at the SQL prompt, and then press Enter to view information about the tables in your user schema.

Type SELECT table_name FROM all_tables; at the SQL prompt, and then press Enter to retrieve the names of all tables that you have privileges to manipulate. To retrieve a list of all of the constraints for a specific database table in your user schema, you use the following command:

SELECT constraint_name, constraint_type

FROMƒuser_constraints

WHERE table_name = ‘DATABASE_TABLENAME’;

Deleting and Renaming Existing Tables

To delete a table, you use the DROP TABLE command, which has the following syntax:

DROP TABLE tablename;

To drop a table that contains columns that other tables reference as foreign keys, you have two options. One option is to first drop all the tables that contain the foreign key references. For example, to delete the LOCATION table, you could first delete the FACULTY table, and then you could delete the LOCATION table.The second option is first to delete all of the foreign key constraints that reference the table to be deleted. To delete the foreign key constraints that reference a table, you use the CASCADE CONSTRAINTS option in the DROP TABLE command, which has the following syntax:

DROP TABLE tablename

CASCADE CONSTRAINTS;

When you execute the DROP TABLE command with the CASCADE CONSTRAINTS option, the system first drops all of the constraints associated with the table, and then drops the table.

To rename an existing table, you use the RENAME TO command, which has the following syntax:

RENAME old_tablename

TO new_tablename;

RENAME faculty TO nw_faculty;

Adding Columns to Existing Tables

The basic syntax of the command to add a new column to a table is:

ALTER TABLE tablename

ADD(columnname data_declaration constraints);

In this syntax, columnname is the name of the new data column. Data_declaration defines the new column’s data type and maximum size, and constraints defines any constraints you want to place on the new column, such as foreign key references, check conditions, or value constraints.

To add the START_DATE column to the FACULTY table type the following command at the SQL prompt:

ALTER TABLE faculty

ADD (start_date DATE);

Modifying Existing Column Data Definitions

The general syntax of the command to modify an existing column’s data declaration is:

ALTER tablename

MODIFY(columnname new_data_declaration);

ALTER TABLE faculty

MODIFY (f_rank CHAR(4));

Deleting a Column

The general command to delete an existing column is:

ALTER TABLE tablename

DROP COLUMN columnname;

Renaming a Column

ALTER TABLE tablename

RENAME COLUMN old_columnname TO new_columnname;

Adding and Deleting Constraints

To add a constraint to an existing table, you use the following command:

ALTER TABLE tablename

ADD CONSTRAINT constraint_name constraint_definition;

To add the UNIQUE constraint to the FACULTY table type the following command at the SQL prompt:

ALTER TABLE faculty

ADD CONSTRAINT faculty_f_pin_uk UNIQUE(f_pin);

To remove an existing constraint, you use the following command:

ALTER TABLE tablename

DROP CONSTRAINT constraint_name;

To drop the constraint type the following command at the SQL prompt:

ALTER TABLE faculty

DROP CONSTRAINT faculty_f_pin_uk;

Enabling and Disabling Constraints

You use the following command to disable an existing constraint:

ALTER TABLE tablename

DISABLE CONSTRAINT constraint_name;

Similarly, you use the following command to enable a constraint that you previously disabled:

ALTER TABLE tablename

ENABLE CONSTRAINT constraint_name;

USING SCRIPTS TO CREATE DATABASE TABLES

@ D:\206\Lecture03\Ch3Northwoods.sql

OR

START D:\206\Lecture03\Ch3Northwoods.sql

Using the INSERT Command

The basic syntax of the INSERT statement for inserting a value for each table column is:

INSERT INTO tablename

VALUES (column1_value, column2_value, ...);

INSERT INTO location

VALUES (1, 'CR', '101', NULL);

You can also use the INSERT command to insert values only in specific table columns. The basic syntax of the INSERT statement for inserting values into selected table columns is:

INSERT INTO tablename (columnname1, columnname2,...)

VALUES (column1_value, column2_value, ...);

INSERT INTO faculty (F_FIRST, F_LAST, F_ID)

VALUES ('Teresa', 'Marx', 1);

Inserting Values into DATE Columns

The general syntax of the TO_DATE function is:

TO_DATE('date_string', 'date_format_model')

In this syntax, date_string represents the date value as a text string, such as ‘08/24/2006’, and date_format_model is the format model that represents the date_string value’s format, such as MM/DD/YYYY.You convert the text string ‘08/24/2006’ to a DATE data type using the following command:

TO_DATE('08/24/2006', 'MM/DD/YYYY');

Similarly, the following command converts the character string ‘24-AUG-2005’ to a DATE data type:

TO_DATE('24-AUG-2005', 'DD-MON-YYYY')

To convert a 10:00 AM value to a DATE format for C_SEC_ID 1, you use the following command:

TO_DATE('10:00 AM', 'HH:MI AM')

CREATING SEARCH CONDITIONS IN SQL QUERIES

The general syntax of a SQL search condition is:

WHERE columnname comparison_operator search_expression

Defining Search Expressions

You must enclose text strings in single quotation marks. For example, you use the following search condition to match the S_CLASS value in the STUDENT table:

WHERE s_class = 'SR'

Search_expression values within single quotation marks are case sensitive. The search condition S_CLASS = 'sr' does not retrieve rows in which the S_CLASS value is ‘SR’.

When search_expression involves a DATE data value, you must use the TO_DATE function to convert the DATE character string representation to an internal DATE data format. The following search condition matches dates of January 1, 1980:

WHERE s_dob = TO_DATE('01/01/1980', 'MM/DD/YYYY')

Creating Complex Search Conditions

Logical operators AND, OR, and NOT can be used to combine search conditions. For example, the following complex search condition matches all rows in which BLDG_CODE is ‘CR’ and the capacity is greater than 50:

WHERE bldg_code = 'CR' AND capacity > 50

For example, the following search condition matches all course section rows that meet either on Tuesday and Thursday or on Monday, Wednesday, and Friday (at Northwoods University, R denotes courses that meet on Thursday):

WHERE day = 'TR' OR day = 'MWF'

You can use the NOT logical operator to match the logical opposite of a search expression, using this syntax:

WHERE NOT(search_expression)

For example, the following search condition finds all rows in the STUDENT table in which the S_CLASS column has any value other than ‘FR’:

WHERE NOT (s_class = 'FR')

Updating Table Rows

An UPDATE action query also usually contains a search condition to identify the row or rows to update. The general syntax of an UPDATE action query is:

UPDATE tablename

SET column1 = new_value1, column2 = new_value2, ...

WHERE searchƒcondition;

In this syntax, tablename specifies the table whose rows you wish to update. The SET clause lists the columns to update and their associated new values. For example, the following action query changes Teresa Marx’s F_RANK value to ‘ASSOCIATE’ and her F_PIN value to 1181:

UPDATE faculty

SETƒf_rank = 'ASSOCIATE', f_pin = 1181

WHERE f_id = 1;

Deleting Table Rows

The general syntax for a DELETE action query is:

DELETE FROM tablename

WHERE search condition;

Truncating Tables

When you need to delete all of the rows in a table quickly, you can truncate the table, which means you remove all of the table data without saving any rollback information.When you truncate a table, the table structure and constraints remain intact. To truncate a table, you use the TRUNCATE TABLE command, which has the following general syntax:

TRUNCATE TABLE tablename;

You cannot truncate a table that has foreign key constraints as long as the foreign key constraints are enabled. Therefore, you must disable a table’s foreign key constraints before you can truncate the table. Recall that you use the following command to disable an existing constraint:

ALTER TABLE tablename

DISABLE CONSTRAINT constraint_name;

RETRIEVING DATA FROM A SINGLE DATABASE TABLE

The basic syntax for a SQL query that retrieves data from a single database table is:

SELECT columnname1, columnname2,...

FROM ownername.tablename

[WHERE search_condition];

In the FROM clause, ownername specifies the table’s user schema, and tablename specifies the name of the database table. If you are retrieving data from a table in your own user schema, you can omit ownername (and the period) in the FROM clause. If you are retrieving data from a table in another user’s schema, you must include ownername in the FROM clause, and the table’s owner must have granted you the SELECT privilege on the table. For example, you use the following FROM clause to retrieve data values from the LOCATION table in user SCOTT’s user schema:

FROM scott.location

The WHERE clause optionally specifies a search condition that instructs the query to retrieve selected rows. To retrieve every row in a table, the data values do not need to satisfy a search condition, so you can omit the WHERE clause.

If you want to retrieve all of the columns in a table, you can use an asterisk ( * ) as awildcard character in the SELECT clause instead of typing every column name.

Type and execute the following query to select all rows and columns from the LOCATION table:

SELECT *FROM location;

Suppressing Duplicate Rows

Some of the faculty members have the same rank, so the query SELECT f_rank FROM faculty; retrieves duplicate values.The SQL DISTINCT qualifier examines query output before it appears on your screen and suppresses duplicate values.The DISTINCT qualifier has the following general syntax in the SELECT command:

SELECT DISTINCT columnname;

For example, to suppress duplicate faculty ranks, you use the command:

SELECT DISTINCT f_rank FROM faculty;

Using Search Conditions in SELECT Queries

To use an exact search condition in a SELECT query:

SELECT f_first, f_mi, f_last, f_rank

FROM faculty

WHERE f_rank = 'Associate';

Next you create an inexact search condition that retrieves the number of every room in the Business (‘BUS’) building at Northwoods University that has a capacity greater than or equal to 40 seats.You use the greater than or equal to ( >= ) comparison operator in the inexact search condition. To use an inexact search condition in a SELECT query type the following query to retrieve specific rows in the LOCATION table:

SELECT room

FROM location

WHERE bldg_code = 'BUS'

AND capacity >= 40;

Searching for NULL and NOT NULL Values

To search for NULL values, you use the following general syntax:

WHERE columnname IS NULL

Similarly, to retrieve rows in which the value of a particular column is not NULL, you use the following syntax:

WHERE columnname IS NOT NULL

Using the IN and NOT IN Comparison Operators

You can use the IN comparison operator to match data values that are members of a set of search values. For example, you can retrieve all enrollment rows in which the GRADE column value is a member of the set (‘A’,‘B’). Similarly, you can use the NOT IN comparison operator to match values that are not members of a set of search values.

Using the LIKE Comparison Operators

Sometimes, you need to perform searches by matching part of a character string. For example, you might want to retrieve rows for students whose last name begins with the letter M, or find all courses with the text string MIS in the COURSE_NO column. To do this, you use the LIKE operator. The general syntax of a search condition that uses the LIKE operator is:

WHERE columnname LIKE 'character_string'

Character_string represents the text string to be matched and is enclosed in single quotation marks. Character_string must contain either the percent sign ( % ) or underscore ( _ ) wildcard characters. Character_string represents the text string to be matched and is enclosed in single quotation marks. Character_string must contain either the percent sign ( % ) or underscore ( _ ) wildcard characters.

The percent sign ( % ) wildcard character represents multiple characters. If you place ( % ) on the left edge of the character string to be matched, the DBMS searches for an exact match on the far-right characters and allows an inexact match for the characters represented by ( % ). For example, the earch condition WHERE term_desc LIKE '%2006' retrieves all term rows in which the last four characters in the TERM_DESC column are 2006.

The underscore ( _ ) wildcard character represents a single character. For example, the search condition WHERE s_class LIKE '_R' retrieves all values for S_CLASS in which the first character can be any value, but the second character must be the letter R.

You can use the underscore ( _ ) and percent sign ( % ) wildcard characters together in a single search condition. For example, the search condition WHERE c_sec_day LIKE '_T%' retrieves all course sections that meet on Tuesday, provided exactly one character precedes T in the C_SEC_DAY column.The search condition ignores all of the characters that follow T in the column value, so the query retrieves values such as MT,MTW,and MTWRF.

Sorting Query Output

When you insert rows into an Oracle database, the DBMS does not store the rows in any particular order.When you retrieve rows using a SELECT query, the rows may appear in the same order in which you inserted them into the database, or they may appear in a different order, based on the database’s storage configuration.You can sort query output by using the ORDER BY clause and specifying the sort key, which is the column the DBMS uses as a basis for ordering the data.The syntax for a SELECT query that uses the ORDER BY clause is as follows:

SELECT columnname1, columnname2, ...

FROM ownername.tablename

WHERE search_condition

ORDER BY sort_key_column;

USING CALCULATIONS IN SQL QUERIES

· Multiplication, Division *, /

· Addition, Subtraction +, -

For example, you use the following SELECT clause to retrieve the product of the INV_PRICE times the INV_QOH columns in the INVENTORY table:

SELECT inv_price * inv_qoh

Date Calculations

To retrieve the current system date from the database server, you use the SYSDATE pseudocolumn. The following query retrieves the current system date:

SELECT SYSDATE

FROM DUAL;

For example, the following expression specifies a date that is 10 days after the order date (O_DATE) in the Clearwater Traders ORDERS table:

o_date + 10

The following expression returns the number of days between the current date and the order date column in the ORDERS table:

SYSDATE - o_date

To express the calculated ages in years instead of days, you must divide these values by the number of days in a year, which is approximately 365.25. To evaluate the subtraction operation before the division operation, you must place the subtraction operation in parentheses using the following expression:

(SYSDATE - S_DOB)/365.25

To determine the date on which a student enrolled in the university, you subtract the TIME_ENROLLED value from the current system date, as in the following expression:

SYSDATE - time_enrolled

You can also add or subtract intervals from one another to calculate the sum or difference of two intervals. For example, suppose you need to update the TIME_ENROLLED column every month by adding one month to the interval value.You use the following query to add an interval of one month to the current TIME_ENROLLED value:

SELECT s_id, time_enrolled + TO_YMINTERVAL('0-1')

FROM student;

Similarly, you use the following query to add an interval of 10 minutes to the C_SEC_DURATION column in the COURSE_SECTION table:

SELECT c_sec_id, c_sec_duration +

TO_DSINTERVAL('0 00:10:00')

FROM course_section;

Single-row Number Functions

Single-row Character Functions

Single-row Date Functions

ORACLE10g SQL GROUP FUNCTIONS

Using the COUNT Group Function

The COUNT group function returns an integer that represents the number of rows that a query returns. The COUNT(*) version of this function calculates the total number of rows in a table that satisfy a given search condition.

Using the GROUP BY Clause to Group Data

If a query retrieves multiple rows and the rows in one of the retrieved columns have duplicate values, you can group the output by the column with duplicate values and apply group functions to the grouped data. The GROUP BY clause has the following syntax:

GROUP BY group_columnname;

Using the HAVING Clause to Filter Grouped Data

You can use the HAVING clause to place a search condition on the results of queries that display group function calculations. The HAVING clause has the following syntax:

HAVING group_function comparison_operator value

For example, suppose you want to retrieve the total capacity of each building at Northwoods University, but you are not interested in the data for buildings that have a capacity of less than 100.You use the following HAVING clause to filter the output:

HAVING SUM(capacity) >= 100

FORMATTING OUTPUT IN SQL*PLUS

So far, you have accepted the default output formats in SQL*Plus—output column headings are the same as the database column names.

To specify alternate output heading text, you use the following syntax in the SELECT clause:

SELECT columnname1 "heading1_text",

columnname2 "heading2_text", ...

Aliases

An alias is an alternate name for a query column. After you create an alias, you can reference the alias in other parts of the query, such as in the GROUP BY or ORDER BY clause. The general syntax for creating an alias is:

SELECT columnname1 AS alias_name1...

JOINING MULTIPLE TABLES

SELECT column1, column2,...

FROM table1, table2

WHERE table1.joincolumn = table2.joincolumn

AND search_condition(s)

Inner Joins

The simplest type of join occurs when you join two tables based on values in one table being equal to values in another table.This type of join is called an inner join, equality join, equijoin, or natural join.

Query Design Diagram

Outer Joins

An inner join returns rows only if values exist in all tables that are joined. If no values exist for a row in one of the joined tables, the inner join does not retrieve the row. An outer join returns all rows from one table, which is called the inner table. An outer join also retrieves matching rows from a second table, which is called the outer table.

To create an outer join in Oracle10g SQL, you label the outer table in the join condition using the following syntax:

inner_table.join_column = outer_table.join_column(+)

The outer join operator ( + ) signals the DBMS to insert a NULL value for the columns in the outer table that do not have matching rows in the inner table.

Self-joins

To create a self-join, you must create a table alias and structure the query as if you are joining the table to a copy of itself. A table alias is an alternate name that you assign to the table in the query’s FROM clause.The syntax to create a table alias in the FROM clause is:

FROM table1 alias1, ...

Introduction to PL/SQL

©Bob Godfrey, 2008.

PL/SQL is delivered as part of SQL*Plus, and is used to extend the capabilities of SQL by adding procedural elements using traditional programming constructs.

If you are new to programming, then the prospect of learning PL/SQL might seem a little daunting. In essence however, a program is nothing more than a set of instructions for achieving some purpose. The recipe for Australia's traditional Anzac Biscuits shown right incorporates both the elements and the structure of a simple program. First, the recipe is named (in this case Anzac Biscuits). Second, there are some declarations - in this case the list of ingredients. Third, following these declarations, there are a list of instructions which are to be obeyed in sequence. In a program, we would have a program (or subprogram) name, followed by some declarations of variables to be used, and finally, the instructions (called statements) themselves. These executable statements would be enclosed in a pair of "begin"..."end" brackets, just as in the recipe the horizontal bar was used to separate the ingredients from the cooking instructions.

Anzac Biscuits Ingredients 1 cup rolled oats 1 cup plain flour, sifted 1 cup sugar ¾ cup dessicated coconut 150g butter 2 tablespoons golden syrup 2 tablespoons boiling water 1 ½ teaspoons bicarbonate of soda Combine oats, sifted flour, sugar and coconut in a large bowl. Melt butter and golden syrup together in a small pan. Mix boiling water and bicarbonate of soda together. Blend into the butter mixture. Pour over dry ingredients and mix well. Place spoonfuls of mixture onto greased baking trays, allowing room to spread out. Bake at 150°C for 15-20 minutes, or until golden. Loosen while warm. Cool on trays, then transfer to a wire rack to cool completely. Store in an airtight container.

PL/SQL block structure

Unnamed block

declare
… … …
begin
… … …
end;

Stored procedure

create or replace procedure_name
as
… … … {declarations automatically follow the as statement}
… … …
begin
… … …
… … …
exception
… … …
end;

Nested blocks

create or replace procedure_name
as
… … …
… … …
begin
… … …
… … …
begin
… … …
… … …
exception
… … …
end;
… … …
… … …
exception
… … …
end;

A simple PL/SQL program

1. First, start up Oracle, and at the SQL> prompt type in the command SET SERVEROUTPUT ON.

(This enables the display of output from procedures like DBMS_OUTPUT.PUT_LINE to appear.)

2. Type the following text using say Windows Notepad. (While PL/SQL is not case sensitive, we here shall use the convention of typing reserved words in upper case, and user-assigned names in lower case. Whether or not you follow this convention is a matter of personal choice.)

DECLARE
-- variable to hold the current date
TodaysDate DATE;
BEGIN
TodaysDate := SYSDATE;
DBMS_OUTPUT.PUT_LINE(‘Today is ‘);
DBMS_OUTPUT.PUT_LINE(TodaysDate);
END;

[Note that PL/SQL is a strongly typed language, which means all variables must be declared prior to use]

3. Save this text for later use in a file (say called example1.txt).
4. Copy the text in Notepad, and having fired up Oracle, paste it into SQL*Plus.
5. Press the Enter key to get to a new prompt line, and then type /, followed by Enter again, and your program should execute.

If you get a message like {Warning: Created with compilation errors} then try the command SHOW ERRORS to examine the details of the coding errors found.

Variables and Types

Database and other information is handled by PL/SQL through variables. Each variable must be declared to be of a defined type. The types available include all the types used in SQL for database table columns, plus generic types used in PL/SQL like the NUMBER type. For example

DECLARE
unit_price NUMBER;
description VARCHAR(15);

It is important that if a PL/SQL variable is going to be used to store or retrieve data in a database column, that the variable be of the same type as the column. One way to do this is to use the %TYPE operator, as in

DECLARE
required_code product.id%TYPE

which declares that the variable "required_code" will have the same type as the column "id" of the "product" table.

We can in addition declare a variable to be suitable to hold a complete row of a table (a record with several fields) by

DECLARE
product_tuple product%ROWTYPE;

All variables are given an initial value of NULL unless declared otherwise, as in

DECLARE
x NUMBER := 3;

Program Structure

According to programming theory, all programs can be built up with combinations of three basic structures - sequence, selection, and iteration. The program described above used the simplest of these three structures - the sequence. The program example that follows uses the more complex selection structure.

Date and Character String Manipulation

The file DateTime.sql contains the following code:

remark - Requires SET SERVEROUTPUT ON command for
remark - DBMS_OUTPUT.PUT_LINE results to be displayed.
DECLARE
TodaysDate DATE;
Today VARCHAR2 (9);
TimeNow CHAR (5);
Hour24 CHAR(2);
Meridian CHAR (4);
BEGIN
TodaysDate := SYSDATE;
Today := TO_CHAR(TodaysDate, 'DAY');
Today := RTRIM(Today);
Today := SUBSTR(Today,1,1) || LOWER(SUBSTR(Today,2,LENGTH(Today) - 1));
DBMS_OUTPUT.PUT_LINE('Today is ' || Today || ',' || TO_CHAR(TodaysDate));
TimeNow := TO_CHAR(TodaysDate, 'HH:MI');
Hour24 := TO_CHAR(TodaysDate, 'HH24');
IF Hour24 < '12:00' THEN
Meridian := 'A.M.';
ELSE
Meridian := 'P.M.';
END IF;
DBMS_OUTPUT.PUT_LINE('Your session started at ' || TimeNow || ' ' || Meridian);
END;
/

When run, this script produces the following typical output:

Today is Monday,05-NOV-07 Your session started at 02:11 P.M.

[If no output is produced, make sure that you have used the SET SERVEROUTPUT ON command at the SQL> prompt.]

If you are new to programming, or hesitant about jumping into PL/SQL at the deep end, there is a step-by-step analysis of this program that might help break the ice.

Working through the program code step-by-step, SYSDATE delivers today’s system date. [Note that like all Oracle dates, this also includes the time.]

The function TO_CHAR converts either a number or a a date to a character string. When used with dates, the function is expressed as TO_CHAR(,). [If the date_format_mask is omitted, the date is converted to a default DD-MON-YY format, for example 27-JUL-08.]

Common formats used with dates are:

Format	Returns	Example
YYYY	Four digit year	2008
Y or YY or YYY	Last one, two or three digits of year	8 or 08 or 008 for 2008
BC or AD	B.C. or A.D.	2008 A.D.
RR	Last two digits of the year, but modified to cross century boundaries. If the current year lies in 0..49, then a return of 0 through 49 refers to the current century, and 50 through 99 for the last century. If the current year lies in 50..99, then a return of 0 through 49 refers to the previous century, and 50 through 99 for the current century.	98 for 1998 15 for 2015
SYEAR or YEAR	Year spelled out; using an S places a minus sign before B.C. dates.	TWO THOUSAND EIGHT
Q	Quarter of the year	returns 2 for dates between April and June inclusive
MM	Month	Jan = 01..Dec=12
RM	Roman numeral month	IV for April
MONTH	Name of month as a nine-character upper-case string	FEBRUARY
Month	Name of month as a nine-character mixed-case string	February
MON	Three-letter abbreviation for the name of the month	FEB
WW	Week of year
W	Week of month
DDD	Day of year	Jan 1st is 1, Feb 1st is 32 etc.
DD	Day of the month (01..31)	28
DDTH	Day of the month as an ordinal number	28TH
D	Day of the week (1-7)	4 for Wednesday
DAY	Day of the week, spelt out in upper-case	FRIDAY
Day	Day of the week, spelt out in mixed-case	Friday
DY	Abbreviated day name	SUN, MON etc.
AM, PM, A.M., P.M.	Meridian indicator (with and without periods)	AM
HH or HH12	Hour of day (using 12-hour clock)	11
HH24	Hour of day (using 24 hour clock)	15
MI	Minutes	(0..59)
SS	Seconds	(0..59)

The RTRIM function removes trailing spaces from a character string.

Common string manipulation functions include:

Function	Returns
SUBSTR(,,)	Extracts length characters from the string, starting at character number start
INSTR(,)	the character position within string where there is a match for the text.
LENGTH()	the length of string.
RTRIM()	Removes trailing spaces from string
LOWER()	Converts the string to lower case

String concatenation is achieved with the symbol ||.

IF/THEN/ELSE construct

The code described above used the selection structure IF… THEN… ELSE … This construct can be nested, as in the code fragment:

Today := RTRIM(TO_CHAR(SYSDATE,‘DAY’);
IF Today = ‘SUNDAY’ THEN
DBMS_OUTPUT.PUT_LINE(‘It is the weekend’);
ELSE
IF Today = ‘SATURDAY’ THEN
DBMS_OUTPUT.PUT_LINE(‘It is the weekend’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘It is a weekday’);
END IF;
END IF;

Multiway branches can be coded as

IF THEN
ELSEIF THEN

ELSEIF THEN
ELSE

END IF;

for example

IF age<18 THEN salary:=1000 ;
ELSEIF age < 25 THEN salary:=1500;
ELSEIF age < 35 THEN salary:=2000;
ELSE salary:=2500;
END IF;

Using SQL commands within PL/SQL blocks

The command file PLSQLselect.sql contains the following code:

DECLARE
pname varchar(20);
price number;
stock number;
BEGIN
SELECT product_name,unit_price, on_hand
INTO pname, price, stock
FROM product_t
WHERE unit_price = (SELECT MAX(unit_price) FROM product_t);
DBMS_OUTPUT.PUT('There are '||TO_CHAR(stock,'99')||' units of the product '||
pname||' in stock,');
DBMS_OUTPUT.PUT_LINE(' each valued at '||TO_CHAR(price,'$999.99'));
END;
/

and uses PL/SQL to format the output of an SQL SELECT statement, in this case to identify the product with the highest unit price, producing

The command file includes a special form of the SELECT statement with the selected values being stored INTO the declared variables listed. This only works for retrieval of a single row (and so this routine will not work if, as might happen, there are two or more products with the same highest price). It is possible to use select where multiple rows will be retrieved, but this requires the use of cursors, which we will cover in a later session.

We can also use the INSERT command in a PL/SQL block, as in the command file InsertForLoop.sql

DROP TABLE seat;
CREATE TABLE seat (SeatRow CHAR(1), SeatColumn NUMBER);
DECLARE
RowCount BINARY_INTEGER;
ColCount BINARY_INTEGER;
Rows CHAR (3);
AlphaRow CHAR;
BEGIN
Rows := 'ABC';
FOR RowCount IN 1..3
LOOP
AlphaRow := SUBSTR(Rows,RowCount,1);
FOR ColCount IN 1..5
LOOP
INSERT INTO seat VALUES(AlphaRow, ColCount);
END LOOP;
END LOOP;
END;
/

which creates a seating file for an auditorium with 3 rows of seats (A, B, and C), and 5 seats per row (1..5). The SUBSTR(s, n, l) extracts l characters from the string s, starting at the n'th character.

The construct

FOR IN .. LOOP

END LOOP;

has been used to control the iterative process, and produce the following table contents:

Loop constructs in PL/SQL

As well as the construct already described

FOR IN .. LOOP

END LOOP;

there is also (for a decrementing index)

FOR IN REVERSE .. LOOP

END LOOP;

There is also a WHILE loop construct

WHILE LOOP

END LOOP;

where the LOOP is repeated as long as the stated condition holds TRUE. Clearly, for the loop to terminate, at least one statement in the loop body must be capable of making the condition take the value FALSE, for example

X := 2;
WHILE X < 10 LOOP
Y:=Y+X;
X := X+2
END LOOP;

which computes in Y the sum of all single-digit even numbers;

The basic form of iterative control is the simple LOOP, as in

LOOP

END LOOP;

The statements should include an exit statement, otherwise the loop will be executed infinitely. Thus the code fragment

X := 1;
LOOP
Y:=X*X;
IF Y >=50 THEN
EXIT;
END IF;
END LOOP;

will find the highest number whose square is less than or equal to 50.

The EXIT WHEN statement ends a loop conditionally, as in

X := 1;
LOOP
Y:=X*X;
EXIT WHEN Y >=50;
END LOOP;

The UPDATE command in PL/SQL

Consider the following procedure named debit_account, which debits a bank account: When invoked or called, this procedure accepts an account number and a debit amount. It uses the account number to select the account balance from the accts database table. Then, it uses the debit amount to compute a new balance. If the new balance is less than zero, an exception is raised; otherwise, the bank account is updated.

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts
WHERE acct_no = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance
WHERE acct_no = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;

As an exercise

1. Modify the PL/SQL program DateTime.sql to display the date in the form Monday, 5th Nov 2007.
2. Write a PL/SQL program to print out a small (say 5x5) multiplication table.
3. Construct a version of InsertForLoop.sql that will add a field price to each row, and will set the seat price to $20 if in the front (A) row, and $15 otherwise.

References

Many of the examples described above are drawn from chapter 4 of Morison, J. & Morison, M. A Guide to Oracle 9i, 2003, published by Course technology.