Structured Query Language - a primer (part 1)

SQL - Structured Query Language

Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

RDBMS - Relational Database Management System<

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.

Data is stored in tables … and tables are broken up into fields

The data in RDBMS is stored in database objects called tables.

A table is a collection of related data entries and it consists of columns and rows.

A field is a column in a table … allowing you to maintain specific information about every record in the table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

A record (row), is each individual entry that exists in a table … a record is a horizontal entity in a table.

Database Tables

A database most often contains one or more tables.

Each table is identified by a name (e.g. “Customers” or “Orders”).

Tables contain records (rows) with data.

SQL Statements

Most of the actions you need to perform on a database are done with SQL statements

Keep in mind that SQL keywords are NOT case sensitive: select is the same as SELECT

Some of the more important SQL commands:

SELECT Column

Selects the “CustomerName” and “City” columns from the “Customers” table

SELECT CustomerName, City
FROM Customers;

SELECT *

Selects all the columns from the “Customers” table

SELECT *
FROM Customers;

SELECT DISTINCT

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

So consider the following comparison:

Selects all (and duplicate) values from the “Country” column in the “Customers” table.

SELECT Country
FROM Customers;

Selects only the DISTINCT values from the “Country” column in the “Customers” table.

SELECT DISTINCT Country
FROM Customers;

The following SQL statement lists the number of different (distinct) customer countries:

SELECT COUNT (DISTINCT Country)
FROM Customers;

WHERE

The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For example:

• Selects all the customers from the country “Mexico”, in the “Customers” table.

  SELECT * FROM Customers
  WHERE Country = 'Mexico';
  

• Text fields versus Numeric fields

• Text fields → single quotes … Numeric fields → no quotes

  SELECT * FROM Customers
  WHERE CustomerID = 1;
  

LIKE

Note: the LIKE operator typically performs a search without the normal performance benefit of indexes. Using ’=’, ’<>’, etc.. instead will increase performance.

Users of the LIKE predicate should be aware that case sensitivity (e.g., ‘S’ versus ‘s’) may be different based upon database product or configuration.