SQL Kennis

Beste CP'ers,

Het lijkt er steeds op dat het SQL niveau op CP heel laag is. Daarom wil ik een uitdaging starten. De bedoeling is heel simpel: Je moet een select-query schrijven die aan de informatie behoefte voldoet. We beginnen met de volgende database structuur. Creëer dus een database, en voer de volgende query's uit.

CREATE TABLE guestbooks (
 id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(50) NOT NULL DEFAULT 'Gastenboek',
 site VARCHAR(255) NOT NULL,
 PRIMARY KEY(id),
 UNIQUE(site)
)ENGINE=InnoDB;


CREATE TABLE authors (
 id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(30) NOT NULL,
 PRIMARY KEY(id),
 UNIQUE(name)
)ENGINE=InnoDB;


CREATE TABLE messages (
 id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
 guestbook_id INT(5) UNSIGNED NOT NULL,
 author_id INT(5) UNSIGNED NULL,
 orginal INT(5) UNSIGNED NULL,
 title VARCHAR(100) NOT NULL,
 message TEXT NOT NULL,
 PRIMARY KEY (id),
 CONSTRAINT fk_messages_guestbook
  FOREIGN KEY (guestbook_id)
  REFERENCES guestbooks(id)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 CONSTRAINT fk_messages_author
  FOREIGN KEY (author_id)
  REFERENCES authors(id)
   ON DELETE SET NULL
   ON UPDATE CASCADE
)ENGINE=InnoDB;

Een belangrijke toelichting: Orginal is een INT die kan verwijzen naar een record in de eigen tabel (messages). Wanneer orginal NULL is, is het zelf een origineel bericht, anders is het een bewerking. Let op! Je kunt dus ook een bewerking op een bewerking hebben!

Vervolgens stoppen we de volgende populatie in onze database:

INSERT INTO guestbooks (id,name,site) VALUES
 (1,'Mijn gastenboek','http://www.mijnonlinegastenboek.nl'),
 (2,'Appels & Co','http://www.appelsenco.nl'),
 (3,'Blog gastenboek','http://www.barry-en-ronnie-hun-blog.nl');


INSERT INTO authors (id,name) VALUES
 (1,'Stefan'),(2,'Henk'),(3,'Jan'),(4,'Connie'),(5,'Els'),(6,'Anne'),(7,'Niels'),(8,'Barrie'),(9,'Ronnie');


INSERT INTO messages (id,guestbook_id,author_id,orginal,title,message) VALUES
 (1,1,1,NULL,'Hallo','Leuk gastenboek'),
 (2,1,4,NULL,'Hallo','Leuk gastenboek'),
 (3,1,7,NULL,'Hallo','Leuk gastenboek'),
 (4,1,3,NULL,'Hallo','Leuk gastenboek'),
 (5,1,1,1,'Hallo','Leuk gastenboek EDIT'),
 (6,1,5,NULL,'Hallo','Leuk gastenboek'),
 (7,1,5,NULL,'Hallo','Leuk gastenboek'),
 (8,1,2,NULL,'Hallo','Leuk gastenboek'),
 (9,1,6,NULL,'Hallo','Leuk gastenboek'),
 (10,2,8,NULL,'Hallo','Leuk gastenboek'),
 (11,2,3,NULL,'Hallo','Leuk gastenboek'),
 (12,2,6,NULL,'Hallo','Leuk gastenboek'),
 (13,2,1,NULL,'Hallo','Leuk gastenboek'),
 (14,2,1,13,'Hallo','Leuk gastenboek EDIT'),
 (15,2,2,NULL,'Hallo','Leuk gastenboek'),
 (16,2,6,12,'Hallo','Leuk gastenboek'),
 (17,1,4,NULL,'Hallo','Leuk gastenboek'),
 (18,1,2,NULL,'Hallo','Leuk gastenboek'),
 (19,1,9,NULL,'Hallo','Leuk gastenboek'),
 (20,1,7,3,'Hallo','Leuk gastenboek'),
 (21,1,8,NULL,'Hallo','Leuk gastenboek'),
 (22,1,3,NULL,'Hallo','Leuk gastenboek'),
 (23,1,6,9,'Hallo','Leuk gastenboek'),
 (24,1,1,NULL,'Hallo','Leuk gastenboek'),
 (25,1,2,NULL,'Hallo','Leuk gastenboek'),
 (26,3,1,NULL,'Hallo','Leuk gastenboek'),
 (27,3,6,NULL,'Hallo','Leuk gastenboek'),
 (28,3,5,NULL,'Hallo','Leuk gastenboek'),
 (29,3,6,27,'Hallo','Leuk gastenboek'),
 (30,3,1,26,'Hallo','Leuk gastenboek');

De informatie behoefte (vraag) is als volgt: Geef een lijst van alle gebruikers met het aantal keer dat zij een bericht hebben gewijzigd.

De verwachte populatie is als volgt:

+-------+-------+-----------+
|id     |name   | num_edits |
+-------+-------+-----------+
|1      |Stefan |         3 |
|2      |Henk   |         0 |
|3      |Jan    |         0 |
|4      |Connie |         0 |
|5      |Els    |         0 |
|6      |Anne   |         3 |
|7      |Niels  |         1 |
|8      |Barrie |         0 |
|9      |Ronnie |         0 |
+-------+-------+-----------+

Oplossingen graag via PB, en NIET hier!

Voorlopig is er geen prijs, tenzij iemand nog iets in de aanbieding heeft, of een ander crewlid iets leuks bedenkt. Alle goede inzendingen worden in ieder geval gemeld!

Dus criminalspoint laat zien wat je kunt!

Allemaal veel succes, en ik hoop op veel deelname.

Beide inzendingen kloppen inderdaad! Netjes! Ik hou het nog even bij deze query, mocht het inzenden een beetje tot een eind lopen zal ik een moeilijkere query geven.:) Op de zelfde database uiteraard.

Inderdaad inmiddels heb ik in totaal 4 inzendingen, namelijk van:
Niels, JeroenH, Darsstar en MrMees.

MrMees is er nog niet helemaal uit, en Darsstar heeft zelfs twee oplossingen gegeven (één met subquery, en één met join).

Binnenkort zal ik een nieuwe uitdaging plaatsen! Dus jullie hebben nog even de tijd.:cheer::cheer:

Ik hoop natuurlijk op nog meer inzendingen!

Morgen kom ik waarschijnlijk met een nieuwe informatie behoefde. Dus voor iedereen weer een nieuwe query, natuurlijk wel een beetje moeilijker.;)

Nieuwe reactie samengevoegd met originele reactie op 06.12.09 19:51:33:
Ik begin met de vorige inzendingen, deze kwamen van:
JeroenH
Niels
Darsstar
MrMees
Alle inzendingen waren goed!

De orginele oplossing die ik had was:

SELECT a.id, a.name, COUNT(m.orginal) AS num_edits FROM authors a LEFT JOIN messages m ON m.author_id = a.id GROUP BY m.author_id

Toevoegingen hieraan zijn:
JeroenH:

Citaat

Hoewel ik eigenlijk geen count wilde gebruiken op InnoDB vanwege de performance.

InnoDB is hier inderdaad niet snel in. Echter, het gaat nu even om je kunnen, en we kampen nu eenmaal even met het feit dat we MySQL gebruiken (Iedereen draait MySQL, daarom heb ik voor MySQL gekozen).

Darsstar:

SELECT
    a.id,
    a.name,
    (SELECT COUNT(*) FROM messages m WHERE m.author_id = a.id AND orginal IS NOT NULL) AS num_edits
FROM
    authors a
ORDER BY
    id

Een mooie oplossing met een subquery.

Een nieuwe uitdaging voor jullie! De nieuwe informatie behoefte is:

Geef van alle auteurs het ID en de naam het aantal berichten, de gemiddelde lengte van hun berichten (afgerond), de naam het gastenboek waarin ze de meeste berichten hebben geplaatst, en voor hoeveel procent de berichten in dat gastenboek van de auteur is. Order de resultaten op het aantal berichten.

Het verwachte resultaat is:

id      name     num_messages   avg_length      most_posted     procent
1       Stefan   7              16      Mijn gastenboek         43
6       Anne     6              15      Blog gastenboek         33
2       Henk     4              15      Mijn gastenboek         75
3       Jan      3              15      Mijn gastenboek         67
5       Els      3              15      Mijn gastenboek         67
7       Niels    2              15      Mijn gastenboek         100
4       Connie   2              15      Mijn gastenboek         100
8       Barrie   2              15      Appels & Co             50
9       Ronnie   1              15      Mijn gastenboek         100

Deze is echt niet makkelijk! Veel succes dus. De bedoeling is het resultaat in ??n query. Lukt je dat niet, mag je het ook met een view proberen, of met PHP (meerdere query's). Meerdere oplossingen is alleen maar leuk!

Nee, dat maakt niet uit. Ik heb mijn populatie ook een keer aangepast, misschien heeft het daar mee te maken (Wel weer terug gezet). Ben wel heel benieuwd naar je oplossing! Want hij is inderdaad lastig.:cheer: