SQL Tips

Guidelines

• Document as you go.
• Leave bread crumbs on the trail.
• Keep it simple.
• Use prefixes or suffixes to make it obvious.
• Use consistent coding style.
• Add comments when it's not obvious.
• Anticipate disasters proactively.
• Testing
• DELETE
• INSERT
• SELECT
• UPDATE
• DELETE

Terms

• DDL - Data Definition Language
• DML - Data Manipulation Language
• PL/SQL - Procedural Language for SQL
• RDBMS - Relational DataBase Management System

Example

CREATE TABLE author (
id          number,
name        varchar2(100),
birth_date  date,
gender      varchar2(30)
);

Using IJ

% ./bin/ij
ij version 10.8
ij> connect 'jdbc:derby:myDB;create=true'; 

CREATE TABLE Products
     (
         ProductCode VARCHAR(10),
         Description VARCHAR(40),
         Price DOUBLE
);
0 rows inserted/updated/deleted
ij>

INSERT INTO Products
     VALUES ('candy', 'chocolate''s flavor', 5.25);
1 row inserted/updated/deleted
ij> disconnect;
ij> exit;

Running Script

% ./bin/ij
ij version 10.8
ij> connect 'jdbc:derby:myDB';
ij> run 'buildMyDB.sql';
ij> disconnect;
exit;

# to run script from command line
% java org.apache.derby.tools.ij myDBCreate.sql

# to start the Derby server
% java org.apache.derby.drda.NetworksServerControl start

# to stop the Derby server
% java org.apache.derby.drda.NetworksServerControl shutdown

Links

• Beginning PL/SQL
• Apache Derby Manual
• Apache Derby FAQ
• Apache Derby Tutorial
• Apache Derby Install Page