SQL stands for Structured Query Language and is a special-purpose programming language that can be used to manage data and databases. Specifically, it is designed around relational database management systems - that is, databases that store their information in tables with rows and columns.
SQL is often pronounced "sequel" - although this name refers to a specific database developed by IBM in the 1970s. It was known as "Structured English Query Language". It developed into the language we know today.
A query is a statement - or a line of code - that is passed to the database. It is then interpreted by the database engine and processed. The results are then returned from the database engine for use in further computation, or even simply display on the screen.
There are three broad categories for queries:
Data manipulation
Schema manipulation
Security manipulation
In school-level SQL, we will only concentrate on the data manipulation queries.
Briefly, however, schema manipulation refers to the structure of the database - the tables, and fields - and how to add and change these structures. The function of security manipulation queries might be a little more obvious to you. They refer to how privileges are assigned within the database and allows some databases to restrict who can get access, and what type of access, to the data in the database.
Of the data manipulation queries, there are four we need to look at. These conform to the "CRUD" acronym: create, read, update and delete.
INSERT (create)
SELECT (read)
UPDATE (update)
DELETE (delete)
The most common and important is the SELECT query which is used to read data from a database. In fact we do much more than just retrieve data. SQL allows us to summarise and extract useful information from the data present.
Essentially, there are three types of a SELECT query:
Filtering: very often we are only interested in a subset of the data in our database. We might be interested in extracting information about those who are over 50 or only about those who have passed a test.
Calculating: we might have a birth date in the database and need to calculate an age, or we may have a set of co-ordinates and need to calculate a distance from a certain point.
Summarising: (often called aggregating) this allows us to summarise the data in multiple rows of of a table by using so-called aggregate functions. These include the ability to find averages, sums, maximums, minimums and other useful statistical measures. Examples of this include finding the average age of contestants, the total number of people over the age of 65 or the highest score in a season.
As you will see, we can combine these three variations to make very powerful queries that can answer nearly any question about the data.
--
Here is a sample of some of the functions which can be used in your SQL statements.
SQL aggregate functions return a single value and are calculated from values in a column.
AVG(column) - Returns the average value
COUNT(column) - Returns the number of rows
FIRST(column) - Returns the first value
LAST(column) - Returns the last value
MAX(column) - Returns the largest value
MIN(column) - Returns the smallest value
SUM(column) - Returns the sum
SQL scalar functions return a single value, based on the input value.
FORMAT(value, format) - Formats how a field is to be displayed.
IIF(expression, truepart, falsepart).
LCASE(text) - Converts a field to lower case.
LEN(text) - Returns the length of a text field.
MID(text) - Extract characters from a text field.
MONTH(date) - Returns the month of a given date.
NOW() - Returns the current system date and time.
ROUND(number) - Rounds a numeric field to the number of decimals specified.
TRIM(text) - Removes extra whitespace from text.
WEEKDAYNAME(date) - Returns the name of the weekday of the date.
YEAR(date) - Returns the year of a current date.
See: http://www.techonthenet.com/access/functions/ for a more comprehensive list of functions.