SQL Server Code for Question

Question

After 1 hour back of this question I cannot solve the code for it. Can anyone help me?

Question: "Indicate the number of drivers per brand of car"

The SQL Server tables created were the following:

CREATE TABLE Carro(
IdCarro INT NOT NULL,
Marca VARCHAR(20) NOT NULL,
Modelo VARCHAR(50),
PRIMARY KEY (IdCarro));

CREATE TABLE Piloto(
IdPiloto INT NOT NULL,
Nome VARCHAR(20) NOT NULL,
Morada VARCHAR(50),
Telefone INT,
PRIMARY KEY (IdPiloto));

CREATE TABLE Equipa(
IdEquipa INT NOT NULL,
Nome VARCHAR(20),
Morada VARCHAR(50),
Telefone INT,
PRIMARY KEY (IdEquipa));

CREATE TABLE Patrocinador(
IdPatrocinador INT NOT NULL,
Nome VARCHAR(20)NOT NULL,
Morada VARCHAR(50),
PRIMARY KEY (IdPatrocinador));

CREATE TABLE Prova(
IdProva INT NOT NULL,
Nome VARCHAR(20) NOT NULL,
Local VARCHAR(50) NOT NULL,
Data DATE NOT NULL,
NrVoltas INT NOT NULL,
MelhorTempo TIME,
PRIMARY KEY (IdProva));

CREATE TABLE EquipaParticipaProva(
IdEquipa INT NOT NULL,
IdCarro INT NOT NULL,
IdPiloto INT NOT NULL,
IdProva INT NOT NULL,
TempoPorVolta TIME,
TempoFinal TIME,
PosicaoRelativa INT,
PosicaoFinal INT,
FOREIGN KEY (IdProva) REFERENCES Prova(IdProva),
FOREIGN KEY (IdCarro) REFERENCES Carro(IdCarro),
FOREIGN KEY (IdPiloto) REFERENCES Piloto(IdPiloto),
PRIMARY KEY (IdEquipa,IdPiloto,IdCarro));

CREATE TABLE PatrocinadorOficial(
IdPatrocinador INT NOT NULL,
IdProva INT NOT NULL,
Valor MONEY NOT NULL,
FOREIGN KEY (IdProva) REFERENCES Prova(IdProva),
PRIMARY KEY (IdPatrocinador, IdProva));

CREATE TABLE PatrocinadorNaoOficial(
IdPatrocinador INT NOT NULL,
IdProva INT NOT NULL,
Valor MONEY NOT NULL,
FOREIGN KEY (IdProva) REFERENCES Prova(IdProva),
PRIMARY KEY (IdPatrocinador, IdProva));

CREATE TABLE Mecanico(
IdMecanico INT NOT NULL,
Nome VARCHAR(20),
Morada VARCHAR(50),
Telefone INT,
PRIMARY KEY (IdMecanico));

CREATE TABLE Afinacao(
IdAfinacao INT NOT NULL,
IdMecanico INT NOT NULL,
IdEquipa INT NOT NULL,
TipoAfinacao VARCHAR(20) NOT NULL,
TempoDespendido TIME,
Data DATE NOT NULL,
FOREIGN KEY (IdEquipa) REFERENCES Equipa(IdEquipa),
FOREIGN KEY (IdMecanico) REFERENCES Mecanico(IdMecanico),
PRIMARY KEY (IdAfinacao));

Later they were inserted in the same "values" for test.

"Inserts" placed for testing:

INSERT INTO Carro
VALUES (1, 'Nissan', 'Skyline');

INSERT INTO Carro
VALUES (2, 'Subaru', 'Impreza');

INSERT INTO Carro
VALUES (3, 'Mitsubishi', 'Evolution');

INSERT INTO Piloto
VALUES (111, 'Piloto1', 'Castelo Branco', 111111111);

INSERT INTO Piloto
VALUES (112, 'Piloto2', 'Castelo Branco', 111111112);

INSERT INTO Piloto
VALUES (113, 'JaGanhou', 'Lisboa', 111111113); 

INSERT INTO Piloto
VALUES (114, 'AsdoVolante', 'Lisboa', 111111114);

INSERT INTO Equipa
VALUES (001, 'OsAtolados', 'Castelo Branco', 272722123);

INSERT INTO Equipa
VALUES (002, 'Sparco', 'Lisboa', 212312432); 

INSERT INTO Equipa
VALUES (003, 'ESTeam', 'Castelo Branco', 272343573);

INSERT INTO Prova
VALUES (010, 'RallycrossCB', 'Castelo Branco', '2017-02-02', 4, '00:04:23');

INSERT INTO EquipaParticipaProva
VALUES (002, 3, 113, 010, '00:01:20', '00:04:40', 2, 2);

INSERT INTO PATROCINADOR
VALUES(921, 'IPCB', 'Av da Talagueira');

INSERT INTO PATROCINADOR
VALUES(922, 'Bells Bar', 'Praceta Qualquer Coisa');

INSERT INTO EQUIPAPARTICIPAPROVA
VALUES(1, 3, 111, 11, '00:01:00', '00:10:00', 1, 1);



INSERT INTO Prova
VALUES (11, 'RallyLels', 'Bells', '2017-03-04', 10, '00:10:00');

Answer 1

Just use a COUNT with DISTINCT grouped by Marca:

SELECT car.Marca,
       COUNT(DISTINCT epp.IdPiloto) AS quantidade
  FROM carro car
       INNER JOIN EquipaParticipaProva epp ON epp.IdCarro = car.IdCarro
 GROUP BY car.Marca

• The GROUP BY for Marca will ensure that the result is grouped by the different brands;
• The DISTINCT will ensure that each IdPiloto appear only once per cluster;
• The COUNT shall count the lines resulting from the Cartesian product of JOIN between the table carro and EquipaParticipaProva;