SQL View in MS Access

SQL stands for Structured Query Language. It is used to communicate with relational databases.

SQL View is the area in Access Query Design where you can view, write, and edit the SQL statement for a query.

Example

SELECT *
FROM Students;

This query retrieves all fields and records from the Students table.

Follow these steps to open SQL View in MS Access:

1. Open your Access database.
2. Click the Create tab.
3. Select Query Design.
4. Close the Show Table dialog if it appears.
5. Open the View menu on the Query Design tab.
6. Select SQL View.
7. Type your SQL statement in the editor.
8. Click Run (!) to execute the query.
9. Save the query with a meaningful name.

You can also switch an existing query between Design View and SQL View using the View menu.

The SELECT statement retrieves data from one or more tables.

Example 1: Display All Columns

SELECT *
FROM Students;

The asterisk (*) means all columns.

Example 2: Display Selected Columns

SELECT StudentName, Course
FROM Students;

This query displays only the StudentName and Course fields.

Example 3: Display Student Names and Fees

SELECT StudentName, Fees
FROM Students;

Only the selected fields appear in the query results.

The WHERE clause selects records that meet a specified condition.

Example 1: Find ADCA Students

SELECT *
FROM Students
WHERE Course = "ADCA";

This query displays students whose Course is ADCA.

Example 2: Find Students with Fees Above 5000

SELECT StudentName, Fees
FROM Students
WHERE Fees > 5000;

Only students whose Fees value is greater than 5000 are returned.

Example 3: Find One Student by ID

SELECT *
FROM Students
WHERE StudentID = 101;

This query retrieves the record whose StudentID is 101.

The ORDER BY clause sorts query results in ascending or descending order.

Example 1: Sort Names A to Z

SELECT *
FROM Students
ORDER BY StudentName ASC;

Example 2: Sort Fees from Highest to Lowest

SELECT StudentName, Fees
FROM Students
ORDER BY Fees DESC;

Example 3: Sort by Course and Name

SELECT StudentName, Course, Fees
FROM Students
ORDER BY Course ASC, StudentName ASC;

• ASC means ascending order.
• DESC means descending order.

AND and OR combine multiple conditions in a WHERE clause.

Example 1: AND Operator

SELECT *
FROM Students
WHERE Course = "ADCA"
AND Fees > 5000;

Both conditions must be true for a record to be included.

Example 2: OR Operator

SELECT *
FROM Students
WHERE Course = "ADCA"
OR Course = "Tally Prime";

A record is included if either condition is true.

Aggregate functions perform calculations on a set of values and return a summary.

Example 1: Count Students

SELECT Count(StudentID) AS TotalStudents
FROM Students;

Example 2: Calculate Total Fees

SELECT Sum(Fees) AS TotalFees
FROM Students;

Example 3: Calculate Average Fees

SELECT Avg(Fees) AS AverageFees
FROM Students;

Example 4: Find Highest and Lowest Fees

SELECT Max(Fees) AS HighestFee,
       Min(Fees) AS LowestFee
FROM Students;

These queries return summary values instead of displaying every individual record.

GROUP BY combines records that have the same value in a specified field. It is commonly used with aggregate functions.

Example: Total Fees by Course

SELECT Course, Sum(Fees) AS TotalFees
FROM Students
GROUP BY Course;

This query displays each course once and calculates the total fees for that course.

Example: Count Students by Course

SELECT Course, Count(StudentID) AS TotalStudents
FROM Students
GROUP BY Course;

This query shows how many student records belong to each course.

An UPDATE statement changes existing records. Use a WHERE clause when you intend to change only selected records.

Example: Update Fees for One Student

UPDATE Students
SET Fees = 12000
WHERE StudentID = 101;

This statement sets the Fees value to 12000 for the record with StudentID 101.

Both views can be useful. You can begin in Design View and switch to SQL View to examine or edit the generated SQL statement.

Always check table names, field names, and data types when Access displays an SQL error.

Create a Students table containing StudentID, StudentName, Course, Fees, and AdmissionDate fields.

Tasks

1. Display all student records using SELECT.
2. Display only StudentName and Course.
3. Find students enrolled in ADCA.
4. Find students whose fees are above 5000.
5. Sort student names in ascending order.
6. Sort fees in descending order.
7. Use AND to find ADCA students whose fees exceed 5000.
8. Use OR to find students in two different courses.
9. Count student records using Count(StudentID).
10. Calculate total fees using Sum(Fees).
11. Calculate total fees for each course using GROUP BY.
12. Write an UPDATE statement for one student in a test database.

1. What is SQL?
2. What is SQL View in MS Access?
3. How do you open SQL View?
4. What does SELECT * mean?
5. What is the purpose of the WHERE clause?
6. What is the difference between ASC and DESC?
7. How do you count student records in SQL?
8. What is the purpose of GROUP BY?
9. What does an UPDATE statement do?
10. Why should you be careful when using UPDATE without WHERE?