Learning SQL: Difference between revisions
From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs) m Created page with "09-Nov-2011 I am taking a class through [http://ed2go.com/oregonstate OSU] right now on SQL. I am taking notes here. I am using PostgreSQL so it will be a different experience ..." |
Brian Wilson (talk | contribs) mNo edit summary |
||
Line 5: | Line 5: | ||
I am using PostgreSQL so it will be a different experience for me than for everyone else. | I am using PostgreSQL so it will be a different experience for me than for everyone else. | ||
Supplementary text http://www.iwanttolearnsql.com/SQLSimplified.htm | |||
== lesson 01 == | == lesson 01 == | ||
Line 62: | Line 64: | ||
* Use DATE instead of DATETIME | * Use DATE instead of DATETIME | ||
Latest revision as of 05:15, 10 November 2011
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.
Supplementary text http://www.iwanttolearnsql.com/SQLSimplified.htm
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