4.10.4 - SQL retrieval across related tables (C#)

SELECT, filtering and ordering

A retrieval query describes which stored data is wanted without changing the database.

Our original club database has three relations. MemberID, ClubID and BookingID are their respective primary keys. Booking.MemberID and Booking.ClubID are foreign keys. Each booking records a purchase of sessions, so one member can have several bookings.

Member

Club

Booking

Read this query as “take Name from Member, keep YearGroup 12, then order by Name”.

SELECT Name
FROM Member
WHERE YearGroup = 12
ORDER BY Name ASC;

It returns Aisha, then Chen. SELECT chooses output fields; FROM identifies input tables; WHERE filters rows; ORDER BY orders the result. ASC means ascending and is the default; DESC reverses it. If the task needs a predictable order, specify one. Without ORDER BY, do not assume that rows follow their stored or printed order. With tied names, a second field such as MemberID can break ties.

Select several fields with commas. SELECT * retrieves every field, but is unsuitable when the task wants only named fields. WHERE may use =, <>, <, <=, > and >=. Combine requirements with AND; use OR when either is enough. Parentheses make mixed alternatives explicit, for example (YearGroup = 12 OR YearGroup = 13) AND Name <> 'Ben'. Numeric literals are unquoted; text literals use single quotes in these examples. The inspected AQA SQL schemes also accept double-quoted text; actual database dialects may treat it differently. A semicolon ends the complete statement, not each clause. SQL keywords are shown uppercase for readability.

SQL text inside C#

The host language and database language have different jobs. This complete static C# example stores and prints SQL text; it does not contact a database.

using System;

class Program
{
    static void Main()
    {
        string query = "SELECT Name FROM Member WHERE YearGroup = 12;";
        Console.WriteLine(query);
    }
}

C# uses double quotes around its string. The SQL statement inside the string still has SQL syntax. A database connection and command would be needed to request results; Console.WriteLine alone cannot retrieve members. For a Paper 2 SQL question, answer with SQL, not this C# wrapper.

Joining related tables

A join matches rows through their related key values so that a result can contain information stored in different tables.

Member names are in Member; bookings connect members to clubs; club names are in Club. To find names and session counts for Robotics bookings of at least three sessions, join on both key relationships and then apply the restrictions.

SELECT m.Name, b.Sessions
FROM Member AS m
INNER JOIN Booking AS b ON m.MemberID = b.MemberID
INNER JOIN Club AS c ON b.ClubID = c.ClubID
WHERE c.ClubName = 'Robotics' AND b.Sessions >= 3
ORDER BY m.Name;

Aliases m, b and c are short table names used consistently within this statement. m.MemberID identifies which table supplies a field whose name occurs in both tables. The first join matches each booking to its member; the second matches it to its club. An inner join keeps matching pairs, so a member without bookings does not appear. Aisha's Robotics row gives 4 sessions and Chen's gives 5; Ben's 2-session booking is removed by the filter.

The same inner-join result can be expressed with a comma-separated table list and all joining and filtering conditions in WHERE:

SELECT Member.Name, Booking.Sessions
FROM Member, Booking, Club
WHERE Member.MemberID = Booking.MemberID
  AND Booking.ClubID = Club.ClubID
  AND Club.ClubName = 'Robotics'
  AND Booking.Sessions >= 3
ORDER BY Member.Name;

These are alternative ways to express this relationship, not extra tables to add together. Without a linking condition, a comma join combines every row with every row from the other table. Member's four rows and Booking's five would produce 20 combinations before filtering. Plausible-looking names can therefore appear beside another member's booking. A foreign-key declaration protects stored relationships; it does not supply an omitted query condition.

Several matching bookings may legitimately return the same member name more than once. SELECT DISTINCT m.Name removes duplicate output names when the task wants each name once. It does not repair a faulty join. If two different members share a name, selecting MemberID as well preserves their distinct identities.