The SQL Structure: Basic Features & SQL Command Types

In this chapter you will learn the fundamental features of the SVIEW
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 Server Database

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 perfomied 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.

Leaming 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 sehemas, domains and constraints.

Schema- This is also called the conceptual view or the complete logical view that 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. C alifomia 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 species lie 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.

SQL Command Types

Before you start programming in SQL, you need to understand its basic command categories in performing various mnctions database creation, object manipulation, data population and update, data deletion, query submission, access control and database administration, among others. The following are the main categories:

Data Definition Language (DDL)

Data Definition Language (or simply DDL) enables you to create. change or restructure, and even destroy the basic elements that are contained in a relational database. DDL focuses only on the structure, not the data contained within the elements. These basic elements or data objects include tables, schemas. views and more. Having no independent physical existence, 8 view is regarded as a virtual table in which its det'mition only exists in the metadata. However, the view’s data comes from the table (or tables) where you will derive the view. Stated below are some of the most common DDL commands:

CREATE - This command statement is responsible for building the database structure. lts syntax is:

CREATE TABLE
CREATE VIEW

ALTER - This command statement is in charge of changing the database structure after-1t has been created. Its syntax is:

ALTER TABLE
ALTER VIEW

DROP - This command is the reverse of the CREATE statement, which destroys the database structure. Its syntax is:

DROP TABLE
DROP VIEW

Data Manipulation Language (DML)

Data Manipulation Language (or simply DML) consists of SQL commands that handle data maintenance functions. This means that you are able to manipulate the data contained within the relational database objects. The command statements, which read like normal English sentences, will allow you to enter, change, remove or retrieve data.

Data Query Language (DQL)

Data Query Language (or simply DQL) consists of commands that perform data selection, which is the main focus of relational database users in the world of SQL. The statement used is SELECT that can be accompanied by other clauses or options so that your extracted results will be in an organized and readable format. You can submit a query to the database using a separate application interface or just a single command-line.

Data Control Language (DCL)

Data Control Language (or simply DCL) consists of commands that allow you to manage data access within the database. Furthermore, the database is protected from accidental or intentional misuse by controlling user privileges. DCL concentrates on transactions, which capture all SQL statements that perform database operations and save them in a log file.