Database administration in GUI clients
Inspect schemas, fix data, manage users and run migrations in DB GUI clients.
20 project-authored proposals · taxonomy v1.1 · 0 new benchmark runs. Verifiers and partial-credit rules below are designs, not executed results.
- Typical applications
- DBeaver · pgAdmin · DataGrip · SSMS
- Class GUI-need label
HY· Hybrid
Task proposals · 20
Expand to inspect the complete original wording and proposed checks. Original proposal text remains English in both interfaces.
R03.01.01.02-T01 Create a read-only analyst role with default privileges in pgAdmin
Original instruction
In pgAdmin 4 connected to the sandbox PostgreSQL 16 server, create login role 'analyst' (password from fixtures/secrets.env, connection limit 5, expires 2026-12-31) with read-only access to all current and future tables in schema sales of database 'erp', and no access to schema hr.
- Platforms
mac· macOSwin· Windows- Applications
- pgAdmin
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clicktype· Text & forms- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Grader queries pg_roles, has_table_privilege for every sales/hr table, and pg_default_acl; then creates a new sales table as owner and checks analyst can SELECT but not INSERT. Connection limit and expiry exact.
Proposed partial credit
30% role attributes; 30% current table grants; 25% default privileges; 15% hr denied
R03.01.01.02-T02 Restore a backup under a new name and check its integrity in SSMS
Original instruction
In SSMS, restore fixtures/Sales_2025.bak onto the sandbox SQL Server as database Sales_QA, relocating data and log files to D:\qa\, set recovery model SIMPLE and compatibility level 160, then run DBCC CHECKDB and save its output to output/checkdb.txt.
- Platforms
win· Windows- Applications
- SSMS · SQL Server
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clicktype· Text & forms- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader queries sys.databases and sys.master_files: name, file paths, recovery model, compat level exact; row counts of 5 key tables equal the backup manifest. checkdb.txt contains '0 allocation errors and 0 consistency errors' for Sales_QA. Source database untouched.
Proposed partial credit
35% restore with relocated files; 25% settings; 20% data present; 20% CHECKDB output
R03.01.01.02-T03 Import a messy CSV into an existing table with DBeaver
Original instruction
Using DBeaver's data import, load fixtures/vendors_2025.csv (semicolon-delimited, Windows-1252, dd.mm.yyyy dates, comma decimals) into PostgreSQL table purchasing.vendors, mapping columns per docs/mapping.md. Rows failing constraints must go to table purchasing.vendors_rejects instead of aborting.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- DBeaver · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clicktype· Text & forms- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader compares purchasing.vendors to gold: 1,463 valid rows with correct dates, decimals and accented names; 37 rejects in vendors_rejects with original values. No duplicate inserts; existing 200 rows unchanged.
Proposed partial credit
40% valid rows exact; 20% encoding/locale parsing; 25% rejects captured; 15% existing data untouched
R03.01.01.02-T04 Add missing foreign keys from an EER diagram in MySQL Workbench
Original instruction
Reverse-engineer the sandbox MySQL schema 'shop' into an EER model in MySQL Workbench, add the 9 relationships drawn in input/target_erd.png (with ON DELETE rules shown), then synchronize the model to the server. Orphan rows blocking a constraint must be moved to table shop.orphans first.
- Platforms
mac· macOSwin· Windows- Applications
- MySQL Workbench · MySQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
HY· Hybrid- Interaction primitives
drag· Drag & dropclick· Point & clickvis· Visual judgment- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader reads information_schema.REFERENTIAL_CONSTRAINTS: 9 FKs with correct columns and delete rules. Orphans moved (counts match), no other data changed. Saved .mwb model contains the relationships. The mysql CLI and client drivers are not installed on the VM, and performance_schema connection attributes must show the FK DDL came from a MySQL Workbench session (program_name).
Proposed partial credit
50% FKs and delete rules; 25% orphans handled; 15% no other data changes; 10% model saved
R03.01.01.02-T05 Fix a slow query from its execution plan and pin the good plan
Original instruction
Report query fixtures/q_orders.sql takes 40 s on SQL Server 'Sales'. In SSMS, inspect the actual execution plan, add at most one index that fixes it, then use Query Store to force the good plan for the parameterized version. Save the before/after .sqlplan files in output/.
- Platforms
win· Windows- Applications
- SSMS · SQL Server
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
vis· Visual judgmentclick· Point & clickcode· Code & commands- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs the query 5× with 3 parameter values: median ≤1 s each. sys.indexes shows ≤1 new index; sys.query_store_plan has is_forced_plan=1 for the query. Both .sqlplan files parse and the after plan shows an Index Seek on the new index.
Proposed partial credit
40% runtime target; 20% single index; 25% plan forced in Query Store; 15% plans saved
R03.01.01.02-T06 Rename a column safely across views and functions in DataGrip
Original instruction
In DataGrip on PostgreSQL 'erp', rename column customers.cust_nm to customer_name and update all 7 dependent views, 3 SQL functions and 2 triggers so everything compiles and returns the same results.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- DataGrip · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickcode· Code & commands- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: column renamed; all dependent objects valid; each view's output equals its pre-change snapshot (EXCEPT both ways empty); functions return identical results on 20 test calls. No objects dropped and recreated with lost grants (ACL compare).
Proposed partial credit
20% rename; 40% views equivalent; 25% functions/triggers; 15% grants preserved
R03.01.01.02-T07 Find and end the blocking session holding a lock
Original instruction
Application users report the 'orders' page hangs. In pgAdmin's Dashboard and lock views, find the session that blocks the others, record its pid, query and lock in output/blocker.json, and terminate only that session.
- Platforms
mac· macOSwin· Windows- Applications
- pgAdmin · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickvis· Visual judgment- Work product
WP08· App/system state- Conditions
risk· Irreversible/risky- Proposed difficulty
- 3 / 5
Proposed verifier
Grader's scenario has 1 root blocker and 4 waiters (one waiter also blocks others). blocker.json pid and relation must match the root; after action, root session gone and all 4 waiters' sessions still alive and completed. Terminating any other session fails.
Proposed partial credit
40% correct blocker identified; 40% only the blocker terminated; 20% waiters complete
R03.01.01.02-T08 Build an aggregation and schema validation in MongoDB Compass
Original instruction
In MongoDB Compass connected to sandbox db 'support', build an aggregation on tickets that returns median resolution hours per product for 2025 (excluding reopened tickets) and save it as view 'resolution_2025'. Add a compound index for the pipeline and a JSON Schema validator requiring priority in P1–P4.
- Platforms
mac· macOSwin· Windows- Applications
- MongoDB Compass · MongoDB
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickcode· Code & commands- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader reads system.views pipeline and runs the view: values equal gold ±0.01 h. Index exists and explain() shows IXSCAN for the pipeline's $match. Inserting priority 'P5' is rejected by the validator; existing docs untouched.
Proposed partial credit
45% view results; 20% index used; 25% validator; 10% data untouched
R03.01.01.02-T09 Compare and sync staging to production data for three tables
Original instruction
Using DBeaver's data compare, compare tables pricing.products, pricing.discounts and pricing.tiers between the 'prod-copy' and 'staging' PostgreSQL connections. Generate a sync script that makes staging match prod-copy, save it to output/sync.sql, and apply it to staging only.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- DBeaver · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickvis· Visual judgment- Work product
WP08· App/system state- Conditions
risk· Irreversible/risky- Proposed difficulty
- 4 / 5
Proposed verifier
Grader: staging tables now equal prod-copy (row-level hash); prod-copy unchanged (checksum + pg_stat_user_tables n_tup_upd/ins/del unchanged). sync.sql re-applied to a fresh staging copy produces the same result.
Proposed partial credit
50% staging matches; 30% prod-copy untouched (gate); 20% script reproducible
R03.01.01.02-T10 Schedule a nightly maintenance job with alerts in SQL Server Agent
Original instruction
In SSMS, create a SQL Server Agent job 'Nightly_Sales_Maint' that rebuilds indexes over 30% fragmentation, updates statistics and backs up Sales to D:\backup with a dated filename, scheduled daily at 01:30, emailing operator 'DBA Team' on failure via the configured Database Mail profile.
- Platforms
win· Windows- Applications
- SSMS · SQL Server Agent
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clicktype· Text & formscode· Code & commands- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader reads msdb (sysjobs, sysjobsteps, sysschedules, sysoperators, sysnotifications): steps present in order, schedule exact, failure notification set. Starts the job: backup file created with today's date; fragmented index rebuilt (sys.dm_db_index_physical_stats below 5%).
Proposed partial credit
30% job steps; 20% schedule; 20% failure alert; 30% successful run effects
R03.01.01.02-T11 Repair mojibake in customer names
Original instruction
214 rows in the sandbox MySQL table crm.customers show mojibake ('Müller', 'François') from a bad UTF-8/Latin-1 import. Using MySQL Workbench or DBeaver, identify the affected rows, fix them in place, and record ids in output/fixed_ids.txt. Correct rows must not change.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- MySQL Workbench · DBeaver · MySQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickcode· Code & commandsvis· Visual judgment- Work product
WP02· Records/table- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: all 214 rows equal gold UTF-8 names; the 9,786 untouched rows unchanged (hash); fixed_ids.txt equals the affected set. Double-encoded fixes (e.g., 'Ã' remaining) count as failures per row.
Proposed partial credit
70% rows fixed (per row); 20% no collateral changes; 10% id list exact
R03.01.01.02-T12 Add a NOT NULL column to a 120M-row table without downtime
Original instruction
On the sandbox PostgreSQL 16 'events' DB, add column region text NOT NULL DEFAULT 'unknown' to table events, backfill it from sessions in batches, and add an index on (region, ts), all while the provided load generator keeps writing. No lock may block writers for more than 1 s.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- pgAdmin · PostgreSQL
- Interfaces
desk· Desktop GUIterm· Terminal- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader's load generator logs write latencies: max lock wait ≤1 s throughout. Final state: column NOT NULL with default, 100% of pre-existing rows backfilled correctly, index valid (indisvalid true, built CONCURRENTLY).
Proposed partial credit
40% no blocking >1 s; 30% backfill correct; 20% index valid; 10% constraint final
R03.01.01.02-T13 Set up tenant row-level security in SQL Server
Original instruction
In SSMS for database 'SaaS', implement row-level security so users in role TenantUser only see and modify rows of their own tenant (tenant id from SESSION_CONTEXT) in 6 tables, while role SupportAdmin sees all. Demonstrate with EXECUTE AS tests saved to output/rls_tests.sql.
- Platforms
win· Windows- Applications
- SSMS · SQL Server
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs hidden tests as 3 tenant users and SupportAdmin: SELECT counts match per tenant; cross-tenant INSERT/UPDATE blocked (BLOCK predicates). sys.security_policies enabled with filter+block predicates on all 6 tables.
Proposed partial credit
40% filter predicates; 30% block predicates; 20% admin bypass; 10% agent tests run
R03.01.01.02-T14 Unlock an Oracle user and reset its password policy
Original instruction
In Oracle SQL Developer connected as the sandbox admin, unlock account REPORTING, assign it to profile APP_SVC (password life unlimited, 5 failed logins), set a new password from fixtures/secrets.env, and grant roles RPT_READ and CONNECT only.
- Platforms
mac· macOSwin· Windows- Applications
- Oracle SQL Developer · Oracle Database
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clicktype· Text & forms- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Grader queries DBA_USERS (account_status OPEN, profile), DBA_PROFILES limits, DBA_ROLE_PRIVS (exactly 2 roles), and logs in with the new password.
Proposed partial credit
30% unlocked; 25% profile; 30% roles exact; 15% login works
R03.01.01.02-T15 Export an ER diagram and data dictionary for a schema
Original instruction
In DBeaver, produce documentation for PostgreSQL schema 'billing' (23 tables): an ER diagram laid out without overlapping tables exported to output/billing_erd.png, and output/dictionary.csv listing table, column, type, nullable, default, PK/FK and column comment.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- DBeaver · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
drag· Drag & dropclick· Point & clickvis· Visual judgment- Work product
WP03· Visual layout- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
dictionary.csv compared to information_schema/pg_catalog: exact rows. PNG: OCR finds all 23 table names; detected table boxes do not overlap (box IoU 0) and FK lines present (VLM check, 20%). psql, SchemaSpy and Graphviz are absent and the DB password lives only in DBeaver's encrypted connection store, so queries and the ERD must come from DBeaver.
Proposed partial credit
50% dictionary exact; 30% diagram complete and non-overlapping; 20% VLM layout check
R03.01.01.02-T16 Recover from an accidental DELETE with point-in-time recovery
Original instruction
At 14:07 someone ran DELETE FROM payments WHERE status='pending' on the sandbox PostgreSQL primary (see fixtures/incident.md). Using the pgBackRest repository and WAL archive, recover the deleted rows to the moment before the delete and reinsert them into the live database without losing writes made after 14:07.
- Platforms
lin· Linux- Applications
- PostgreSQL · pgBackRest · pgAdmin
- Interfaces
term· Terminaldesk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP08· App/system state- Conditions
risk· Irreversible/risky- Proposed difficulty
- 5 / 5
Proposed verifier
Grader: payments now equals gold (deleted rows restored, post-14:07 inserts/updates preserved). Live cluster never restored over (system identifier unchanged; uptime continuous). Restore performed on a separate PITR instance (log check).
Proposed partial credit
30% PITR instance at correct target time; 40% rows restored; 20% later writes preserved; 10% live cluster untouched
R03.01.01.02-T17 Encrypt a sensitive column with the Always Encrypted wizard
Original instruction
In SSMS, use the Always Encrypted wizard to encrypt column dbo.Patients.SSN (deterministic) and dbo.Patients.Notes (randomized) in database 'Clinic' with a new column master key in the local Windows certificate store, then confirm the app query in fixtures/app_query.sql still works with column encryption enabled.
- Platforms
win· Windows- Applications
- SSMS · SQL Server
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & click- Work product
WP08· App/system state- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader: sys.columns encryption_type 1 for SSN, 2 for Notes; CMK and CEK metadata present; raw SELECT without column encryption returns varbinary; with Column Encryption Setting=Enabled returns plaintext equal to original; row count unchanged.
Proposed partial credit
40% columns encrypted with correct types; 20% keys; 30% data round-trips; 10% app query works
R03.01.01.02-T18 Diff two schema versions and apply a migration in DataGrip
Original instruction
Compare PostgreSQL databases 'app_v12' and 'app_v13' in DataGrip, review the differences, and generate a migration script that brings a fresh copy of v12 to v13 without dropping data in renamed columns. Apply it to 'app_dev' (a v12 copy) and save the script to output/migrate.sql.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- DataGrip · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickvis· Visual judgmentcode· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: schema of app_dev equals app_v13 (pg_dump --schema-only normalized diff empty); data in 2 renamed columns preserved (not drop+add); migrate.sql reproduces the result on another v12 copy.
Proposed partial credit
40% schema equality; 35% renamed data preserved; 25% script reproducible
R03.01.01.02-T19 Find the top statements by latency in MySQL Workbench
Original instruction
Using MySQL Workbench's Performance Reports on the sandbox MySQL 8 server under the provided load, identify the 3 statement digests with the highest total latency and the table each mostly scans. Write output/top3.json with digest text, total latency, and table.
- Platforms
mac· macOSwin· Windows- Applications
- MySQL Workbench · MySQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickvis· Visual judgment- Work product
WP12· Answer/decision- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader compares to performance_schema.events_statements_summary_by_digest snapshot taken during the load window: same 3 digests in order; latency within 5%; tables match seeded slow queries.
Proposed partial credit
50% digests and order; 25% latency values; 25% tables
R03.01.01.02-T20 Migrate a MySQL database to PostgreSQL with DBeaver
Original instruction
Migrate sandbox MySQL database 'legacy_crm' (41 tables, enums, tinyint booleans, zero dates, auto_increment) to PostgreSQL 'crm' using DBeaver's data transfer. Fix type mappings, sequences and constraints, then verify row counts and per-table checksums, recording them in output/verify.csv.
- Platforms
mac· macOSwin· Windowslin· Linux- Applications
- DBeaver · MySQL · PostgreSQL
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
click· Point & clickcode· Code & commandsvis· Visual judgment- Work product
WP08· App/system state- Conditions
long· Long-horizonmultiapp· Multi-app- Proposed difficulty
- 5 / 5
Proposed verifier
Grader: 41 tables exist with mapped types per docs/type_map.md; row counts and normalized checksums equal source; sequences set to max(id)+1; FKs and unique constraints present; zero dates mapped to NULL. verify.csv matches grader's numbers.
Proposed partial credit
35% data equality; 20% type mapping; 35% sequences and constraints; 10% verification file
Mapped research references
Existing benchmark-to-class links from the research catalog. They are not record-level annotations, executions or product endorsements.