The SQL Structure
In this chapter you will learn the fundamental features of the SQL language and an overview of its programming aspect. In addition, you will be presented with a step-by-step instruction on where and how to download SQLite, a version of the SQL software that will be used all throughout the discussion of this e-Book.
SQL Fundamental Features
SQL is a flexible computer language that you can deploy in different ways to communicate with relational databases. This software has some distinct features that differentiates it from other programming applications. First and foremost, SQL is a nonprocedural language. Most computer programs (e.g., C, C++ and Java) solve problems by following a sequence of commands that is called a procedure. In this case, one specific operation is performed after another until the required task has been accomplished. The flow of operation can either be a linear sequence or a looping one, depending on what the programmer had specified. This is not the same for SQL. In using this application, you will just have to specify the output that you want, not how you want to generate the output. From the CUSTOMER TABLE, if you want to create a separate list of contacts whose company are located in Texas then you have to retrieve the rows where the STATE column contains “TX” as its value. In writing the SQL command, you don’t have to indicate how the information should be retrieved. It is the primary role of the database management system to examine the database and decide how to generate the results you wanted.
Learning the SQL syntax is like understanding the English language structure. Its command language, comprised of a limited number of statements, performs three primary data functions definition, manipulation and control. The SQL programming language also includes reserved words that are only to be used for specific purposes. Thus, you cannot use these words as names for variables, tables and columns; or in any other way apart from their intended use. Below are some of the most common reserved words in SQL:2011.
If you think that an SQL database is just a collection of tables, then you are wrong. There are additional structures that need to be specified to maintain the integrity of your data, such as schemas, domains and constraints.
• Schema– This is also called theconceptual viewor thecomplete logical viewthat defines the entire database structure and provides overall table organization. Such schema is considered a metadata – stored in tables and part of the database (just like tables that consist of regular data).
• Domain– This specifies the set of all finite data values you can store in a particular table column or attribute. For example, in our previous CUSTOMER TABLE the STATE column can only contain the values “TX”, “NY”, “CA” and “NV” if you only provide products and services in the states of Texas, New York, California and Nevada respectively. So these four state abbreviations are the domain of the STATE attribute.
• Constraint– Often ignored but one of the important database components, this sets down the rules that identify what data values a specific table attribute can contain. Incorporating tight constraints assures that database users only enter valid data into a particular column. Together
with defined table characteristics, column constraints determine its domain. Using the same STATE column as an example with the given constraint of only the four values, if a database user enters “NJ” for New Jersey, then the entry will not be accepted. The system will not proceed until a valid value is entered for the STATE attribute, unless the database structure needs to be updated due to sudden business changes.
