Any SQL gurus here? How do I answer these interview questions?

[jingramm]

I have to reply to an email before going into an interview. I have SQL experience through some past jobs and I am a very quick learner, however I never was a full blown FT SQL developer or anything.

The questions are:

- Do you write ad-hoc queries or stored procedures/functions?
- How complex are these queries? Does it involved complex aggregation?

I have SQL experience but I'm trying to determine how to best address these questions. What type of knowledge is expected and what falls under complex aggregation and ad-hoc queries?

 

[KLin]

1. It depends on the requirements of the request
2. It depends on the requirements of the request

Really the first question depends on how often would the user need the resulting data. Then it might be worth it to put the query into a stored procedure and make a SQL report for the user to run.

2nd question is really dependent on what kind of data is being requested.

 

[jingramm]

1. It depends on the requirements of the request
2. It depends on the requirements of the request

Really the first question depends on how often would the user need the resulting data. Then it might be worth it to put the query into a stored procedure and make a SQL report for the user to run.

2nd question is really dependent on what kind of data is being requested.

These are actually pre-interview questions. They are asking about my experience with SQL and not what I would do.

What are some examples of ad-hoc queries and what is an example of complex aggregation? Hopefully I can relate something from my background and be able to speak to it.

 

[ringtail]

1 both.

• ad hoc so you can answer new questions quickly.

• Stored for: (a) repetitive reports, and
  

(b) so users have a consistent experience during repeat use, and

(c) as a bank of proven routines that can be used like templates for fast adaptation to new uses as needed.

2 complexity

• all you can do is tell them your self-assessment, or maybe even give an example of something good that you're proud of
  

 

[jingramm]

1 both.

• ad hoc so you can answer new questions quickly.

• Stored for: (a) repetitive reports, and
  

(b) so users have a consistent experience during repeat use, and

(c) as a bank of proven routines that can be used like templates for fast adaptation to new uses as needed.

2 complexity

• all you can do is tell them your self-assessment, or maybe even give an example of something good that you're proud of
  

How do I answer how complex these queries are? What is considered complex aggregation and anyone have a sample query that involves complex aggregation that I can look into?

 

[Jaydip]

How do I answer how complex these queries are? What is considered complex aggregation and anyone have a sample query that involves complex aggregation that I can look into?

Here you go

SELECT Dept.deptname, Emp.salary, Project.[Count],Emp.salary/prj.[Count] as ‘AVGSALBYDEPT’
FROM Department Dept,
(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,
(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj
WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Let me know if you have any doubts

 

[KB]

Complex aggregation may involve querying statistics included in views, inline queries, common table expressions, user defined scalar functions or user defined table functions.
It may also include using SQL functions/commands such as CASE.. WHEN, AVG, MIN, MAX, SUM, COUNT, ROW_NUMBER , RANK OR PIVOT. Some of these are SQL Server specific, Oracle has their own functions. I would learn what each one of these is and when to use each before comfortably answering questions on complex aggregation.

 

[kn51]

Been doing SQL for 20 years.

Started on Oracle (plenty of PL/SQL) but most of my life have been in MSSQL land with Transact-SQL.

With that said...

1) There are 10 ways to skin the cat in SQL. From elegant to downright horrible.
2) I learn new things everyday. Just looking at Jaydip's example expanded my knowledge. A lot of us get caught up in our own way of thinking, habits, etc. From join statements, to alias conventions, etc.

To answer your questions...ad hoc to me would be if my boss came in and asked me for some oddball report on how many people smoke crack at our agency. It would be a one off thing. Disclaimer: I wouldn't suggest this as an example in your job search.

Complex aggregates to me would be the previously mentioned functions such as sums and counts combined with group/having/order clauses.

Stored procedures...used for abstraction from the front end and scheduled tasks. And of course passing variables and perhaps needing a cursor for something very complicated.

While quite an elegant language with 4 basic commands, SQL has it shortcomings. Adding onto that is the whole "normalization" and relationship integrity mantra taught in school. Things can go overboard. There are queries I have written where just the join statements would fill a page. When you get in the real world of SQL things are a tad different.

Also look up views and cursors. Cursors are somewhat of a crutch but are needed in some cases.

Probably didn't help you much, but gave my 2 cents. Hopefully it was worth at least a penny.

 

[ringtail]

Jaydip

please discuss use of switch "/ " in your "Emp.salary/prj"

 

[jingramm]

what are some common interview questions that test about "complex aggregation" queries?

 

[SecurityTheatre]

I'm pretty sure the ad-hoc in the question refers to the "on the fly" creation of SQL in application code vs the use of stored queries.

This is a common question you might ask a candidate who is looking to be a developer of a database-backed application.

 

[Markbnj]

I'm pretty sure the ad-hoc in the question refers to the "on the fly" creation of SQL in application code vs the use of stored queries.

This is a common question you might ask a candidate who is looking to be a developer of a database-backed application.

That's exactly what it means in this context. Some people will also refer to it as "dynamic" SQL, because it is sent to the server as part of the request rather than being precompiled beforehand, as with a stored proc or UDF. Ad hoc could also refer to any manually constructed query entered into a query execution tool for the purpose of answering a one-off question, but that usage is more common among DBAs and analysts.

 

[Jaydip]

Jaydip

please discuss use of switch "/ " in your "Emp.salary/prj"

Do you want me to explain it further?

 

[PrincessFrosty]

Generally you'd write ad-hoc SQL for one time use as part of being an admin for the server filling one off requests for stats or data requests.

You'd write stored procedures more often for being part of a bigger development team where a web or application developer needs to interact with the data in a repeatable/reliable way. At the end of the day stored procedures are just good for automation and for reducing network traffic by doing logic on the DB server and sending the result rather than sending a full data set to the app.

Aggregate functions are useful in the 2nd case where a developer might have an app wanting to make a decision based on some data, ideally the SQL server does the aggregate calculations locally and simply sends the single result back as the query response.

These questions are likely there just to weed out inexperienced SQL developers, they may also be looking specifically for someone to be part of a larger development team and want to see if you've got the skills to be given strict requirements by a developer and implement a stored procedure solution that abstracts the DB to the application/web devs.

 

[Tweak155]

One additional benefit of stored procedures is security from the front end application. The front end application will not be able to identify what tables are being joined / searched / etc and will only get a result.

 

[Markbnj]

One additional benefit of stored procedures is security from the front end application. The front end application will not be able to identify what tables are being joined / searched / etc and will only get a result.

Agreed, and that is also an improvement in modularity through decoupling the client code from the schema. I like stored procs, but unfortunately they are not much favored by modern developers, probably because most of them are using ORMs now and have no idea what is going on inside the box anyway.

 

[smackababy]

Agreed, and that is also an improvement in modularity through decoupling the client code from the schema. I like stored procs, but unfortunately they are not much favored by modern developers, probably because most of them are using ORMs now and have no idea what is going on inside the box anyway.

At a place I worked, security did not allow us to use stored procedures because, and this is a direct quote from them, "if they gain access to our database, they can see all the queries we run."

I am actually not a huge fan of this type of thread, perhaps not this thread but the interview answers type, because if a person doesn't know the answer, us giving it to them or how to better answer a question is a disservice to the interviewer.

 

[Markbnj]

At a place I worked, security did not allow us to use stored procedures because, and this is a direct quote from them, "if they gain access to our database, they can see all the queries we run."

I am actually not a huge fan of this type of thread, perhaps not this thread but the interview answers type, because if a person doesn't know the answer, us giving it to them or how to better answer a question is a disservice to the interviewer.

Ha, because it's so much better protected when it's in the source code that is deployed to the web or application server.

Anyway, completely agree with you on threads like this. The line is a fine one. If it boils down to "I need to know more about this topic to do well in this interview," then that's fine. If it's "I could care less about this but have to convince these guys I know what I'm talking about," then it's deceptive. So far I have judged this thread to be more in the former category.