How Exact Abacus Software Support use SQL and Why

Structured Query Language or (SQL) is a programming language designed for managing data in relational database management systems (RDBMS). The Exact Abacus Software Support team use SQL for accessing and manipulating data in the 3ex.net tables.

This is a very useful tool for being able to look behind the scenes at how the raw data is held in the 3ex tables. It allows us to view the data fields in the tables and look at how all of the tables are linked together.

The scale of access that SQL gives you for manipulating the data is almost limitless. This is far superior and is a much more effective tool than the View and Modify program we had with the old 3ex system.

We use SQL to access the 3ex.net data tables, such as running a quick search for a Sales Order Reference Number or Product Code. SQL allows us to perform simple data fixes, whilst also giving us the scope to perform much more technical tasks such as auditing the entire stock file.

At Exact Abacus we run SQL using Microsoft SQL Server Management Studio 2005 and 2008.
If you are just starting out using SQL, here is an example of a query that allows you to search for a Menu Option/Menu Group:

select

MenuOption.Description as OptionDesc

,MenuGroup.Description as GroupDesc

from MenuOption

inner join MenuGroup onMenuOption.MenuGroupId =MenuGroup.Id

where menuoption.Description like ‘%Sales Orders%’

Unless you are experienced in using SQL we don’t recommend testing out your new Skills on the live 3ex.net sql database! But you can find plenty of resources here:

http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

If you want to learn more about using SQL and 3ex.net please contact the Exact Abacus Support Team.

…and a useful SQL Quick Reference guide

To help you get to grips with SQL here’s is a list of easy to use SQL statements.

 

SQL Statement Syntax

AND / OR SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
AS (alias) SELECT column_name AS column_alias
FROM table_nameorSELECT column_name
FROM table_name AS table_alias
BETWEEN SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
DELETE DELETE FROM table_name
WHERE column_name=valueorDELETE FROM table_name
DELETE * FROM table_name(Note: Deletes the entire table)
GROUP BY SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INNER JOIN SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOIN SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name asc or desc
SELECT SELECT column_name(s)
FROM table_name
SELECT * SELECT *
FROM table_name
SELECT TOP SELECT TOP number|percent column_name(s)
FROM table_name
UPDATE UPDATE table_name
SET column1=value, column2=value,…
WHERE column_name=value
WHERE SELECT column_name(s)
FROM table_name
WHERE column_name operator value

 

by ste on 09/06/2012

Let's talk

We're here to help. Send us a message and we'll get back to you as soon as possible.

Get in touch

Guides and Resources

View Resource Library