Database Practice Library
Every practice question across the Databases pages in one place, grouped by topic and difficulty. Use the filters to narrow by difficulty or mark band; click any page name to open the full question with its sub-parts and follow-on text.
Difficulty
Marks
Vocabulary
1 question.
Relational Database Fundamentals
- · Define each of the following terms in the context of a relational database:
Benefits and limits
4 questions.
Relational Database Fundamentals
- · A regional medical clinic is replacing its paper-based patient record system with a relational database. Explain two benefits the clinic ...
- · Outline two reasons why a community sports club managing 800 members and weekly class bookings would use a relational database rather tha...
- · Describe two limitations of the relational model that would push the developers of a global social media platform (with billions of users...
- · A student is building a school library application in Java. They have a Book class with a List<Loan> field, a Member class with a List<Lo...
Schemas
8 questions.
- · Define the term *schema* as used in a relational database, and state the three levels of schema.
- · Describe the difference between a conceptual schema and a logical schema. Give one example of what each would and would not include.
- · Identify three characteristics of a logical schema.
- · Distinguish between the conceptual, logical, and physical schemas of a database. For each level, state one decision that belongs at that ...
- · Explain the importance of data modelling in the design of a new relational database for a chain of veterinary clinics.
- · A chain of bookshops runs the same shop-management software in two contexts: one branch is a small village shop, and one is the chain's h...
- · To what extent is the three-schema architecture still useful in a world where many small applications are built rapidly using a single DB...
Relational Database Fundamentals
- · Look at the *Harbour Run* schema in Worked Example 2. Identify:
ERDs
8 questions.
- · Explain the role of entity-relationship diagrams when modelling data. Do *not* describe the symbols used; focus on the *purpose* of the d...
- · A clinic has tables PATIENT and APPOINTMENT. Each patient has had many appointments; each appointment is held by exactly one patient. Sta...
- · A bookshop sells books to customers. Each customer can place many orders; each order is placed by exactly one customer. Construct an ERD ...
- · A community sports club tracks members, classes, and bookings. Each member can book many classes; each class can be booked by many member...
- · A driving authority tracks individuals, driving licences, and vehicles. An individual may hold at most one driving licence; each licence ...
- · An online food-ordering platform connects customers, restaurants, menu items, and orders. A customer can place many orders; each order is...
- · Discuss the advantages and disadvantages of crow's-foot notation compared with Chen notation for documenting a database used by a softwar...
- · A pet-passport database tracks OWNER, PET, and PASSPORT. An owner may have many pets. Each pet has exactly one passport, and each passpor...
Data types
4 questions.
- · For each column, state the most appropriate data type and justify your choice in one short sentence.
- · A staff database has a PayGrade column holding values 1 to 12. Outline why integer is an appropriate data type for PayGrade.
- · An athletics database has an OlympicRecord column holding values like 9.63 and 19.30 (seconds). Outline why integer is *not* an appropria...
- · A booking system stores BookingDate as the string \"dd/mm/yyyy\". Outline two problems this causes and suggest a better approach.
Keys
5 questions.
- · Look at the following table:
- · An attendance system has the table ATTENDANCE(StudentID, ClassID, Date, Status) with primary key (StudentID, ClassID, Date). Explain why ...
- · A school library is moving from a spreadsheet to a relational database. It needs to track MEMBER, BOOK, and LOAN. Construct a schema for ...
- · Discuss the advantages and disadvantages of using a surrogate primary key (e.g. an auto-increment integer) compared with a natural primar...
Relational Database Fundamentals
- · The following table holds enrolment information for an after-school programming club.
Normalisation
9 questions.
- · State what is required for a table to be in each of 1NF, 2NF, and 3NF (one sentence per form).
- · Outline one reason why a relational database is typically normalised to 3NF.
- · Distinguish between 1NF and 2NF using one specific structural difference.
- · A booking table is given as BOOKING(BookingID, MemberID, MemberName, ClassID, ClassTitle, CoachName) with primary key BookingID. Outline ...
- · The following table is in 1NF with composite primary key (OrderID, ItemID):
- · Take the 2NF schema from question 5 and continue normalising. Construct the 3NF schema, identifying any transitive dependencies you remov...
- · A community music school records lessons using this single table:
- · An online news site's homepage displays each headline alongside the author's name and photograph. The site loads the homepage millions of...
- · The unnormalised table
SQL: SELECT
7 questions.
- · Outline the difference between DDL and DML, naming two commands from each category.
- · Write an SQL query against the Member table that lists the first name and surname of every member born before 1 January 2000, sorted by s...
- · Write an SQL query that lists every class whose Title contains the word \"Run\", sorted by StartsAt (earliest first).
- · Construct an SQL query that lists the first name, surname, and booking date of every booking made by members whose tier is 'Premium'.
- · Construct an SQL query that lists the class title of every class booked by Aroha Kahurangi (FirstName = 'Aroha', Surname = 'Kahurangi') i...
- · Write an SQL query that lists the distinct coaches of every class scheduled in June 2026, sorted alphabetically.
- · Given the sample tables above, state the output of the query
SQL: DML
9 questions.
- · State the three SQL DML commands used to modify data, and describe in one short sentence what each one does.
- · Write an SQL statement that adds a new class to the *Harbour Run* Class table:
- · Write an SQL statement that changes the status of every booking made before 1 January 2024 to 'archived'.
- · Write an SQL statement that removes every cancelled booking older than two years (assume today is 2026-05-22).
- · Explain what would happen if a developer ran DELETE FROM Booking; without a WHERE clause, and outline two practices that protect against ...
- · A Member table has indexes on MemberID (primary key) and Surname. Explain which of the following two updates is faster, and why:
- · The Booking.MemberID column is a foreign key to Member.MemberID. Explain what happens in each of the following cases, assuming the foreig...
- · Discuss the trade-off between adding many indexes to a table and keeping write performance fast. Refer to read-heavy and write-heavy work...
- · For the *Harbour Run* schema, construct SQL statements that perform each of the following operations. Use one statement per task. (2 mark...
Aggregates (HL)
3 questions.
- · Construct an SQL query that, for each membership tier, returns the tier name and the average age (in years) of members in that tier. Use ...
- · Construct an SQL query that lists every coach who has run more than 5 classes in 2026, ordered by the number of classes they have run, bi...
- · Construct an SQL query that lists, for each class title, the total number of confirmed bookings (Status = 'confirmed') made in June 2026,...
Transactions (HL)
7 questions.
- · Define the term *database transaction*.
- · State the four ACID properties and give a one-sentence summary of each.
- · Explain why atomicity is essential when a bank transfer is implemented as two UPDATE statements.
- · Explain how transaction durability is achieved, even when the server may crash at any moment.
- · Describe the role of the three TCL commands BEGIN TRANSACTION, COMMIT, and ROLLBACK in implementing the ACID properties.
- · Explain how the database prevents two customers from booking the same final seat at the cinema at the same time. Refer to the *Isolation*...
- · Discuss the trade-offs an engineering team faces when choosing between a strongly ACID-compliant relational database and a NoSQL database...
Views (HL)
2 questions.
- · Define the terms *virtual view* and *materialised view*, and state one key difference between them.
- · Evaluate whether a reporting team should use a virtual view or a materialised view for a \"top 100 customers this month\" report that is op...
Alternative DBs (HL)
9 questions.
- · Outline what is meant by each of the four alternative database approaches covered on this page: NoSQL, cloud, spatial, and in-memory.
- · A start-up is building a navigation app that maps real-time vehicle positions onto a road network. Suggest the most appropriate database ...
- · Describe what is meant by *spatial data*, and state two geometric primitives a spatial database can store.
- · Discuss the main trade-offs of choosing a NoSQL database over a relational database for a system that needs to store millions of user pos...
- · Explain why in-memory databases are dramatically faster than disk-based ones, and identify two trade-offs that come with using one.
- · Explain two benefits and two drawbacks of using a cloud database for a small SaaS start-up's relational data store.
- · A food-delivery start-up needs to handle: (a) orders and payments, (b) real-time driver locations, (c) shopping-cart state, (d) user revi...
- · Evaluate whether a long-established financial institution with strict regulatory requirements should migrate its core transactional datab...
Relational Database Fundamentals
- · A start-up is choosing between a relational database and a NoSQL document store for its core product. Discuss the trade-offs they should ...
Warehouses and OLAP (HL)
8 questions.
- · Define the term *data warehouse*.
- · State the four canonical properties of a data warehouse and give a one-sentence summary of each.
- · Distinguish between OLTP and OLAP workloads, giving one example of each.
- · Explain the primary objectives of a data warehouse for a chain of supermarkets that operates in 12 countries.
- · Compare classification and clustering as data-mining techniques. Refer to the type of input data, the kind of output, and a typical use c...
- · Describe the process of anomaly detection. Identify two real-world applications where it adds value.
- · Discuss how a data warehouse, OLAP tooling, and data mining work together to support business intelligence in a large hospital chain. Ref...
- · Evaluate whether a small charity with 3,000 supporters and a single fundraising platform should invest in building a data warehouse, or w...
Distributed DBs (HL)
8 questions.
- · Define a distributed database and outline one reason an organisation might choose to deploy one.
- · State four features of a distributed database from the eight covered on this page, and write a one-sentence description of each.
- · Distinguish between partitioning and replication in a distributed database.
- · Describe how a distributed database achieves fault tolerance. Refer to replication, automatic failover, and the practical effect on users.
- · Explain what is meant by *transparency* in a distributed database. Name at least three different kinds and what each hides from the appli...
- · Discuss the trade-off between strong consistency and high availability in a globally distributed database. Refer to one scenario where co...
- · A ride-sharing app has 50 million users across 100 countries. Suggest an appropriate partitioning strategy for the User table, and justif...
- · Evaluate the suitability of a distributed database for a national health-records system covering 60 million patients. Refer to at least f...