How to populate and query database Problem - Plan - Procedure: How to populate and query database

Tuesday, July 14, 2009

How to populate and query database

Once the database is running, the next step is to populate it. So the task here is to populate all tables, so we can query them and get some meaningful output. So for this exercise we'll need a MySQL Command Line Client.
So the first table we are going to populate is OperatingSystem table, where we insert some of them static values.
Say, we'll have 3 systems there: Windows, MacOS X and Linux.
Run the following queries:
> INSERT INTO OperatingSystem VALUES(NULL,'Windows');
> INSERT INTO OperatingSystem VALUES(NULL,'MacOS X');
> INSERT INTO OperatingSystem VALUES(NULL,'Linux');

Notice, how we have NULL for ID, this is because the field is auto-incremented. If you query the table, you should get this output:
> SELECT * FROM OperatingSystem;

OperatingSystemIDOperatingSystemName
1Windows
2MacOS X
3Linux

Now, once we've done that, let us add some of the services. For this example I'll add a RAM query for all three systems, the commands are currently not filtered, we'll add proper ones later.
so let's insert the following:
> INSERT INTO Service VALUES(NULL, 'RAM','1','mem/P');
> INSERT INTO Service VALUES(NULL, 'RAM','2','alloc');
> INSERT INTO Service VALUES(NULL','RAM','3','cat /proc/meminfo');

so the query from service table should give us the following result:
> SELECT * from Service;
ServiceIDServiceNameOperatingSystemIDCommand
1RAM1mem/p
2RAM2alloc
3RAM3cat /proc/meminfo


Now let's add few computers into computer table.

> INSERT INTO Computer Values(NULL,'Linux Server','192.168.0.10','3');
> INSERT INTO Computer Values(NULL,'Windows Worstation','192.168.0.101','1');
> INSERT INTO Computer Values(NULL,'iMac','192.168.0.102','2');

So that should take care of the initial populating of our database.

0 comments:

Post a Comment