I've studied for the 5.0 Developer cert, and the 5.6 Developer cert, and I've also mentored about 100 students through these exams. Most/all of the students were new to database development, so I had to teach them from zero to Oracle Certified in the space of about 10 weeks.

To echo what /u/justintxdave said, the 5.6 Developer exam is tough. A lot of the questions are deliberately obtuse in my opinion and seem designed to catch you out. Plus they have questions on Java talking to MySQL, PHP talking to MySQL and Microsoft .NET talking to MySQL. This seems a little unfair to me - if you're a PHP developer, you're unlikely to know .NET and vice-versa.

The 5.0 Certification Study Guide is still a good resource, but you need to be careful to identify those things that have changed from 5.0 to 5.6 - e.g. DATETIME, TIMESTAMP, SQL MODES, and there are topics in the new Developer exam that were in the old DBA exam - transactions, foreign key constraints etc. The sample questions on the DVD of 5.0 book are a good practice tool.

Here's the list of topics I studied for the 5.6 exam, and what books/resources I used.

Study Guide refers to the 5.0 Certification Study Guide , and the relevant chapter numbers.

Head First SQL is a very basic beginners guide to SQL that you probably don't need.

I also used Paul du Bois's MySQL Developer's Library to fill in some blanks.

There is was a real need for an official 5.6 Certification book, but at this stage an 8.0 cert is probably just around the corner. I always had a notion I might write a 5.6 book, but life got in the way :)

Best of luck with the exam!

I've been a web developer for two years and can tell you what I learned over time that led to me getting an internship at a software company. I didn't major in computer science but I did eventually go to a bootcamp.

I'll note that I'm not a huge fan of that flowchart, because it's backwards. You need to be familiar with what all those tools can accomplish, but I don't think anybody is capable of holding expert-level competence in so many areas at the same time. The key is getting to the point where you know or can figure out what tool will be required for any given task. For example, I don't really know much about Websockets, but I know that I'd probably need them if I wanted to build a chat application.

Anyway, here's my learning track in terms of programming

Pre-2011: Excel formulas

First programming I ever did for a bunch of random things, and at this time I didn't realize I wanted to be in software development yet.

2011-2013: SQL

Started with the DBA where I was working sending me a Word document with some common queries I could run in a SQL client so I could answer other people's questions and same him time. Over time I learned how to change the queries and then write my own. This book helped: https://www.amazon.com/SQL-Queries-Mere-Mortals-Hands/dp/0321444434/ref=sr_1_2?ie=UTF8&qid=1494701750&sr=8-2&keywords=sql+queries+for+mere+mortals

2013-2014: Java, shell scripting, other unix/linux related things

Took a programming class using Java and a shell scripting class in a local university's online program. Used these books:

https://www.amazon.com/Java-Introduction-Problem-Solving-Programming/dp/0132162709/ref=sr_1_14?ie=UTF8&qid=1494701761&sr=8-14&keywords=introduction+to+programming+with+java
https://www.amazon.com/Unix-Shell-Programming-Stephen-Kochan/dp/0672324903/ref=sr_1_6?ie=UTF8&qid=1494701778&sr=8-6&keywords=unix+programming

I think Java is a very good first object oriented language to learn, even though most bootcamps want to do either Ruby or Javascript. Ruby (and Rails) have a little too much syntax sugar and other shortcuts that make it hard for a beginner to understand, and JavaScript is just plain weird (along with its ecosystem, which requires knowing a little history of the language and why it is the way it is).

2015: Ruby, Rails, a little JS. Attended a bootcamp and got my first job.

2016: ReactJS, actually understanding JS

2017: More JS, and now learning Elixir and the Phoenix framework.



As I mentioned before, these languages and frameworks are just tools. The way I've learned has been to do projects, figure out what tools would be necessary to accomplish all the functionality, and then slowly make progress on learning how to use those tools, eg, copy/pasting, modifying code other people have written, and then finally taking the training wheels off.

Select is exactly as it implies, it is what you are selecting. Performance wise, it's the same to say, give me everything vs give me one or two things. It's best to specify what you want, because if you don't need everything, you're going to pull a lot of network time trying to pull more data across. Also, it can break things. Example: My beginning point has 5 columns and so does my end point, I get sloppy, I just say, hey, just grab everything from my beginning point, insert it into my end point. What if I add a column in the future? It will break your code because the end point has 5 columns, but you added a 6th column to your beginning point and told it to grab everything.

For joins, practice. Lots of practice. This covers joins well: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Here are some books I'd highly recommend:

First book to read

Second book

Go download SQL 2016 developer, almost the same as enterprise and free. They have a huge DB for it that you can practice in.

Here are a couple general goto lists for freely available technical material on the web.

http://stackoverflow.com/questions/194812/list-of-freely-available-programming-books
http://www.reddit.com/r/csbooks/

That said, I've never found a great comprehensive introduction to SQL online, though section two of the PostgreSQL manual is an adequate overview. When you really need to ramp up quickly, it's a better use of time to find a good book. If you need to do this on the cheap and live in or near a major metro, don't overlook the public library for reference material. The SQL language has been stable for a long time, and you don't need an absolutely current book to pick up the basics.

Safari Bookshelf is another fantastic option. This gives you access to nearly everything from O'Reilly. Microsoft Press, Apress, and other major tech publishers. There's a ten day free trial, and it might be worth subscribing for a month or two if ten days isn't sufficient.

So, to the recommendations.
As you're probably aware, trial versions of SQL Server are available freely from Microsoft. If you're already on Windows, this is probably the most straightforward way to begin with minimum installation hassles. Most tutorials covering SQL Server reference either the old Northwind (no longer included with SQL Server 2005 and up, but available here) or the newer AdventureWorks sample databases.

If she's fairly technical, she might jump into the deep end with something like T-SQL Fundamentals (see library, safari books, or read it at a bookstore for the cost of a cup of coffee)

For a slower start, look into an introductory book on Microsoft Reporting Services. (MS's answer to Crystal Reports, included with SQL Server) Avoid theMS Press Step by Step series, as it is almost universally crap. Larson's books are a better starting point. See Microsoft SQL Server 2008 Reporting Services or similar. These will cover SQL in the context of generating reports with graphical tools, which may be a more palatable start for the non-programmer.

Not as highbrow literature but it's Learning PHP, MySQL, JavaScript, and CSS: A Step-by-Step Guide to Creating Dynamic Websites for me. I knew how to write HTML files, I knew how to write CSS files, but I didn't know how to serve an actual website. If you want to learn how to set up a simple website of mostly HTML files with some basic logic, good luck. Nobody writes about that. Every online tutorial tells you to use some framework to setup a website, I couldn't tell where the webdev basics ended and framework specific knowledge began. Eventually I caved and bought that O'Reilly book, and it taught me everything I needed to understand what framework tutorials were actually doing. Can't beat a good book for the basics.

Let me clarify a little. It's helpful to find books that I can digest from the ground up on and compare with current standards or use creatively. Simple put I enjoy doing the homework on a book. What I am look to accomplish is to write SQL Queries for corporate finances and manufacturing. Working in the environment I am in currently allows me the opportunity to learn and practice it.


Here are a few I looked into:

https://www.amazon.com/SQL-Minutes-Sams-Teach-Yourself/dp/0672336073/ref=sr_1_4?ie=UTF8&qid=1519240184&sr=8-4&keywords=beginning+SQL

https://www.amazon.com/SQL-Beginners-Learning-Programming-Course/dp/1532716958/ref=sr_1_14?ie=UTF8&qid=1519240184&sr=8-14&keywords=beginning+SQL


https://www.amazon.com/SQL-Practice-Problems-learn-doing/dp/1520807635/ref=sr_1_7?ie=UTF8&qid=1519239367&sr=8-7&keywords=beginning+SQL

Like someone told you before, it depends. In the database world, the complexity is more measured in terms of number of IO reads. Let's say you have a table of 10 000 users and you do "SELECT * FROM users WHERE username = 'admin';".

Without any index (default), the upper bound will be 10 000 lookup (if the data is at the end), the lower bound 1 lookup (if the data is at the beginning) and the average will be 5000 lookup. So basically: O(n).

With an index on the username column, you usually get that down to O(log n) lookups.

You need to trust your database server for optimization and use EXPLAIN statement to get a feeling on what's going on.

My general advice is to create an index on every column you may use in your WHERE conditions. But beware, sometimes, only the first index is used.

I suppose you are using MySQL, so here's a very good book on performance and speedup strategies.

http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064

>dynamic sql is a pain to read and alter, generally there are better ways to do stuff.

Yes, but sometimes it is the least-bad solution to a problem.

You might find this book helpful in dealing with dynamic SQL and finding the right uses for it.

Jeremiah Peschka also has (had) a PASS Summit session that's helpful, http://www.sqlpass.org/summit/2014/Sessions/Details.aspx?sid=6071. If you have access to the 2014 PASS Summit recordings, you can watch it instead of just looking at the demos & slides (linked).

I've just picked up The Rational Guide to Extending SSIS 2005 with Script and SQL Server Integration Services Design Patterns recently.

I'm completely self taught with SSIS and consider myself decent to intermediate, but I know I'm probably closer to beginner in skill with it. I'll be starting with the 2005 book probably Monday next week as I finish The Datawarehouse Toolkit by Kimball.

The author of the 2nd book, Andy Leonard, recommended the first book if you had only one book to read to learn SSIS. So I'm giving it a shot and seeing if it's worth it, then I'll find out if either book is worth it but these are the two books I've found that are recommended for learning SSIS.

"Python for Data Analysis" is pretty good. It's written by Wes McKinney, the creator of Pandas, so its focus is using Pandas for data analysis, but it does include sections on basic and advanced NumPy features: http://www.amazon.com/Python-Data-Analysis-Wrangling-IPython/dp/1449319793

Alternatively, the prolific Ivan Idris has written four books covering different aspects of NumPy, all published by Packt Publishing. I haven't read any of them, but the Amazon reviews seem OK:

• NumPy Beginners Guide (2nd ed.): http://www.amazon.com/NumPy-Beginners-Guide-Ivan-Idris-ebook/dp/B00CITNP76
  
• Learning NumPy Array: http://www.amazon.com/Learning-NumPy-Array-Ivan-Idris-ebook/dp/B00L2AP6KQ
  
• NumPy Cookbook: http://www.amazon.com/NumPy-Cookbook-Ivan-Idris-ebook/dp/B009X5KIH8
  
• Python Data Analysis: http://www.amazon.com/Python-Data-Analysis-Ivan-Idris-ebook/dp/B00OZLVAW2
  

Systems Analysis and Design in a Changing World (Sixth Edition) <-- this was the version required for a class

Seventh Edition(?)

This book is free from any programming languages and is instead supposed to aide the development of thinking and planning applications.

murach's SQL Server 2012 for developers <-- required for class

murach's SQL Server 2016

Uses SQL to further enforce "forms" for application data structures.

Professional Test Driven Development with C#: Developing Real World Applications with TDD

Book I purchased during my 4000 project class. Team based project where we had to

1. To plan out the application using skills from System Analysis and Design.
   
   
2. Create a presentation to show to a client.
   
   
3. Create a manual to help onboard developers.
   
   
4. Create a manual for clients.
   
   
5. design and deploy a database (database schematic included in onboarding manual).
   
6. Write tests for the application.
   
   
7. Deploy the application to Azure.
   
   After my research at the time, this book seemed to be the most appropriate for my scenario.
   
   

I would recommend MySQL over the one you have listed. I used the first edition of this years ago to learn mySQL and it is an excellent resource.

The High Performance MySQL book that chili_beans listed is a good addition as well.

This this the book I'm using and I'm quite happy with it. It's not free, but it's only like $4 second hand.

It's actually possible to perform a pivot on a data set in MS SQL Server. I use MSSQL Server 2008 at my job and have done this a few times in the past, but I don't have any of the code at home so I can't share that with you.

What I can tell you is that I learned how to do it from the book Microsoft SQL Server 2008 T-SQL Fundamentals by Itzik Ben-Gan. It has chapters on pivoting and dynamic SQL that demonstrate how it's done with a brute force method in order to help you understand what the "pivot" function is actually doing. If you get this book from the library or something, you might be able to figure out how to use MySQL to do the same thing (I think). Ideally, you would just use SQL Server, but that shit is expensive — like, thousands of dollars I think.

Don't think it'll be much use for Proggit to point OP to the latest edition of PHP For Dummies.

Taking AP Calc it seems like he's got a leg up on most people.

Seems that there's the assumption that OP isn't up to the more challenging texts.

>I'd like to get at least a small sample of what I'm biting into.

Well, if he is going to major in CS he'll eventually get into these and other advanced books.

Have you read any of Marcus Winand's stuff? He has a blog called Use the Index, Luke, which is superb.

I also have his book, which is also excellent: SQL Performance explained.

Here are all the local Amazon links I could find:


amazon.co.uk

amazon.ca

amazon.com.au

amazon.in

amazon.com.mx

amazon.de

amazon.it

amazon.es

amazon.com.br

amazon.nl

amazon.co.jp

amazon.fr

Beep bloop. I'm a bot to convert Amazon ebook links to local Amazon sites.
I currently look here: amazon.com, amazon.co.uk, amazon.ca, amazon.com.au, amazon.in, amazon.com.mx, amazon.de, amazon.it, amazon.es, amazon.com.br, amazon.nl, amazon.co.jp, amazon.fr, if you would like your local version of Amazon adding please contact my creator.

I picked this up a few months ago and have been learning tons -

http://www.amazon.com/SQL-Queries-Mere-Mortals-Hands/dp/0321444434