Many database vendors provide their own dialect of SQL to supplement the standard. For example, conditional processing, error handling, and flow-of-control statements are often provided by database vendors as an element of their own dialect of the SQL standard. The ANSI SQL standard SQL/PSM (Persistent Stored Module) package describes many features associated with programming stored procedures and incorporates many of the extensions offered by these dialects. However, the implementation of SQL/PSM is not consistent or reliable across all database vendors. Some popular dialects of SQL … [Read more...]
SQL History and Implementation
April 1, 2013 by Leave a Comment
In the early 1970's, the seminal work of IBM research fellow Dr. E. F. Codd led to the development of a relational database product called SEQUEL, or Structured English Query Language. SEQUEL due to some legal hurdles was changed to ultimately SQL, or the Structured Query Language. IBM, along with other database vendors, wanted a standardized method for accessing and manipulating data in a relational database. Although IBM was the first to develop relational database theory, Oracle is widely credited with being first to market and popularize the technology. Over time, SQL proved popular … [Read more...]
Introduction to the Relational Model and ANSI SQL
April 1, 2013 by Leave a Comment
Relational database management systems (RDBMS) such as those covered on this website are the principal engines of structured data systems worldwide. They enable a multitude of users to quickly and simultaneously access, create, edit, and manipulate data without impacting other users. And they enable developers to write useful applications to access their data while providing administrators with the consistent and reliable capabilities they need to maintain, secure, and optimize organizational data resources. An RDBMS may be defined as a system whose users view data as a collection of tables … [Read more...]
ALL Operator
April 1, 2013 by 2 Comments
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...]
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...]
What Is a Relational Database?
September 5, 2012 by Leave a Comment
Relational?!? Move On, Geezer! Maybe you're thinking that relational databases management systems (RDBMSs), like Microsoft SQL Server and Oracle, are going the way of punched cards and rotary phones. After all, there's been a lot of hype these days in the IT media about the rise of so-called NoSQL (Not Only SQL) databases. Many new and upcoming CS and MIS graduates who like working with data might think that relational databases are, at best, soon-to-be legacy systems and, at worst, are a career dead-end. Wrong!!! It's true that all the cool-cat computing services (Amazon, Facebook, … [Read more...]
