用于测试查询构建功能的SQL查询

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.

我正在考虑构建一个小应用程序来帮助开发人员学习Q&A应用程序的高级查询概念。我需要一些SQL专家列出一堆棘手的查询,并提供使用的示例表模式。

2 个解决方案

#1

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

我给他们一些游标示例,并问他们如何将它们重新编写为基于集合的查询

#2

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.

我不久前遇到的一件事是管理Web应用程序内的事务限制。每个客户都有一系列他们注册的交易规范,限制必须足够灵活,以限制特定交易类型,所有交易或某些交易类型子集。

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

#1