SQL / SQL Structure Database Programming

---

Managing Tables

To create a table you must be based on its specifications as they are defined during the requirement analysis phase for the Entities (Objects) Data Model - EDM of the application.

That means you know the pieces of information (columns) that will structure each row of the table as well as the data types of the fields.

Moreover, you must take care to start the table and column names with a letter which could be followed by letters, numbers, or underscores.

These names can have a maximum total length of 30 characters.

Notice that SQL reserved keywords such as "SELECT", "CREATE", "INSERT", "DELETE", etc are not allowed to be used as names for tables or column names.

The data types define the type of data that each particular column can accept as content.

According to the aim that a column (attribute) serves its data type should be defined respectively.

Here are the most common data types:

• char (size):
  

This data type defines a fixed-length character string. Its size is specified in parenthesis with a maximum number 255 bytes.

• varchar (size):
  

The variable character defines the variable-length character string. Its maximum size is specified in parenthesis.

•  number (size):
  

The number defines the number value. Its size is a max number of column digits specified in parenthesis.

• date:

 Date value

• number (size,d):
  

The number with decimal part defines the number value with a maximum number of total digits in "size", and with a maximum number of "d" digits to the right of the decimal.

On the one hand during the tables creation process we have the columns data types declarations and on the other hand the columns constraints. But, what are constraints?

A constraint is associated with a column as well as the data which is going to be put into that column to satisfy it.

For example, a very popular constraint is that a column can not have a null value "not null" and another also very popular constraint is the "primary key" constraint which demands a unique identification of each record (row) in a table.

---

Tables and Information Structure

A relational database consists of one or more associated tables which contain columns and rows. Each table holds information about a specific category of entities.

The table columns are predefined as the common attributes of all entities that the table contains.
The table rows are the records of the entities that the table contains. Each separate row gives information of each unique entity.

Each field of the table gives a piece of information which is based on the attribute of the particular column.

Thus, if we consider that we have a table which contains information about the customers of a company with the name "Customers" a data instance of it will be the following.

As you can see the table "Customers" above has four pre-defined fields (attributes) which are the LastName, the FirstName, the City, and the State.
On the other hand It consists of four rows (records) of information each one describes a unique customer (entity or object).

---

Create, Insert and Update SQL Statements

The Tables SQL Statements Create, Insert and Update

The CREATE Statement

It is now the time to design and create our own tables. We may have as many columns as we would like. The constraints are optional.
 
Thus, we will start using the CREATE statement to create a new table.
The following code shows the simple format of the statement.

CREATE table "table_name" ("col_name1" "data type", "col_name2" "data type", "col_name3" "data type" );

The following syntax is used if you want to implement optional constraints:

CREATE table "tablename" ("col_name1" "data type" [constraint], "col_name2" "data type" [constraint], "col_name3" "data type" [constraint]
);

The following example creates the table Customers

CREATE table Customers (LastName varchar(15), FirstName varchar(20), Credit number(7), City varchar(30), State varchar(2) );

As you can see after the name of the table we opened parenthesis and when we finished with its columns (fields of the record) definition we closed it.

It is important to make sure that you use comma in order to separate each column definition. Finally as any SQL statement the CREATE statement should end with a ";".

After the execution of the statement above we will receive a new table with no data as the following.

The INSERT statement

To enter or add new rows (records) into a table within the database we use the INSERT statement.

The syntax is:

INSERT INTO table_name VALUES (value1, value2, ... valuen);

The keyword into is followed by the table name which is followed by the keyword values and a list of values enclosed in parenthesis separated by commas.

These values concern the contents of the row fields and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.

Keep in mind that the order of your values must match the order and data type for each of the fields within the table.
 
If you are adding a row that has data for each of the fields within that table, the syntax above is more suitable.

You can use an alternate syntax as the following:

INSERT INTO table_name (column1,column2,...,columnN) VALUES (value1, value2,..., valuen);

Through this format you can specify which columns you have data for and what the values are. In addition, the order of the columns and the corresponding values must match. In the following examples we insert a new row (record) in the Customers table:

INSERT INTO Customers VALUES ("Smith", "Jane", 22000, "Houston", "TX");

The result will be the new Customers table that follows.

In order to add specific data for a new row (record) we use the second type of syntax as follows:

INSERT INTO Customers (LastName, FirstName, City, State) VALUES ("Smith", "Jane", "Houston", "TX");

The result will be:

The UPDATE statement

In order to update existing data within a table of a database we use the UPDATE statement.

The syntax is:

UPDATE table_name SET column_name = NewValue WHERE column_name = SomeValue;

Thus, if we want to change each credit value of the table Customers from 15000 to 20000 we can write the following:

UPDATE Customers SET Credit = 20000 WHERE Credit = 15000;

The result will be:

Through the same UPDATE statement we can update multiple columns at a time.
If we want to change the City and State of customer Brown Helen because she relocated, we will write the following statement:

UPDATE Customers SET City = "Texas", State="TX" WHERE LastName = "Brown" AND FirstName = "Helen";

The execution of the statement above will return:

As you can see to update or change table records which satisfy specific criteria you must use the where clause.

A more complicated update syntax example is the following in which we are increasing the credit adding 5000 USD more to customers who live in the state "TX" and their existing credit is less or equal to 30000 USD.
Look at the code:

UPDATE Customers SET Credit = Credit + 5000 WHERE State = "TX" AND Credit <= 30000;

The result would be:

We have mentioned in the first chapter of this book "SQL and Relational Databases" that each table of a database must have a unique record identifier which is called the primary key.
The aim of this primary key is to make record identification easier and more fullproof.

Thus, if in our example earlier with the customer who relocated in a new City and State we had one more customer with the same LastName and FirstName like Brown Helen, then both of them will be updated instead of only one.

So, keep always in mind that the unique identifier (primary key) in your criteria would prevent you from these types of problems.

---

Delete, Select and Drop SQL Statements

Delete, Select and Drop SQL Statements

The DELETE statement

The DELETE statement is used to delete rows or records within a table and clear out the data we no longer need.

To delete a specific record or row, we use the DELETE statement with the keyword FROM followed by the table name, followed by the where clause which contains the conditions to delete.

The syntax is as follows:

DELETE FROM table_name WHERE column_name = SomeValue;

To delete all records from a table use the operand * as follows:

DELETE * FROM table_name;

You must be very careful when you are going to use the delete statement ensuring that you are deleting the correct rows (records).
The only secure way to delete records is through the where clause using their unique identifiers or primary keys.
In the following example we will delete Jones George from our table of Customers.

DELETE FROM Customers WHERE LastName = "Jones";

The result will be:

If you omit the WHERE clause, all records of the table will be deleted!


The SELECT Statement

The SELECT statement is used to select read-only data from a database based on a set of criteria. It is the most often used statement of SQL language.

The syntax for a select statement is:

SELECT column1, column2, ... columnN FROM table_name
WHERE condition1, condition2, ..., conditionN;

One column must be selected at least as minimum choice using the select statement. The semicolon (;), is used to terminate an SQL statement and separate it from other SQL statements.

The use of the semicolon (;)depends on the RDBMS that we are going to use and whether the RDBMS requires it or considers it as optional. For example, if we want to select all the records from the "Customers" table, the statement will be:

SELECT * FROM Customers;

The star * denotes that select statement will select "all" the records of the table.
After the execution of the statement above we will receive a list of its records as they are shown below.

If we want to choose only the last names and the cities of all customers, we write only these fields in the SELECT statement as below:

SELECT LastName, City FROM Customers;

The names of columns which follow the SELECT statement determine which columns will be selected.
The table name that follows the keyword FROM specifies the table from which the selected columns will return their contents.

From the SQL syntax above we will get a list of all the records (rows) of the table Customers with the two particular columns (attributes) as below.

You can use low case instead of capital letters to write SQL statements since SQL is not case sensitive and accepts both of them.
Moreover, there is no syntax restriction for SQL statements to continue in the same line or to be broken in more than one lines.

Thus, the previous syntax example could be written as follows.
select LastName, City from Customers;

Choose the way of syntax you want and follow it consistently.
In this book our syntax convention will be based on capital letters for SQL statements and separate lines for their components.


The DROP Statement

The DROP statement is used to delete a table from a database schema.
To delete a table including all of its rows, we use the DROP statement followed by the keyword table and the name of the table which is going to be deleted as in the following syntax:

DROP table "table_name";

You must be very careful when you are using the DROP statement which deletes the whole table as an entity (object) from the database schema.

On the other hand with the DELETE statement as we have seen previously we can delete all rows (records) of a table leaving its structure unchanged; that means all columns and constraint information will remain as they are.

In simple words the DROP statement removes the table definition as well as all of its rows from the database schema.

---