Good Database knowledge - Urgent response Requested

Zambia

Hi

A company I have applied to has asked me to demonstrate good database knowledge. Now I have good database knowledge but I have no idea how to answer this question?

Do the want SQL scripts ? Some description of Design ?

Anybody have any idea ? Or Is thi another case or recruitment firms and HR depts unaware of a subject and winging it.

Any ideas folks I have to send this in an hour...

Zam

pwd

Well I asked on another forum what sort of stuff I'd need to know to be qualified for a database engineer job in google taht I was considering applying for. Here's a response I got:

"I would be looking for someone with:
- significant experience with two or more different relational database engines, e.g. MS/Sybase SQL Server, Oracle, Informix, Ingres, DB2, etc.
- preferably some experience or at least working knowledge of other database technologies, e.g. network, CODASYL, inverted list.
- experience as a DBA in a 24x7 enterprise shop. Maintenance and automation are one thing; doing them to a system that never sleeps is another altogether.
- significant experience with the physical modeling and performance tuning of both transactional and to a lesser extent data warehouse databases.
- experience or at least a strong understanding of hardware sizing and storage array technology

Questions I might ask a candidate include:
- explain the different kinds of application/database locking schemes and circumstances where you might use each
- explain how your favorite database implements row-level locking internally and any limitations or implications that are not obvious. For extra credit, compare and contrast the schemes of two or more different databases.
- explain what the model-view-controller architectural paradigm is and how you have/would leverage various database features to improve client-server application performance
- explain transaction isolation levels and in particular the concepts of dirty and repeatable reads. For extra credit, describe one or more mechanisms used internally to implement repeatable reads.
- explain the difference between a logical data model and a physical data model. Describe your favorite modeling technique and why it works for you.
- explain at least one non-relational database technology and the kind of problem(s) it/they might be better suite for.
- explain the differences between a primary key and a unique index. For extra credit, compare and contrast how different databases implement them and any differences in the limitations associated with each.
- explain the process of normalizing a data model and it's advantages. Then describe circumstances under which you would back away from normalization.
- explain, in terms of your favorite database, the likely access plan and number of physical I/Os required to retrieve a purchase order, i.e. header and details. Assume the database cache is empty.
- estimate the physical I/O budget for a typical transaction saving that same PO for the first time. This time assume a "warm" database with a populated cache.

The above questions are designed to elicit how much the candidate knows about database architectures and internals. As such, the I/O questions are not so much about exact numbers as they are about the process of estimation.

Practical problems I might ask a candidate to solve on the white board might include:
- describe one or more techniques you could use to pivot several years of monthly summary data into a 12 column, row-per-year report
- describe how one might take weekly summary data and produce a rolling 4 week-per-row report
- describe how you would use effectivity dating to keep a history of changes in a table
- describe an extension to the technique above to keep a history of changes in a separate history table
- describe an efficient technique for applying inserts/updates/and deletes from a working table to a master table. Feel free to create your own table definitions including whatever process support fields you require.

The practical problems are more to see how a candidate thinks and solves problems. Ironically, it does not demonstrate as much if the answer is completely known ahead of time and simply written out, although; having solutions to all of these off the top of ones head certainly demonstrates both depth and breadth of experience."

Spud83

Since you say you have good database knowledge I would say the best thing to do is to give examples of when you have used them commercially. What I mean is list the type of projects you have worked on and how you utilized a database within them. List the different kinds of databases you have used also. I'm guessing what they really want to know how you have worked with them before rather than you getting into technical details.

johny28

Zambia232 wrote:

Hi

A company I have applied to has asked me to demonstrate good database knowledge. Now I have good database knowledge but I have no idea how to answer this question?

Do the want SQL scripts ? Some description of Design ?

Anybody have any idea ? Or Is thi another case or recruitment firms and HR depts unaware of a subject and winging it.

Any ideas folks I have to send this in an hour...

Zam

Hi There,

SQL:
there are no SQL scripts there are what we call SQL statements,

Now, SQL in itself is a Query lanaguage to retreive data and modify data/structre. This is in DB2, Oracle and MySql.

Unless yyou are using PLSQL which there is more into it that the SQL statements you can write scripts to do trrigers and other things...

If in the interveiw they will be only asking you about SQL stuff then you should know

Select from <table_name> Where condition..

Delete

Modify

.....

Google SQL statements.

The other part you mentioned is "discription design" I suppose by this you mean database design which is mainly about ER and cardinaltiy

again google Entity relationships ..

I had an interveiw a while ago in a company in town and they asked these things but then we did not agree coz their offer was lower than what I expected even if they offered me what I wanted I still would had been hesitant taking the job..

Let me know if you need more help..

Thanks

Johnny

Tom Dunne

Johny, you are massively over-simplifying things.

johny28 wrote:

SQL:
there are no SQL scripts there are what we call SQL statements,

How do you create a database? Do you enter each "Statement" individually, or do you put them all into a script?

johny28 wrote:

Now, SQL in itself is a Query lanaguage to retreive data and modify data/structre. This is in DB2, Oracle and MySql.

There are also many,many more databases.

Unless yyou are using PLSQL which there is more into it that the SQL statements you can write scripts to do trrigers and other things...

PL/SQL is Oracle specific. Other databases have their own languages.

If in the interveiw they will be only asking you about SQL stuff then you should know

Select from <table_name> Where condition..

Delete

Modify

They will ask you these questions if it is an entry-level position.

Google SQL statements.

Yeah, that will really help in the interview. :rolleyes:

The other part you mentioned is "discription design" I suppose by this you mean database design which is mainly about ER and cardinaltiy

What about data modelling?

again google Entity relationships ..

Again, a massive over-simplification.

OP: Pick something you have worked on before - a relatively complex business solution.

An example I have given before (this is more DBA than design, btw) was where I worked in a large corporate environment. We shutdown the systems every quarter for maintenance, which took 5 DBAs up to an hour. I wrote a script that cleanly shutdown the systems automatically and only took one DBA 15 minutes to do. This showed an understanding of what had to be done, how it was done and how it could be automated, etc.

Zambia

Cheers lads that has the thing sent of.

johny28

tom dunne wrote:

Johny, you are massively over-simplifying things.



How do you create a database? Do you enter each "Statement" individually, or do you put them all into a script?



There are also many,many more databases.



PL/SQL is Oracle specific. Other databases have their own languages.



They will ask you these questions if it is an entry-level position.



Yeah, that will really help in the interview. :rolleyes:




What about data modelling?



Again, a massive over-simplification.

OP: Pick something you have worked on before - a relatively complex business solution.

An example I have given before (this is more DBA than design, btw) was where I worked in a large corporate environment. We shutdown the systems every quarter for maintenance, which took 5 DBAs up to an hour. I wrote a script that cleanly shutdown the systems automatically and only took one DBA 15 minutes to do. This showed an understanding of what had to be done, how it was done and how it could be automated, etc.

True..

But for an interveiw , from my expereince only the basics are needed...

johny28

tom dunne wrote:

Johny, you are massively over-simplifying things.



How do you create a database? Do you enter each "Statement" individually, or do you put them all into a script?



There are also many,many more databases.



PL/SQL is Oracle specific. Other databases have their own languages.



They will ask you these questions if it is an entry-level position.



Yeah, that will really help in the interview. :rolleyes:




What about data modelling?



Again, a massive over-simplification.

OP: Pick something you have worked on before - a relatively complex business solution.

An example I have given before (this is more DBA than design, btw) was where I worked in a large corporate environment. We shutdown the systems every quarter for maintenance, which took 5 DBAs up to an hour. I wrote a script that cleanly shutdown the systems automatically and only took one DBA 15 minutes to do. This showed an understanding of what had to be done, how it was done and how it could be automated, etc.

BTW thanks for you suggestions and questions... Not really in the mood to attend them.

Thanks

DublinWriter

Zambia232 wrote:

Anybody have any idea ? Or Is thi another case or recruitment firms and HR depts unaware of a subject and winging it.

Sounds very much like it.

It all depends on the role in which they expect you to go in. Typically with database stuff you're either talking developer or DBA.

As a developer you need to know generic ANSI-SQL, plus whatever propriatery extended SQL comes with the particular DBMS you'll be using. With Oracle it's PL/SQL and with Microsoft SQL Server and Sybase it's T-SQL. DB2, Informix and Ingres also have their own extended SQL variations.

As a DBA, you'll really need to know the nuts-and-bolts of the underlying DMBS in terms of query-optimisation, tuning and security.

But your original question from the agency/employer sounds vague.

Best of luck,

DW.