Learning SQL
From Wildsong
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