INTRODUCTION TO SQL

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.
STUDENT_ID STUDENT_NM COURSE_ id PHONE ADDRESS
1001 james Oracle 972-888-9018 888,NorthCentralExp,Dallas,TX- 75089
1002 JIM MSSql Server 972-678-8909 567, Preston Road, Dallas, TX - 75240
1003
BRUCE
Java
214-571-1567
1234, Elm Street, Dallas, TX - 75039
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_IDFROM STUDENTWHERE STUDENT_NM = 'JIM'
You will get the result as:
COURSE_ID
MSSql Server
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, ADDRESSFROM STUENTWHERE COURSE_ID = 'Oracle'
The result will be
STUDENT_NM
ADDRESS
JAMES
888, North Central Exp, Dallas, TX- 75089
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:
STUDENT_ID
STUDENT_NM
COURSE_ID
PHONE
ADDRESS
1001
JAMES
Oracle
972-888-9018
888, North Central Exp, Dallas, TX- 75089
1002
JIM
MSSql Server
972-678-8909
567, Preston Road, Dallas, TX - 75240
1003
BRUCE
Java
214-571-1567
1234, Elm Street, Dallas, TX - 75039
1004
DAVE
Java
972-912-4008
567, Washington Ave, Dallas - 75543
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
STUDENT_ID
STUDENT_NM
COURSE_ID
PHONE
ADDRESS
1004
DAVE
Java
972-912-4008
146, Dallas Parkway, Dallas - 75240
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 STUDENTWHERE STUDENT_NM = 'JAMES'
once you delete the record and you select all the information from the student table you should see the following information:
STUDENT_ID
STUDENT_NM
COURSE_ID
PHONE
ADDRESS
1002
JIM
MSSql Server
972-678-8909
567, Preston Road, Dallas, TX - 75240
1003
BRUCE
Java
214-571-1567
1234, Elm Street, Dallas, TX - 75039
1004
DAVE
Java
972-912-4008
567, Washington Ave, Dallas - 75543
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 DAVIDIf 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 JAMESIf JAMES wants to provide only Select and Insert on ITEMS to DAVID then he can issue: GRANT SELECT, INSERT ON ITEMS TO JAMESIf JAMES wants to provide all the operations on ITEMS to DAVID then he can issue: GRANT ALL ON ITEMS TO JAMESOnce you provide all permissions on an object to an user then indirectly he becomes the owner and can do any manipulation to the table.