Source: Levelica Canonical: https://www.levelica.com/en/guides/sql-and-databases # SQL and Databases ## Summary Structuring data and querying it, from single-table reads to architecture and standards work. ## Description Working with SQL and databases means deciding how data gets stored and how you get it back out. You start with single-table lookups, then move to queries that join several tables. Next come schemas drawn from real requirements, tuning that starts with an execution plan, and architecture calls that split transaction processing from analytics. What separates the levels isn't fluency with one product's features. It's whether you can explain why you chose that structure and that storage technology. --- ## Level Overview | Level | Proficiency Stage | Hashtags | | ----- | ------------------------ | ------------------------------------------------------------------ | | Lv.1 | Query Reader | #Select #Where #TableStructure #PrimaryKey | | Lv.2 | Data Retriever | #Join #GroupBy #Subquery #DataTypes | | Lv.3 | Schema Builder | #Normalization #IndexBasics #Transaction #DataModeling | | Lv.4 | Performance Tuner | #QueryOptimization #ExecutionPlan #LockContention #Partitioning | | Lv.5 | Data Architect | #SchemaEvolution #Replication #WorkloadSeparation #RetentionPolicy | | Lv.6 | Platform Governance Lead | #DataGovernance #PlatformStrategy #StorageEngine #TeamEnablement | | Lv.7 | Field Shaper | #StandardsAuthorship #DatabaseResearch #DataLegacy | > Proficiency mapping source: Based on the SFIA 9 Database design (DBDS) responsibility levels and the SQLD/SQLP certification ladder from the Korea Data Agency (KDATA). --- ## Level 1 #Select #Where #TableStructure #PrimaryKey ### Summary Pulling rows that match a condition from a single table. ### Description You've just started working with databases. You understand that a table is rows and columns, and that every column carries a data type. Conditions and sorting get you the rows you want. You also know a missing value won't turn up under a normal comparison. Working across several tables at once is still out of reach. ### Checklist - [ ] Can explain that a table is made of rows and columns and that each column has a data type - [ ] Can pull rows matching a condition from a single table using SELECT and WHERE - [ ] Can sort results and cap the row count to take only the top few - [ ] Can explain with an example how a primary key uniquely identifies a row - [ ] Can recognize that a missing value won't match a normal comparison operator and handle it - [ ] Can connect to a practice database and list its tables and column structures > If you've checked off most of this list, you're ready for the Data Retriever stage, writing queries that link tables together. A relational database gets its power from storing data in separate tables and stitching them back together on demand. --- ## Level 2 #Join #GroupBy #Subquery #DataTypes ### Summary Joining tables and aggregating them into reportable results. ### Description You link two or more tables, pull only the columns you need, and group rows to produce sums and counts by category. You can reproduce the gap between an inner join and an outer join with real data and explain it. You change data too, checking the affected row count as you go. You can build the result someone asked for, but not the structure behind it. ### Checklist - [ ] Can join two or more tables and read back only the columns you need - [ ] Can produce sums, averages, and counts by category using grouping and aggregate functions - [ ] Can use a subquery to feed one query's result into another query's condition - [ ] Can change data with INSERT, UPDATE, and DELETE and check the affected row count - [ ] Can reproduce the difference between an inner join and an outer join with real data and explain it - [ ] Can reshape raw data into report format using date and string functions > If you've checked off most of this list, you're ready for the Schema Builder stage, turning requirements into tables nobody handed you. The biggest jump in autonomy sits between querying someone else's structure and deciding the structure. --- ## Level 3 #Normalization #IndexBasics #Transaction #DataModeling ### Summary Designing schemas from requirements and handling transactions. ### Description You listen to business requirements, sort out entities and relationships, and design the schema yourself. You normalize, but you can point to the spots where denormalizing pays off and say why. You wrap several changes in one transaction, so a failure rolls all of them back. You add indexes and measure how query time shifts. Your backup and recovery procedure is written down. ### Checklist - [ ] Can draw a data model of entities, attributes, and relationships from business requirements - [ ] Can normalize to third normal form and justify where denormalization pays off - [ ] Can group several changes into a transaction that rolls back on failure - [ ] Can add an index to a frequently queried column and measure the change in query time - [ ] Can earn a data certification such as SQL Developer (SQLD) - [ ] Can independently write a schema of 10 or more tables that meets operational requirements - [ ] Can document backup and recovery procedures and run a real recovery test > If you've checked off most of this list, you're ready for the Performance Tuner stage, making the queries you already write survive real load. The query processing and optimization chapters laid out by Silberschatz et al.(2019) are the theoretical starting point for this shift. --- ## Level 4 #QueryOptimization #ExecutionPlan #LockContention #Partitioning ### Summary Reading execution plans to clear slow queries and contention. ### Description You read an execution plan, find the wasteful step, and rewrite the query around it. You reproduce lock contention and deadlocks, then clear them by adjusting isolation levels. For large tables, you partition or apply a retention policy to cut what they cost to keep. When something breaks, you trace the cause through logs and metrics and lead the recovery. ### Checklist - [ ] Can read an execution plan and point to the step where a full scan happens - [ ] Can record a case where you cut a slow query's response time by half or more - [ ] Can reproduce the cause of lock contention or a deadlock and clear it by adjusting isolation levels - [ ] Can partition a large table or apply a retention policy to cut its upkeep cost - [ ] Can rewrite a complex reporting request using window functions and common table expressions - [ ] Can decide which indexes to add or drop based on statistics and execution plan changes - [ ] Can trace the cause of an outage through logs and metrics and lead the recovery procedure > If you've checked off most of this list, you're ready for the Data Architect stage, choosing which system each workload belongs on. In the SFIA 9 Database design definition, this is the boundary where your scope widens from tuning single queries to advising on platform choice. --- ## Level 5 #SchemaEvolution #Replication #WorkloadSeparation #RetentionPolicy ### Summary Splitting transactional and analytical work, designing failover. ### Description You separate transaction processing from analytical demand and design a structure for each. You set recovery time and recovery point targets as numbers, then build replication and failover to hit them. Your criteria for choosing storage technology are documented, and you advise the wider organization on them. Schema changes ship without downtime. You coach developers, lifting query quality across the whole team. ### Checklist - [ ] Can separate transactional and analytical requirements and design a data structure for each - [ ] Can design replication and failover with numeric recovery time and recovery point targets - [ ] Can document selection criteria for relational and non-relational storage and advise the organization - [ ] Can earn an advanced certification such as SQL Professional (SQLP) or show equivalent work - [ ] Can design and apply a procedure that ships schema changes without service downtime - [ ] Can coach 5 or more developers on query writing and modeling and cut review findings - [ ] Can set a data policy covering retention, access rights, and personal data handling > If you've checked off most of this list, you're ready for the Platform Governance Lead stage, writing rules that bind teams you never sit on. Designing one system well is one job. Defining the criteria many teams work to is another. --- ## Level 6 #DataGovernance #PlatformStrategy #StorageEngine #TeamEnablement ### Summary Setting the data standards and governance an organization runs on. ### Description You set the data architecture standard for the whole organization and roll it out across teams. You design and run governance that ties quality metrics to ownership and change approval. In public, you argue technology choices from how storage engines actually work. Your training programs and open source contributions reach past your own organization. ### Checklist - [ ] Can establish an organization-wide data architecture standard and apply it across teams - [ ] Can design and run data governance covering quality metrics, ownership, and change approval - [ ] Can argue a technology choice publicly from how a storage engine or query processor works - [ ] Can present data design methodology at an industry conference or in a trade publication - [ ] Can design and run a training program for 20 or more data engineers - [ ] Can show merged contributions to an open source database or related tooling > If you've checked off most of this list, you're ready for the Field Shaper stage, authoring the standards and methods the field runs on. When rules that hold inside your organization start getting cited outside it, your reach widens to the whole industry. --- ## Level 7 #StandardsAuthorship #DatabaseResearch #DataLegacy ### Summary Leaving a legacy in data design methodology and standards. ### Description You redefine how data gets designed and handled from the ground up. The modeling methodology you created is adopted by outside organizations, and your writing and standards documents get cited in both practice and research. Your work on certification systems or standards bodies leaves a record behind. The architects you trained now work on their own. ### Checklist - [ ] Can show that a data modeling methodology you designed is used by 3 or more outside organizations - [ ] Can verify that your database writing, papers, or standards documents have 10 or more outside citations - [ ] Can show a record of work on data standards bodies or certification system design - [ ] Can show that a tool or system you built has been maintained and extended for 5 or more years - [ ] Can mentor 5 or more data architects who go on to work independently > If you've checked off most of this list, you're standing at the front edge of how data gets handled. You shape the design criteria the next generation will work from. The task here is writing new standards, not restating the textbook ones Silberschatz et al.(2019) already settled. --- ## References - [SQLP and SQLD National Certification Examinations](https://www.dataq.or.kr/www/sub/a_03.do) — Korea Data Agency (KDATA) — Registered Private Qualification No. 2022-04 - [SFIA 9 — Database design (DBDS)](https://sfia-online.org/en/sfia-9/skills/database-design) — SFIA Foundation - [Database System Concepts, 7th Edition](https://www.db-book.com/) — Silberschatz, Korth & Sudarshan — McGraw-Hill (2019) - [Errors and Complications in SQL Query Formulation](https://jyx.jyu.fi/handle/123456789/59322) — Taipalus, Siponen & Vartiainen — ACM Transactions on Computing Education 18(3), Art. 15 (2018)