ICT 0417 - Databases

18. Querying Data

This section focuses on using queries to extract specific information from a database. Queries allow you to select subsets of data based on defined criteria. You can use single criteria or multiple criteria to achieve this.

Understanding Queries

A query is a request for data from a database. It specifies what data you want to retrieve and any conditions that the data must meet.

Single Criteria Queries

A single criteria query selects records based on a single condition applied to a specific field. For example, finding all customers who live in a particular city.

Example: Find all students with a mark greater than 70 in the 'Marks' field.

SQL Syntax (Conceptual):

SELECT * FROM Students WHERE Marks > 70; 

Multiple Criteria Queries

Multiple criteria queries use logical operators (AND, OR, NOT) to combine several conditions. This allows for more complex and targeted data retrieval. For example, finding all customers who live in London and have placed an order in the last month.

Example: Find all products that are either red OR blue AND have a price less than £20.

SQL Syntax (Conceptual):

SELECT * FROM Products WHERE (Colour = 'Red' OR Colour = 'Blue') AND Price < 20; 

Common Operators

Here's a summary of common operators used in queries:

• =: Equal to
• <> or !=: Not equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to
• AND: Both conditions must be true
• OR: At least one condition must be true
• NOT: Reverses the condition

Practical Examples & SQL (Conceptual)

The following table illustrates how queries can be used with different criteria.

Query	Description	Example Data	Result
Find all employees in the 'Sales' department.	Selects records where the 'Department' field equals 'Sales'.	Employee ID Name Department 101 Alice Smith Sales 102 Bob Johnson Marketing 103 Charlie Brown Sales	Employee ID Name Department 101 Alice Smith Sales 103 Charlie Brown Sales
Find all products with a price greater than £50.	Selects records where the 'Price' field is greater than 50.	Product ID Name Price 201 Laptop £120 202 Mouse £10 203 Keyboard £60	Product ID Name Price 201 Laptop £120 203 Keyboard £60
Find all customers who live in London AND have placed an order.	Selects records where the 'City' field equals 'London' AND the 'HasPlacedOrder' field is true.	Customer ID Name City HasPlacedOrder 301 David Lee London Yes 302 Eve Green Manchester No 303 Frank White London Yes	Customer ID Name City HasPlacedOrder 301 David Lee London Yes 303 Frank White London Yes

Important Considerations

When writing queries, it's important to:

• Use appropriate data types for comparisons.
• Be mindful of case sensitivity (some database systems are case-sensitive).
• Test your queries to ensure they produce the expected results.
• Consider performance – complex queries can be slow.