The ALL operator returns True when all of the records in query result set pass the Boolean condition. A bit more explanation. The ALL statement gives a TRUE value when: 1) the subquery returns no records, or 2) all records meet the comparison. ALL will return false if even one record fails the Boolean condition. If any of the values are NULL, then the operation value will be NULL, not TRUE. For example, we want to see which authors currently have no titles: SELECT au_id FROM authors WHERE au_id <> ALL(SELECT au_id FROM titleauthor) Syntax SELECT … WHERE expression comparison … [Read more...]
Archives for April 2013
BETWEEN operator
April 1, 2013 by Leave a Comment
BETWEEN performs a Boolean True/False test to determine whether or not a value falls within a particular range, including the values mentioned in the BETWEEN clause. BETWEEN returns TRUE when in the range, FALSE when not in the range, and NULL if any range values are unknown. It can't be used with data type like BLOB, CLOB, NCLOB, REF, and ARRAY. Syntax SELECT … WHERE expression [NOT] BETWEEN bottom_of_range AND top_of_range Explained WHERE expression Compares an expression, that is, a single value like a column value or a literal expression, to the values … [Read more...]
Codd’s Rules for Relational Database Systems
April 1, 2013 by Leave a Comment
E.F. Codd applied rigorous mathematical theories (primarily set theory and relational algebra) to the management of data, and he compiled a list of criteria a database must meet to be considered relational. At its core, the relational database concept centers around storing data in tables. This concept is now so common as to seem trivial; however, not long ago the goal of designing a system capable of sustaining the relational model was considered a long shot with limited usefulness. Following are Codd’s Twelve Principles of Relational Databases: 1. Information is represented logically … [Read more...]
ANY / SOME operators
April 1, 2013 by Leave a Comment
The ANY and SOME operators, which are synonyms, perform a Boolean test returning true if even one record in the result set matches the Boolean condition. ANY/SOME is semantically equivalent to IN and EXISTS, but much less widespread. To ensure easy maintenance of your code, use IN or EXISTS rather than ANY/SOME wherever possible. A bit more explanation, ANY/SOME operators return TRUE when at least one record matches and return FALSE when none match or when there's an empty result set. If any of the values are NULL, then the operation value will be NULL, not TRUE. ANY/SOME can be used for … [Read more...]
SQL Statement Classes
April 1, 2013 by Leave a Comment
Long-standing classes for SQL statements originated back in the days of the SQL92 standard. These classifications are still common: Data Manipulation Language (DML): SQL statements which manipulate data, such as SELECT, INSERT, UPDATE, DELETE and MERGE. Data Definition Language (DDL): SQL statements control the creation, modification, and removal of database objects, such as CREATE, ALTER and DROP. Data Control Language (DCL): SQL statements related to permissions to execute and access database objects and data, such as GRANT and REVOKE. Starting with SQL3, seven core categories define the … [Read more...]
