In this post you will know what are the most valuable skills a SQL developer must have. Standard Query Language (SQL) is used by relational databases, including IBM's DB2, Oracle, Microsoft's SQL Server, and open source database MySQL.SQL was devised for manipulating data in relational database tables. SQL skills are applicable to any application or database development, support and administration role. Some of them being:
Security:
This should always be first. Understand SQL injection and parameterized queries.
Restoring a database. Notice how I did not say, "making a backup." That will obviously be a necessary step, but it's far more important to know how to restore, because otherwise your backups are for naught.
Query load monitoring: How do you identify which queries account for the greatest share of your database load?
Query optimization: Once you find the bad queries, improve them.
Creating indexes
Rewriting query logic
Partitioning or sharding
Denormalization
Using caches or message queues in your application
DBMS tuning: Most databases have many options to tune resource use. For example, allocating as much RAM as possible to cache data and indexes.
Capacity planning:
Know the limits of your server platform, how many queries per second can it execute, how much IO do those queries demand of your disk array, how much space do you have for data, indexes, and logs. Measure how much of these resources you are using now, and how does that compare to the maximum. Is your resource usage increasing? How long will it be before you run out of capacity?
SQL types: Understand the characteristics of different SQL types as they pertain to logic, performance, and storage. I'll include SQL NULLs as part of this entry, although that could be a separate item itself.
Transactions: Understand the importance of grouping changes and committing them as an atomic unit of work. Understand isolation levels.
Set-based data operations. Many developers think of a database as a collection of individual rows, and fail to use powerful features of SQL to update sets of data in one query. Developers who use object-relational mapping (ORM) tools are especially guilty of this.
High availability: What happens if/when your database server shuts down, or needs to be upgraded? Does your application handle this gracefully? Can you queue data changes temporarily until the database is back online? Do you have a spare database instance for failover? How do you get the spare back in sync with the primary?
In simple terms the following skills must have every sql developer:
Learn the joins properly.Specially OUTER JOIN .
-> Get a clear view of Primary key and Foreign key.
-> ACID properties along with E-R diagram
-> Query optimization and performance tuning(Use of tools like explain plan,tkproof)
use of packge DBMS_PROFILER for performance issues.
->Normalization ( In data modelling its necessary) and Autonomous Transaction.
-> Use of Index,Materlized view(refreshing techniques)
-> Context Switching and Referential integrity.
-> Dynamic SQL
->use of functions like SUBSTR,INSTR,LEAD,LAG,CASE,DECODE,FLOOR.
-> Getting into named blocks like PROCEDURES/FUNCTIONS/TRIGGERS/CURSORS/COLLECTIONS/PACKAGES and know there use in a real life requiremnet. (In PLSQL only)
Security:
This should always be first. Understand SQL injection and parameterized queries.
Restoring a database. Notice how I did not say, "making a backup." That will obviously be a necessary step, but it's far more important to know how to restore, because otherwise your backups are for naught.
Query load monitoring: How do you identify which queries account for the greatest share of your database load?
Query optimization: Once you find the bad queries, improve them.
Creating indexes
Rewriting query logic
Partitioning or sharding
Denormalization
Using caches or message queues in your application
DBMS tuning: Most databases have many options to tune resource use. For example, allocating as much RAM as possible to cache data and indexes.
Capacity planning:
Know the limits of your server platform, how many queries per second can it execute, how much IO do those queries demand of your disk array, how much space do you have for data, indexes, and logs. Measure how much of these resources you are using now, and how does that compare to the maximum. Is your resource usage increasing? How long will it be before you run out of capacity?
SQL types: Understand the characteristics of different SQL types as they pertain to logic, performance, and storage. I'll include SQL NULLs as part of this entry, although that could be a separate item itself.
Transactions: Understand the importance of grouping changes and committing them as an atomic unit of work. Understand isolation levels.
Set-based data operations. Many developers think of a database as a collection of individual rows, and fail to use powerful features of SQL to update sets of data in one query. Developers who use object-relational mapping (ORM) tools are especially guilty of this.
High availability: What happens if/when your database server shuts down, or needs to be upgraded? Does your application handle this gracefully? Can you queue data changes temporarily until the database is back online? Do you have a spare database instance for failover? How do you get the spare back in sync with the primary?
In simple terms the following skills must have every sql developer:
Learn the joins properly.Specially OUTER JOIN .
-> Get a clear view of Primary key and Foreign key.
-> ACID properties along with E-R diagram
-> Query optimization and performance tuning(Use of tools like explain plan,tkproof)
use of packge DBMS_PROFILER for performance issues.
->Normalization ( In data modelling its necessary) and Autonomous Transaction.
-> Use of Index,Materlized view(refreshing techniques)
-> Context Switching and Referential integrity.
-> Dynamic SQL
->use of functions like SUBSTR,INSTR,LEAD,LAG,CASE,DECODE,FLOOR.
-> Getting into named blocks like PROCEDURES/FUNCTIONS/TRIGGERS/CURSORS/COLLECTIONS/PACKAGES and know there use in a real life requiremnet. (In PLSQL only)
No comments:
Post a Comment