April 18th, 2007
Group functions are also called multiple-row functions. These functions return a result for a group of rows that they process. To add grouping capabilities the SELECT clause will have two new clauses GROUP BY and HAVING.
SELECT * | colname, colname…
FROM tablename
[WHERE condition]
[GROUP BY colname, colname…]
[HAVING group condition];
When using group functions there are two things that need to be kept in mind:
1. Using the DISTINCT keyword will include only unique values. The ALL keyword is defaulted and instructs Oracle to include all values except null.
2. All group functions except the COUNT(*) will ignore NULL values. Read the rest of this entry »
Posted in Oracle, School | No Comments »
April 5th, 2007
When you select data, there’s a good chance that you’re looking for something specific. In using the SELECT statement you can attach a WHERE clause to limit the data that you are looking through. If you’re very good and know what you’re looking for then you can use the WHERE clause to find exactly what you want.
WHERE Clause Syntax
SELECT [ DISTINCT | UNIQUE ] ( *, columnname, [ AS alias ], … )
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY columnname ];
Read the rest of this entry »
Posted in Oracle, School | No Comments »
March 29th, 2007
Joining tables brings together data from two or more tables in one query in order to get all the information you need. There are six different types of joins:
|
Element
|
Description
|
|
Cartesian Join
|
This join replicates each row from the first table with every row from the second table. This will display every possible combination and can be implemented by two methods:
- Not including a joining condition in the WHERE clause.
- Using the JOIN method with the CROSS JOIN keywords.
|
|
Equality Join
|
Through a commonly named and defined column a join can be crated. This also has two methods:
- Using the WHERE clause.
- Using the JOIN method with the NATURAL JOIN, JOIN….ON, JOIN….USING keywords.
|
|
Non-Equality Join
|
Joins tables when there are no equivalent rows.
- Using the WHERE clause.
- Using the JOIN method with the JOIN….ON keywords.
|
|
Self-Join
|
Joins a table to itself.
- Using the WHERE clause.
- Using the JOIN method with the JOIN….ON keywords.
|
|
Outer Join
|
Include records of a table in output when there is no matching record in the other table.
- Using the WHERE clause with a ( + ) operator.
- Using the JOIN method with the OUTER JOIN keywords with the assigned type of LEFT, RIGHT, FULL
|
|
Set Operators
|
Combines results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT, and MINUS.
|
Read the rest of this entry »
Posted in Oracle, School | No Comments »
March 3rd, 2007
Creating a user
Like table names and columns, a user name must follow the same rules. There is one minor difference. A user name can contain a dollar sign.
CREATE USER username
[ IDENTIFIED BY password ]
[ PASSWORD EXPIRE ]
The syntax for creating a user is pretty straight forward. The PASSWORD EXPIRE is used for when the user logs on for the first time and makes them to change their password. Once a user has been created they are not allowed to log into the system because they have no privileges at all.
Posted in Oracle, School | No Comments »
February 24th, 2007
If you log on to the computer and you see a message like this:
svchost.exe — application error the instruction at “0×745f2780″ reference memory at “0×00000000″. the memory could not be ‘read’
Chances are that you have corrupted Windows Update data. To fix this problem follow these steps: Read the rest of this entry »
Posted in Operating Systems, XP | 1 Comment »
February 23rd, 2007
A database object is anything that has a name and a defined structure. Other database objects that are used in Oracle 10g are sequences, indexes, and synonyms.
- A sequence will generate sequential integers that can be used to assist with internal controls or serve as primary keys for tables.
- A database index serves the same basic purpose as an index in a book.
- A synonym is a nick name that is given to an object that may have a complex name. There are two types of synonyms, one being a private, which is used only by an individual on objects they own, and the other being public, which is used by others to access another’s database objects.
Posted in Oracle, School | 1 Comment »
February 19th, 2007
The keywords that Manipulate data and control transactions are called Data Manipulation Language or DML. With out the DML a database would be useless and a waste.
Inserting Data
Syntax
INSERT INTO tablename [(columnname, …)]
VALUES (datavalue, …);
When inserting data if you are inserting a full row of data, placing data in all columns then you can omit the column names as long as you follow the order of the columns in the table. If there are no constraints that require a value to be inserted into the column you can opt out those values but you must then name the columns you wish to place data in if you either omit a column or change the order of data. Another way of not naming the columns is to insert a NULL into the column.
Read the rest of this entry »
Posted in Oracle, School | 5 Comments »
February 14th, 2007
Constraints are used to keep accurate data. In oracle you can apply these policies to a column or table. There are five different types of constraints:
| Constraint |
Abbreviation |
Description |
| PRIMARY KEY |
_pk |
This is a unique field that is not allowed to have null values. |
| FOREIGN KEY |
_fk |
This constraint is placed in the “many” table. The data that’s entered in this field must have a value in the parent table. |
| UNIQUE |
_uk |
All data that is stored in a column with a UNIQUE constraint on it will not have two of the same values. This constraint is a bit different from a PRIMARY KEY where the PRIMARY KEY does not allow NULLs, a UNIQUE constraint does allow NULLs. |
| CHECK |
_ck |
This constraint ensures that a specified condition is true before any data is added to the table. |
| NOT NULL |
_nn |
This makes sure that there is a value going into the table. |
Posted in Oracle, School | No Comments »
February 14th, 2007
When creating tables in Oracle we have to follow some rules in order to successfully create a table:
- Table names and columns can be up to 30 characters long.
- Names of tables and columns cannot have any blank spaces.
- Numbers, underscores, and number signs are allowed in the table and column name.
- Each table that a user owns must have a unique name and each column in that table must have a unique column name.
- You can’t use reserved keywords as a table or column name.
Read the rest of this entry »
Posted in Oracle, School | No Comments »
February 7th, 2007
With every new operating system comes headaches as we find that our favorite programs or hardware no longer works with it. If you’re like me, you’re probably being forced into using Windows because everyone else does. Then again, if I had Linux installed on my box along side Vista, I’d feel a bit dirty and I’d be using a real boot loader like GRUB. In Vista Microsoft did away with the ntldr.exe and boot.ini files and replaced it with a new program.
Startup Process
After the system gives control to the Master Boot Record, Vista then takes charge of the system and starts itself:
1. The MBR reads the boot sector of the active partition and executes the Bootmgr.exe program.
2. The Boot Manager reads the configuration file and gives the user the choices it that’s contained in that file.
3. Once a choice is selected one of the following is done:
- When starting any edition of Vista, the %SystemRoot%\System32\Winload.exe is executed.
- When resuming Vista from Hibernation the boot manager program executes Winresume.exe which will restore the system to the way it was before it went into hibernation.
- When choosing an earlier version of Windows, the boot manager will locate the ntldr and you’ll have to go through another boot loader.
Read the rest of this entry »
Posted in Operating Systems, Vista | No Comments »