SQL is a popular and useful programming language. You can make SQL even more useful if you know the phases of SQL development, the criteria for normal forms, the data types used by SQL, a little bit about set and value functions, as well as some tips on how to filter tables with WHERE clauses.
>
>
Phases of SQL System Development
In developing any system, you start at the beginning and go through to the end, and it's no different with SQL. The following list shows you what to consider at each phase of the SQL development lifecycle:
Definition Phase: Precisely define the problem to be solved, its magnitude, and who will work on it.
Requirements Phase: Develop a detailed description of exactly what the development effort will produce. Gather all relevant information and put it into a requirements document (Statement of Requirements). Get client signoff.
Evaluation Phase: Determine exactly how you will meet the requirements. What tools will you use? How will you deploy your development team? Determine whether the job is doable within time and budget constraints.
Design Phase: Create a database model and then design a database and database application that satisfy the terms of the requirements document.
Implementation Phase: Build the database and the database application. Include copious documentation within the code and in external documents.
Final Documentation and Testing Phase: Give the database and application a tough workout. Hit the system with every conceivable input condition and a few inconceivable ones. Try to overload it. See where it breaks. When it breaks, send it back to the implementers or even back to the designers. Document everything.
Maintenance Phase: Fix latent bugs as they arise. Provide updates and enhancements called for by the client.
>
>
>
SQL Criteria for Normal Forms
In SQL, normal forms are defining characteristics of relational databases. SQL forms get classified according to the types of modification anomalies they're subject to. First, second, and third normal forms (1NF, 2NF, 3NF) serve as remedies to the three main sources of modification anomalies.
The normal forms are nested in the sense that a table that's in 2NF is automatically also in 1NF. Similarly, a table in 3NF is automatically in 2NF, and so on. For most practical applications, putting a database in 3NF is sufficient to ensure a high degree of integrity. To be absolutely sure of its integrity, you must put the database into DK/NF.
The following lists lay out the criteria for each form:
First Normal Form (1NF):
Table must be two-dimensional, with rows and columns.
Each row contains data that pertains to one thing or one portion of a thing.
Each column contains data for a single attribute of the thing being described.
Each cell (intersection of row and column) of the table must be single-valued.
All entries in a column must be of the same kind.
Each column must have a unique name.
No two rows may be identical.
The order of the columns and of the rows does not matter.
Second Normal Form (2NF):
Table must be in first normal form (1NF).
All nonkey attributes (columns) must be dependent on the entire key.
Third Normal Form (3NF):
Table must be in second normal form (2NF).
Table has no transitive dependencies.
Domain-Key Normal Form (DK/NF):
Every constraint on the table is a logical consequence of the definition of keys and domains.
>
>
>
SQL Data Types
Depending on their histories, different SQL implementations support a variety of data types. The SQL specification recognizes nine predefined general types, shown in the lists below
Exact Numerics:
INTEGER
SMALLINT
BIGINT
NUMERIC
DECIMAL
Approximate Numerics:
REAL
DOUBLE PRECISION
FLOAT
Boolean:
BOOLEAN
Character Strings:
CHARACTER
CHARACTER VARYING (VARCHAR)
NATIONAL CHARACTER
NATIONAL CHARACTER VARYING
Datetimes:
DATE
TIME
TIMESTAMP
TIME WITH TIMEZONE
TIMESTAMP WITH TIMEZONE
Intervals:
INTERVAL DAY
INTERVAL YEAR
Large Objects:
BLOB
CLOB
Collection Types:
ARRAY
MULTISET
Other Types:
ROW
XML
>
>
>
SQL Value Functions
You use SQL value expressions to combine two or more values. Several kinds of SQL value expressions exist, corresponding to the different data types; the following tables list string, numeric, and datetime values, functions, and effects:
| Function | Effect |
|---|---|
| SUBSTRING | Extracts a substring from a source string |
| UPPER | Converts a character string to all uppercase |
| LOWER | Converts a character string to all lowercase |
| TRIM | Trims off leading or trailing blanks |
| TRANSLATE | Transforms a source string from one character set to another |
| CONVERT | Same as TRANSLATE. (It transforms a source string from one character set to another.) |
| Function | Effect |
|---|---|
| POSITION | Returns the starting position of a target string within a source string |
| CHARACTER_LENGTH | Returns the number of characters in a string |
| OCTET_LENGTH | Returns the number of octets (bytes) in a character string |
| EXTRACT | Extracts a single field from a datetime or interval |
| Function | Effect |
|---|---|
| CURRENT_DATE | Returns the current date |
| CURRENT_TIME(p) | Returns the current time; (p) is precision of seconds |
| CURRENT_TIMESTAMP(p) | Returns the current date and the current time; (p) is precision of seconds |
>
>
>
SQL Set Functions
Sometimes, the information that you want to extract from an SQL table doesn't relate to individual rows but rather to sets of rows. SQL provides five set (or aggregate) functions to deal with such situations — COUNT, MAX, MIN, SUM, and AVG. Each function performs an action that draws data from a set of rows rather than from a single row:
| COUNT | Returns the number of rows in the specified table |
| MAX | Returns the maximum value that occurs in the specified table |
| MIN | Returns the minimum value that occurs in the specified table |
| SUM | Adds up the values in a specified column |
| AVG | Returns the average of all the values in the specified column |
>
>
>
SQL WHERE Clause Predicates
The WHERE clause is an SQL filter that passes rows that meet the search condition and rejects rows that don't meet the condition. By including WHERE clauses in your SQL SELECT statements, you can restrict the rows that you place into the resulting table to those that satisfy specific conditions. The following table lists the predicates to use to filter for the information you want:
| = | Equal |
| <> | Not equal |
| < | Less than |
| <= | Less than or equal |
| > | Greater than |
| >= | Greater than or equal |
| ALL | BETWEEN |
| DISTINCT | EXISTS |
| IN | LIKE |
| MATCH | NOT IN |
| NOT LIKE | NULL |
| OVERLAPS | SIMILAR |
| SOME, ANY | UNIQUE |
>
>
dummies
Source:http://www.dummies.com/how-to/content/sql-allinone-for-dummies-cheat-sheet.html
No comments:
Post a Comment