Understanding database tables in SQL

Structured Query Language (SQL) is a programming language used to manage and query data in databases. Databases often organise information into tables, where records are the rows containing individual entries, and fields are the columns representing specific attributes of their entries.

Key components of a database table

• Records - Each row in the table, representing a complete set of data for one item.
• Fields - Each column in the table, holding a specific type of information across all records.

SQL allows you to search these tables to extract specific data.

Example database table: hotels

This table stores information about different hotels, with fields like hotelName and hotelRating. SQL queries can retrieve data from such tables.

Using SELECT and FROM to retrieve data

The foundation of any SQL search query involves the SELECT and FROM keywords. These specify what data to retrieve and where to find it.

How SELECT and FROM work

• SELECT - Specifies the fields you want to retrieve.
• FROM - Indicates the table containing the data.

This basic structure returns data for all records in the table unless further filtered.

Example query: Retrieving a single field

This query retrieves the hotelName field for every record in the hotels table, resulting in:

Example query: Retrieving multiple fields

This returns both the hotelName and hotelRating for all records:

Using wildcards to select all fields

When you need to retrieve every field from a table without listing them individually, SQL provides a wildcard character.

The * wildcard

• The asterisk (*) acts as a wildcard, meaning "all fields".
• Use it with SELECT to return complete records.

Example query with wildcard

This returns all fields for every record in the hotels table, displaying the entire table as shown earlier.

Wildcards are useful for broad searches but can be combined with filters for more targeted results.

Filtering results with the WHERE clause

To narrow down search results, add a WHERE clause after FROM. This specifies conditions that records must meet to be included.

How the WHERE clause works

• WHERE - Follows FROM and defines a condition using comparison operators.
• Only records satisfying the condition are returned.

This helps in finding specific data, such as hotels with high ratings.

Example query with WHERE

This retrieves all fields for records where hotelRating is 4.0 or higher:

Combining conditions using AND and OR operators

For more precise searches, combine multiple conditions in the WHERE clause using Boolean operators. These allow complex filtering based on several criteria.

Boolean operators in SQL

• AND - Requires all conditions to be true for a record to be included.
• OR - Includes a record if at least one condition is true.

These operators connect conditions, making queries more specific.

Example query with AND

This returns hotelName for records with En-suite bathrooms and prices under £43:

Worked example - Querying for hotels with more than 110 rooms

Stacey wants to find hotels with more than 110 rooms, displaying the hotel name and number of rooms.

Step 1: Identify the requirements

• Fields needed: hotelName, rooms
• Table: hotels
• Condition: rooms > 110

Step 2: Write the query

Step 3: View the results

Step 4: Interpretation

This query filters the table to show only hotels meeting the room requirement, helping Stacey compare options.

Worked example - Querying with multiple conditions

Stacey now requires hotels with more than 110 rooms and a rating of 3.9 or above, displaying the hotel name and price.

Step 1: Identify the requirements

• Fields needed: hotelName, priceInPounds
• Table: hotels
• Conditions: rooms > 110 AND hotelRating >= 3.9

Step 2: Write the query

Step 3: View the results

Step 4: Interpretation

Only one hotel satisfies both conditions, demonstrating how AND narrows results effectively for precise searches.