A SQL Developer is responsible for managing data in a relational database. Database Providers like Microsoft, Oracle and MySql have their own IDE for managing data. For example Microsoft has SQL Server Management Studio for this purpose and Oracle has Oracle Developer Studio. A SQL Developer makes use of these IDEs for managing data.
Today I would like to list down the most common tasks that every SQL Developer must be familiar with as these will often come up as a requirement in the course of their day to day to work. This list will also serve as a guideline for anyone who wishes to advance their career towards becoming a SQL Developer.
In no particular order, here’s the list of tasks:
- Connecting to different SQL Servers using Windows and SQL Server Authentication.
- Creating new databases in many different ways.
- Backing up and restoring databases.
- Designing and Normalizing Tables as per need.
- Using Primary Keys, Identity Columns and Foreign Keys for table creation.
- Altering Tables and Columns.
- Properly using Data Types and Column Size Limits.
- Writing basic to advanced level queries for SELECT, UPDATE, INSERT and DELETE.
- Writing table JOIN statements.
- Filtering Rows using WHERE clauses and JOIN statements.
- String manipulation using functions like Replace, Stuff, Trim and Substring.
- Creating tables on the fly.
- Using Cursors for looping.
- Writing Stored Procedures.
- Writing Functions.
- Writing Triggers.
- Using Linked Servers.
- Adding Indexes on tables.
- Using aggregator functions like Sum, Count, Group By, Order By, Row_Number and Distinct.
- Accessing table and column information across databases using Information_Schema.
- Generating Scripts to re-create database objects like tables and functions.
- Importing Data from external sources (mainly csv files sand excel sheets).
- Creating and authorizing Database Users.
Note: Many of these tasks can be completed in two different ways i.e. either using the GUI or using Structured Query Language.
Using the GUI
While it may be easier to operate through a User Interface for a newbie as he doesn’t have to remember the necessary queries, but on the long run it is better and also important to be fluent in performing these tasks writing queries.
Once you get the hang of it, you will find that writing query is more easier and faster than operating through UI as you no longer have to worry about where a particular function is located to perform a task. You just have to write the query on the query editor window.
The intention for this post was to identify the common operations that a SQL Developer must be fluent with in order to work in any professional environment. While this is not an an inclusive list of tasks and I may have left out a few other essential tasks, but nevertheless, if you are familiar with the tasks listed above, surely you can call yourself a SQL Developer ;).