Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 21 '11 at 4:37

This question came from our site for professional and enthusiast programmers.

3  
question for programmers.stackexchange, maybe? –  Chris Browne Oct 21 '11 at 4:20
1  
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 stackoverflow.com/questions/62069/mysql-interview-questions see more in there >>>>>>>>>>>>>>>>>>>>> –  Prix Oct 21 '11 at 4:22
2  
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
add comment

4 Answers

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!
share|improve this answer
    
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
add comment

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.

share|improve this answer
    
I like the dapper.net ORM. But then again, it doesn't generate sql. It just converts the data for you =) –  acidzombie24 Oct 22 '11 at 1:39
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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). –  Emmad Kareem 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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