SQL Howto
  Nelson Rocha (CoolMaster), coolmaster@PTlink.net
  v1.0, 12/28/2002

  This document explain SQL, includes the basic and advanced
  usage. Covers all aspects of how to retrieve, insert, change data;
  create, remove, alter tables and how to create/delete databases.
  Also explain other things like joins, clauses...
  ______________________________________________________________________


  INDEX


  1. Intro
  1.1. Comments

  2. Select
  2.1. Distinct
  2.2. Alias
  2.3. Operators
  2.3.1. Comparison
  2.3.2. Arithmetic
  2.3.3. Character
  2.3.4. Logical
  2.3.5. In and between
  2.4. Functions
  2.5. Clauses
  2.5.1. Order by
  2.5.2. Group by
  2.5.3. Having
  2.5.4. Limit
  2.6. Joins

  3. Manipulate data
  3.1. Insert
  3.2. Update
  3.3. Delete

  4. Manipulate tables
  4.1. Create
  4.2. Alter
  4.3. Drop

  5. Manipulate databases
  5.1. Create
  5.2. Drop

  6. Conclusion

  ______________________________________________________________________


  1. Intro

  This howto pretend to introduce you to Structured Query Language (SQL).
  SQL is a language used on most relational databases, like MySQL, Oracle, PostegreSQL, Microsoft SQL Server,
  Microsoft Access and so on. This language belong to fourth programming generation, with it you can
  retrieve, insert, delete, data from databases, create tables, databases and so on. It's a non procedural language.

  The examples in this howto will be referred to MySQL but since i will use standard features it can be used on other
  DBMS (Data Base Manager System).
  mysql> <-- is the MySQL prompt. Commands in MySQL end with a semi-colon (;).

  I will start explain the SELECT statement since some features of it can be used on other operations, don't worry
  if you don't have any database and table created or even data, at the final of this howto you will be able to
  do many SQL operations.

  SQL is not case sensitive, so is case insensitive. This means that the keyword SELECT can be written like Select, select, 
  SeLect and so on. Although tables data are case sensitive, if you look for name 'CoolMaster' and you write 'coolmaster'
  you could not retrieve what you want.


  1.1. Comments

  Sorry for my english, i know that is not perfect.
  You can find this document in http://www.bubix.net/~coolmaster or http://scripters.ptlink.net.
  If you have any doubt or suggestion you can mail me or just find me on irc.PTlink.net then #PST.


  2. Select

  The Select statement let you to retrieve data from tables, in relational databasis data is stored in tables.
  Example: we have a table named clients with four columns: firstname, lastname, telephone, city. If we want to retrieve
  all data from clients we do:

mysql> SELECT * FROM clients;
+-----------+-----------+-----------+-------------------+
| firstname | lastname  | telephone | city              |
+-----------+-----------+-----------+-------------------+
| Nelson    | Rocha     |    123456 | Porto             |
| Patricia  | Silvestre |    213123 | Almargem do Bispo |
| Jorge     | Sampaio   |    312222 | Lisboa            |
| Nando     | Morkon    |    123221 | Porto             |
+-----------+-----------+-----------+-------------------+
4 rows in set (0.00 sec)

  The select statement is combined with FROM, note that SELECT and FROM are SQL keywords, i will write then in uppercase
  letters but you can write it in other ways.
  The '*' means all columns on table, FROM clients represent the table to look for.

  If we want to retrieve only firstname and lastname we do:

mysql> SELECT lastname, firstname FROM clients;
+-----------+-----------+
| lastname  | firstname |
+-----------+-----------+
| Rocha     | Nelson    |
| Silvestre | Patricia  |
| Sampaio   | Jorge     |
| Morkon    | Nando     |
+-----------+-----------+
4 rows in set (0.00 sec)

  You can see that the order of columns can be changed when retrieving data, note that is on visualization, the table
  structure does not change.
  After SELECT you tell the columns to retrieve separated by commas (,).


  2.1. Distinct

  Let's retrieve the city column from table clients.

mysql> SELECT city FROM clients;
+-------------------+
| city              |
+-------------------+
| Porto             |
| Almargem do Bispo |
| Lisboa            |
| Porto             |
+-------------------+
4 rows in set (0.00 sec)

  You see the data 'Porto' two times, if you want you can prevent this to happen and appear only once, to do it you can
  use the keyword DISTINCT.

mysql> SELECT DISTINCT city FROM clients;
+-------------------+
| city              |
+-------------------+
| Porto             |
| Almargem do Bispo |
| Lisboa            |
+-------------------+
3 rows in set (0.00 sec)


  2.2. Alias

  You can rename at visualisation level the name of each column, real useful when you expand your query.
  To do it you can use an alias:

mysql> SELECT firstname fn, lastname lm, telephone phone, city  FROM clients;
+----------+-----------+--------+-------------------+
| fn       | lm        | phone  | city              |
+----------+-----------+--------+-------------------+
| Nelson   | Rocha     | 123456 | Porto             |
| Patricia | Silvestre | 213123 | Almargem do Bispo |
| Jorge    | Sampaio   | 312222 | Lisboa            |
| Nando    | Morkon    | 123221 | Porto             |
+----------+-----------+--------+-------------------+
4 rows in set (0.00 sec)

  Alias are very simple to use i guess that you can figure it alone :].


  2.3. Operators

  Like other language SQL also support operators, operators can be arithmetic, comparison, logical...
  To use comparison operators you need a new keyword named WHERE.
  At this time you are able to retrieve the any column of a table, but if you want to see the clients
  that live in 'Porto' what do you do? For this little table is easy to figure it out but if you have
  many records guess what?

mysql> SELECT * FROM clients WHERE city = "Porto";
+-----------+----------+-----------+-------+
| firstname | lastname | telephone | city  |
+-----------+----------+-----------+-------+
| Nelson    | Rocha    |    123456 | Porto |
| Nando     | Morkon   |    123221 | Porto |
+-----------+----------+-----------+-------+
2 rows in set (0.00 sec)

  Firstly we do exactly the same, we select all columns from table clients, then we use the keyword WHERE followed
  by the column and an equal sign, we compare each city column to "Porto" if city is equal to "Porto" that
  row will be printed, note that we have Porto in ", this tell that is a character string, you can use ' instead.
  Guess what? The '=' sign is an operator, this is a comparison operator and will compare one value to other.

  Lets see, we now the client phone number but we don't know the name of person and we only want to see
  the firstname and lastname not the city or see number again.

mysql> SELECT firstname, lastname FROM clients WHERE telephone = 123456;
+-----------+----------+
| firstname | lastname |
+-----------+----------+
| Nelson    | Rocha    |
+-----------+----------+
1 row in set (0.00 sec)


  2.3.1. Comparison

  You already saw one comparison operator (=), you can find others like: <, >, <=, >=, !=, <>. The != and <>
  means not equal. So if you want to retrieve all clients that don't live in Porto you can do:

mysql> SELECT * FROM clients WHERE city <> "porto";
+-----------+-----------+-----------+-------------------+
| firstname | lastname  | telephone | city              |
+-----------+-----------+-----------+-------------------+
| Patricia  | Silvestre |    213123 | Almargem do Bispo |
| Jorge     | Sampaio   |    312222 | Lisboa            |
+-----------+-----------+-----------+-------------------+
2 rows in set (0.01 sec)

  The <, >, <= and >= are also easy to use, < stands for less, <= for less or equal, > for greater and >= for
  greater or equal, so if you want to see all telephone numbers that are greater of 200000 you do:

mysql> SELECT * FROM clients WHERE telephone > 200000;
+-----------+-----------+-----------+-------------------+
| firstname | lastname  | telephone | city              |
+-----------+-----------+-----------+-------------------+
| Patricia  | Silvestre |    213123 | Almargem do Bispo |
| Jorge     | Sampaio   |    312222 | Lisboa            |
+-----------+-----------+-----------+-------------------+
2 rows in set (0.00 sec)

  You are now able to use other comparison operators.


  2.3.2. Arithmetic

  You can perform addition, subtraction, multiplication, division and get the remainder. All math precedence are
  preserved so an expression like: 2+5*2-6/2 will result on 9, is the same to: 2+(5*2)-(6/2).

  Here we have another table to demonstrate the arithmetic operators:

mysql> SELECT * FROM prices;
+------------+--------+
| model      | price  |
+------------+--------+
| Porsche    | 146000 |
| Ferrari    | 276000 |
| Fiat Uno   | 6000.1 |
| Fiat Punto |   9000 |
+------------+--------+
4 rows in set (0.00 sec)

  Now we want to increase 2000 euros to each price, we can use the plus(+) operator:

mysql> SELECT model, price+2000  FROM prices;
+------------+-----------------+
| model      | price+2000      |
+------------+-----------------+
| Porsche    |        148000.5 |
| Ferrari    |    278000.09375 |
| Fiat Uno   | 8000.1000976562 |
| Fiat Punto |           11000 |
+------------+-----------------+
4 rows in set (0.00 sec)

  You can see that a new column appear, this is a virtual column, the data on table does not change. You can see also
  that the decimal part is changed, don't worry about it.
  You can perform any operation with two or more columns.

  The minus(-) operator can be used to subtraction and to change the sign of a number, if is a positive number it will
  change to negative and vice-versa.

  We want to decrease 20 euros in all prices what can we do?

mysql> SELECT model, price-20 FROM prices;
+------------+-----------------+
| model      | price-20        |
+------------+-----------------+
| Porsche    |        145980.5 |
| Ferrari    |    275980.09375 |
| Fiat Uno   | 5980.1000976562 |
| Fiat Punto |            8980 |
+------------+-----------------+
4 rows in set (0.00 sec)

  To change the sign we can do:

mysql> SELECT model, -price FROM prices;
+------------+------------------+
| model      | -price           |
+------------+------------------+
| Porsche    |        -146000.5 |
| Ferrari    |    -276000.09375 |
| Fiat Uno   | -6000.1000976562 |
| Fiat Punto |            -9000 |
+------------+------------------+
4 rows in set (0.00 sec)

  The multiplication(*), division(/) and remainder (%) operators work like the others:

mysql> SELECT model, price*2 FROM prices;
+------------+-----------------+
| model      | price*2         |
+------------+-----------------+
| Porsche    |          292001 |
| Ferrari    |     552000.1875 |
| Fiat Uno   | 12000.200195312 |
| Fiat Punto |           18000 |
+------------+-----------------+
4 rows in set (0.00 sec)


  We can combine it:

mysql> SELECT model, price, price*0.19+price "IVA 19%" FROM prices;
+------------+--------+-----------------+
| model      | price  | IVA 19%         |
+------------+--------+-----------------+
| Porsche    | 146000 |      173740.595 |
| Ferrari    | 276000 |  328440.1115625 |
| Fiat Uno   | 6000.1 | 7140.1191162109 |
| Fiat Punto |   9000 |           10710 |
+------------+--------+-----------------+
4 rows in set (0.00 sec)

  Here we can multiply price with 0.19 and then add the previous value of price to the current one,
  here i demonstrate the usage of alias, to use more than one word enclose it with quotes.

  You can also use WHERE, if you want only to change the price of Porsche you can do:

mysql> SELECT model, price*0.19 FROM prices WHERE model = "Porsche";
+---------+------------+
| model   | price*0.19 |
+---------+------------+
| Porsche |  27740.095 |
+---------+------------+
1 row in set (0.00 sec)


  2.3.3. Character

  You can use this operators to manipulate the character data.

  The operator LIKE works for patterns, so if you want to retrieve the names of model started with
  'F' you can do:

mysql> SELECT * FROM prices WHERE model LIKE 'F%';
+------------+--------+
| model      | price  |
+------------+--------+
| Ferrari    | 276000 |
| Fiat Uno   | 6000.1 |
| Fiat Punto |   9000 |
+------------+--------+
3 rows in set (0.01 sec)

  You are now introduced to a new keyword named LIKE, this is an operator, the F% is the pattern,
  means that the first letter need to be 'F' followed by any character, is the same of '*' on some
  languages like Perl or operating systems like UNIX. If you want to retrieve all data that have
  the "at" included you can use the pattern '%at%'.

  The underscore(_) represent only one character instead of zero or more represented by %.


  2.3.4. Logical

  There are three logical operators, AND, OR and NOT.

  Back to clients table:

mysql> SELECT * FROM clients;
+-----------+-----------+-----------+-------------------+
| firstname | lastname  | telephone | city              |
+-----------+-----------+-----------+-------------------+
| Nelson    | Rocha     |    123456 | Porto             |
| Patricia  | Silvestre |    213123 | Almargem do Bispo |
| Jorge     | Sampaio   |    312222 | Lisboa            |
| Nando     | Morkon    |    123221 | Porto             |
+-----------+-----------+-----------+-------------------+
4 rows in set (0.00 sec)

  You want to retrieve clients that the lastname begin with 'S' and lives in Lisboa, we can combine
  this two conditions using the operator AND:

mysql> SELECT * FROM clients WHERE lastname LIKE "S%" AND city = "LISBOA";
+-----------+----------+-----------+--------+
| firstname | lastname | telephone | city   |
+-----------+----------+-----------+--------+
| Jorge     | Sampaio  |    312222 | Lisboa |
+-----------+----------+-----------+--------+
1 row in set (0.00 sec)

  If we want to retrieve clients that lives in Porto or Lisboa we can do:

mysql> SELECT * FROM clients WHERE city = "Porto" OR city = "Lisboa";
+-----------+----------+-----------+--------+
| firstname | lastname | telephone | city   |
+-----------+----------+-----------+--------+
| Nelson    | Rocha    |    123456 | Porto  |
| Jorge     | Sampaio  |    312222 | Lisboa |
| Nando     | Morkon   |    123221 | Porto  |
+-----------+----------+-----------+--------+
3 rows in set (0.00 sec)

 If we want to retrieve data that name does not start with 'S' we can use the NOT operator:

mysql> SELECT * FROM clients WHERE lastname NOT LIKE "S%";
+-----------+----------+-----------+-------+
| firstname | lastname | telephone | city  |
+-----------+----------+-----------+-------+
| Nelson    | Rocha    |    123456 | Porto |
| Nando     | Morkon   |    123221 | Porto |
+-----------+----------+-----------+-------+
2 rows in set (0.01 sec)


  2.3.5. In and between

  This operators make some comparison job easier.

  The IN operator let define a list to match, for example we can retrieve clients from Porto and Lisboa
  using IN:

mysql> SELECT * FROM clients WHERE city IN("Porto", "Lisboa");
+-----------+----------+-----------+--------+
| firstname | lastname | telephone | city   |
+-----------+----------+-----------+--------+
| Nelson    | Rocha    |    123456 | Porto  |
| Jorge     | Sampaio  |    312222 | Lisboa |
| Nando     | Morkon   |    123221 | Porto  |
+-----------+----------+-----------+--------+
3 rows in set (0.00 sec)

  The BETWEEN operator let us define some kind of range, is the same of combine EXPRESSION1 >= EXPRESSION2 AND
  EXPRESSION1 <= EXPRESSION3

  So the following queries are equivalent:

mysql> SELECT * FROM clients WHERE telephone >= 200000 AND telephone <= 300000;
+-----------+-----------+-----------+-------------------+
| firstname | lastname  | telephone | city              |
+-----------+-----------+-----------+-------------------+
| Patricia  | Silvestre |    213123 | Almargem do Bispo |
+-----------+-----------+-----------+-------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM clients WHERE telephone BETWEEN 200000 AND 300000;
+-----------+-----------+-----------+-------------------+
| firstname | lastname  | telephone | city              |
+-----------+-----------+-----------+-------------------+
| Patricia  | Silvestre |    213123 | Almargem do Bispo |
+-----------+-----------+-----------+-------------------+
1 row in set (0.00 sec)


  2.4. Functions

  I will only show some functions, you should search for more on your SQL system documentation.

  The count() function gives the number of rows returned by SELECT statement, for example:

mysql> SELECT count(*) FROM clients WHERE city = "Porto";
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.04 sec)

  The function SUM() returns all sum of values in the given column:

mysql> SELECT sum(telephone) FROM clients;
+----------------+
| sum(telephone) |
+----------------+
|         772022 |
+----------------+
1 row in set (0.00 sec)

  The function AVR() returns the average in the given columns.
  The function MIN() returns the lowest value in the given column.
  The function MAX() returns the greatest value in the given column.
  The function ABS() returns the absolute value of number, for example a negative number will become positive.
  The function CEIL() removes the decimal part and increase the other part one value, and return. For example
   1.2 become 2.
  The function FLOOR() does the reverse of function CEIL(), number 3.14 will become 3.

  SQL has trigonometric functions: COS(), COSH(), SIN(), SINH(), TAN(), and TANH().

  The function CONCAT() concatenates two tables to one. Accept two column arguments.
  The function UPPER() returns the column in uppercase letters.
  The function LOWER() returns the column in lowercase letters.
  The function INITCAP() returns the first letter in uppercase and the rest in lowercase.

  You have other functions, search.


  2.5. Clauses

  You already use a clause before, I'm talking about the WHERE clause. SQL has others to do some important
  jobs like sort and grouping things. You will learn that here.


  2.5.1. Order by

  If you want to sort some numeric values you can use this clause, given the following table:

mysql> SELECT telephone FROM clients;
+-----------+
| telephone |
+-----------+
|    123456 |
|    213123 |
|    312222 |
|    123221 |
+-----------+
4 rows in set (0.06 sec)

  To sort in ascendant order we can do:

mysql> SELECT telephone FROM clients ORDER BY telephone;
+-----------+
| telephone |
+-----------+
|    123221 |
|    123456 |
|    213123 |
|    312222 |
+-----------+
4 rows in set (0.07 sec)

  To sort in descent order we include the DESC keyword:

mysql> SELECT telephone FROM clients ORDER BY telephone DESC;
+-----------+
| telephone |
+-----------+
|    312222 |
|    213123 |
|    123456 |
|    123221 |
+-----------+
4 rows in set (0.00 sec)

  You can also sort character values.


  2.5.2. Group by

  Given the following table:

mysql> SELECT * FROM letsgroup;
+------------+-------+
| name       | grade |
+------------+-------+
| CoolMaster |    20 |
| CoolMaster |    10 |
| CoolMaster |     9 |
| Nando      |     9 |
| Ninfa      |     5 |
| Nando      |    15 |
| Morkon     |    15 |
| Morkon     |    12 |
+------------+-------+
8 rows in set (0.00 sec)

  Lets assume that we want to sum all grades of CoolMaster, Morkon, Ninfa, Nando, we can group the names
  and them make the sum, do this:

mysql> SELECT name, sum(grade) FROM letsgroup GROUP BY name;
+------------+------------+
| name       | sum(grade) |
+------------+------------+
| CoolMaster |         39 |
| Morkon     |         27 |
| Nando      |         24 |
| Ninfa      |          5 |
+------------+------------+
4 rows in set (0.00 sec)

  And if we want to retrieve how much entries each name has?

mysql> SELECT name, sum(grade) sum, count(*) times FROM letsgroup GROUP BY name;
+------------+------+-------+
| name       | sum  | times |
+------------+------+-------+
| CoolMaster |   39 |     3 |
| Morkon     |   27 |     2 |
| Nando      |   24 |     2 |
| Ninfa      |    5 |     1 |
+------------+------+-------+
4 rows in set (0.01 sec)

  As you can see the query is growing, and if we want to get the average for each name? Easy...

mysql> SELECT name, sum(grade) sum, count(*) times, avg(grade) average FROM letsgroup GROUP BY name;
+------------+------+-------+---------+
| name       | sum  | times | average |
+------------+------+-------+---------+
| CoolMaster |   39 |     3 | 13.0000 |
| Morkon     |   27 |     2 | 13.5000 |
| Nando      |   24 |     2 | 12.0000 |
| Ninfa      |    5 |     1 |  5.0000 |
+------------+------+-------+---------+
4 rows in set (0.00 sec)

  Instead of avg(grade) we could use sum(grade)/count(*) but you should always use the functions.


  2.5.3. Having

  You are not able to use WHERE in groups so you need to use another clause like works equal to WHERE,
  the cluase is HAVING.
  Picking letsgroup table again we can only give the average of names that have at least two grades,
  we can do:

mysql> SELECT name, sum(grade)/count(*) average FROM letsgroup GROUP BY name HAVING count(*) >= 2;
+------------+---------+
| name       | average |
+------------+---------+
| CoolMaster |   13.00 |
| Morkon     |   13.50 |
| Nando      |   12.00 |
+------------+---------+
3 rows in set (0.00 sec)

  When you use HAVING just think that you are using WHERE but with another name.


  2.5.4. Limit

  Imagine that you want to limit the data retrieved, for example you want to make lists and show
  each time 10 entries you can use limit, imagine that we want to retrieve of table letsgroup
  the first four entries and then the order four you can do:

mysql> SELECT * FROM letsgroup ORDER BY grade LIMIT 0, 4;
+------------+-------+
| name       | grade |
+------------+-------+
| Ninfa      |     5 |
| CoolMaster |     9 |
| Nando      |     9 |
| CoolMaster |    10 |
+------------+-------+
4 rows in set (0.00 sec)

  We order it first, but that is not necessary. Now lets retrieve the next four:

mysql> SELECT * FROM letsgroup ORDER BY grade LIMIT 4, 8;
+------------+-------+
| name       | grade |
+------------+-------+
| Morkon     |    12 |
| Nando      |    15 |
| Morkon     |    15 |
| CoolMaster |    20 |
+------------+-------+
4 rows in set (0.00 sec)

  With this you can make a nice page system in seconds.


  2.6. Joins

  With this you can join tables without creating new ones, lets make two tables:

mysql> SELECT * FROM table1; SELECT * FROM table2;
+------+--------+
| name | number |
+------+--------+
| Ola  |      1 |
| Ola  |      2 |
| Ola  |      3 |
| Ola  |      4 |
+------+--------+
4 rows in set (0.00 sec)

+------+--------+
| name | number |
+------+--------+
| Hi   |      1 |
| Hi   |      2 |
| Hi   |      3 |
| Hi   |      4 |
+------+--------+
4 rows in set (0.00 sec)

  Now lets join them:

mysql> SELECT * FROM table1, table2;
+------+--------+------+--------+
| name | number | name | number |
+------+--------+------+--------+
| Ola  |      1 | Hi   |      1 |
| Ola  |      2 | Hi   |      1 |
| Ola  |      3 | Hi   |      1 |
| Ola  |      4 | Hi   |      1 |
| Ola  |      1 | Hi   |      2 |
| Ola  |      2 | Hi   |      2 |
| Ola  |      3 | Hi   |      2 |
| Ola  |      4 | Hi   |      2 |
| Ola  |      1 | Hi   |      3 |
| Ola  |      2 | Hi   |      3 |
| Ola  |      3 | Hi   |      3 |
| Ola  |      4 | Hi   |      3 |
| Ola  |      1 | Hi   |      4 |
| Ola  |      2 | Hi   |      4 |
| Ola  |      3 | Hi   |      4 |
| Ola  |      4 | Hi   |      4 |
+------+--------+------+--------+
16 rows in set (0.00 sec)

  Be careful and analise each entrie, look only to number columns.

mysql> SELECT t1.name, t2.name FROM table1 t1, table2 t2;
+------+------+
| name | name |
+------+------+
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
| Ola  | Hi   |
+------+------+
16 rows in set (0.00 sec)

  As we can see we get a part of each table, t1.name means that we are looking at column name
  in t1 table, if you see FROM t1 is table1.

mysql> SELECT t1.name, t1.number, t2.name, t2.number FROM table1 t1, table2 t2 WHERE t1.number = t2.number;
+------+--------+------+--------+
| name | number | name | number |
+------+--------+------+--------+
| Ola  |      1 | Hi   |      1 |
| Ola  |      2 | Hi   |      2 |
| Ola  |      3 | Hi   |      3 |
| Ola  |      4 | Hi   |      4 |
+------+--------+------+--------+
4 rows in set (0.01 sec)

  This retrieve data when number in table1 is equal to name in table2.

mysql> SELECT t1.name, t1.number, t2.name, t2.number FROM table1 t1, table2 t2 WHERE t1.number = t2.number OR t1.number > 2;
+------+--------+------+--------+
| name | number | name | number |
+------+--------+------+--------+
| Ola  |      1 | Hi   |      1 |
| Ola  |      3 | Hi   |      1 |
| Ola  |      4 | Hi   |      1 |
| Ola  |      2 | Hi   |      2 |
| Ola  |      3 | Hi   |      2 |
| Ola  |      4 | Hi   |      2 |
| Ola  |      3 | Hi   |      3 |
| Ola  |      4 | Hi   |      3 |
| Ola  |      3 | Hi   |      4 |
| Ola  |      4 | Hi   |      4 |
+------+--------+------+--------+
10 rows in set (0.00 sec)

  You can do whatever you want...
  This is just a little introduction to joining, this is a powerful feature of SQL.


  3. Manipulate data

  Until this time you only know how to retrieve data from tables, it's time to learn how to manipulate it.
  Later you will learn how to create tables and databases.


  3.1. Insert

  To insert we use the INSERT INTO statement. See the next table:

mysql> SELECT * FROM mobiles;
+----------+-------+-------+
| marca    | model | price |
+----------+-------+-------+
| Nokia    | 5510  |  35.2 |
| Nokia    | 3310  |  30.1 |
| Nokia    | 3330  |  32.9 |
| Ericsson | t28s  | 30.15 |
| Motorola | C333  | 40.15 |
+----------+-------+-------+
5 rows in set (0.00 sec)

  Now we want to insert the mobile Sharp, model GX10 with price 70.25.
  Note that marca and model are data type char so you can only insert characters to it, just enclose it with ",
  price is a float, can contain decimal numbers.

mysql> INSERT INTO mobiles (marca, model, price) VALUES ("Sharp", "GX10", 70.25);
Query OK, 1 row affected (0.00 sec)

  Lets check:

mysql> SELECT * FROM mobiles;
+----------+-------+-------+
| marca    | model | price |
+----------+-------+-------+
| Nokia    | 5510  |  35.2 |
| Nokia    | 3310  |  30.1 |
| Nokia    | 3330  |  32.9 |
| Ericsson | t28s  | 30.15 |
| Motorola | C333  | 40.15 |
| Sharp    | GX10  | 70.25 |
+----------+-------+-------+
6 rows in set (0.00 sec)

  Look at INSERT INTO statement, is followed by the table name, (name, model, price) is the list of columns
  followed by VALUES then the list to insert, note that the column list need to match to VALUES list,
  if you want you don't need to use column list but values will need to match exactly the same number of columns.
  For example:

mysql> INSERT INTO mobiles VALUES ("Ericsson", "T300", 60.25);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mobiles;
+----------+-------+-------+
| marca    | model | price |
+----------+-------+-------+
| Nokia    | 5510  |  35.2 |
| Nokia    | 3310  |  30.1 |
| Nokia    | 3330  |  32.9 |
| Ericsson | t28s  | 30.15 |
| Motorola | C333  | 40.15 |
| Sharp    | GX10  | 70.25 |
| Ericsson | T300  | 60.25 |
+----------+-------+-------+
7 rows in set (0.00 sec)

  Lets imagine that we only want to add marca and model but we don't know yet the price, we can do:

mysql> INSERT INTO mobiles (marca, model) VALUES ("Samsung", "S100");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mobiles;
+----------+-------+-------+
| marca    | model | price |
+----------+-------+-------+
| Nokia    | 5510  |  35.2 |
| Nokia    | 3310  |  30.1 |
| Nokia    | 3330  |  32.9 |
| Ericsson | t28s  | 30.15 |
| Motorola | C333  | 40.15 |
| Sharp    | GX10  | 70.25 |
| Ericsson | T300  | 60.25 |
| Samsung  | S100  |  NULL |
+----------+-------+-------+
8 rows in set (0.00 sec)

  The price table will have NULL data in row 8, this means that does not have any data. You can prevent this
  to happen using NOT NULL when creating the table.


  3.2. Update

  The update statement let us to change some data, the usage is UPDATE table SET conditions.
  For example lets update the Nokia 3310 price, do:

mysql> UPDATE mobiles SET price = 29.2 WHERE marca = "Nokia" AND model = "3310";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM mobiles;
+----------+-------+-------+
| marca    | model | price |
+----------+-------+-------+
| Nokia    | 5510  |  35.2 |
| Nokia    | 3310  |  29.2 |
| Nokia    | 3330  |  32.9 |
| Ericsson | t28s  | 30.15 |
| Motorola | C333  | 40.15 |
| Sharp    | GX10  | 70.25 |
| Ericsson | T300  | 60.25 |
| Samsung  | S100  |  NULL |
+----------+-------+-------+
8 rows in set (0.00 sec)

  If we don't put the WHERE clause all prices will be changed to that value.
  You can set more than one column just separate them with.. guess what? a comma(,).


  3.3. Delete

  To delete a entrie just use DELETE statement, the usage is DELETE FROM table WHERE conditions.
  Lets delete all rows that have NULL data on price, we can use the IS NULL:

mysql> DELETE FROM mobiles WHERE price IS NULL;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mobiles;
+----------+-------+-------+
| marca    | model | price |
+----------+-------+-------+
| Nokia    | 5510  |  35.2 |
| Nokia    | 3310  |  29.2 |
| Nokia    | 3330  |  32.9 |
| Ericsson | t28s  | 30.15 |
| Motorola | C333  | 40.15 |
| Sharp    | GX10  | 70.25 |
| Ericsson | T300  | 60.25 |
+----------+-------+-------+
7 rows in set (0.00 sec)

  This statement is very easy to use. You should not have problems with it, just keep in mind that
  after you delete no data recover is possible.


  4. Manipulate tables

  To create, change and drop (delete) some table you need to manipulate them.


  4.1. Create

  To create a table use use CREATE TABLE statement, just like this:

mysql> CREATE TABLE cds (name char(20), id int NOT NULL, price float);
Query OK, 0 rows affected (0.00 sec)

  After CREATE TABLE you put the table name and then the list of columns, note that name of column is
  followed by type, the name column as type char (character) and can contain a string with 20 characters len.
  The column id is type int and is NOT NULL, this means that id can't contain a NULL value, price is a float
  type.
  You should read your documentation about the types that your SQL implementation has.


  4.2. Alter

  With this statement you are able to alter any attribute of columns in table or add any column to it.
  To change just do this:

mysql> ALTER TABLE cds MODIFY name char(50);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

  Now name column can support string with 50 characters len.
  To create a new column use ADD instead of MODIFY, for example:

mysql> ALTER TABLE cds ADD comment text;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

  The new column name is comment type text.

  You can't delete a column, the only way is to copy a table to another.


  4.3. Drop

  To delete a table just use DROP TABLE name;
  For example:

mysql> DROP TABLE cds;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM cds;
ERROR 1146: Table 'coolmaster.cds' doesn't exist

  This error message tell that table cds in database coolmaster does not exist.


  5. Manipulate databases

  To create or delete a database you need to be able to create or drop it, maybe only system administrators
  can do it.


  5.1. Create

  To create a database do:

mysql> CREATE DATABASE newdb;
Query OK, 1 row affected (0.02 sec)


  5.2. Drop

  To delete a database do:

mysql> DROP DATABASE newdb;
Query OK, 0 rows affected (0.00 sec)


  6. Conclusion

  Well, this was a long journey but now you should be able to do many things with SQL.
  You should learn how to normalize tables, about primary keys and other database stuff.
  If you want you can learn more about joining, learn subquerys, unions, intersections,
  temporary tables, copy tables to another and so on.
  
  *Good* luck and have phun...
  
  CoolMaster, 2002 productions, copyleft to GNU documentation license.