The primary function of a databases is to support the retrieval and storage of information.
Relational database management systems offer various tools for querying (retrieving) information:
 |
Viewing one, some, or all fields from one or more tables and then Selecting ranges of data for retrieval. |
|
Adding records to a table. |
|
Changing records in a table. |
|
Removing records from a table. |
In addition, there are functions for creating tables and database structure.
Relational Algebra
In order to view data, there needs to be a process for identify "which" or "what" data you wish to retrieve.
Since relational databases are composed of tables and fields, you can specify the values of fields that you wish to retrieve.
Relational algebra is used to correlate fields and the type of information to be retrieved. These correlations include:
|
= the field is equal to the value, e.g. ‘lastname = “Hillman”’ |
|
>, < greater than, less than, e.g., ‘age > 21’ |
|
>=, <= greater than or equal, less than equal, e.g., ‘weight <= 200’ |
|
<> not equal to, e.g., ‘sex <> “Male”’ |
|
% wildcard character, e.g., ‘lastname = “H%”’ retrieves lastname data starting with the letter “H” |
|
like similar to, e.g., ‘lastname like “smith”’ retrieves lastname data such as “Smith”, “Smithy”, “SMITH” |
|
AND, OR, NOT logical operators that combine conditions, e.g., ‘lastname = “Hillman” AND sex = “Male”’ |
Data Functions: Working with Selected Data
Many database management systems also allow you to apply various calculations and or computations to retrieved data including:
|
Sum – add the value of all instances of a retrieved field |
|
Average – find the average value of returned data |
|
Count – how many data points retrieved |
|
Max and Min - get the maximum or minimum values of a field. |
Structured Query Language - SQL
Structured Query Language (SQL) SQL is the ANSI (American National Standards Institute) standard relational database language used for managing objects, data, and security. Many commercial database products use SQL syntax as a means to access and manage databases.
There are 6 basic statements in SQL:
-
Select -- Retrieves columns and rows from a table or tables.
-
Insert -- Adds rows to a table.
-
Update -- Updates columns in existing rows in a table.
-
Delete -- Deletes rows from a table.
-
Create -- Creates a new table.
-
Select Into -- Creates new tables with data from an existing table.
The following sections describe the syntax for each of these statements.
Select
The select statement is used to retrieve data from one or more tables. It can also be used to get information about data such as how many or an average.
Select — columns of data to be retrieved.
From — tables from which to retrieve rows
Where — criteria for returned data
Where operators include: =, >, <, >=, <=, <> (not equal) IN, BETWEEN..AND, LIKE
Group By — for aggregate queries, columns by which the data is to be grouped
Having — for aggregate queries, criteria that the aggregate value returned must have
Order By — sort order of the returned rows
The product or result of a SELECT statement is a table that contains the data specified in the SELECT statement. In other words, the SELECT statement actually results in the creation of a set of records (sometimes called a recordset) consisting of fields based on the selection criteria.
The minimum number of records is: 0 - if no records are present or do not meet the criteria of the query, then the recordset is empty.
The minimum number of fields is: 1 - at least one field must be returned (this assumes at least one record is returned).
Examples of Select Statements:
|
SELECT * from employee;
| ||
|
SELECT lastname, firstname, address, city, state, zip FROM employee WHERE state='MD' GROUP BY zip
|
Linking relationships can join tables. For example:
|
Two tables:
| ||
|
SELECT employee.lastname, employee.firstname FROM employee, project WHERE employee.employeeid = project.employeeid
|
Using data functions such as count, avg, etc.:
|
SELECT COUNT(lastname) from employee;
| ||
|
SELECT avg(salary) from employee;
|
Insert
The insert statement is used to add data to a table.
Insert Into -- which table will have rows added
column list -- which columns will add data
Values (value list) -- values to be added to the table
Select -- returns rows to be added to the table
Example: INSERT INTO employee (lastname, firstname, address, city, state, zip) VALUES ('Smith', 'David', '111 Elm Street', 'Anycity', 'MD', '20872')
Update
The update statement is used to modify data already in the table.
Update — table to be updated
Set — columns to update and values
From — tables to be included in the update
Where — criteria to determine which columns will be updated
Example: UPDATE students SET address = '99 Elm St' WHERE lastname = 'Smith'
Delete
The delete statement is used to remove data from a table.
Delete From — specify table in which rows are to be deleted
Where — criteria for deletion
Example: DELETE FROM employee WHERE employeeid = 10
Create
The create statement is used to build new tables.
column list — describes columns to be created in new table
Example: CREATE TABLE divisions (divisionid id NOT NULL , divisiondescription varchar (30) NULL )
Select Into
The select into statement is used to build new tables from existing tables.
Into — new table being created
From — table from which data is coming from
Where — criteria for data being selected
Group By — for aggregate queries, columns by which data is grouped
Having — for aggregate queries, criteria that must be met
Order By — sort order of returned rows
Example: SELECT lastname, firstname INTO localemployees FROM employee WHERE state = 'MD'
Some things to remember about using SQL...
| SQL is generally not case sensitive - but check with the particular implementation to be sure. | ||||||
| Always watch field types when constructing
statements...
| ||||||
| SQL statements are normally enclosed in double quotes
when used in programs...
| ||||||
| In cases where a quote is included in the field, use a
series of two single quotes...
| ||||||
| Dates are stored with a single quote and #, e.g., '#10/12/99#' | ||||||
| When constructing SQL statements, be very careful of where clauses, especially in update and delete statements. | ||||||
| Use dot notation in join table situations to identify which tables and fields...e.g., members.lastname | ||||||
| Test in the database application prior to running the query. | ||||||
| Logical operators include AND, OR, NOT | ||||||
| Algebra Operators: =, <> (not equal), > , < , Like (for comparisons) | ||||||
| Use the % for wild cards. | ||||||
| SQL may be a standard, but there are differences in how it is implemented by various database programs. |
Key words and SQL
As SQL can be used in a programming environment, there are certain words you should avoid using in SQL and in the naming of tables and data:
From the SQL 1992 standard and appendices:
AFTER, ALIAS, ASYNC, BEFORE, BOOLEAN, BREADTH,
COMPLETION, CALL, CYCLE, DATA, DEPTH, DICTIONARY, EACH, ELSEIF,
EQUALS, GENERAL, IF, IGNORE, LEAVE, LESS, LIMIT, LOOP, MODIFY,
NEW, NONE, OBJECT, OFF, OID, OLD, OPERATION, OPERATORS, OTHERS,
PARAMETERS, PENDANT, PREORDER, PRIVATE, PROTECTED, RECURSIVE, REF,
REFERENCING, REPLACE, RESIGNAL, RETURN, RETURNS, ROLE, ROUTINE,
ROW, SAVEPOINT, SEARCH, SENSITIVE, SEQUENCE, SIGNAL, SIMILAR,
SQLEXCEPTION, SQLWARNING, STRUCTURE, TEST, THERE, TRIGGER, TYPE,
UNDER, VARIABLE, VIRTUAL, VISIBLE, WAIT, WHILE, WITHOUT
also...
ABSOLUTE, ACTION, ADD, ALLOCATE, ALTER, ARE, ASSERTION, AT, BETWEEN,
BIT, BIT_LENGTH, BOTH, CASCADE, CASCADED, CASE, CAST, CATALOG,
CHAR_LENGTH, CHARACTER_LENGTH, COALESCE, COLLATE, COLLATION, COLUMN,
CONNECT, CONNECTION, CONSTRAINT, CONSTRAINTS, CONVERT, CORRESPONDING,
CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER,
DATE, DAY, DEALLOCATE, DEFERRABLE, DEFERRED, DESCRIBE, DESCRIPTOR,
DIAGNOSTICS, DISCONNECT, DOMAIN, DROP, ELSE, END-EXEC, EXCEPT,
EXCEPTION, EXECUTE, EXTERNAL, EXTRACT, FALSE, FIRST, FULL, GET,
GLOBAL, HOUR, IDENTITY, IMMEDIATE, INITIALLY, INNER, INPUT,
INSENSITIVE, INTERSECT, INTERVAL, ISOLATION, JOIN, LAST, LEADING,
LEFT, LEVEL, LOCAL, LOWER, MATCH, MINUTE, MONTH, NAMES, NATIONAL,
NATURAL, NCHAR, NEXT, NO, NULLIF, OCTET_LENGTH, ONLY, OUTER, OUTPUT,
OVERLAPS, PAD, PARTIAL, POSITION, PREPARE, PRESERVE, PRIOR, READ,
RELATIVE, RESTRICT, REVOKE, RIGHT, ROWS, SCROLL, SECOND, SESSION,
SESSION_USER, SIZE, SPACE, SQLSTATE, SUBSTRING, SYSTEM_USER,
TEMPORARY, THEN, TIME, TIMESTAMP, TIMEZONE_HOUR, TIMEZONE_MINUTE,
TRAILING, TRANSACTION, TRANSLATE, TRANSLATION, TRIM, TRUE, UNKNOWN,
UPPER, USAGE, USING, VALUE, VARCHAR, VARYING, WHEN, WRITE, YEAR, ZONE
and...
ACTION, ACTOR, AFTER, ALIAS, ASYNC, ATTRIBUTES, BEFORE, BOOLEAN,
BREADTH, COMPLETION, CURRENT_PATH, CYCLE, DATA, DEPTH, DESTROY,
DICTIONARY, EACH, ELEMENT, ELSEIF, EQUALS, FACTOR, GENERAL, HOLD,
IGNORE, INSTEAD, LESS, LIMIT, LIST, MODIFY, NEW, NEW_TABLE, NO,
NONE, OFF, OID, OLD, OLD_TABLE, OPERATION, OPERATOR, OPERATORS,
PARAMETERS, PATH, PENDANT, POSTFIX, PREFIX, PREORDER, PRIVATE,
PROTECTED, RECURSIVE, REFERENCING, REPLACE, ROLE, ROUTINE, ROW,
SAVEPOINT, SEARCH, SENSITIVE, SEQUENCE, SESSION, SIMILAR, SPACE,
SQLEXCEPTION, SQLWARNING, START, STATE, STRUCTURE, SYMBOL, TERM,
TEST, THERE, TRIGGER, TYPE, UNDER, VARIABLE, VIRTUAL, VISIBLE,
WAIT, WITHOUT
and..
CALL, DO, ELSEIF, EXCEPTION, IF, LEAVE, LOOP, OTHERS, RESIGNAL,
RETURN, RETURNS, SIGNAL, TUPLE, WHILE
Please note that this is not a complete list AND you can get away with using many of the "key words" listed above.