What is SQL?

A Quick Overview

SQL, short for Structured Query Language is pronounced Ess Queue el and is a simple non procedural language that lets you store and retrieve data in a relational database. This is a quick introduction to SQL.

• For more about relational databases see What is a Relational Database?

Two Classes of SQL

SQL falls into two classes

1. Data Manipulation Language (DML) - SQL for retrieving and storing data.
2. Data Design Language (DDL) - SQL for creating, altering and dropping tables.

Most of the time, the SQL you use is for manipulating the data but occasionally you'll need to create new tables, alter existing ones or add an index. One of the best things about SQL is that you can do all of these operations with just simple SQL commands.

Flavors of SQL

The standards in use today are Ansi-89 and Ansi-92 though there have been three more released (1999, 2003 and 2006). It's the flavor supported by the database server you're using that matters. All modern ones support Ansi-92. Each database publisher has their own slightly different version of SQL. If you use proprietary SQL features, your SQL becomes non portable and needs rewriting if you move to a different database server.

Third Party Tools

Often, the tools you use such as IDEs for designing and running SQL provide table design, creation and management. Here are a few I've used.

• EMS MySQl Manager
• SQLYog
• SQL Server Enterprise Manager
• DbArtisan
• MySQLAdmin

Most are standalone applications but the last one is an open source web application.

Comments in SQL

Use two dashes to make the rest of the line a comment:

-- Don't mangle the furdwinder!

Most SQLs support the C-Style comments as well.

/* Like this */

Data Storage

Data is stored in tables made up of individual rows of data. Each row has the same number and type of columns, defined when you created the table. Database data is held in each row, much like fields or members in a struct or class. A typical payroll record might have these columns.

• EmployeeID int
• EmployeeName varchar(30)
• EmployeeGradeID int -- a number indicating some level
• EmployeeDOB datetime
• TotalGrossPayYTD float -- (YTD means Year To Date)
• TotalTaxDeductedYTD float
• TotalGrossPayM float -- (M for Month)
• TotalTaxDeductedM float
• AnnualSalary Float
• TaxBand varchar(8) -- Special string
• DateLastPaid datetime

There would probably be other administrative columns such as date of last payroll run etc.

You use data manipulation SQL to create this with the Create Table command. Another table might have the employees details in the firm- such as the department they work in, total days of vacation allowed (and taken) etc. These aren't relevant to the payroll so wouldn't be in that table but they would probably have the EmployeeID and EmployeeName columns. These are needed for indexing.

Indexes

Tables can have millions of rows but usually only a subset of those rows is needed to work on. This is where the concept of an index comes from. The database designer tells the database server to add an index to a particular column. With 100,000 rows in the Payroll table, fetching the row for employee id 78965 would require a lot of reads to find that particular row without indexes. With an index, it reads the index table, find where that row is held and then fetches it- much faster!

The Four Main SQL statements

These are

1. Select - Fetches data from one or more tables
2. Insert - Inserts a row of data into a table
3. Update - Changes in a value in one or more rows
4. Delete - Deletes one or more rows of data from a table

This is the SQL to fetch an employee record from the payroll table

select * from Payroll where EmployeeId = 78965

The * means fetch all columns. You could also fetch just a couple of columns with this query for all employees in taxband 'XYZ'.

select EmployeeID, AnnualSalary from Payroll where TaxBand = 'XYZ'

If you leave the where clause off then you get all rows. The select statement lets you fetch any combination of columns and rows from one (or more tables).

Inserting a new row

Insert adds a row. you specify which columns you wish to add and then provide values for them.

insert into Payroll ( EmployeeID, EmployeeName, EmployeeGrade, EmployeeDOB, TotalGrossPayYTD, TotalTaxDeductedYTD,
TotalGrossPayM, TotalTaxDeductedM, AnnualSalary, TaxBand, DateLastPaid ) values (4567, 'David Bolton', 3,'1958-09-18',0.0, 0.0, 0.0,
0.0, 80000, 'ABG' , NULL)

There has to be one value for each column listed. If any are excluded then that column has to have a default value or the value Null. (see shortly)

Updating Rows

Updating lets you modify one or more columns in one or more rows.

update Payroll
set DateLastPaid = GetDate(), -- A built in function that returns today's date
TotalGrossPayYTD = TotalGrossPayYTD + ( AnnualSalary/12),
TotalGrossPayM = AnnualSDalary/12
where TaxBand='XYZ'

This uses the where clause to extract rows for TaxBand = XYZ' then sets the DateLastPaid, TotalGrossPayYTD and TotalGrossPayM columns for each of those rows.

In practice, payroll is a lot more complicated than this!

Deleting Rows

Delete uses the where clause to specify which rows you wish to remove. If you leave it off you can delete all rows!

delete Payroll where EmployeeID = 4567 -- I got fired!

What is NULL?

It is a special value means that no data exists. If a database table is a bit like a spreadsheet then a null value is an empty cell. If you do much with SQL you'll come across Null values.

Joins

The power of SQL really comes into its own when you use join. This lets you retrieve data from two or more tables that have related columns. For example say we had a grade table with two columns,

1. GradeID int
2. GradeDescription varchar(20) that has this data.

0 - Graduate
1 - Programmer
2 - Analyst
3 - Architect
4 - System Designer

Then we could do a select like this

select EmployeeName, GradeDescription from
PayRoll,Grade
where EmployeeGradeID = GradeId
and EmployeeId = 4567

Would return David Bolton, Architect

That concludes this article. For lots more about SQL and databases in general, we recommend our sister site's coverage of SQL at databases.about.com.