Basic SQL Training

Discussion in 'Programming' started by chari, Sep 26, 2012.

  1. chari

    chari Senior member

    Joined:
    Oct 14, 2004
    Messages:
    265
    Likes Received:
    0
    Couldn't find a better place to post this so here goes...

    Does anyone know of a good, free resource that has basic SQL query training/tutorials/etc?

    There is a job opening in my department and they do quite a bit of querying daily and it would be nice to brush up on some basics of SQL to be better prepared for an interview.

    Any help or guidance would be appreciated.
     
  2. alex298

    alex298 Junior Member

    Joined:
    Sep 18, 2012
    Messages:
    4
    Likes Received:
    0
    Hello,

    Lynda.com has good SQL video tutorials. The video topic is "SQL Essential Training". I am afraid that posting the link is against the policy of this forum therefore do a search on Google with the keyword "Lynda.com SQL Essential Training".

    Best regards

    Posting a link to a site with relevant information is not against forum policy. However posting a link to a site where a paid subscription is required to view the content is likely to be frowned upon. When such a link is posted by a new account with a low post count it may in fact be viewed as spam by the moderators. So I appreciate your not posting the link, and let this be a PSA to the members that the site referenced in this message requires a paid sub.

    Markbnj
    Programming moderator
     
    #2 alex298, Sep 26, 2012
    Last edited by a moderator: Sep 27, 2012
  3. Cogman

    Cogman Lifer

    Joined:
    Sep 19, 2000
    Messages:
    10,086
    Likes Received:
    14
    Do you have other programming experience? If yes, then SQL will be dead simple to learn. If not, it might be a little tough (but it shouldn't be too hard to grasp). The whole syntax is really pretty easy to grasp, it takes something like 20 mins to learn all of the standard stuff (though, admittedly, there is a lot more complex stuff out there).

    http://beginner-sql-tutorial.com/sql.htm

    Start here, read all of the syntax, and you should be good on the knowing of how to get stuff done.

    After that, the rest is really data organization. You want to keep your data as normalized as possible. Keep related data together, don't repeat values if at all possible, etc.

    There are other things to learn, like indicies, how to use them, when to use them etc ( http://use-the-index-luke.com/ this is a pretty good tutorial for that).
     
  4. chari

    chari Senior member

    Joined:
    Oct 14, 2004
    Messages:
    265
    Likes Received:
    0
    Thank you! The job doesn't require any changes be made to the data, just querying to collect data. I will take a look at that resource. I watched someone working in it and it seems pretty easy but wanted to know the syntax going in.

    Thanks again!
     
  5. DaveSimmons

    DaveSimmons Elite Member

    Joined:
    Aug 12, 2001
    Messages:
    38,741
    Likes Received:
    33
    The part that was most unfamiliar to me was the idea that you split up your data / objects into many tables and then use eleventy JOINs in an illegible mix of plain, LEFT, RIGHT, INNER and FLAVIN* to stitch your object back together.



    * not a real JOIN type
     
  6. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    Yeah it sort of looks like abnormalization to begin with.
     
  7. Cerb

    Cerb Elite Member

    Joined:
    Aug 26, 2000
    Messages:
    17,409
    Likes Received:
    0
    That's because you're mentally putting a hierarchical system overlay onto data that does not necessarily need that kind of rigid organization. It's also what leads to bloated and unnecessary ORMF use, which can make for hideous creations (what should be a thin container abstraction to views and query results, with explicit request and commit, usually becomes a bloated nightmare, because programmers will use it to force their OO peg into a SQL hole). That there is a simpler level of necessary relationships for data integrity, that only that should be implemented in the DBMS, and that all the rest of organizing the data can and should be decoupled, is the 'a-ha' one should be after.
     
  8. Cogman

    Cogman Lifer

    Joined:
    Sep 19, 2000
    Messages:
    10,086
    Likes Received:
    14
    Meh, IMO, the data should be organized as naturally as possible with good normalization, etc. After it is organized well, you create views and stored procs for the program to access and manipulate the data. On the program layer, a library should handle the objectifying of the incoming data.

    What generally tends to happen is after a database is created, someone will come along and decide "Hey, I just need one more column here to store x/y/z" In the end, you end up with these super bloated tables with lots of disjointed information and nobody willing to clean them up for fear of breaking 1000 different applications that rely one them.
     
  9. Cerb

    Cerb Elite Member

    Joined:
    Aug 26, 2000
    Messages:
    17,409
    Likes Received:
    0
    Either you're reading something different than I tried to write, or one of the above quotes contradicts the other :p.

    Depending on details, too many columns is either trying to cram a specific flat-file organization, or not taking care for any at all; and too many tables is usually hierarchical (IE, tables with 1-2 columns and no RI implementation, because hey, the application code does that, right?).

    When the ORM[F] basically defines the database (most major ones will do this, if you want), or otherwise treat it as a crutch, rather than a way to abstract the data stream and coherency issues in languages with a different worldview, things get ugly. Meanwhile, a little understanding of how the DB is intended to work would usually solve the problem.

    Likewise, just for querying, the SQL, even with all its weirdness, is actually fairly easy (reading someone else's is much harder :)). Anyone with a little math background should be able to grok most of the theory behind how it is supposed to work, and then focus on the quirks of SQL implementations...but I've even had to deal with that being FUBARed (turn everything into char of length n, spill it into unnamed excel spreadsheet columns, then (a) complain when it doesn't work right, (b) complain when it is slow, and (c) I have to track down the problem between my DB, their spreadsheet--the worst problems were always from people that thought in terms of flat files--and their reporting or data importer to another DB, even though named CSV would work for the strange systems, and straight T-SQL would have done the job for any and all current applications and reports...).

    Coupling of data structure and what is being done with it is the problem these DBMSes are very good at preventing and solving; being able apply more structure than is necessary for data coherency (IE, normalized) as needed, for each user and/or task, is that solution...or, well, the best one that has yet been come up with.
     
    #9 Cerb, Sep 28, 2012
    Last edited: Sep 28, 2012
  10. DaveSimmons

    DaveSimmons Elite Member

    Joined:
    Aug 12, 2001
    Messages:
    38,741
    Likes Received:
    33
    Relational DBs and normalization makes sense compared to having 1,000 field single tables. It's also often the best way to store one-to-many data.

    It's just that reading someone else's clever code to JOIN 4-5 tables back into a single view can be ... fun.

    I spend most of my day with application programming rather than writing SQL, so I can't yet quickly build the model in my mind like I do for a section of C/C++ or PHP code. I still have to stop and think it through.
     
  11. Cogman

    Cogman Lifer

    Joined:
    Sep 19, 2000
    Messages:
    10,086
    Likes Received:
    14
    RI stuff almost never works in practice. I've never seen a piece of software the could be perfectly developed from some sort of RI. In fact, that usually ends up being the most bloated and worthless software that I've witnessed.

    If given the choice, I would choose a hyper-normalized database over a flat database any day of the week. It is stupidly easy to take a overly normalized database and convert it into one that is flatter (essentially, that is what is happening when you do those complex joins). On the other hand, it is nigh impossible to take a DB that is unnormalized and make it normalized with existing data.

    It has little to do with the actual application setup and everything to do with data normalization. Very rarely does the application perfectly model the database.

    Not totally. ORMs are made to make converting DB data into objects easy. That is it really. IMO, the "Meanwhile, a little understanding of how the DB is intended to work would usually solve the problem." doesn't solve the issue at all the ORMs are trying to solve. I don't like putting random SQL strings into my language of choice. I don't like that I have to loop through each returned row and decide which piece of data goes where, what to do when something is null, and how to deal with multiple returned selects. It isn't hard to deal with, it is just ugly tedious code.

    That is what ORMs try to solve. Yeah, they aren't perfect and they can result in less than optimal select statements and DB structures, but they are there to ultimately cut down on the amount of time a developer has to invest in getting data from the database into a usable state. No combination of views, stored procs, or db structuring is going to solve that problem.

    Never said that it SQL is hard. From a developer standpoint it is really quite easy. The problem is that there are a lot of people who only have cursory knowledge of excel or access that are being put in charge of more complex systems.

    As I said earlier, I prefer a database that is overnormalized to one that isn't. There is just a night and day difference to how easy they are to work with. Sure, overnormalization will result in giant joins, but that really isn't an insurmountable problem. Create a view that swallows up those joins and if nobody ever accesses the data outside of the view then you can look into consolidating (if you have performance problems).
     
  12. Cerb

    Cerb Elite Member

    Joined:
    Aug 26, 2000
    Messages:
    17,409
    Likes Received:
    0
    A DB with normalized data, but without any RI enforcement is going to be more fragile than a flat file DB. It's one of the basic features of the relational model that make it worthwhile. If you need the DB to keep the data matching the schema, and can't trust the application to do it, implementing RI constraints within the DBMS is your best option.

    I have no clue what you mean by software being developed from RI, though. Software needs to be developed to be compatible with the mechanisms used to enforce RI, and how it is enforced can be a performance problem to tackle, but it's just an aspect of making a DB be a RDB.

    Been there, done that, and it was quite tedious (there were regexes for permissions, too, as I recall). It would not have been worth it if requests weren't timing out just before lunch time, honestly. I did have to resort to using one true lookup table, though, to keep the changes local to the database.

    Nor should it. The DB should model the data with the most convenient set of tables and rules to ensure that it doesn't become in any way garbled by misuse (flat-file tables with dependent and/or multivalue fields are being misused from the beginning), or made too difficult to extend, later. That was basically the point: the data has a certain level of structure that it must have to be able to guarantee it is correct. Some structure will be based on how it is used, but any tight coupling to how it is used by the application should be avoided, because it trades an easy short-term problem for more difficult long-term problems, even if the application appears to keep data sane and coherent by itself for the moment (many tables because each new object type gets its own, and many tables to have 6NF, are very different things :)).

    I guess you haven't come across egregious uses of ORM frameworks to make SQL DBs into flat or object DBs...it can be D:. The problem I've seen is that features in ORM frameworks get used to squeeze the DB into a remote cache, rather than treating it like a first-class server that manages databases. The result can be a mire of opaque framework code, and a database with a structure fit for a Great Old One, or [at first glance] random added tables to an existing DB. In either case, trying to add anything useful, or track down bad or missing data, can be a nightmare.

    IoW, that it gets used not for the problem of handling a view without SQL statements and for loops littering code, but for the problem that management likes their SQL DBMSes, and the devs either have little to no use for it, or only think in records and/or OO hierarchies, so the DB gets made to fit the way the application organizes the data in its code, or is treated like it should be storing a giant CSV file, with the ORM allowing most of their application code to look rather neat and tidy, despite what is behind it.

    A COBOL programmer that can write C# and use NHibernate is a dangerous being :).
     
    #12 Cerb, Sep 29, 2012
    Last edited: Sep 29, 2012
  13. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    Are you sure you guys are talking about the same thing? Because I thought Cerb was talking about relational integrity. Relational integrity is a state, or a goal. You have it if all the relations in your database have valid records on both sides, and you enforce it by preventing queries from creating broken relations. It's not that big a deal for the data access layer that lives in the software that uses the DB. If the data and object structures have some coherence then relational integrity shouldn't cause any problems, and unless 'RI' means something else entirely it shouldn't cause bloated systems :).
     
  14. Ns1

    Ns1 No Lifer

    Joined:
    Jun 17, 2001
    Messages:
    51,878
    Likes Received:
    78
    Pardon the obvious:


    If I learned all the SQL syntax and whatnot and figured out how to run my own queries, htf do I actually put this knowledge into use?

    I'm gonna guess that the answer is MS technet + find a book that gives you a prepopulated SQL DB to play with, but I wanted to reach out to AT first...
     
  15. slugg

    slugg Diamond Member

    Joined:
    Feb 17, 2002
    Messages:
    4,531
    Likes Received:
    1
    Good question. And your hunch is pretty close to the answer, actually.

    I haven't read this whole thread (TLDR), but I can point you to the direction of a quick, quick start.

    1. Download and install Microsoft SQL Server Express 2012.
    2. Click here to read about where to download the sample databases and how to install them.

    Once you have those sample databases installed, you'll be set. Many, many guides rely on those databases for educational purposes. Hell, the Northwind DB has basically become the canonical standard for pretty much any education related to SQL Server.

    The rest is all just up to you. You can play around with it. You can even link Excel to SQL Server and do some other cool stuff. Just have fun. Don't worry about getting too deep into the books; just think about practical problems and try to figure it out. Books help, sure, but 90% of your learning curve will just be exploratory.
     
  16. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    Microsoft offers SQL CE for free, and they have a downloadable tutorial database. However I have never used CE and I don't know if it comes with the tools you need to set up a database, create and execute queries, etc. MySql is free, and you can get a complete set of GUI tools for it. I was thinking there must be some web site where you can practice SQL queries against a back-end db, but I didn't find anything.

    If I were you I'd just dive in and grab one of those databases, open up a web browser and start Googling. By the time you get it set up and create your first DB you'll have learned a ton.
     
  17. raf051888

    raf051888 Member

    Joined:
    Jan 17, 2011
    Messages:
    167
    Likes Received:
    0
  18. Albatross

    Albatross Platinum Member

    Joined:
    Jul 17, 2001
    Messages:
    2,340
    Likes Received:
    1
  19. Markbnj

    Markbnj Elite Member <br>Moderator Emeritus
    Moderator

    Joined:
    Sep 16, 2005
    Messages:
    15,687
    Likes Received:
    8
    Pretty slick. That's the kind of site I was thinking of in my post above.
     
  20. Ns1

    Ns1 No Lifer

    Joined:
    Jun 17, 2001
    Messages:
    51,878
    Likes Received:
    78
    Update:

    I got the company to pay for SQL Server 2008 Management Studio as well as classes for SQL 2008.

    How does this sound for a first class?

    After that, I have 3 "tracks" I can be on:

    Any comments one way or another?
     
    #20 Ns1, Feb 13, 2013
    Last edited: Feb 13, 2013
  21. Cogman

    Cogman Lifer

    Joined:
    Sep 19, 2000
    Messages:
    10,086
    Likes Received:
    14
    Depends on your goal. The first path sets you up better to be a developer, the second sets you up better to be a sysadmin. They are both valuable, but it really depends on what you want to emphasize.

    (That being said, there are elements in the second that would be pretty valuable for a developer).
     
  22. Ns1

    Ns1 No Lifer

    Joined:
    Jun 17, 2001
    Messages:
    51,878
    Likes Received:
    78
    As a business analyst, right now my goals simply running my own queries and doing my own analysis.

    Right now I have to email developers to do various queries on the DB and I hate that.

    I'm not sure where my long-term goal is WRT SQL. I know for sure though that straight programming is not in my future.
     
  23. slugg

    slugg Diamond Member

    Joined:
    Feb 17, 2002
    Messages:
    4,531
    Likes Received:
    1
    The intro class sounds perfect! As far as "describing how to create programming objects," just go through the motions. If you do well, great. If not, it doesn't matter. Programmers should be able to create objects based on data models, so nobody cares. In fact, Visual Studio will even do that for you.

    As far as the specialty tracks, the way you want to go is the Business Intelligence route, without a doubt. You'll definitely use analysis (SSAS) and reporting (SSRS) services. Integration (SSIS), not so much, but most likely you'll just pick it up by osmosis. Doesn't hurt, anyway.

    The other two options are irrelevant to you, methinks.

    Why 2008? Why not the latest tech? It doesn't matter too much, since the concepts are all the same and you can figure it out, but wouldn't it make more sense to train on SQL Server 2012?
     
  24. Ns1

    Ns1 No Lifer

    Joined:
    Jun 17, 2001
    Messages:
    51,878
    Likes Received:
    78
    The company is on SQL 2008 and the project we're currently working on is SQL 2008...so 2008 it is.

    and this is a fortune500 company mind you, so I guess this is the corporate IT life in non-tech sector
     
  25. slugg

    slugg Diamond Member

    Joined:
    Feb 17, 2002
    Messages:
    4,531
    Likes Received:
    1
    Don't sweat learning the old tech. It's a non-issue. Just thought I'd ask. Have fun!
     
Loading...