Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?"


migration rejected from Jun 11 '15 at 12:31

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by durron597, gnat, MichaelT, Kilian Foth, Michael Kohne Jun 11 '15 at 12:31

This question appears to be off-topic. The users who voted to close gave these specific reasons:

  • "Questions seeking career or education advice are off topic on Programmers. They are only meaningful to the asker and do not generate lasting value for the broader programming community. Furthermore, in most cases, any answer is going to be a subjective opinion that may not take into account all the nuances of a (your) particular circumstance." – Kilian Foth, Michael Kohne
  • "Questions about what language, technology, or project one should take up next are off topic on Programmers, as they can only attract subjective opinions for answers. There are too many individual factors behind the question to create answers that will have lasting value. You may be able to get help in The Whiteboard, our chat room." – durron597, gnat, Community
If this question can be reworded to fit the rules in the help center, please edit the question.

question for programmers.stackexchange, maybe? – Chris Browne Oct 21 '11 at 4:20
Have you checked the related question when you were writing your question it does show some interesting things in regards what you are asking aka see more in there >>>>>>>>>>>>>>>>>>>>> – Prix Oct 21 '11 at 4:22
SQL "portfolio" tends to be about high use/production databases, with millions+ records. Syntax knowledge doesn't mean a lot if it doesn't perform well... – OMG Ponies Oct 21 '11 at 4:23
up vote 3 down vote accepted

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!
I've chosen this as the answer (despite it having fewer upvotes) in that the most pertinent part of the question (at least to me) was determining exactly what employers would want to see in terms of a MySQL skillset. I was surprised to see it recommended that I not use a framework, but this does make sense. Thanks! – ubtng Oct 23 '11 at 1:24

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.

I like the ORM. But then again, it doesn't generate sql. It just converts the data for you =) – acidzombie24 Oct 22 '11 at 1:39

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.


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.

could you please elaborate on "if someone actually used an ORM to design a database" - My understanding is that most ORM use an already designed database (except for Entity Framework and alike). – NoChance Oct 22 '11 at 0:28
@HLGEM No wonder your stored procs are over 100 lines long and you need 'SQL' programmers. – Hasan Khan Oct 22 '11 at 3:32
Some can create your database design for you, It is a poor idea to allow it to do so as the datbase design needs as much care and thought as the application design. – HLGEM Oct 24 '11 at 13:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.