STANAANDREY

select rep's nr tel with max news

Aug 5th, 2021
2,147
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. BEGIN TRANSACTION;
  2.  
  3. /* Create a table called NAMES */
  4. CREATE TABLE IF NOT EXISTS stiri (
  5.     titlu TEXT PRIMARY KEY,
  6.     continut_stire TEXT NOT NULL,
  7.     data_publicare INT DEFAULT '1556399472'
  8. );
  9.  
  10. CREATE TABLE IF NOT EXISTS reporteri (
  11.     nume_complet TEXT PRIMARY KEY NOT NULL,
  12.     numar_telefon TEXT NOT NULL
  13. );
  14.  
  15. CREATE TABLE IF NOT EXISTS stiri_reporteri (
  16.     titlu_stire TEXT NOT NULL,
  17.     nume_reporter TEXT NOT NULL,
  18.  
  19.     PRIMARY KEY (titlu_stire, nume_reporter),
  20.     FOREIGN KEY (titlu_stire) REFERENCES stiri(titlu),
  21.     FOREIGN KEY (nume_reporter) REFERENCES reporteri(nume_complet)
  22. );
  23.  
  24. /* Create few records in this table */
  25. INSERT INTO stiri (titlu, continut_stire) VALUES
  26. ('stire_1', 'Starea vremii'),
  27. ('stire_2', 'Zodiac'),
  28. ('stire_3', 'Carti de citit'),
  29. ('stire_4', 'Sfaturi de dieta');
  30.  
  31. INSERT INTO reporteri VALUES
  32. ('Andrei', '12345'),
  33. ('Ionut', '67890');
  34.  
  35. INSERT INTO stiri_reporteri VALUES
  36. ('stire_1', 'Andrei'),
  37. ('stire_1', 'Ionut'),
  38. ('stire_2', 'Andrei'),
  39. ('stire_2', 'Ionut'),
  40. ('stire_3', 'Andrei'),
  41. ('stire_3', 'Ionut'),
  42. ('stire_4', 'Ionut');
  43. /*commit changes*/
  44. COMMIT;
  45.  
  46. /* Display all the records from the table */
  47.  
  48. SELECT numar_telefon FROM (stiri_reporteri JOIN
  49. reporteri ON
  50. stiri_reporteri.nume_reporter = reporteri.nume_complet) GROUP BY numar_telefon ORDER BY COUNT(numar_telefon) DESC LIMIT 1;
  51.  
RAW Paste Data