SQL - Introduction
INFORMIX-SQL: Data access language used to communicate with the database manager.
SQL stands for Structured Query Language. It is a data access language used for communicating with a database manager.
A significant feature of SQL is that it can be used by two different interfaces. SQL is both an interactive query language and a database programming language. This means that any SQL statement that can be entered at a terminal can also be embedded in a program.
History
Initially developed by D.D. Chamberlain and others at IBM. The American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) have stated SQL standards.
Uses of SQL
The most important use is the SELECT statement. The SELECT statement allows you to look at data from a relational database, whereas INSERT, UPDATE, and DELETE allow you to manipulate data in the database. When you combine the SELECT statement with a manipulating statement, SQL allows you to change specific data.
The SQL Query
SELECT
The SELECT statement by itself can retrieve data from the database, and this action is commonly referred to as a query.
Example: Select all rows from a table. The * in the SELECT statement is a wildcard, which stands for all the columns in the specified table.
SELECT * FROM table1;
Example:: Specify certain columns from a table.
SELECT column1, column2 FROM table1;
Example: Sort the data retrieved in asceding or descending order.
SELECT * FROM table1 ORDER BY column1 desc|asc
Example: Sort multiple columns from a table.
SELECT column1, column2, column3 FROM table1 ORDER BY column2, column3
WHERE
The WHERE clause of a statement allows you to specify exactly which rows you would like to see. When using the WHERE clause, you can use the following operators: <, >, <=, >=, = !=
Example
SELECT column1, column2, column3
FROM table1
WHERE column1 = valueA
Modifying SQL Statements
The following statements modify data:
- DELETE
- INSERT
- UPDATE
Privilege SQL Statements
There are two levels of privileges: database level and table level.
Here are the database-level privileges:
- CONNECT enables you to open a database and run queries, as well as create and index temporary tables.
- RESOURCE enables you to create tables.
- DBA allows CONNECT and RESOURCE privileges, along with additional grant privileges.
Here are the table-level privileges:
- SELECT enables you to select rows from a table.
- DELETE enables you to delete rows.
- INSERT enables you to insert rows.
- UPDATE enables you to update specific rows.
Referential Integrity SQL Statements
SQL is also used for setting up the referential integrity on a database. Referential integrity is the relationship between tables. Each table must have a primary key. When the primary key is used in other tables, not as the primary key but as a reference point back to the table that uses that value as the primary key, it is called the foreign key.
Embedded SQL Statements
SQL can also be used with almost any programming language. Here are the options that are available when programming with SQL:
- SQL in SQL APIs
- SQL in Application Language
- Static SQL
- Dynamic SQL
