Querying a database is just half of the battle. The other half is making the data that is pulled out of the table look pretty. Reports are used for those non-techie, higher ups that like it when things look pretty.
Views have two purposes:
Views are Database objects and are treated like tables. Unlike tables, views do not store any data. Views simply store a query.
HTML Source EditorWord wrap Sub queries come in handy when you want to find a record but need to know what exactly to look for. For example, if you want to find all the items that are more expensive than a specific item. Instead of issuing two SELECT statements, you can use sub queries to find the answer in just one query.
When designing Sub queries, the first query that is designed is the sub query. In order to execute sub queries correctly, you must determine what it is that you are looking for. After successfully testing the sub query, it is this that will be passed as input to the parent query or the outer query.
A function is a block of predefined code. Single-row functions return only a single row for each record processed. These functions include functions that deal with characters to dates.
| Type of Functions | Functions |
| Case conversion | UPPER, LOWER, INITCAP |
| Character manipulation | SUBSTR, INSTR, LENGTH, LPAD/RPAD, RTRIM/LTRIM, REPLACE, TRANSLATE, CONCAT |
| Numeric | ROUND, TRUNC, MOD, ABS |
| Date | MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, TO_DATE, ROUND, CURRENT_DATE |
| Regular expressions | REGEXP_LIKE, REGEXP_SUBSTR |
| Other | NVL, NVL2, TO_CHAR, DECODE, SOUNDEX, TO_NUMBER |
When using a function in a SELECT the output will be modified accordingly but the data will not change in the database unless the function is used in an INSERT statement. (more…)
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. (more…)
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.
SELECT [ DISTINCT | UNIQUE ] ( *, columnname, [ AS alias ], … )
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY columnname ];
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:
|
|
Equality Join |
Through a commonly named and defined column a join can be crated. This also has two methods:
|
|
Non-Equality Join |
Joins tables when there are no equivalent rows.
|
|
Self-Join |
Joins a table to itself.
|
|
Outer Join |
Include records of a table in output when there is no matching record in the other table.
|
|
Set Operators |
Combines results of multiple SELECT statements. Includes the keywords UNION, UNION ALL, INTERSECT, and MINUS. |
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.
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.
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.
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.