A new database is born

I'm about to do some database testing using Oracle 10 on Windows 7. First step I do is creating a new user. Then I create a first table.

To create a user I refer to the Oracle home page page available through web browser. In the Administration section, Database Users, I select the Create User option.

I specify a lousy username (test) and an even lousier password (password) count on the fact that I'm going to put in a bunch of worthless data on a local database. For the same reason I'm not be picky in assigning priviledges to this use, I give it all of them. Not a good idea, usually.

In any case, I click on the Create button, and that's done. I have my test user ready to act.

To work on it, I use Oracle SQL Developer, I create a new Oracle connection, specifying user and password as just created.

When I open the newly created connection, a new working space window is opened. In there I could now create a table for the test user:

CREATE TABLE doughnut (
  name VARCHAR2(10),
  price DEC(5,2),
  stock INT,
  created DATE
);

In this way we are saying that DOUGHNUT is the name of a new table containing four fields:
NAME a string that can't be longer that 10;
PRICE is a floating point number, maximum five digits, two of them are are reserved for fractional side, so we can use not more that three digits for the integer side;
STOCK is a integer;
CREATE is a date.

To see what we actually have created we can ask to Oracle to describe the table:

desc doughnut;

If we are not happy anymore with a table, we can remove it for good using the drop command:

drop table doughnut;

We can insert an item in our table using the insert command:

INSERT INTO doughnut (NAME, PRICE, STOCK, CREATED)
  VALUES ('Blueberry', 2.99, 33, to_date('12-01-2007','dd-mm-yyyy'));

We ask Oracle to insert into our table a new row. We specify the fields we want to set and then the values. Notice that the string is delimited by single quotes, and that we specify explicitely the date format, to avoid ambiguity.

Head First SQL is about SQL - as one could expect - but seen from the point of view of a MySQL user. I'm actually reading it just for fun (I know, I'm a bit on the nerdy side in these days) but I'm working with Oracle 10, and not with MySQL. That means I twist a bit the examples showed in the book to adapt them to the environment I'm using.