Introduction to SQL

Before beginning this tutorial, it is assumed that:

  • You have installed a Relational Database Management System (RDBMS)
  • This system uses the Structured Query Language
  • You have configured the RDBMS
  • You have read the documentation and know how to access the RDBMS
  • You know how to execute a query statement within the RDBMS

Part I – What is SQL?

The goal of these tutorials is to teach you everything you need to know about SQL. We will start off easy, the first few tutorials will be extremely simple, and somewhat short. As we progress into the more detailed features of SQL, they will grow in both length and knowledge. I used Microsoft SQL Server for this tutorial. The queries presented within this tutorial, however, should work on any ANSI SQL-92 RDBMS. I hope you enjoy.

Abridged Background of SQL

The acronym, SQL, stands for Structured Query Language. It is commonly pronounced as ‘Sequel’, though this is not the proper way to pronounce it. SQL is simply pronounced, ‘S Q L’. You say each letter individually. It is an acronym, not an abbreviation; there by it has no spoken form. Now that you know how to pronounce it, let’s discuss what it is.

SQL is a Relational Calculus developed by Codd in the early 70’s. It is commonly used in Relational Database Management Systems (known throughout the rest of this tutorial as RDBMS). It is, without a doubt, the easiest ‘language’ you will ever learn. Actually knowing the language, and being able to use it, however, are two separate issues. There are very few keywords, phrases or ‘methods’ within SQL. The power behind your queries is not in the language, but the developer using the language. You will learn, throughout this tutorial and in general practice, that no matter how much you already know about relational data … their is always more to learn.

Integrating WordPress into dynamic templates

I installed a WordPress blog on my development server the other day and began playing with it. The first real challenge I faced was how to pull my WordPress installation into my Web site’s template.

My issue is, I’m using a content management system (CMS) to manage the bulk of my Web site’s content. However, I wanted to use WordPress to manage my various blogs. I obviously wanted my blogs to look like the rest of my Web site, so I needed to come up with a plan to integrate my WordPress installation into my CMS, somehow.

Basically, what it came down to was that I needed to find a way to store all of my WordPress output into PHP variables. Once I had done that, I could plug those variables into my template. The main problem I ran across, however, was the fact that 99% of the functions WordPress uses to build its output utilize echo commands rather than simply returning the output.

That was no good for me, obviously, as it started printing content onto my page before the template had been processed.

PHP came to my rescue, and with very little headache. PHP’s output buffer was the simple answer to my problem.

Sorting database results

Databases offer a lot of flexibility when dealing with data. By spending some time upfront on the database design, it will save you plenty of headaches later on. Since the object of this to tutorial is not to elaborate on the advantages of using a database, I will skip to its limits.

The operations you can run on a database are always based on logic and numbers. So for example, when you run a store and sell T-Shirts, and you sort by size, “M” will always be sorted before “S”, even though it is not correct from a humanistic point of view.

Assuming we have a table called “Products” in the database, here is a set of data that you would typically deal with and a solution for sorting the data in a more useful way using the MySQL database.

Name    | Size 

T-Shirt | L 

T-Shirt | M 

T-Shirt | S 

T-Shirt | XL 

T-Shirt | XS

And here is how you typically retrieve records:

SELECT Name, Size FROM Products ORDER BY Size

This query returns all products, however, not sorted “XS, S, M, L, XL” but in “L, M, S, XL, XS”.

The solution to this problem is a simple CASE (see: Control Flow Functions):

SELECT Name, Size, 

CASE Size 

WHEN XS THEN 1 

WHEN S THEN 2 

WHEN M THEN 3 

WHEN L THEN 4 

WHEN XL THEN 5 

END 

AS Sort_Order 

FROM Products 

ORDER BY Sort_Order ASC

This returns the products in order of their sizes. Use ASC (ascending) or DESC (descening) to change the order from smallest size to largest size or vice-versa.

That’s it, enjoy.

Rewrite and Redirect for IIS – Sort of

Last week at work, we unveiled one of the new Web sites I’ve been working on for a while.  In doing so, we removed the old Web site completely from the server and replaced it with the new site.

Of course, when we did so, a lot of our old links became obsolete, so I had to find an effective solution to keep our regular visitors from going bananas when they tried to access some of their favorite pages.  That’s when I began to look into some options for redirecting without having to create placeholder pages just for those redirects.  After quite a few fruitless searches, I was able to piece together a fairly good solution, and also discovered an easy way to achieve URL rewriting on an IIS server (similar to the way you would with htaccess on an apache server).

First, you need to set up your “404” so that it points to a VBScript page.  That’s the most important part.  Once you’ve got that done, navigate to a page that you know does not exist on your server, just so you can test the behavior of your 404 page.

VRML

What is VRML?

VRML stands for Virtual Reality Modeling Language. It was designed to create a more “friendly” environment for the World Wide Web. VRML incorporates 3-D shapes, colors, textures, and sounds to produce a “virtual world” that a user could walk and fly through. VRML is an interpreted language. That is, commands written in text are interpreted by the browser and displayed on the user’s monitor. Many of these worlds can be found on the web today. The current specification, VRML 97, supports JAVA, sound, animation, and Javascript. It allows the world to be dynamic; always changing.

HTML and VRML

HTML and VRML are similar, and very different. Both languages use a text editor as standard equipment. You don’t need anything other than a text editor for HTML, as is the same with VRML. However, the similarities stop there. By creating the 3rd dimension, a lot more creativity, as well as frustration arises. VRML requires that “objects” be placed in an X, Y, and Z space. HTML only uses the X and Y plane. Also, HTML can be viewed in only one way, which the page designer specifies. VRML can be viewed from all angles, making a world be much more enjoyable to experience. VRML files are inherently much bigger than HTML files. Due to this, you should use some methods to reduce your file size.

VRML History

VRML was an idea that was expressed in the First International Conference of the World Wide Web. Mark Pesce, Tony Parisi, Tim Berners-Lee, and David Ragget discussed VRML at this conference. VRML would have to be platform independent to be implemented on the internet. Also, the language had to be able to place objects in 3-D space, as well as include attributes such as shape, color, and size. Since VRML would be used in the internet, all platforms should be able to support it: UNIX workstations to humble desktop PC’s. Silicon Graphics introduced the Open Inventor format, and it was widely accepted. Since then, slight changes were made, and VRML 1.0 was introduced in May 1995. A more clarified version, VRML 1.0c, was issued January 1996. Now, VRML 2, called Moving Worlds, is in full swing.
Differences between VRML 1.0 and 2.0

The major differences that VRML 2.0 has is that it is more interactive, more realistic. VRML 1.0 had static worlds, that is, no interaction and the shapes you see in the world are the shapes that will stay, no movement. VRML 2.0 enhances this by adding JAVA and Javascript support, as well as sound and animation. Now instead of just looking at an unexciting house, you can see the windows flutter, the doors open, cars entering and exiting the garage. Also you can hear music playing, and interactivity like opening the door, using an elevator, or flying through space, automatically! VRML 2.0 was such a major leap from 1.0 that a whole new language had to be created. VRML 1 and 2 are not, compatible. There are conversion tools available, but the best thing is to learn VRML 2.0 from scratch, and experience the new dimension of reality it offers.

RGB Color Chart

Check out the world famous color chart for all web safe RGB colors.

990033
R 153
G 000
B 051
FF3366
R 255
G 051
B 102
CC0033
R 204
G 000
B 051
FF0033
R 255
G 000
B 051
FF9999
R 255
G 153
B 153
CC3366
R 204
G 051
B 102
FFCCFF
R 255
G 204
B 255
CC6699
R 204
G 102
B 153
993366
R 153
G 051
B 102
660033
R 102
G 000
B 051
CC3399
R 204
G 051
B 153
FF99CC
R 255
G 153
B 204
FF66CC
R 255
G 102
B 204
FF99FF
R 255
G 153
B 255
FF6699
R 255
G 102
B 153
CC0066
R 204
G 000
B 102
FF0066
R 255
G 000
B 102
FF3399
R 255
G 051
B 153
FF0099
R 255
G 000
B 153
FF33CC
R 255
G 051
B 204
FF00CC
R 255
G 000
B 204
FF66FF
R 255
G 102
B 255
FF33FF
R 255
G 051
B 255
FF00FF
R 255
G 000
B 255
CC0099
R 204
G 000
B 153
990066
R 153
G 000
B 102
CC66CC
R 204
G 102
B 204
CC33CC
R 204
G 051
B 204
CC99FF
R 204
G 153
B 255
CC66FF
R 204
G 102
B 255
CC33FF
R 204
G 051
B 255
993399
R 153
G 051
B 153
CC00CC
R 204
G 000
B 204
CC00FF
R 204
G 000
B 255
9900CC
R 153
G 000
B 204
990099
R 153
G 000
B 153
CC99CC
R 204
G 153
B 204
996699
R 153
G 102
B 153
663366
R 102
G 051
B 102
660099
R 102
G 000
B 153
9933CC
R 153
G 051
B 204
660066
R 102
G 000
B 102
9900FF
R 153
G 000
B 255
9933FF
R 153
G 051
B 102
9966CC
R 153
G 102
B 204
330033
R 051
G 000
B 051
663399
R 102
G 051
B 153
6633CC
R 204
G 051
B 204
6600CC
R 102
G 000
B 204
9966FF
R 153
G 102
B 255
330066
R 051
G 000
B 102
6600FF
R 102
G 000
B 255
6633FF
R 204
G 051
B 255
CCCCFF
R 204
G 204
B 255
9999FF
R 153
G 153
B 255
9999CC
R 153
G 153
B 204
6666CC
R 102
G 102
B 102
6666FF
R 102
G 102
B 255
666699
R 102
G 102
B 153
333366
R 051
G 051
B 102
333399
R 051
G 051
B 153
330099
R 051
G 000
B 153
330033
R 051
G 000
B 051
3300FF
R 051
G 000
B 255
3333FF
R 051
G 051
B 255
3333CC
R 051
G 051
B 204
0066FF
R 000
G 102
B 102
0033FF
R 000
G 051
B 255
3366FF
R 051
G 102
B 255
3366CC
R 051
G 102
B 204
000066
R 000
G 000
B 102
000033
R 000
G 000
B 051
0000FF
R 000
G 000
B 255
000099
R 000
G 000
B 153
0033CC
R 000
G 051
B 204
0000CC
R 000
G 000
B 204
336699
R 051
G 102
B 153
0066CC
R 000
G 102
B 204
99CCFF
R 153
G 204
B 255
6699FF
R 102
G 153
B 255
003366
R 000
G 051
B 102
6699CC
R 102
G 153
B 204
006699
R 000
G 102
B 153
3399CC
R 051
G 153
B 204
0099CC
R 000
G 153
B 204
66CCFF
R 102
G 204
B 255
3399FF
R 051
G 153
B 255
003399
R 000
G 051
B 153
0099FF
R 000
G 153
B 255
33CCFF
R 051
G 204
B 255
00CCFF
R 000
G 204
B 255
99FFFF
R 153
G 255
B 255
66FFFF
R 102
G 255
B 255
33FFFF
R 051
G 255
B 255
00FFFF
R 000
G 255
B 255
00CCCC
R 000
G 204
B 204
009999
R 000
G 153
B 153
669999
R 102
G 153
B 153
99CCCC
R 153
G 204
B 204
CCFFFF
R 204
G 255
B 255
33CCCC
R 051
G 204
B 204
66CCCC
R 102
G 204
B 204
339999
R 051
G 153
B 153
336666
R 051
G 102
B 102
006666
R 000
G 102
B 102
003333
R 000
G 051
B 051
00FFCC
R 000
G 255
B 204
33FFCC
R 051
G 255
B 204
33CC99
R 051
G 204
B 153
00CC99
R 000
G 204
B 153
66FFCC
R 102
G 255
B 204
99FFCC
R 153
G 255
B 204
00FF99
R 000
G 255
B 153
339966
R 051
G 153
B 102
006633
R 000
G 102
B 051
336633
R 051
G 102
B 051
669966
R 102
G 153
B 102
66CC66
R 102
G 204
B 102
99FF99
R 153
G 255
B 153
66FF66
R 102
G 255
B 102
339933
R 051
G 153
B 051
99CC99
R 153
G 204
B 153
66FF99
R 102
G 255
B 153
33FF99
R 051
G 255
B 153
33CC66
R 051
G 204
B 252
00CC66
R 000
G 204
B 252
66CC99
R 102
G 204
B 153
009966
R 000
G 153
B 102
009933
R 000
G 153
B 051
33FF66
R 051
G 255
B 102
00FF66
R 000
G 255
B 102
CCFFCC
R 204
G 255
B 204
CCFF99
R 204
G 255
B 153
99FF66
R 153
G 255
B 102
99FF33
R 153
G 255
B 051
00FF33
R 000
G 255
B 051
33FF33
R 051
G 255
B 051
00CC33
R 000
G 204
B 051
33CC33
R 051
G 254
B 051
66FF33
R 102
G 255
B 051
00FF00
R 000
G 255
B 000
66CC33
R 102
G 204
B 051
006600
R 000
G 102
B 000
003300
R 000
G 051
B 000
009900
R 000
G 153
B 000
33FF00
R 051
G 255
B 000
66FF00
R 102
G 255
B 000
99FF00
R 153
G 255
B 000
66CC00
R 102
G 204
B 000
00CC00
R 000
G 204
B 000
33CC00
R 051
G 204
B 000
339900
R 051
G 153
B 000
99CC66
R 153
G 204
B 102
669933
R 102
G 153
B 051
99CC33
R 153
G 204
B 051
336600
R 051
G 102
B 000
669900
R 102
G 153
B 000
99CC00
R 153
G 204
B 000
CCFF66
R 204
G 255
B 102
CCFF33
R 204
G 255
B 051
CCFF00
R 204
G 255
B 000
999900
R 153
G 153
B 000
CCCC00
R 204
G 204
B 000
CCCC33
R 204
G 204
B 051
333300
R 051
G 051
B 000
666600
R 102
G 102
B 000
999933
R 153
G 153
B 051
CCCC66
R 204
G 204
B 255
666633
R 102
G 102
B 051
999966
R 153
G 153
B 102
CCCC99
R 204
G 204
B 153
FFFFCC
R 255
G 255
B 204
FFFF99
R 255
G 255
B 153
FFFF66
R 255
G 255
B 102
FFFF33
R 255
G 255
B 051
FFFF00
R 255
G 255
B 000
FFCC00
R 255
G 204
B 000
FFCC66
R 255
G 204
B 102
FFCC33
R 255
G 204
B 051
CC9933
R 204
G 153
B 051
996600
R 153
G 102
B 000
CC9900
R 204
G 153
B 000
FF9900
R 255
G 153
B 000
CC6600
R 204
G 102
B 000
993300
R 153
G 051
B 000
CC6633
R 204
G 102
B 051
663300
R 102
G 051
B 000
FF9966
R 255
G 153
B 102
FF6633
R 255
G 102
B 051
FF9933
R 255
G 153
B 051
FF6600
R 255
G 102
B 000
CC3300
R 204
G 051
B 000
996633
R 153
G 102
B 051
330000
R 051
G 000
B 000
663333
R 102
G 051
B 051
996666
R 153
G 102
B 102
CC9999
R 204
G 153
B 153
993333
R 153
G 051
B 051
CC6666
R 204
G 102
B 102
FFCCCC
R 255
G 204
B 204
FF3333
R 255
G 051
B 051
CC3333
R 204
G 051
B 051
FF6666
R 255
G 102
B 102
660000
R 102
G 000
B 000
990000
R 153
G 000
B 000
CC0000
R 204
G 000
B 000
FF0000
R 255
G 000
B 000
FF3300
R 255
G 051
B 000
CC9966
R 204
G 153
B 102
FFCC99
R 255
G 204
B 153
FFFFFF
R 255
G 255
B 255
CCCCCC
R 204
G 204
B 204
999999
R 153
G 153
B 153
666666
R 102
G 102
B 102
333333
R 051
G 051
B 051
Pages: