INTRODUCTION
TO SQL - ACCESS
U. Rangith
ICTT – Instructor | NVQ Level – 04
Vocational Training Center
Ministry of Vocational Training Authority of Sri Lanka
There are Four Main Parts
■ SELECT
■ FROM
■ WHERE
■ ORDER BY
Access SQL – Programming with
Access 2007
■ SELECT Statement
– Return value from a specify field in a table or tables
■ Example with Employee Table:
– SELECT ID, FirstName, LastName, UserName
■ Fully Qualified:
– SELECT employee.ID, employee.FirstName, employee.LastName,
employee.UserName
Selecting Field From Table to Display
■ SELECT Statement
– Return value from a specify field in a table or tables
■ Example with Employee Table:
– SELECT FieldName1, FieldName2, FieldName3
■ Fully Qualified:
SELECT Table1. FieldName1, Table1. FieldName2, Table1. FieldName3, Table1.
FieldName4
FROM Table1;
SELECT Query Example
■ Example with Employee Table:
– SELECT ID, FirstName, LastName, UserName
■ Fully Qualified:
– SELECT employee.ID, employee.FirstName, employee.LastName,
employee.UserName
■ SELECT *
FROM Employee;
INNER JOIN
Return all record were given values are equal in both tables.
Usage
FROM Customers INNER JOIN Contacts ON
Customer.ID=Contacts.Customer_ID
OUTER JOIN
Return records from one table and some records from another table where given values are equal in
both tables.
Usage
FROM Customers LEFT JOIN Contacts ON Customer.ID=Contacts.Customer_ID
All SOME
FROM Customers RIGHT JOIN Contacts ON Customer.ID=Contacts.Customer_ID
SOME ALL
Full Use
SELECT Customers.CustomerName, Contacts.LastName,Contacts.Email FROM Customer
INNER JOIN Contacts ON Customers.ID=Contacts.Customer_ID
Connecting Two Tables or More Table
SELECT Table1.Field_Name, Table1.Field_Name, Table3.Field_Name
FROM Table1 INNER JOIN (Table2 INNER JOIN Table3 ON Table2.bid=Table3.bid) ON
Table1.sid=Table3.sid
WHERE (Condition);
Example:
SELECT sailor.sid, Sailor.sname, Reserved.color
FROM Sailor INNER JOIN (Reserved INNER JOIN Boast ON Reserved.bid=Boast.bid)
ON Sailor.sid=Boast.sid
WHERE Sailor.sname="Raman"; Table1: Sailor
Table2: Reserved
Table3: Boast
Caculating Average
SELECT Avg(Table_Field_Name) AS Field_Heading/Field_Name
FROM Table_Name;
Example:
SELECT Avg(sailor.age) AS Average
FROM sailor;
Using Where Condition
SELECT Avg(Table_Field_Name) AS Field_Heading/Field_Name
FROM Table_Name;
WHERE(Condition)
Example:
SELECT Avg(sailor.age) AS Average
FROM sailor
WHERE (sailor.rating<10);
Count Data
SELECT COUNT(Field_Name)
FROM Table_Name;
Example:
SELECT COUNT(sname)
FROM sailor;
Using And, OR Functions
SELECT Sailor.sid, Sailor.sname, Reserved.color
FROM Sailor INNER JOIN (Boast INNER JOIN Reserved ON Boast.bid=Reserved.bid)
ON Sailor.sid=Boast.sid
WHERE Reserved.color="red" Or Reserved.color="green";
Filtering Data using Where Conditon
SELECT Sailor.sid, Sailor.sname, Boast.bid
FROM Sailor INNER JOIN Boast ON Sailor.sid=Boast.sid
WHERE Boast.bid=103;
ORDER BY
ORDER BY This is used to Order Change the Ascending Order of particular Field Name
SELECT Table1.Field_Name1, Table1.Field_Name2, Table1.Field_Name3
FROM Table1
ORDER BY Table1.Field_Name3;
Example:
SELECT EMP.ID, EMP.First_Name, EMP.Contact_Number
FROM EMP
ORDER BY EMP.Contact_Number;
Calculating SUM
SELECT sum(Table1.Field_Name1) AS New_Field_Name FROM Table1;
Example:
SELECT sum(sailor.age) AS Total_Sum FROM sailor;
The SQL MIN() and MAX() Functions
■ The MIN() function returns the smallest value of the selected column.
■ The MAX() function returns the largest value of the selected column.
MIN() Syntax
SELECT MIN(Field_Name)
FROM Table_Name
WHERE condition;
Example
SELECT MIN(Student_Average)
FROM Stu_Marks
Where Condition;
The SQL MIN() and MAX() Functions
■ The MIN() function returns the smallest value of the selected column.
■ The MAX() function returns the largest value of the selected column.
MAX() Syntax
SELECT MAX(Field_Name)
FROM Table_Name
WHERE condition;
Example
SELECT MAX(Student_Average)
FROM Stu_Marks
Where Condition;
The SQL BETWEEN Operator
■ The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates.
■ The BETWEEN operator is inclusive: begin and end values are included.
SELECT Field_Name1,
FROM Table_Name
WHERE Field_Name BETWEEN value1 AND value2;
Example:
SELECT Stu_ID, Stu_Marks, Stu_Total;
FROM Student
WHERE Stu_Marks BETWEEN 50 AND 70;
UPDATE A FIELD RECORDS
SELECT Table1.Field_Name1, Table1.Field_Name2 + Value AS Field_Name
FROM Table1;
Example:
SELECT EMP.EMP_ID, EMP.EMP_Salary + 5000 AS Updated_Salary
FROM EMP;
Filtering records from a field
SELECT Table_Name.Field_Name1, Table_Name.Field_Name2
FROM sailor
WHERE Field_Name LIKE ‘ Character * ' ;
SELECT EMP.EMP_Name, EMP.Salary
FROM EMP
WHERE EMP_Name LIKE 'A*';
Its Display the employe name which is starts by “A”
Try Some Practical…
