Structured Query Language (SQL)

SQL, the Structured Querying Language, is used to define, administer, and manage relational databases. These are usually provided by relational database systems. The database language SQL is standardized. PostgreSQL 9 is very similar to the SQL standard ANSI SQL: to 2008. Each database system such as such as Oracle, DB2, PostgreSQL, Maria DB, MSSQLServer uses its own SQL dialect. As a dialect 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 and does not distinguish between lowercase and uppercase letters. However, keywords are usually capitalized, data types and table names is small. SQL is according to their tasks 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