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: 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: 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.