Learn SQL Database Basics – DDL, DML, DQL with few examples

A Brief Overview of Database

Database means a collection of some information stored over time. Moreover, stored in a structured format for retrieval at a later time.

In fact, 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 short, a system that allows for the storing of information and some capabilities to manage that data referred as a database. Moreover, a database is a system which supports:

• To create and manage data using some data structure.
• Which is a means of data entry into the data structure.
• Software to store, search and retrieve the data.
• Perform manipulation of the data (creating, updating, editing, deleting, and so on).

What Is SQL?

Structured Query Language, in short SQL, is the successor of an IBM product SEQUEL. SEQUEL stands for Structured English Query Language.

Moreover, SQL is an ANSI (American National Standards Institute)  standard computer language that allows developers to access and manipulate a database system. 

All leading databases widely adopted SQL as the language of choice to work with. For example, MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and Cloudscape.

SQL includes  commands to create and manage databases. So, as a language we can break SQL into several smaller components. Further, we will explain in the next sections.

SQL Data Types :

SQL supports a variety of different data types. It maps to the different types of data and stores 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, adopts the SQL data types. It makes data processing smoother. So, you can move data from SQL queries into Java objects.

However, 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, uses to create and modify a database.  DDL includes commands such as CREATE, ALTER, and DROP.

CREATE command creates a database and the definition of the tables and columns for each table. ALTER command alter/change the tables within a database. DROP command uses to delete the database and all its contents.

Further please find some examples of the various DDL commands given below.

CREATE DATABASE <your database name here>

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, manages the data stored in the database. For example, this includes commands such as INSERT, UPDATE, and DELETE.

The INSERT statement to insert data into a table, one record at a time.

The UPDATE command to modify the contents of the columns within a single row of a
specific database table.

The DELETE command to delete specific rows from a table in the database.

Further, please find some examples of DML statements given below.

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 :

Once you have created the database and added some data to it, you will definitely want the ability to retrieve that data. In fact, This is what the Data Query Language allows you to do so.

Basically, It includes the basic SELECT command that allows you to select rows from a table in the database. The Data Query Language includes functionality such as the WHERE clause. So that you can specify search or data filtering criteria to search the data in the database.

Further, please find some examples of the Data Query Language.

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, but with a more complex conditional statement.

Learn More About SQL :

In order to learn more about SQL syntaxes and command usage, please refer these websites

• http://www.w3schools.com/sql/default.asp.
• Moreover, you can also read Sams Teach Yourself SQL in 24 Hours, 3rd edition, by Stephens and Plew (ISBN: 0672324423).

Finally, hope you enjoyed the article and learnt few basics about DDL, DML and DQL in SQL.