PWBHnzWt

1CREATE TABLE IF NOT EXISTS `categoria` (
2  `id` INT NOT NULL AUTO_INCREMENT,
3  `nome` VARCHAR(25) NOT NULL,
4  `descricao` VARCHAR(100) NULL,
5  PRIMARY KEY (`id`))
6ENGINE = InnoDB;
7
8CREATE TABLE IF NOT EXISTS `veiculo` (
9  `id` INT NOT NULL AUTO_INCREMENT,
10  `cat_id` INT NOT NULL,
11  `modelo` VARCHAR(100) NULL,
12  `placa` VARCHAR(7) NULL,
13  PRIMARY KEY (`id`),
14  INDEX `cat_id` (`cat_id` ASC),
15  CONSTRAINT `cat_id`
16    FOREIGN KEY (`cat_id`)
17    REFERENCES `categoria` (`id`))
18ENGINE = InnoDB;
19
20CREATE TABLE IF NOT EXISTS `locacao` (
21  `id` INT NOT NULL AUTO_INCREMENT,
22  `veiculo_id` INT NOT NULL,
23  `dt_inicio` DATETIME NOT NULL,
24  `dt_fim` DATETIME NOT NULL,
25  PRIMARY KEY (`id`),
26  INDEX `veiculo_id` (`veiculo_id` ASC),
27  CONSTRAINT `veiculo_id`
28    FOREIGN KEY (`veiculo_id`)
29    REFERENCES `veiculo` (`id`))
30ENGINE = InnoDB;
31	
32SELECT c.nome, count(c.id) AS disponiveis
33FROM categoria AS c
34INNER JOIN veiculo AS v
35ON v.cat_id = c.id
36GROUP BY c.id;
37	
38nome, disponiveis
39A - ECONÔMICO, 5
40B - ECONÔMICO COM AR, 4
41C - SEDAN COM AR, 2