4.10.4 - SQL retrieval across related tables (VB.NET)

4.10.4 - SQL retrieval across related tables (VB.NET)

Learn to retrieve the right fields and records, connect related tables, summarise data and check a query against known results. SQL is the assessed language here: these Paper 2 written tasks have no Run. The VB.NET route is incomplete: runnable support pending.

Selecting, filtering and ordering records

A retrieval query describes the data wanted from a database without changing the stored records.

SQL

Structured Query Language (SQL) is a language for defining and working with relational database data. A SELECT query produces a result table.

An equipment store holds this Equipment table. ItemID is its primary key; costs are in pounds per day.

ItemIDItemNameCategoryDailyCost
10CameraMedia12
20TripodMedia4
30MicrophoneAudio6
40MixerAudio15
50ProjectorPresentation15
SQL
SELECT ItemName, DailyCost
FROM Equipment
WHERE Category = 'Audio' AND DailyCost >= 6
ORDER BY DailyCost DESC, ItemName ASC;

SELECT names the result columns; FROM names their source. WHERE keeps only rows for which the condition is true. Both conditions must hold because they are joined by AND. The result is Mixer, 15 followed by Microphone, 6. DESC means decreasing order; ASC means increasing order and is the default. The second sort field breaks ties in the first. Without ORDER BY, do not assume a particular result order.

Use single quotes for text literals; numbers are normally unquoted. SQL keywords are shown in capitals for readability. = compares values, <> means not equal, and <, <=, > and >= express boundaries. A semicolon terminates the whole statement, not each clause. SELECT * returns every column, but naming fields gives precisely the requested output.

OR accepts either condition. Parentheses make the intended grouping clear:

SQL
SELECT ItemName
FROM Equipment
WHERE (Category = 'Media' OR Category = 'Audio')
  AND DailyCost <= 12
ORDER BY ItemName;

This returns Camera, Microphone and Tripod. Removing the parentheses changes the condition because AND binds more tightly than OR: all Media rows would then pass, whatever their cost. BETWEEN 6 AND 12 includes both endpoints. LIKE 'M%' matches text beginning with M: % represents any sequence of characters, while _ represents one character. DISTINCT removes duplicate result rows, for example when requesting the categories rather than the items.

Joining related tables

A join combines the records that refer to the same entity, allowing one query to retrieve fields held in different tables.

The store keeps its members and hires separately from equipment:

MemberIDMemberNameYearGroup
11Amira12
12Ben13
13Chen12
HireIDItemIDMemberIDDaysReturned
101101120
102201131
103301210
104101310
105401221

Member.MemberID and Equipment.ItemID are primary keys. Their corresponding fields in Hire are foreign keys. Returned is 0 for an outstanding hire and 1 for a returned hire. One member can have many hires; one item can appear in many hires.

SQL
SELECT M.MemberName, E.ItemName, H.Days
FROM Member AS M
INNER JOIN Hire AS H ON M.MemberID = H.MemberID
INNER JOIN Equipment AS E ON H.ItemID = E.ItemID
WHERE H.Returned = 0
ORDER BY M.MemberName ASC, H.HireID ASC;

For Hire 101, the first equality finds Member 11, Amira; the second finds Equipment 10, Camera. The same process connects each hire to its member and equipment. The condition then excludes Hires 102 and 105. The result is Amira–Camera–2, Ben–Microphone–1 and Chen–Camera–1. AS introduces shorter table aliases. Qualifying a field as H.MemberID identifies its table when the same field name appears in more than one table.

An INNER JOIN retains matching records only. A member with no hires does not appear in this result. A query that needs members with no hires would require a different join, such as a left outer join; do not silently treat an inner join as if it included them.

The same relationships can be stated using a comma-separated FROM list and equalities in WHERE. In that form the relationship conditions must be joined to the other conditions by AND. Merely listing tables does not establish a relationship: an unconnected pair produces every combination of their rows, a Cartesian product. DISTINCT cannot repair the wrong associations.

Continue this lesson.

You’re previewing NovaLearn without an account. Create a free account to read the rest.

Create free account