Fundamentals of SQL And Basics of DDL DML DCL Commands

SQL (Structured Query Language) is database computer language used to manipulate the data in a relational database management systems (RDBMS). A relational database is composed of tables similar to what is displayed in <image1>.  A relation database could have one table or hundreds of tables.  Did you ever wonder how many tables Facebook could have? A table contains columns which identifies the entity. In this case we have a table called Students (entity) and we have column-names to gather specific information about the student such as LastName, FirstName, State and so on.

<image1>

The standard SQL language is “standard” across multiple databases types such MySQL, Oracle, Microsoft SQL, and others.  However, each database software (MySQL, Oracle, Microsoft SQL) offers or bundle their own flavor to the “standard” SQL language.  Some of them offers special function or unique features that will not exist other databases software.

Each database software have their out tools for accessing their databases objects. In MySQL we have the ” MySQL GUI Tools” plus other external tools from different companies. In Oracle we have “Oracle Enterprise Manager” plus some other very user friendly provides by other companies. In Microsoft SQL we have all in one “SQL Server Management Studio” and there are some other third party tools but the one that comes with database does a very good job.

The SQL language falls in three basic categories. Now, some databases may have more additions to its categories.  There is the DDL (Data Definition Language), the DML (Data Manipulation Language), and DCL (Data Control Language).  I will explain briefly those categories.

DDL (Data Definition Language)

A Data Definition Language or Data Description Language (DDL) is a SQL language for defining the structure(s) of database object(s) such as tables, table views, triggers, store procedures, and so on.

CREATE	It used to create objects in the database such as CREATE VIEW, CREATE TABLE, CREATE TRIGGER, and so on Example:  CREATE TABLE students ( StudentID INT, FirstName VARCHAR(100));
ALTER	It used to change the structure of the database object such as adding column to a table, or changing the view Example:  ALTER TABLE Students  ADD LastName varchar(50);
DROP	It is used to delete an objects from a database … E.g.: DROP TABLE [table-name] or views or trigger Example: DROP TABLE Students ;
TRUNCATE	It is used to remove all records from a table, including all spaces allocated for the records are removed. Example:  TRUNCATE TABLE Students;

DML (Data Manipulation Language)

Data Manipulation Language (DML) is the most common language used to manipulate the data in a database. Every technical personnel should be familiar with these basic four commands. Most computer programs and/or database users uses to insert, delete and update data in a database.

SELECT	The command that most people will used. It is used to retrieve data from a database table or table view. Example: SELECT LastName, FirstName, State FROM Students;
INSERT	Insert new data into a database table. Example:  INSERT INTO Students (StudentID, Lastname, FirstName, State, Zip) VALUES (162, ‘Juan’, ‘Smith’, ‘NY’, 12550);
UPDATE	If you want to make changes to a data that is been already stored in the database (updates = changes). Example: UPDATE Students SET LastName = 'Smith' WHERE StudentID = 155;
DELETE	Delete data from a table. However, it will not delete the space allocated from that database. Use TRUNCATE to remove space allocated. Example: DELETE FROM Students WHERE StudentID = 155;

DCL (Data Control Language )

A Data Control Language (DCL) is a computer language used to control access or privileges to data in a database or database objects with a database.

GRANT	It used to give access rights or privileges to database objects (tables, view, schemas, and so on) Example: GRANT SELECT, INSERT, UPDATE, DELETE ON Students to JSmith;
REVOKE	Remove rights or privileges give via the GRANT command. Example: REVOKE DELETE ON Students FROM JSmith;

TCL – Transaction Control Language

A Transaction Control Language (TCL) is a computer language used to manage changes made by DML

COMMIT	It is used to save the changes or commit the changes to the database. Example: BEGIN TRANSACTION; DELETE FROM Students WHERE StudentID = 155; COMMIT TRANSACTION;
ROLLBACK	Restore the changes to its original state since the last COMMIT. Used in large transactions.  In the following example I did not delete anything because at the end I rollback the transaction. Example: BEGIN TRANSACTION; DELETE FROM Students WHERE StudentID = 155; ROLLBACK TRANSACTION;

Now, these are just simple basic SQL commands that will be standard across multiple database types.  Keep in mind that every database software (Oracle, MySQL, Microsoft SQL, Pervasive) have their own additions to basic language.