Introduction
SQL is pronounced as Sequel and
is an abbreviation for structured Query Language. It was defined by Donald Chamberlin and
others at the IBM Laboratory in 1974.
SQL does not use variables and
control of flow statements hence it is often used as a database Sub-Language
that can be added to a program written in a Host Programming Language which is
called as Embedded SQL. Following is the
list of relational databases that supports SQL:-
Ø
SQL Server
Ø
Oracle
Ø
Access
Ø
Ingress
Ø
Sybase
Ø
Informix
SQL is used to query the database
and gets results of the Query from the database. The four major SQL statements are:- SELECT,
INSERT, UPDATE AND DELETE.
Retrieving data
Select statement is the most
common statement and is used to retrieve rows of data from a table. When select statement is executed, database
searches specified table extracts chose columns and select rows that meet a
condition. To select all the fields of a
table an
astrick(*) should be used. For example:
à Select * from TitleAuthor
Book_No Author_ID
0-001417-7-5 A001
0-001413-8-8 A002
0-011129-1-6 A004
(TitleAuthor
Table)
To select only Author_ID from
TitleAuthor table:-
à select Author_ID from TitleAuthor
Author_ID
A001
A002
A003
A004
(TitleAuthor
Table)
To omit duplicate columns
DISTINCT clause can be used:-
à select DISTINCT Author_ID from TitleAuthor
To sort records of the table
Order by Clause can be used with select statement:-
à Select * from Author
Order
by Author_Name
Author_ID Author_Name Phone_No
A003 Sam Adams (206)555-6874
A004 Ved Prakash NULL
A002 Anthoney Desouza (61)345-1247
A005 Ritchi Waugh (265)234-1233
(Author
Table)
Tables can be sorted with
Ascending or Descending options as follows:-
à Select * from Publisher
Order
by Pub_ID Desc
Pub_ID Pub_Name City
908 Barem Books London
456 AGD Pub Berlin
326 Peter Pub Portland
41 Prentice
Hall Calcutta
(Publisher
Table)
To find out Author_Name
‘Ved Prakash’:-
à Select
Author_ID from Author
Where
Author_Name =’Ved Prakash’
Author_ID
A004
The Order by clause becomes
relevant if we are looking more than one Author.
à Select Author_ID, Author_Name
From
Author where Author_Name=
“Sam
Adams” or Author_Name = “Ved Prakash”
Order
by Author_Name
Author_Id Author_Name
A002 Sam Adams
A004 Ved Prakash
With IN operator the query can be
written to enable the value of an expression is equal to any of the several
values in a list values should be separated by comma.
à Select Author_ID, Author_Name
from
Author where Author_Name IN
(“Sam
Adams”, “Ved Prakash”)
order
by Author_Name
Besides IN keyword following
keywords can be used with where clause:-
Ø
IN à Checks value in a Range.
Ø
NOT IN à Checks for values not in a Range.
Ø
LIKE à Checks values that like a specified
value.
Ø
NOT LIKE à Checks values that not like a specified
value.
Ø
IS NULL à Checks for a NULL value.
Ø
IS NOT NULL à Checks for values that are not NULL.
Ø
AND à Checks for multiple conditions.
Ø
OR à Checks fro either of he two conditions.
Ø
BETWEEN à Checks values between a set of values.
Ø
NOT BETWEEN à Checks for values not between a set of
value.
Data can be retrieved with like
operator. Wild card characters used with
like keyword are:-
- For
only one character.
[ ] Any single character within a specified Range.
[^] Any single character Not within specified Range.
% Any
Value.
à Select * From Author
Where
Author_Name LIKE “S_ _ _”
Author_ID Author_Name Phone_No
A002 Sam
Adams (206)555-6874
à Select * from Title
Where
Title Like “V%”
Title Year
Pub_ID Book_No
Visual
Basic 6.0 1998 326 0-0014370
à Select * from Title
Where
Title like “[^V]%”
à Select Author_ID, Author_Name
From
Author Where Phone_No is NULL
à Select * from Title
Where
Year between 1980 and 1999
Inserting data
Without any data we can not
retrieve, update, and delete data. Data
can be inserted into a table with INSERT statement.
Syntax:
INSERT into (Table Name)
Values (Value1, Value2, Value3,
ValueN . . . .)
à Insert into Author
values
(“A006”, “Arti Prabhu”, “(022)-543-8818”)
Deleting data
With DELETE statement we can
delete rows from a table. DELETE has to
be used with care as once the statement is executed there is no confirmation
and rows get deleted. After records are
deleted we can not undo the operation.
Syntax:
DELETE from (Table Name)
[Where Condition]
à Delete * from Title
Above command will delete all the
rows from the database table.
à Delete from Title
Where
Pub_Id = 41
Above command will delete a row
where Pub_Id is 41.
Updating data
With UPDATE statement data can be
updated or modified.
Syntax:
Update (Table Name)
Set Value_list [Where Condition]
à Update Publisher
Above command will update all the
rows in column City
to Bombay
à Update Publisher
Where
Pub_Name = “AGD Pub”
Referential Integrity
It is a rule on relational
databases, used to ensure that relationships between records in related tables
are valid. Referential integrity is
enforced when all of the following conditions are met:-
- We can not enter a value in Foreign Key field of
related table that does not exist in primary key of Master table.
- A record can not be deleted from Master table if
matching records exist in related table.
- We can not change a Primary Key value in Master
table, if that record has related records.
Cursors
Cursors enables an application to
use rows in the result set in a manner similar to using records in a sequential
file. With the help of cursors we can
provide a mechanism to applications by mapping one row or a small block of rows
from the result set into program variables.
We can define a cursor as a
result set where browsing is enabled and current position of a record is known.
Types of cursors
The cursors are of four types and
they are as follows:
1)
Dynamic
Cursors
Dynamic cursors
detect all additions, changes and deletions made to rows in the result set,
regardless of whether changes occur from inside the cursor or by other users
outside the cursor.
It can be used
in multi-user environment where each user needs to immediately view the changes
made to a database by other users.
Dynamic cursors require more memory and network traffic when compared to
keyset cursors. Dynamic cursors provide
forward and backward scrolling.
2)
KeySet
Cursors
Keyset cursors
are controlled by a set of unique identifiers (keys) known as keyset. Keys are a set of columns of result set that
uniquely identify the rows in result set.
Keyset cursors
behave like dynamic cursors but we can not view records that other users add,
and prevents access to records that other users delete. But we can still view the changes made to the
data by other users.
It is used in
multi-user environment when changes made to database by users have little or no
effect on each other. It allows forward
and backward scrolling through the result set.
3)
Static
Cursors
It always
displays result as it was when cursor was first opened. They provide forward and backward
scrolling.
It do not
detect updates, deletes and inserts made by other users. These cursors are of limited use in data
entry programs.
4)
Forward-Only
Cursors
It is default
cursor type. It enables to only forward
through the result set. It does not
support a forward or backward scrolling in result set, it only supports
fetching the rows serially from start to end of the result set.
If an
application does not require scrolling through the result, the forward only
cursor is the best way for retrieving data quickly.
Top
Practical Session 3
Example 1 (Creating Program
With ActiveX Objects)
Open project MDICourse_prj. Add a new form to the above project. Also add Sub Menu Option Find Course to Mdi
Menu. Design the Connection_frm
interface as follows:-
|
Object
|
Property
|
Setting
|
|
|
|
|
|
Form
|
Name
|
Connection_frm
|
|
|
Caption
|
Course Details
|
|
|
MDI Child
|
True
|
|
Label1
|
Caption
|
Course Id
|
|
Label2
|
Caption
|
Course Title
|
|
Label3
|
Caption
|
Hours
|
|
Text1
|
Name
|
txtId
|
|
Text2
|
Name
|
txTitle
|
|
Text3
|
Name
|
txtHours
|
|
Text4
|
Name
|
txtFind
|
|
Command1
|
Name
|
cmdFirst
|
|
|
Caption
|
<<
|
|
Command2
|
Name
|
cmdPrevious
|
|
|
Caption
|
<
|
|
Command3
|
Name
|
cmdNext
|
|
|
Caption
|
>
|
|
Command4
|
Name
|
cmdLast
|
|
|
Caption
|
>>
|
|
Command5
|
Name
|
cmdAdd
|
|
|
Caption
|
&Add
|
|
Command6
|
Name
|
cmdCancel
|
|
|
Caption
|
&Cancel
|
|
Command7
|
Name
|
cmdDelete
|
|
|
Caption
|
&Delete
|
|
Command8
|
Name
|
cmdSave
|
|
|
Caption
|
&Save
|
|
Command9
|
Name
|
cmdExit
|
|
|
Caption
|
E&xit
|
|
Command10
|
Name
|
cmdFind
|
|
|
Caption
|
&Find
|
|
Frame1
|
Caption
|
Navigation Buttons
|
|
Frame2
|
Caption
|
Action Buttons
|
|
Frame3
|
Caption
|
Search Course ID
|
Save the Connection Form. Save the project. Select menu ProjectàReferencesàMicrosoft
Data Binding Collection. The form should
look like as follows.
Add following lines
of Code to Declaration Section
Dim Cnn1 As ADODB.Connection
Dim Rscourse As ADODB.Recordset
Dim addFlag As Boolean
To open a connection
and Recordset to retrieve records, add following code to Load event of the
form:-
Private Sub Form_Load( )
Set Cnn1 = New ADODB.Connection
Cnn1.Provider = "Microsoft.Jet.OLEDB.4.0"
Cnn1.Open "E:\Sandhya\MyCourse.mdb"
Set Rscourse = New ADODB.Recordset
Rscourse.Open "course", Cnn1, adOpenDynamic,
adLockOptimistic, adCmdTable
addFlag = False
End Sub
Create private
procedure Display and write the following Code:-
Private Sub Display( )
txtId.Text = Rscourse!CourseId
txtTitle.Text = Rscourse!CourseTitle
txtHours.Text = Val(Rscourse!Hours)
End Sub
Write the following
code in to Form’s Activate Event
Private Sub Form_Activate( )
Display
End Sub
Private Sub Form_Unload(Cancel As Integer)
Cnn1.Close
End Sub
Open the MDI Formà Open Click Event of
mnuFindCourse and write following code lines:
Private Sub mnuCourseFind_Click( )
Connection_frm.show
End Sub
Write following
individual Codes for other buttons also:-
Private Sub cmdAdd_Click( )
txtId.Text = ""
txtTitle.Text = ""
txtHours = ""
txtId.SetFocus
addFlag = True
cmdNext.Enabled = False
cmdFirst.Enabled = False
cmdPrevious.Enabled = False
cmdLast.Enabled = False
End Sub
Private Sub cmdCancel_Click( )
With Rscourse
.CancelUpdate
Display
cmdFirst.Enabled = True
cmdLast.Enabled = True
cmdNext.Enabled = True
cmdPrevious.Enabled = True
End With
End Sub
Private Sub cmdDelete_Click( )
Dim Res As Integer
Res = MsgBox("Wish to Delete Record", vbYesNo,
"Confirm")
If Res = vbNo Then Exit Sub
With Rscourse
.Delete
.MovePrevious
If .EOF Then
.MoveLast
End If
If .BOF Then
.MoveFirst
End If
Display
End With
End Sub
Private Sub cmdExit_Click( )
Unload Me
End Sub
Private Sub cmdFind_Click( )
Dim Find_Str As String
Dim Cnn2 As ADODB.Connection
Dim rsSearch As ADODB.Recordset
If txtFind.Text = Empty Then
MsgBox "cannot be left empty"
txtFind.SetFocus
Exit Sub
End If
Find_Str = "select *from Course where CourseId ='"
& txtFind.Text & "';"
Set Cnn2 = New ADODB.Connection
Cnn2.ConnectionString = "Provider =
Microsoft.Jet.OLEDB.4.0;Data Source = E:\Sandhya\MyCourse.mdb"
Cnn2.Open
Set rsSearch = New ADODB.Recordset
rsSearch.Open Find_Str, Cnn2, adOpenDynamic,
adLockOptimistic
If rsSearch.EOF And rsSearch.BOF Then
MsgBox "Invalid Course Id", vbOKOnly,
"Stop!!"
Exit Sub
End If
txtId.Text = rsSearch!CourseId
txtTitle.Text = rsSearch!CourseTitle
txtHours.Text = rsSearch!Hours
Cnn2.Close
End Sub
Private Sub cmdFirst_Click( )
With Rscourse
.MoveFirst
Display
End With
End Sub
Private Sub cmdLast_Click( )
With Rscourse
.MoveLast
Display
End With
End Sub
Private Sub cmdNext_Click( )
With Rscourse
.MoveNext
If .EOF Then
.MoveLast
End If
Display
End With
End Sub
Private Sub cmdPrevious_Click( )
With Rscourse
.MovePrevious
If .BOF Then
.MoveFirst
End If
Display
End With
End Sub
Private Sub cmdSave_Click( )
With Rscourse
If adflag Then
.AddNew
Rscourse!CourseId = txtId.Text
Rscourse!CourseTitle = txtTitle.Text
Rscourse!Hours = Val(txtHours.Text)
Rscourse.Update
End If
End With
addFlag = False
End Sub
Private Sub txtFind_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub
Private Sub txtHours_KeyPress(KeyAscii As Integer)
If Chr(KeyAscii) = vbBack Then Exit Sub
If Not IsNumeric(Chr(KeyAscii)) Then
KeyAscii = 0
MsgBox "Hours must Be Numeric", vbOKOnly,
"Stop!!"
End If
End SubTop
