labs/tiddlers/content/labs/lab03/_Labs_03_Developing and Testing Your Code.md at 55b45110efb4c0c429b5e3312346684a1a0ae4e0

When developing your code, use an incremental approach: test early and often! Run each CREATE TABLE statement by itself (using Ctrl+Enter) and verify that there are no syntax errors.

If errors are reported, try to read and understand them. If the error isn’t obvious, check for things like missing or extraneous commas, blank lines, semicolons, parentheses, or misspelled keywords.

Enable line numbers in the gutter/left margin of the editor (right-click), as error messages will often refer to the line number. If you can’t see an error there, look at the end of the previous line. You will often see a red underlining as well.

If you need to make changes to a table, the easiest way is to drop the table, amend your code, and run it again. Use the DROP TABLE statement like so:

drop table Scientist;

You may wish to keep a DROP TABLE statement for each table at the start of your script for easy use. Enclose them all in a block comment so that they normally have no effect but can be run when required.

To check that a table is working properly, try inserting data, e.g.

insert into Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone)
values (123, 'Hankins', 'Stephanie', 'steph@mail.com', '021 123 456');

For testing, use the data from the Excel spreadsheets you already have. When entering dates, convert from the character representation to a true date using the TO_DATE function with a suitable format, e.g.

to_date('31/JUL/2017', 'DD/MON/YYYY')

You can also convert from an internal date to a formatted string using the TO_CHAR function:

to_char(current_date, 'YYYY-MM-DD HH24:MI:SS')

See the documentation of format models for more information: https://www.h2database.com/html/functions.html?highlight=date&search=date#firstFound

To check that the data has gone in, use the SELECT statement to query the table

select * from Scientist;

To insert a default value, either omit the column name and value from your INSERT statement, or use the DEFAULT keyword in place of the value.

To insert a null (missing value), either omit the column name and value from your INSERT, or use the NULL keyword instead of the value.

As an alternative to dropping and re-creating the table, you can use the ALTER TABLE statement. This is a little more complicated to use, but has the advantage of leaving the table and its data in place. This would be an essential tool for a database administrator wanting to modify a live database! That said, modifying your CREATE TABLE statements has the advantage of keeping the entire table definition in one place. As you will see next time, the order of your CREATE TABLE statements will matter when you have foreign keys.

Make sure you have saved your SQL source file, and know where to find it again!

Further Reading -- need updating links...