Basic SQL Training

[chari]

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.

 

[alex298]

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

 

[Cogman]

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).

 

[chari]

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!

 

[DaveSimmons]

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

 

[Markbnj]

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

Yeah it sort of looks like abnormalization to begin with.

 

[Cerb]

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

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.

 

[Cogman]

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.

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.

 

[Cerb]

Meh,

IMO, the data should be organized as naturally as possible with good normalization, etc.

Either you're reading something different than I tried to write, or one of the above quotes contradicts the other .

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.

 

[DaveSimmons]

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.

 

[Cogman]

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?).

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.

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.

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.

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...).

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.

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.

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).

 

[Cerb]

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.

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.

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.

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.

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.

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 ).

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.

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 .

 

[Markbnj]

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.

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 .

 

[Ns1]

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...

 

[slugg]

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...

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.

 

[Markbnj]

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...

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.

 

[raf051888]

I would start with W3 Schools SQL training.

 

[Albatross]

http://sqlfiddle.com/

 

[Markbnj]

http://sqlfiddle.com/

Pretty slick. That's the kind of site I was thinking of in my post above.

 

[Ns1]

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?

Overview:
This course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2008.

Who Should Attend:
This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries.

At Course Completion:
After completing this course, students will be able to:
- Describe the uses of and ways to execute the Transact-SQL language.
- Use querying tool.
- Write SELECT queries to retrieve data.
- Group and summarize data by using Transact-SQL.
- Join data from multiple tables.
- Write queries that retrieve and modify data by using subqueries.
- Modify data in tables.
- Query text fields with full-text search.
- Describe how to create programming objects.
- Use various techniques when working with complex queries.

Prerequisite Comments:
Relational database design skills
Programming skills
Knowledge of data integrity concepts.
Core Windows Server skills


Outline: Top
Lesson 1: Getting Started with Databases and Transact-SQL in SQL Server 2008
Overview of SQL Server 2008
Overview of SQL Server Databases
Overview and Syntax Elements of T-SQL
Working with T-SQL Scripts
Using T-SQL Querying Tools
Top
Lesson 2: Querying and Filtering Data
Using the SELECT Statement
Filtering Data
Working with NULL Values
Formatting Result Sets
Performance Considerations for Writing Queries

Lesson 3: Grouping and Summarizing Data
Summarizing Data by Using Aggregate Functions
Summarizing Grouped Data
Ranking Grouped Data
Creating Crosstab Queries

Lesson 4: Joining Data from Multiple Tables
Querying Multiple Tables by Using Joins
Applying Joins for Typical Reporting Needs
Combining and Limiting Result Set

Lesson 5: Working with Subqueries
Writing Basic Subqueries
Writing Correlated Subqueries
Comparing Subqueries with Joins and Temporary Tables
Using Common Table Expressions
Top
Lesson 6: Modifying Data in Tables
Inserting Data into Tables
Deleting Data from Tables
Updating Data in Tables
Overview of Transactions

Lesson 7: Querying Metadata, XML, and Full-Text Indexes
Querying Metadata
Overview of XML
Querying XML Data
Overview of Full-Text Indexes
Querying Full-Text Indexes

Lesson 8: Using Programming Objects for Data Retrieval
Overview of Views
Overview of User-Defined Functions
Overview of Stored Procedures
Overview of Triggers
Writing Distributed Queries

Lesson 9: Using Advanced Querying Techniques
Considerations for Querying Data
Working with Data Types
Cursors and Set-Based Queries
Dynamic SQL
Maintaining Query Files

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

The MCTS SQL Server 2008 Business Intelligence is for individuals who work with the business intelligence features of Microsoft SQL Server 2008. With certification training at New Horizons, the student will learn:
•SQL Server Analysis Services
◦Analysis Services dimensions, measures, cubes and measure groups
◦MDX queries
◦Data mining
•SQL Server Integration Services
◦Designing and controlling SSIS packages
◦Package error handling and security
•SQL Server Reporting Services
◦Designing basic and advanced reports
◦Publishing, executing and subscribing to reports

Who's It For?
The MCTS SQL Server 2008 Implementation and Maintenance certification is an entry-level SQL Server 2008 certification. With certification training at New Horizons, the student will learn:

•Installing, configuring and maintaining SQL Server 2008
•Managing SQL Server 2008 security
•Replication, disaster recovery and performance tuning
•Working with views, stored procedures, triggers and functions
•Transactions and locks

Who's It For?
Individuals who work extensively with Transact-SQL and/or write applications for SQL Server 2008 are ideal candidates for the MCTS SQL Server 2008 Database Development certification. With certification training at New Horizons, the student will learn:
•Working with databases and database files
•Creating and tuning SQL Server 2008 indexes
•Enforcing data integrity
•Working with views, stored procedures and functions
•SQL Server 2008 managed code

Any comments one way or another?

 

[Cogman]

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).

 

[Ns1]

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).

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.

 

[slugg]

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?

 

[Ns1]

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?

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

 

[slugg]

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

Don't sweat learning the old tech. It's a non-issue. Just thought I'd ask. Have fun!