2. SQL Puzzles

Werner Van Belle¹ - werner@yellowcouch.org, werner.van.belle@gmail.com

1- Programming Technology Lab (PROG) Department of Computer Science (DINF) Vrije Universiteit Brussel (VUB); Pleinlaan 2; 1050 Brussel; Belgium

Abstract : 3 difficult questions

Reference: Werner Van Belle; SQL Puzzles;

Onderstaand vind u een aantal reele SQL problemen. Tracht deze zo goed mogelijk op te lossen.

A. Top salespersons

a. De databank bestaat uit een tabel die de verkoop per district voor elke persoon bevat. Schrijf een query die de drie hoogste sales per district weergeeft. Beschrijf het gedrag van uw query in district twee.

District	SalesPerson	SalesID	Amount
1	Larry	1	5000
1	Larry	2	5000
1	Larry	3	5000
1	Moe	4	500
1	Curly	5	300
2	Harry	6	500
2	Fred	7	500
2	Tom	7	500
2	Dick	8	500
3	Melvin	9	700
3	Irving	10	500
1	Harpo	11	400
4	Mary	12	5000
4	Sally	13	4000
4	Oprah	14	3000
4	Jenny	15	2000
4	Jessie	16	1000

** b. Pas de query aan zodat niet de drie hoogste sales per district weergegeven worden, maar wel de 3 beste salesman. Wat doet uw query in district 2 ? ^¹

B. Double Duty

De tabel bevat personen en hun rol die ze in de firma uitvoeren. ‘D’ is director, ‘O’ is employee, andere codes (zoals ‘X’ in dit geval) spelen geen rol. We willen nu een tabel die bevat welke mensen welke rol vervullen, met dit verschil dat mensen die zowel employee als directeur zijn als ‘B’ gemarkeerd willen zien.

Ter illustratie. De onderstaande tabel bevat de roles

Person	Role
Smith	O
Smith	D
Jones	O
White	D
Brown	X

Als resultaat willen we de volgende tabel

person	Expr1001
Jones	O
Smith	b
White	D

Solutions

Aa:

SELECT *

FROM SalesData AS so

WHERE Amount >= (SELECT min(amount)

FROM salesdata as s1

WHERE so.district = s1.district

AND so.amount <= s1.amount

HAVING count(*) <= 3);

Ab: TopPersons

SELECT DISTINCT district, salesperson

FROM salesdata AS so

WHERE amount <= (SELECT MAX(S1.amount)

FROM salesdata as s1

WHERE so.district = s1.district

AND so.amount <= s1.amount

HAVING count(DISTINCT amount) <= 3);

B. Double Duty

SELECT r1.person, max(r1.role)

FROM roles as r1

WHERE r1.role in ('D','O')

GROUP BY r1.person

HAVING count(*) = 1

UNION

SELECT r2.person, 'b'

FROM roles as r2

WHERE r2.role in ('d','o')

GROUP BY r2.person

HAVING count(*) = 2;

1 Deze query is niet (of allesinds veel te moeilijk) te schrijven in Microsloft Acces omdat de COUNT(DISTINCT *) niet werkt.

http://werner.yellowcouch.org/
werner@yellowcouch.org