Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1537
Members Online:
3
Guests Online:
4

Total Post History
Posts:
80278
Topics:
18352

7-Day Post History
New Posts:
9
New Topics:
2
Active Topics:
3

Our newest member
Alpha

Other

FAQ
All times are UTC [ DST ]

Google Ads

How much should a sql developer know

Microsoft SQL Server
Please wait...

How much should a sql developer know

Postby SQLDenis on Sun Jul 11, 2010 3:28 pm

This was brought to my attention by Remou...thanks


basically someone posted on stackoverflow what he thought were basic, intermediate and advanced topics. Here is the list

http://stackoverflow.com/questions/2054 ... vanced-sql

Basics

'
1. SELECTing columns from a table
2. Aggregates Part 1: COUNT, SUM, MAX/MIN
3. Aggregates Part 2: DISTINCT, GROUP BY, HAVING

Intermediate

'
1. JOINs, ANSI-89 and ANSI-92 syntax
2. UNION vs UNION ALL
3. NULL handling: COALESCE & Native NULL handling
4. Subqueries: IN, EXISTS, and inline views
5. Subqueries: Correlated
6. WITH syntax: Subquery Factoring/CTE
7. Views


Advanced Topics

'
* Functions, Stored Procedures, Packages
* Pivoting data: CASE & PIVOT syntax
* Hierarchical Queries
* Cursors: Implicit and Explicit
* Triggers
* Dynamic SQL
* Materialized Views
* Query Optimization: Indexes
* Query Optimization: Explain Plans
* Query Optimization: Profiling
* Data Modelling: Normal Forms, 1 through 3
* Data Modelling: Primary & Foreign Keys
* Data Modelling: Table Constraints
* Data Modelling: Link/Corrollary Tables
* Full Text Searching
* XML
* Isolation Levels
* Entity Relationship Diagrams (ERDs), Logical and Physical
* Transactions: COMMIT, ROLLBACK, Error Handling



I think some of the advance stuff should belong in intermediate...

Also the following..JOINs, subqueries, UNION vs UNION ALL that is all beginners stuff




I want to turn this into a blogpost...what do you think a beginner, intermediate and advanced user should know

For me beginner == < 2 years
intermediate == between 2 and 5 years
advance == 5 + years of experience
Expert == 10 + years of experience

So knowing that is Data Modelling: Primary & Foreign Keys really advanced?


Let hear your thought

Also I would like to add an Expert category where someone has 10 years experience..now star thinking about trace flags, parameter sniffing etc etc
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: How much should a sql developer know

Postby tarwn on Sun Jul 11, 2010 5:57 pm

I think you would need some clarification on whether you are categorizing the skills (ie, these are basic, intermediate, and advanced skills) or listing skills someone of the appropriate rank should have. For instance, most of the data modeling I would qualify as less than advanced in difficulty, but I might not require them from a SQL Developer that was in the basic or intermediate role.

It looked like the original post over at StackOverflow was defining tasks by their level of difficulty and your talking about both level of difficulty and level of expectations for job levels.

Not knowing much about the SQL Developer role and having a completely different background, I'm not sure I can offer many suggestions for relative level of importance or skill-level, but one thing that strikes me is that there looks like three major sets of skills:
1) Procedural level - understanding the language and constructs, creating procedures and views, CTEs, transactions, etc
2) Tuning and Optimization - understanding the performance trade-offs, being able to actively optimize, measuring to detect problems, etc
3) Modeling - Understanding the data model, Defining new models and turning concepts into databases, revising existing models for better storage/performance/scalability/etc

I think most of (1) belongs in the basic and intermediate buckets (transactions really shouldn't be in the advanced group, for instance, and I agree with your comments on UNION, etc). I couldn't really speak to (2) and (3) since my background and positions required me to learn all of (3) much earlier than most items from (2), but that may not be the same as the path a SQL Developer would need to take.

I'm interested with how this turns out. I think it will make for an interesting topic.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 809LTD Gold - Rating: 809LTD Gold - Rating: 809LTD Gold - Rating: 809LTD Gold - Rating: 809
LTD Gold - Rating: 809LTD Gold - Rating: 809LTD Gold - Rating: 809LTD Gold - Rating: 809LTD Gold - Rating: 809
LTD Gold - Rating: 809
 
Posts: 3638
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA

Re: How much should a sql developer know

Postby SQLDenis on Sun Jul 11, 2010 6:59 pm

Sorry, that list is not what I think it should be, it is the list I grabbed from the link.


Dynamic SQL in my opinion is certainly not advanced, it could be intermediate when you factor in plan reuse, ootput parameters etc etc

What I want to get out of this is the following

I have you 5 years of experience (Advanced) with programming with SQL Server then you should know the following things. And the same for the other levels

I probably worded it wrong

Ultimately I want to have a nice Wiki article that lists the skill level, the skills and then links to some great contents about those skills
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: How much should a sql developer know

Postby damber on Sun Jul 11, 2010 7:26 pm

'years' is a crappy measurement of skills

it is also a crappy measurement of the quality of experience

just sayin'
a smile is worth a thousand kind words, so smile, it's easy! :-)


CODE: $5
WORKING CODE: $500
PROPERLY DESIGNED & WORKING CODE: Priceless
User avatar
damber
LTD Admin
LTD Admin
LTD Silver - Rating: 660LTD Silver - Rating: 660LTD Silver - Rating: 660LTD Silver - Rating: 660LTD Silver - Rating: 660
LTD Silver - Rating: 660LTD Silver - Rating: 660LTD Silver - Rating: 660LTD Silver - Rating: 660LTD Silver - Rating: 660
 
Posts: 3134
Joined: Tue Oct 09, 2007 1:48 pm
Location: North Wales, UK

Re: How much should a sql developer know

Postby SQLDenis on Sun Jul 11, 2010 7:39 pm

damber wrote:'years' is a crappy measurement of skills

it is also a crappy measurement of the quality of experience

just sayin'



True..you can do 10 years of <insert here> programming and never advance from what you learned the first year.......there are user on forums like that..they ask the same kind of questions 5 years in a row..a user (NULL IS NULL or NULL IS NOT NULL) comes to mind

But in general when we are looking for developers we either looks for intermediate or advanced developers...and then of course you eliminate 90% during the phone interview
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: How much should a sql developer know

Postby Thirster42 on Mon Jul 12, 2010 2:38 pm

My opinion:

basic:
Select
Inner Join and Left/Right Outer Joins
Aggregates/Group By
Views
Stored Procedures


Intermediate:
NULL Handling
Sub-Queries
CTE's
Functions
Cursors
Triggers
Dynamic SQL
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: How much should a sql developer know

Postby SQLDenis on Mon Jul 12, 2010 6:17 pm

I like to only focus on the SQL Engine part of this, so no SSRS, SSAS, SSIS or Notification Services

If you can write/run it from a SSMS query window it is fair game
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: How much should a sql developer know

Postby SQLDenis on Fri Feb 04, 2011 1:59 pm

I would maybe create the blogpost this week so I need your input

so for these

    beginner == < 2 years
    intermediate == between 2 and 5 years
    advance == 5 + years of experience
    Expert == 10 + years of experience


what should a person be expected to know?
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: How much should a sql developer know

Postby Thirster42 on Fri Feb 04, 2011 3:57 pm

SQLDenis wrote:I would maybe create the blogpost this week so I need your input

so for these

    beginner == < 2 years
    intermediate == between 2 and 5 years
    advance == 5 + years of experience
    Expert == 10 + years of experience


what should a person be expected to know?


beginner:
simple select statement (one table, where clause)
simple inner join statement (two tables)
simple outer join (left or right) statement (two tables)
Understanding of parent-child key relationships (1-1, 1:m, and the dreaded m:m)
create views
create stored procedures
simple data modeling (2-5 tables)


intermediate:
simple cte's
derived tables
complex data modeling (6-15 tables)



advanced:
recursive cte's
dynamic sql
advanced data modeling (16+ tables)




this is form my perspective, relative to how difficult i found it to learn initially, and what order i learned it in
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: How much should a sql developer know

Postby Kermit on Fri Feb 04, 2011 5:00 pm

It's down to exposure to a particular technology, patterns & practices, framework and importantly your peers.

So based on the above, you can identify certain items in the list that you could say:

"If person A has done 1 years worth of work dealing with Database Architecture and Data Modelling" then you know he she has potentially done some advanced work.

The above on it's own doesn't quantify an overall measure however when combined with the other items on the list (covering other areas of interest and expertise), you can build an overall scale to measure against.

I like to visualise this as the example used to explain joins:

Draw a circle, overlap another circle, intersect a circle etc...

Experience in this field could be measured in this way, have first circle (in the middle) representing the basic (core) skills, then then other areas overlapping, intersecting or remaining off to side as more circles.

I should really draw something but I have run out of crayons.

Oh and I can only tick off about 8 items on that list which makes me "A person who fell over a database on his way out of the fire exit"
Last edited by Kermit on Fri Feb 04, 2011 5:02 pm, edited 1 time in total.
Reason: can't type for isht
User avatar
Kermit
LTD Admin
LTD Admin
LTD Bronze - Rating: 92LTD Bronze - Rating: 92
 
Posts: 520
Joined: Thu Oct 11, 2007 11:14 am
Location: United Kingdom

Re: How much should a sql developer know

Postby SQLDenis on Tue Feb 22, 2011 2:30 am

User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: How much should a sql developer know

Postby SQLDenis on Tue Feb 22, 2011 3:27 pm

Getting some good comments...don't hesitate to add your own
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
LTD Gold - Rating: 3451LTD Gold - Rating: 3451LTD Gold - Rating: 3451
 
Posts: 21669
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated