Using SQL in Microsoft Access

Support de cours Using SQL in Microsoft Access, tutoriel & guide de travaux pratiques Access en pdf.

Using SQL in Microsoft Access

Structured Query Language (SQL) is a language often used in querying, updating, and managing relational databases. Each query created in Microsoft Access has an associated SQL statement that defines the actions of that query. You can use SQL statements to:
View and modify queries you created using the QBE grid
For every query you create using the QBE grid in Design view, you can view and make changes to its SQL statement.
Set properties
You can use SQL statements when setting form and report properties, such as Record Source and Row Source.
Create SQL-specific queries
For some types of queries, you must use SQL statements. Union queries, pass-through queries, and data-definition queries cannot be designed using the QBE grid.
Create subqueries
To create a subquery, you use the QBE grid, but you enter an SQL SELECT statement for criteria.
Using SQL to View and Modify Queries
When you create a query with the QBE grid, Microsoft Access constructs the equivalent SQL statement behind the scenes. You can view or modify the SQL statement, rather than use the QBE grid. After you make changes to the SQL statement, the changes are reflected in the QBE grid.
To view or modify an SQL statement
1. Create a query, or open an existing query in Design view.
2. From the View menu, choose SQL View.
Microsoft Access displays the query in SQL view. This view is of the SQL statement that is equivalent to what you created in Design view.
3. If you want to make changes, enter the changes into the SQL statement.
4. To return to the QBE grid select from the View menu, choose Design View.
The QBE grid reflects the changes you made.
Activity P8-2
Examine each of the queries you created for Activity P8-1 in SQL view. If you have saved any queries created in earlier practicals, also examine these queries in SQL view.
Now re-examine the queries for Activity P8-1(a) to (f) using the QBE grid. Convert your parameter queries into standard select queries by carrying out the following modifications (shown underlined) to these queries using the QBE grid and note the changes reflected in the corresponding SQL statement. Also, run the queries to ensure that they retrieve the correct information.
(a) To display the details of videos in category “Comedy” or “Children”.
(b) To display the details of one or more members with “Smith” as the last name.
(c) To display the details of member number “4”.
(d) To display the details of one or more members with last name “Smith” and first name “Lorna”.
(e) To display the member number, full name, and address of members who have rented out video number “1”.
(f) To display the details of videos with a daily rental rate that is less than 4.00.
…….