In this tutorial, l’m going to quickly go over the languages that exist in SQL.
SQL stands for Structured Query Language. It is made up of three components, Data Definition Language(DDL), Data Manipulation Language(DML) and Data Control Language(DCL). Let’s take a look at each of these languages and the general statements that fall under it and the syntax for those statements.
DDL: The data definition languages are those languages used for creating objects that will persist permanently in the database. That means, objects that will be stored, either temporary or permanent in the database. The sql statements that fall under DDL are;
CREATE: For creating objects. There are different types of objects that can be created and stored in the database, either permanently or temporary. An example of an object is a table. A table is a two dimensional structure which stores data. A table has columns and rows. These is the create statement for creating the table object.
CREATE TABLE table_name(column1 datatype, column2 datatype,–).
Data types have the same meaning as data type in other programming languages. There are common data types to the different flavours of SQL but there are also some specific data types to different flavours of SQL. Examples of data types are int for integer, char for storing characters, varchar for storing characters too. It’s good to read on the different data types and know when to use what. For example, char and varchar are used for storing character strings but char stores spaces with the string if it’s not up to the length specified but varchar doesn’t store space with the character string. For both char and varchar, it is required to state the maximum length of characters expected to be stored. If you specify a length and entered something more than that length, both varchar and char will cut off the string to the maximum length specified during declaration.
So let’s create a table to store information about students that store all their name information and an id for them
CREATE TABLE Student(id int, firstname varchar(50), lastname varchar(50))
if l want to make the id a primary key, which l discussed in the earlier tutorial, then l’ll specify it as follows
CREATE TABLE Student(id int primary key, firstname varchar(50), lastname varchar(50))
Ok, next DDL statement is Drop.
DROP: This is for dropping an object completely from the database. Using the drop statement drops both the object and all containing data in it.The drop statement goes like this, DROP
Next DDL statement is ALTER. Alter is for changing the the structure of an object that is already created and stored in the database. For example, lets say we’ve created our student table and stored it in the database but all of a sudden, we realise we need a new field like middle name in the table. we can use the alter statement to add that column, you can also use the alter statement to remove a column from the table. The syntax for adding a new column in an existing table looks like this,
ALTER TABLE tablename
add column columnname datatype
The Syntax for removing a column from a table is like this, ALTER TABLE tablename, DROP COLUMN columnname.
If you want to change the data type for a column in the table, you can use the alter statement like this,
ALTER TABLE tablename
ALTER COLUMN existingcolumnname new datatype
In general, those are the standard DDL statements. Truncate is another DDL statement but it’s not as popular as the ones l mentioned. Truncate deletes data from the object and reset identity columns if one exists in the object.
Now let’s go on to the DML component of SQL.
DML: The data manipulation language is used for adding, updating and removing records or data from/into an object in the database. They have nothing to do with the structure of the object, they simply add new records or change existing records in the object. The different DML statements are as follows;
Insert: This is for adding a new record to the table.
INSERT INTO tablename(column1,column2)
values(V1,v2,—)
The next DML statement is Update, this is for changing a value in the database.
UPDATE tablename
SET colulmn = value
This will change the value in the specified column for every record in the table.
if you want to update the value only in some specified record, then you have to use the WHERE clause to specify the condition to meet for update.
For example,
Update student
set firstname =’2smart4school’
Where id = 1
I think that’s clear enough.
The next statement is SELECT. This is not actually a DML statement but only for reading data.
To see all the students in the statement table, we’ll write a query like this,
Select * from student.
if you want to select a particular student, you can use the WHERE clause like this,
SELECT * from student
where id = 1.
* will return all the column fields in the table, if you want only some columns to show up in your resultset, then you have to specify those columns in the select statement. example select firstname, lastname from student.
The last Component of SQL is the DCL. Data control language is mainly for access control over data. To give permission or restrict access to some data. The main parts of DCL is Revoke and Grant. Revoke will deny access and Grant gives access.
This is the end of general discussion. In my next tutorial, l’ll start discussing a particular flavour of SQL,that is TSQL, the microsoft version of SQL and I’ll be using Microsoft SQL Server 2008 R2 for my demonstration. See you in the next tutorial.
