Learning SQL from Scratch

The SQL language (Structured Query Language) is the dominant language used to access data stored in databases. This was one of the original languages applied to Edgar Codd’s relational model and overtime became the defacto standard. SQL standardization started when it was accepted by the “American National Standards Institute” in 1986 and then again in 1987 by the ISO “International Organization for Standardization”.

The SQL Language has been around long enough to become very mature and this is a good thing as the skills you use from one database to another are transferrable to a high degree. Every rdbms has slight differences and functions that are not covered under the standard but almost everything else will be familiar.

To start this tutorial we will begin at the beginning — with the “select” statement, this is the most used statement and one of the most versatile. All relational databases arrange there information in tables. These tables are a collection of rows and columns much like a grid. Each column carries a named piece of data.

For example let’s say we need a table that carries the names of members if a website. In the table we would like to store their name, username and password.

The construction of the table would be like this:

First_name Last_name Username Password

These columns would be grouped in rows and in turn that would be the general make-up of the table. The next thing that is important is how do we identity the correct rows in this or any table. Normally there would be an identity column that uniquely identifies a row. This can be done with one column a or a group of columns as long as it is unique.

So now what we have is:

MemberId First_name Last_name Username Password

An identity key in the form of an integer will be added to this column for every row that is inserted into the table.

With this we can now identity the row we seek. To select a row in a database there are a number of options, but for now the simplest is to select all columns where the “rowid” in this case MemberId is the one you seek.

select * from Members where MemberId=1024;

1024 being the value that was assigned to that row when it was created.

This will return the member data that we need from the database. You can also ask for specific columns if you only need specific information. That is appropriate when all the columns are not neccessary and would cause unneccessary work for the system.

For example:

Select First_name from Members where MemberId=1024;

This will of course just return the first name for the member.

The next concept I would like to introduce is “joins”. Now let’s say that we have another table that carries address information for the member. How will we also grab that information at the same time?

Below is the example layout for the Address table.

AddId Street_address City State Zip

So in order to obtain the additional information that we now need we will have to “join” the data from the first table with that of the second table.

This will be what is called an “inner join” or “equi-join”. We will have to add one other column to the second table that is common to both tables.

AddId MemberId Street_address City State Zip

By adding the MemberId column to the second table, we create a way to “join” the two tables, this value will be the same as the MemberId of the first table.

Select * from Members inner join Addresses on Members.MemberId=Addresses.MemberId;

Now in truth this will turn us back a table of “all” Members and their addresses. We need to add one more thing: The “where” clause from the initial example.

Select * from Members inner join Addresses on

Members.MemberId=Addresses.MemberId

where MemberId=1024;

This will return 1 row consisting of all the columns of the first and second table allowing us to have the infromation that we need.

Look for part 2 of “Learning SQL From Scratch”.

Utilizing binary operators in storage and access

The use of binary operators in programming is one of the most under taught concepts when one learns to program or learn a new language. It leaves out a tool that gives the ability to filter and manipulate data in a very concise and compact way. I wanted to write a tutorial and this was one of the epiphanies of my early programming career and also something that once I understaood drove my colleagues looney tunes with because I would not stop talking about or attempting to apply to a new situation. Once I understood these concepts I could not believe how many ways I was able to use them, Compressing lists of boolean values, filtering data rows in databases, simplifying permissions and user roles in applications, also understanding code that up till then was Greek, after a point it became apparent that the uses were limited only by my willingness to utilize them. What makes this so powerful? One reason is the ability for a simple two operand expression to filter as many as 60 boolean values! (using a standard 64 bit integer, one column in a database) To understand this concept you will have to understand how integers are stored. If already familiar, this part may be skipped.

The simplest example would be to look at an illustration. All base 10 numbers are stored and used by a computer in their native binary form.

Counting from 0 to 10 in binary would look like: (8 bit representation)

00000000

00000001

00000010

00000011

00000100

00000101

00000110

00000111

00001000

00001001

00001010

or

0,1,10,11,100,101,110,111,1000,1001,1010

But you would use the values of course as the numbers 1 through 10. The reason that this is important is so you can visually see what I are talking about. Each (1) moving from right to left, is a power of 2. By working with integers and are all powers of 2 such as 0,1,2,4,8,16 etc. you can combine and extract those values successfully from the stored integer.

Focus your attention on the fact that basically your looking at a series of boolean values, or string of yes and no’s. This unique fact can be used programmatically to store large amounts of data in a very small compact space. If you have an entity in a program and need to store attributes for that entity, you can store that information as a series of one’s and zero’s within the binary digit and then parse out the information based upon value and position. When you use binary operators with integer values you can extract those values from that representation. The number 4 or binary value 100 combined using the “bitwise or” operator with the number 8 or binary value 1000 create the integer value 12 or 1100. So if we store the value 12 we can extract those two “yes” answers.

Finding what values are stored within the binary number.

Ex. if we check (12 & 8) the result is 8 we would know that the value exists and the answer is therefore true.

If however we check (4 & 8) the result is 0 we now know that the value does not exist and is therefore false. If the value we are checking is returned it exists within the binary number.

Now this seems simplistic but when you have a number that presents a combination of possibly 60 values it starts to make more sense. I think at this point we need a real world example. I built a BtoB website that had of all things customers or members, these members had quite a few small pieces of information that had to be stored in the form of preferences or attributes. Given the value 2147492867 stored in an integer column the values present in this are 1,2,1024,8192 and 2147483648. I then have a table that gives definitions to these values.

1 Member active

2 Purchasing Member

1024 Shipper — Member who utilizes shipping software.

8192 Member is Vendor

2147483648 Member uses local express

(in the following examples the database tables being used all have a column called “Bitcolumn” that is a large or long 64bit integer datatype.) 

These five “yes” answers where provided by the single value 2147492867. This same value also provided the other 55 “no” answers from a long/large/bigint (depending upon the database) integer column in the database. So how besides the obvious does this help us? What if we needed a list of active members who were vendors. We could query:

select * from members where (bitcolumn & 8193) = 8193. (this is the result of the “bitwise or” (8192|1) = 8193 as defined in the list above.)

This would produce that list. Now what if we wanted to obtain a list of active members that were purchasing vendors but not shippers that use local express.

214791843 = (1 | 2 | 8192 | 214782648)

then bring in the value of shipper (1024) to negate that value or filter out those rows.

select * from members where (bitcolumn & 214791843) = 214791843 and (bitcolumn & 1024) = 0

Notice the simplication of the where clause, the more values sought in the query the more simplified in comparison to the normal approach and never would you need more then 2 clauses one for existance and one for non-existance. Another note is that this type of search is very fast for two reasons, the where clause is simplified and you are working with binary values wich allows the database to use much faster algorithms to compute value. The next example will show the versatility of this approach.

What if you wanted to know if a member has at least one of those values.

select * from members where (bitcolumn & 214791843) != 0

What ever value is in the right operand will if present be returned. So by checking for not equal to zero will let you know that at least one of those values exists and turn back the matching list. You can also apply this to table joins.

select * from t1 inner join t2 on (t1.bitcolumn & t2.bitcolumn) = 214791843

or

select * from t1 inner join t2 on (t1.bitcolumn & t2.bitcolumn) != 0

select * from t1 inner join t2 on (t1.bitcolumn & t2.bitcolumn) != 0 and (t1.bitcolumn & 1)  = 1

Note also not all databases may support this particular join clause but the major ones should, it might also end up in the where clause depending on SQL compliance.

Well this concludes part 1, part 2 will discuss the application of binary operators to control permissions and roles in applications. Bye for now.