There’s a lot to learn with SQL. Once you understand the basics, you’ll feel comfortable running queries on tables.
But what else is there to know? What other things do you need to know about, and which topics are more advanced?
I’ve broken down the many different topics of SQL and databases in this SQL Roadmap.
This is based on my own opinion and some research I’ve done.
I’ve broken it up into three sections: Beginner, Intermediate, and Advanced.
They don’t align to certifications, or particular job roles. They are just the different skills that would apply to someone who I would consider a beginner, intermediate, or advanced.
I’ve written posts on many of these topics, which I have linked to below if you want to know more. I plan on writing more posts on these topics and will add them as they are written. So, if something looks interesting to you here, but there’s no post yet – it’s coming soon!
Let’s look at the roadmap.
Beginner
- Understand what a database is, and the related objects (table, column, row).
- Understand what SQL is, what it does, and relationship between database and SQL
- Understand what an ERD (Entity Relationship Diagram) is and how it relates to a database
- SELECT data from a table, including all columns and specific columns
- What is the Dual Table in Oracle?
- More posts coming soon!
- WHERE clauses (filtering data using =, IN, BETWEEN, etc):
- The Difference Between WHERE and HAVING Clause
- More posts coming soon!
- Ordering results with the ORDER BY clause
- Simple aggregate functions, aggregate with a group by, HAVING clause
- Updating data with the UPDATE statement
- Joins – inner, left, right, outer
- Creating, altering, and dropping tables.
- Delete data from a table
- Insert data into a table
- Temporary tables and their usage
- A basic idea of what indexes are for
- Primary keys: what they are
- Foreign keys: what they are, how to work around them (cascading delete, etc)
- Understand basics of transactions and locking
- Understand constraints
- Use functions within SQL
- Oracle Date Functions: The Complete Guide
- Oracle SQL Functions list which includes all functions such as TO_DATE and ROUND
- Describe the purpose of DML and DDL
- Describe the data types available for columns
- Operators such as IN, EXISTS
Intermediate
- How indexes work, the different types of indexes
- Understand subqueries, able to think through using them in joins, using subqueries in different places (e.g. SELECT, FROM, WHERE)
- Pivot and Unpivot
- PL/SQL Cursors
- Self join: what is it used for, able to perform a self join
- Can generate complex results using GROUP BYs and aggregate functions (see above links)
- Basic profiling for monitoring/debugging, such as reading a log file
- Understand the difference between OLAP and OLTP structures and know when to use OLAP
- Triggers, when to use them, when not to use them
- Understand transactions and can layer or next transactions
- Normalisation, what it is, how to follow the normalisation process to 3NF
- PL/SQL, creating functions or stored procs
- Usage of other objects such as sequences, views, materialised views, synonyms
- Understand regular expressions and their functions
- Know which functions exist and how to use them in various ways to get what is needed (analytic vs aggregate, different parameters, nested functions)
- Understand the Interval data type, what it’s used for, how it’s different from dates
- What character sets are
- Partition By
- Use ampersand substitution to restrict and sort output at runtime:
- Use SQL row limiting clause
- Nonequijoins
- Correlated subqueries – select, update, and delete
- WITH clause
- Single row and multi row subqueries
- SET operators – what they are, using them, control the order of rows returned
- Truncate data
- Drop columns and set columns to UNUSED
- Create and use external tables
- Query various data dictionary views
- System privileges vs object privileges
- Grant privileges on tables and a user
- Tell the difference between privileges and roles
- Create simple and complex views with visible/invisible columns
- Perform flashback operations
- Merge rows in a table
Advanced
- Generate and read an execution plan, and understand how different parts of a query affect it:
- Tune queries with execution hints without messing up performance
- Profile and use traces for identifying and understanding statistics of executions
- Know what the data structures are on the disk
- Use performance counters and understand what the database load and behaviour is from monitoring them
- Know how to design an OLAP cube and do advanced data mining with it
- How to use triggers safely with minimal risk
- How to use distributed transactions even with layers
- How to follow the normalisation process to the remaining normal forms
- Best practices for database design in many different situations, common database field guidelines
- Know how and when to denormalise a database
- How to set up distributed transactions across a database and integrated systems
- Setup a database for high availability, data recovery, etc
- Deep understanding of internal Oracle DB workings
- Setup a data warehouse and create ETL processes to populate the data
- PL/SQL understanding of the fundamentals and advanced topics, such as loops, IF THEN ELSE structures, variable types, rowtypes, and so on.
I hope this gives you a clearer understanding of what’s involved in database development and SQL.
Have I missed anything on this list? Let me know in the comments below.
