T Level Digital: SQL Queries and Entity Relationship Diagrams

Writing and reading SQL, and drawing the entity relationship diagram behind it, are part of the introduction to analytics in the Pearson T Level core for Digital Data Analytics, which is assessed through two written papers and an employer-set project. The data content that all three digital cores share also expects relational models with tables, rows and keys, so students on the Digital Software Development and Digital Support and Security routes will find the key and relationship questions useful too.

Every query question in the quiz shows the rows it works on, taken from the invented tables of an online hamper shop: four customers, five orders and two suppliers. You predict the exact result. Nine questions work through the commands the specification lists: a WHERE filter on town, a COUNT with BETWEEN that turns on whether the end values are included, a LIKE pattern where the underscore stands for exactly one character, GROUP BY with COUNT, an INNER JOIN filtered by order value, a LEFT JOIN that returns one more row than the inner join would, ORDER BY with DESC, NOT, and a UNION that removes duplicate towns. Three questions move to the design behind the tables: what a customer_id in the Orders table is and which relationship it creates, how a many-to-many relationship between students and courses is resolved with a link table, and why that link table needs a composite key. Every result was checked by running the queries, and each explanation walks through which rows survive and why.

The flashcards cover each SQL command in the specification with a one-line example, the three joins, UNION against UNION ALL, and the vocabulary of entity relationship diagrams: entities, primary, foreign and composite keys, cardinality and link tables.

The written work has eight tasks to answer on paper. Four ask you to write or explain queries on the same data, and state the result: customers per town in alphabetical order, March orders over 40 with the highest first, INNER against LEFT JOIN with the row counts, and UNION against UNION ALL. Two are design tasks: the entities, keys and relationships for a college with students, courses and tutors, and the three kinds of key illustrated from a hotel booking database. Two are longer, evaluative answers of the kind used for the higher-mark questions: SQL on a relational database against filtering a spreadsheet of 200,000 orders, and a firm that stores each customer's orders as a comma-separated list in one field. Each has a model answer and the points a marker would look for.

There is also a short oral practice with an examiner, who reads out every table row before asking about it, accepts any correct SQL and gives brief feedback at the end.

• Predict the exact result of queries using SELECT, WHERE, AND, OR, NOT, BETWEEN and LIKE with % and _
• Use ORDER BY, GROUP BY and COUNT to sort and summarise data
• Distinguish INNER JOIN, LEFT JOIN and RIGHT JOIN and predict how many rows each returns
• Use UNION and explain how it treats duplicates
• Identify primary, foreign and composite keys
• Describe relationships and their cardinality on an ERD and resolve a many-to-many relationship with a link table

Practice material written by Zestly, based on the core content of the Pearson T Level Technical Qualification in Digital Data Analytics (first teaching September 2025), content area 2: SQL commands (2.1.2) and entity relationship diagrams (2.8.2), with relational data models from the data content shared by the Pearson digital cores.