SQL can be typed in any combination of upper and lower case letters such as SELECT, select, or SeLeCt. Normally, anything that is not between quotes is typed in capital letters and is displayed using a type font where every character has the same width so that the SQL statement can be aligned for readability. The formatting of spaces and tabs in an SQL statement does not affect it’s execution, but it does affect your ability to read it. While multiple SQL statements can be placed on one line, this practice is discouraged. An SQL statement can span multiple lines and can start in any column as long as words, names, or quoted strings are not split. Here is an unformatted statement:

SELECT CUSTOMER_TBL.CUST_ID, CUSTOMER_TBL.CUST_NAME, CUSTOMER_TBL.ADDRESS, CUSTOMER_TBL.CITY, CUSTOMER_TBL.STATE, CUSTOMER_TBL.ZIP, INVENTORY_TBL.DESCRIPTION, INVENTORY_TBL.CARGO_TONS, SALES_TBL.SALE_DATE, SALES_TBL.QTY, SALES_TBL.AMOUNT FROM CUSTOMER_TBL, INVENTORY_TBL, SALES_TBL WHERE CUSTOMER_TBL.CUST_ID = SALES_TBL.CUST.ID AND SALES_TBL.INVN_ID = INVENTORY_TBL.INVN_ID;

Here is the same statement with reformatting for improved readability. Don’t worry about the details. They will be covered later.

SELECT C.CUST_ID,
           C.CUST_NAME,
           C.ADDRESS,
           C.CITY,
           C.STATE,
           C.ZIP,
           I.DESCRIPTION,
           I.CARGO_TONS,
           L.SALE_DATE,
           L.QTY,
           L.AMOUNT
  FROM CUSTOMER_TBL C,
           INVENTORY_TBL I,
           SALES_TBL L
WHERE C.CUST_ID = O.CUST.ID
    AND O.INVN_ID = I.INVN_ID;

Both statements are the same, but the second statement is much easier to read. Spacing and tabs have been added to the second statement, which also uses table aliases in the FROM clause for brevity. Don’t worry about the details of the statement. What is illustrated here is clarity and readability.

Here is a statement used to explain the SQL syntax:

Comment: A comment is optional and is for human interpretation only. The software totally ignores any comments. Most PC and server vendors use the “–” for comments, whereas mainframe engines such as IBM’s DB2 use “/*” and “*/” to bracket their comments.

SQL Statement: An SQL statement is a legitimate combination of clauses (in a specific order) consisting of appropriate keywords, identifiers, symbols, and operators, and is terminated with a semicolon.

Clauses: A clause is part of an SQL statement and can be required or optional in different contexts. Each clause starts with a keyword (i.e. SELECT, FROM, WHERE, ORDER BY, etc.) and these clauses must be written in a particular order. Following the keyword are the appropriate identifiers and operators.

Keywords: These are reserved words that have specific meaning to the RDBMS interpreter. Since these are reserved words, they can not be used for any other purpose (such as table names).

Identifiers: These are the names of database objects that have been created by you or your database administrator to identify tables, views, columns, indices, aliases, etc. They can be any name except a keyword.

Terminating semicolon: All SQL statements must contain a semicolon “;” that defines the end of the SQL statement.

SQL (pronounced SEQUEL) stands for Structured Query Language and is the language used to communicate with the Relational Database Management System (RDBMS). This language is simpicity itself and consists of the following modules.

Most users will only use the DML commands.

Relational Model

The table (row and column) structure is easy for users to understand. The relational database model, as implemented by “Codd’s 12 rules,” has gained wide acceptance by vendors.

SQL Standards

SQL is an open standard and is not “owned” by any company. The standard is established by the American National Standards Institute (ANSI) under the joint leadership of the International Organization for Standardization (ISO) and the International Engineering Consortium (IEC).

Like English, Easy to Learn

Compared to some other programming languages, SQL is easy to learn. An SQL statement reads like a sentence.

Nonprocedural

If you have never done programming before, this point is of little consequence: It means that when you do a query, all of the data results are returned to you at once, instead of giving you one row (or record) at a time.

Interactive or Embedded

With interactive SQL, you issue SQL commands directly to the graphical- or command-line tool of DBMS, which displays the results as soon as they are produced. If you are writing an application, you can embed the SQL in the host programming language such as C++, COBOL, Java, Perl, PHP, or Python. You can use SQL in a PHP CGI script to query a MySQL database.

Multiple Views of Data

The person that created the database can give different views of the database to different users. For example, an employee from the payroll department might be restricted to only payroll data. Also, data from multiple tables can be combined into one table presentation.

Dynamic Data Definition

With SQL, the structure of the database can be changed dynamically while users are accessing the database. This means that SQL can create a database, control security, retrieve data, change data, and share data with many users simultaneously.

Client/Server Database

A central computer called the server is accessed from a local area network (LAN) or a wide area network (WAN). PCs, called clients, are used to access the server. These client/server networks are generally not accessible to the general public.

Web-Based Database

Customers are able to use PCs with internet browsers to access a web site available to the general public. They may need to log in (if required) to access a database. SQL is executed by the web application and returns data to the web server where it is returned to the internet browser for the customer’s use.

In a many-to-many relationship, each row in Table A can have zero, one, or many matching entries in Table B and Table B can have zero, one, or many matching entries in Table A. There are two ways to create a many-to-many relationship:

> Two tables can be joined together. See JOIN.
> You can create a third table called a junction table whose primary key is a composite of the keys from Table A and Table B. This creates a unique key for the junction table and changes these keys to a one-to-many relationship.

In a one-to-many relationship, each row in Table A can have zero, one, or many matching entries in Table B. However, Table B has only one matching entry in Table A. A one-to-many relationship exists when the primary key of Table A matches a foreign key in Table B.

Relational DataBase Management Systems (RDBMS) perform more reliably than other systems because they function with an association established between common column values of two or more tables. There are three kinds of relationships:

> One-to-One
> One-to-Many
> Many-to-Many

In a one-to-one relationship, each row in Table A can have no more than one matching entry in Table B. If you are going to have a one-to-one relationship, then you probably need to include the columns from both tables in just one table. A possible exception to combining multiple one-to-one tables into one table would be if the second table’s data required higher security that would be applied only to the second table.

 

Foreign keys are used to associate data between tables. For instance, a foreign key of REP in the CUSTOMER_TBL points to the primary key EMPL_ID in the EMPLOYEE_TBL.

> A foreign key is one or more columns in a table whose values reference values in another table.
> A foreign key in the child table establishes a relationship to the primary key in the parent table.
> The matching foreign key and primary key can have different names.
> If this relationship is enforced, then this is called referential integrity.
> If the relationship is not enforced and there is a foreign key in a child table that does not match
the primary key of the parent table, then this is called an orphan row.
> Unlike primary keys, foreign keys can be null.
> Foreign keys are not always unique in their table.
> A foreign key can reference the primary key of the same table. For instance, the MANAGER of
an employee can reference the EMPLOYEE ID of the manager.

Tables and columns have unique names, but rows are unnamed. Rows can not be identified by their position within the table, and the relational model looks at a table as an unordered set. Therefore, each table is required to have one or more columns as a unique primary key. It is traditional to place the primary key columns in the left-hand positions.

> A table must have one and only one primary key.
> A primary key with only one column is called a simple key.
> A primary key with two or more columns is called a composite key.
> No two rows in a table can have the same primary key.
> A primary key only includes the columns necessary for uniqueness.
> A primary key can not have an empty (null) value in any column.
> If referential integrity is enforced, a primary key can not be deleted unless all foreign keys are also deleted.

Tables

You will see the Relational Database as a collection of one or more tables.

A table:
> Is a two dimensional grid of rows and columns.
> Holds data as a value at each row and column intersection.
> Has at least one column and zero or more rows. (The table is empty if it has no rows.)
> Has a unique name within the Relational Database.

An RDBMS uses two types of tables:
> User tables contain user-defined and maintained data.
> System tables contain data about the tables defined by the users and is called the system catalog. This includes table definitions, security, and performance statistics.

Columns

> Each column reflects a specific property or attribute of the table’s entity.
> Each column has a unique name within its table to signify this property or attribute. However, this same name may also be used in other tables.
> Each column entry contains a single data value.
> The order of the columns from left to right is unimportant.
> Each column has a constraint on its values called the domain. This domain limits the column values to characters and numeric values (names, dates, times, etc.) with other specifications, such as length and null values. A null value means that there is no value, which is not the same as a blank value.

Rows

> Each row reflects a fact about a table’s entity. If this is an employee table, then each row is for a different employee.
> Each column in the row contains a value or a null.
> No two rows of a table can be identical, because each row in a table is uniquely identified by its primary key
> The order of the rows, top to bottom, is unimportant.