This article was last updated on : October 27th, 2016 at 1:06 pm

SQL Databases Basics – DDL, DML, DQL

A Brief Overview of Databases

A database is a collection of some information that is stored over time in a structured format for retrieval at a later time. This is a vague definition, but by this definition, even a simple file system is an example of a database. This is reasonably accurate.

In practice, a system that allows for the storing of information and some capabilities to manage that data is referred to as a database. Thus, a database can be defined as a system that

  • Supports the creation and management of data using some data structure.
  • Supports a means of data entry into the data structure.
  • Supports a means for searching and retrieving the data.
  • Supports the manipulation of the data (editing, deleting, and so on).

What Is SQL?

Structured Query Language, or SQL, is the successor of an IBM product called SEQUEL, for Structured English Query Language. SQL is an ANSI (American National Standards Institute)  standard computer language that allows developers to access and manipulate a database system. It has been widely adopted as the language of choice to work with all the leading databases such as MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and Cloudscape. SQL includes  commands to create and manage databases. SQL as a language can be broken into several smaller components as explained in the next sections.

SQL Data Types :

SQL supports a variety of different data types that map to the different types of data that can be stored in the database.

These include data types such as binary, bit, char, datetime, float, integer, varchar, and several others.

Various Java data types, such as String, Long, Float, byte, short, and so on, are mapped to the SQL data types so that you can move data from SQL queries into Java objects. Every database has a slightly different mapping of Java data structures to SQL data types.

SQL Data Definition Language :

The Data Definition Language, or DDL, is used to create and modify a database.  DDL includes commands such as CREATE, ALTER, and DROP. The CREATE command is used to create a database and the definition of the tables and columns for each table. The ALTER command is used to alter the tables within a database. The DROP command is used to delete the database and all its contents.

Some examples of the various DDL commands are

CREATE DATABASE databaseName

This creates a database with the given name.

CREATE TABLE tableName ( columnName dataType,…)

This creates a table in the database with the given definition of columns and the data type for each column.

ALTER TABLE tableName ADD columnName dataType

This modifies a table definition to add a column.

ALTER TABLE tableName DROP columnName

This modifies a table definition to delete a column.

DROP DATABASE databaseName

This deletes the database.

DROP TABLE tableName

This deletes a particular table from the database.

SQL Data Manipulation Language :

The Data Manipulation Language, or DML, is used to manage the data stored in the database. This includes commands such as INSERT, UPDATE, and DELETE.

The INSERT statement is used to insert data into a table, one record at a time.
The UPDATE command is used to modify the contents of the columns within a single row of a
specific database table. The DELETE command is used to delete specific rows from a table in the database.

Some examples of DML statements are

INSERT INTO tableName (column1, column2,…) VALUES (value1, value2,….)

This inserts values into a particular table.

UPDATE tableName SET column1 = new WHERE column1 = old

This modifies the value of a column within a table given a specific condition.

DELETE FROM tableName WHERE column1 = value

This deletes a row from a table, given a specific condition

SQL Data Query Language :

After you have created the database and added some data to it, you will definitely want the ability to retrieve that data. This is what the Data Query Language allows you to do. It includes the basic SELECT command that allows you to select rows from a table in the database. The Data Query Language also includes functionality such as the WHERE clause that allows you to specify search
criteria to search the data in the database.

Some examples of the Data Query Language are

SELECT * FROM tableName

This selects all rows from a table

SELECT column1… FROM tableName WHERE column2 = value

This selects specific columns from a table given a specific condition.

SELECT column1… FROM tableName WHERE column1 = value AND column2 = otherValue

This is similar to the previous statement, only with a more complex conditional statement.

Learn More About SQL :

graphics/bytheway_icon.gif To learn more about SQL and the syntax and usage of these commands,
you can go to http://www.w3schools.com/sql/default.asp. You can also read about it in a book such
as Sams Teach Yourself SQL in 24 Hours, 3rd edition, by Stephens and Plew (ISBN: 0672324423).

 

Share this post:

Recent Posts

Leave a Comment