MS Access 2007 Step By Step - Asim Abbasi

Chapter 1: Overview

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

Having a Brief SQL Flavor

SQL for MS Access differ from SQL for Oracle very minutely. All companies having RDBMS database as product have to fulfill the basic RDBMS commands. SQL is composed of bunch of commands rather complete English words and I sometimes say to my students that anyone knowing just English can imagine the outcome of SQL commands e.g.

SELECT ename, salary
FROM emp;

Here ename and salary are the column names (a.k.a. field names) of emp table. This is one of the basic forms of SELECT statement in SQL. SELECT statements are used to retrieve the information from the database. If you want to limit the number of records you can write in the following manner.

SELECT ename, salary
FROM emp
WHERE salary < 30000;

Using the WHERE clause can help in filtering records or in other words limiting the number of records in the output of a query. There is one more very interesting clause in the SELECT statement that is the ORDER BY clause. Using this clause you can have the data arranged in order e.g. alphabetically ascending or descending etc.

SELECT ename, salary
FROM emp
WHERE salary < 30000
ORDER BY ename;

If you want the order to be descending then just write DESC at the end of the statement as shown below.

SELECT ename, salary
FROM emp
WHERE salary < 30000
ORDER BY ename DESC;

Other DML Statements:

UPDATE statement is used to edit the information already existing in the table. If you want to insert a new record in the table then we use the INSERT statement and if you want to delete any record or more than one record we use the DELETE statement.

Example:

UPDATE emp
SET comm = NULL
WHERE job = 'TRAINEE';

In this example, emp table gets updated and the comm field will be having NULL values where job field values are equal to ‘TRAINEE’.

Example:

INSERT INTO dept
VALUES (50, 'PRODUCTION', 'SAN FRANCISCO');

In this example, one record gets inserted (appended) into the dept table. Remember that this command will work only if the order of the fields’ data-type matches with the order of data-type of the values you are going to insert. e.g. if the first field of dept table is of integer data-type but instead of inserting integer (50) you insert date then the command will result in an error.

Another version,

INSERT INTO emp (empno, ename, job, sal, comm, deptno)
VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);

In this particular example, the order of data-types of values and explicit table fields names should match e.g. if you have deptno (Integer data-type) at the last place then in the values you should have some integer data-type value at the last place otherwise the SQL statement will result in error.

Example:

DELETE FROM emp
WHERE JOB = 'SALESMAN' AND COMM < 100;

Here records from emp table gets deleted but only those where JOB field value is equal to ‘SALES MAN’ and COMM field value is less than 100.

Chapter 1: Overview

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

Share with others:

products

books

FREE VB, ASP Projects

MS Access 2007 Step By Step - Asim Abbasi