Introduction to SQL - ISInc

Information is at the heart of all computer systems.  The storage, retrieval and manipulation of information is a core function of virtually all applications whether those applications reside on the desktop, a server, or the web.  Most modern applications make use of relational databases (RDBs) to deliver these functions and the computer language that is most often used to with RDBs is SQL (often pronounced as the English word “sequel”).  Because of the importance of information management and pervasiveness of RDBs in modern computing a passing familiarity with the SQL language can be a strong benefit to virtually anyone who works with computer technology.

SQL dates back to the early 1970s and because of its age it makes use of a much more English-like syntax than modern languages such as Java or C#.  This English-like syntax makes the language much easier for non-programmers to quickly pick up the basic intent of code statements.  However it’s syntax is considerably less flexible and less efficient to code than those modern languages.  Fortunately a little SQL goes a long way!  The core functions of SQL are it’s ability to retrieve and manipulate data stored in the RDB.  SQL also offers commands to manage data structures, control access to data, and group changes together in units referred to as transactions.

Understanding the basics of the two core functions of SQL, data retrieval and manipulation, is not that difficult.  There are only a small number of basic commands but those commands can result in a myriad of actions in the RDB.  One important consideration, although standards exist for the SQL language virtually all RDB vendors have chosen to implement the language in different manners.  Most of these differences are slight but it is important to note that you may need to tweak the following SQL statements depending on what database you wish to use them with. 

The first area of SQL that most people are exposed to are the statements that allow for data retrieval.  This is often referred to querying the database.  To write a valid query some knowledge of how a RDB stores data is required.  Relational databases group data together in what are referred to as “tables”.  Each table contains a set of related data.  For example a table called Materials might contain data on part numbers, weight, cost, etc.  We refer to each specific piece of data as a “field” within that table.  So cost might be a field within the Materials table that contains the average price paid for each part.  Another table might be called Suppliers and would contain data such a name, supplier id, address, etc.  It is from these tables that our SQL statements must query in order to pull data for an application to process.

At it’s simplest a statement to query parts data from the aforementioned Materials table might look like:

SELECT part FROM materials;

This statement would retrieve all part numbers from the materials table.  As you can see because of the English-like syntax of SQL it is fairly easy to understand what the intent of the statement is.  (The semi-colon at the end is a delimiter that SQL uses to indicate where a statement ends.)  Now depending on the size of the materials table that could be a very large list.   It might be valuable to restrict the data that is retrieved if not all is required.  For example our application might only be interested in parts in excess of 100 lbs. 

Data queries can be limited by modifying the SELECT statement using the keyword WHERE.  For example:

SELECT part, weight FROM materials

WHERE weight > 100.00

ORDER BY weight;

Pretty easy right?  We are asking to return all parts and their weight from the materials table where the weight is in excess of 100 lbs.  Notice the SQL statement is not that different from the last sentence I wrote.  This is why SQL can be a very easy language to jump into.  In addition I added another keyword to the end of the statement, ORDER BY.  I bet you can guess what that does.  By default the data returned from a SELECT is in a random order but by using order by I can guarantee that I get data in an order I specify.  In this case I the query will be sorted by weight so I can see the heaviest parts first.  (Most databases will default to a descending sort order but they will always offer a way to control that usually with a keyword of ASCENDING or DESCENDING following the field name.)

Using the SELECT statement and a few basic modifiers such as WHERE a huge number of possibilities open up for accessing data in an RDB.  Some other functions available are grouping of data, allowing for summation to reduce the data retrieved, and combining data from multiple tables in a single query referred to a “joining” tables (pulling a part data and information on it’s supplier in a single statement for example).  Though more complicated these functions build on the basic SELECT statement and extend the English-like syntax of SQL.

The second area of SQL we will discuss is data manipulation.  In order to query a database it must contain data in the first place!  How you get data into a table and how you can modify it after it has been created are controlled through three basic SQL commands;INSERT, DELETE, and UPDATE.  These three keywords are the foundation for data management in most modern applications.  I am sure you have already guessed what each does.

The INSERT statement allows an application to create data in a table.  This is how our RDB is initially filled and added to over time.  For example to create two parts in our Materials table I would would execute these two statements.

INSERT INTO materials (part, cost, weight) VALUES (‘3XPLD’, 3.50, 17);

INSERT INTO materials (part, cost, weight) VALUES (‘3XPLZ’, 12, 57.5);

 

These statements would add two into the materials table.  The first with the part equal to’3XPLD’, the cost equal to $3.50 and the weight equal to 17 lbs.  But you probably already figured that out thanks to the ease of SQL.

 

DELETE as you would expect will remove data from a table.  UPDATE would let you change specific fields.  For example we could change the price of 3XPLZ from 12 to 14 using the following:

UPDATE materials SET cost = 14 WHERE part = ‘3XPLZ’;

As you can see the UPDATE statement borrows the WHERE modifier from SELECT making it very easy to use once you understand the basics of querying data.  DELETE follows a similar syntax so once you learn the basics of SQL it is very easy to expand you understanding.

Well I hope this has helped you understand the basics of SQL and shown how easy it can be to unlock such a critical piece of modern computing.  There are a host of free RDBs and SQL environments that you can practice with and if you are already coding in other languages most likely there is a way to access databases and leverage SQL code in your current tools.

Name (required)

Mail (will not be published) (required)

Website

Δ