[ Pobierz całość w formacie PDF ]

section...
Nonstandard SQL..."check local listings"
" INTERSECT and MINUS are like the UNION statement, except that INTERSECT produces rows that
appear in both queries, and MINUS produces rows that result from the first query, but not the second.
" Report Generation Features: the COMPUTE clause is placed at the end of a query to place the result of an
aggregate function at the end of a listing, like COMPUTE SUM (PRICE); Another option is to use break
logic: define a break to divide the query results into groups based on a column, like BREAK ON
BUYERID. Then, to produce a result after the listing of a group, use COMPUTE SUM OF PRICE ON
BUYERID. If, for example, you used all three of these clauses (BREAK first, COMPUTE on break second,
COMPUTE overall sum third), you would get a report that grouped items by their BuyerID, listing the sum
of Prices after each group of a BuyerID's items, then, after all groups are listed, the sum of all Prices is
listed, all with SQL-generated headers and lines.
" In addition to the above listed aggregate functions, some DBMS's allow more functions to be used in Select
lists, except that these functions (some character functions allow multiple-row results) are to be used with
an individual value (not groups), on single-row queries. The functions are to be used only on appropriate
data types, also. Here are some Mathematical Functions:
ABS(X) Absolute value-converts negative numbers to positive, or leaves positive numbers alone
CEIL(X) X is a decimal value that will be rounded up.
FLOOR(X) X is a decimal value that will be rounded down.
GREATEST(
Returns the largest of the two values.
X,Y)
LEAST(X,Y) Returns the smallest of the two values.
MOD(X,Y) Returns the remainder of X / Y.
POWER(X,Y) Returns X to the power of Y.
ROUND(X,Y) Rounds X to Y decimal places. If Y is omitted, X is rounded to the nearest integer.
SIGN(X) Returns a minus if X
SQRT(X) Returns the square root of X.
Character Functions
LEFT(
Returns the leftmost X characters of the string.
g>,X)
RIGHT(
Returns the rightmost X characters of the string.
ng>,X)
UPPER(
Converts the string to all uppercase letters.
ng>)
LOWER(
Converts the string to all lowercase letters.
ing>)
INITCAP(
Converts the string to initial caps.
ring>)
LENGTH(
Returns the number of characters in the string.
ring>)
||
ing> followed by the second.
LPAD(
g>,X,'*') X characters long.
RPAD(
g>,X,'*') X characters long.
SUBSTR(
Extracts Y letters from the string beginning at position X.
ing>,X,Y)
NVL(
n>,) value of is not NULL, NVL has no effect.
Syntax Summary--For Advanced Users Only
Here are the general forms of the statements discussed in this tutorial, plus some extra important ones (explanations
given). REMEMBER that all of these statements may or may not be available on your system, so check
documentation regarding availability:
ALTER TABLE ADD|DROP|MODIFY (COLUMN SPECIFICATION[S]...see
Create Table); --allows you to add or delete a column or columns from a table, or change the specification
(data type, etc.) on an existing column; this statement is also used to change the physical specifications of a table
(how a table is stored, etc.), but these definitions are DBMS-specific, so read the documentation. Also, these
physical specifications are used with the Create Table statement, when a table is first created. In addition, only one
option can be performed per Alter Table statement--either add, drop, OR modify in a single statement.
COMMIT; --makes changes made to some database systems permanent (since the last COMMIT; known as a
transaction)
CREATE [UNIQUE] INDEX
ON (); --UNIQUE is optional; within brackets.
CREATE TABLE
( [()] ,
...other columns); (also valid with ALTER TABLE)
--where SIZE is only used on certain data types (see above), and constraints include the following possibilities
(automatically enforced by the DBMS; failure causes an error to be generated):
1. NULL or NOT NULL (see above)
2. UNIQUE enforces that no two rows will have the same value for this column
3. PRIMARY KEY tells the database that this column is the primary key column (only used if the key is a one
column key, otherwise a PRIMARY KEY (column, column, ...) statement appears after the last column
definition.
4. CHECK allows a condition to be checked for when data in that column is updated or inserted; for example,
CHECK (PRICE > 0) causes the system to check that the Price column is greater than zero before
accepting the value...sometimes implemented as the CONSTRAINT statement.
5. DEFAULT inserts the default value into the database if a row is inserted without that column's data being
inserted; for example, BENEFITS INTEGER DEFAULT = 10000
6. FOREIGN KEY works the same as Primary Key, but is followed by: REFERENCES
(), which refers to the referential primary key.
CREATE VIEW AS ;
DELETE FROM WHERE ;
INSERT INTO [()]
VALUES ();
ROLLBACK; --Takes back any changes to the database that you have made, back to the last time you gave a
Commit command...beware! Some software uses automatic committing on systems that use the transaction features,
so the Rollback command may not work. [ Pobierz całość w formacie PDF ]