Structured Query Language (SQL) :SQL_Intro1.htm

SQL, the Structured Querying Language, is used to define, administer, and manage tables in the relational databases. SQL apprehends data at the logical level and  is standardized to send instructions to the database.

Each database system such as such as Oracle, DB2, PostgreSQL, Maria DB, Microsoft SQL Server, have their own SQL dialect. However, these vendors follow the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop"

PostgreSQL 9 is very similar to the SQL standard ANSI SQL: to 2008.  As a dialect, it includes extensions and restrictions to SQL, the SQL expressions of this workshop can be applied to other database systems not readily available.

SQL is a strongly typed language, differs from the programming languages like C/C++, C# or Java,  and does not distinguish between lowercase and uppercase letters. However, keywords are usually capitalized, data types and the table-name is supposed to be in small letters.

SQL in practice prepares some kind of statements to carry out the tasks, like querying a table, inserting or updating data in the table. It allows many languages like PHP, Java, C/C++, or .Net as as an interface.

SQL is divided into several parts: DDL Data Definition Language, the definition of database objects, such as is used Tables, views (views), triggers, indexes, constraints (constraints) and data types. DML, Data Manipulation Language, is used for inserting, changing and removing records. DCL, Data Control Language, the access control of relational databases is used. This includes the user and rights management, as well as the regular processing of database operations using transactions.
 
DDL : Data Definition Language (DDL) refers to the command types , which  are used to define the database structure or schema. Some examples :
  • CREATE - to create objects in the database
  • ALTER - alters the structure of the database
  • DROP - delete objects from the database
  • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT - add comments to the data dictionary
  • RENAME - rename an object
 
DML :Data Manipulation Language refers to the command type, which are are used for managing data within schema objects. Some examples:
  • SELECT - retrieve data from the a database
  • INSERT - insert data into a table
  • UPDATE - updates existing data within a table
  • DELETE - deletes all records from a table, the space for the records remain
  • MERGE - UPSERT operation (insert or update)
  • CALL - call a PL/SQL or Java subprogram
  • EXPLAIN PLAN - explain access path to data
  • LOCK TABLE - control concurrency
 
DCL : Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
 
 
TCL : Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
  • COMMIT - save work done
  • SAVEPOINT - identify a point in a transaction to which you can later roll back.
  • ROLLBACK - restore database to original since the last COMMIT.
  • SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
Lexical Convention:

SELECT employee_id as eid,first_name, last_name, email,phone_number as phone,hire_date,job_id, salary, commission_pct as comm, manager_id as mgr, department_id as d_id FROM employees

Below is a display of using PHP as an interface middle ware, to create a logical statement embedded in a HTML element.