Functions

ABS Function:

ABS returns the absolute value. The output is positive even if the input is negative:

  ABS(x) = x if x>=0
ABS(x) = -x if x<0

ABS can be useful for testing values that are "close". For example this query shows each country that has area that is roughly 70 thousand. The value 70000 is the target value, 500 is the "tolerance" so the test ABS(area-70000)<500 style="" border="1">name area Georgia 69700 Ireland 70182

CASE

CASE allows you to return different values under different conditions.

If there no conditions match (and there is not ELSE) then NULL is returned.

  CASE WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE def_value
END

SELECT name, population
 ,CASE WHEN population<1000000>
name
 
population
 
result
 
 
 
Monaco
 
32000
 
small
 
 
 
Liechtenstein
 
34000
 
small
 
 
 
Bahamas
 
321000
 
small
 
 
 
The Maldives
 
338000
 
small
 
 
 
Brunei
 
374000
 
small
 
 
 
Malta
 
397000
 
small
 
 
 
Armenia
 
3000000
 
medium

Select statements.

Show who won the 1962 prize for Literature.
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'

2) Show the year and subject that won 'Albert Einstein' his prize.
select yr,subject from nobel where winner like 'Albert Einstein';

3)Give the name of the 'Peace' winners since the year 2000, including 2000.
select winner from nobel where subject = 'peace' and yr >= 2000;

4) Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.
select * from nobel where subject = 'Literature' and yr between 1980 and 1989;

5) Show all details of the presidential winners: ('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter')
select * from nobel where winner in ('Theodore Roosevelt', 'Woodrow Wilson', 'Jed Bartlet', 'Jimmy Carter');

6) Show the winners with first name John
select winner from nobel where winner like 'john%';

SQL - Platforms

SQL - MySQL and PostgreSQL

MySQL and PostgreSQL are open source database programs rich in functionality and flexibility. They are often the choice of web developers and small businesses simply because they get the job done for a very reasonable price. Also they will go anywhere and can operate on nearly every operating system available.

SQL - SQL Server

Microsoft's SQL Server is steadily on the rise in the commercial world gaining popularity slowly. This platform has a GUI "Windows" type interface and is also rich with functionality. A free trial version can be downloaded at the Microsoft web site, however it is only available to Windows users.

SQL - DB2 and Oracle

By far the selection of choice for large corporations is either Oracle or DB2. Companies that have large ties to IBM stick to their DB2 software whereas others have made the switch to Oracle. These systems run on personal computers as well as large corporate mainframes.

The Database Model

Dr. Edgar F. Codd was the founding father of what is known as the relational model of databases. In 1970, he published a groundbreaking article "A Relational Model of Data for Large Shared Data Banks." Included within the article were 12 rules of relational databases. These rules are as follows (paraphrased).

1. Information Rule
   All data in the database should be represented as values in tables.
2. Guaranteed Access
   Every piece of data should be accesible by using the table name, primary key, and a column name.
3. Treatment of NULL Values
   Null values must be treated as incomplete data pieces. Nulls are not to be confused with zeros.
4. Active-Online Relational Catalog
   A database is represented at a logical level of tables.
5. Sublanguage
   Having one supported language with a well-defined syntax.
6. Updating Views
   All views should be updated through the system.
7. Set-level Insertion, Update, and Deletion
   System must support set-time insert, update, and deletion operations.
8. Data Independence (Physical)
   Alterations to the way data is stored must not alter the application itself.
9. Data Independence (Logical)
   Altering tables, columns, and/or rows must not alter the application itself.
10. Integrity Independence
    The language of the database must define integrity rules.
11. Distribution Independence
    Distributing the database to numerous locations should be anonymous and existing applications should be unaffected.
12. Nonsubversion
    If the system uses a low level interface to record data, then there must be a higher level interface used when administrating.

The largest of corporations follow these rules of cataloging information to this very day.

Four Principles of Database Design

SQL - Four Principles of Database Design

When designing and implementing a database, keep in mind these four guidelines.

Atomicity:

Your coded statements flow without the constant need to update or "fix" your data.

Consistency:

Your statements are either executed 100% or fail 100%, do not implement code that partially works.

Isolation:

Keep data files and logs away from public eyes, and limit the number of users with administration access to your database.

Durability:

Maintain reliable servers with plenty of storage space and back-up systems that save transactions immediately.