Databases – Queries and Database Practice Queries

1. Databases – Queries and Database Practice Queries When you create a database – one of the main strengths of it is the ability to query it to get the relevant information that you need. In DBMS systems like Access it allows you to create your own queries using QBE (Query by Example). In this interface you can put parameters to refine your searches through your database to return only the items you wish.

2. Databases – Queries and Database Practice Queries – Parameters When you select the fields you want to include in a query and run it, every record in the database would be returned.

3. Databases – Queries and Database Practice Queries – Parameters In order to make a query more meaningful, it is usual to include at least one parameter (search criteria) in the query. When you run the query, you will be presented with the result that matches the parameter used.

4. Databases – Queries and Database Practice Queries – Parameters - Ranges Parameters can also be ranges as well. If you are using a criteria on a currency field or numerical field – Range Parameters are especially relevant. Here are some further examples of types of parameters which could be used within a query: Greater than >500 Less than <250 Greater or equal to >=310 Less than or equal to <=1000 Between between 200 AND 600 >=200 AND <=600

5. Databases – Queries and Database Practice Queries – Parameters - Wildcards The * is used as a wildcard. This means you can search for a record, even if you don't know the exact thing to write in your query. E* - will search for everything which starts with an E. So for example, if we couldn't remember the customer's name was Eaton, but we knew it started with E, then we could have used a wild card to find the record i.e. E*. This would have also found us Ely and Essen, but it would have helped to narrow down the search. *N - will search for everything which ends with a N. This search might find customers such as, Brown, Green, Richardson. *as* - This will search for everything which contains the consecutive letters 'as' somewhere within the field, e.g. Aston, Last, Brasier

6. Databases – Queries and Database Practice Queries – Parameters – Boolean Queries You can use the keywords AND, NOT and OR in your queries as well and they perform exactly as boolean values should. You can combine them as well.

7. Databases – Queries and Database Practice Queries – Parameters – Dynamic Parameters You can also set up your queries so you can ask the user what the criteria is at run time – this makes it more dynamic. To do this you simply use the [] and a prompt in the criteria box.

8. Databases – Queries and Database Practice Task Can you normalise the Basement Order form and create a database? Resources are on the interanet.

9. Databases – Queries and Database Practice Task Using the Basement Database – create an example of the following queries. Static query with one criteria Static query with two or more criteria Dynamic query with one criteria Dynamic query with two or more critera Query with range criteria Query with wildcard criteria Query with boolean criteria Complex query with multiple features from above EXTENSION – Query with a function.