- For more about relational databases see What is a Relational Database?
Two Classes of SQLSQL falls into two classes
- Data Manipulation Language (DML) - SQL for retrieving and storing data.
- Data Design Language (DDL) - SQL for creating, altering and dropping tables.
Flavors of SQLThe 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 ToolsOften, 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
- SQL Server Enterprise Manager
Comments in SQLUse two dashes to make the rest of the line a comment:
Most SQLs support the C-Style comments as well.
-- Don't mangle the furdwinder!
/* Like this */
Data StorageData 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
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.
IndexesTables 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 statementsThese are
- Select - Fetches data from one or more tables
- Insert - Inserts a row of data into a table
- Update - Changes in a value in one or more rows
- Delete - Deletes one or more rows of data from a table
This is the SQL to fetch an employee record from the payroll table
The * means fetch all columns. You could also fetch just a couple of columns with this query for all employees in taxband 'XYZ'.
select * from Payroll where EmployeeId = 78965
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).
select EmployeeID, AnnualSalary from Payroll where TaxBand = 'XYZ'
Inserting a new rowInsert adds a row. you specify which columns you wish to add and then provide values for them.
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)
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)
Updating RowsUpdating lets you modify one or more columns in one or more rows.
This uses the where clause to extract rows for TaxBand = XYZ' then sets the DateLastPaid, TotalGrossPayYTD and TotalGrossPayM columns for each of those 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'
In practice, payroll is a lot more complicated than this!
Deleting RowsDelete 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.
JoinsThe 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,
- GradeID int
- GradeDescription varchar(20) that has this data.
Then we could do a select like this
0 - Graduate 1 - Programmer 2 - Analyst 3 - Architect 4 - System Designer
Would return David Bolton, Architect
select EmployeeName, GradeDescription from PayRoll,Grade where EmployeeGradeID = GradeId and EmployeeId = 4567
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.