Database and SQL Tutorial Series

Intro to SQL

SQL and SQL Standardization

What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases.  SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?   SQL can:

• execute queries against a database
• retrieve data from a database
• insert records in a database
• update records in a database
• delete records from a database
• create new databases
• create new tables in a database
• create stored procedures in a database
• create views in a database
• set permissions on tables, procedures, and views

SQL is a Standard - BUT.... Although SQL is an ANSI/ISO standard, there are different versions of the SQL language. However, they all DBMSs support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, CREATE) in a similar manner.  It should also be noted that most of the DBMSs also have their own proprietary extensions in addition to the SQL standard!  This course uses Oracle as it's DBMS, but will try to remain agnostic with respect to the teaching and learning of the SQL language to maximize the learner's ability to apply their learned knwoledge to a different DBMS in the future.

Sub-Languages of SQL

There are several sub-categories of SQL that play specific roles in the querying and manipulation of databases.

DML	Data Manipulation Language	This set of SQL statements are used to query or change the data stored in the database.  DML common statements include SELECT, INSERT, UPDATE, and DELETE.  These commands generally only impact rows of data in existing tables.  These commands are about "What" data is stored in the database.
DDL	Data Definition Language	This set of SQL statements are used to create or modify the structure of the database.  "How" is data stored in the database?  These commands generally only impact tables and columns and other stored objects in the database.  The main statements included in DDL are CREATE, CREATE OR REPLACE, and ALTER.
TCL	Transaction Control Language	TCL is a subset of statements that assist in controlling one or more SQL DML statements that need to either completely succeed or entirely fail.  A transaction must be complete or not happen at all.  Common commands include BEGIN, COMMIT, SAVEPOINT and ROLLBACK
PL/SQL	Procedural Language extensions to SQL	PL/SQL is an advanced compiled procedural language extension to SQL that allows for complex processes to be performed on data or databases.  PL/SQL contains many of the operations that we would find in most programming languages, such as loops, functions, variables, conditional statements, etc.  This topic is not covered until DBS501 or DBS511.