Learning SQL

09-Nov-2011

I am taking a class through OSU right now on SQL. I am taking notes here.

I am using PostgreSQL so it will be a different experience for me than for everyone else.

lesson 01

I am using my home server, bellman.

psql -U postgres
CREATE DATABASE esql;
\c esql

CREATE TABLE Customer
(
CustomerID INTEGER Primary Key NOT NULL,
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL,
HomePhone CHAR(20),
Address CHAR(50),
State CHAR(02),
City CHAR(30),
PostalCode INTEGER
);

This SQL is valid in PostgreSQL but it converts everything to lower case. It automatically creates an index on the field with the primary key.

esql=# \d customer
        Table "public.customer"
   Column   |     Type      | Modifiers 
------------+---------------+-----------
 customerid | integer       | not null
 firstname  | character(50) | not null
 lastname   | character(50) | not null
 homephone  | character(20) | 
 address    | character(50) | 
 state      | character(2)  | 
 city       | character(30) | 
 postalcode | integer       | 
Indexes:
   "customer_pkey" PRIMARY KEY, btree (customerid)

The INSERT statements give worked perfectly.

Somethings I learned:

• There is a type "money".
• To insert a value as type money you have the cast it like this:

INSERT INTO table (price) VALUES (CAST('100.0' AS MONEY));

Almost does not seem worth the effort... :-) It does put in the $ for you in SELECT statements,

It turns out you can simply quote it, like a DATE it figures it out.

INSERT INTO table (price) VALUES '100.0');

• Use DATE instead of DATETIME

Supplementary text http://www.iwanttolearnsql.com/SQLSimplified.htm