what is a good way to show mysql experience?

Question

As always, if this is a duplicate question that I have yet to uncover, please feel free to close or delete it.

I'm attempting to get into the programming industry. I've focused mainly on PHP due to how widely it's used, but I have some experience in other languages as well.

The thing is, I know SQL syntax quite well, and PHP-wise I don't have much if any problem using either the mysqli interface (if I have to) or PDO. However, I'm a bit stumped as to what to create as a portfolio piece that would be a good showcase of what I can do with SQL. The reason for this is that most frameworks worth their salt include some form of ORM. Most of the grunt work in terms of setting up database queries and so forth is usually both done for you and hidden from you, so that you can focus on your domain logic.

I have a bit of experience with CakePHP, which uses its own form of ORM, and am interested in developing this experience further due to it being a fairly popular (and thus likely to show up as a job requirement) framework. Symfony is equally unhelpful AFAIK in that you have the choice of using Propel or Doctrine; I'd love experience with either but I'm not seeing a whole lot of demand for them, at least not for the entry-level jobs I've been looking at. However, I am aware that CodeIgniter will allow you to override its ORM / ActiveRecord capabilities and allow you to write your own SQL queries.

I have a relatively simple web app I want to create as a portfolio piece. If I use CakePHP to create it, I wind up with a piece that shows off my CakePHP skills, but not my SQL skills, at least not directly. If I use CI, I can show off my SQL skills directly, but then gain experience in a framework that doesn't appear to be used as often, at least not in industry.

The real question comes down to this: if you were (or are) in charge of hiring a new programmer for your team, and your requirements include, for example, MySQL, how would you prefer that the incumbent programmer show his or her expertise in this skill? Would you accept a programmer who only had ORM-based solutions to show you directly but could, if requested, easily whip up a fairly complex SQL query? If not, what would you recommend as a means of demonstrating skill in SQL?

I suppose an alternate title for this question could be "What do interviewers expect from entry-level candidates in terms of SQL?"

Answer 1

Create a project showing the usage of:

1. PDO and prepared statements.
2. InnoDB and foreign key constraints / transactions.
3. Stored procedures
4. Maybe write your own layer over PDO to handle exceptions and simplify the interface?
5. MVC and OOP architecture.
6. Provide SQL files to create the database and all initial data.
7. Don't use a framework for your demo project, that's kind of cheating and not showing off your skills. You wouldn't re-invent the wheel (again) for a real-world web app but show the employer you understand how they work and that you can write your own. This is a demonstration of what you know!

Answer 2

If you're going to work with databases, you should know how. The sql created by the ORM will never be perfect. It's like using a GUI tool to create HTML, eventually you need to know what you're doing and write some code.

Many smaller companies are going to try to hire programmers that can do many things. It may not be ideal and if you can get another job, you can avoid it in your career.

They may have someone on staff who does report writing (be glad it's not you), but needs someone with a higher level of sql & database structure knowledge to do some of the heavy lifting for them.

You may have to write some ad hoc queries to trouble-shoot an immediate problem for a customer.

There may be customers or a buy-out of another company and you have to covert the database from their system to yours.

Of course you can suggest they have the DBA do it, but that could be you whether you know it or not.

Answer 3

I've never interviewed anyone for a database job, but if I did, I'd want them to at least be able to demonstrate an understanding of inner and outer joins, subqueries, derived tables, and aggregate functions. In your existing software, you can probably create a "report" or two that summarizes data in a meaningful way, using a more "complex" query. You might be able to further demonstrate an understanding of the underlying database system if you create a (very simple) application that keeps a good portion of the application logic in stored routines and triggers. I'd also be interested in seeing if the candidate can justify certain design decisions based on the particular RDBMS their solution is made for. For example, if someone handed me a sample for MySQL whose tables used MyISAM format but were obviously being used in some "relational" way, I'd want to know why they chose to use use that storage engine.

As an aside, it could be useful to make a project in a new framework (CI is quick to pick-up), as it demonstrates flexibility.

Answer 4

Our applications are heavily database dependant. The databases are fairly large and extremely complex. We have stored procs that are well over 100 lines long. I would never consider anyone who only has ORM experience because they can't write the complex queries we need (and because they wouldn't know how to tune the poor queries the ORM writes) for any but the most junior trainee position. If someone actually used an ORM to design a database, I'd throw their resume in the trash because they are clearly incompetent to work with an enterprise database.