SQL queries to test query building capabilties

Question

I was thinking of building a small application to help fellow developers to learn advanced query concepts with a Q&A applications. I need some SQL expert to list a bunch of tough queries and also provide the sample table schemas used.

Answer 1

Something I came up against not too long ago was managing limits for transactions within a web application. Each customer had a series of transaction specs they signed up for and the limits had to be flexible enough to place a limit on a particular transaction type, all transactions or a certain subset of transaction types.

So how would you determine in a single query if a particular transaction spec had already reached it's limit for the day?

Table layouts look something like this.

TRANSACTION_SPECS
specid
name

CAPS
capid
dailycap

TRANSACTION_CAPS
specid
capid

TRANSACTIONS
transactionid
specid
date

Answer 2

I'd give them examples of cursors and ask them how to rework them into set-based queries