6 Things About Programming That Every Computer Programmer Should Know by North Vincent

Author:North, Vincent
Language: eng
Format: epub, pdf
Tags: Computers & Technology
Publisher: Twelve Shades Publications
Published: 2015-06-22T16:00:00+00:00

Three: SQL Queries and Concepts

SQL (Structured Query Language) is the now-ubiquitous lingua franca of the database world. With few (legacy …) exceptions, all database systems in use today are engineered to use it. Although the implementations are not the same, they are at this point sufficiently similar that I can now make several important and general statements about them.

The SQL language was invented as part of the SEQUEL project at IBM, based in part on a pioneering paper by Dr. E. F. Codd, which was published in the June, 1970 issue of the Communications of the ACM. A key feature of this language was that it was essentially declarative. SQL allows you to specify what data you wish to obtain. It is up to the database engine to, on the fly, devise a plan for obtaining these answers, and then to do so. The database engine may exploit characteristics of the database – such as indexes that have been created on certain fields – in order to produce its answers more efficiently, but an SQL query does not specify how the work is to be carried out.

Information in an SQL database is organized into tables, which contain an unlimited number of rows. Each row consists of an identical set of columns, each of which (usually …) contains either a single value (of a single specified data type), or “no value at all.” (When this is the case, we say that the column IS NULL.) The rows in the table are in an unspecified order, although you can request that query-results should be returned to you sorted in any order you wish.

Conceptually, every SQL query (the SELECT statement) consists of a specification of the following:

1.A list of the columns whose values you want to see. (If the same column name appears in more than one table, you must be specific.)

2.The tables from whence the data is to come, and how these tables are related to one another for the purposes of this query. (See below …)

3.The selection criteria (WHERE clause …) that is to be used.

4.If you’d like to receive summary statistics, specify what the data should be GROUPed BY.

5.If you’d like to receive only certain summary rows, specify what characteristics the groups-of-interest should be “HAVING”.

6.If you’d like to receive the results sorted in a particular way, specify what the rows should be ORDERed BY.

SQL queries can obtain results from any number of tables at a time. The query specifies how the rows in the various tables should be considered to be related to one another. This relationship is expressed by the presence of identical values in one-or-more specified columns in any pair of tables. For example, a customer_id field in an Orders table is sufficient to enable any sort of information about that customer to be retrieved from a Customers table in the same query. This is referred to as a join.

There are three types of joins that can be used: “inner joins,” which return only rows which have identical

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.