Mastering Database Queries: A Practical Guide with Examples

Database Queries: A Practical Guide

Understanding and Implementing Queries

This guide provides practical examples and explanations for creating and using database queries. We’ll cover various scenarios and techniques to help you effectively manage and retrieve data.

Section 1: Filtering and Sorting Data

30. Filtering by Birthdate and Phone Prefix: To permanently display customers born before 1975 with phone numbers starting with 954 in the Agenda table, use the following criteria:

  • BIRTHDATE: < 01/01/1975
  • Tlfno: 954*

Note: These criteria are applied in the same row, meaning both conditions must be met.

31. Filtering by Province: To permanently display only individuals from Seville in the Agenda table, use the following criterion:

  • Province: Sevilla

32. Creating an Identical Query Table: To create a query table identical to the Book table:

  1. Go to the Queries tab.
  2. Select “Create query in Design View.”
  3. Add the Book table.
  4. Select all fields.
  5. Close and save the query with a descriptive name.

33. Creating a Query Based on Age: To display customers over 40 years old in the Agenda table (assuming an “AGE” field exists):

  • AGE: > 40

34. Displaying Specific Fields and Sorting: To permanently display only NAME, LAST NAME, and AGE in the Agenda table, sorted by age in descending order:

  • Select fields: NAME, LAST NAME, AGE
  • Order by AGE: Descending

35. Filtering by Age Range: To permanently display customers between 18 and 50 years old in the Agenda table:

  • AGE: Between 18 And 50

36. Filtering by Province and Name: To permanently display customers from Seville named Antonio in the Agenda table:

  • NAME: Antonio
  • Province: Sevilla (in the same row as the NAME criterion)

37. Filtering by Multiple Provinces: To permanently display customers from Seville, Madrid, or Segovia in the Agenda table:

  • Option 1: Use separate criteria rows for each province (Seville, Madrid, Segovia) with the “Or” operator.
  • Option 2: Use a single criterion with multiple values: Province: In (Seville, Madrid, Segovia)

38. Filtering by Province or Name: To permanently display customers from Seville or named Antonio in the Agenda table:

  • Province: Sevilla
  • NAME: Antonio (in a separate row below the Province criterion)

39. Filtering by Name Patterns: Assuming customer names like Antonio, Ann, and Mary exist in the Agenda table:

  • a. Names starting with “AN”: NAME: AN*
  • b. Names ending with “O”: NAME: *O
  • c. Names starting and ending with “AN” or any character: NAME: AN*O
  • d. Names containing “TO”: NAME: *TO*

Note: The asterisk (*) represents any number of characters.

40. Combining Filtering and Sorting: To display customers from Seville or with names starting with “AN”, sorted by age in descending order:

  • Order by AGE: Descending
  • Province: Sevilla
  • NAME: AN* (in a separate row below the Province criterion)

Section 2: Relational Databases and Table Relationships

41. Creating and Relating Tables: This example demonstrates creating and relating three tables: Patients, Doctors, and Visits.

Patients Table:

  • CODE OF PATIENT (AutoNumber, Primary Key)
  • Name
  • Surname
  • Sex

Doctors Table:

  • CODE OF MEDICAL (AutoNumber, Primary Key)
  • Name
  • Surname
  • Age
  • Province

Visits Table:

  • CODE OF VISIT (AutoNumber, Primary Key)
  • Purpose of Visit
  • Treatment
  • Patient’s Name (Lookup field linked to Patients table)
  • Doctor’s Name (Lookup field linked to Doctors table)

Creating Lookup Fields:

  1. In the Visits table design view, choose “Insert” -> “Column Search.”
  2. Select “I want you to look up the values in a table.”
  3. Choose the appropriate table (Patients or Doctors).
  4. Select the desired fields (e.g., Name and Surname).
  5. Choose “Hide key column.”
  6. Name the new field appropriately (e.g., Patient’s Name or Doctor’s Name).

This setup allows you to link records in the Visits table to corresponding records in the Patients and Doctors tables, creating a relational database structure.