Tutorial: Advanced SQL

[Jordan]

Advanced SQL

SQL is perhaps, a very easy language, so here we will try to explore its advanced nature...so that you will see that you can do a lot more with SQL. More than just "select", "order by" and so on...

1. Joining tables:
You can join two or more tables extremely easy with SQL. Assuming the following example:

1.1 Create your tables with phpmyadmin.

Create some database, for example: names
Then, dump the following code below, in order to create the table homecity:

-- phpMyAdmin SQL Dump
-- version 2.11.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 01, 2008 at 06:54 PM
-- Server version: 5.0.45
-- PHP Version: 4.4.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `names`
--

-- --------------------------------------------------------

--
-- Table structure for table `homecity`
--

CREATE TABLE IF NOT EXISTS `homecity` (
`id` bigint(20) NOT NULL auto_increment,
`city` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `homecity`
--

INSERT INTO `homecity` (`id`, `city`) VALUES
(1, 'Los Angeles'),
(2, 'New York');


1.2 Then dump the following code, so that to create another table: name

-- phpMyAdmin SQL Dump
-- version 2.11.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 01, 2008 at 06:55 PM
-- Server version: 5.0.45
-- PHP Version: 4.4.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `names`
--

-- --------------------------------------------------------

--
-- Table structure for table `name`
--

CREATE TABLE IF NOT EXISTS `name` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `name`
--

INSERT INTO `name` (`id`, `name`) VALUES
(1, 'John'),
(2, 'Jane');

Now, let's try to join the two tables: Name + homecity. Why do we do this? Because we want to associate the names of John to his home city and Jane to her home city. We do this, using this SQL query. Open SQL on your phpmyadmin and type and execute this:

SELECT name.name, homecity.city
         FROM name, homecity
        WHERE name.id = homecity.id

You should then see similar to the following result:



Now the tables are joined:



Note that this query does NOT change your tables! It only shows you information about them, but it doesn't change your tables forever or even for a moment. It just doesn't rearrange anything, but it's used to show you in a moment the information you are looking for.

If we want to know who is from Los Angeles? We add the "AND" clause at the end of the previous query, so now it looks like this:

SELECT name.name, homecity.city
         FROM name, homecity
         WHERE name.id = homecity.id
        AND homecity.city = 'Los Angeles'

We do the same thing as described above, but using the JOIN clause. The JOIN clause returns only results if it finds match between the fields. The difference is that in the previous example we relied on the primary keys. Now even if we don't have primary keys we can use JOIN to get the results:

Inner Join:

SELECT name.name, homecity.city
         FROM name
         INNER JOIN homecity
        WHERE name.id = homecity.id

We can use as well LEFT JOIN. Left join shows all rows from the first table even if there is no corresponding match in the second table. For example, we have a name "Michael" with primary key which is 3 in our first table(names). However in the other table(city) we have only two cities with primary keys...1 and 2. Since we don't have 3 rows there, it is obvious that we don't get any results corresponding to Michael. Now let's insert firstly the value of Michael in our "names" database and then demonstrate the LEFT JOIN.

Open SQL on phpmyadmin and insert the value for Michael.

Then open again the SQL window in phpmyadmin and write this code:

SELECT name.name, homecity.city
         FROM name
        LEFT JOIN homecity ON name.id = homecity.id

You will see a similar screen when you execute it:




You can use and the RIGHT join, which is similar although it shows only the rows from the right table which doesn't have a corresponding value in their left value. Insert the values MIAMI and SAN FRANCISCO in the table: city. Then type this code to learn more about the RIGHT JOIN:

        SELECT name.name, homecity.city
         FROM name
        RIGHT JOIN homecity ON name.id = homecity.id

Though now, you must notice something very interesting. In the previous case when you used to insert the value "Michael", It then appeared that the ID of Michael becomes 3 and it hasn't association with the right table. Now you add two values(San Francisco with ID 3 and Miami with ID 4). So, as you can see Michael has value 3 and San Francisco has value of 3 and they are now here connected.

Now if we want to show one and the same fields from two separate tables, we can use UNION. For example we can now add two fields with one and the same name...Lets call those field: phones. Now open your SQL window in phpmyadmin and add the following code into "name".

ALTER TABLE `name` ADD `phones` VARCHAR( 20 ) NOT NULL ;

Now once again type the following code into "homecity":

ALTER TABLE `homecity` ADD `phones` VARCHAR( 20 ) NOT NULL ;

Now, in the homecity table insert a phone number, for example: 415-310-2209
Now, in the name table insert some different phone number, for example: 212-492-3333

And now we show them with the UNION clause:

SELECT phones
         FROM name
         UNION 
         SELECT phones
        FROM homecity;

Keep in mind that the UNION statement shows only values which are different. For example, it doesn't show duplicates. So, if the phone number 415-310-2209 is used and in the name table and in the homecity table, it will be skipped. But, you can even show the duplicated values with the use of UNION ALL.Before that you can add the phone number 415-310-2209 and to the other table where it is not listed(name). Then use the UNION ALL:

SELECT phones
FROM name
UNION ALL
SELECT phones
FROM homecity

Indexes:

Indexes are essential part of any large SQL database, meaning that sites like Wikipedia, Yahoo, Flickr and other SQL driven sites have them, for sure. The idea behind indexes is that it creates track of your records. If you try to use windows search on your desktop, you most probably have seen the "error" that you need to create and index before you can use Windows desktop search. Now we can easily create indexes in SQL. Consider the following example:

CREATE UNIQUE INDEX Index1
        ON NAME(name);

You have now indexed city on the homecity table. If you need to index more fields use that syntax:

CREATE UNIQUE INDEX Index1
        ON HOMECITY(city, phones);

Then you will index and city and phones.

SQL functions:
SQL as well supports mathematical functions. Now, let's try to sum the amount of some numeric values. Assuming that we have a table where we have the names of Bill Gates and Steve Ballmer. We also keep track of their money. Say, for example that Bill Gates has 60,000,000,000 USD and Steve Ballmer has 20000000000 USD. Now, if we were from the IRS we would like to find out how much money have they at all, since we need this for our tracks on Microsoft. We do this using the SUM clause in SQL:

SELECT SUM(column) FROM table;

Note that you can use the next functions as well:

ABS()
Return the absolute value

ACOS()
Return the arc cosine

ASIN()
Return the arc sine

ATAN2(), ATAN()
Return the arc tangent of the two arguments

ATAN()
Return the arc tangent

CEIL()
Return the smallest integer value not less than the argument

CEILING()
Return the smallest integer value not less than the argument

CONV()
Convert numbers between different number bases

COS()
Return the cosine

COT()
Return the cotangent

CRC32()(v4.1.0)
Compute a cyclic redundancy check value

DEGREES()
Convert radians to degrees

DIV(v4.1.0)
Integer division

/
Division operator

EXP()
Raise to the power of

FLOOR()
Return the largest integer value not greater than the argument

LN()
Return the natural logarithm of the argument

LOG10()
Return the base-10 logarithm of the argument

LOG2()
Return the base-2 logarithm of the argument

LOG()
Return the natural logarithm of the first argument

-
Minus operator

MOD()
Return the remainder

%
Modulo operator

OCT()
Return an octal representation of a decimal number

PI()
Return the value of pi

+
Addition operator

POW()
Return the argument raised to the specified power

POWER()
Return the argument raised to the specified power

RADIANS()
Return argument converted to radians

RAND()
Return a random floating-point value

ROUND()
Round the argument

SIGN()
Return the sign of the argument

SIN()
Return the sine of the argument

SQRT()
Return the square root of the argument

TAN()
Return the tangent of the argument

*
Times operator

TRUNCATE()
Truncate to specified number of decimal places

-
Change the sign of the argument

Example of ATAN:

SELECT ATAN(3342);

Conclusion:
Now that we showed you how powerful SQL can be, you need perhaps a better understanding of what hosting needs, what data types and so on do you need in order to handle the best SQL application. We will explain this in detail in the next tutorial called: SQL data types.

[Xav]

You're right, SQL IS quite easy! I've always used Microsoft Jet, though. Just a personal preference.

[Xav]

You're right, SQL IS easy! I've always preferred Microsoft Jet, though. Just a personal preference.