What do you with you knew when you started in SQL?

I have a request.  I’ve been given the opportunity to teach a course at our local community college on Database Design and Development and they’ve given me room to come up with my own curriculum.  I want to create a balance of theory, practice, quizzes, and projects, but one of the things I feel like I have a great opportunity to do is to pass on some really great knowledge that I’ve picked up over the years.

Please comment below and give me examples of things you wish you knew sooner.  I’d like to put as many tools in my students toolboxes before they get moving.  Feel free to leave advice on teaching in a formal setting, keeping people’s attention and the most important thing….

How do I keep people’s attention if the class if FOUR HOURS LONG?!?!?  Two hour lecture, two hour lab, once a week.

as an example – someone mentioned on twitter that one of the most important things they’ve taught their students is the practice of always writing out a select statement to get the right number of records, THEN changing the select to update/delete, etc so as to not inadvertently have an Oh $#!& moment.  (my apologies, I went back through my feed and couldn’t find the post posted by Brian Hogan)

6 thoughts on “What do you with you knew when you started in SQL?

  • For Developers, the importance of truly understanding JOIN types and when to use them cannot be overstated. Indexes as well. I faked my way through both of those as a self-taught developer with Access, then SQL Server. That’s the T-SQL. For schema, proper design of tables (normalization, data types) is a must.

  • Write queries for human readability first.

    If you can show the query to someone else who’s never seen your database before, and in a matter of seconds they can explain what your query is doing, then odds are the query optimizer will do a pretty good job of building an efficient query plan. Plus, when you come back to the query months or years later to fix it, you’ll be able to pick right up where you left off.

    If the other person takes a deep breath and asks for a pen and some scratch paper to figure out what you’re doing, odds are it’s not going to perform or be easily maintainable.

  • I’ll piggyback on Brent’s reply: design databases as human readable.

    Naming things is hard, but one thing that can make it significantly easier is to use descriptive language. So, for example, a column called id could be improved by calling it Customer_ID. When these attributes are referenced in other contexts, like in a query, it becomes much easier to understand and it reduces the work of aliasing columns and tables in every query.

    Underscores and capitalization are not stylistic preferences. They_Literally_Make_Everything_Easier_To_Read. andimsureyoullfindawaytoagree. MOSTEVERYONEDOESAFTERAFEWEXAMPLES.

    Don’t use hungarian notation even though there are a multitude of bad examples. Never prefix a proc with usp, because it only teaches your eyes to ignore it and it certainly spoils sorting. Never prefix a table or view (tbl or vw), because applications should treat tables and views as the same type of thing – a place to get data.

    Do use application specific conventions. Use schemas to group object types – sales, hr, dimension, fact, etc. Objects in applications might have type prefixes, like a set of tables: Customer, Customer_Contact_Info, Customer_Orders.

    With intellisense and code completion, there’s really no excuse not to be more verbose. This even acts as a form a self documentation, which if you didn’t do any documentation, at least you made it so anyone can find their way around.

    • Hey Mark, my apologies, thanks for the comment, didn’t realize I hadn’t approved it.

Comments are closed.