SQL The Basics

Strange title isn’t. Is there such a thing as basic SQL(Structured Query Language). Lets try and find out. I will be covering some basics, thus creating a simple table. Making some basic queries and basics on T-SQL (Transact SQL).

Why use databases

Best to begin with a little story I suppose. Why would you want to use a database? Well it’s pretty easy. Databases give you the capability to store massive amounts of data in a orderly manner. With easy and structured access. I can hear those lovers of files shouting that you can do the same with files. I’ll grant them this, files can store information in the same way. But when it comes to quick access or easy manipulation then nothing beats databases.

Now I won’t bore you with the technical ways databases work. Meaning the locking mechanisms, reverting failures, tracking order and logging actions. (I could if I really wanted to, I mean we’ve all been taught how this works) Instead I will start out with the basics in table creation and querying.

Creating a table

The first thing you have to understand is the nature for SQL. Back when databases started there were many different companies creating them (still are), so there is a need for a standard. Having said this SQL is anything but standard. Every database supports different things, which makes SQL and database management in general very complicated. In this post I’ll be focusing on the MySQL variant.

We are going to create a table that will hold the information on personnel working for a small company. Now this means we need people’s home address information as well as the role they play and who is their boss.

Lets assume we want to know the following:

  • Personnel info,
    • Home address
    • Telephone number
    • Birthday
    • Social security number
  • Employee info,
    • Employee number
    • Employee
    • Boss

Did you already notice me breaking them up in two separate groups, these groups will also be my two tables. Most times this grouping happens naturally. You’ve also got the fields for the table. So here are the two creation SQL queries.

 1CREATE TABLE t_Personnel (
 2	p_ID INT(4),
 3	p_Name VARCHAR(50) NOT NULL,
 4	P_Address VARCHAR(150) NOT NULL,
 5	p_Birth DATE,
 6	p_SSN INT(10),
 7	CONSTRAINT PRIMARY KEY(p_ID)
 8);
 9
10CREATE TABLE t_Employee (
11	p_ID INT(4),
12	p_Employee INT(4),
13	p_Boss INT(4),
14	CONSTRAINT PRIMARY KEY(p_ID),
15	CONSTRAINT FOREIGN KEY(p_Employee) REFERENCES t_Personel(p_ID) ON DELETE CASCADE,
16	CONSTRAINT FOREIGN KEY(p_Boss) REFERENCES t_Employee(p_ID) ON DELETE SET NULL
17);

Time to explain the queries a bit. Most of it is pretty basic. You create a table with a view fields and tell what datatype they should have. This also means you need to indicate the size of the field, this is the part between the brackets. Something new is the creating of the PRIMARY KEY, which tells the database this field should be unique and indexed. Another constraint we add is a foreign key. This tells the database that the information in the field also has to exist in the referenced tables field.

Querying the database

Now that you have the database all set up (pretend it is already filled with useful information) you’re ready to start asking it for information. Lets presume that you want information on the boss of ‘F. MacBrull’. Looking at the tables you’ll have to first get the employee information of the guy, which means looking him up in the t_Personnel. So lets do this part already.

1SELECT FROM t_Personnel p_ID WHERE p_Name = "F. MacBrull";

Now you have to find his employee information using the fetched information like this:

1SELECT b.p_Boss FROM t_Employee AS b WHERE p_Employee = (SELECT a.p_ID FROM t_Personnel AS a WHERE p_Name = "F. MacBrull");

Found that one a bit tougher already. We’re just beginning! As promised you’ve just selected the number of the boss that belongs to the already found information on ‘F. MacBrull’. So lets try and get his name, after all this is what we are really after.

1SELECT c.p_Name FROM t_Personnel WHERE c.p_ID = (SELECT b.p_Boss FROM t_Employee AS b WHERE p_Employee = (SELECT a.p_ID FROM t_Personel AS a WHERE p_Name = "F. MacBrull"));

Finally, we’ve got his bosses name. Some of you might ask: “Was all this really necessary?” NO! But it wouldn’t have been as much fun. And every time you query a database it takes time, so why not do it all at once.

SQL  databases 

See also