3x3Kk1Ct

1BEGIN TRANSACTION;
2
3/* Create a table called NAMES */
4CREATE TABLE IF NOT EXISTS stiri (
5    titlu TEXT PRIMARY KEY,
6    continut_stire TEXT NOT NULL,
7    data_publicare INT DEFAULT '1556399472'
8);
9
10CREATE TABLE IF NOT EXISTS reporteri (
11    nume_complet TEXT PRIMARY KEY NOT NULL,
12    numar_telefon TEXT NOT NULL
13);
14
15CREATE 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 */
25INSERT 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
31INSERT INTO reporteri VALUES
32('Andrei', '12345'),
33('Ionut', '67890');
34
35INSERT 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*/
44COMMIT;
45
46/* Display all the records from the table */
47
48SELECT numar_telefon FROM (stiri_reporteri JOIN
49reporteri ON
50stiri_reporteri.nume_reporter = reporteri.nume_complet) GROUP BY numar_telefon ORDER BY COUNT(numar_telefon) DESC LIMIT 1;
51