Fundamentals of SQL And Basics of DDL DML DCL Commands

SQL Basics

SQL Basics

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>

Students Table

Students Table

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

DDL

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

DML

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

DCL

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

TCL

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.


About onlinejt

Blogger

, ,

No comments yet.

Leave a Reply

*