Introduction to Structured Query Language (SQL)

SQL is structured Query Language which is a computer language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase and SQL Server uses SQL as standard database language. SQL language has several parts

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Integrity
4. View defination
5. Transaction control
6. Embedded SQL & dynamic SQL
7. Authorization
8. Data Control Language (DCL)

Domain Type (Data type):

SQL supports a variety of built in data types including:
a) Char(n): A fixed length character string with user defined length.
b) Varchar(n): Variable length character string with user defined maximum length.
c) Int: An integer
d) smallint: a small integer.
e) Float(n): A floating point number with precision of at least n digit.
f) Date: Date containing a year, month and day of the month.
g) Time: The time of day, in hrs, minutes and seconds
h) Timestamp: A combination of date and time

1. Data Definition Language (DDL)

It allows you to create, alter or remove different kind database objects such as tables, views, indexes. Some of the DDL commands are as follows:
a) Create database command: creates a new database
For example:
create database DCEIII
b) Drop database command: delete a database
For example:
drop database DCEIII 
c) Create table command : creates a new table
For example:
create table student
(
sn int not null,
fname varchar(30),
lname varchar(30),
address varchar(30),
phone varchar(30),
)
d) Drop table command: deletes a table
For example:
drop table stud 
e) Alter table command: used to add/drop/modify attribute to an existing table.
For example:
alter table student
add remark varchar(30);

2. Data Manipulation Language (DML)

SQL DML allows you to query and change data from existing database object. Some of the DML commands are as follows: 
INSERT INTO - inserts new data into a database
For example:
insert into student(sn,fname,lname,address,phone,remark)
values(1,'Resham','Giri','Dang',9847836990,'teacher') 
SELECT statement :
The SELECT statement is used to select data from a database.
For example:
SELECT fname,phone
FROM student
and
select * from student 
UPDATE - updates records in a database
update student set
address='Pyuthan' where sn=1 
DELETE - deletes records from a database
delete from student
where sn=2

3. Data Control Language (DCL)

It is used to control the kind of data access to the database and transaction control.
Grant, revoke are used for data access control.
Commit, rollback and save point are transaction control command.

Grouping by clause

Group by clause is used to forms groups. Tuples (rows) with the same value on the entire attribute (column) in the group by clause are placed in one group.

For example:

select department,sum(sal)as totalsalary

from empsalary

group by department

Aggregate functions are used to summarize the results of an expression for a group of records to return a single value for that group. Aggregate functions are often used with GROUP BY clause of the SELECT statement. The standard aggregating functions are:

a) Count(): is used to count the number of values in the column.

b) Avg(): is a average function, which calculates the average of values in specified column.

c) Max(): returns maximum of the set of values.

d) Min(): returns minimum of the set of values.

e) Sum(): calculates sum of the value in a set of numbers.

For example:

SELECT MIN(sal), MAX(sal), AVG(sal),count(sal),sum(sal)

from empsalary

SQL Constraints:

Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.

Contraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL:

· NOT NULL Constraint: Ensures that a column cannot have NULL value.

· DEFAULT Constraint: Provides a default value for a column when none is specified.

· UNIQUE Constraint: Ensures that all values in a column are different.

· PRIMARY Key: Uniquely identified each rows/records in a database table.

· FOREIGN Key: Uniquely identified a rows/records in any another database table.

· CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

· INDEX: Use to create and retrieve data from the database very quickly.

Data Integrity:

The following categories of the data integrity exist with each RDBMS:

· Entity Integrity: There are no duplicate rows in a table.

· Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.

· Referential integrity: Rows cannot be deleted, which are used by other records.

· User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain, or referential integrity.

Basic Structure of an SQL

The basic structure of an SQL consists of three clauses: select, from and where.

 Select: It is used to list the attributes desired in the result of query.

 From: It lists the relations to be scanned in the evaluation of expression.

Where: It consists of a predicate involving attributes of the relation that appear in the from clause.

 A typical SQL query has the form:

select A1, A2,…, An

from r1, r2,…, rm

where P;

Benefits of SQL

1. It is simple, flexible and powerful.

1. It can process more than one record at a time.

1. We can do various operations using it, like querying data, creating and updating tables and inserting and deleting rows

1. Program written is SQL are portable. They can be easily transferred from one database system to another.

1. · All popular RDBMS support SQL.