SQL: Basic Database Programming

This entry is part 1 of 20 in the series SQL Tutorial

SQL is a special programming language designed for managing data.

There is an ANSI standard but codes are always not portable among the different database systems.

For example, the statements that you have written for Sybase might not work if you use it to query an Oracle database.

In SQL, we always say that you only need to know the most common functions which are known as CRUD, create, read, update and delete.

If you are new to database, CRUD are just the functions that you need to learn.

SQL: Database and tables

This entry is part 2 of 20 in the series SQL Tutorial

In a database, the main thing that we need to know is that database can contain many tables.

For learning purposes, the examples that we are going to use will contain only one or two tables.

In a table, there are rows and columns just like an Excel sheet.

Below is a screenshot of using SQL Advantage to access a Sybase database.

sql_advantage

Sybase and Oracle are two big corporations making database related products.  MySQL database is also an Oracle product.

MySQL is open source and it powers many web sites and blogs including this codecrawl.com.

SQL: The first example

This entry is part 3 of 20 in the series SQL Tutorial

Let’s look at our first MySQL example.  This example is a slight modification of the example given by sqlfiddle.com.

CREATE TABLE supportContacts 
	(
 id int auto_increment primary key, 
 type varchar(20), 
 details varchar(30)
 );

INSERT INTO supportContacts
(type, details)
VALUES
('Name', 'Daryl'),
('Phone', '91118888'),
('Country', 'Singapore'),
('Occupation', 'Engineer');

The above code is for building the structure and data for the database.  There are 3 columns – id, type and details in the table supportContacts.

To put the data into the table supportContacts, we use the insert into statement.  Each row of data in the table is called a record.

sql_ex1

On the right of the screenshot, we have our first query statement:

select id, type, details from supportContacts

It means we are selecting all the 3 columns for display.  The displayed table is shown on the bottom of the page.

SQl: Create table with variables

This entry is part 4 of 20 in the series SQL Tutorial

Let us create another more interesting table artiste to illustrate some common query methods in the next few posts.

We use create table command as in the sql code below.

CREATE TABLE artiste 
     (
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10)  
 );

INSERT INTO artiste
(artiste, album, year)
VALUES
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992);

In the table artiste, we define a table with 4 columns – id, artiste, album and year.

Variables in the table

VariableDescription
idpositive integer, auto increment
artistevariable-length string of up to 30 characters
albumvariable-length string of up to 50 characters
yearinteger of up to 10 characters

To display the whole table, we can use a very common query statement:

select * from artiste

The * will select all columns, thus displaying the whole table.

Auto_Increment

We set the column id to auto_increment, with default starting value of 1.

When you insert a NULL (recommended) (or 0, or a missing value), into an auto_increment column, the maximum value of that column plus 1 would be inserted. You can also insert a valid value to an auto_increment column, bypassing the auto-increment.

SQL: CRUD

This entry is part 5 of 20 in the series SQL Tutorial

The acronym CRUD (create, read, update, delete) refers to the major functions that are implemented in relational database.  Once you have mastered these 4 functions, you will be quite proficient in manipulating the database, just like manipulating the DOM in web programming.

FunctionSQL
createinsert
readselect
updateupdate
deletedelete

In the earlier examples, we have already used insert into and select statements.

In the upcoming posts, we will delve further into the select statements.