A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other
Since it possible to join a table to itself (a self-join), so that even qualified column names can be ambiguous, SQL provides an Alias facility. E.g. SELECT P.Staff_no, P.Name, Q.Staff_no FROM Staff P, Staff Q WHERE P.Name = Q.Name AND P.Staff_no < Q.Staff_No. The aliases (P and Q), a form of label, are specified in the FROM clause of the query, each following a specification of the table for which it is an alias. 'List the staff numbers and names of all members of staff who have the same name as another member of staff'. (< becuse <> is symmetrical, would lead to same pair being reported twice). Aliases can be used in any query (not just self-joins), e.g. to provide qualified column references or to reduce typing (though they needn't be short). An alias is usable only within the statement in which it is defined. When an alias is defined in a from clause, the original table name cannot be used in the rest of the statement unless it is separately specified as part of the FROM clause (Staff P, Staff).
Quantified predicate. SELECT name FROM student WHERE registered <= AL: (SELECT DISTINCT registered FROM student). List the names of all students who registered in the earliest year. The keyword ALL requires that the condition is true for every member of the collection. SQL only allows ALL to be used with subqueries. e.g. (not using subqueries!) 5 >= ALL (2,4,3,5) is true, 5 = ALL(2,3,4,5) is false (not all are 5), 5<> ALL (2,4,3,5) because there is a 5. (i.e. NOT (5 = ANY (2,4,3,5)).
Logical operator. When AND is used between 2 conditions, both must be true for the combined condition to be true. The precedence of AND is higher than OR.
quantified predicate. SQL only allows ANY to be used with subqueries. e.g. (not using subqueries!) 5 = ANY(2,3,4,5) is true because there is a 5, 5<> ANY (2,4,3,5) is true (not = 2,3,4)
Set function. When applied to a collection of values defined by a column expression of numeric data type, AVG results in the average of non-null values in the collection. If DISTINCT is includedin an argument, all duplicate values are ignored in the calculation. If the values in the collection are all null then the function results in null, as is the case when the collection is empty. See Set Functions (where it is used as an e.g. )
Table with stored data
Predicate. Operator. E.g. SELECT country, product, quantity FROM production WHERE quantity BETWEEN 5000 AND 6000. ... WHERE a NOT BETWEEN b AND c. (I.e. a< b or a>c). ... WHERE NOT (a BETWEEN b AND c) (I.e. a< b or a>c). Note, e.g. the difference between the set of character strings which satisfy the condition BETWEEN 'S' AND 'T' and the set of character strings which satisfy LIKE 'S%' is the one character string 'T'.
SELECT * FROM s_country, S_city results in a table which is the Cartesian product of the 2 tables specified in the FROM clause, in that it has thhe columns of both the tables and in that every row of the second table has been appended to every row of the first. Ccosequently, the number of rows in the final table is the product of the number of rows in each table. See also join.
schema tables
CHAR, VARCHAR
names of columns of data type character string, and may include constant character strings (enclosed in quotes) with a character string operator. (SQL2)
Attribute
either a numeric expression or a character string expression May be included in a SELECT clause or a WHERE clause (comparability important) e.g. WHERE ((births - deaths)/population) * 100 > 0.5.
Any 2 numeric expressions can be compared and so can 2 character string expressions, but not 1 of each.
=, <, >, <=, >>=, <>. Any kind of comparison must be made between comparable column expressions, i.e. numeric or data string.
uses more than just query specification. There are 2 ways of formulating composite queries: one using the UNION operator and the other using subqueries.
Set function. With a column expression argument: when applied to a collection of values defined by a column expression of any data type, COUNT results in the number of non-null values in the collection. If DISTINCT is included in the argument, all duplicate values are ignored in the calculation. If the values in the collection re all null or the collection is empty, then the function results in zero (which is a more reasonable result than for AVG. E.g. SELECT COUNT(*) FROM Population returns 1 col, 1 row, answering 'how many rows are there in the population table? In general, the COUNT function with am asterisk results in the number of rows in a table. COUNT can also be used to count the number of values in a column, in which case the argument is a column expression which may be of any data type. When used in this way, it is similar to the AVG function, in that only non-null values arecounted and if DISTINCT is included duplicates are involved. The BETWEEN operator is inclusive.
Includes a DDL and a DML, e.g. SQL.
See schema tables
e.g. char, varchar, float, real, smallint (whose may value depends on the implementation)
Database Management System
Data definition language
in SELECT clause (including set functions e.g. AVG), prevents any duplicate rows appearing in the final table.
Data manipulation language
Subset of SQL2, but same as SQL:1989, for compatibility with SQL2
gives the result of the SQL query. (Logical processing model)
More than one table can be referenced in the FROM clause, allowing us to specific joins in SQL.
Clause in a query specification. Groups rows of data together so that each group can be processed in a similar way. E.g. SELECT region, COUNT (*) FROM student GROUP BY region. Shows how many students are registered in each region. (Equivalent to performing the following query separately for each region: SELECT COUNT (*) FROM student WHERE region = '1'.) The purpose of this kind of query is for each group of rows to be represented by just a single row in the final table. Because of this, the SELECT clause in such a query can only contain either a grouping column or a value expression involving a set function, which results in one value for every group. E.g. SELECT classification, MAX(quantity), COUNT (product) FROM production GROUP BY classification. E.g. SELECT course_code, COUNT (student_id) FROM enrolment WHERE course_code IN ('c2', 'c3') GROUP BY course_code - answers the question How many students are enrolled for each of courses c2 and c3?. Sequence: FROM, WHERE, GROUP BY, SELECT. Sometimes it is necessary to group the data within a group into subgroups, e.g. within region, group by counsellor number: GROUP BY region, counsellor_no. While SQL does not determine any particular ordering for the rows in the result of a GROUP BY clause, an implementation will. See also HAVING clause.
A column specified in the GROUP BY clause and hence one with the same value in every row of each group.
Clause. The HAVING clause is to groups what the WHERE clause is to rows. In the same way that rows have to satisfy the search condition in a where clause, so groups must satisfy the search condition in a HAVING clause in order to be processed further. E.g. SELECT country, COUNT (product), SUM(quantity), FROM production GROUP BY country HAVING SUM(quantity) > 15000. Using the production table (FROM), (no WHERE to restrict rows), for every country (GROUP BY), list the number of products it produces (SELECT) provided this total is in excess of 15,000,000 tonnes (HAVING). Any column referenced in a predicate in the search condition of a HAVING clause must be a grouping column or must have a set function applied to it.E.g. compare with GROUP BY e.g.: SELECT course_code, COUNT (student_id) FROM enrolment GROUP BY course_code HAVING course_code IN ('c2', 'c3') . Can interchange HAVING and GROUP BY only when a predicate in a WHERE clause is allowable in a HAVING clause (e.g. WHERE course_code in the e.g. is a grouping column)..
Predicate. Operator. SELECT name, population FROM city WHERE name IN ("Athens', Lisbon', 'Oporto'). .. WHERE credit NOT IN (0.5, 1.0). (i.e. credit <> 0.5 and <> 1.0)
Subset of SQL2
e.g. FROM prouces output intermediate table that is copy of the input, which SELELCT uses to produce another containing the columns required. (Logical processing model)
Predicate. Operator. Used to check a column for null. Can only involve a column name and not a column expression.
More than one table can be referenced in the FROM clause, allowing us to specific joins in SQL. SELECT * FROM s_country, S_city results in a table which is the Cartesian product of the 2 tables specified in the FROM clause. If 2olumns from each table share the same name, you can reference either unambiguously by qualifying a column name with the name of the table in the FROM clause to which it relates, using a dot notation. E.g. S_Country.Name, S_City.Name. A pair of rows, one from each table, are related if they have the same value in the same columns. Thus to produce a joined table based on such a relationship, we can use a Cartesian product of the 2 tables and impose a condition (called a join condition) that the 2 corresponding columns must be equal. It is often helpful to put a join condition before other predicates, so that their roles may be distinguished. E.g. A.Id = B.Id AND A.Course = B.Course - join on 2 columns - 2 predicates. More generally, any number of tab;es can be specificed in the FROM clause of a query and the WHERE clause can include any number of conditions, so many joins may be specified in one statement. See also Self-join.
See Subqueries versus Joins.
A Join condition imposes a condition on those columns which represent a relationship between the tables being joined. We can use a Cartesian product of 2 tables and impose a condition (called a join condition) that 2 corresponding columns must be equal. This condition is specified in the WHERE clause of a query, and is no different from any other predicates that may be included in the query (you must qualify column names if there is any ambiguity). The join condition is expressed using comparison predicates for individual columns.
e.g. FROM, WHERE, GROUP BY, HAVING, SELECT + ALL, ANY (aka SOME)
Predicate. Operator. Used to match a character string column with a known string or part of a string. Can only involve a column name and not a column expression. E.g. SELECT DISTINCT classification FROM production WHERE classification LIKE '_h%s'. The LIKE operator is used in a situation where one wishes to compare character string values in a column with a pattern in the form of a constant character string. The matching of the strings need not be complete. To allow it to be incomplete, the underscore character, _, stands for any single character while the percentage sign, %, stands for any sequence of zero or more characters. So, List all the distinct classifications in the production table which have an h in the second position and end in s. A lower case letter will not match with its uppercase counterpart (use WHERE country LIKE '%g% OR country LIKE '%G%'). In general, a LIKE predicate can be used to match any column of data type character string with any constant string (enclosed in quotes). NOT can also be used, as NOT LIKE.
AND, OR, NOT
shows that the clauses of simple queries are logically processed in the order: FROM..., WHERE..., GROUP BY..., HAVING.., SELECT...
Set Function. When applied to a collection of values defined by a column expression of any data type, MAX results in the maximum of non-null values in the collection. In the case of character string data types, the maximum value is based on the numeric codes of each character (in DBQ, this is ASCII), which results in the last value as found in a dictionary. DISTINCT can be included in the argument, but it does not have any affect! If the values in the collection are all null or the set is empty, then the function results in null.
MIN operates in exactly the same way as MAX, except that it selelcts the minimum value.
There is no specific SQL concept corresponding to a natural join, but that result can be producr=ed by including a join in the WHERE clause and specifying appropriate columns in the SELECT clause.
Logical operator. Reverses the truth value of the condition to which it applies, so that true becomes false and false becomes true.
is not a value. The only predicate that can use it is IS (NOT) NULL. DBQ uses 'null' as a way of displaying what originates as nul in the base table. Implementations may use other notations for displaying null, such as a ?, - or a blank sapce.
INTEGER, SMALLINT, DECIMAL
involves column names (from FROM table; must be of numeric data type) and arithmetic operators (+, -, *, /) and may include numeric constants.
Logical operator. When OR is used between 2 conditions, either one or the other (or both) must be true for the combined condition to be true. The precedence of AND is higher than OR, so use brackets where necessary.
Means of sorting the final results of a query.
Predicates in SQL use the conventional comparison operators. They alos use BETWEEN, IN, LIKE and IS NULL. Unlike traditional Boolean logic, a predicate is unknown when it involves null (except with IS NULL). You can't use null in any of the predicates; you have to use the IS NULL predicate.
Predicates are said to be quantified because the comparison involves a collection of values from a subquery and there is the need to specifiy how many members of the collection are expected to satisfy the predicate. This may be either all or at least one, for which we use the keywords ALL or ANY respectively. SOME can be used instead of ANY.
SELECT
FROM
WHERE
GROUP BY
HAVING
<select list
<table list>
<search condition>
<grouping column list>
<search condition>
statement for data retrieval, uses a basic building block: query specification. Must contain SELECT and FROM. The result of the query is a table. What data, not how retrieved. A query statement is a combination of many clauses that can be processed to give a single resultant table, whereas a query specification is formed from just one, or none, of SELECT, FROM, WHERE, GROUP BY and HAVING clauses (though WHERE and HAVING subclauses may include subqueries).
recalls the stored query whose name follows.
simple queries, composite queries, ORDER BY.
hold data about what tables are in the database, what columns these tables have and what the data types of these columns are. They can be queried. Plus passwords, details of the DBQ implementation. (Data dictionaries hold similar data for use of system developers rather than the DBMS)
Columns are listed in sequence of SELECT, not of original table. List the name of each country and the name of each city (in that order) for each city in the CITY table. SELECT country FROM city produces duplicate rows. SELECT DISTINCT country FROM city does not.
e.g. table called city: List all the data about all the cities in the CITY table.
It is possible to join a table to itself. See Alias. A common reason for joining a table to itself occurs in the case of a recursive relationship. E.g. nurse in hospital.
Functions of a collection of values (not of a set, as sets may not have dups but set functions can). AVG, COUNT, MAX, MIN, SUM. E.g. SELECT AVG (Population) FROM Country. Result is table of 1 column and 1 row. (Beware NULLs!). Can't have SELECT AVG (Cars), Area FROM Country. as conflict about number of rows resulting. Can have SELECT AVG (Births), AVG(Deaths) FROM Country. E.g. can have SELECT AVG (((Births-deaths)/Population)*100) FROM Country. SELECT AVG (DISTINCT Population) FROM .. ignores all duplicates. See COUNT. A subquery cannot be an argument of a set function.
just uses query specification
SOME can be used instead of ANY.
Structured Query Language. Excludes storage DDL, interactive I/O
SQL:1987. Often a subset of individual versions of SQL. Common Core. No PK or FK constraints.
SQL:1992. Domains, etc.
SQL1
PK or FK constraints.
SQL2
e.g. DBQ, DB2, Oracle, Ingres.
The subquery, like the join, allows us to use data from different tables.
Part of composite query, when one query specification is nested within another, so that the result of the inner query specification becomes part of the processing of the outer. In this case the composite form is still considered a query specification. E.g. List all those countries whose production of oats is less than the average. SELECT country FROM production WHERE product = 'Oats' AND quantity < (SELECT AVG(quantity) FROM production WHERE product = 'Oats' ). The (SELECT AVG()...) is the subquery. In general, subqueries can be nested, one inside another, which means that subqueries can have subqueries which themselves can have subqueries, and so on. While the subquery and the outer query include a FROM clause which specifies the Production table, each effectively has a separate copy of this table. First process the subquery, then use the result of processing the subquery in processing the rest of the query. In general, a subquery is a query specification which must result in a table consisting of a single column (except when used in an EXISTS predicate) and must be enclosed in brackets as part of an outer query specification. A subquery can occur in a predicate of the search condition for a WHERE or HAVING clause. If it is known that the subquery results in a single value, then it can be used as the right hand value expression with the comparison operators, <, =, etc. More generally, a subquery results in a collection of values for the specified column, in which case normal comparison operators cannot be used and a quantified predicate has to be employed. As well as answering requests which involve a comparison of a value in each row with either a set function or a collection of values based on the whole table, subqueris can be used for a request which involves a grouped table and requires a comparison of some value for each group with either a set function or a collection of values based on the group. e.g. Give the course code and number of students for the course on which the largest number of students are enrolled. SELECT course_code, COUNT(Student_Id) FROM Enrolment GROUP BY course_code HAVING COUNT(Student_Id) >= ALL (SELECT COUNT(Student_Id) FROM Enrolment GROUP BY course_code). A subquery cannot be an argument of a set function, so can't use MAX in the last example.
Set Function. When applied to a collection of values defined by a column expression of numeric data type, SUM results in the sum of non-null values in the collection. If DISTINCT is used in the argument, all duplicate values are ignored in the calculation. If the values in the collection are all null then the function results in null, as is the case when the collection is empty (see comment for AVG).
see Catalog.
Relation
Operator used in composite queries. Constitutes a way of combining the results of separate, (union) compatible queries. E.g. Provide a table which allows us to compare the populations of Spain and Ireland for the years given in the POPULATION table with the populations for the same countries given in the COUNTRY table for 1985. SELECT country, yr, population FROM population WHERE country IN ('Spain', 'Ireland') UNION SELECT Nmae, 1985, population FROM country WHERE name IN ('Spain', 'Ireland'). The final table is the union of the final tables of the 2 individual queries, not including any duplicate rows, whether the duplication originated within or between the two intermediate tables (this removal of duplicates is an exception to SQL processing; you don't need DISTINCT). If you particularly don't want duplicates removed then you can use the operator UNION ALL. Each query specification is processed independently of the other, and any columns or aliases have no meaning with the other. The need for UNION may be recognised whenever there is a requirement for a table such that each row may come from two or more other tables. More than two tables can be a source of such rows because, in general, a single statement may include the union of a number of query specifications. UNION statements have at least two query specifications.
If you particularly don't want duplicates removed then you can use the operator UNION ALL.
Each separate query specification which is part of a union query must result in intermediate tables whose columns match in number and have comparable data types. Exact matching of the parameters of the data type is not required, e.g. character strings may have different lengths.
part of SELECT clause of a query. Either a column expression or a set function.
Table which is derived from base tables.
Specifices a search condition. Only those rows (all of them) which satisfy the condition given in the WHERE clause of a query may contrinute to its result. SELECT Staff_No FROM Staff WHERE Name = 'Jo' lists the staff numbers of all staff with the name Jo. Notes: the search condition of the WHERE clause contains just one predicate, which is the single condition Name = 'Jo'; the Name column does not appear in the final table, as Name is of data type character string; 'Jo' must be in quotes; 'jo' (lower-case) would not have been found; logical processing = FROM, WHERE, SELECT. Column expressions can be used in the predicates of a WHERE clause. Checking any predicate for a row involves only the values in that row. Thus set functions are not allowed in the predicates of a WHERE clause, because they are calculated using all rows.
See NULL.
See NULL.
(Blank space) see NULL.
see LIKE.
see LIKE.
all the columns in the table.
Back to top Back to Index Home Serious Stuff
© 2001. Copyright Sue Nethercott.