A little lecture on SQL

(*This "little (10-minute)" lecture only means to provide basic understanding of the SQL (language). Do not use it on actual database system as SQL is very powerful : a simple SQL sentence can destroy all data in a database)

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common RDMS (relational database management system) that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

What is table?
A relational database system contains one or more objects called tables (it is like a spreadsheet in some way). The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns.

Here is a sample table called "weather". city, state, high, and low are the columns. The rows contain the data for this table:

Table_weather

city   state    high   low < --- column name
Phoenix   Arizona  105  90 < --- row of data , some called “record” or “transaction”
Tucson   Arizona  101  92
Flagstaff   Arizona  88  69
San Diego   California  77  60
Albuquerque   New Mexico  80  72

How to choose data from a table?
The “select” statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:

select city, state, high, low          < --- select which column to display
from Table_weather                   < --- from which table
where city LIKE 'Ariz%';         < --- condition

This SQL statement will match any city that start with 'Ariz'.

Conditional selections used in where clause:

= Equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
LIKE *See note below

* The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified.

How to create a table?
The “create table” statement is used to create a new table. Here is the format of a simple create table statement:

Example:

create table employee
(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));

The “varchar” followed the column name “first” is called a data type.

Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type.

Here are the most common Data types:

char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
varchar(size) Variable-length character string. Max size is specified in parenthesis.
number(size) Number value with a max number of column digits specified in parenthesis.
date Date value
number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.

Power of SQL database!
The magic of SQL database is that it only limits column's width in a table, but not column's length. How long can a table be? Imagine a SQL database table in a world-class bank containing the records of all cash-withdrawal transactions in the past 10 years!

Leave this paragraph to programmer!
What are constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table. What is relationship? In database design, it uses many tables. To search through many tables at once you need to "join" them (create a relationship among them). That's why we called these database systems "relational database system". A common example is the use of "part_no" in product description table and also sales table. So when it is called in invoice table the record contains data from both product description table and sales table.

How to insert a record into table?
The insert statement is used to insert or add a row of data into the table.

Example:

insert into employee
(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia');

In the example above, the column name first will match up with the value 'Luke', and the column name state will match up with the value 'Georgia'.

How to edit a record in table?
The update statement is used to update or change records that match a specified criteria.

Examples:

update phone_book
set area_code = 623
where prefix = 979;

How to delete a record in table?
The delete statement is used to delete records or rows from the table.

Examples:

delete from employee
where lastname = 'May';

Note: if you leave off the where clause, all records will be deleted!

How to delete a table?
The drop table command is used to delete a table and all rows in the table.

Example:

drop table sales_record;            < -- WARNING: only boss can use this command.
                                                          Imagine you drop a table containing a year full of sales records!