Neon-komputadór

Structured Query Language (SQL)

Structured Query Language (SQL) is the ANSI standard language for relational database management and is used by all major database programs. Working knowledge of SQL will greatly assist the user in any database application, include popular programs such as Oracle, Microsoft SQL Server, Sybase, MySQL, Access DBase and so forth, which usually use proprietary commands to supplement their general reliance on SQL. As mentioned in the section in the previous chapter for MS-Access, a relational database contains one or more tables, which has information stored in cells which are referenced by columns and rows. Columns usually consist of at least the name and datatype for the column. Rows contain the records for the columns.

SQL is the syntax for executing commands to a relational database. Its commands consist of a Data Manipulation Language, which includes SELECT, UPDATE, DELETE, INSERT INTO, a Data Definition Language, which includes CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX

The initial command in SQL is 'SELECT'. This is used to query the database and retrieve the data according to specified criteria, e.g., SELECT "columnname" FROM "tablename". A common option is to is to use the wild card "*" to select all columns. An optional where clause can be added to selections based on conditional relations (e.g., =, >, <. <=, >=, <> or !=, LIKE). The LIKE condition is used for selection of characters, where the use of a "%" sign can be used as a wildcard, for example the SQL command; "SELECT suco FROM lorosae WHERE suco LIKE t%" would select all those suco's starting with the letter 't' (Talitu, Tohu Meta, Tulatekeu, Tekinomata, Tapo etc).

Here's a more advanced SELECT from East Timor's Passport database program. Despite its apparent complexity, most of it should still be understandable:

SELECT [tblTDApplications].ApplicationNo, [tblTDApplications].ApplicationOfficeID, [tblApplicationType].Price, [tblApplicationType].ApplicationTypeID FROM tblApplicationType INNER JOIN tblTDApplications ON [tblApplicationType].ApplicationTypeID=[tblTDApplications].ApplicationTypeID WHERE ((([tblTDApplications].ApplicationNo)=[Forms]![frmPassportProduction].[ApplicationNo]) And (([tblTDApplications].ApplicationOfficeID)=[Forms]![frmPassportProduction].[ApplicationOfficeID]));

The SELECT statement actually has five main clauses, although FROM is only one that is required. The overall format of the SELECT statement is as follows: SELECT [ALL, DISTINCT] column1 [,column2 etc] FROM table1 [, table2] [WHERE <>] [GROUP BY "column list"] [HAVING <>] [ORDER BY "column list" [ASC | DESC]. ALL and DISTINCT are keywords to either select ALL (the default value if not specified) or DISTINCT records in the query results.

Although required by the GROUP BY clause aggregate functions are used to process columns of numeric data and may be used without the GROUP BY clause. The functions include: MIN, which returns the smallest value in a given column, MAX, which returns the largest value, SUM, which returns the sum of values, AVG which returns the average value, COUNT, which returns the total number of values and COUNT(*) which returns the number of rows in a table. An example without the use of the GROUP BY clause would be the statement: SELECT AVG(salary) FROM TblEmployees; which would average the values from the salary column from the table called TblEmployees. The GROUP BY clause collects all the records from the specified functions and processes aggregate functions on one more columns. The HAVING clause allows specification of condition for the records in each group. It follows the GROUP BY clause with the syntax GROUP BY "column list" HAVING condition. For example,

SELECT Name, AVG(salary), Division FROM TblEmployees GROUP BY Division HAVING salary>4000;

would select the name column information and maximum salaries if over 4,000 from the TblEmployees table and organize in Division order. This order is by default in ascending order, but can be arrange in descending order with the ORDER BY clause with commas separators used for multiple columns.

The WHERE operator accepts logical and relational conditions to narrow a SELECT statement. Furthermore, it also uses the 'IN', and 'BETWEEN' keywords (or 'not in', 'not between'). The IN operator is a test operator that tests whether a value is in the column list provided after the keyword, e.g., SELECT lastname, salary FROM TblEmployees WHERE lastname IN ('Alves', 'Gasper', 'Guterres', 'Santos' 'da Silva');. It is in many ways, an abbreviation of multiple OR statements. The same could be achieved, for example, by the statement WHERE lastname='Alves' OR lastname='Gasper' OR lastname='Guterres' OR lastname='Santos' OR lastname='da Silva';. The BETWEEN operator condition sets values with relational values e.g., WHERE age BETWEEN 20 AND 30 - which is the equivalent of WHERE age >= 20 AND age <= 30.

SQL also accepts some standard mathematical operators (* multiplication, / division, % modulo, + addition, - subtraction). There are also a number of functions which, whilst not ANSI standard, are almost included in every relational database system available. These include ABS(x), which returns the absolute value of a variable, POWER(x,y), which returns the value of x raised to the power of y, ROUND(x,y) which rounds the decimal x to the number of places indicated by y, and SQRT(x) which returns the square root value of x.

When multiple tables are selected, then the JOIN statement can be used to link the two tables into a single query result where both table have a common column. For example, following the previous example, suppose TblEmployees has an employeenumber column and another table, TblJobdescriptions also had the same column data called jobnumber . SELECT firstname, lastname, employeenumber FROM TblEmployees INNER JOIN TblJobdescriptions ON employeenumber = jobnumber;

Another common command is the CREATE TABLE statement. This is often used in the course of a series of SQL queries where data is not just manipulated, but stored independently as well from the tables it was derived from. The format of a simple statement for this command is: CREATE TABLE "tablename" ("column1name" "datatype", "column2name", "datatype"); After the datatype declaration optional constraints may be placed on the column creation, for example the maximum number of characters (e.g., suco varchar(20)). The most common datatypes and size values include char(size), a fixed-length character string up to 255 bytes, varchar(size), a variable length character string, number(size), number(size, d) a number value with size expressed in digits, before and after the decimal point, and date (yyyymmdd). As with all other programming languages SQL reserved keywords cannot be used for tables or column names (e.g., SELECT, CREATE, INSERT etc).

Other popular constrains on a table column are "UNIQUE", which specifies that no two records can have the same value in a particular column, "NOT NULL" which ensures that every record in a column must hold a value and "PRIMARY KEY" or "INDEX" which ensures that that each an every record (row) in a table has a unique identifier (e.g., CREATE UNIQUE INDEX index_name ON tablename (columnname). If the UNIQUE condition is not included duplicate index numbers can be inserted into the column.

The INSERT statement is used to insert a row of data into a table. The statement requires the tablename and a list of column names separated by commas, closed in parentheses and followed by the values, also in parentheses, that must match the columns specified. Character strings must be enclosed in single quotes, numbers must not. e.g., INSERT INTO Tblsuco (suconame, subdistrict, sucocode) values ('Abafala', 'Quelicia', 30401); Data can be inserted into specified columns or entire rows.

Records in a table are updated with the UPDATE statement which operates by specifying the tablename, setting the column names and new values and specifying the conditions of the update. For example, UPDATE TblApplicant SET age = age +1 WHERE birthday = 'May 8'; Further records can be deleted by using the DELETE statement in a same format - specify the table name, where the records are according to to column name and what under conditions the deletions are occuring, for example: DELETE FROM TblVolunteer WHERE firstname = 'Lev' and lastname = 'Lafayette'; . To delete an entire record, simply leave the conditions empty (e.g., DELETE FROM TblVolunteer;) This can be achieved in a more deliberate fashion with the DROP TABLE command, which removes the table definition as well as deleting all the records. DROP can also be used for INDEX (DROP INDEX tablename.indexname) and databases (DROP DATABASE databasename).