Structured Query Language

SQL is a database language used to create, manipulate and control the access to the Database objects. SQL is a non procedural language used to access relational databases. It is a flexible, efficient language with features designed to manipulate and examine relational data.

SQL is only used for definition and manipulation of database objects. It cannot be used for application development like form definitions, creation of procedures etc...For that you need to necessarily have some 3gl languages such as cobol or 4gl languages such as Dbase to provide front-end support to the database.

Key features of SQL are:

• Non procedural language
• Unified Language
• Common language for all Relational databases. ( Syntax may change between different RDBMS )

SQL is made of Three sub-languages such as:

• Data Definition language (DDL)
• Data Manipulation language (DML)
• Data control language (DCL)

Data Definition Language (DDL): allows you to define database objects at the conceptual level. It consists of commands to create objects and alter the structure of objects, such as tables, views, indexes etc.. Commonly used DDL statements are CREATE, DROP etc..

If you want to create a table Student,then use the following syntax

CREATE TABLE STUDENT
( STUDENT_ID INTEGER PRIMARY KEY,
STUDENT_NM VARCHAR(30),
COURSE_ID VARCHAR(15) ,
PHONE VARCHAR(10) ,
ADDRESS VARCHAR(50) );

To drop a table from the database

DROP TABLE STUDENT;

Data Manipulation language(DML): Allows you to retrieve or update data within a database. It is used for query, insertion, deletion and updating of information stored in databases. Eg: Select, Insert, Update, Delete.

Select statement:
Select statement in SQL language is used to display certain data from the table.For example:- if you want to know what course Jim is taking; Select statement fetches you the information you want,when you use the information you have. So,in the above scenario the information you have is student_nm as Jim and and the information you want is course_id, the intersection of those two columns in that table is what you are looking for.

SELECT (what you want)
FROM (which tables)
WHERE (what you have )

Now the select statement to know the course_id Jim looks like this:

SELECT COURSE_ID
FROM STUDENT
WHERE STUDENT_NM = 'JIM'

You will get the result as:

If you want to see all the rows in the table then your select will be:

SELECT * FROM STUDENT;

If you would like to show student_nm and address who is attending Oracle course in the form of a report then your select will look like:

SELECT STUDENT_NM, ADDRESS
FROM STUENT
WHERE COURSE_ID = 'Oracle'

The result will be

Insert Statement

Insert statement is used to insert a new row into the table. For example:- If a new student DAVE is joining Java course then,use the INSERT SQL statement.

INSERT INTO STUDENT (STUDENT_ID, STUDENT_NM, COURSE_ID,PHONE, ADDRESS ) VALUES
(1004, 'DAVE', 'Java','972-912-4008', '567, Washington Ave, Dallas - 75543' )

after executing the insert statement,your table should look like below when you issue a select from student table:

Update Statement

is used to change the existing information in the table.For example:-If DAVE moved to another address then we need to change the ADDRESS column for DAVE's record.If the new address is 146, Dallas Parkway, Dallas - 75240 then your update should be:

UPDATE STUDENT SET ADDRESS = '146, Dallas Parkway, Dallas - 75240'

WHERE STUDENT_NM = 'DAVE'

In order to make sure you updated the Address column for DAVE issue following SQL

SELECT * FROM STUDENT WHERE STUDENT_NM = 'DAVE'

then you should see the following result

Delete Statement

is used to delete a row from the table ie remove records from the table.For example:JAMES moved to different city, and he does not want to take the course.In order to remove JAMES's record from the table we use the DELETE statement

DELETE STUDENT
WHERE STUDENT_NM = 'JAMES'

once you delete the record and you select all the information from the student table you should see the following information:

If you dont include where clause in delete statment then it will remove all the rows from the table.

Data control language(DCL)
In RDBMS one of the main advantages is the security for the data in the database. You can allow some user to do a specific operation or all operations on certain objects. Examples for DCL statements are GRANT, REVOKE statements.

GRANT is used to Grant a permission to an user so that the user can do that operation.
REVOKE is used to take back that permission from that user on that object.

For example we have two users JAMES and DAVID
If JAMES created a table called ITEMS then JAMES becomes the owner of that table.
DAVID cannot access ITEMS table because he is not the owner of that table.
DAVID can access ITEMS if JAMES gives the permission on his table.
JAMES can give different types of access like Select, Update, Delete and Insert
on ITEMS table to DAVID.
For example:-
If JAMES wants to provide only Select on ITEMS to DAVID then he can issue:
GRANT SELECT ON ITEMS TO JAMES

If JAMES wants to provide only Select and Insert on ITEMS to DAVID then he can issue: GRANT SELECT, INSERT ON ITEMS TO JAMES

If JAMES wants to provide all the operations on ITEMS to DAVID then he can issue:
GRANT ALL ON ITEMS TO JAMES

Once you provide all permissions on an object to an user then indirectly he becomes the owner and can do any manipulation to the table.