A Beginner’s Guide to Becoming One with SQL

by on April 11th, 2011 5 comments

If you don’t know what SQL is, it stands for Structured Query Language. It is a programming language that allows for the access of manipulation of databases and their various tables. This language follows a strict set of standards created by ANSI (American National Standards Institute). This means that regardless of what version is used on a server, there will be certain aspects that must be uniform throughout every sort of implementation.

In short, SQL can be used for the creation of databases, tables, and procedures in a database. It can also run queries through a database, insert records into a database, update records in that database, and retrieve data from a database. In order to show any sort of data from a database that you are manipulating with SQL, you will need to run an SQL server or another similar RDBMS (relational database management system) database program like Microsoft Access or MySQL. You will then need to use a server side scripting language such as PHP or ASP, and then a regular website programming language such as HTML, which can be used along with CSS.

Actually creating a database is very straightforward in SQL. It is a very simple, single line of code:

CREATE DATABASE dbname;

Once you create a database, you must create tables in order to put data into that database. Here’s a sample line of code that will do just that:

CREATE TABLE name( col1 datatype, col2 datatype, …);

Where the name field is (col1, col2), you simply title the column of the table, such as “FirstName” or “LastName”. The datatype is fairly self explanatory. This is where you set the type of variable that you will want to coincide with the column name. Generally these will either be TEXT or and INTEGER, but there are other types of data that variables can be set to, which are more advanced such as DATE and CURRENSY.

It is equally as easy to get rid of a table with the DROP command. So, if you need to delete a table, you can just do:

DROP TABLE User;

As with any database, SQL gives you the commands to insert as well as modify the data that is in your tables. Here is a template you can follow in order to insert data:

INSERT INTO target [(field1[, field2[, ...]])]

VALUES (value1[, value2[, ...]);

Clearly, this format is very easy to mess up, and typing out the code to do it can become very tedious. This is why many webmasters make use of database front ends such as Microsoft Access or SQLite, which provide the ability to manually enter data into fields nad tables in an interface very similar to that of Microsoft Excel. This method would be great for updating data that takes field from end users, though.

As you can see, SQL is very, very similar to the English language. Even the graphical programs mentioned earlier (Microsoft Access and SQLite) convert what you are doing graphically into SQL commands that can be understood and then interpreted by the database. These two facts make the learning curve of SQL extremely low, if you have not noticed already. For instance, let’s take a look at an SQL query. As all other type of SQL commands presented before, it is very similar and appears to be written almost in English. Check out the code below:

SELECT StockNumber

FROM Prices

WHERE Price > 5

If you read this and guessed that it selected all of the data in the table “StockNumber”  in the Database of “Prices” , when the price of that item is above 5, you are absolutely correct and are catching on quick. If you did not get it right away, do not worry, just try to read the code out loud as if it was an English sentence and do not think to hard on it. You will get the hang of it eventually. While learning the language, you should also keep in mind that it is not case sensitive, meaning the FROM is equivalent to from, or FRom, or frOM, or any other variation you can make. This means you cannot name variables, tables, or databases the same, and rely on capitalization to differentiate between them.

Speaking of English, you can also create phrases by joining queries with fixed text in order to produce results that are immediately readable by the end user with code such as:

SELECT FirstName & " " & Lastname & " works in the " & Dept & " department" AS Job

FROM User;

This would create and print a group of strings that look something like this “Zachary Davis works in the writing department”. This will also give that full phrase a custom title of “JOB”.  Obviously, this will all be coming from the “User” table, and will include lines for all possible users in that table, so, if there are five users, there will be five lines. The ampersands (“&”) are what connects all of the separate parts of the phrase together. The string outputted could not be put together without them, and function the exact same way that plus signs do (“+”) in languages such as Java.

This is just a brief overview of what you can do with SQL. This programming language allows you to do just about anything you want with the data housed in databases, especially a whole lot more advanced functions that were not covered in this article. If you want to learn more about the language, I recommend that you check out w3schools’ SQL section, as they have a bunch of great tutorials for all web based programming languages (not just SQL), and are run by the entity that produces many of the standards that the web’s programming languages run on. You should also stay tuned right here to Tech King, as we will be putting out many other SQL related posts in the future. We will be covering more advanced functions as well as specific uses of SQL that will take the concepts presented in this brief overview, so stay tuned!